Creating Interactive Reports with Power BI Desktop

While it is possible to develop interactive reports through the Power BI service, the preferred tool for authoring interactive reports is Power BI Desktop. This is a completely free tool that can be downloaded from the following link: https://powerbi.microsoft.com/en-us/desktop.

Once you have downloaded, installed, and opened Power BI Desktop, it will ask you to sign into a Power BI account. Feel free to skip this step if you are not going to publish reports to the Power BI service.

The Power BI Desktop UI has a similar look and feel to Microsoft Office products such as Microsoft Excel. The ribbon at the top of the canvas allows you to connect to different data sources, create custom measures and columns, and perform different tasks that are specific to the different Power BI Desktop views. There are three views available in Power BI Desktop, all of which can be accessed on the left side of the canvas. The following is a list of the views in the order that they appear:

• Report—In this view report creators can build and modify interactive reports. This view includes several tool panes on the right side of the UI that report creators can use to build new data visualizations and choose what pieces of data are displayed on the page. At the bottom of this view, there is a tab labeled “Page 1” next to a + sign. Interactive reports can have several pages as a means of organizing content for different topics.
• Data—This view displays all tables, measures, data fields that are used in the data model. This view also allows users to transform data for best use by the report's data model.
• Model—This view allows users to see and edit the relationships among the tables in the data model.

Figure 6.1 illustrates a blank report on the Report view and highlights where you can access the other views.

The first step in building an interactive report is to connect to one or more data sources. To do this, click the Get Data button in the Home ribbon at the top of the canvas. This will open the Get Data page (see Figure 6.2), displaying the many data sources that Power BI can connect to. Power BI Desktop groups its data in the following categories:

• File—This category includes common file sources, such as Excel, CSV, JSON, and PDF files.
• Database—This category includes several popular database services, including SQL Server, Access, Oracle, MySQL, PostgreSQL, SAP HANA, Amazon Redshift, Teradata, and MariaDB.
• Power Platform—This category provides connectivity to the Microsoft Power Platform ecosystem, including Power BI datasets and dataflows as well as Dataverse.
• Azure—This category contains connectors to the most popular Azure data services, including Azure SQL Database, Azure Synapse Analytics SQL pools (both dedicated and serverless), Azure Blob Storage, ADLS, Azure Databricks, and Azure Cosmos DB.
• Online Services—This category contains connectors to popular SaaS platforms like SharePoint Online lists, Microsoft Exchange Online, Dynamics 365, Salesforce, and Google Analytics.
• Other—This category contains connectors for services that do not fall in any specific category, such as Active Directory, SharePoint lists, Solver, and Apache Spark. It also includes generic interfaces such as ODBC, OLE DB, OData, and REST APIs to expand Power BI's connectivity options to any service that is accessible via those interfaces. Finally, users can leverage existing R or Python scripts to access data sources through the R script or Python script connector.

After you select a connector type, Power BI will open a prompt that requests access information specific to that connector. Most connector types will also ask whether to import a copy of the data into Power BI's in-memory data store or leave the data in its source system where it is queried dynamically every time the report is interacted with. Power BI supports the following data connectivity types:

• Import—This data connectivity type will load all of the data objects selected into the Power BI cache. This is the most performant data connectivity option as any visual that is built off of imported data will query the cache every time it is interacted with. The imported data is packaged with the data model, and reports that are published to the Power BI service or Power BI Report Server will also publish the imported data as a dataset. Changes that are made to the source data store are not automatically reflected, making it necessary to manually refresh a dataset or scheduling it to be refreshed during predefined time periods.
• DirectQuery—This data connectivity type does not load data from the source data store into Power BI. Instead, Power BI creates a reference to the data objects selected and queries the data source every time a data visualization is interacted with. While there are no storage limitations with this option, there are performance implications as every interaction with a data visualization results in a query being issued against the source data store. DirectQuery is usually used instead of importing data when data is changing frequently and near-real-time reporting is needed, and when the volume of data being reported on is very large, making it cumbersome to import all of it.
• Live connections—This data connectivity type is similar to DirectQuery but is specific to connectors that use the same storage engine as Power BI, such as Azure Analysis Services and SQL Server Analysis Services.

