Data Warehouse Architecture and Analytic Reporting
Located in a northwest suburb of Chicago, this supply chain management enterprise specializes in supply chain process development and optimization for companies all over the U.S. Their comprehensive services are aimed at helping businesses create new supply chain business processes as well as improving existing systems due to business growth, growth in new markets or locations, mergers and acquisitions, inefficient/inflexible processes, or special customer demands.
At the heart of this firm’s expertise is their ability to most expeditiously get something from Point A to Point B. However, they did not have existing BI capability to determine if this was happening efficiently and cost-effectively. As a result, the organization’s Director of BI and B2B Services looked to build the internal BI infrastructure from the ground up to solve this problem. He proposed building a data warehouse to allow the firm to gain visibility into the core functionality of what they do—shipping goods out, receiving goods in, and accurately maintaining/tracking inventory. CBIG was brought aboard to plan, design, and build the architecture and analytics processes.
Our goal was to implement a scalable, cost-effective technology platform by analyzing, developing, and constructing a data warehouse and analytical reporting architecture to support current and future business intelligence needs. Key challenges included identification of data sources, missing data, standardization and cleansing of data into common format, and identification of a reporting platform.
The CBIG team worked with the firm’s IT team to identify the list of metrics that they wanted to be able to report on, and the sources of the data that would be used to support these metrics. CBIG then analyzed the customer’s existing data including historical data, and subsequently developed the organization’s data warehouse and business intelligence solution, incorporating ETL tools and reports. Source data feeds originated from AS/400, Oracle, Excel, and CSV. The project timeline, from October to the end of March (6 months), was fast-tracked to build the warehouse, while report-building functionality is being implemented now.
CBIG was able to turn the project around much faster than the CEO imagined. Based on preliminary results, the firm got some terrific insights and plans to add even more functionality and capability to the system. The CEO and Director of BI and B2B Services used the success of this approach to sell these solutions internally to other stakeholders within the organization, and other phases are now planned including route tracking capability.
- Microsoft SQL Server 2012
- Microsoft SSIS
- IBM Cognos