Administering and Monitoring Power BI

In addition to solutions targeting business processes and entities, such as sales and customers, Power BI can also serve as a platform for system monitoring and administration. Diverse system telemetry and diagnostic data sources such as logs from Azure services, performance monitor counters, and events can be integrated into Power BI datasets to deliver robust visibility to system health, activity, and performance. Although there are several dedicated monitoring tools available, such as Operations Manager in Microsoft System Center, building a custom solution with Power BI provides full flexibility and control over all layers of the solution, while leveraging relevant Power BI features, including data-driven alerts, email notifications and subscriptions, and Power BI mobile. Additionally, as more organizations adopt and deploy Power BI, existing licenses and experience can significantly reduce the costs of developing and maintaining these solutions...

Technical requirements

• Power BI Desktop.
• SQL Server with the AdventureWorksDW2019 database installed. This database and instructions for installing it are available here: http://bit.ly/2OVQfG7.
• Files for this chapter can be downloaded from the following GitHub repository: https://github.com/PacktPublishing/Microsoft-Power-BI-Cookbook-Second-Edition.

Creating a centralized IT monitoring solution with Power BI

Power BI's rich data connectivity and transformation capabilities are very well suited for the integration needs of system and database administrators. A collection of log files containing performance monitor counter statistics can be retrieved from a file directory (or multiple directories), consolidated, and further enhanced to support reporting. Additional sources, such as snapshots of performance and configuration data stored in a dedicated administrator database, can also be included in a scheduled data refresh process, and the inclusion of existing BI dimension tables, such as date and time, further simplifies the overall monitoring solution.

In this recipe, a set of Windows Performance Monitor counter files containing statistics on CPU, memory, and more are integrated with administrative data stored in a SQL Server database, including query wait statistics and instance configuration values.

Getting ready...

Constructing a monitoring, visualization, and analysis layer

Monitoring and administration tools, such as Performance Monitor, SQL Server Activity Monitor, Query Store, and Extended Events, include their own graphical interfaces for viewing and analyzing their own specific datasets. However, these features are limited relative to the data exploration and visualization capabilities provided by dedicated BI tools, such as Power BI. Additionally, as per the first recipe of this chapter, system and database administrators require an integrated view over distinct data sources with a common and flexible visual surface. The ability to define logic on top of monitoring source data, along with the "anywhere" availability of Power BI content and its data alert and advanced analytics features, further enhances the value of integrated monitoring datasets.

In this recipe, the monitoring dataset created in the first recipe of this chapter is leveraged to develop reporting content...

Importing and visualizing dynamic management view (DMV) data

Analysis Services data models and Power BI instances include many dynamic management views (DMVs), which can be used to retrieve both schema metadata and resource usage associated with the various database objects. Query performance for imported data models is directly impacted by the amount of memory consumed. Therefore, having visibility to memory consumption and factors impacting memory consumption, such as compression and cardinality, is essential in performance tuning efforts. Power BI integration and visualization tools can enhance the value of the system information provided by DMVs to provide owners of Power BI and SSAS datasets with an intuitive, sustainable reporting layer in support of these assets.

In this recipe, M queries are created to retrieve and transform DMV information from a Power BI dataset. Essential relationships, measures, and report visuals are then built to support memory usage analysis.

...

Increasing DBA productivity

SQL Server Query Store is a monitoring feature available to all editions of SQL Server 2016 and later—it significantly simplifies and expedites query tuning and troubleshooting. The Query Store database provides aggregated metrics regarding query executions, query plans, and wait statistics to enable visibility to performance trends and usage patterns.

"Query Store is a fantastic flight data recorder for your execution plans. It will help you troubleshoot parameter sniffing issues, connection settings issues, plan regressions, bad stats, and much more."

—Brent Ozar, Author and Microsoft-Certified Master in SQL Server

Query Store includes a graphical interface of charts and user controls, and its schema lends itself to custom T-SQL queries such as 10 longest-running queries in the past hour. While these are great features and sufficient for certain scenarios, administrators often have to make trade-offs between the flexibility...

Providing documentation