Power BI allows users to mix different connectivity types, resulting in composite data models. Composite models consist of two or more data connections from different data sources. For example, a report creator can use DirectQuery to connect to an Azure Synapse Analytics dedicated SQL pool to minimize the size of the Power BI dataset and import reference data from a CSV file to optimize report performance. While this is useful, not all data sources can be used in a composite model. More information about composite model limitations can be found at https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models#considerations-and-limitations.

By selecting the Azure SQL Database connector, you can connect to the dp900sql001 logical server and the dp900sql001 database that were created in Chapter 2, “Relational Databases in Azure.” As you can see in Figure 6.3, by expanding the Advanced Options setting on the SQL Server database pop-up window, you can use a T-SQL query to retrieve the data you want added to the data model. After clicking OK, you will be asked to authenticate to the Azure SQL Database instance.

If you do not use a query to retrieve data from the database, Power BI will open a new prompt that will allow you to select one or more tables or views to import or reference (see Figure 6.4). This example will import the SalesLT.Product, SalesLT.ProductCategory, and SalesLT.SalesOrderDetail tables from the database into the dataset. Click Transform Data after selecting the tables to use Power BI's Power Query Editor to modify data before it is loaded into Power BI.

The Power Query Editor enables report creators to preprocess data to meet report requirements. Users can apply several transformations to the data, such as changing column data types, removing rows based on specific criteria, splitting columns, pivoting rows into columns and vice versa, applying mathematical functions to columns, and parameterizing the dataset to make it more dynamic. The Power Query Editor can also apply machine learning transformations by connecting to an Azure Machine Learning model, by invoking an Azure Cognitive Services API, or by running an R or a Python script against the data.

As you can see in Figure 6.5, the Power Query Editor is divided into the following four main components:

• In the ribbon at the top of the page, there are several buttons available to apply transformation steps to the data.
• In the left pane, each table/view selected is presented as a query. Users can switch back and forth between queries to apply different transformations to each one. Keep in mind that if you use a T-SQL query to establish the connection to the data source, then the data source will be presented as a single query in the Power Query Editor.
• In the center pane, data from the selected query is displayed and is available for shaping.
• The Query Settings pane on the right of the page displays all of the transformation steps applied to the query. This pane also allows users to rename the query to a more user-friendly name.

Transformations that are applied through the Power Query Editor are translated to M code. M is the formula language used by the Power Query engines in Analysis Services (both Azure and SQL Server Analysis Services), Excel, and Power BI to apply data transformations to connected data sources. The Power Query Editor allows users to view the M code by clicking the Advanced Editor button in the ribbon at the top of the page. It also lets users edit the code and add transformation steps or parameters that are not exposed through the Power Query Editor UI. Figure 6.6 is an example of the Advanced Editor and the M code that is generated to connect to the SalesLT.SalesOrderDetail table. The code also applies a single transformation that changes the LineTotal column data type.

Click Close & Apply in the ribbon at the top of the page to load the data after you are satisfied with the data model. You can revisit the Power Query Editor to add or modify data transformation steps at any time by clicking the Transform Data button in the Home ribbon.

If you go to the Model view in Power BI Desktop, you will see that Power BI has inferred a one-to-many relationship between the Product and Sales Order Detail tables and a one-to-many relationship between the Product Category and Product tables (see Figure 6.7). These relationships are proposed based on common field names and can be altered through the Manage Relationships button in the Home ribbon. At the bottom of the view, users can create additional tabs that focus on different subsets of tables. The All Tables tab is created automatically to display the relationships between all tables in the model.

Before moving to the Data view, it is important to note how Power BI manages imported data. Power BI use an in-memory storage engine that stores data as tabular models, just like Azure Analysis Services and the SQL Server Analysis Services tabular mode. Tabular models use relational constructs like rows and columns to manage data. The VertiPaq engine used to power tabular models leverages modern compression algorithms to ensure fast performance for data retrieval and custom calculations. Tabular models support Data Analysis Expression (DAX) formulas to create custom calculations such as measures or calculated columns.

