Shortly after joining ShopKo Stores in 1996 as their new Data Warehouse Manager, I became involved in the development and support of a spreadmart called the Weekly Merchandise Status Report (WMSR).
In ShopKo-speak: “The Whim-sur.”
Not familiar with the term “spreadmart?”
Well, you may not know the term, but there is a good chance that you have at least one – probably many – spreadmarts around your business. In fact, people in your company might be managing a large portion of the business using spreadmarts.
The term spreadmart was coined a few years ago by The Data Warehouse Institute (TDWI) – a knowledge-sharing organization for business intelligence/data warehouse professionals – to describe a non-IT created personal data mart often built using a desktop spreadsheet tool, such as Microsoft Excel.
Spreadmarts exist in an attempt to satisfy an organization’s never ending demand for quick, cheap, and easy access to information; a demand that often cannot (or will not) be met by the company’s IT organization. A recent TDWI survey found that over 90% of all organizations use spreadmarts for at least some of their reporting – a trend that is expected to continue for the foreseeable future.
Creating spreadmarts allows a business user (or a whole functional area) to bypass their IT department and develop complex and sometimes mission critical reporting applications without the need to “stand in line” at the IT department hoping that some junior programmer will eventually get around to building those sales forecast reports that you desperately need for your budget meeting.
Reporting is never an IT department’s top priority.
The proliferation of spreadmarts throughout a company is often the result of senior management’s desire to have critical company information presented to them in a manner that is impossible to obtain using the company’s approved reporting infrastructures (such as a data warehouse).
By the way, it is a myth that executives don’t want to see detail level company performance data. While they may want to review only summary level data to start with (for example using a “dashboard” tool) every executive I’ve ever worked with has wanted to “dive into the cornflakes” when it comes to looking at their company’s data – particularly when times are tough.
That was the case at ShopKo with the WMSR spreadmart.
This particular spreadmart was the brainchild of ShopKo’s then COO, Bill Podany.
Podany had come on board to develop and execute a new merchandising strategy that the company hoped would re-energize and re-brand the stale and unfocused regional retailer to compete effectively with the likes of Wal-Mart and Target.
A key element of ShopKo’s merchandise strategy involved eliminating large numbers of low margin and poorly performing inventory categories ($89 men’s polyester suits, straight-weight motor oil, claw hammers from Bolivia, for example) while building up higher margin and more trendy categories (mostly women’s and kid’s moderately priced clothing).
To understand what was and was not selling (or should or shouldn’t be sold), required that ShopKo’s merchants and executives have a tool that allowed them to analyze multiple years of integrated sales and inventory data using a specific process and report format dictated by Podany.
As none of ShopKo’s existing reports came close to meeting Podany’s requirements, and given that ShopKo’s IT department was almost fully engaged at the time revamping the company’s business technology systems, the job of developing this sales analysis tool fell to the analysts in ShopKo’s merchandise planning department.
As data warehouse manager, my team and I were responsible for providing some of the raw sales, margin, and inventory data to the analysts building what was essentially a very large standalone MS-Excel spreadsheet residing on the senior analyst’s desktop computer.
On a weekly basis, the merchandise analysts would load the data we provided them into a series of complex spreadsheets. They would then manually enter pieces of data that were not available electronically, merge this data with data from other applications and spreadsheets, and run a number of custom-built MS-Excel macros that “massaged” the data into the WMSR format. After waiting a few hours for the MS-Excel software to perform all the calculations and formatting, out would pop that week’s WMSR.
Following a quick review by the analysts, the spreadmart was distributed all over the company by email or printed off and bound for the executives.
In many ways the WMSR was an excellent analysis tool. As it evolved over the years, it provided ShopKo’s executives and merchants with a tool that allowed them to see historical sales and inventory information at both the macro and micro level all on one report. It also gave them the ability to monitor key performance indicators on a regular basis so they could see where things were going right, and more importantly, where they weren’t, when it came to sales and inventory levels.
Although the WMSR provided value to, and was widely used by, everyone connected with the merchandising function at ShopKo, the development of this tool serves to illustrate many of the weaknesses of a spreadmart.
For example, over time, the WMSR created an island of data separate from the data found in the corporation’s managed data repositories – in particular, the data warehouse my team was building.
Because the analysts building and maintaining the WMSR used naming conventions, data manipulations routines, and data filters that were different from the data warehouse, the WMSR data and the data from the warehouse became disconnected. This caused what is called “the dueling reports phenomenon” in which multiple people each present what should be the same data but with different figures.
“Is the gross margin for category 30 on the WMSR right, or is the gross margin reported out of the data warehouse right?” was the type of question heard all the time.
Reconciling these differences was often impossible with the result that people began to distrust the information reported to them from either source. A lot of time was spent in data validation exercises along with the running and re running of reports in an attempt to come up with the “one right answer.”
The WMSR required a great deal of effort to build and maintain on the part of the business. Several relatively high paid analysts spent upwards of 50% of their workweek loading, manipulating, and formatting data for the WMSR output.
Instead of spending the time analyzing sales and inventory data, these people in effect became an adjunct IT department supporting one application.
Although no information system is immune from data errors, the data found in spreadmarts such as the WMSR, is often rife with errors. This is due to manual data entry errors, overly complex and untested formulas, and a lack of an audit trail to ensure that the data in the spreadmart meets the company’s control and compliance standards.
Even with these and other limitations, the WMSR continued to be relied upon for years as ShopKo’s preferred sales analysis tool. It continued to exist even after the data warehouse could have duplicated the WMSR functionality and eliminated all of the tool’s shortcomings.
This brings up yet another problem with spreadmarts. While many people readily acknowledge the limitations of spreadmarts, and though eliminating spreadmarts from an organization is usually one of the justifications for building a data warehouse, spreadmarts can be harder to get rid of than a nest of Florida cockroaches. Even in the face of company mandates and IT strong-arm tactics, many users (like those at ShopKo) refuse to give them up.
That was true ten years ago and it is still true today. If anything, it is worse today given how sophisticated and easy to use desktop reporting tools have become in the last couple of years.
So, given that spreadmarts are here to stay, what can be done to improve them?
Realizing that spreadmarts are now a business reality, some companies are working to improve their spreadmarts’ data quality by renovating how they function. The key to this renovation is to split the data integration process (data collection, transformation, and storage) from the analytical process (reporting and distribution).
The goal being to re-engineer the spreadmarts’ data provisioning so IT can manage the data centrally while allowing end-users to continue to pull data (but not store it) in their familiar and much-loved spreadsheets for reporting and analysis.
A spreadmart renovation improves data quality, supports data governance, enhances the timeliness of data provisioning, improves resource utilization, eliminates training time on a new BI tool, and supports IT/business alignment. When done well, a spreadmart renovation is a win for both IT and the business.
