English
Using Azure Data Factory 2 0 with AnalyticsCreator DWH Automation
This session demonstrates new AnalyticsCreator Azure integration features, including Azure SQL Database deployment, Azure Data Factory pipeline generation, and Power BI model creation from one metadata-driven warehouse model. The demo uses AdventureWorks as the source, generates the warehouse structure, loads data through Azure Data Factory, and publishes a Power BI model in the cloud.
Questions
- How does AnalyticsCreator generate Azure Data Factory pipelines?
- Can AnalyticsCreator deploy a warehouse model to Azure SQL Database?
- How does AnalyticsCreator create Power BI models in the cloud?
- What role does the workflow pipeline play in Azure Data Factory?
- Can AnalyticsCreator replace SSIS packages with Azure Data Factory pipelines?
- How does AnalyticsCreator use the data mart layer for Power BI models?
Key Takeaways
- AnalyticsCreator can generate Azure Data Factory pipelines instead of SSIS packages.
- Azure SQL Database can be used as the generated data warehouse target.
- Azure SQL Database can be used as the generated data warehouse target.
- Persisting stores selected transformation output physically in tables.
- The generated ARM template can be imported into Azure Data Factory.
- Linked services must be mapped to the correct integration runtimes.
- The workflow pipeline runs import, historization, and persisting.
- AnalyticsCreator can publish Power BI models through XMLA endpoints.
- Power BI reports can be created from the generated model.
- For SAP sources, Theobald connectivity is still recommended for larger data volumes.
Transcript
Hello everyone. Today I will show new AnalyticsCreator features related to Azure integration.
We will create a repository called AV, build a data warehouse, publish it to Azure, generate an Azure Data Factory workflow to load the data, and create a Power BI model in the cloud.
First, I add a connector to the AdventureWorks 2016 database on my computer. I define the connection string, set the Azure source type to SQL Server, and test the connection successfully.
Next, I start the Data Warehouse Wizard and select tables from the Human Resources schema. AnalyticsCreator imports the data, historizes it, creates dimensions, and creates facts.
The generated model contains the source, staging, persisted staging, core, and data mart layers.
I add persisting to selected transformations and synchronize the data warehouse.
Then I add a calendar dimension to a fact transformation using the Start Date field. In the data mart layer, I add measures such as distinct count of Employee, Department, and Shift.
The model is now ready for Azure deployment.
I create a deployment package and generate a DACPAC file, which contains the database definition. This is deployed to the Azure SQL Database called AV.
Instead of generating Integration Services packages, AnalyticsCreator creates Azure Data Factory pipelines through an ARM template. I import this template into an empty Data Factory called AC Factory.
The generated workflow pipeline runs import, historization, and persisting in the correct order. After starting the workflow, the data is loaded successfully into Azure SQL Database.
Next, I create a second deployment package to deploy the tabular model to Power BI cloud.
I use the XMLA endpoint of the Power BI workspace, set the compatibility level to Power BI, and deploy the model. After correcting a missing aggregation setting and defining the data source, the Power BI dataset is created and refreshed successfully.
The generated model contains the facts, dimensions, and measures from the AnalyticsCreator data mart layer.
The key point is that the ETL process can now be handled through Azure Data Factory instead of Integration Services.
For SAP connectivity, we currently recommend the Theobald connector for large SAP data loads. The native Azure Data Factory SAP connector is suitable for smaller volumes, but Theobald is better suited for large SAP tables.
Generated Integration Services packages can still be used on-premise or with Azure SSIS Integration Runtime where required.
Thank you for joining. A more detailed Azure Data Factory session will follow, and this shorter session will be made available for customers and partners waiting for this functionality.
