How to compare Power BI Models
Want to compare models to document improvement? Want to compare your DEV/QA model with your model in PRODUCTION? Want to know the model size improvement of modifications made to a model? The ability to compare and analyze models, table, column sizing, and relationships allows you to show the improvements made to a model via optimization techniques. Enter the Power BI Model Comparison solution @ vizbp.
YOU DO NOT HAVE TO BE AN ADMIN with this product – just an admin of the Premium workspaces you want to analyze!
Premium is required currently until XMLA connectivity is opened up to non-premium workspaces.
There are other solutions available for portions of this functionality but most are only available for single models (I will reference these at the end of the post).
What do you do if you want to document or analyze improvements made to your solution? With existing solutions, you have to download each of your pbix files you would like to compare, run analysis on each one individually, and manually document the sizes, comparison, and improvements. You still can perform this analysis – don’t get me wrong. The value in this product just gives you a huge head start on that development and costs much less than the hours it would take to develop a product like this from scratch or to manually document improvements. It even has some capabilities that the other solutions do not.
It is a fully functional data model – joining all Power BI models and DMV queries to be able to see data model size, table size, column size, measure DAX expressions, relationships, etc.. It also has all of the metrics and visuals built out to easily analyze your Power BI solutions.
If you want to connect to a single model, there are a lot of options (I will list those at the bottom of the post). I even built a version that would find the open pbix port via R and query that single model. This works great during development but not when wanting to analyze your full environment.
It utilizes the Power BI API to query your Power BI environment for the workspaces of which you are an admin and the underlying datasets you specify. It then queries each one utilizing DMV functions to extract the dataset components (tables, columns, measures, relationships, etc.).
If you want to connect to the Power BI API and build this solution yourself, you very well can. You just need to create an Azure Active Directory Application and download some freely available connectors and/or utilize various methods to skirt around the OAuth2 authentication protocol (I will include links below) and you can begin connecting to your Power BI environment. In order to build reporting, you have to do some hi-jinks and jump through some hurdles (and be pretty good at M) in order to build out a functioning model.
- Power BI REST APIs
- Register an Azure AD application to use with Power BI
You also have to know DMV queries and how to join them all together:
- The Vertipaq Engine in DAX
- Dynamic Management Views (DMVs)
- SSAS: Using DMV Queries to get Cube Metadata
- SSAS DMV (Dynamic Management View)
But, believe me, it takes dozens to hundreds of hours to build out a solution like this.
If you have the time, I encourage you to go for it. It is an experience building something like this that is so valuable to an organization or to the individuals like me. I want my Power BI solutions and environment (Pro or Premium) to be fully optimized. I thought:
- There has to be a way to connect to the solutions I have access to.
- There has to be a way to pull two data models in a single view.
- There has to be a way to compare those models to document improvements to data model size, changes to measures, reduction in the number of columns or tables, etc.
I have built a product that does these things and more!
Take a look at the product and see if it would be valuable for you or your organization. It is available on vizbp.com:
While there, take a look at other solutions people like me are making available for free or for purchase. Take a look at becoming a seller yourself and offering your solutions in the marketplace.
While here, take a look at my other articles related to vizbp.com:
- Why I started vizbp…..
- Why buy from vizbp?
- Why sell with vizbp?
- Product Information for Power BI Model Analyzer
References to other Power BI model analyzer solutions (my apologies if I did not reference yours – let me know if I failed to and I will add yours as well):
- SQLBI – Vertipaq Analyzer
- RADACAD – Power BI Helper
- fourmoo – XMLA for PBIX
- Data Savvy – Documenting your Tabular or Power BI Model
- Kasper on BI – New SSAS memory usage report using Power BI
- Insight Quest – Tabular Model Schema Reference v2.0
- TheBIccountant – Analyse your memory consumption in PowerBI
- Azure Data Ninjago – Building Power BI Memory Usage Dashboard using DMV
- SQLShack – Automate documentation of SQL Server Analysis Server Tabular Model
- Other tools