Company Overview
The client for this business intelligence solution is based in North America, and is primarily engaged in the marketing, bottling, and distribution of a broad portfolio of beverages to the US, Canadian, and European markets. With revenues of over $20 billion, the client operates over 80 production and over 350 distribution facilities globally, a fleet of over 50,000 vehicles, and owns some 2.5 million coolers, beverage dispensers, and vending machines. The client’s portfolio continues to grow and garner market share with a mix of new products and market expansion.
Business Challenge
To manage sales opportunities across their broad network of customer sites, the client had been relying on a business intelligence reporting mechanism based on VBA programming for Microsoft Excel that gathered source data from flat files and pre-generated the thousands of reports required to support the client’s sales force on a monthly basis. This mechanism required approximately 40 hours of processing time along with management monitoring at the beginning of each month to generate the reports for just one of the client business units. And this was one business unit out of more than 40 that the client monitored, meaning that to support all business units would require some 1600 hours of processing and management monitoring time. With a deadline to extend this reporting solution to the remaining 40+ business units looming, finding a more efficient business intelligence reporting mechanism to gather the source data and deliver reports to client field users became a top client priority.
Solution Approach
The Northridge Business Intelligence (BI) consulting team recommended Microsoft SQL Server as the foundational platform to meet this challenge. The team constructed an Extract, Transform, and Load (ETL) system using SQL Server Integration Services (SSIS) to capture and store the data required for the reports from the existing flat files that were fed from a source system. Northridge also constructed a Data Warehouse to store the collected data in a manner that would generate efficient reports. Reporting speed was a critical, since client field users would be running the reports offsite and would be regularly running reports for multiple locations, often more than 20 at once. To serve the data to the users, Northridge generated a SQL Server Reporting Services (SSRS) report system that would deliver the data on-demand, rather than pre-generating the thousands of reports required for the field.
Results Delivered
The completed solution was delivered on-time, and represented a significant upgrade over the prior solution in terms of time and resource investment, as well as improved business intelligence leading to more accurate and rapid decision making. The administrators at the client site no longer needed to dedicate machines to the task of running reports in Excel for each business unit for one week every month. They also no longer had to oversee the process during the course of each monthly run, as the processing time had been reduced from days to just a couple of hours. Most importantly, client field users no longer wait a week for report availability, as the reports are now available to them over the web the morning after the first day of each month. Since the SQL Server Reporting Services (SSRS) reports now deliver data on demand, the platform has also freed the client to begin loading data to the system more often than once a month for even more rapid analysis and decision making.