Working in the Service

"A dashboard is really a content aggregator. It lets you bring together lots of different data sources in one place so you can have a 360 degree view of your business on one dashboard."

- Adam Wilson, group program manager for the Power BI service

Power BI dashboards are collections of tiles created in the Power BI service, representing the visuals from one or many Power BI reports and, optionally, other sources, such as Excel and SQL Server Reporting Services (SSRS). Dashboards are best utilized to centralize essential key performance indicators (KPIs), measures, and trends in a visual- and mobile-optimized layout. Additionally, dashboards can provide an entryway to other dashboards or reports and are further enhanced with URL links, streaming data, images, web content, and interactivity.

In this chapter, Power BI dashboards are constructed to provide intuitive, at-a-glance monitoring of critical measures and high-impact business activities...

Technical Requirements

The following are required to complete the recipes in this chapter:

• Power BI Desktop and a Power BI service account.
• 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 and can be downloaded from the following GitHub repository: https://github.com/PacktPublishing/Microsoft-Power-BI-Cookbook-Second-Edition.

Building a Dashboard

With a robust dataset and multiple reports created in Power BI Desktop, dashboards can be developed in the Power BI service to consolidate the most valuable KPIs, measures, and visualizations onto a single pane of glass. Additionally, dashboards provide an access point to the detailed reports supporting the tiles and are optimized for mobile access through the Power BI mobile application.

Power BI dashboards are commonly used by executives and senior managers who desire an intuitive, consolidated view of relevant KPIs and visualizations. In many cases, these stakeholders will almost exclusively rely on dashboards rather than the more detailed and interactive reports underlying the dashboards. Such dashboards are often sourced from reports built against multiple datasets reflecting different business processes and functional areas such as Accounting and Supply Chain. Enterprise dashboards typically utilize card and KPI visuals to focus on strategic objectives...

Preparing for Q&A

Q&A can be a powerful method of enabling users to explore datasets, enabling them to directly submit their own questions in both the Power BI service and through the Power BI mobile application. The tables and measures of each dataset, represented by a tile on the dashboard, are available to answer Q&A questions.

This recipe provides data model design and metadata tips to prepare a dataset for Q&A. Additionally, synonyms are added to a Power BI Desktop data model to improve the accuracy of natural language queries.

Getting ready

To prepare for this recipe:

• Complete the first recipe in this chapter, Building a Dashboard.

How to Prepare for Q&A

To implement this recipe, use the following steps:

1. Open the North American Sales dashboard.
2. At the upper left of the dashboard, click Ask a question about your data.

   

   Figure 5.13: Power BI Q&A

3. Type the following: wholesaler net sales...

Adding Alerts and Subscriptions

Alerts can be configured on the tiles of dashboards to provide a notification that a specific condition has been met. Alert rules and options can be managed in the Power BI service, and notifications can be limited to the notification center in Power BI or shared via email. Data-driven alerts enhance the value of Power BI dashboards, as they immediately bring attention to significant events or outcomes as the dataset supporting the dashboard tile is refreshed. Power BI dashboards and reports also support subscriptions. Subscriptions send images and links of refreshed reports and dashboards directly to user inboxes according to the defined schedule. Additionally, paginated report subscriptions can be configured to attach report data to subscription emails in common file formats such as Excel and PDF.

This recipe demonstrates how to create and configure alerts and subscriptions, as well as how to use this functionality to unlock even more powerful...

Deploying Content from Excel and SSRS

Dashboards in Power BI can consolidate much more than just Power BI report visuals. Microsoft Excel objects—such as pivot tables, charts, and workbook ranges—and SSRS report items can also be pinned as dashboard tiles. This integration with Power BI allows teams to utilize existing reports and skills, and to leverage the unique capabilities of these tools as part of overall BI solutions.

In this recipe, a pivot table and pivot chart from an Excel workbook are integrated into an existing Power BI dashboard. Additionally, an SSRS report item is also pinned to this dashboard. For more advanced integration examples, see Chapter 13, Integrating Power BI with Other Applications.

Getting ready

To prepare for this recipe, follow these steps:

1. Complete the first recipe in this chapter, Building a Dashboard.
2. Install Analyze in Excel from the Power BI service. See Chapter 1, Configuring Power BI Tools, for details...

Streaming Datasets

Streaming datasets allow Power BI dashboards to display real-time data. Real-time data sources are generally Internet of Things (IoT) devices, but can also include such things as social media applications and stock prices.

This recipe demonstrates how to use real-time data in a Power BI dashboard.

Getting ready

To prepare for this recipe:

• Complete the first recipe in this chapter, Building a Dashboard.

How to Stream Datasets

To implement this recipe, use the following steps:

1. Navigate to the workspace where North American Sales is located in the service.
2. Under the workspace name, select New, and then Streaming dataset.

   

   Figure 5.33: Create a new Streaming dataset

3. In the New streaming dataset pane, choose PUBNUB and click Next.
4. Name the dataset Streaming Sensor Data.
5. Use the following Sub-key: sub-c-5f1b7c8e-fbee-11e3-aa40-02ee2ddab7fe.
6. Use the following Channel: pubnub-sensor...

Authoring Dataflows

Dataflows are reusable, scalable self-service ETL (extract-transform-load) artifacts created exclusively in the Power BI cloud service. Dataflows utilize the same Power Query (M) language for data access and transformation that has existed within Power BI Desktop but can be shared across datasets and, with Power BI premium capacity, can support large, resource-intensive workloads.

This recipe demonstrates how to create a dataflow within the Power BI service, and how that dataflow can then be leveraged within Power BI Desktop.

Getting ready

To prepare for this recipe, follow these steps:

1. Download the CH5_R7_AuthoringDataflows.xlsx file from the following GitHub repository: https://github.com/PacktPublishing/Microsoft-Power-BI-Cookbook-Second-Edition.
2. Save the CH5_R7_AuthoringDataflows.xlsx file to your OneDrive for Business directory.

How to Author Dataflows

To implement this recipe, perform the following steps:

...

Conclusion

The Power BI service provides the capability to create and share additional BI objects, such as dashboards, apps, streaming datasets, and dataflows; these components can be enriched and enhanced with alerts, subscriptions, images, and web content. In this chapter, Power BI dashboards were constructed to provide simple, at-a-glance monitoring of critical measures and high-impact business activities. The unique features of dashboards, such as Q&A natural language queries, data alerts, and the integration of other report types—such as Excel and SSRS—was also included. Finally, the creation and use of streaming datasets and dataflows was also covered.