Power Community

Power Community

New ‘ExecutionMetrics’ event in Azure Log Analytics for Power BI Semantic Models

Integrating Power BI with Azure Log Analytics helps owners of semantic models and capacity administrators conduct in-depth historical and near real-time analyses of the activities related to semantic models such as:

  • Identifying the root cause for capacity throttling by determining the specific query, user, report, or visual responsible.
  • Optimizing and troubleshoot queries and refreshes.
  • Proactive detection of long-running/problematic queries or users experiencing errors.
  • Auditing

To enhance customer comprehension about the activities of the semantic model, a new event called ‘ExecutionMetrics’ is now generated for each Discover, Command, and Query request. This event provides useful execution metrics for the respective request such as:

  • Number of rows processed for queries and refresh.
  • Time a request got delayed due to capacity throttling.
  • Time to establish a connection to data source.
  • Approximate memory and CPU consumption.

For instance, using the following KQL query allows quick identification of which semantic models and users that experienced capacity throttling in the previous day:

let executionMetrics = PowerBIDatasetsWorkspace
| where TimeGenerated > ago(1d) 
| where OperationName == "ExecutionMetrics" 
| extend eventTextJson = parse_json(EventText) 
| extend capacityThrottlingMs=toint(eventTextJson.capacityThrottlingMs)
| where capacityThrottlingMs > 0;
let commands = PowerBIDatasetsWorkspace 
| where OperationName in ("CommandEnd", "QueryEnd", "DiscoverEnd") 
| project
TimeGenerated,
ExecutingUser,
ArtifactId,
PowerBIWorkspaceId,
CommandOperationName = OperationName,
XmlaRequestId,
CorrelationId,
CommandText = EventText;
commands
| join kind=inner executionMetrics on XmlaRequestId
| project
TimeGenerated,
ArtifactId,
PowerBIWorkspaceId,
ExecutingUser,
CommandOperationName,
XmlaRequestId,
EventText,
CommandText,
capacityThrottlingMs
| summarize countThrottling = count(), avgThrottlingDuration = avg(capacityThrottlingMs) by PowerBIWorkspaceId, ArtifactId, ExecutingUser, CommandOperationName

Community

The Power BI Customer Service and Support (CSS) team have released a great new open-source Power BI report template for Log Analytics. This template leverages the ‘ExecutionMetrics’ events, along with various analytical insights garnered from years of assisting clients in diagnosing and solving issues with their semantic models. Learn more about this template here.

Learn more about Log Analytics and the ‘ExecutionMetrics’ event at our documentation.

This post was originally published on this site

- Advertisement -spot_img

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisement - Advertisement

Latest News

Preferred Solution | New feature | Microsoft Dataverse

Click on the below image to navigate to my YouTube Channel. Please like and share your valuable feedback on this...

More Articles Like This

- Advertisement -spot_img