Back in September, I promised I would write another blog post with a beautiful looking Power BI template. Even though I did the model long ago, I did not get a chance to write a post until recently one of my blog readers reminded me. Thanks, Martin 🙂

Quick Recap:

As I mentioned in my previous blog post Calling Power BI API using Power BI Desktop to document Power BI Service, we can get a lot of metadata from Power BI service. I feel like, it is essential to have an overview of what’s happening on your tenant. The only place I know to get this information is Power BI API. I used Power BI API a data source in Power BI, I needed a bearer token to get any data from Power BI Service, so I used “https://login.windows.net/common/oauth2/token” URL to get the token. Which mean I looked at two different URL’s which restricted me to manual refresh in Power BI Desktop than having an automated schedule. Hopefully, it will change shortly.

You can use this template to answer many questions like:

• Show me all dashboards in my organization
• Total number of reports in the organization
• Users who have admin rights
• Number of refreshes
• Number of failures
• Why some refresh failed
• When was the last time a certain dataset refreshed, if it’s failed why
• And many more…

Template to Document the Power BI Service:

You can download the template here – https://1drv.ms/u/s!Avm7gbgZtlMlqGprbKHS6zHd0aPf

Above download link has a zip file with PBIT file and a CSV file. CSV file has user details required for authentication. This blog post covers the approach I used to call the API – https://powerbi.microsoft.com/en-us/documentation/powerbi-developer-register-app/.Once you updated “UserDetails.CSV” file with essential information, save it to your preferred location.

When you open the template, you will see a pop message asking Full path of the UserDetails file, e.g. C:\Users\xxxx\OneDrive\xxx\PBI Portal\PBI\UserDetails.csv. Enter file location and click on the Load button. That’s all; it should just work. Sometimes, you may get a timeout error. Most of the times refreshing again will do the trick if not go to edit queries and choose “Refresh All queries”.

The first page has drill through functionality enabled where it shows the lil driller icon. Are you impressed with above screenshots, want to know how I designed this model? Find it here – https://wp.me/p5u6Pw-AQ 

Summary

Remember, you can see all data points only if you have enough rights and level of access is maintained through Azure App. Also, you can only retrieve the data from workspaces where the username you used to call API has the access. This Power BI model gives exposure to a lot of information but not all. Using this model, I can find all users who have access to a Workspace but not to Power BI Apps. If you want to know all users on your tenant, use Graph API as I showed here – https://prathy.com/2017/11/find-power-bi-users-organisation/. Ideally, I would love to see an OData compatible API like Microsoft Graph API so that I can schedule the refresh. Also, I don’t think we have an endpoint to get Power BI Apps or username of data source connections. Power BI hides Username in data source connections, so this would be something handy to understand which username we used to set up the data source. You may have noticed many placeholders, I would like to add much more information to it, and design reports a bit nicer.  If I make any changes to the template, I will keep this post updated or write another with changes. Hope this helps someone out there! Thanks for reading 🙂

Keep smiling,

Prathy 🙂