Build a data warehouse using Data Vault 2 0 with dimensional model on top

Peter: AnalyticsCreator is a data warehouse automation application that generates source code automatically. It supports the full lifecycle of a data warehouse, including design, development, change management, and deployment.

The application is designed for experts, but it can also be used by non-experts. Instead of programming everything manually, users work with a model, and AnalyticsCreator generates the required code.

We have seen strong results in customer projects. Bosch reported more than 80 percent savings in time and cost, and another SAP-focused customer reported being 20 times faster than originally estimated.

Peter: AnalyticsCreator supports greenfield projects, modernisation of existing data warehouses, SAP integration, Azure migration, and managed service scenarios for partners.

Our vision is to give customers independence. AnalyticsCreator is a pure design-time application and avoids runtime dependency. The generated code continues to run independently after deployment, so customers are not locked into AnalyticsCreator for daily operations.

Peter: AnalyticsCreator supports the full architecture of a data warehouse, from source systems through staging, core layers, data marts, cubes, and presentation layers.

The process follows five main steps. First, we connect to the source system. Second, AnalyticsCreator extracts the metadata. Third, it analyses the metadata and generates a draft model. Fourth, users optimise and extend the model. Fifth, the generated source code is deployed to the target Microsoft environment.

Dimitri: I start by creating a new repository and importing the SAP FI metadata connector.

This metadata connector contains predefined metadata for SAP Finance and Controlling tables. It allows me to model a data warehouse without direct access to the SAP system.

Later, the metadata can be compared with the real SAP system and extended with customer-specific fields if needed.

Dimitri: I launch the Data Warehouse Wizard and select SAP FI tables.

For this part of the demo, I choose the Data Vault approach. The wizard automatically generates hubs, links, satellites, dimensions, and fact transformations.

The generated model includes staging, persistent staging, core, and data mart layers. This gives us a complete draft structure that can then be reviewed and refined.

Dimitri: The import definitions map source fields to staging fields. Filters and variables can be used to support differential loading, and additional scripts can run before or after the import process.

For the Data Vault model, import tables receive additional calculated columns that contain hash keys. These hash keys are generated using reusable SQL macros.

The hash keys replace business keys in relationships and are used later to connect the generated Data Vault objects.

Dimitri: Master data tables generate hubs and satellites. Hubs expose the business keys and hash keys, while satellites contain the descriptive attributes.

Transactional tables generate links and link satellites. Links contain hash references to related hubs, while link satellites contain additional descriptive information.

The core layer then converts the Data Vault objects into facts and dimensions. Fact transformations combine the related Data Vault objects, and AnalyticsCreator generates the SQL views automatically.

Dimitri: Snapshot historization allows us to retrieve valid historical records for specific snapshot dates. The snapshot dimension makes historical timeline analysis possible because we can analyse data as it was valid at a particular point in time.

Next, I create a second repository using the classic Kimball approach. This model contains historized dimensions and facts without Data Vault hubs and satellites.

I then add hash keys to the dimensional model. AnalyticsCreator can automatically generate hash keys and hash references for dimensional structures as well. Fact transformations can switch between business key joins and hash key joins, so users can decide which strategy fits their model best.

Dimitri: The mixed approach combines the simplicity of Kimball modelling with the flexibility of Data Vault hash key handling.

This keeps the model easier to understand than a full Data Vault implementation, while still supporting flexible historization and relationship handling.

Peter: We will continue with future AnalyticsCreator webinars and our annual AnalyticsCreator Congress. The congress will include roadmap discussions, customer stories, and partner presentations.