Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Introduction
In this article, you will learn how to process massive volumes of data using Microsoft Fabric.
Prerequisites
Overview
This architectural overview represents the various fabric artifacts that we will use during the process.
Above architecture follows the guidelines of medallion architecture. The Medallion Lake architecture organizes data in a Lakehouse into distinct layers, following best practices for data management and processing.
Procedure Overview
1. Create a Lakehouse to store the raw data and enriched data in delta format.
2. Create a Data pipeline to copy the raw data from on premises data source to Lakehouse.
3. Create a Notebook to transform the raw data and to build the staging tables.
4. Create a dataflow gen 2 to normalize the staging tables.
5. Create Power BI semantic model and build the report.
Procedure
Create a Lakehouse
1. Open app.powerbi.com or app.fabric.microsoft.com Create a new fabric workspace. Make sure the workspace is hosted in a Fabric/Premium capacity.
2. Click on the power bi icon on bottom left and switch to Data Engineering persona. Click on Lakehouse icon to create a new Lakehouse
enter name and select the sensiticity label and click on create.
3. As soon as you click on create, a lakehouse, a SQL analytics endpoint and also a default semantic model will be automatically created.
You can click on the three dots icon next to the SQL endpoint and clcik on‘Copy SQL analytics endpoint’. Note it your notepad, we will use this info in the next section.
Before creating the pipeline:
Create an on-premises gateway connection
a. Open app.fabric.microsoft.com and click on the gear icon on top right Click on ‘Manage gateways and connections’ page
b. Click on ‘+New’ button on top left and a new connection window will open on the right.
After filling all the details of your data source, click on ‘Create’ button.
Note: You can only create an on-premises gateway connection after configuring the on premises gateway machine. Refer the link attached in the prerequisites section for more information.
Create a Data pipeline
1. Open your fabric workspace and switch to the data engineering persona click on pipeline icon to create a new data pipeline.
2. Enter the name of your pipelines and select the appropriate sensitivity label and click on create.
3. Open the pipeline on the top menu click on copy data drop down Click on ‘Add to Canvas’
4. Click on the added ‘Copy data’ activity under ‘General’ section you can enter a meaningful name to your activity.
5. Switch to ‘Source’ section select the on-prem gateway connection from the connection dropdown. Check the box next to ‘Recursively’ Incase if you want to delete the files from on prem server as soon as they moved to the Lakehouse then click on the check box next to ‘Delete files after completion’
6. Switch to ‘Destination’ section From the ‘Connection’ dropdown, select the Lakehouse that was created before Select the ‘File Format’ as binary.
7. Run the pipelines and check the Run status and make sure the files are copied the Lakehouse.
Now that data is copied to the Fabric Lakehouse, In the following section we will create a Fabric notebook to transform the data and to build the staging tables.
Create a Notebook
1. Switch to Synapse Data Engineering persona click on Notebook icon
2. Select the appropriate sensitivity label and click on ‘Save and continue’
3. Open the Notebook and in the left-hand panel ‘Add’ to link the Lakehouse
4. We created the Lakehouse in the previous steps, so select existing Lakehouse and select the Lakehouse.
5. Import the necessary libraries required to transform the raw data Initialize the relative path of raw data folder in Lakehouse Initialize the schema variable and include the columns in the csv files.
6. Run the below spark code to load the data from csv files into spark data frames create staging delta tables.
7. Make sure delta table is created. You can verify it by expanding the table folder in the left-hand panel.
Raw data has been transformed, lets normalize the fact table using Dataflow gen 2
Create a Dataflow gen 2
1. Switch to Data Factory persona and click on the Dataflow Gen2 icon.
2. Click on get data and select SQL server Enter the Lakehouse SQL analytics end point in the ‘Server’ section click on next and select the ‘Staging_Sales’ table created before and click on next.
3. As per the business logic and keeping the downstream analytics solutions in mind. Normalize the fact table into dimensions and facts.
4. On the right bottom you will find an option to add Lakehouse as destination, Click on add and select the Lakehouse. Enter the table name and click on Next Choose the update method as append or overwrite depending on the data pipeline strategy.
5. Select the appropriate data types for each column and click on ‘Save Settings’
6. Repeat step 4 and step 5 for all other tables and run the dataflow.
7. Once the data refresh is completed, open the Lakehouse or SQL analytics endpoint to validate the newly created delta tables.
Note: To reduce the run time of dataflow, make sure you use ‘Native SQL’ option and include the transformation logic within the SQL statement. For example: In the below query, instead of performing the distinct operation in the dataflow after retrieving whole sales table, we included the distinct operation within SQL statement.
Considering the truncating the staging tables and archive the raw data files in the Lakehouse, as soon as the dataflow refresh is completed. This practice can reduce the running time in the subsequent runs and reduces cost.
Create a Power BI Semantic model and build the Power BI Report
1. Switch to Power BI persona open the Lakehouse and on top you will find an option to create a ‘New semantic model’.
2. Enter the name for your semantic model and select all the production grade non staging tables and click on confirm.
3. Open the new semantic model and establish the relationships between the dimension tables and fact tables.
4. After completing the necessary data modelling, create appropriate measures for building the dashboard.
5. Click on the new report button on the top to create the reports as per the A sample report would look like this
Data Refresh
To refresh all the key data artifacts, instead of scheduling them individually or triggering them manually, orchestrating all the activities in the data pipeline would be the best option.
In the same pipeline that we created in the previous steps, include the notebook, dataflow and semantic model refresh activities and schedule the pipeline.
You can also consider including failure alerts, as soon as any activity in the pipeline fails you will be notified with an emai
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.