English
All about Azure and Power BI data automation
Questions
- How does AnalyticsCreator automate Azure and Power BI data warehouse delivery?
- How can the same model be deployed on-premise and to Azure?
- How does AnalyticsCreator generate Azure Data Factory pipelines?
- How are Power BI models generated from the data mart layer?
- How can calendar dimensions and measures be added to fact transformations?
- How does AnalyticsCreator redeploy model changes?
Key Takeaways
- AnalyticsCreator can generate and deploy the same warehouse model on-premise and in Azure.
- The demo uses AdventureWorks 2016 as the source database.
- The generated model includes source, staging, persistent staging, core, and data mart layers.
- Fact transformations can be created manually and enriched with calendar dimensions
- Calendar macros convert date fields into calendar dimension IDs.
- Separate calendar roles are needed when one fact table has multiple date fields.
- Measures can be generated and named automatically for Power BI models.
- Persisting stores complex view results physically in tables to improve performance.
- Deployment packages can generate DACPAC files, SSIS packages, XMLA files, and Visual Studio solutions.
- Azure deployments can generate Azure Data Factory ARM templates, linked services, datasets, and pipelines.
- Power BI models can be published through the XMLA endpoint when Power BI Premium is available.
- AnalyticsCreator supports redeployment of model changes, including new sources, dimensions, calculated columns, and measures.
Transcript
Hello everyone, and welcome to our AnalyticsCreator classroom on Azure and Power BI data automation.
Peter begins with customer examples from Bosch, MyMuesli, and an SAP-based customer. Bosch reported saving more than 80 percent of time and cost, MyMuesli built its own data warehouse after only four days of training, and another customer created an Azure staging layer from SAP data in only a few days.
AnalyticsCreator is a metadata-driven design application for data warehouse automation. It generates source code, supports the full warehouse lifecycle, and runs as a pure design-time application with no runtime dependency.
Dimitri creates a new repository using the AdventureWorks 2016 demo database as the source.
He adds a SQL Server connector, runs the Data Warehouse Wizard, and creates a layered model with source, staging, persistent staging, core, and data mart layers.
He then creates fact transformations manually, adds calendar logic using Date to ID macros, creates separate calendar roles such as Start Date, End Date, and Rate Change Date, and adds measures such as distinct counts.
Dimitri first creates a local deployment package for SQL Server and SQL Server Analysis Services. AnalyticsCreator generates a DACPAC file, SSIS packages, a tabular OLAP model, an XMLA file, and configuration files.
He then creates an Azure deployment package targeting Azure SQL Database, Azure Data Factory, and Power BI. AnalyticsCreator generates an Azure Data Factory ARM template and deploys the Power BI model through the XMLA endpoint.
After importing the ARM template into Azure Data Factory, Dimitri connects the generated linked services to the correct integration runtimes.
The workflow pipeline runs import, historization, and persisting. Once the workflow completes, the data appears in Azure SQL Database. Dimitri refreshes the Power BI dataset and creates a simple report from the generated model.
The same AnalyticsCreator model can therefore be deployed locally, to Azure, or in a mixed architecture.
During the Q&A, Dimitri explains connector mapping for Azure Data Factory and shows how changes are deployed.
He adds a new source table, redeploys the Azure package, removes an unsupported geography column for Power BI, and confirms that the new Dim Address table appears in the dataset.
He also adds a calculated Is Working column and measure. A deployment issue appears because the new non-nullable column is added to a populated table, so he truncates the table and redeploys successfully.
The session closes with an invitation to send future demo requests and join the AnalyticsCreator Congress.
