Comparing data between a semantic model and an on-prem cube

[Neka]

Hello, 

Not sure if this is the correct place to ask but I have a question:

Our company is migrating from on-premises SQL Server Analysis Services to Fabric Semantic Models. We are developing our first cube as a proof of concept and we want to automate data comparisons between the two environments to make sure our semantic model is working. What would be an effective architecture for this automation? Is it feasible to connect to both sources from Fabric Data Factory using notebooks? We have tried to do it through Power BI but have run into issues because we can't do any transformations as the connections are direct query and there is too much data in the cube to import. 

[v-yilong-msft · Accepted solution]

Hi @Neka ,

Here are some of my personal thoughts on your question:

1. Effective Architecture for Automating Data Comparisons

1). Use Fabric Data Factory to extract data from both the on-premises SSAS and the Fabric Semantic Model.
For on-premises SSAS, you can use the Self-Hosted Integration Runtime to securely connect and extract data.

2). Perform necessary transformations using Fabric Data Factory pipelines. This can include data cleaning, normalization, and formatting to ensure consistency between the two datasets.

3). Store the transformed data in a Lakehouse or Warehouse within Fabric.
Use Fabric Notebooks to write comparison scripts. These notebooks can leverage Spark or Pandas to perform detailed data comparisons, checking for discrepancies in measures, dimensions, and other key metrics.

2. Feasibility of Connecting to Both Sources from Fabric Data Factory Using Notebooks

Currently, Fabric Notebooks do not directly support connecting to on-premises databases through the Power BI Gateway. However, you can use a workaround by creating a Shortcut in Fabric Lakehouse for your external data. This allows you to query the data using notebooks.

You can look at this topic below:

Ingesting on premise data with notebooks - Microsoft Fabric Community

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yilong-msft · Accepted solution]

Hi @Neka ,

Here are some of my personal thoughts on your question:

1. Effective Architecture for Automating Data Comparisons

1). Use Fabric Data Factory to extract data from both the on-premises SSAS and the Fabric Semantic Model.
For on-premises SSAS, you can use the Self-Hosted Integration Runtime to securely connect and extract data.

2). Perform necessary transformations using Fabric Data Factory pipelines. This can include data cleaning, normalization, and formatting to ensure consistency between the two datasets.

3). Store the transformed data in a Lakehouse or Warehouse within Fabric.
Use Fabric Notebooks to write comparison scripts. These notebooks can leverage Spark or Pandas to perform detailed data comparisons, checking for discrepancies in measures, dimensions, and other key metrics.

2. Feasibility of Connecting to Both Sources from Fabric Data Factory Using Notebooks

Currently, Fabric Notebooks do not directly support connecting to on-premises databases through the Power BI Gateway. However, you can use a workaround by creating a Shortcut in Fabric Lakehouse for your external data. This allows you to query the data using notebooks.

You can look at this topic below:

Ingesting on premise data with notebooks - Microsoft Fabric Community

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Neka]

Thank you so much for your response, I really appreciate it. Even with a SHIR, how would you connect to SSAS in Fabric DF? We couldn't find any connector for SSAS or anything like it. 

[v-yilong-msft · Accepted solution]

Hi @Neka ,

I found a related topic, maybe it can help you.

You can read it according to your needs:

Solved: SSAS in Fabric - Microsoft Fabric Community

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.