How to automate a Power BI Data Dictionary
Whether you want to analyze an individual workspace or your full Power BI environment (all workspaces and solutions you are an admin of), you will find the Power BI Data Dictionary 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. The only other requirement is for the models to be documented – for the developers to enter table, column, and measure definitions when creating the model. Unfortunately, there is not much documentation regarding this specifically for Power BI – for Power Pivot and SSAS, there are plenty of references.
There are other solutions available for this functionality but most are only available for single models or SSAS solutions (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 capacities, workspaces, tables, columns, and measure DAX expressions. It also has all of the visuals built out to easily analyze your Power BI 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 data definitions.
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 understand and 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 and search all data model metadata in a single view.
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 Data Dictionary
References to other Power BI data dictionary solutions (my apologies if I did not reference yours – let me know if I failed to and I will add yours as well):