Home Power BI Power BI Template to document the Power BI Service

Power BI Template to document the Power BI Service

by Prathy Kamasani

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 – http://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 🙂

 

You may also like

10 comments

Jordan 7 May 2019 - 6:32 pm

The idea is great but I can’t get past the RefToken query with this error:

AADSTS50158: External security challenge not satisfied.

Probably an authentication issue to my tenant – anything I can try or am stuck waiting on MSFT to put this into Graph?

Reply
smita 11 July 2018 - 8:00 pm

Hi Thanks for the interesting post. I tried it but keep getting an error for reftoken query:
Expression.Error: The field ‘access_token’ of the record wasn’t found.
Details:
error=invalid_request
error_description=AADSTS90019: No tenant-identifying information found in either the request or implied by any provided credentials.

Reply
PointZeroOne 14 March 2018 - 3:47 am

Hi, I’m trying to use this but I’m getting a message about invalid username or password.

Username/password is correct.

Expression.Error: The field ‘access_token’ of the record wasn’t found.
Details:
error=invalid_grant
error_description=AADSTS70002: Error validating credentials. AADSTS50126: Invalid username or password
Trace ID: 5dd7ab20-34c3-4b22-9887-d53632423500
Correlation ID: 77022373-1312-4f42-b53a-a20e0ea92be2
Timestamp: 2018-03-14 03:45:51Z
error_codes=List
timestamp=2018-03-14 03:45:51Z
trace_id=5dd7ab20-34c3-4b22-9887-d53632423500
correlation_id=77022373-1312-4f42-b53a-a20e0ea92be2

Reply
prathyusha kamasani 17 March 2018 - 12:37 pm

Hi, in user details file, you need to provide your username and password, probably it is still using the dummy credentials I have given in the file.

Reply
PointZeroOne 18 March 2018 - 12:30 am

Hi, no I’ve updated the files with the correct credentials/token, and am putting in the credentials that I used to log in to create the keys.

Reply
prathyusha kamasani 19 March 2018 - 10:01 am

Ok, can you please go to edit query mode and refresh RefToken query, see if you are getting any Ref Token or not. If that succeeds, try and refresh all queries in Edit Query mode.

Reply
Wes Baird 14 December 2017 - 11:40 am

I got this working for a Native app but not a Web API as defined in Azure Active Directory. The initial compile did not work so I went into Query Editor and reviewed each of the items that were in error and compiled them individually. That worked and now I have a working report. Thanks for the initial insight and work on this.

Reply
Wes Baird 20 March 2018 - 3:59 pm

I got this working for a Native app but not a Web API as defined in Azure Active Directory. The initial compile did not work so I went into Query Editor and reviewed each of the items that were in error and compiled them individually. That worked and now I have a working report. Thanks for the initial insight and work on this.

Reply
Ivan Bondarenko 6 December 2017 - 12:32 pm

It is a great idea, but I couldn’t make it work for myself. Can’t pass through authorisation, “Error validating credentials”, ” Password does not exist in store for this user”. Probably due to SSO authorisation configured in our tenant. Will wait until this info appears in Graph API.

Reply
Ivan Bondarenko 20 March 2018 - 3:59 pm

It is a great idea, but I couldn’t make it work for myself. Can’t pass through authorisation, “Error validating credentials”, ” Password does not exist in store for this user”. Probably due to SSO authorisation configured in our tenant. Will wait until this info appears in Graph API.

Reply

Leave a Reply