Microsoft Fabric Consultants

Migrating & Transforming Data using Microsoft Fabric Tools

The Challenge

The company faced significant challenges in utilizing the data directly from the source because of the complexity. In addition to this, the reports developed earlier were not interactive and took a lot of time to load.

The Solution

• Simplify the transformations and utilize them in the report.
• Utilize Power BI Report functionalities to provide interactivity.
• Improve decision-making through advanced analytics and reporting.

To address these challenges, we implemented a comprehensive solution leveraging various Microsoft Fabric tools, including Power BI, Power Automate, Datawarehouse, Data Factory and Dataflow Gen2.

Step 1: Data Integration and Management - Consolidate data from different sources into a unified platform.

• Data Sources: Azure SQL Database
• Process: Using Dataflow Gen2 in Data Pipeline, we migrated the data in our staging tables created in the datawarehouse. This integration enabled the company to reduce the load on the SQL Server. And then, from the staging tables, we migrated data to the tables using Stored Procedures which is executed by Data Pipeline.

Step 2: Direct Lake Reporting with Power BI - Provide insights into Sales, Customer, Profit, Opportunities
Reports: Custom Power BI dashboards were created using the default semantic model in DirectLake mode. This will reduce the refresh time taken for the dataset. With the large size of the data, refresh often used to fail because of timeout error. Hence, we implemented Incremental Refresh for the tables that took time to refresh

Step 3: Process Partitions for Incremental Refresh with Power Automate - Automate refreshing partitions daily to get the latest data
Incremental Refresh is configured in the dataset to store 3 years of data and refresh the last 1 month of data. So, in this case, if the data is modified before the last month, we wouldn’t get the latest data.
To resolve this, we created Power Automate, that will refresh the partitions in which the data is modified, which will be executed daily.

The Extra Mile

• Enhanced Visibility: Power BI reports provided broad visibility into the information of the company, enabling proactive management and quick resolution of issues.
• Improved Efficiency: Automated workflows reduced data inconsistency issues which would result in mistrust among the users.
• Cost Savings: Integrated all the tools to be used which is economical as we do not need to subscribe to services from different ecosystems.