How to analyze your Power BI data model size
Whether you want to analyze an individual solution or your full Power BI environment (all workspaces and solutions you are an admin of), you will find the Power BI Model Analyzer solution invaluable. 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 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 all of your solutions? What if you want to review solutions you did not author but are published in your environment? You would have to develop a solution like this from scratch or analyze each of the solutions one at a time. You still can – 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 do so. 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, object dependencies, relationships, compression, and encoding. It also has all of the metrics and visuals built out to easily analyze your Power BI environment.
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 all workspaces of which you are an admin and the underlying datasets. It then queries each one utilizing DMV functions to extract the dataset components (tables, columns, measures, relationships and dependencies, compression metadata, encoding metadata, 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 – not even mentioning the documentation that is required to make a product presentation ready for sales.
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 see all of my solutions I have access to.
- There has to be a way to easily filter to find all solutions by owner ( in this case, the person who imported/created the dataset).
- There has to be a way to see all data model sizes in a single view.
- There has to be a way to proactively monitor solutions published to watch for solutions not utilizing best practices or over a specified size.
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