Configure Azure Log Analytics for Power BI

Author: Angad Soni
Chief Architect, Business Applications & Data Modernization
Long View

 

---

 

Power BI is integrating with Azure Log Analytics to enable administrators and Premium workspace owners to configure a Log Analytics connection to their Power BI subscription. This document describes how the integration between Log Analytics and Power BI works and how to configure it for your environment. 

Azure Log Analytics for Power BI, with reference links and images: 

1. Become familiar with the integration: Power BI has integrated with Azure Log Analytics, allowing administrators and Premium workspace owners to connect their Power BI subscription to Log Analytics. This integration enables you to monitor and analyze data from your Power BI environment¹. 

Create a Log Analytics Workspace 

1. Create a Log Analytics Workspace: Before you can set up the integration, you need to create a Log Analytics Workspace in the Azure portal. To do this, sign in to the Azure portal and navigate to the Log Analytics Workspaces page. Click on the “+ Add” button to create a new workspace. https://learn.microsoft.com/en-us/azure/azure-monitor/logs/quick-create-workspace?tabs=azure-portal  

Register Resource Provider 

1. Register the ‘microsoft.insights’ resource provider: In the Azure portal, select the subscription that contains your Log Analytics workspaces. In the left-hand menu, click on “Subscriptions” and then select your subscription from the list. In the Settings section of your subscription’s overview page, select “Resource providers” and search for ‘microsoft.insights’. Then, click on the “Register” button next to ‘microsoft.insights’ to enable it³. 

 

 

Assign Role

1. Ensure the user has the correct role: The user setting up the integration must have the Log Analytics Contributor role for the Log Analytics Workspace. To check this, navigate to your Log Analytics Workspace in the Azure portal and click on “Access control (IAM)” in the left-hand menu. Then, click on “Role assignments” and search for the user who will be setting up the integration. Make sure that they have been assigned the “Log Analytics Contributor” role⁴.

Enable Log Analytics

1. Enable Log Analytics in the Power BI Admin portal: Sign in to the Power BI Admin portal and navigate to “Tenant settings” in the left-hand menu. Scroll down to “Audit and usage settings” and expand the “Azure Log Analytics connections for workspace administrators” section. Set the slider to “Enabled” and specify any necessary security groups in the “Apply to” field⁵.

 

 

Configure Logging

Configure logging in a Premium Workspace: In a Premium workspace, admins can enable Log Analytics by clicking on “Workspace settings” in the top-right corner of the screen. In the Settings pane, select “Azure connections” and expand the “Log Analytics” section. Select your Azure subscription, Resource group, and Log Analytics workspace from the dropdown menus. Then, click on "Save"⁵.

 

 

 

 

 

Monitor

1. Monitor your data: Once you have completed these steps, data from your Power BI environment will be collected in your Log Analytics Workspace. You can use this data to monitor usage, troubleshoot issues, and gain insights into your environment.

Disconnect Azure Log Analytics

You can disconnect from Azure Log Analytics to stop sending logs to Azure. To disconnect, in the Power BI Workspace Settings, go to the Log Analytics settings. Select Disconnect from Azure. Then choose Save to disconnect.

 

 

Note

When you disconnect a Power BI workspace from Azure Log Analytics, logs are not deleted. Your data remains and follows the storage and retention policies you set there.

Usage scenarios

There are many ways that Azure Log Analytics and Power BI can help solve real-world challenges for your organization. Consider the following:

• Identify periods of high or unusual Analysis Services engine activity by capacity, workspace, report, or user.
• Analyze query performance and trends, including external DirectQuery operations.
• Analyze dataset refresh duration, overlaps, and processing steps.
• Analyze custom operations sent using the Premium XMLA endpoint.

 

For reports and other information go to: https://github.com/microsoft/PowerBI-LogAnalytics-Template-Reports

 

 

Error conditions and resolutions

The following table provides a collection of common errors, the events or configurations that triggered them, and suggested resolutions.

 

Trigger Condition	Type	Message
You don't have permission to write to the Log Analytics Workspace	Error - cannot proceed	You need write permissions on this Log Analytics workspace to connect it to Power BI. Contact the person in your organization who manages Azure subscriptions to fix this problem.
You don't have permission to write to the Log Analytics workspace account	Error - cannot proceed	You need write permissions on this Log Analytics workspace to connect it to Power BI.
You don't have access to any Azure subscriptions	Error - cannot proceed	You don't have access to any Azure subscriptions. Ask the person who manages Azure subscriptions in your organization to grant you contributor access or higher.
You don't have access to any Azure Log Analytics workspaces within that subscription	Error - cannot proceed	You don't have access to an Azure Log Analytics workspace. Ask the person who manages Azure subscriptions in your organization to add you to the Log Analytics owner or contributor role.
Workspace-level Log Analytics disabled when trying to connect	Information	Ask your tenant admin to grant workspace admins permission to connect Log Analytics workspaces.
Workspace-level Log Analytics disabled when trying to disconnect	Information	Your tenant admin revoked permission for workspace admins to connect their own Azure Log Analytics workspaces. If you disconnect, you can't connect to another one.

 

