The corporate was in an aggressive growth mode by acquiring 2~3 smaller companies every year. Each of them had implemented various ERP systems and their own data model. The corporate did not have any reporting system to look at consolidated data from all of these entities at one place so that they could make informed knowledgeable decisions. The analysts had to prepare a labor intensive manual consolidated report by going through hundreds of spreadsheets and reports from each of these entities. They were looking for an integrated solution that would allow them to view all sales data from all acquired companies on one dashboard that could be sliced and diced by key dimension.

A generic interface files layout was designed for Material Master Data, Customer data, Sales organizational and divisional data, Sales area data, Sales force data, orders, Invoices, Hierarchies etc. ETL processes were developed to load the interface files from various sources into the staging area, scrub the data, apply business logic and load them to the data warehouse for further reporting and analysis. A universal reporting hierarchy was built for customer and products. An interface was developed which would translate the hierarchies from each of these entities into the universal hierarchy.