Limitations of Excel & Access For Channel Data Analysis
Microsoft Excel and Access are two desktop programs that have helped revolutionize business processes. Excel, launched for Windows in 1987, is a spreadsheet program whose foundational unit is the cell. Access, a desktop relational database program launched by Microsoft in 1992, uses the table as its structural unit.
Cells and tables perform like champs for collecting structured data, and since both programs are nearly ubiquitous in the business world, almost anyone knows or can quickly learn how to use them. It’s then no wonder so many companies have adopted Excel and/or Access to create home-grown channel data management (CDM) systems.
Is Excel the Right Tool for The Job?
Is excel the right tool for the job? Let’s take a closer look. In Excel, each cell is a data collection point. Cells are organized into rows and columns and can be formulated to perform a wide range of calculations, from the simple to the complex.
Excel is great at crunching numbers, especially on static data sets. But how does it handle complex data analysis when the quantity of data is huge and streaming in constantly from disparate sources? Not so well.
To be precise, an Excel 2013 worksheet is 1,048,576 rows by 16,384 columns. That seems like a lot, until you consider the amount of point-of-sale, inventory and sales-in/sales-out data generated daily by a large, multi-tier channel organization, which can amount to millions of values. Just loading this sheer volume of data is time consuming; ensuring that the data from all the dissimilar partners in the channel is accurate and consistent is even more time consuming and people intensive. Finally, subjecting this data to meaningful analysis is a herculean task for even a sophisticated user.
What About Access?
In a sense, Access picks up where Excel leaves off. It’s a powerful relational database that can store large amounts of data, up to a limit of 2 gigabytes in a table. Access lets users draw correlations between the data through simple queries. It can also generate user-friendly reports.
Access is ideal for developing database solutions at the individual and small group level. It can also be used by professional developers to create higher-end, sophisticated applications for workgroups or departments. But it reaches its limitations when asked to perform mission-critical, enterprise level analytics.
Predicting trends in the channel, for example, requires the ability to accurately gather great quantities of data from many different sources in varying formats at high speed. It also requires an analytic engine that can handle queries from multiple business users, not just IT professionals. With its inflexible table structure, table size limit, and query restrictions, Access isn’t well-suited for predictive analytics.
New Tools for The Channel
Both Excel and Access perform well for their intended purpose and within their inherent limitations. However, they are not designed to handle the vast quantity of data streaming in from the channel, which must be cleansed and normalized to ensure its accuracy and usability.
Moreover, data residing in Excel or Access cannot easily be imported into ERP systems used by a majority of manufacturers to forecast demand/supply and manufacturing needs. Nor can it be easily uploaded into CRM systems such as Salesforce, Sugar or Oracle, which are commonly used by vendors to manage partner performance and measure sales against goals.
This is a job for specialized CDM software that can gather, clean, enhance and analyze “big data” from the channel to produce business intelligence, run analytic reports, and provide alerts and notifications when the numbers fall outside KPI parameters set by the manufacturer.