Power BI allows users to configure connections to Azure Log Analytics where they can retain detailed historical activity data. This repo hosts Power BI Template (.pbit) reports that you can point to your Azure Log Analytics workspaces to load data and get inisights right away!

After opening the template you will be prompted to enter your Log Analytics Workspace ID. Once it has this information and you have logged in to Azure, the report can refresh.

Here are the available templates:

• Log Analytics for Analysis Services Engine: This report allows you to visualize the activity of datasets hosted in the Analysis Services Engine in Power BI workspaces. You can use it to identify load patterns, investigate user actions, look at query performance trends, visualize refreshes, and much more!

This project welcomes contributions and suggestions. Most contributions require you to agree to a Contributor License Agreement (CLA) declaring that you have the right to, and actually do, grant us the rights to use your contribution. For details, visit https://cla.opensource.microsoft.com.

When you submit a pull request, a CLA bot will automatically determine whether you need to provide a CLA and decorate the PR appropriately (e.g., status check, comment). Simply follow the instructions provided by the bot. You will only need to do this once across all repos using our CLA.

This project has adopted the Microsoft Open Source Code of Conduct. For more information see the Code of Conduct FAQ or contact [email protected] with any additional questions or comments.

This project may contain trademarks or logos for projects, products, or services. Authorized use of Microsoft trademarks or logos is subject to and must follow Microsoft's Trademark & Brand Guidelines. Use of Microsoft trademarks or logos in modified versions of this project must not cause confusion or imply Microsoft sponsorship. Any use of third-party trademarks or logos are subject to those third-party's policies.

• SE Duration measures are currently blank. This is because Vertipaq Storage Engine events are not yet supported in the Preview.
• If you find the report takes a very long time to refresh and appears to be stuck, try to load a shorter period of time. We have built the Power Query logic with Log analytics Query limits in mind, but it is possible you are hitting limits. Another thing to try is to go to the Regional Settings -> Locale in the template and change it to US date format. We have had reports of European customers having the report load blocked becuase of date conversion errors.

The goal of the Template app is to build a tool that can be used to analyze AS Engine behavior in general and to help isolate and debug specific problems in depth. You can slice any operation by using CapacityId, Workspace Name, Dataset Name, and ReportId to give you the necessary context.

The following are some examples of questions that you can answer with the Template app.

• What is the engine activity by capacity and workspace?
• What is the engine load by day or by hour?
• What operations are taking the most CPU time or duration?
• How does the load vary by hour of day?
• Which users are generating load?

• Which Data Analysis Expressions (DAX) queries were issued in a particular scope?
• Which queries are the most expensive?
• How does a unique DAX query vary over time, and which are the worst?
• What was the query text?
• What were the Storage Engine or DQ queries that were generated from a DAX query?
• How much time did the query spend in Storage Engine versus Formula Engine?
• Did queries use aggregations and what was the performance benefit?

• Which refreshes are expensive?
• Which refreshes overlap?
• Which operations within a refresh are in parallel, and which ones take the longest time?
• What were the different operations and suboperations within a refresh?

Since the Template app contains various AS Engine events, you can customize it to answer questions related to other events such as Discover or Notification.

The Template app loads data from a single Azure Log Analytics workspace.

It doesn't matter if the Log Analytics workspace contains data from many Power BI workspaces. It also doesn't matter which level of administrator configured logging. The log schemas are exactly the same for every role, so there's only one version of the Template app. There are different levels of aggregation to accommodate a range of use cases. For more information, see Using Log Analytics in Power BI

The Template app has the following tables and relationships:

• User
• Query Duration Segment
• Scenario
• Calendar
• Time
• Operation
• Suboperation - Aggregations
• Suboperation - Query
• Suboperation - Refresh

The following image is an entity relationship (ER) diagram.

The following parameters are defined in the template:

The following diagram shows the workflow for the Template app.

This page shows engine activities and load at a workspace level, focusing on identifying interesting datasets, reports, or users. You can use this page to identify a high-level issue to analyze further by navigating or drilling through to other pages of the Template app.

This page provides engine load and activity trends by day and hour, with the ability to select a scenario such as Refresh Completed or Query Completed. You can drill through to the Engine activity detail page to get a look at a detailed list of each activity within the selected context.

This page is a drillthrough page showing event-level data. For example, you can list all queries that ran in a particular time range.

This page provides a Gantt chart style view of refreshes to observe duration and parallelism. You can drill through to the dataset refresh details for more details.

This drillthrough page shows the operations that occurred within a single dataset refresh. You can use this view to identify the longest running operation in a refresh and to see if there are any dependencies between activities.

This page is an overview of query performance, highlighting typical and low performing durations and letting you see how variable each unique query is.

This drillthrough page shows visuals such as a detailed table for the query, a table for related queries, and more. For Import tables, the page shows you the internal Vertipaq storage engine queries and durations. For DirectQuery models, the page shows you the external queries, for example T-SQL sent to a SQL Server.

This page shows you every execution of a query, provides CPU and duration stats, and provides trend visuals to see if there are any spikes.

This page shows a summary view that focuses on users, helping you identify the most active users and those users who are experiencing worse performance relative to others.

