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.
Develop enterprise information warehouse (EIW) which will consolidate sales and master data from a variety of systems and report on that information via a user-friendly reporting tool. Reduce the onboarding time for all future acquisitions by creating a plug and play data transformation and data flow model. The main challenge was to create a unified and generic data model.
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.