Navigate to the Data view to see the imported data in tabular format; it can be broken down into the following components (see Figure 6.8):

• The data grid in the center of the view displays the selected table and all columns and rows in it. You can highlight specific columns in the data grid to apply filters and quick transformations to the data.
• The Fields pane on the right side of the view allows you to choose which table is displayed in the data grid. You can also use the Fields pane to add new calculated measures or columns, rename data objects, and hide objects from the report view.
• The formula bar just above the data grid allows you to enter DAX formulas to create new measures and calculated columns. This is grayed out by default, but if you click New Measure or New Column, the formula bar will become available for you to enter DAX code. Let's take this time to create a new measure that calculates the most popular item sold by order quantity by clicking New Measure and adding the following DAX code to the formula bar:

          Most Popular Item Sold = TOPN(1, VALUES('Product'[Name]),
          CALCULATE(SUM('Sales Order Detail'[OrderQty])))
  
• At the top of the view there are two contextual ribbons, Table Tools and Column Tools, that contain common transformation activities like changing a column's data type and format.

Once the data is modeled and cleansed to meet report specifications, navigate to the Report view to build an interactive report. This view allows report creators to do what they do best: build reports. The following steps can be used to create a report that displays the total order sales and total order quantity for every subcategory of road bikes that were sold.

1. Create a slicer for product names. There are two ways to start authoring visualizations with Power BI Desktop. Either you can drag a field from the Fields pane onto the canvas and choose the appropriate visual, or you can drag a visual from the Visualizations pane onto the canvas and drag data fields into the appropriate setting. For this filter, we are going to drag the Names field from the Product Category table onto the canvas and choose the Slicer visual. Select Road Bikes in the slicer to filter every visual on the page to only show data for road bikes.
2. Create a clustered column chart to display total order quantity for each product category sold. Select the Clustered Column Chart visual to create a blank clustered column chart. Drag the Name field from the Product table to the Axis setting. Next, drag the OrderQty field from the Sales Order Detail table to the Values setting. This will create a clustered column chart that displays the number of items sold for each product. Because the slicer is actively filtering the report by the road bike category, the column chart will display the order quantity data for each road bike subcategory. You can modify the visual's format at any time by clicking the Format icon in the Visualizations pane and changing settings such as the bar colors, text font, title, and data labels.
3. Create three card visuals that display the total sales amount, total order quantity, and most popular item sold. Select the Card visual three times to create three blank cards on the canvas. Add the following to each card:
   1. For the first card, drag the OrderQty field from the Sales Order Detail table to the Fields setting. Power BI will automatically aggregate the OrderQty field to display its sum based on any applied filters.
   2. For the second card, drag the LineTotal field from the Sales Order Detail table to the Fields setting. Power BI will automatically aggregate the LineTotal field to display its sum based on any applied filters.
   3. For the third card, drag the previously defined Most Popular Item measure from the Product table to the Fields setting. Power BI will use this measure to display the item with the highest OrderQty value based on any applied filters.

As an interactive report, the filtering capabilities extend further than just the slicer visual. By clicking any of the columns in the column chart, the page will filter so that the other visuals will focus on the specific product name that is associated with the clicked column. Also, if you hover your mouse over any of the columns you will see a tooltip that displays the product name and order quantity for that specific product. Figure 6.9 illustrates the finished report with the tooltip for the first column in the column chart.

After you have finished building the report, you can save the file locally as a PBIX file. PBIX files (saved with the .pbix filename extension) can be opened at any time by Power BI Desktop so that report creators can modify the visuals, data model, and query definitions used to power the report. In addition to saving reports as PBIX files, you can save the report as a Power BI report template (PBIT) that other report creators can use as a starting point for a new report's layout, data model, and query definitions. Unlike a PBIX file, PBIT files (saved with the .pbit filename extension) do not include the data used to power the report. More information about Power BI report templates can be found at https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-templates.

