English
All about Azure and Power BI data automation
Questions
- How can AnalyticsCreator automate Azure and Power BI data warehouse delivery?
- How does AnalyticsCreator generate Azure Data Factory pipelines?
- Can AnalyticsCreator deploy the same model on-premise and to Azure?
- How are Power BI models generated from AnalyticsCreator?
- How does AnalyticsCreator handle fact transformations and calendar dimensions?
- How can model changes be redeployed with AnalyticsCreator?
Key Takeaways
- AnalyticsCreator is a metadata-driven design application for data warehouse automation.
- It generates source code instead of requiring manual development.
- The application focuses on Microsoft target environments such as SQL Server, Azure SQL Database, Azure Data Factory, and Power BI.
- AnalyticsCreator is a design-time application with no runtime dependency.
- The demo uses AdventureWorks as the source database.
- The Data Warehouse Wizard creates a draft model from source metadata.
- Fact transformations can be created from related source tables.
- Calendar dimensions and macros convert date fields into calendar keys.
- Persisting stores complex transformation views physically in tables.
- Deployment packages can generate DACPAC files, SSIS packages, Azure Data Factory ARM templates, and Power BI models.
- The same model can be deployed on-premise or to Azure.
- Azure Data Factory pipelines can be generated and executed to load, historize, and persist data.
- Model changes can be redeployed, although deployment constraints such as non-nullable columns on populated tables must be handled carefully.
Transcript
Hello everyone, and welcome to our AnalyticsCreator classroom on Azure and Power BI data automation.
We begin with customer examples from Bosch, MyMuesli, and an SAP-based customer. Bosch reported saving more than 80 percent in time and cost, MyMuesli built a company-wide data warehouse after only a few days of training, and another customer created an Azure staging layer from SAP data much faster than originally estimated.
AnalyticsCreator is a data automation application for experts and beginners. It generates source code instead of requiring manual programming and supports the full data warehouse lifecycle, including design, development, change management, and deployment.
Common use cases include greenfield data warehouse projects, modernisation, reverse engineering, SAP-based analytics, Azure migration, and near-real-time data warehouse scenarios.
Dimitri starts by creating a new repository and connecting to the Microsoft AdventureWorks 2016 demo database.
He then launches the Data Warehouse Wizard, selects Human Resources tables, imports the data, historizes it, creates dimensions, and creates fact transformations. AnalyticsCreator generates a layered model with source, staging, persisted staging, core, and data mart layers.
In the core layer, AnalyticsCreator creates transformations, usually as SQL views. Dimitri creates fact transformations based on Employee Department History, Employee Pay History, and Job Candidate.
He then adds calendar dimensions for fields such as Start Date, End Date, and Rate Change Date. A reusable Date to ID macro converts date fields into calendar dimension IDs. Measures such as distinct counts are added in the data mart layer.
First, Dimitri creates an on-premise deployment package containing a DACPAC file, SSIS packages, and a tabular OLAP cube.
He then creates an Azure deployment package. This deploys the warehouse structure to Azure SQL Database, generates Azure Data Factory pipelines, and publishes the Power BI model to a Power BI Premium workspace through the XMLA endpoint.
The generated ARM template is imported into Azure Data Factory, creating linked services, datasets, and pipelines.
Dimitri connects the linked services to the correct integration runtimes, then runs the workflow pipeline. The data is imported, historized, and persisted successfully. After refreshing the Power BI dataset, the generated model is ready for reporting.
During the Q&A, Dimitri explains how AnalyticsCreator can support different connector mappings and how changes can be deployed to existing databases.
He also demonstrates adding a new calculated column and measure. A deployment issue appears because the new column is not nullable while the target table already contains data, which is a typical deployment scenario. It can be solved by truncating the persisted table, adding a default value, or handling the deployment manually through the generated DACPAC process.
The session closes with an invitation to send future demo ideas and join the AnalyticsCreator Congress.
