October 16, 2018

Steps to an Automated Power BI Audit Log Solution

Post by: Marcus Radue

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 Cmdlets

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 download
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.

Relevant Insights

Are You Falling into the Cloud Conversation Gap?

Most conversations about migrating services to the cloud focus on migration of application workloads and data. The vision is often...

How Aggregation Tables Improve Performance for Power BI Reports

In this blog, Marcus Radue, Solution Architect, offers high-level guidance in the advantages of aggregation tables in Power BI. For a...

Getting Started with Azure DevOps – Views

Like most Microsoft products, there are many ways to look at or perform the same activities. Azure DevOps is no...
X