A Practical Guide to Implement End-to-End Data Warehousing PoC Using Microsoft Fabric-Part1

Sometime back we had the opportunity to help a customer implement a PoC using Microsoft Fabric. In a series of blogs, we will demonstrate how we helped our customer from the SfMC team to implement a Data Warehouse PoC using the ELT (Extract, Load, Transform) approach. For this PoC, we used sample data from SQL Server’s well-known sample databases, giving us a straightforward and accessible way to illustrate the capabilities of Microsoft Fabric in a real-world data warehousing scenario. 

 

The following were the customer requirements:​

Build the enterprise grade Data Warehouse solution in Microsoft Fabric​ 

• Ingest data from 59 diverse sources across 130 different countries 

• Source data from ADLS (JSON)​ 

• Source data from Azure and on-prem SQL Server databases​ 

• Other data sources. ​ 

 ​PoC scope:​ 

• ELT approach ingest (OLTP DB & ADLS Gen2) data directly into the Warehouse​ 

• Transformation using stored procedures to load the production schema 

 Solution Summary: 

• Ingest OLTP data from source SQL Server (full load)​ 

• Meta data driven copy activity to load data into staging schema of the Data Warehouse.​ 

• Stored procedures to process the staging data along with data from ADLS shortcuts.​ 

• Load the production Dimension tables.​ 

• Application’s customer data arrives into ADLS storage as JSON files.​ 

• Stored Procedures to process the data and load Customer dimension and Fact data using incremental load.​ 

• Built 6 Dimensions and 1 Fact.​ 

• Build reports.​ 

Sample overview of items that were implemented: 

Sample report from the PoC: 

 

Prerequisites:

To implement this PoC, the following are the prerequisites: 

• you may need to download the following files: 

You can download them from: Download all the required files to local drive (eg: C:\temp). We assume that you already have a Fabric tenant setup with a Fabric Capacity (you will need a F64 capacity to test the co-pilot feature else a F2 capacity will be sufficient).

 

If Fabric is not enabled, use the link below to enable Fabric for your organization. 

Step by Step Guide to Enable Microsoft Fabric for Microsoft 365 Developer Account 

You will also need: 

• A logical SQL Server (how to deploy one, how to add local IP address to the firewall exception) 
• ADLS Gen2 Account (how to deploy one, how to create a container) 
• Once created, Once the account is created, navigate to the “Data Storage” section and create a container name it levelup or a name of choice 

 

• Open the container “levelup”and create a folder called “JSON_FILES” 

• Install Storage Explorer, configure to connect to your Azure subscription, and navigate to storage account container. Upload five JSON files from the downloaded folder “JSON_FILES” to ADLS Gen2 in the "levelup" container under the "JSON_FILES" folder. 

 

• Upload the following folders by choosing “Upload Folder” from Azure Storage Explorer.  

• • Sales_SalesOrderDetail 
  • Sales_SalesOrderHeader 

 

The above two folders containing Delta files, will be used for creating shortcuts in the Lakehouse which will be used for building the tables within the Warehouse in the subsequent tasks.  

• After the upload, you should have the folders below inside your Levelup container. 

 

Next, create a database on the Azure SQL Server by using the bacpac files downloaded earlier: 

• Connect to Azure SQL Logical Server using SSMS (if you don’t have, you can download here) 

• Right click on Databases and select the option “Import Data-Tier Application”. 

 

 

• Follow the screen captures below to complete the database bacpac import. 

 

 

 

 

Note: Depending upon DTU/V-core chosen, the Import activities might take up 30 mins. 

 

 

 Continue to the next blog