Build An Historical Database For Power BI Report’s Usage Metrics Using Power Automate

Use ELT Data Integration Process to Analyze Your Power BI Report’s Usage Data Using the Combined Power of Power Automate & Power BI

Photo by Agence Olloweb on Unsplash

As a Data Analyst, you have completed the marathon of requirement gathering, building data models and churning out insights into a engaging Power BI report 💪. You demo this report to your stakeholders and they are amazed by the report’s content and its functionality and have promised to incorporate this report into their daily decision making process. But how do you know if they are really going to use it? 🤔 (Because if they don’t, then all your effort is gone down the drain).

The Problem With The Inbuilt Usage Tracker

Of Course, Power BI has an out of the box usage tracking report, but with one tiny limitation — it just reports last 90 days worth of data.

Power BI’s Inbuilt Usage Tracker

What Sparked This Idea?

I wanted to do a MoM% and a QoQ% analysis on one of my Power BI report’s usage data as well as enrich this data with a few more details. To my surprise, when I opened the usage metrics report, it didn’t have data beyond last 90 days.

I knew that the inbuilt usage tracker was storing data somewhere in a semantic model. What if I could extract this data everyday, store it in a repository of my own and finally build a custom usage report out of it?

One way to achieve this is to manually export the data — which would mean, visit the usage metrics report everyday, extract the data, clean and deduplicate it and store it in a Excel file. But thanks to Power Automate, I could forgo this tedious manual process and build an automated pipeline to achieve this task.

Solution Overview

Here is wireframe of how I built a historical database of usage metrics using ELT process. It uses Power Automate to extract data from the semantic model and load into a SharePoint file. This data is then further imported and transformed and enriched in Power BI to build the custom usage metrics report.

Flowchart

In the next section, I will provide a detailed walkthrough on how this can be technically achieved using all the mentioned tools.

Steps In Building The Power Automate Workflow

Step 1 : Identify The DAX Query to Extract Data

• To Identify the data source(Semantic Model) of the inbuilt usage metrics report, navigate to the report’s workspace, click on 3 dots next to the report name and click on “View usage metrics report”.
• We will “Save a copy” of this report in order to edit it and view the underlying dataset.

PBI inbuilt usage metrics report

• The objective here is to identify the DAX query populating the different charts in this report. Since we are unable to download this report, we will try to recreate it through Power BI Desktop.
• In PBI Desktop, click on “Get data” → “Power BI semantic models”
• In the window that appears, you would be able to connect to the Usage Metrics Report Semantic Model of the respective workspace.
• Using this model, create a table visual with these columns → ‘Dates’[Date], ‘Users’[UniqueUser], ‘Report views’[ConsumptionMethod] and ‘Model measures’[Report views]
• Add a filter to this page to show data for just the report that you are looking for. I have used the ‘Reports’[ReportGuid] column to filter out my report.
• On the top menu, click on “Performance analyzer” and “Start recording”. “Refresh visuals” so that the DAX query that populates this visual is generated. Copy this query.

Performace analyzer in Power BI Desktop

Step 2 : Extract Data Using Power Automate

• Create a Scheduled Cloud Flow in Power Automate. Provide a suitable name and the recurring time for this flow to run.

Power Automate

• Add a node “Run a query against a dataset”. Select the workspace and the data set would be “Usage Metrics Report”. Finally, paste the DAX query copied in step1 in this the “Query text” section.

Configuration of Run a query against a dataset node

• At this moment if you test this flow, it will fetch the dataset in a JSON format. In the upcoming steps, we will parse this JSON data and store it in SharePoint

Step 3 : Create a SharePoint File to Store Data

• Create a new Excel file in a SharePoint location.
• Insert a table with these headers : Report_views, Date, UniqueUser and ConsumptionMethod.
• Return to your Power Automate workflow and add a node “List rows present in the table” and configure it to retrieve rows from the SharePoint file you created.
• For the first run, this node will output empty rows, but for all subsequent runs, this node will fetch all the rows added in the previous runs.
• Add a node to initialize a string variable. This variable will hold the combination of “Date” and “UniqueUser” for each row in the later steps.

Initial 4 nodes in this workflow

Step 4 : Identify Records Which Already Exist in the SharePoint File

• Add a conditional node to check if there are any rows present in the SharePoint file. As mentioned in the previous step, the file will be empty in the first run but will have records in all the subsequent runs.
• Configure this conditional node with a dynamic expression as follows: length(outputs(‘List_rows_present_in_a_table’)?[‘body/value’]) > 0

Condition — If rows exist

• If the condition is met, i.e. if rows exists, then the “List rows present in a table” will output the data in a JSON format. Its now time to parse this JSON.
• Add a “Parse JSON” node and configure its properties. For the “Content” property, insert dynamic content and select “body/value” under “List rows present in a table”.
• You would also need to input the schema of the JSON data.

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "@@odata.etag": {
                "type": "string"
            },
            "ItemInternalId": {
                "type": "string"
            },
            "Report_Page": {
                "type": "string"
            },
            "Report_views": {
                "type": "string"
            },
            "Date": {
                "type": "string"
            },
            "UniqueUser": {
                "type": "string"
            },
            "ConsumptionMethod": {
                "type": "string"
            }
        },
        "required": [
            "@@odata.etag",
            "ItemInternalId",
            "Report_Page",
            "Report_views",
            "Date",
            "UniqueUser",
            "ConsumptionMethod"
        ]
    }
}

