To go from your business application to an analytics solution, usually a step in between is needed. The relational database of an ERP system is not very well suited to directly link your dashboards to. This type of database is built to store the different steps in your production process, project planning or financial transactions, and usually has a lot of different tables to do this as efficient as possible. This large number of tables is the result of normalizing the database, which is intended to limit the number of times the same information is stored. Finding a good balance between too much and too little normalization is an art only a skilled database designer understands. If you over-normalize your relational database it can become very tedious to maintain and evolve.
Data visualization dashboards are meant to discover trends in your business by using your data. Bu they are difficult to link directly to a relational database with a lot of different tables. They follow a different logic, which means the data model needs to be de-normalized. We give up on the idea to only store information once, and allow for duplicates to facilitate visualizations. Think about the way you structure data you want to use in an Excel pivot table. There you also allow for doubles in your data source to link the pivot table to. A data warehouse is built for this kind of de-normalized data models. In general there are two kinds: dimensional models and data vault models. Read more about data warehouses here.
In the case of Verhofste, no data warehouse was present. Building a new DHW has significant costs and throughput time. Instead we advised to de-normalize the data needed to perform the required analysis as views in the database of the ERP. Views are a technical term that means queries stored as tables in this context. There are a couple of assumptions that were fulfilled which made this possible: there is a good contact with the ERP vendor who could free up a developer to build the views, all the data that was needed for the analysis is present in the ERP, a unique key to link the ERP data to the accounting data already existed, no historization or snapshots of the data were in scope.
