Recently, our data team noticed a troubling pattern with clients who were looking to deploy Power BI throughout their organizations: they were experiencing limited visibility to all Power BI activities, associates, and licensing. Clients would ask us, “How am I able to efficiently manage and administer my Power BI environment without being able to access any of the underlying detail behind it all?”
To answer that question, we made a repeatable and seamless solution that can be deployed in any environment. This solution involves the use of PowerShell cmdlets to retrieve Office 365 audit log data, staging that data in an Azure SQL database through Azure Data Factory, and displaying that data in a Power BI report and dashboard.
The Benefits of Unlocking Your Power BI Activity Insights
With insights into your Power BI activity, you can:
- Measure your overall Power BI adoption over time (number of users, usage by department/location, etc.)
- Monitor activities with potential risk for data loss (print, sharing, etc.)
- Celebrate successes across organization (leaderboards for top dashboards, top reports, etc.)
- Effectively manage Power BI licenses (Pro-licensed user activity)
- Improve end user support
Steps to an Automated Audit Log Solution
PowerShell can be intimidating for any data analytics developer or business analyst who may not use it on a regular basis. PowerShell is often used by your Office 365 administrator so there’s usually not a need for the data developers or analysts to involve themselves in this area.
However, Microsoft has provided several PowerShell cmdlets that can be simply installed on your local machine, assuming you have the correct administrative rights. Once the software and cmdlets have been installed, you can begin to retrieve the O365 audit information you desire.
PowerShell cmdlets for Power BI administration
PowerShell cmdlet documentation
Azure SQL Database and Data Factory
Once the audit log detail has been extracted via PowerShell and stored, we recommend massaging that data through some sort of ETL process before bringing into a Power BI report. Our solution involves storing the audit log data in .csv files and landing it into an Azure Blob storage container. From there the data is transformed through an Azure Data Factory solution and ultimately landed in an Azure SQL database. We recommend this method as a low cost, cutting edge solution. (More information on Azure pricing can be found here.)
Power BI Reporting
After massaging the data through Azure Data Factory or another Microsoft ETL tool of your choosing, we are now ready to bring this into a Power BI report. Simply connecting to your Azure or On-premises SQL database will allow you to access the O365 Power BI audit log data.
Power BI Desktop Download (if you are on Windows 10 download directly from the App Store)
Automated Audit Log Solution in Action
Now let’s see how the previous steps combine to answer that FAQ from before: “How am I able to efficiently manage and administer my Power BI environment without being able to access any of the underlying detail behind it all?”
If you like what you see in this blog post and want to learn more about you can benefit from automated data solutions like this, feel free to contact us.