Data Automation for beginners, automate data warehouses, marts, Power BI

AnalyticsCreator is a metadata-driven design application for data warehouse automation. It is designed for developers and experts, but it can also be used by people with less technical experience.

Instead of programming everything manually, AnalyticsCreator generates source code from the model. It supports the full lifecycle of a data warehouse, data mart, and data lake, including design, development, maintenance, change management, and deployment.

Let me share a few customer examples.

Bosch used AnalyticsCreator in a Power BI and IT controlling project and saved around 80 percent in time and cost. MyMuesli used AnalyticsCreator to learn data warehousing and build a data warehouse across several departments. Another customer with SAP sources built an Azure-based data warehouse and reported being 20 times faster than expected.

AnalyticsCreator can be used for greenfield projects, where no data warehouse exists yet. It can also support modernisation projects, where an existing warehouse needs to be improved, redesigned, or migrated.

Other common use cases include partial reverse engineering, SAP integration, Azure migration, near real-time platforms, and managed service scenarios for partners.

Our vision is customer and partner independence. AnalyticsCreator is a pure design-time application. No AnalyticsCreator runtime component is required, and the generated code runs on the customer side without an AnalyticsCreator runtime licence.

AnalyticsCreator also transfers the copyright of generated source code to customers and partners. This gives customers more control over their generated solution.

From an architecture perspective, AnalyticsCreator collects data from sources, builds storage, core, data mart, and analysis layers, and can generate analytical models for tools such as Power BI, Tableau, Qlik, Cognos, and Excel.

AnalyticsCreator follows a five-step approach.

First, you connect to the source data. Second, you declare or extract metadata. Third, AnalyticsCreator uses this metadata to generate a draft data warehouse model. Fourth, you optimise the model by adding measures, dimensions, scripts, calculations, and business logic. Fifth, you deploy the result to the required target environment.

I will now create a new repository. A repository is a SQL Server database that contains all information about the data warehouse.

The repository is open, so it can be analysed or modified if required. Some customers also fill the repository with metadata to speed up migration from existing data warehouses.

For this demo, I connect to the Microsoft AdventureWorks database. AnalyticsCreator supports several source types, including SQL Server, Oracle, ODBC, OLE DB, flat files, Excel, Access, SAP metadata, Azure Blob Storage, and different blob storage file formats.

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

The wizard imports the selected tables, historizes them, creates dimensions, and creates fact transformations. AnalyticsCreator then generates a layered data warehouse model with source, staging, persisted staging, core, and data mart layers.

The staging layer imports source data into the data warehouse. The persisted staging layer stores historized data. The core layer transforms relational data into facts and dimensions. The data mart layer acts as the reporting interface.

Historization compares imported data with data already stored in the persisted staging layer. If differences are detected, AnalyticsCreator stores the changes and keeps previous versions of the data.

AnalyticsCreator supports full historization, SCD Type 1, and fields that should not be tracked. It generates stored procedures for historization, and these procedures can be modified if needed.

The core layer converts relational data into facts and dimensions. Transformations are usually generated as SQL views.

Predefined transformations can also be applied by datatype. For example, string fields can be trimmed automatically, and null values can be replaced with defined default values.

AnalyticsCreator creates a fact transformation for Employee Department History. Because the participating tables are historized, joining them requires additional logic.

Snapshot historization joins historized tables using snapshot dates and validity periods. This makes it possible to access both current and historical versions of the data.

I also add a calendar dimension to the fact transformation. A Date to ID macro converts date values into calendar dimension keys. Macros are reusable SQL transformation blocks with placeholders, so the same logic can be reused consistently across the model.

Persisting materializes transformation views into physical tables to improve performance.

Full persisting reloads the table each time, while merge persisting updates only detected differences. AnalyticsCreator generates stored procedures for persisting, and these can also be adjusted if required.

The data mart layer is the interface layer for reporting tools and users. It contains facts, dimensions, relationships, and OLAP properties. Here, I define measures such as distinct counts and row count measures.

Synchronization creates a SQL Server developer database containing the structure of the model.

This process also validates the design. If invalid logic is added, synchronization detects the error and highlights the affected transformation, so issues can be corrected before deployment.

To generate the final artefacts, I create a deployment package.

The package can generate a DACPAC file, Integration Services packages, and XMLA scripts for tabular or multidimensional OLAP models. The same model can be deployed on-premise, to Azure, or in mixed environments.

The generated Visual Studio solution contains SSIS packages. The workflow package executes the import, historization, and persisting packages in the correct order. The generated packages can still be maintained manually if needed.

Finally, I connect Power BI Desktop to the generated tabular model. The model includes fact transformations and shared dimensions, and it can be used for reporting.

If you are interested in testing AnalyticsCreator, you can request a trial version, explore the AnalyticsCreator Wiki, and follow the tutorial videos. We also invite you to attend future sessions and the AnalyticsCreator Congress.

Thank you for joining us. If you have further questions, please contact us by email.