60 Minutes to create from SAP source a DWH on MS SQL or Azure SQL DB

One customer example is Robert Bosch. The original estimate for the data warehouse project was around one year of development time. With AnalyticsCreator, the project was completed in approximately two months.

Another example is MyMuesli. They needed only four days of consulting from us to train a new employee. After that, the employee was able to continue developing the data warehouse independently.

Today I will create an SAP data warehouse from scratch and publish parts of it first on-premise and then in Azure.

I start by opening AnalyticsCreator and creating a new data warehouse project. AnalyticsCreator creates the repository database in SQL Server. This database contains the full data warehouse definition.

There are two ways to connect to SAP. The first option is to add a connector directly to an SAP system.

For this demo, I will use a metadata connector instead. A metadata connector stores the source metadata definition. This means I do not need a direct connection to the SAP system at the beginning. I can still create the data warehouse because the structures of the SAP tables are already known.

The metadata connector also stores references between SAP tables, because SAP itself does not expose this relationship metadata in the required way.

For example, the relationship between BKPF and BSEG uses four fields. These references are very important for automatic data warehouse generation.

We could manually create imports, historization, and transformations step by step, but instead I will use the Data Warehouse Wizard.

The wizard creates a draft version of the data warehouse automatically. I will import every table, historize every table, create dimensions, and create one fact transformation for BSEG.

The generated model follows a typical layered data warehouse structure. On the left side, we have the sources. The next layer is the staging layer.

The import package definition maps SAP columns to staging columns. We can also define filters, for example to import only records from business year 2021 onwards.

The next layer is the persisted staging layer, which contains historized tables.

To historize the data, AnalyticsCreator uses the Slowly Changing Dimension approach. The historized table contains all source columns, plus Valid From, Valid To, and a surrogate key.

The next layer is the core layer.

The core layer converts the relational source structures into dimensions and facts. Transformations are usually generated automatically as SQL views.

Now we look at the fact transformation. I add the calendar dimension using macros.

Macros are an important feature of AnalyticsCreator. In this case, I use a macro to convert SAP date strings into integer calendar IDs.

Next, I add persisting to materialize the fact transformation into a table. Persisting stores the contents of a transformation view physically in a table.

The next layer is the data mart layer. This layer becomes the interface for OLAP cubes and Power BI models.

The model is now ready for deployment.

To deploy it, we create a deployment package. The deployment package generates DACPAC files, SSIS packages, and Power BI tabular models.

Visual Studio opens the generated solution, where we can see the SSIS packages created automatically by AnalyticsCreator.

All SAP tables are imported in parallel. The historization package executes the generated historization stored procedures.

Inside Power BI Premium, we can see the generated semantic model. All dimensions, measures, and facts appear automatically.

I then deploy the same model into Azure SQL Database. This shows that the same AnalyticsCreator model can be reused for hybrid architectures.

Here we can see a large SAP-based AnalyticsCreator project.

Using filters, we can inspect how one SAP table flows through the data warehouse into data marts and analytical structures.

This shows that AnalyticsCreator is not only a data warehouse generator, but also a data lineage tool.