AnalyticsCreator Congress 2021 Demo - Customer in the Construction Engineering Industry

My name is Robert Buch, and I am a BI Architect at BI Automation. In this session, I will introduce a customer project where we implemented a data warehouse with AnalyticsCreator, followed by a short demo from Elmir on historization.

The customer was an international engineering consultancy with around 2,500 employees, 40 offices, and locations across five continents. Their project-related and financial data was consolidated at the headquarters in Austria. The goal was to feed a new controlling cube from a new ERP source.

The customer moved from Agresso, now part of Unit4, to Deltek Maconomy. They wanted the existing cube functionality, reports, and dashboards to remain largely unchanged, while only the source system changed.

The old architecture included ERP data, intranet employee information, Active Directory security data, and CSV enrichment files. However, data lineage was difficult to understand, and the old ETL process used inefficient full loads.

AnalyticsCreator was selected because it provides visible data lineage, automated documentation, reusable ETL patterns, and automatic SSIS package generation. The new controlling cube now loads in around 20 minutes using delta loading, compared with around four hours for the old full-load cube.

Because customer data could not be shown, the team prepared a simple demo focused on historization.

Historization is essential because production systems often overwrite changed values. Without it, historical reports can become incorrect. SCD Type 2 solves this by storing Date From and Date To values for each record, so the data warehouse knows which version was valid at a specific point in time.

The demo uses two CSV tables: Orders and People. The People table contains four regions with one responsible person per region, while the Orders table references those regions. When a responsible person changes, the warehouse must preserve the old responsibility for historical orders.

Elmir creates a repository called ART SCD Progress and adds a CSV connector for the Orders and People files. He reads the file structures, defines primary keys, creates imports, and synchronizes the model.

He then adds historization. Orders use SCD Type 2 with Do Not Close, because each monthly file contains new order data and old orders should not be closed when they are missing from later files. People also uses SCD Type 2, while the Remark column uses SCD Type 1 so changes do not create a new history row.

A reference is added from Orders to People using the Region column. Elmir then creates a simple core model with a calendar dimension, dimensions, and a fact transformation.

Elmir creates and deploys the package with SSIS packages and environment variables. The demo then uses a recorded video to simulate several monthly loads.

The initial load imports the first Orders and People files. In the November load, the responsible person for the West region changes, and AnalyticsCreator closes the old record and creates a new current record. In the December load, an employee changes surname, and AnalyticsCreator creates a new historical version.

A validation query confirms that earlier West-region orders are linked to Anna, while later orders are linked to Betty Bugatti after the change was detected.

The demo shows how quickly SCD Type 2 historization can be implemented with AnalyticsCreator, and how later changes can be handled by adjusting historization settings instead of rebuilding the logic manually.