How to analyze Power BI Usage across workspaces

Workarounds…..workarounds…..workarounds. We all build workarounds as necessary until more enterprise-ready functionality is made available.

This is true for Power BI Usage metrics across workspaces.

We have usage models for each individual workspace but there are several issues with them.

1. you have to copy the model in order to edit it and make it useful
2. you are unable to share this model with non-admins or non-members of the workspace (why? – business owners want to see who is using the report, how often, etc.)
3. you have to make this modification in each and every workspace you want to measure usage against
4. you are unable to connect to them via XMLA

Therefore, you have to create a workaround. What are your options?

1. Office 365 audit logs solution
2. ?
3. wait for the Power BI group to implement cross-workspace usage analysis

What if you do not have access to the audit logs? What if you do not want to wait and require this information now.

Several groups will download the usage report pbix and export the data they need….too manual. So, I wanted to automate this task. How?

I use a combination of:

1. Analyze in Excel
2. PowerShell
3. Windows Task Scheduler
4. Power BI Dataflow
5. Power BI Data Model

* Note – this does not require Power BI Premium but does require you to be an admin of each workspace you would like to gather stats for.

Analyze in Excel

1. Connect to the Power BI Report Usage Metrics Model in the first of the respective Power BI Workspaces.
2. Create a Pivot Table with the following fields:
   1. ‘Reports'[ReportGuid]
   2. ‘Reports'[DisplayName]
   3. ‘Dates'[Date]
   4. ‘Users'[UserPrincipalName]
   5. ‘DistributionMethods'[Name]
   6. ‘Views'[ViewersCount]
   7. ‘Views'[ViewsCount]
3. Repeat steps 1 & 2 for each workspace you would like to gather stats for.
4. Copy the sheet from each respective Excel file to a main Excel file. This will in turn copy the connections as well.
5. Save the file to SharePoint on Office365 (or OneDrive).

PowerShell

Create a script to:

1. Download the Excel file w/ all Pivot Tables from SharePoint on Office365 (or OneDrive).
2. Refresh the Excel file.
3. Upload the Excel file back to SharePoint on Office365 (or OneDrive).

Windows Task Scheduler

Schedule the PowerShell task to run on whatever schedule you would like.

• Action = Start a program
• Program = powershell.exe
• Arguments = -ExecutionPolicy Bypass -File “<directory/path to PowerShell script>”
• Run as local administrator

Power BI Dataflow

Schedule a dataflow to pick up the data from the Excel file (cycle through tabs), aggregate the data, etc.

Power BI Data Model / Report

Create a report using the Power BI usage data to visualize the usage, join to other data (i.e. from Power BI API – report/dataset relationship, owner of the dataset, etc.).

Share access to the report, use it in other data models to show report-specific data in an individual report, etc. Do with it what you will as they say.

Conclusion

Because there are so many variable parts to this solution, I have held off on creating this as a solution on vizbp.com. As well, this is a temporary solution as the hope is that the Microsoft release for this functionality does not restrict it to Power BI Admins or Capacity Admins only and allows this report to be shared (or accessed via XMLA) by the organization.

If there is enough interest, I can look to create this as a template on vizbp.