Power BI Usage Metrics with the help of Power Automate - PBI Analytics

Power BI Usage Metrics Analytics 

Metrics play vital role in organization as it gives real-time and forecasted analysis for business players to take decision. These decision help the organization to grow and prosper in the market.

Most of these metrics are build using BI tools available in market such as Power BI, Tableau, Spotfire ..etc. Today I am going to take Power BI as an example and discuss further on it. Typically any organization will have 100's of reports created for various analytical analysis which business is looking for and they will be adding 100 more as and when new request comes. And there will many analyst and business stakeholders how will be using them proactively and some rarely. 

What about user analytics of these users and report analytics of all the reports that are being used? These analytics will help the IT team to priorities the current user base, priorities the reports eco system and also will give insight to the IT team about how the end product, the reports are been utilized within the organization and they can improvise and put efforts in right direction that would help the organization in achieving its goals.

Microsoft has provided this feature to monitor the usage metrics, but you need to have following privileges to access this feature 

• You need a Power BI Pro or Premium Per User (PPU) license to run and access the usage metrics data.
• To access usage metrics for a particular dashboard or report, you must have edit access to that dashboard or report.
• Your Power BI admin has to have enabled usage metrics for content creators

If you got all these prerequisites then you can follow the below Microsoft link and follow the steps included here. 

But there are limitation in the above method 

• Report usage metrics access is read only, the only way you can customize is save as the report and customize it later.
• Power bi pro license is required to view the usage metrics 
• Only 90 days of usage data is preserved and the report refreshed over night so it taken 24 hour for next refresh.
• Users will not be able to see the usage metrics across the workspace 
• Members with view access cannot see the usage metrics for Power BI reports and dashboards. 
• These usage metrics are limited to a single dashboard/report level or a workspace level only 

So we will discuss one of the way to access the metrics at organization level and also at user level using the following stack. We will be accessing the metrics from office 365 REST API with these stack. 

• Power Automate
• Sql Server
• Power BI 

Office 365 REST API will give several information across Microsoft apps like Outlook, Power BI, Azure .. etc. More details of what information can be extracted from the API endpoint can be get from this link . So we would extract only audit information related to Power BI. Below Microsoft documentation explains about the REST API in detail(link)

Below are steps how you can get response from REST API and store it in a table and build a Power BI report to show analytics on report usage and user analytics using Power Automate.

We will use Power Automate to access the REST API and to store the data in SQL server table.

    1. Authenticate and get the Access Token for next next API call, store it in a             variable to be used later for various API calls 

    2. We need to start the subscription so that we can get all audit                               related information from the REST API.

    3. When you hit the API with get request there are certain things that you need to take care off

• We need to pass two date arguments to the API, Start and End date and the API response will only return audit link for 24 hours, that means difference between start and end date should not be greater than 24 hours.
• The API response will be link for each event that happened inside organization in different Microsoft managed platforms. To get the details we would need to create a GET call to these API event endpoint

        Below is the sample API GET request call to retrieve audit information from managed API.

https://manage.office.com/api/v1.0/44ba1918-87d2-44a5-bc1d/activity/feed/subscriptions/content?contentType=Audit.General&startTime=2021-05-27T21:52:09&endTime=2021-05-28T21:52:09

        The API response sample is shown below

  {

    "contentUri": "https://manage.office.com/api/v1.0/xxxxxxxx-87d2-44a5-bc1d-/activity/feed/audit/20210527221403493000531$20210527222044060000197$audit_general$Audit_General$na0018",

    "contentId": "20210527221403493000531$20210527222044060000197$audit_general$Audit_General$na0018",

    "contentType": "Audit.General",

    "contentCreated": "2021-05-27T22:20:44.060Z",

    "contentExpiration": "2021-06-10T22:14:03.493Z"

  }

        We need to loop in all these API link one by to get the details of each event and filter out Power BI event from the response and then later store the                response to a SQL Server table 

. 

    4. Using the data stored in the above table you can draw various analytics on power bi report, users and all its objects that will give more detail                        information on usage of power bi within organization.

     5. We can even automate the dataset used for this PBI report in power automate in same flow by adding a refresh dataset task after the table is             refreshed. This would keep the report dataset updated Realtime for the                 report.

    6. We need to stop the subscription which we had started earlier to read the audit data from management API.

Below is the layout of complete flow which is designed to bring audit data from office management API into a sql server table.

This is one way of orchestrating the process same can be even done in PowerShell, Python and other platforms too using the same Office 365 Management REST API, here in this approach I have used Microsoft Power Automate to build this flow to read audit data from the API.

Hope this would be useful to any of the readers who are trying the achieve similar results.