When the new Power BI service activity logging API was announced over the holidays, I was intrigued and anxious to start working with it. I’ve had some experience with report usage monitoring using the existing Office logs and usage metric reports that do provide some useful information but can be a chore to use. Activity monitoring and troubleshooting with the new logging API is focused entirely on Power BI tenant events like dashboard, interactive and paginated reports views, deployments, errors and data refresh events. This should be easier than before, enabling admins to be more proactive by tracking usage patterns. In a short series of blog posts, I’ll demonstrate how to build a complete activity logging and reporting solution for your entire Power BI tenant. In this first post of the series, we’ll just get started with the basics by capturing just a few log events for a brief window of activity and surface them in a simple report.
Before we get started,
a brief history lesson is on order:
When the Power BI cloud-based service was initially offered back in 2013 as a feature extension to Office 365, it used SharePoint Online as the storage architecture. Some additional logging events were added to the existing and numerous Office 365 events catalog. It has always been cumbersome to find relevant reporting information amid all of the other noise in these logs. Components of the Power BI back-end services have since been migrated into a more specialized service infrastructure but the activity logging has remained in Office 365 until December of 2019. The Office logs required special privileges within the Office tenant and produced volumes of event data related only to folders, files and Office documents.
The new Power BI Activity Log API is specially-suited and optimized just for Power BI. By contrast, it will be much easier to identity and track relevant service and user activity for workspaces, apps, dashboards, interactive Power BI reports and paginated reports in the Power BI service.
I envision that my production-scale logging solution will use an orchestration tool like Azure Data Factory to iterate over historical activity logs, store log files to Azure Data Lake Storage and then incrementally update a Power BI data model for reporting and analysis. This first example will use PowerShell script manually executed from my desktop.
PowerShell Me, Baby
The new Get-PowerBIActivityEvent commandlet is added to the Microsoft Power BI Management library. Install the latest version to gain access to the activity logs.
In my project, the first step is to open and run the PowerShell ISE as a local administrator. To installed the latest Power BI Management library locally, I execute this code :
Install-Module -Name MicrosoftPowerBIMgmt.Admin
I need to sign-in to my Power BI tenant with a service or user account that has tenant admin privileges. This code opens a standard login dialog and prompts for a user name and password, populating a Credential type object variable used to open a connection to the Power BI service:
$Cred = Get-Credential
Connect-PowerBIServiceAccount -Credential $Cred
In the Power BI product team’s Developer blog, Senior Program Manager Kay Unkroth explains that the Get-PowerBIActivityEvent commandlet can be called with date/time parameters to include only one day at a time. This line requests all activity on Junuary 5th, 2020, caching the activity log information as a JSON structure:
$activities = Get-PowerBIActivityEvent -StartDateTime ‘2020-01-05T00:00:00.000’ -EndDateTime ‘2020-01-05T23:59:59.999’
Finally, I write the log contents to a local file using this code:
$activities | Out-File -FilePath C:\Temp\RSActivityLog.json
Importing this file into Power BI Desktop produces the following simple table:
A couple of important things to point out
The API is optimized to handle large numbers of events. As such, it is limited to return records for a range of time up to one full day using the StartDateTime and EndDateTime parameters. The web service returns a continuation token return parameter to let you know if there is more data beyond a fixed frame size that will typically return about 5,000 to 10,000 records.
Incidentally, I’ve played with a few different file formats. JSON is by far the most flexible format but you may not get all the key/values you want just by consuming the results right out-of-the-box and without parsing all the nested levels. In Kay’s article, he uses the ConvertTo-JSON directive to flatten the native JSON document into a more conventional colon-delineated log flat file with named key/value pairs. Using this method, I was able to get more field information that those that apparently slipped through the cracks from the JSON document. Although, I had to massage the output a bit and then transform the rows into columns using some fancy pivot dancing in Power Query.
This simple report
is a first effort showing records for a short time frame. I’ve produced a single table with some recognizable grouping and slicing attributes but we can do considerably more. Using these fields, I analyze activities for only dashboard , Power BI or paginated report views. We can filter by user, object type, operation type, the web browser or devices used to view content, type of workload or the containing workspace.
In a fully-fleshed-out data model, some of the attributes might exist as separate dimension/lookup tables. But, this is enough for now.
Post Conclusion
Please share your questions and your own experience with the activity logging API, and watch for subsequent posts about tenant monitoring and activity management solutions.