Business Intelligence (BI) for Less Than $25k

Business Intelligence (BI) provides valuable and meaningful data in a dashboard environment, but it can quickly become expensive. Associated Grocers (AG) in Baton Rouge implemented BI for less than $25,000. Senior Vice President Steven A. Miller explains how it was done.

I began learning the benefits of BI in 2006 by reading such magazines as CIO and DM Review. I was intrigued to find that companies that implemented BI found information that provided the basis for targeted investment and growth of their companies. I wondered what was buried deep in our data. Cost estimates for such enterprise-level tools grew well into the six figure range. Even after negotiating the lowest possible cost for a turn-key implementation, the cost was still too high for such a new technology deployment at AG. Justification was based on information that we expected to present, but required people to act on the information. Without the right system and people, the returns might not materialize. In October 2006, I attended a BI presentation from a local vendor at a business and technology trade show in Baton Rouge, La. The vendor showed me that by using Microsoft tools such as SQL 2005, report services and analysis services, a company could create a BI tool using Excel as a means for display. Since AG had recently standardized on Microsoft products through a Microsoft Enterprise Agreement (MSEA), it was conceivable that this would be the most effective way for AG to get started on our BI quest, but we had no idea how to start. (Also read Four Tips for Better Business Intelligence.)

Just a few weeks later, the accounting department approached me with a series of questions concerning profitability. They were concerned about fluctuations in margins on freight and gross sales. I took the opportunity to explain that if we cubed up the data in SQL and allowed users to connect to the data using Excel, reports could be generated without running any lengthy access queries—rapid analysis of the problem at hand was the only justification I could offer. I convinced the organization to spend $5,000 with a local vendor to show us how to cube up the data and build reports in Excel using pivot tables. I called it the mini-data mart. Over the course of the next few weeks, the vendor began learning our business and assisted in creating cubes that analyzed the sources of margin fluctuations on sales and freight. Unfortunately, the gross margin analysis proved too difficult to determine because of the selling system structure and varied promotional programs. As a result, there was no way of accurately backing out the current markup and applying the prior markup to determine the difference in expected margin. Valuing Business Intelligence.)

The freight revenue analysis, however, was a fairly straightforward application. The accounting team arrived at the same answer that my team did in about the same amount of time, even though I was creating an entirely new system. Despite the fact that freight revenue had increased, the number of cases delivered to longer distances from the distribution center was less than expected. This report is now a standard report for AG.

After early successes of cubing up data for sales and freight, there was natural interest in all the other information that could be displayed. The marketing team, who works closely with our member stores, began to create pivot tables in Excel that displayed our customer’s sales in both chart and graph in various departments over time compared with similar stores in the area. It was a very useful tool to show opportunities for growth, more efficient ordering, or achieving lower markup rates. (Also read Five Ways to Get Your Employees Better Information More Quickly.)

As user interest in and acceptance for the Excel Pivot Table capabilities gathered momentum, it became necessary to evaluate a more robust graphical interface that could be used by a larger number of employees. Fortunately, another local vendor in Baton Rouge suggested we try a product called Report Portal. This product could be downloaded from the Internet and used for 60 days without any cost. We paid the local vendor for some assistance in getting the system up and running. AG’s Virtual infrastructure and Microsoft Enterprise Agreement allowed quick installation of the new system. Within days, our team began to understand how to connect to the portal. Many other cubes have been added to the mix including operational statistics, order processing metrics and help desk requests. Numerous reports have been created and added for easy access by users. In many cases, users get to information by clicking on links delivered via e-mail or by perusing the list of reports inside the Web portal—even using BlackBerrys. For experienced users, reports can be created on the fly from data that is no more than one day old at any time. For instance, credits issued to our customers are an important metric to follow. The new tool provides quick access to credits by type and by store. This allows AG to focus our attention on the particular type of credit or store with large numbers of credits where the savings can be most noticeable. VPs and directors look daily at sales data to see how new and existing products perform. The users may not know all the inner workings of the system, but they have begun to adopt the terminology. A recent e-mail from a director at AG states, “The elves did not build the cube last night.” His data was there, but in many ways this e-mail highlights the adoption, success and dependence of the frugal BI implementation at AG.

