Extending the Power BI Usage Metrics Template

The Power BI Usage Metrics Solution Template is now the primary or “Plan A” approach for monitoring Power BI activities (i.e. create, delete, share) and analyzing usage patterns and trends. Prior to the solution template, the best option for tenant-wide monitoring has been to build an ETL process based off the Office 365 Audit Log data, a database for storing the log data, and finally build the calculations and report visuals on top of this database. The following Guy in a Cube video gives a solid introduction to the template including expected costs.

However, as robust as the usage metrics solution template is in terms of exposing Power BI activities (e.g. 11 rich Power BI report pages), it can be even more valuable when you consider leveraging the underlying architecture rather than just the pre-built Power BI report. This customization could be as minimal as adding a few DAX measures or visuals to the PBIX file included with the solution template or maybe creating a new Power BI report which utilizes the solution template’s PBIX file as a source dataset via live connection.

Alternatives to Power BI Usage Metrics Report (.PBIX)

More broadly, the Azure SQL Database provisioned to store the Power BI usage metrics data could become a source for reports in other tools or possibly enhanced with other data sources such as Active Directory or HR data. For example, many IT/BI administrators responsible for analyzing Power BI usage may prefer to write T-SQL queries in SQL Server Management Studio (SSMS):

The adtlog.20_PowerBI table within the pbiTelemetry database is the source of the Power BI Log Data query and table included in the Power BI Desktop file provided by the solution template. It contains the core attributes necessary for building new usage/monitoring reports such as UserID, ReportName, DataConnectivityMode, WorkspaceName, etc.

Assuming you have the firewall rule set to allow your client’s IP address and can authenticate to the database via SQL Server database credentials or Azure Active Directory, you could easily build a new monitoring/usage report in Excel per the following image:

Maybe you want operational, paginated reports (SSRS reports) on top your Power BI usage data. If that’s the case, you could create a shared data source for the Azure SQL database in a Report Server Project in SQL Server Data Tools (SSDT) per the following image:

Not so fast…

Before you decide to branch off the Azure SQL database into your own monitoring/usage reports, you should note the significant customization built into the Power BI desktop file provided via the template. This includes the columns created in Power Query/M, the columns created via DAX, and the many DAX measures as well.

Depending on what you need to build with the Power BI log data, you may need to apply all or some of the same logic implemented in the Power BI Desktop file.

It would’ve been nice if this logic was built into the back end of the solution template (included in the SQL database) rather than within Power BI Desktop. Additionally, I question certain elements of the solution such as the conditional columns created via DAX. Nonetheless, I’ve been very pleased with the template thus far and would strongly recommend it if you need greater visibility to Power BI activities/usage. 

Wrapping Up

The Power BI Usage Metrics Solution Template doesn’t tie you to a static set of reports, a particular BI tool (PBI), or even a single dataset. It makes it very easy to get started quickly with its pre-built Power BI reports but if/when the need arises you have many good options for modifying or extending Power BI monitoring on your terms.