All about Azure and Power BI

AnalyticsCreator orchestrates the full lifecycle of a data warehouse, data marts, and data lakes. It supports design, development, change management, and deployment.

The goal is to replace traditional ETL approaches with a faster and more automated process. In customer projects, AnalyticsCreator can help deliver results up to 10 times faster and at lower cost.

One example is Bosch. We completed an IT controlling data warehouse project where Bosch saved around 80 percent in time and cost. A previous team needed around 12 months to build the earlier version. With AnalyticsCreator, one person rebuilt and modernised the warehouse in around two and a half months.

Another example is MyMuesli. They started their data journey with a junior consultant who had just come from university. After only four training days, they were able to continue building their data warehouse independently. Today, they use a data warehouse across all departments.

A third example is a real estate customer with several SAP instances. They needed to create an integration layer from SAP into Azure and reported that they were 20 times faster using AnalyticsCreator.

Our vision is customer independence. AnalyticsCreator is a pure design-time application. There is no runtime dependency and no vendor lock-in.

Once the source code is generated, the solution continues to run without AnalyticsCreator. We also transfer the copyright of the generated code to the customer or partner, so they can use it independently.

We focus 100 percent on the Microsoft data stack and Azure. Our goal is to specialise deeply in Microsoft environments rather than support every possible database platform.

AnalyticsCreator also has an open repository. Everything in the metadata environment is stored in Microsoft SQL Server. Customers and partners can inspect the repository, build their own extensions, or create add-ons.

AnalyticsCreator supports the full data warehouse development process, from sources to staging, core, data mart, in-memory or OLAP layers, and the presentation layer.

It can generate Power BI models, Tableau models, and Qlik models. An export layer is also part of the roadmap, so the final warehouse layer can be exported to analytical databases such as SAP HANA or other targets.

AnalyticsCreator follows a five-step approach.

First, I connect AnalyticsCreator to the source database. Second, AnalyticsCreator extracts metadata such as keys, field names, columns, and structural information. Third, the intelligent wizard analyses the metadata and suggests a draft data warehouse model, such as Kimball, Data Vault, or a mixed approach.

Fourth, I optimise the model by validating structures, cleaning definitions, adding KPIs, and defining historization concepts. Fifth, AnalyticsCreator generates and deploys the source code to the target environment, such as Azure, Power BI, or SQL Server.

AnalyticsCreator can be used for greenfield data warehouse projects, where nothing exists yet. It can also modernise existing data warehouses by importing parts of the current structure or code and redesigning the architecture.

SAP integration is another common use case, especially with the Theobald connector. AnalyticsCreator can also support moves from on-premise environments to Azure, real-time data platform scenarios, partner-managed services, and multi-client analytics environments.

AnalyticsCreator helps deliver results in days instead of weeks. It supports a more agile development process because changes can be made and regenerated quickly.

The holistic data model creates a bridge between IT and the business. It helps users understand the full process from source systems to calculations, data movement, and KPIs. The data lineage model also helps business users understand where data comes from and how it is used.

Now I will create a small data warehouse and publish it to Azure. As the source, I will use the Microsoft AdventureWorks database.

I start AnalyticsCreator and create a new repository. This repository is a new data warehouse project and contains the full description and definition of the warehouse. It is stored as a SQL Server database on my local SQL Server, and I can access every object in it.

The first step is to add a connector. AnalyticsCreator supports Microsoft SQL Server, other database sources, text files, Excel files, SAP, Azure Blob Storage, and OData.

For this demo, I use SQL Server as the data source and create a connector to the AdventureWorks database.

Next, I start the Data Warehouse Wizard. I could create sources, imports, historization, and transformations manually, but the wizard can create most of the draft warehouse automatically.

I select tables from the Human Resources schema and choose a typical Kimball model. The wizard imports the selected tables, historizes them, creates dimensions, and creates fact transformations. I also define object naming rules and create a calendar dimension.

The generated diagram shows the warehouse layers from left to right.

The source layer contains the imported AdventureWorks source tables. The staging layer is where data is first imported. The persisted staging layer stores historized data. The core layer transforms data into facts and dimensions. The data mart layer contains data stars that can later be exported into OLAP cubes, tabular models, or Power BI models.

The staging table has the same structure as the source table, with the same fields and data types.

The import definition shows the mapping between source and target fields. I can add transformations, define filters, create variables, and use those variables in filters. This supports differential loading, so I can load only the data restricted by the filter. I can also add scripts before or after the import process.

After import, the next step is historization. Historization is not mandatory, but we recommend it because it gives access to previous versions of the data.

The historized table contains the original data plus Valid From, Valid To, and a surrogate key. I can configure historization for every column using full history, SCD Type 1, or no historization.

I can also configure what happens when a record is missing from the source. The historized record can be closed, kept open, or an empty record can be added to close gaps in the timeline.

AnalyticsCreator generates stored procedures for historization. If needed, I can modify these procedures or add SQL scripts before or after historization.

The core layer contains transformations. The most common transformation in AnalyticsCreator is a SQL view.

