AnalyticsCreator Congress 2022 | Modernization of a Data Warehouse
Analytics Creator enables teams to automatically generate complete data warehouse repositories from metadata using SQL, eliminating manual object creation. In this session, the presenters show how SAP-based metadata drives the automated creation of staging, persisting, and core layers. The result is a fully generated data warehouse built in minutes instead of weeks.
Short answer
- How can I automate a data warehouse repository?
- Can Analytics Creator generate tables and pipelines automatically?
- How do you integrate SAP data into SQL Server?
- What is metadata-driven data warehouse automation?
- How do you avoid manual DWH modeling?
Key takeaways
- Metadata can fully drive repository generation
- Analytics Creator removes manual object creation
- SAP integration requires custom handling (timestamps, joins, ABAP)
- Incremental loading and historization are built into the process
- SQL-based automation gives full control and no lock-in
- Thousands of objects can be generated automatically
- Manual DWH builds (weeks) → automated (minutes)
Transcript
We are Hendrik Power and Alexander Seidler.
Our goal is to avoid designing thousands of repository objects manually.
We developed SQL scripts to generate objects in the AnalyticsCreator repository database using our own data warehouse metadata.
Within our IT department, there is a separate data warehouse team.
It is a small unit consisting of myself, one colleague, and some external support.
In our company, a significant amount of know-how resides in non-IT departments.
These teams can design cubes and write SQL, which sometimes makes it challenging to align with their requirements.
I am Hendrik Power, Head of Data Warehouse.
I have 20 years of experience with Microsoft SQL Server.
I use my own templating framework, and we have built software to enable communication between SQL Server and SAP ECC.
Our data warehouse consists of two main parts.
The first part is a relational SQL Server database that provides SAP ERP data to business users.
Around 80% of the tables are automatically generated from SAP metadata.
Staging and core layers are generated, as well as procedures.
The second part is a SQL-based SAP mirror that replicates SAP data into SQL Server.
It includes historization, detects changes and deleted records, and supports incremental loading.
Data is loaded via SSIS and ABAP.
SAP does not always provide timestamps, which adds complexity.
Why evolve?
Increasing complexity, high maintenance effort, and the lack of a user interface.
We evaluated several tools.
AnalyticsCreator stood out because it works on-premise, avoids vendor lock-in, and fits well into the Microsoft BI stack.
We use metadata stored in our database and transform it for AnalyticsCreator.
This is implemented using T-SQL procedures.
The main procedure loads metadata for a subset of tables.
We define import tables, SSIS packages, persist logic, incremental modes, friendly names, and row-level security.
We create the repository database by restoring an empty template.
We also create macros and schemas, define connectors, and configure sources, columns, and references.
We create the staging and persisting layers.
The repository is then synchronized with the data warehouse.
Transformations and incremental logic are defined.
We define workflows and packages.
SSIS packages are generated and workflows are executed.
This process creates the database.
The result is a fully automated repository.
No manual modeling is required.
Thousands of tables are generated efficiently.
Manual creation would take weeks—automation achieves this much faster.
