Create automated Power BI solution on Azure with a data warehouse approach

Peter: AnalyticsCreator automates complete analytical solutions. It can be used by experts, but also by users with less technical experience.

Instead of manually programming source code, AnalyticsCreator generates the code automatically. It orchestrates the lifecycle of data warehouses, data lakes, and data marts, including design, development, change management, and deployment.

Peter: Let me share a few customer examples.

Robert Bosch, MyMuesli, and a real estate customer all used AnalyticsCreator to accelerate data warehouse development. These customers reported major time savings compared to traditional development approaches.

Peter: AnalyticsCreator is a pure design-time application. There is no runtime dependency.

Customers retain full use of the generated source code, even after a subscription ends. The same metadata model can be deployed to SQL Server, Azure SQL Database, and Synapse.

Peter: The architecture starts with source systems and continues through the storage layer, core layer, data mart layer, cube layer, and presentation layer.

AnalyticsCreator follows a five-step approach. First, we connect to the source system. Second, we extract metadata. Third, we analyse the metadata and create a draft model. Fourth, we model dimensions, facts, calculations, and custom logic. Fifth, we deploy the generated code.

Dimitri: I start by creating a new repository called Demo Azure.

The repository database stores the complete definition of the data warehouse. For this demo, I create a SQL Server connector for the AdventureWorks 2019 database.

Dimitri: Next, I launch the Data Warehouse Wizard.

The wizard analyses the metadata and creates a draft Kimball-style warehouse model. I select source tables from the Human Resources schema.

Dimitri: AnalyticsCreator generates the warehouse structure with source, staging, persisted staging, core, and data mart layers.

The import definitions include mappings between source and target columns. Filters and optional differential loading can also be configured.

Dimitri: Historized tables contain Valid From, Valid To, and surrogate keys.

Historization supports SCD Type 1, SCD Type 2, and fields that should not be tracked. AnalyticsCreator automatically generates the stored procedures required for historization.

Dimitri: The core layer uses transformations, usually implemented as SQL views.

Predefined transformations can automatically apply common logic, such as trimming spaces from strings or converting null values into defined defaults.

Dimitri: AnalyticsCreator creates fact transformations based on detected source relationships.

Snapshot historization allows access to historical versions of the data by using snapshot dates. This makes it possible to analyse data as it was valid at a specific point in time.

Dimitri: Calendar dimensions are generated automatically and can be reused throughout the model.

Reusable SQL macros convert dates into calendar dimension IDs. This keeps date logic consistent across transformations.

Dimitri: Persisting materializes transformation views into physical tables to improve performance.

Synchronization creates the developer database structure and validates the model. If errors exist, AnalyticsCreator detects them during synchronization and highlights the affected objects.

Dimitri: The data mart layer acts as the reporting interface.

Measures are added to facts and can use naming templates to keep names consistent and unique. Dimensions are automatically linked through surrogate keys.

Dimitri: Deployment packages generate DACPAC files, Azure Data Factory pipelines, and Power BI tabular models.

In this demo, Azure SQL Database is used as the target warehouse. Power BI Premium acts as the OLAP endpoint through XMLA.

Dimitri: I import the generated ARM templates into Azure Data Factory.

The generated pipelines orchestrate import, historization, and persisting. Integration runtimes connect the source systems and Azure SQL Database.

Dimitri: I execute the workflow pipeline successfully, and the data is loaded into Azure SQL Database.

The Power BI dataset refreshes successfully and becomes available for reporting.