Skip to content

microsoft/Power-BI-ADF-Synapse

Repository files navigation

Project

Using Azure Synapse with Microsoft Power BI

This code will provive you a step-by-step guide to create a DataMart with transport information.

This DataMart is build using Azure Data Factory for ELT/ETL and Azure Synapse as database. The final stage is a Power BI report reading the information from DataMart.

Some important aspects in the solution is how to take advantage of dynamic content in Azure Data Factoty, creating a clean solution from begining to end.

What can be achieved?

The idea of the solution is to speed-up ETL / ETL development and simplify how you code your pipelines in Azure Data Factory. Also how you can take advantage of using a combination of Azure Synapse and Power BI as your standard serving layer solution.

We will split the solution deployment in:

  1. Create Azure Services.
  2. Deploy database scripts.
  3. Deploy Azure Data Factory ARM template.

General architecture:

alt text

Azure Data Factory solution:

alt text

Understanding configuration table

Requirements

  • Access to Azure account with permission to create Azure services
  • Permission to install PowerShell components in your local computer
  • A Power BI Free or Pro license

1 - Create Azure Services

The Azure Services can be created in Azure Portal or executing PowerShell scripts:

Scripts location

1.1 - Create resource group.ps1

2.2 - Create Storage Account.ps1

3.3 - Create Azure Synapse Workspace.ps1

4.4 - Create Azure SQL Pool.ps1

5.5 - Create Azure Data Factory.ps1

2 - Create Synapse objects

Once you create Azure services, is necessary to deploy database objects. Please execute next steps:

Scripts location

1.1 - Create schemas.sql

2.2 - Create operational tables.sql

3.3 - Create data warehouse tables.sql

4.4 - Create stage tables.sql

5.5 - Create external tables.sql

6.6 - Insert dimensions data.sql

7.7 - Insert configuration table.sql

3 - Deploy ARM template

Once you deployed Azure services and Synapse objects you need to deploy Azure Data Factory ARM template. Please execute next steps:

ARM template location

1.1 - Before deploy ARM template file, you need to replace some values. There are 6 points to replace the information before deploy scripts. Please replace these values carfully.

2.2 - Once you replace the values with your own information, do the fallow:

  • Open your Azure Data Factory;
  • On manage tab, select "ARM Template" option;
  • Select option "Import ARM Template";
  • On "Custom Deployment" editor, select option "Build your own template in the editor";
  • Load the file "arm_template.json" and save;
  • Select the resource group, region and factory name (same name you deployed with PoweShell);

4 - Others steps

Is necessary to upload a file into the storage account. Please fallow the instructions:

1.1 - Download file named "taxi_zone_lookup.csv": Dowload CSV file

2.2 - Upload file into this location in the storage account: "nyccabdata\ingestion\lookpup\taxi_zone_lookup.csv"

About

No description, website, or topics provided.

Resources

Code of conduct

Security policy

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published