Publishing and Sharing Interactive Reports with the Power BI Service

Now that the data model and report are ready to be shared with other users, let's publish the report to the Power BI service. You can do this by clicking the Publish button in the Home ribbon and choosing a destination workspace for the report and data model. Remember that you will need to log into the service first before you can publish the report.

Workspaces serve as containers in the Power BI service where users can logically organize content and collaborate with colleagues. A single workspace can host several datasets, reports, and dashboards that serve the reporting needs of specific business units or for specific solutions. Content creators with a Power BI Pro or Power BI Premium Per User license can share workspace content to different users and control their level of access by assigning them one of the following roles:

• Viewer—Users assigned this role will only be able to view and interact with reports and dashboards contained in the workspace.
• Contributor—Users assigned this role will be granted Viewer permissions and the ability to create, edit, and delete content. This includes publishing reports to the workspace, downloading a report, and scheduling data refreshes.
• Member—Users assigned this role will be granted Viewer and Contributor permissions as well as the ability to add other users to the workspace with Viewer or Contributor permissions.
• Admin—Users assigned this role will have full control of the workspace, including the ability to update and delete the workspace.

More information about Power BI workspace roles can be found at https://docs.microsoft.com/en-US/power-bi/collaborate-share/service-roles-new-workspaces#workspace-roles.

The workspace features that are available to a user, such as the number of times they can schedule a data refresh or the maximum data model size that a workspace can manage, depends on the type of license the user has and the Power BI plan applied to the workspace. While licensing is not covered by the DP-900 exam, it is important to know that users can use a Power BI Pro or Power BI Premium Per User license to manage content and that workspaces can use shared capacity or premium (dedicated) capacity. The feature and price differences between license and plan types can be found at https://powerbi.microsoft.com/en-us/pricing.

Workspace content is organized into different sections for dashboards, reports, and datasets as well as two other objects not discussed so far: workbooks and data flows. Power BI workbooks are Excel workbooks that are imported into a Power BI workspace. Once imported into a Power BI workspace, the workbook is converted to a dataset that can be used to serve new reports. Power BI dataflows offer similar data connection, ingestion, and transformation functionality as the Power BI Desktop Power Query Editor but are instead managed by the Power BI service.

You can navigate to the Power BI service by going to https://powerbi.com. Once you are signed in, you will be able to traverse the service by using the buttons in the left-side menu. This menu is divided into two sections, with the top one including buttons that will take the user to commonly browsed pages such as the Power BI service home page, recently viewed objects, Power BI apps, and deployment pipelines. The bottom section of the left-side menu allows users to navigate to the different workspaces that they have access to.

All of the content associated with a workspace is displayed in the center of the page when you select a workspace. The workspace home page also includes options that allow users to manage several administrative activities such as setting a dataset refresh schedule and granting user access. Figure 6.10 shows the home page of the “DP900-PBI-Workspace” workspace that contains the DP900PBI report and dataset.

If you click on the report name, Power BI will open it in the center of the page. This view, known as the “reading view,” allows users to interact with visuals and derive insights from the report. Users with the appropriate access can click the Edit button in the top bar to make modifications to the report directly in the Power BI service. Figure 6.11 shows the DP900PBI report in the DP900-PBI-Workspace.

As you can see in Figure 6.11, the top bar includes additional settings that allow you to monitor the usage of your report and share the report to internal and external business users. There are also options that allow users to export the report to a PDF or PowerPoint to serve business users who prefer to consume their reports through those mediums.

Reports, both interactive and paginated, are often too granular for business decision makers who need to monitor the performance of their business in real time. Many times, these decision makers are looking for a clear, summarized view that allows them to monitor their business and see the most important metrics without having to dig through a mountain of reports. Content creators can provide this functionality in Power BI by building dashboards that highlight the most relevant visuals from one or more Power BI reports. The next section, “Dashboards,” examines Power BI dashboards in further detail.