This drillthrough page provides details of the activities performed by a single user.

This page helps identify errors and spot any error trends.

This page allows you to zoom in on a specific error by viewing the detailed event.

The Template app contains a navigation bar at top of the page to navigate to the expected page.

Also, there's a back button on the top-left corner to go back to the previous page and an info icon that provides information about the page.

Every page has a filter button below the navigation bar that you can select to open the pop-up filter panel and make selections.

The current values of the filters are displayed in the smart narrative next to the filters button. You can clear all the slicers by using the Clear button on the top-left corner or close the window by using the X button in the top-right corner.

If more than one value is selected for a filter, the smart narrative displays one of the values followed by "and more".

• Workspace Summary
• Engine Activities
• Engine Activity Details
• Dataset Refreshes
• Dataset Refresh Detail
• Query Statistics
• Query Detail
• Query History
• User Activities
• User Detail
• Error Summary
• Error Detail
• Help

This page is targeted at a workspace administrator and shows activities and statistics related to datasets and queries. It also identifies top reports by load, details popular datasets by operations or users, and allows drillthrough to various pages to get further details.

The following table lists the visuals displayed on the workspace summary page according to their location on the page.

This page provides a trend overview of AS Engine activities by day and by hour. It allows you to identify peaks or outliers on a day and then see how that activity breaks down by hour when you cross-highlight by selecting a day.

The following table lists the visuals displayed on the engine activities page according to their location on the page.

This page allows you to focus on a narrow time range and see the individual activities at a granular level of detail. The following example shows all the DAX queries that were executed in a minute, sorted by longest duration.

The following table lists the visuals displayed on the engine activity details page according to their location on the page.

This page provides an overview of dataset refreshes occurring over a selected period. It allows you to identify long running refreshes and visualize which ones are happening in parallel. This page allows you to select any data refresh and drill through to a page called Dataset refresh detail.

The following table lists the visuals displayed on the dataset refreshes page according to their location on the page.

This page allows you to visualize a single dataset refresh in detail. You can see all the internal operations that the engine performed such as executing queries and compressing data. It allows you to determine the longest running operations, which are parallel, and which might have dependencies.

The following table lists the visuals displayed on the dataset refresh detail page according to their location on the page.

This page focuses on queries in bulk. The goal is to identify which queries are common and which queries have high variability. The Template app provides percentiles and deviations to give you an idea of both typical and more extreme measurements.

Any query can be drilled through to a page called Query detail to see details about its execution. Among other pieces of information, you can see the internal Vertipaq Queries or external DirectQuery text and duration depending on the model type.

You can also drill through to a page called Query history that shows you all the execution of that query over a period, and its performance trend.

The following table lists the visuals displayed on the query statistics page according to their location on the page.

This page provides a detailed look at a single execution of a DAX query. Depending on whether the query was for an Import or DirectQuery model, you might either see the internal Vertipaq Storage Engine queries or the external DQ source queries (for example, T-SQL for SQL Server). It also identifies which aggregations were used, if any.

The following table lists the visuals displayed on the query detail page according to their location on the page.

The cards on the right display the number of users who ran this query and the application that was used to run this query.

This page is a historical view of a single unique query. It shows metrics over time and introduces the Storage Engine and Formula Engine time. You can use it to determine how consistent a query is over time and identify if issues are isolated to particular users or time frames.

The following table lists the visuals displayed on the query history page according to their location on the page.

The cards on the right display the total number of executions of a given query, the execution times in ms, and the aggregation utilization percentage.

This page gives an overview of the user activities across the workspace. It also gives information about the most active users for a period by capturing their CPU time usage, query usage, and operations performed.

The following table lists the visuals displayed on the user activities page according to their location on the page.

The cards on the right display user count and operations count.

This page provides a detailed historical view of activities for a single user.

The following table lists the visuals displayed on the user detail page according to their location on the page.

This page provides an overview of errors or failed executions over time, allowing you to view individual operations that reported an error status.

The following table lists the visuals displayed on the error summary page according to their location on the page.

The cards on the right display overall operations count, query failure count, refresh failure count, and user count.

This page provides details of errors generated by the engine. It also provides the information about failed operations due to query failures.

The following table lists the visuals displayed on the error detail page according to their location on the page.

This page provides a help summary of different features throughout the Template app. It also has support links that can be used for any support assistance.

Each visual in the Template app has a ? icon. Select this icon to learn more about the visual.

• Log Analytics Query Limits

  • Kusto has limits in terms of the number of records returned and the overall size of the data based on the query. The Template app has been built to work around these limits by pulling data in sequential chunks. However, you might still exceed these limits, resulting in a refresh failure in the Template app. For more information, see Query Limits.

  • If the Template app refresh fails due to the previously mentioned data limits, you can configure the Pagination Hours parameter. Setting a lower value here lowers the amount of data retrieved from Log Analytics per call by increasing the number of calls.

• The following events are intentionally excluded from Log Analytics during the Preview:

  • ProgressReportCurrent
  • ProgressReportBegin
  • ProgressReportError
  • VertipaqSEQueryBegin
  • VertipaqSEQueryEnd

  Due to this design decision, storage engine subqueries aren't visible for now on the Query detail page.