Microsoft Fabric Co-Existing as Semantic Layer with Power BI & Snowflake

Overview

Customers have modernized their data warehouse by hosting it on Snowflake (in AWS) and have chosen Power BI (in Azure) as their single reporting/analytics platform. They used to have multiple reporting/analytics platforms like QlikView, Tableau & Power BI, but consolidating them on one platform has made operations & management easier. This architecture has also led to performance challenges and rising costs of Snowflake and Power BI and also prohibited them from implementing Self Data Service.  Customers are looking to reduce costs for Power BI and Snowflake and solve some of the architectural issues in offering self-data service for business users.

 

Some of the challenges identified are as below.

• Current architecture does not support self-data service due to cost issues. Developers create most of the reports/dashboards, and business users need IT team help for any change.
• Report/Dashboard performance problems because of Power BI and Snowflake integration. In some cases, data travels over the internet across data centers for any report/dashboard, adding to latency/performance issues.
• Increasing cost of Snowflake + Power BI with increase in adoption.

To mitigate the above challenges, creating a semantic layer between Snowflake and Power BI on Microsoft Fabric is a possible solution to enable self-data service for users. Semantic layer on Microsoft Fabric will use mirroring capability to replicate base tables from Snowflake in near real time. Once base tables are replicated from Snowflake, using the Data Engineering capability of Fabric aggregated data, calculated measures, KPI/Metrics will be created and presented to business users. For business users it will be more of a drag and drop of objects experience for creating reports/dashboards in Power BI on their own. Power BI will use the ‘Direct Lake Mode’ querying capability of Fabric to get same performance /speed by creating the report in Power BI as ‘Import Mode’.

 

Resources:

• Data Ingestion Methods in Microsoft Fabric
• Snowflake Mirroring in Microsoft Fabric
• OneLake Shortcuts - Microsoft Fabric
• Create and Manage an Eventstream in Microsoft Fabric

 

Current Architecture

Customer has a data warehouse on Snowflake (Finmart 2.0) that receives data feeds from SAP HANA, which has financial data, and from various Excel and csv files. The data on Snowflake is stored in base tables as simple structures with little or no data modelling, and without any computed measures, aggregations, etc. All modelling (Star Schema) and any aggregation, calculation, happens in Power BI engine when data is imported from base tables to Star Schema structure made in Power BI. The current architecture is causing problems for Power BI and preventing customer from creating Self Data Service Architecture.

 

Another way to model queries is by using a composite-based approach from Power BI to Snowflake data warehouse.

 

Here are some of the pros and cons of the architecture mentioned above:

 

Advantages

• Power BI can use detailed/original data to create reports and dashboards that show aggregated/computed measures. The data comes from one place that stores past and current data from SAP HANA and some other sources.

Disadvantages

• Self-data service is not available because data must be imported, modelled, aggregated and transformed to make report/dashboard for business users.
• Different data services on various cloud/data centers. SAP HANA, Snowflake (AWS Cloud), Power BI (Azure Cloud).
• Power BI report has latency in both import mode and direct query mode.
• It will be hard to create data lineage as multiple platforms are used with each having its own repository.
• Data moving over network has its own cost and security issues.
• Egress fee for each query.

 

Proposed Architecture with Fabric as Semantic Layer

 

 

 

A possible architecture is to build a semantic layer in Microsoft Fabric using Snowflake Mirroring. Here are steps for creating a semantic layer in Fabric.

 

1. Use Microsoft Fabric Snowflake Mirroring capability to mirror Snowflake database in Fabric. Mirroring in Fabric is a low-cost and low-latency turnkey solution. Once your operational data source is set up for mirroring, data will continuously replicate into the OneLake for analytics consumption. With the most up-to-date data in a query table format in OneLake, you can now use all the different services in Fabric, such as running analytics with Spark, executing notebooks, data engineering, visualizing through Power BI Reports, and more.

 

2. Each Mirrored Snowflake has an autogenerated SQL Analytics Endpoint that provides a rich analytical experience on top of the Delta Tables created by the mirroring process. Users have access to familiar T-SQL commands that can define and query data objects but not manipulate the data, as it is a ready only copy.

 

3. Using Data Engineering in Microsoft Fabric, read data from mirrored database and build aggregated tables and Star Schema models to be directly consumed by the visualization layer.

 

4. Use Direct Lake Query feature of Power BI   to create reports and dashboards on top of base and aggregated tables in Fabric.

Resources:

• Direct Lake Query Mode in Microsoft Fabric
• Managed Self Service BI in Microsoft Fabric
• Overview of Copilot for Power BI

 

Advantages

• All reporting data (Base + Aggregated) at one place for any reporting requirement.
• The data available in one place, modelled (e.g. Star Schema), key metrics/KPI’s created for ready access. Business users can get Data as a Service by dragging and dropping data elements into Power BI Canvas.
• Data available in near real time for access.
• Fabric also incorporates Power BI, and it has features such as Power BI Co-pilot and ChatGPT that work on their data, which will assist them further in providing Data as a Service.
• Improved data lineage with data from source to aggregation to Power BI all stored in one place, this will also enable better security policies to be applied.
• One Service and One Cost in Azure which is ‘MS Fabric.’

 

Disadvantages

• Semantic layer creation needs Data Replication from Snowflake to MS Fabric.
• An additional Platform layer to handle.

 

Cost Benefit Of Fabric Co-Existing with Snowflake Over 3 Years

Fabric as semantic layer between Snowflake & Power Bi not only helps in improving overall performance of reports and dashboard, it also helps in optimizing cost. Below is a Cost Benefit calculator which can be used to calculate indicative cost savings. Numbers provided in TCO calculator is just an example. Please ensure to enter relevant numbers as per your scenario. Expected cost savings can be between 30-40% and it varies from one implementation scenario to another.

 

Cost Benefit of Fabric Co-Existing with Snowflake Over 3 Years
	YEAR 1		YEAR 2		YEAR 3
	Cost Current Architecture (Snowflake + PBI) ($)/month	Cost Proposed Architecture (Snowflake + PBI + Fabric)          ($)/month	Cost Current Architecture (Snowflake + PBI)       ($)/month	Cost Proposed Architecture (Snowflake + PBI + Fabric)         ($)/month	Cost Current Architecture (Snowflake + PBI) ($)/month	Cost Proposed Architecture (Snowflake + PBI + Fabric)        ($)/month
Snowflake + AWS	$200,000	$150,000	$300,000	$150,000	$400,000	$200,000
AWS Egress Cost (PBI Import in Azure)	$10,000	$10,000	$10,000	$10,000	$10,000	$10,000
Power BI Cost in Azure	$44,000	$44,000	$57,200	$44,000	$74,360	$44,000
Fabric Cost in Azure	0	$8,400	$0	$8,400	$0	$8,400
Total Cost	$254,000	$212,400	$367,200	$212,400	$484,360	$262,400

 

Total 3 Year Cost with Current Architecture	$1,105,560
Total 3 Year Cost with Proposed Architecture	$687,200
Cost Benefit	$418,360
Cost Benefit (%)	38%

 

In the meantime, to try out Fabric, sign up for a free trial.