Regular transformations are generated automatically. Manual transformations allow me to provide the view text myself. SQL scripts, stored procedures, and manually created Integration Services packages can also be used.

Predefined transformations are applied based on field type. For example, a string trim transformation automatically applies LTRIM and RTRIM to varchar and nvarchar columns.

Another predefined transformation can convert null strings to N/A. These transformations standardise common logic and can be defined by the user.

AnalyticsCreator detected that Employee Department History is related to Department, Employee, and Shift. Based on these relationships, it created a fact transformation.

The relationships were imported from AdventureWorks because the database contains foreign keys. AnalyticsCreator uses this metadata to generate the fact transformation automatically.

Employee Department History contains Start Date and End Date columns. Instead of using these fields directly, I use IDs from the generated calendar dimension.

To do this, I use a macro called Date to ID. A macro is a reusable SQL statement with placeholders. In the generated view, the macro call is replaced by the full SQL expression.

Because the tables in the fact transformation are historized, joining them is more complex. A historized table can contain several rows for the same business key.

AnalyticsCreator can solve this with snapshot historization. The generated view uses a snapshot table containing snapshot dates. Each historized table is joined using the condition that the snapshot date falls between Valid From and Valid To.

This retrieves the version of each row that was valid for the selected snapshot date.

By default, the snapshot table contains at least the current date. If I add historical dates, such as the end of previous months, I can access previous versions of the data through the data mart layer and Power BI models.

This creates an additional timeline in the data warehouse. If only current data is required, the transformation can also use only current historized records.

Next, I add persisting to the fact transformation and to some additional transformations.

Persisting stores the content of a view in a physical table during the load process. This is useful for improving performance when transformations are complex.

I synchronize the data warehouse. Synchronization materializes the model on SQL Server by creating a database structure based on the design.

Every time I synchronize, the structure designed in AnalyticsCreator is created in the SQL Server database.

For persisting, AnalyticsCreator generates a stored procedure.

A full persisting procedure deletes the target table content and reloads all data. AnalyticsCreator also supports incremental persisting types, such as merge, incremental load, and historical persisting. Manual persisting can be used when custom logic is required.

The data mart layer is the interface layer of the data warehouse. It contains data stars that can later be used to generate OLAP cubes or Power BI models.

If a transformation should participate in a specific data star, I select the star name and the dimension name. AnalyticsCreator then generates the corresponding view in the data mart schema.

The model is ready, but it is still only a model. Now I will deploy it.

For this demo, I create a pure Azure data warehouse. I have an empty Azure SQL Database, an empty Power BI Premium workspace, and an empty Azure Data Factory with the required integration runtimes.

I generate a deployment package. This is a Visual Studio solution containing everything needed to deploy the data warehouse.

For the Azure deployment, I create and deploy a DACPAC file to Azure SQL Database. I also generate a tabular OLAP model using the Power BI compatibility level and create Azure Data Factory pipelines for the ETL process.

AnalyticsCreator creates the Visual Studio solution and deploys the database directly. After deployment, Azure SQL Database contains the generated structures, and the Power BI model is created in the Power BI workspace.

AnalyticsCreator generates an Azure Data Factory ARM template, which I import into Azure Data Factory. After deployment, Data Factory contains the full ETL definition, including pipelines, datasets, and linked services.

Next, I connect the linked services to the correct integration runtimes. One linked service connects to the Azure SQL data warehouse, and the other connects to the AdventureWorks source database. After testing both connections successfully, the Data Factory environment is ready.

The workflow pipeline is the main pipeline. It runs the full process, including import, historization, and persisting. I start the workflow pipeline, and the data is loaded successfully into Azure SQL Database.

I update the credentials for the Azure SQL Database, refresh the Power BI dataset, and create a small report.

The Power BI model now contains data and can be used for reporting. I can also use Power BI Quick Insights to generate example visuals from the dataset.

With this process, I created an Azure SQL Database, a Power BI model, and an Azure Data Factory ETL process from the same AnalyticsCreator model. The same model can also be deployed on-premise or in a mixed architecture, with some components on-premise and others in Azure.

A question comes up about the cost of running Power BI Premium models in Azure. Power BI Premium per user is a cost-effective option. Microsoft provides different subscription models, including Power BI Pro, Power BI Premium per user, and Power BI Premium per capacity. With Premium per user, model sizes of up to 100 GB and up to 48 refreshes per day are supported.

Another question is about versioning the AnalyticsCreator model. There are two ways to manage versions. First, I can save the repository to the AnalyticsCreator cloud. Each time I save, the previous version is not replaced, so I can later load earlier versions.

Second, I can save the repository to a SQL file. This file contains the full repository definition and can be versioned with Git, Subversion, or another version control system.

If you want to try AnalyticsCreator, you can request a free 30-day trial on our website. At the beginning, I recommend watching the tutorial videos on YouTube and following them step by step. We also provide documentation on our wiki at wiki.analyticscreator.com.

Thank you for joining. If you have any further questions, please contact us by email. Have a nice weekend, and see you soon.