As data models grow and change to support new business processes and logic, access to current documentation becomes imperative. Visibility to basic metadata, such as the relationships of the model, columns of the tables, and the filtering logic built into measures, can significantly aid business teams in utilizing Power BI datasets. Additionally, business intelligence and IT professionals who may be new to a specific model or unfamiliar with a component of the model can benefit greatly from direct access to technical metadata, such as data source parameters, SQL and M queries, and the configured security roles.

In this recipe, several dynamic management views (DMVs) related to the schema of a Power BI dataset are accessed and integrated into a Power BI report. A template is then created with parameters, enabling standard documentation reports across multiple Power BI datasets.

Getting ready

To prepare for this recipe, follow these steps:

...

Analyzing SSAS tabular databases and gateways

A Microsoft on-premises data gateway enables specific cloud services, including Power BI, Azure Analysis Services, Power Apps, and Power Automate, to securely connect to on-premises data sources. In the context of Power BI, these connections support both the scheduled refresh of imported datasets stored in Power BI, as well as DirectQuery and Live Connection datasets, in which only report queries and their results are exchanged between Power BI and the on-premises source. As the availability and performance of the gateway are critical for any Power BI and other supported cloud service deployment requiring on-premises data, regular monitoring of both the gateway service and its host server(s) is recommended.

In this recipe, performance monitor counters specific to the on-premises data gateway and SQL Server Analysis Services are integrated into a single Power BI dataset. This source data is dynamically retrieved and enhanced via M queries...

Analyzing Extended Events

Extended Events is a highly configurable and lightweight performance monitoring system available to both the SQL Server relational database engine and Analysis Services. A vast library of events is available to specific sessions, which can be saved, scheduled, and then analyzed to support performance tuning, troubleshooting, and general monitoring. However, similar to other monitoring tools (such as Windows Performance Monitor and SQL Server Query Store), the Extended Events graphical interface lacks the rich analytical capabilities and flexibility of reporting tools such as Power BI.

In this recipe, the output of an Extended Events session containing query execution statistics is retrieved in a dedicated Power BI event analysis report file. The 1.4 million rows of event data from this file are enhanced during the import process, and report visualizations are developed to call out the most meaningful trends and measures, as well as support further self...

Visualizing log file data

Log files containing SQL Server Agent job history and the Power BI usage activities stored in the Office 365 audit log and Power BI activity log can also be integrated into the Power BI monitoring solution described earlier in this chapter. For example, SQL Agent job data can reveal important trends, such as the performance of a nightly job used to load a data warehouse and the duration and reliability of individual steps within these jobs. Likewise, detailed reporting and, optionally, alerts based on user activities in the Power BI service, such as deleting a dashboard, enable BI and IT administrators to better manage and govern Power BI deployments.

In this recipe, transformations are applied to the structure of the Power BI audit log to convert the audit data stored in JSON format and adjust for local time reporting. Additionally, an advanced T-SQL query is used to access the job history data in SQL Server Agent system tables, and to prepare this data...

Leveraging the Power BI PowerShell Module

Power BI administrators can automate common tasks by developing PowerShell scripts which leverage commands included in the Microsoft Power BI Management PowerShell module such as Get-PowerBIWorkspace and Add-PowerBIWorkspaceUser. In addition, custom administrative solutions can be developed using the Power BI REST APIs and .NET client library. Using the Power BI REST API, administrators can automate the creation of workspaces and dashboards, publish workspace content, share workspace content, remove access to workspaces, delete workspaces, and much more.

This recipe demonstrates how to connect to Power BI via PowerShell and perform simple administrative functions.

Getting ready

To prepare for this recipe, follow these steps:

1. Enable Power BI APIs in Tenant settings of the Power BI service:

   

   Figure 10.28: Power BI APIs tenant settings in the Power BI service

2. Make sure that you are a Power BI Administrator...

Conclusion

This chapter's recipes highlighted the most common and impactful administration data sources, including Windows Performance Monitor, SQL Server Query Store, the on-premises data gateway, the MSDB system database, and Extended Events. Power BI solutions built on top of these sources proactively assess usage trends and resource bottlenecks, while delivering the detailed analysis necessary to identify root causes. Additionally, this chapter covered the metadata of existing Power BI and SSAS data models exposed via dynamic management views (DMVs), such as measure and relationship definitions and resource consumption.