• This will return an array of rows. We will loop through each row, and save the combination of “Date”, “UniqueUser” & “ConsumptionMethod” column into the string variable which we had initialized in Step 3.

Loop through each row which exist in SharePoint file

Append to string variable — concatenation of UniqueUser, Date and ConsumptionMethod columns

//Insert this expression into the Value field of "Append to string variable"
concat(item()['UniqueUser'],'_', item()['Date'],'_', item()['ConsumptionMethod'])

• This string str_key now consists of unique keys for each record and will help us identify the records which already exist in SharePoint in all future runs.

Process so far — until Step 4

Step 5 : Identify Records That Need to be Added Into SharePoint File

• When we configure this flow to run on a schedule, at each run, “Run a query against this dataset” will fetch all the last 90 days of data.
• From this data, we need to eliminate the already existing records previously loaded into SharePoint file , and just append the new records to it.
• Add a Parse JSON node to the above process flow which will parse records from the very first “Run a query against a dataset”. For the “Content” property add a dynamic content and select “First table rows” under “Run a query against a dataset”. Also input the schema.

Content property

• Here is my JSON schema. You may customize it based on your input

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "[IsGrandTotalRowTotal]": {
                "type": "boolean"
            },
            "[Report_views]": {
                "type": "integer"
            },
            "Dates[Date]": {
                "type": "string"
            },
            "Users[UniqueUser]": {
                "type": "string"
            },
            "Report views[ConsumptionMethod]": {
                "type": "string"
            }
        },
        "required": [
            "[IsGrandTotalRowTotal]",
            "[Report_views]"
        ]
    }
}

• After parsing this JSON, it will return all the records present in the SharePoint in the form of an array.
• We will loop through each of the row in the array, and check if the combination of Data,UniqueUser and ConsumptionMethod already exists in the previously created string variable.
• Here are the detailed steps:
• Add a “Apply to each” step to loop through body of the parsed JSON above. Basically this will loop through each of the row in the parsed JSON body.
• Add another “Parse JSON” step to extract the column values of the current row in iteration. Name this steps as “Parse JSON — Extract Columns”. The “Content” property of this step is the Body of “Apply to each” node above. The JSON schema is as follows:

{
    "type": "object",
    "properties": {
        "Dates[Date]": {
            "type": "string"
        },
        "Users[UniqueUser]": {
            "type": "string"
        },
        "Report views[ConsumptionMethod]": {
            "type": "string"
        },
        "[IsGrandTotalRowTotal]": {
            "type": "boolean"
        },
        "[Report_views]": {
            "type": "integer"
        }
    }
}

• Add a “Condition” step to check if this current row in the loop’s iteration already exist in the previously created string variable str_key. (In step #4, this string variable is already populated with the combination of Date, UniqueUser & ConsumptionMethod column values for each row in the SharePoint file. Eg. “2025–06–01_user1_Embedded, 2025–06–01_user2_PowerBI.com” etc. )

This conditional step checks if the current iteration row exists in the string variable

str_key contains concat(body(‘Parse_JSON_-_Extract_Columns’)?[‘Users[UniqueUser]’],’_’,body(‘Parse_JSON_-_Extract_Columns’)?[‘Dates[Date]’],’_’, body(‘Parse_JSON_-_Extract_Columns’)?[‘Report views[ConsumptionMethod]’])

• If the condition is true, then do noting, skip this iteration and go to the next row.
• If the condition is false, it means the current row in the iteration is a new row which didn't exist previously in the SharePoint file. It now time to append this new record into the file.

Step 6 : Load/Store The Extracted Data Into the SharePoint File

• Under the “False” branch insert this step — “Add a row into a table”.
• Configure its properties so that the row is appended to the same Excel file in the SharePoint that we were referring to all this while.

Configure properties of “Add a row into a table” step

• The “Advanced parameters” property will list the column headers of the SharePoint Excel file. We just need to map them to the data.
• Since we have already parsed the column values in the above “Parse JSON — Extract Columns” step, we can now easily map them in this step. To do so, under each column, insert dynamic content .

Mapping columns to its data

We have now reached the end of the flow. We can now test, save and schedule it. Here is how the entire flow will look:

Conclusion:

Until now, we have completed the Extract and Load steps, its now time to move to Power BI and Transform this data. We can now import this SharePoint data into Power BI, clean and enrich it using Power Query, and build our custom report. This completes the entire Extract — Load — Transform (ELT) process automation.

Once the report is ready, we can publish it to Power BI service and schedule the data refresh, add alerts for your data, schedule a copy of this report to be delivered to you and your manager’s inbox.

Note: In this workflow, at a few places I have added a condition to check if this is the first run or a subsequent run. This was done assuming that the SharePoint Excel is empty when we run this flow for the first time. However, you can skip this conditional check by add a dummy row to the SharePoint Excel file. This would force the workflow to always follow one particular path, irrespective of first or subsequent runs. This would make the workflow even more compact and precise by elimination these conditional nodes. Do give it a try!