English
Data Automation for beginners, automate data warehouses, marts, Power BI
AnalyticsCreator helps beginners build a data warehouse by generating SQL Server, SSIS, OLAP, and Power BI artefacts from a graphical metadata model instead of manual coding. The demo shows how to create a Kimball-style data warehouse from AdventureWorks, add historization, generate packages, deploy on-premise, and discuss Azure deployment options.
Short answer
- What is data warehouse automation for beginners?
- How does AnalyticsCreator create a data warehouse from metadata?
- Can beginners use AnalyticsCreator without deep data warehouse experience?
- How does the Data Warehouse Wizard work?
- How does AnalyticsCreator generate SSIS packages and OLAP models?
- Can AnalyticsCreator deploy the same model on-premise and to Azure?
Key Takeaways
- AnalyticsCreator is suitable for experts and beginners.
- The application uses a graphical interface and generates source code automatically.
- Beginners can learn data warehouse concepts faster because the model and generated code are connected.
- AnalyticsCreator is a pure design-time tool with no runtime dependency.
- The same design can target different Microsoft environments.
- The Data Warehouse Wizard can create a draft Kimball-style warehouse from source metadata.
- The demo uses AdventureWorks as a source database.
- Generated layers include source, staging, persisted staging, core, and datamart layers.
- Historization supports SCD Type 1 and SCD Type 2 logic.
- Transformations are usually generated as SQL views.
- Macros provide reusable SQL logic.
- Deployment packages generate DACPAC files, SSIS packages, XMLA scripts, and OLAP models.
- Power BI can connect to the generated tabular model.
- SAP metadata connectors allow modelling even without direct SAP system access.
Transcript
Peter: Hello everyone, and welcome to Data Warehouse Automation for Beginners. My name is Peter, and I am the CEO of AnalyticsCreator. I am joined by Dimitri, our CTO, who is responsible for development, and Brianna from our marketing team.
In this session, I want to show what you can do with AnalyticsCreator and what you can achieve with it. If you already have some experience in data warehousing and analytics, you should be able to understand AnalyticsCreator quickly and become productive in projects within a short time. If you are completely new to data warehousing, it will take longer, but AnalyticsCreator can support your learning journey.
AnalyticsCreator is a data warehouse automation application designed for experts, but it can also be used by people without deep expert experience.
It provides a graphical user interface where you can see the data stream and the holistic data model. Instead of programming everything manually, you design the model and AnalyticsCreator generates the code.
AnalyticsCreator orchestrates the full lifecycle of a data warehouse and data marts, including design, development, change management, and deployment.
Peter: Let me share a few customer examples.
Robert Bosch reported around 80 percent savings in time and cost when creating and maintaining a data warehouse with AnalyticsCreator. This project is described as one of the largest Microsoft BI projects in the Bosch group, with more than 5,000 Power BI users.
MyMuesli started with little data warehouse experience. They used AnalyticsCreator to build data warehouses and reports for different departments and were able to continue development independently after a short training period.
Another customer in the real estate sector had many SAP instances and reported being 20 times faster than originally estimated.
AnalyticsCreator is a pure design-time application. It generates source code and does not require runtime components on the customer side.
The generated source code can be deployed into Microsoft environments such as Azure, SQL Server, and Power BI. Once the solution is generated and deployed, customers can continue running it without depending on AnalyticsCreator in the operational process.
This gives customers independence. AnalyticsCreator is used for design, change, and deployment, but the running data warehouse remains under the customer’s control.
Peter: The architecture starts with source systems, then moves through the storage layer, core layer, data mart layer, and analysis layer.
AnalyticsCreator follows a five-step approach. First, you connect to the source data. Second, AnalyticsCreator extracts metadata from the source. Third, it analyses that metadata and suggests a suitable model. Fourth, you optimise the model by adding business logic, measures, dimensions, and transformations. Fifth, you deploy the generated source code.
Common use cases include greenfield data warehouse projects, modernisation of existing data warehouses, Microsoft Azure analytics environments with SAP sources, migration from on-premise systems to Azure, real-time platform requirements, and managed service scenarios for partners.
More use cases are available on the AnalyticsCreator website.
Dimitri: I will now start AnalyticsCreator and create a new repository called Test DWH.
AnalyticsCreator creates a SQL Server repository database that contains the full definition of the data warehouse. For this demo, I will use AdventureWorks as the source database.
Dimitri: I add a SQL Server connector to the AdventureWorks database. AnalyticsCreator supports SQL Server, Oracle, flat files, Excel, ODBC sources, SAP, and Azure Blob Storage.
To create the draft data warehouse, I use the Data Warehouse Wizard. I select tables from the Human Resources schema and choose the classic Kimball modelling approach.
AnalyticsCreator generates a layer diagram from left to right.
The source layer contains the source metadata. The staging layer imports the data. The persisted staging layer contains historized data. The core layer transforms the data into facts and dimensions. The data mart layer is used for reporting and model generation.
The import package maps source fields to target fields. Filters and variables can also be used for differential data loading.
Historization stores previous versions of data using Valid From, Valid To, and surrogate keys. It can be configured per column using SCD Type 2, SCD Type 1, or no historization. AnalyticsCreator generates the stored procedures required for historization.
The core layer uses transformations, usually generated as SQL views.
AnalyticsCreator supports regular transformations, manual transformations, script transformations, and external SSIS packages. Predefined transformations can also be applied based on field type, for example trimming string values or replacing null strings.
AnalyticsCreator creates a fact transformation for Employee Department History.
Because the source tables are historized, the transformation uses surrogate keys from the historized tables. Snapshot historization uses snapshot dates to retrieve the row version that was valid at a specific point in time.
This makes it possible to access both current and historical versions of the data.
Dimitri: I add calendar dimension references to the fact transformations.
Macros are reusable SQL statements with placeholders. For example, a Date to ID macro converts date values into calendar dimension IDs. Macros can be reused across transformations and updated centrally.
Dimitri: I synchronize the data warehouse model. Synchronization creates the SQL Server database structure and validates the transformations.
To demonstrate validation, I add an intentional error. AnalyticsCreator detects the invalid column and highlights the affected transformation. After correcting the issue, synchronization succeeds.
Persisting materializes transformation views into tables to improve performance.
The data mart layer defines dimensions, facts, relationships, and measures for OLAP and Power BI-style models.
Calendar dimensions can be exposed multiple times, for example as Start Date, End Date, and Rate Change Date. Measures can also be created with generated naming templates to keep names unique.
Dimitri: I create an on-premise deployment package.
The package generates a DACPAC file, SSIS packages, and an XMLA script for a tabular OLAP cube. The generated Visual Studio solution contains workflow, import, historization, and persisting packages.
The workflow package runs import, historization, and persisting in the correct order.
The generated SQL Server database is filled with AdventureWorks data. Power BI Desktop connects to the generated Analysis Services model, and after processing, the model can be used for reporting.
A participant asks whether the wizard is general or source-specific.
Dimitri: AnalyticsCreator has different wizards and metadata options, especially for SAP. The wizard analyses metadata, not the data itself. It reads structures, fields, and references.
Another participant asks about deployment. DACPAC deployment is handled by Microsoft. It can create a new database or generate delta scripts for an existing database.
