Design develop deploy in a automated way your MS Azure Analytics platform

Let me share a few customer examples.

One example is Bosch. They had already spent around 12 months updating an existing data warehouse. With AnalyticsCreator, we regenerated the solution in around two and a half months. They have now been working with the solution for several years and report significantly lower total cost of ownership.

Another example is MyMuesli. They used AnalyticsCreator as both a development application and a learning environment. With only a few days of consulting, they were able to build their data warehouse largely by themselves.

A third example is a real estate customer with several SAP instances. They used AnalyticsCreator to build an Azure data environment and reported being 20 times faster than originally estimated.

AnalyticsCreator has also achieved first-place rankings in the BARC Data Management Survey. Our vision is independence: AnalyticsCreator is a pure design-time application with no runtime dependency. Under the contract terms, customers receive the copyright for the generated source code.

AnalyticsCreator focuses fully on the Microsoft data stack. We concentrate on Microsoft environments such as SQL Server, Azure, Power BI, and related technologies.

AnalyticsCreator also has an open repository. All metadata and modelling information is stored in Microsoft SQL Server, which allows customers and partners to develop add-ons, create custom generators, or support additional scenarios such as write-back.

The architecture can cover the full path from source systems to presentation. AnalyticsCreator can automate layers such as staging, persisted staging, core, data mart, OLAP, and reporting layers. It supports Data Vault, Kimball, and hybrid models.

AnalyticsCreator follows a five-step approach.

First, we connect to the data. Second, we import or maintain the metadata. Third, AnalyticsCreator uses this metadata to generate a draft model. Fourth, we validate and extend the model by adding dimensions, KPIs, calculations, scripts, and business logic. Fifth, we deploy the generated code and structures.

Typical use cases include greenfield data warehouse projects, modernisation, partial reverse engineering, SAP analytics environments, Azure migration, real-time or near-real-time data warehousing, and managed services for partners.

I will now create a data warehouse and publish it on Microsoft Azure. We will create an Azure SQL Database, generate Azure Data Factory pipelines, and create a Power BI model.

I start AnalyticsCreator and create a new repository called Test Azure. A repository is both a project container and a SQL Server database containing the full data warehouse definition.

Next, I add a connector to the AdventureWorks database. AnalyticsCreator supports SQL Server, Oracle, text files, OLE DB, ODBC, SAP, OData, and Azure Blob Storage.

I launch the Data Warehouse Wizard and select tables from the Human Resources schema.

The wizard creates a draft model by importing the selected tables, historizing them, creating dimensions, and generating fact transformations. I also define naming conventions and create a calendar dimension.

AnalyticsCreator then generates a layer diagram with source, staging, persisted staging, core, and data mart layers. The staging layer imports source data. The persisted staging layer stores historized data. The core layer transforms relational data into facts and dimensions. The data mart layer provides the structure for reporting and analytical models.

The import definition maps source columns to target columns. Filters and variables can be used to support differential data loading. For example, a timestamp variable can restrict the import to records changed after a certain point in time. Scripts can also run before or after import.

After import, the data is historized in the persisted staging layer. Historized tables contain the original source fields plus Date From, Date To, and a surrogate key.

During historization, AnalyticsCreator compares imported data with existing historized data. If changes are detected, the previous version is closed and a new version is inserted. Fields can be configured for SCD Type 2, SCD Type 1, or no tracking.

The core layer contains transformations, usually generated as SQL views. Predefined transformations can apply logic based on datatype, such as trimming strings or replacing null values. Manual, script, external, and union transformations are also supported.

For fact transformations involving historized tables, AnalyticsCreator can use snapshot historization. The snapshot table contains dates used to retrieve records valid at specific points in time. These snapshot dimensions can later be used in Power BI to analyse current or historical versions of data.

Persisting can be used to materialize complex transformation views into physical tables. Full and differential persisting approaches are supported, and AnalyticsCreator generates the stored procedures required for persisting.

I add a calendar dimension to the fact transformation. A Date to ID macro converts a date field into a calendar dimension ID.

Macros are reusable transformation blocks with placeholders. They can be used throughout AnalyticsCreator, and if a macro is changed, the logic is updated wherever it is used.

In the data mart layer, I define measures for reporting and analytical models. Measure names can be generated automatically using templates, which helps keep names consistent and unique.

The model is now ready for deployment. I save it to the AnalyticsCreator cloud and create a deployment package for Azure.

The deployment package generates a DACPAC file, an Azure Data Factory ARM template, and a Power BI model deployment. The target Azure SQL Database is initially empty.

After the DACPAC deployment, the Azure SQL Database contains the generated tables and views. I then import the ARM template into Azure Data Factory, which creates the linked services, datasets, and pipelines.

The workflow pipeline runs the process in the correct order: import, historization, and persisting.

The Power BI dataset is created in a Power BI Premium workspace using the XMLA endpoint.

After deployment, I update the dataset credentials and refresh the data. The refresh completes successfully, and the dataset can now be used for reporting.

I also use Power BI Quick Insights to generate example reports. The important point is that AnalyticsCreator has generated the Azure SQL Database, Azure Data Factory pipelines, and Power BI dataset from one metadata model.

During the Q&A, we also discuss Azure Blob Storage and Parquet files. AnalyticsCreator supports Azure Blob Storage, Parquet files, text files, directories, and subdirectories. It can generate Azure pipelines or SSIS packages for these file-based sources.

We also preview the upcoming export connector. This will allow data to be exported from the data warehouse into ODBC or OLE DB databases, CSV or text files, and Microsoft Blob Storage.

Thank you for joining. If there are no further questions, we will close the session.