One standard report is the

chart in this PDF

that shows the past four sales weeks and the current-week sales stacked by day for a segment of our business. Note that this chart indicates that there are two days left in the current week—week five. The chart can easily be changed to look at gross sales by department for the various weeks and has drill-down capability to look at certain weeks, days, departments and stores.

Those who know me know that I like to do things in a small and controlled fashion (when possible) and roll them out in phases over time, thereby limiting the financial exposure of the company. Usually this means testing the waters in information services (my department) first, as my team is naturally driven to use technology. BI was no exception. Others at AG share my concern of embarking on a large-scale implementation of an enterprise-level BI system because initially, AG had very little understanding of the scope, requirements and benefits, which is why justification was so difficult. If we had been able to justify an enterprise-level tool early in the project, it would still have taken years to implement. Our current mode of implementation allows us to learn at a very small cost and create a desire and appetite for more capability over time.

In December 2007, our CFO took part in general training on the use of the BI tool. He was very intrigued and decided to create his own reports. He called me for some assistance and expressed some concerns about how users would go about creating ad hoc reports using the report portal. His comments were that the process and naming scheme is just not intuitive enough. And I must agree that it takes some time to understand how to access the data. Many users will only need standard reports such as metrics, but those super users throughout the company will need the ability to create their own reports. Training is very important. My CFO received some one-on-one training on the use of the system and proclaimed at a recent staff meeting that the BI tool is awesome. He had used the tool to quantify some missed opportunities of our retailer’s purchase of full pallets. AG provides an incentive for many products if the retailer orders an entire pallet. This information was provided to the retailers as an opportunity for improvement. When AG handles a full pallet rather than a pallet of individual cases, it makes AG much more efficient and saves our customers money.

Limitations of the report portal are fairly basic. The system has accumulated some 50 million records. As such, there are times that we want to drill down to the item level but cannot because cubing that amount of data causes the size of the cube to grow exponentially causing the display of data to become lethargic to the point of freezing. In the BI world, when a cube has to process a huge amount of data and freezes, it’s called an ice cube. Additional cubes, therefore, have been created for specific purposes. Second, the report portal cannot perform “void” reporting. In our analysis of other tools, it was a fairly straightforward request to show opportunities. For AG, this would mean the ability to show those items that are best sellers from the distribution center but are not part of a particular store’s current product mix. Lastly, this tool does not allow for any predictive or “what if” analysis. In other words, it does not have any forecasting tools and cannot recast data based on a change in the underlying assumptions of the data. For instance, if AG wanted to know what our total margin would have been if we changed the markup, we could not simply apply that to the existing data on demand. In one case, static data sets have been created that show the revenue at various freight fees for freight analysis as discussed earlier in this document, but there is no way to change these variables on the fly.

So, how did I arrive at the total cost? First, understand that I have assigned no cost to the labor resources for those employed at AG. Considerable time has been invested by our infrastructure team and department stakeholders, but mostly by the database analysts. Employees learned how to create cubes and leverage the Report Portal from the local vendors hired to assist with the project. Internal employees became self sufficient and only needed to call the vendors on rare occasions. One employee in particular has held many positions at AG, which have exposed him to almost every aspect of data management. It was a natural progression for him to participate in this BI initiative. I can honestly say that we would not be as far along the BI path today without the talented employees here at AG. I hold my staff in very high regard. That being said, the remaining costs were very low. Approximately $10,000 was spent on professional services, less than $5,000 on licensing, and $10,000 on the report portal. One year later, BI for less than $25k.

Steven A. Miller is the senior vice president of strategic planning, projects and information services for Associated Grocers, Inc. in Baton Rouge, La. He is responsible for all information technology and strategic planning and projects that span the organization. In this role, he has the opportunity to implement projects that improve efficiency for the organization and participate in the development of employees for future key roles.