Events and schema

After you enable Azure Log Analytics, it starts to log the following event categories. For more information on these events, see Analysis Services Trace Events.

• AggregateTableRewriteQuery
• Command
• Deadlock
• DirectQuery
• Discover
• Error
• ProgressReport
• Query
• Session Initialize
• VertiPaqSEQuery
• Notification

The following table describes the schema.

Property	Existing Azure Analysis Services property	Description
TimeGenerated		The timestamp (UTC) of when the log was generated.
OperationName	EventClass_s	The operation associated with the log record.
CorrelationId		The ID for correlated events. Can be used to identify correlated events between multiple tables.
PowerBIWorkspaceId		Unique identifier of the workspace containing the artifact being operated on.
PremiumCapacityId		Unique identifier of the Premium capacity hosting the artifact being operated on.
ApplicationContext	ApplicationContext_s	Property bag of unique identifiers providing details about the application executing the request. for example, report ID.
ApplicationName	ApplicationName_s	Contains the name of the client application that created the connection to the server. This column is populated with the values passed by the application rather than the displayed name of the program.
ArtifactId		Unique identifier of the resource logging the data.
ArtifactKind		Type of artifact logging the operation, for example, Dataset.
CpuTimeMs	CPUTime_s	Amount of CPU time (in milliseconds) used by the event.
ArtifactName	DatabaseName_s	The name of the Power BI artifact logging this operation.
LogAnalyticsCategory	Unique	Category of the events, like Audit/Security/Request.
DatasetMode		The mode of the dataset. Import, DirectQuery, or Composite.
DurationMs	Duration_s	Amount of time (in milliseconds) taken by the operation.
User	User_s	The user associated with the running operation. Used when an end-user identity must be impersonated on the server.
ExecutingUser	EffectiveUsername_s	The user running the operation.
OperationDetailName	EventSubclass_s	More details about the operation.
XmlaObjectPath	ObjectPath_s	Object path. A comma-separated list of parents, starting with the object's parent.
PowerBIWorkspaceName		Name of the Power BI workspace containing the artifact.
StatusCode	Error_s	Status code of the operation. It covers success and failure.
ProgressCounter	ProgressTotal_s	Progress counter.
XmlaProperties	RequestProperties_s	Properties of the XMLA request.
XmlaSessionId	SPID_s	Analysis Services session identifier.
Level	Severity_s	Contains the severity level of the operation being logged. Success, Informational, Warning, or Error.
Identity		Information about user and claims.
Status		Status of the operation.
EventText	TextData_s	Contains verbose information associated with the operation, for example, DAX Query.
CustomerTenantId		Customer's Power BI tenant identifier.
XmlaRequestId	RootActivityId_g	Unique Identifier of request.
ReplicaId		Replica identifier that will let you identify the replica when Query Scale Out (QSO) is enabled. Read-write replica always has ReplicaId='AAA' and read-only replicas have ReplicaId starting 'AAB' onwards. For non-QSO enabled datasets the ReplicaId is always 'AAA'

 

Sample Log Analytics KQL queries

The following collection of sample queries might be helpful when you use Azure Log Analytics with Power BI. They can be run directly in the Azure portal or through APIs to query the latest data, typically about 5-10 minutes old.

SQLCopy
// log count per day for last 30d
PowerBIDatasetsWorkspace
| where TimeGenerated > ago(30d)
| summarize count() by format_datetime(TimeGenerated, 'yyyy-MM-dd')

// average query duration by day for last 30d
PowerBIDatasetsWorkspace
| where TimeGenerated > ago(30d)
| where OperationName == 'QueryEnd'
| summarize avg(DurationMs) by format_datetime(TimeGenerated, 'yyyy-MM-dd')

//query duration percentiles for a single day in 1 hour bins
PowerBIDatasetsWorkspace
| where TimeGenerated >= todatetime('2021-04-28') and TimeGenerated <= todatetime('2021-04-29') | where OperationName == 'QueryEnd' | summarize percentiles(DurationMs, 0.5, 0.9) by bin(TimeGenerated, 1h) // refresh durations by workspace and dataset for last 30d PowerBIDatasetsWorkspace | where TimeGenerated > ago(30d)
| where OperationName == 'CommandEnd'
| where ExecutingUser contains 'Power BI Service'
| where EventText contains 'refresh'
| project PowerBIWorkspaceName, DatasetName = ArtifactName, DurationMs

// query count, distinctUsers, avgCPU, avgDuration by workspace for last 30d
PowerBIDatasetsWorkspace
| where TimeGenerated > ago(30d)
| where OperationName == "QueryEnd"
| summarize QueryCount=count()
, Users = dcount(ExecutingUser)
, AvgCPU = avg(CpuTimeMs)
, AvgDuration = avg(DurationMs)
by PowerBIWorkspaceId

 

Subscribe to our newsletter for the latest updates.