Trying to compare Semantic Model to SQL Server Analysis Services

[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. 

Thank you for any advice.

[Anonymous · Accepted solution]

Hi, @Neka .I am glad to help you.

You might want to try using multiple model analysis tools to achieve this effect together.
Here is my understanding of your current needs
Your team is currently producing raw data for a multidimensional model. You realise that multidimensional models are not supported in Azure Analysis Services or Power BI Premium semantic models, and that Power BI generates semantic models based on default rules.
You are considering how to compare the original multidimensional model with the generated semantic model to determine which data analysis operations (code written) in the multidimensional model are supported in the semantic model (and can be run normally in the tabular model).

If my understanding is correct, I hope the following suggestions will help you.
1. Use Live connections to connect to your multidimensional model data source in Power BI Desktop, Power BI will convert the metadata of the multidimensional model to tabular format for use and display in Power BI, but the underlying data will remain in the multidimensional model (using real-time querying to access the data in the multidimensional model)
2. When the report is created, publish it to the power BI Service.
Use the XMLA feature. It supports you to remotely access your semantic model in Power BI Service in other software.

Semantic model connectivity and management with the XMLA endpoint in Power BI - Power BI | Microsoft...

You can use the ALM Toolkit to enable a comparison of the differences between models in Power BI Service and models in Power BI Desktop.
URL:
Analysis-Services/AlmToolkit/Semantic Model Comparison and Merging with ALM Toolkit.pdf at master · ...

If you want to implement a comparison between the multidimensional model and the Semantic model
You need to first save a copy of the multidimensional model and convert it to a tabular model
Using SSDT Tools SQL Server Data Tools
URL:
How to import data from SSAS Multi-dimensional to SSAS Tabular
 

Actually I think the most important thing is that you need to understand the difference between the multidimensional model and the semantic model (tabular model) at the modelling stage of the multidimensional model
Which codes are possible in the multidimensional model but not in Power BI (understand the differences and code limitations).
When you are aware of these limitations and consciously modify your modelling rules, you will be able to reduce a lot of cases where code does not work in Power BI.

I hope you find the following article helpful.
URL:

Comparing Analysis Services tabular and multidimensional models | Microsoft Learn

Considerations for migrating from a Multidimensional Model to a Tabular Model. | Datasturdy Consulti...
SQL Server Analysis Services: Multidimensional vs. Tabular Mode - A Comprehensive Guide
comparing 2 tabular models for differences - Microsoft Q&A
Choosing between Multidimensional and Tabular Models in SSAS | Pluralsight

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[loius0044 · Accepted solution]

Glitzyinfo @Comparing the Semantic Model to SQL Server Analysis Services (SSAS) involves examining their roles in data analysis and business intelligence. Both are designed to help organizations create structured, meaningful data representations for reporting and analysis, but they differ in architecture and usage. The Semantic Model in tools like Power BI allows for flexible, intuitive data modeling directly within BI platforms, simplifying data integration and supporting self-service analytics. It’s ideal for scenarios where users need fast, easy access to data insights without extensive IT involvement. On the other hand, SQL Server Analysis Services (SSAS) is a powerful, enterprise-grade tool that offers both Multidimensional and Tabular models.

[Anonymous · Accepted solution]

Hi, @Neka .I am glad to help you.

You might want to try using multiple model analysis tools to achieve this effect together.
Here is my understanding of your current needs
Your team is currently producing raw data for a multidimensional model. You realise that multidimensional models are not supported in Azure Analysis Services or Power BI Premium semantic models, and that Power BI generates semantic models based on default rules.
You are considering how to compare the original multidimensional model with the generated semantic model to determine which data analysis operations (code written) in the multidimensional model are supported in the semantic model (and can be run normally in the tabular model).

If my understanding is correct, I hope the following suggestions will help you.
1. Use Live connections to connect to your multidimensional model data source in Power BI Desktop, Power BI will convert the metadata of the multidimensional model to tabular format for use and display in Power BI, but the underlying data will remain in the multidimensional model (using real-time querying to access the data in the multidimensional model)
2. When the report is created, publish it to the power BI Service.
Use the XMLA feature. It supports you to remotely access your semantic model in Power BI Service in other software.

Semantic model connectivity and management with the XMLA endpoint in Power BI - Power BI | Microsoft...

You can use the ALM Toolkit to enable a comparison of the differences between models in Power BI Service and models in Power BI Desktop.
URL:
Analysis-Services/AlmToolkit/Semantic Model Comparison and Merging with ALM Toolkit.pdf at master · ...

If you want to implement a comparison between the multidimensional model and the Semantic model
You need to first save a copy of the multidimensional model and convert it to a tabular model
Using SSDT Tools SQL Server Data Tools
URL:
How to import data from SSAS Multi-dimensional to SSAS Tabular
 

Actually I think the most important thing is that you need to understand the difference between the multidimensional model and the semantic model (tabular model) at the modelling stage of the multidimensional model
Which codes are possible in the multidimensional model but not in Power BI (understand the differences and code limitations).
When you are aware of these limitations and consciously modify your modelling rules, you will be able to reduce a lot of cases where code does not work in Power BI.

I hope you find the following article helpful.
URL:

Comparing Analysis Services tabular and multidimensional models | Microsoft Learn

Considerations for migrating from a Multidimensional Model to a Tabular Model. | Datasturdy Consulti...
SQL Server Analysis Services: Multidimensional vs. Tabular Mode - A Comprehensive Guide
comparing 2 tabular models for differences - Microsoft Q&A
Choosing between Multidimensional and Tabular Models in SSAS | Pluralsight

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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