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:
- 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 environment1.
Create a Log Analytics Workspace
- 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
- 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 it3.
Assign Role
- 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” role4.
Enable Log Analytics
- 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” field5.
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"5.
Monitor
- 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.