Monitoring Power BI Dataset Refresh Memory And CPU Usage With Log Analytics

Fabric, Log Analytics, Power BI, Refresh 1 Minute

Maybe the fourth- or fifth-most exciting Power BI-related announcement last month (admittedly it was an exciting month) was that Log Analytics for Power BI datasets is now GA and you can now link multiple Power BI workspaces to a single Log Analytics workspace. This, for me, means that enabling Log Analytics has gone from being useful to essential for anyone interested in monitoring Analysis Services engine activity in an enterprise Power BI/Fabric deployment. It also works with Direct Lake datasets too!

Anyway, following on from my recent posts on measuring memory and CPU usage during dataset refresh, both for the refresh as a whole and for individual partitions, here are some KQL queries that allow you to extract that information from Log Analytics.

Here’s the KQL query to get the memory and CPU usage for a refresh (see this post for details):

PowerBIDatasetsWorkspace
| where OperationName == "CommandEnd"
| parse-where EventText with * '[PeakMemory: ' PeakMemoryKB:long ' KB, MashupCPUTime: ' MashupCPUTimeMs:long ' ms, MashupPeakMemory: '  MashupPeakMemoryKB:long ' KB]'
| extend DatasetName=ArtifactName
| project TimeGenerated, CpuTimeMs, DurationMs, MashupCPUTimeMs, MashupPeakMemoryKB, PeakMemoryKB, XmlaRequestId, DatasetName, PowerBIWorkspaceName

And here’s the KQL query to get the memory and CPU usage for individual partitions/Power Query queries (see this post for details):

PowerBIDatasetsWorkspace
| where OperationName == "ProgressReportEnd"
| parse-where EventText with * '[MashupCPUTime: ' MashupCPUTimeMs:long ' ms, MashupPeakMemory: '  MashupPeakMemoryKB:long ' KB]'
| extend Table=split(XmlaObjectPath,'.').[2], Partition=split(XmlaObjectPath,'.').[3]
| extend DatasetName=ArtifactName
| project TimeGenerated, Table, Partition, CpuTimeMs, DurationMs, MashupCPUTimeMs, MashupPeakMemoryKB, XmlaRequestId, DatasetName, PowerBIWorkspaceName

You could use these queries in a Power BI DirectQuery dataset to report on all your refreshes, similar to what I did in this series of posts last year. The XmlaRequestId columns contains the unique identifier for the refresh operation so you could build a one-to-many relationship between the table with the refresh-level data and the table with the partition-level data. Maybe I’ll get round to building that report sometime…

[Thanks to Jast Lu, who wrote the original version of these queries]

Published by Chris Webb

My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.

Published

3 thoughts on “Monitoring Power BI Dataset Refresh Memory And CPU Usage With Log Analytics”

  1. Pingback: Monitoring Datasets with Log Analytics for Power BI – Curated SQL

  2. Hi Chris

    I’m starting with Log Analytics over Power BI

    I discovered Operation[XmlaRequestId] can be the context/parent-id for each DAX queries
    (QueryEnd … Error)
    Reason2 = CALCULATE(MIN(Operation[EventText]),Operation[Operation]=”Error”,
    ALLEXCEPT(Operation,Operation[XmlaRequestId]))
    But according to track semantic models refreshes issues , I havent’ seen the the same logic
    Seems there are not “Error” on the package for refreshes KO
    Thant to any help , to track on KUSTO power BI semantic models refreshes issues
    (CommandEnd … ? ) same Operation[XmlaRequestId] context

    Reply

    1. RequestId does identify individual refresh operations, I think, but the problem is likely to be that what you think of as a single refresh operation is treated by the engine as multiple different operations.

      Reply

Leave a Reply