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.