It is assumed that the reader has access to a Power BI Pro or Premium Per User (PPU) license, the rights to download and install the development tools on their machine, and the necessary access and rights to deploy and manage content in the Power BI service and utilize the Power BI mobile applications. In addition, the following are required to complete the recipes in this chapter:

• A Windows PC running Windows 10 or Windows 11
• At least 10 GB of disk space available; the Power BI Desktop download is up to 600 MB in size and the installed application is over 1.5 GB

Additionally, the Microsoft on-premises data gateway requires the following:

• A Microsoft account
• .NET Framework 4.7.2 (a gateway release from December 2020 or earlier)
• .NET Framework 4.8 (a gateway release February 2021 or later)
• A 64-bit version of Windows 10 or later, or a 64-bit version of Windows Server 2012 R2 or later
• 4 GB of disk space for performance monitoring logs (in the default configuration)

Power BI Desktop is the primary tool used to develop the visual and analytical content that is then deployed and collaborated on in the Power BI service, embedded in other applications and portals, or even shared on the public internet. Although Power BI Desktop runs as a single application, it includes three tightly integrated components with their own options and settings:

• The Power Query and Power Query Editor experience, with its underlying M language and data mashup engine
• The Analysis Services tabular data modeling engine and its DAX analytical language
• The interactive reporting and visualization engine

Configuring and leveraging these capabilities; in addition to advanced analytics and customization features such as R, Python, mobile layout, and natural language queries, makes it possible to build robust and elegant BI and analytics solutions.

Getting ready

Most organizations restrict the ability of employees to install software such as Power BI Desktop on their corporate devices. These organizations typically define a policy and process to push a particular version of Power BI Desktop to all user devices or certain groups of users, thus avoiding compatibility issues and ensuring users have access to recent features.

How to install and configure Power BI Desktop

There are actually three different versions of the Power BI Desktop application, as follows:

• Power BI Desktop (Microsoft Store app)
• Power BI Desktop (Microsoft Installer)
• Power BI Desktop RS (Report Server edition)

The preferred method of installation is by using the Microsoft Store app. In order to install Power BI Desktop from the Microsoft Store, follow these steps:

1. Open the Microsoft Store app and search for Power BI Desktop. Alternatively, you can enter https://aka.ms/pbidesktop into any browser bar and the Microsoft Store app will automatically open to the Power BI Desktop app, as shown in Figure 1.1.

It is important to make sure that you get the Power BI Desktop app and not just the Power BI app. The Power BI app is for viewing reports published to the Power BI service only.

Figure 1.1: The Power BI Desktop app in the Microsoft Store

2. Click the Get button.
3. Once the download and installation is complete, click the Open button.

Power BI Desktop is now installed, and you are ready to start leveraging this powerful tool for BI.

How it works

Power BI Desktop supports a rich set of configuration options both for the currently open file and all instances of Power BI Desktop on the given device. These options control the availability of the preview and end user features and define default behaviors’ resource usage, security, and privacy/isolation of data sources.

Regardless of the version of Power BI Desktop that is installed, these options are available by using the File menu in the ribbon, selecting Options and settings, and then Options, as shown in Figure 1.2.

Figure 1.2: Power BI Desktop Options and settings

Selecting Options brings up the Options window, displayed in Figure 1.3.

Figure 1.3: Power BI Desktop Options (February 2021)

GLOBAL options are applied to all Power BI Desktop files created or accessed by the user, while CURRENT FILE options must be defined for each Power BI Desktop file. The following steps are recommended for GLOBAL options:

1. On the Data Load tab, under Data Cache Management Options, confirm that the currently used data cache is below the Maximum allowed (MB) setting. If it is near the limit and local disk space is available, increase the value of Maximum allowed (MB). Do not clear the cache unless local disk space is unavailable, as this will require additional, often unnecessary, queries to be executed.

Figure 1.4: Global Data Load options

In addition, under the Time intelligence heading shown in Figure 1.4, consider turning off Auto date/time for new files. While it is perhaps convenient to have out-of-the-box calendar hierarchy functionality for date columns, this can also significantly increase the size of a data model, and it’s strongly recommended to utilize a common date dimension table, preferably from a corporate data warehouse source. Finally, under the Type Detection heading, it is recommended that you choose the Never detect column types and headers for unstructured sources option. The default detection of column types from unstructured sources, such as text or Excel files, will create a hardcoded dependency on the column names in the source file. Additionally, this default transformation will be applied prior to any filter expression and, thus, can require more time and resources to perform the refresh.

2. On the Power Query Editor tab, under the Layout header, ensure that Display the Query Settings pane and Display the Formula Bar are both checked, as seen in Figure 1.5.

Figure 1.5: Power Query Editor Layout settings

3. On the Security tab, under the Native Database Queries header, select the Require user approval for new native database queries option, as shown in Figure 1.6. Native queries are the user-specified SQL statements passed to data sources as opposed to the queries that Power BI generates internally.

Figure 1.6: Security option for Native Database Queries

Optionally, set Show security warning when adding a custom visual to a report as well. Custom visuals can be divided into three categories, as follows:

• Certified for Power BI (only on AppSource)
• Uncertified but available in AppSource
• Third-party and not available in AppSource

Certified custom visuals have been thoroughly tested for safety to ensure that the visuals do not access external services or resources, and that they follow secure coding practices. Uncertified visuals available in AppSource have been through a validation process, although there is no guarantee that all code paths have been tested or that no external services or resources are accessed. Third-party visuals not available in AppSource should be used with caution, and it is recommended that organizations establish policies and procedures regarding their use.

4. On the Privacy tab, under the Privacy Levels heading, configure the privacy levels for all data sources and enable the Always combine data according to your Privacy Level settings for each source option, as shown in Figure 1.7. Use the Learn more about Privacy Levels link for details on these settings.

5. Use the Diagnostics tab shown in Figure 1.8 to provide version information and diagnostics options if there is a need to troubleshoot a particular problem with Power BI Desktop. The Enable tracing option under the Diagnostic Options header writes out detailed trace event data to the local hard drive and, thus, should only be activated for complex troubleshooting scenarios.

6. Use the Preview features tab to enable preview features for evaluation purposes. The options under Preview features change with new versions, as some previous options become generally available and new preview features are introduced. The monthly Power BI Desktop update video and blog post (see https://powerbi.microsoft.com/en-us/blog/) provides details and examples of these new features. Once preview features are changed, a restart of the Power BI Desktop application is required.

Figure 1.9: Preview features available with the October 2023 release of Power BI Desktop

7. On the Data Load tab under CURRENT FILE, more experienced users should disable all of the options under the Type Detection and Relationships headings; these model design decisions should be implemented explicitly by the Power BI developer with knowledge of the source data.

Also, note that you can disable Auto date/time here on an individual file basis if you did not disable this option at a GLOBAL level.

Figure 1.10: Current File Data Load options

There’s more…

There are options for developing and editing Power BI reports, models, and source queries within the Power BI service. However, these web service experiences generally offer only a limited subset of the functionality of the dedicated client applications. Additionally, files created in Power BI Desktop and other client tools can be stored and managed with version control systems. For these reasons, it’s currently recommended to limit web service development to only those artifacts which do not have a dedicated client app such as dataflows.

For a variety of reasons, it may not be possible to install and use the Microsoft Store app version of Power BI Desktop. This may be because of an older version of Windows being run, corporate policies, a desire to use the 32-bit version of Power BI Desktop (the Microsoft Store app is 64-bit only), or you may be working with an on-premises version of Power BI Report Server. In these cases, you can download an executable installer (an .EXE file). To install the EXE version, follow these steps:

1. The executable installer version of Power BI Desktop can be downloaded on the Power BI downloads page: https://powerbi.microsoft.com/downloads.
2. Under Microsoft Power BI Desktop, choose Advanced download options.

Figure 1.11: Download the executable installer version using Advanced download options

3. Select your language preference and click the Download button.
4. Choose either the 64-bit version (PBIDesktopSetup_x64.exe) or the 32-bit version (PBIDesktopSetup.exe), and then click the Next button.
5. Once the file downloads, click Open file.
6. The Microsoft Power BI Desktop Setup Wizard will launch. Select a language and click the Next button. If prompted on whether you want to allow this app to make changes to your device, click the Yes button.
7. Complete the rest of the installation wizard, including accepting the license terms and destination folder, and clicking the Next button each time.
8. On the final screen, click the Install button.
9. Once the installation finishes, click the Finish button.

If you are using Power BI Report Server (on-premises), you need to download the corresponding version of Power BI Desktop optimized for Report Server (the RS version). Updates for Power BI Report Server come out quarterly, and there is a specific version of Power BI Desktop released simultaneously that is optimized to work with each quarterly version. The installation is the same as the executable installer (EXE) version. However, to find the download for the RS version, follow these steps:

1. Use a browser to navigate to https://powerbi.microsoft.com/report-server.
2. Use the Advanced download options link directly under the DOWNLOAD FREE TRIAL button.

Figure 1.12: Download the RS version using Advanced download options

After this, the instructions are the same as the executable installer version just covered. Pick up the installation steps from step 3.

See also

For additional information on the topics covered in this recipe, refer to the following links:

• Power BI security white paper: http://bit.ly/22NHzRS
• Data source privacy levels: http://bit.ly/2nC0Lmx
• Power BI auto date/time: http://bit.ly/3bH59cn
• Change settings for Power BI reports: http://bit.ly/2OP8m0F

Microsoft SQL Server is a popular relational database management system (RDBMS). Database software products such as SQL Server have the primary function of storing and retrieving data as requested by other software applications, which may run either on the same computer or another computer across a network.

SQL Server often serves as the source of data for Power BI reports. In addition, some of the recipes and examples in this book center around accessing the Microsoft AdventureWorks sample database hosted on SQL Server. This recipe will help you get this environment created so that you can follow along in those recipes.

Getting ready

To prepare for this recipe, download the Developer edition of SQL Server from the following link: https://www.microsoft.com/en-us/sql-server/sql-server-downloads. The downloaded file should be named SQL2022-SSEI-Dev.exe.

Also, download SQL Server Management Studio, which is used to view and configure Microsoft SQL Server. Download the SSMS-Setup-ENU.exe file from this link: https://aka.ms/ssmsfullsetup.

How to install SQL Server

First, we will install the Developer Edition of SQL Server, and then, SQL Server Management Studio.

Installing Developer Edition of SQL Server

To install SQL Server, use the following steps:

1. Run the file SQL2022-SSEI-Dev.exe.
2. If prompted by User Account Control, select Yes.
3. On the installation splash page, choose Basic.
4. Press the Accept button to agree to the terms and conditions.
5. Either click the Install button or choose a different folder by clicking the Browse button, and then click Install. The default installation location is C:\Program Files\Microsoft SQL Server.
6. When the installation completes, you will be presented with a screen similar to Figure 1.13:

Figure 1.13: Successful installation of SQL Server

7. Click the Close button.

Installing SQL Server Management Studio

1. Run the SSMS-Setup-ENU.exe file.
2. On the installation splash screen, click the Install button.
3. If prompted by User Account Control, select Yes.
4. If the installation fails, reboot your computer, and then run the SSMS-Setup-ENU.exe file again.
5. After the installation completes, click the Restart button to restart your computer and finish the installation.

Microsoft SQL Server as well as SQL Server Management Studio are now installed and ready for use.

How to install the AdventureWorks database

The AdventureWorks database is a sample database provided by Microsoft that is often used for demonstration and learning purposes. The database is designed to showcase the capabilities and features of Microsoft SQL Server. The AdventureWorks database has evolved over different versions of SQL Server, with the latest version at the time of this book’s publication being 2022.

The database represents a fictional company called AdventureWorks Cycles, a manufacturing company that produces and sells bicycles and related products. The AdventureWorks database includes various tables that model different aspects of the company’s operations, such as customers, sales, products, employees, and more.

Each version of the AdventureWorks database comes in three different backup (.bak) files:

• Online Transaction Processing (OLTP): The full dataset
• Data Warehouse (DW): A summarized version of the dataset for reporting purposes
• Lightweight (LT): A trimmed-down version of the OLTP dataset

For our purposes, we will use the data warehouse (DW) version of the AdventureWorks database.

To install and configure the AdventureWorks DW database, follow these steps:

1. Download the backup (.bak) file from GitHub using this link: https://bit.ly/3QTnCXI. The downloaded file is called AdventureWorksDW2022.bak.
2. Open Windows File Explorer and move the AdventureWorksDW2022.bak file from the Downloads directory to the C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup directory.
3. If not already open, open SQL Server Management Studio and connect to your local instance of SQL Server, as described in the How it works section of this recipe.
4. Expand the instance of SQL Server in the Object Explorer, and then right-click Databases and choose Restore Database…
5. On the General page, select Device, and then the ellipsis (…). In the Select backup devices window, click the Add button. Select the AdventureWorksDW2022.bak file and click the OK button. Click the OK button in the Select backup devices window, and finally, click the OK button on the General page. Use Figure 1.14 for guidance on executing these steps in sequence.

Figure 1.14: Restoring the AdventureWorksDW2022 backup file

6. Change to the Files page and select the checkbox for Relocate all files to folder, as shown in Figure 1.15:

Figure 1.15: Relocate all files to folder

7. Click the OK button once the database is restored successfully.

The AdventureWorksDW2022 database is now available in the Object Explorer under Databases, as shown in Figure 1.16:

Figure 1.16: Restored AdventureWorksDW2022 database

We will use this database in future recipes within this book to demonstrate some of the amazing capabilities of Power BI.

How it works

Microsoft SQL Server runs as a set of services on your computer. These services can be seen using the Windows Services application and include the following:

• SQL Server
• SQL Server Agent
• SQL Server Browser
• SQL Server CEIP service
• SQL Server VSS Writer

Multiple instances of SQL Server can run on the same computer. The default is to create an instance called MSSQLSERVER.

We can connect to this service using SQL Server Management Studio. To do this, start typing SQL Server Management Studio in the Windows search bar, and then open the application when it appears, as shown in Figure 1.17:

Figure 1.17: Running SQL Server Management Studio

On the Connect to Server window, ensure that Database Engine is selected for Server type, that the name of your local computer appears for Server name, and that the Authentication is set to Windows Authentication. Click the Connect button to connect to the local instance of SQL Server, as shown in Figure 1.18:

Figure 1.18: SQL Server Management Studio

See also

For additional information on the topics covered in this recipe, refer to the following links:

• Microsoft SQL Server documentation: https://bit.ly/3FVerji
• SQL Server Management Studio documentation: https://bit.ly/47x3nV8
• AdventureWorks sample databases: https://bit.ly/3Ue8y8J

Power BI Report Builder is a separate, free report authoring tool that is part of the Microsoft Power BI software suite. Power BI Report Builder is the evolution of the venerable Report Builder tool, which allows users to create paginated reports (also known as traditional SQL Server Reporting Services or SSRS reports). Reports authored in Power BI Report Builder can be published to and viewed in the Power BI service, similarly to traditional Power BI Desktop reports.

Getting ready

To get ready for this recipe, first download the Power BI Report Builder installer from the following link: https://www.microsoft.com/en-us/download/details.aspx?id=105942.

Once on the page in your web browser, choose your language, and then click the Download button to download the PowerBiReportBuilder.msi file.

How to install Power BI Report Builder

To install Power BI Report Builder, follow these steps:

1. Once PowerBiReportBuilder.msi is downloaded, click Open file in your Downloads pane or run the file from your Downloads directory.
2. On the welcome page, click the Next button.
3. Accept the license terms and click the Next button.
4. Choose a Destination Folder or accept the default and click the Next button.
5. Click the Install button.
6. If prompted by User Account Control, click the Yes button.
7. Once Power BI Report Builder is installed, click the Finish button.

Power BI Report Builder is now installed and ready to be used.

How it works

Unlike Power BI Desktop, which creates files with a PBIX extension, Power BI Report Builder saves report files as Report Definition Language (.rdl) files. RDL files are actually eXtensible Markup Language (XLM) files, and this is the same file format used by SQL Server Reporting Services (SSRS). Power BI Report Builder files can be published to either the Power BI service or Power BI Report Server, which is the successor to SQL Server Reporting Services.

Paginated reports operate quite differently than Power BI Desktop reports. Paginated reports are optimized for printing and enable the display of long tables or matrixes that span many report pages. This is different from how Power BI Desktop can only display a limited number of rows without scrolling.

Another key difference is that paginated reports have limited interactivity features compared with Power BI Desktop reports. In Power BI Desktop, clicking on visuals can influence and change the display of other visuals on a page. Paginated reports do not have such interactivity between visuals. Interactivity with paginated reports is done via Parameters, which function like Slicers in Power BI Desktop reports.

A final key differentiator between paginated reports and Power BI Desktop reports is that there are only a limited number of visuals, ~12 available for paginated reports, versus the dozens of default visuals available in Power BI Desktop and the hundreds of custom visuals available from AppSource.

To run Power BI Report Builder, start typing Power BI Report Builder in the Windows search bar, and then run the Power BI Report Builder app in the Windows Start menu. Next, follow these steps:

1. On the Getting Started window, select New Report and then Table or Matrix Wizard.
2. Make sure that the Create a dataset radio button is selected, and then click the Next button.
3. Click the New… button to start creating a new dataset.
4. In the Data Source Properties window, click the Build… button.
5. On the Connection Properties pane, type localhost or the name of your local computer for Server name. Depending on your circumstance, you may also need to include your SQL instance name in the form “server\instance”. Type AdventureWorksDW2022 or enter another database name.
6. You can test the connection using the Test Connection button, and if successful, click the OK button.
7. Back on the Data Source Properties window, click the OK button.
8. On the New Table or Matrix window, click the Next button.
9. In the Database view pane, select the FactInternetSales table, and then click the Next button.
10. Drag the SalesAmount field into the Values field and the DueDate field into the Row groups field, and then click the Next button.
11. On the next window, click the Next button once again.
12. Finally, click the Finish >> button.
13. The report should now look like Figure 1.19.

Figure 1.19: Simple paginated report

14. Click the Run button to display the report.

Notice at the top of the displayed report that you can navigate through the multiple pages of the report, using the page navigation buttons within the Navigation section of the ribbon.

The Design button has replaced the Run button in the ribbon. Click the Design button and observe the Publish button on the far right of the ribbon, as shown in Figure 1.19. Clicking the Publish button prompts you to Sign in to the Power BI service.

See also

For additional information on the topics covered in this recipe, refer to the following links:

• What is Power BI Premium?: https://bit.ly/3QY3J0L
• What is Power BI embedded analytics?: https://bit.ly/3QY4fgg

The Microsoft on-premises data gateway (or simply gateway) is a Windows service that runs in on-premises environments or as an infrastructure-as-a-service (IaaS) resource running in the cloud. The sole purpose of the gateway is to facilitate secure (encrypted) and efficient data transfer between on-premises and IaaS data sources and Microsoft Azure services, such as Power BI, Power Apps, Power Automate, Azure Analysis Services, and Azure Logic Apps, via an outbound connection to Azure Service Bus. It is important to note that if all data sources used in Power BI reports are in the cloud (and so not on-premises) and accessible by the Power BI service, then the Microsoft on-premises data gateway is not required, as the Power BI service will use native cloud gateways to access native cloud data sources; this could be something such as Azure SQL Database or Azure SQL Managed Instances.

Once installed, a gateway can be used to schedule data refreshes of imported Power BI datasets, to support Power BI reports and dashboards built with DirectQuery, plus live connections to Analysis Services databases. The gateway can also be used by Power Apps, Azure Logic Apps, Power Automate, Azure Analysis Services, and Dataflows.

A single on-premises data gateway can support the refresh and query activity for multiple data sources, and permission to use the gateway can be shared with multiple users. Currently, the gateway supports all common data sources via scheduled imports, including Open Database Connectivity (ODBC) connections, and many of the most common sources via Live Connection and DirectQuery.

Getting ready

The hardware resources required by the gateway vary based on the type of connection (import versus live connection, for example), the usage of the reports and dashboards in the Power BI service, and the proportion of data volume handled by the gateway versus the on-premises source systems. It is recommended to start with 8-core CPUs, 8 GB of RAM server, and a 64-bit version of Windows 2012 R2 or later for the operating system. A solid-state drive is also recommended with at least 4 GB of disk space. This machine cannot be a domain controller, and to maintain the availability of Power BI content, the gateway server should always be on and connected to the internet.

Another top consideration for the gateway is the location of the gateway server in relation to the Power BI tenant and the data sources to be supported by the gateway. For example, if a SQL Server database is the primary gateway source and runs on a server in the Western United States, and the Power BI tenant for the organization is in the West US Azure region, then the gateway should be installed on a server or potentially an Azure virtual machine (VM) in the West US Azure region, or a location in the Western United States.

Based on an analysis of current and projected workloads, the gateway resources can be scaled up or down, and optionally, additional gateways can be installed on separate servers to distribute the overall Power BI refresh and query deployment workload.

For example, one gateway server can be dedicated to scheduled refresh/import workloads, thus isolating this activity from a separate gateway server responsible for DirectQuery and Live Connection queries.

The gateway does not require inbound ports to be opened and defaults to HTTPS, but it can be forced to use TCP. For the default communication mode, it is recommended to whitelist the IP addresses in your data region in your firewall. This list is updated weekly and is available via the Microsoft Azure Datacenter IP list (https://bit.ly/40APL97).

To prepare for this recipe, perform the following steps:

1. Download the latest Microsoft on-premises data gateway from https://powerbi.microsoft.com/downloads/.

Figure 1.20: Download the Microsoft on-premises data gateway

2. Select the Download standard mode link.

You are now ready to install the gateway.

How to create the gateway

The gateway has two modes, Standard mode and Personal mode. Personal mode is intended for personal use and, thus, cannot be shared among users within an enterprise. We will focus on Standard mode, as the installation and configuration are essentially the same for Personal mode:

1. Once the gateway is downloaded, choose Open file to run GatewayInstall.exe.
2. Choose the file directory for the installation, accept the terms of use and privacy agreement, and then click the Install button. If prompted on whether you want to allow this app to make changes to your device, click the Yes button.
3. Sign in to the Power BI service to register the gateway:

Figure 1.21: Registering the gateway

4. On the next screen after signing in, choose Register a new gateway on this computer, and then click the Next button.
5. Enter a user-friendly name for the gateway and a recovery key, and then click the Configure button.

Figure 1.22: Final configuration information for the gateway

With the gateway installed and registered, data sources, gateway admins, and authorized data source users can be added to the Power BI service. A Manage Gateways option will be available under the gear icon in the Power BI service. Managing gateways is covered in a later chapter of this book.

Figure 1.23: Successfully installed gateway

How it works

As new versions of the gateway become available, a notification is made available in the Status tab of the on-premises data gateway UI, as per Figure 1.23. The Power BI gateway team recommends that updates should be installed as they become available.

The Standard mode on-premises data gateway, rather than the Personal mode gateway, is required for the Import and DirectQuery datasets created in this book and the use of other Azure services in the Microsoft business application platform.

The Power BI service uses read-only connections to on-premises sources, but the other services (for example, Power Apps) can use the gateway to write, update, and delete these sources.

The recovery key is used to generate both a symmetric and an asymmetric key, which encrypts data source credentials and stores them in the cloud. The credentials area is only decrypted by the gateway machine in response to a refresh or query request. The recovery key will be needed in the following scenarios:

• Migrating a gateway and its configured data sources to a different machine
• Restoring a gateway to run the service under a different domain account or restoring a gateway from a machine that has crashed
• Taking over ownership of an existing gateway from an existing gateway administrator
• Adding a gateway to an existing cluster

There’s more…

The final configuration screen for the Microsoft on-premises data gateway shown in Figure 1.22 provides several advanced options, including the following:

• Add to an existing gateway cluster
• Change region
• Provide relay details

Gateway clusters remove single points of failure for on-premises data access. Since only a single standard gateway can be installed on a computer, each additional gateway cluster member must be installed on a different computer. If the primary gateway is not available, data refresh requests are routed to other gateway cluster members. When using gateway clusters, it is important that all gateway cluster members run the same gateway version and that offline gateway members are removed or disabled—offline gateway members will negatively impact performance. You must know and enter the Recovery Key for the previously installed gateway when joining a cluster.

By default, the gateway is installed in the same Azure region as your Power BI tenant in the Power BI service. In almost all cases, this setting should not be changed. However, with multi-geo support in Power BI Premium, it may be necessary to install gateways in different Azure regions. The Change Region option provides this flexibility.

Azure relays are automatically provisioned for installed gateways at the time of installation. However, the Provide relay details option allows you to provide your own relay details, if you wish to associate the relay with your Azure subscription and manage the sender and listener keys for the relay. This is a very advanced option, and you should fully understand Azure relays and what you are trying to accomplish before attempting this configuration. It is important to note that only WCF relays with NetTcp are supported for this feature.

See also

• Details of configuring and managing data sources through the on-premises gateway are covered in Chapter 11, Deploying and Distributing Power BI Content
• Set the Azure relay for an on-premises data gateway: http://bit.ly/3rMJMvP

Power BI professionals responsible for the development of semantic models (datasets) routinely utilize additional tools beyond Power BI Desktop to create and manage their models. For example, they use Tabular Editor to quickly implement changes to measures or add new objects, such as perspectives and calculation groups. Likewise, ALM Toolkit is routinely used to deploy incremental and metadata-only changes to Power BI.

Another such tool is DAX Studio, a third-party tool used to query data models, edit and format code, browse the structure and metadata of data models, and analyze the performance and execution characteristics of DAX queries. For larger and more complex data models and expressions, as well as projects involving multiple models, DAX Studio becomes an essential supplement to the development and performance-tuning processes.

Data Analysis Expressions (DAX) is the “language of Power BI,” as it is used to create the measures and queries visualized in Power BI reports and dashboards. Power BI generates and submits DAX queries to the source data model based on the structure of the visualization, user selections, and filters—just as other tools such as Excel generate MDX queries based on the selections and structure of pivot tables and slicers from workbooks. DAX expressions are also used to define security roles and can optionally be used to create columns and tables in data models, based on other tables and columns in the model, which can be refreshed at processing time and used by measures and queries. DAX serves the same function in Azure Analysis Services (AAS) and SQL Server Analysis Services (SSAS) tabular models, as well as Power Pivot for Excel models. It’s essential that BI professionals have a robust tool to develop and analyze DAX code and the data models containing these expressions.

Getting ready

To prepare for this recipe, follow this step:

1. Download the current release from the DAX Studio website: https://daxstudio.org/downloads/. The installer version is a single file that provides a wizard-based installation and setup, while the portable version is a ZIP file that does not provide a wizard-based installation. The installer version is recommended.

You are now prepared to install DAX Studio.

How to install DAX Studio

1. Use the Open file link or run the file from your Downloads folder. The file will be named something like DaxStudio_3_0_10_setup.exe.
2. Once the installation starts, select the option Install for all users (recommended).
3. If prompted by User Account Control, select the Yes button.
4. Accept the license agreement and click the Next button.
5. Choose a folder path to install the tool and click the Next button.
6. Choose whether the DAX Studio add-in for Excel will also be installed. Click the Next button. Note that:
   1. The add-in for Excel is required to connect to Power Pivot for Excel data models.
   2. Additionally, when DAX Studio is opened from Excel, query results can be exported directly to Excel tables.
7. Select the Start menu folder (the default is DAX Studio), and then click the Next button.
8. Check the Create a desktop shortcut box and leave the Privacy checkbox unchecked. Click the Next button.
9. Finally, click the Install button.

How it works

Upon full installation, including the add-in for Excel, launching Power BI Desktop will display an additional menu choice, External tools, in the ribbon. Selecting External tools in the ribbon displays all installed external tools, as shown in Figure 1.24:

Figure 1.24: DAX Studio in the Add-Ins ribbon in Excel

Clicking the DAX Studio icon from within Power BI Desktop launches DAX Studio and automatically connects to the current Power BI dataset.

If the Excel add-in was chosen during installation, a DAX Studio icon also appears on the Add-Ins tab in the Excel ribbon.

External tools for Power BI use a JavaScript Object Notation (JSON) file, stored in the directory C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools. These files have a .pbitool.json file extension, such as DAX Studio’s daxstudio.pbitool.json file. These files have the following format:

{
  "version": "1.0",
  "name": "DAX Studio",
  "description": "Use DAX Studio for DAX authoring, diagnosis, performance tuning and analysis.",
  "path": "C:\\Program Files\\DAX Studio\\DaxStudio.exe",
  "arguments": "/server=\"%server%\" /database=\"%database%\"",
  "iconData": "image/svg+xml;base64, … "
}

This information is read by Power BI Desktop upon starting and populates the External tools toolbar in the ribbon. This means that installing or uninstalling external tools requires Power BI Desktop to be restarted before the changes are reflected within the program.

There’s more…

There are two additional tools that are useful for Power BI, namely:

• ALM Toolkit
• Tabular Editor

ALM Toolkit is a third-party tool from MAQ Software that provides advanced features such as Power BI dataset comparison, code merging, partial deployments and bug fixes, source control integration for dataset metadata, and definition reuse between tabular models. To download and install ALM Toolkit, follow these steps:

1. In a browser, navigate to http://alm-toolkit.com.
2. Near the top of the page, click the DOWNLOAD LATEST VERSION button.
3. Use the Open file link to launch AlmToolkitSetup.msi or open the file from your Downloads folder.

Figure 1.25: Initial installation screen for ALM Toolkit

4. Click the Next button on the initial installation screen.
5. Accept the license agreement and click the Next button.
6. Choose a folder path and click the Next button.
7. On the final installation screen, click the Next button to start the installation.
8. If prompted by User Account Control, click the Yes button.

The Power BI ALM Toolkit can now be launched from the Windows Start menu or Power BI Desktop’s External tools toolbar.

Another powerful tool is Tabular Editor. At the time of this book being published, Tabular Editor comes in two versions, the free, open source version 2 and the commercial version 3. Tabular Editor is an alternative to SQL Server Data Tools (SSDT) for authoring and editing tabular models for Analysis Services. Tabular Editor provides a hierarchical view of the objects in your tabular model metadata, such as columns, measures, and hierarchies. Tabular Editor integrates with Power BI Desktop, allowing batch changes to DAX measures and enabling advanced capabilities, such as calculation groups and perspectives. Finally, Tabular Editor also enables offline editing capabilities by allowing you to open the tabular model directly from Model.bim files. To install and use Tabular Editor, follow these steps:

1. Download the latest Tabular Editor from here: https://bit.ly/3bJFBvl.
2. At the top of the page, click the Windows installer version, such as TabularEditor.2.24.0.Installer.msi. The exact version number may vary.
3. Once the file downloads, use the Open file link or open the file from your Downloads folder.

Figure 1.26: Initial installation screen for Tabular Editor

4. Click the Next button on the initial installation screen.
5. Accept the license agreement and click the Next button.
6. Choose a folder path and click the Next button.
7. Check the boxes Create Desktop shortcut and Create Program Menu shortcut, and then click the Next button.
8. On the final installation screen, click the Next button to start the installation.
9. If prompted by User Account Control, click the Yes button.
10. Once installation is complete, click the Close button.

Tabular Editor can now be launched from the Windows Start menu or from Power BI Desktop’s External tools toolbar.

See also

• DAX Studio tutorials and documentation: https://daxstudio.org/docs/intro/
• ALM Toolkit documentation: http://alm-toolkit.com/HowToUse
• Tabular Editor documentation: https://docs.tabulareditor.com/

Power BI offers a flexible licensing model including per user and per capacity options that allow organizations to provision licenses according to their needs, budgets, and preferences. As with other modern cloud services, organizations can align their scalability and feature requirements with corresponding Power BI licenses to both meet their business requirements at minimum expense.

Fundamentally, Power BI licensing options can be categorized across two dimensions: who/what is licensed (users versus capacity) and what features come with those licenses (Power BI Premium vs. Power BI Pro). Organizations can acquire and assign licenses exclusively on a per user basis such as Power BI Pro and Power BI Premium per User or they can provision a Premium or Fabric capacity pool of resources to support the read only needs of large numbers of end users or a mix of per user and capacity resources can be provisioned to target specific groups and solutions within an organization.

Power BI Premium per User and Power BI Premium or Fabric capacity are more expensive than Power BI Pro licenses but they provide greater performance, scalability, and access to features many consider essential for enterprise deployments including deployment pipelines.

Note that Microsoft is in the process of overhauling Power BI licensing now that Microsoft has introduced Fabric. These changes will continue to occur, and so some of the specific information in this recipe may change substantially in the future. However, the overall process of determining the correct licensing for an organization will remain the same.

Getting ready

To help an organization determine the most suitable Power BI licensing model, you should gather information about the organization’s user needs and technical requirements, such as:

• The number of users: Determine the total number of report authors and report viewers.
• Usage scenarios: Determine how Power BI will be used within the organization. Does the organization intend to use Power BI for internal reporting, external sharing, embedded analytics within applications, or a combination of these usage scenarios?
• The volume of data: What is the expected total size of the Power BI datasets? What is the estimated size of the largest Power BI dataset required by the organization?
• Performance: Is the performance of Power BI reports and dashboards extremely important to the organization?
• Budget constraints: Determine the organization’s budget for Power BI licensing.
• An on-premises or cloud preference: Is the organization required to keep certain data on-premises and not in the cloud?
• External sharing: Is there a need to share reports and dashboards with external users?
• Embedded analytics: Does the organization plan on embedding Power BI reports into applications or websites?
• Other Microsoft licensing: Does the organization already use Microsoft 365 or other Power Platform services? Certain Microsoft licensing bundles include Power BI licenses.
• Future growth plans: Understand the organization’s growth plans and whether the chosen licensing model can scale accordingly.

It is crucial to work closely with key stakeholders, such as business users, decision makers, and technology administrators to ensure that the chosen licensing model aligns with an organization’s specific goals and requirements. Consulting with Microsoft or a Cloud Solution Provider (CSP) licensing expert can also provide valuable insights and guidance.

How to license Power BI

Since Power BI Desktop, the primary tool for creating reports is always free; the licensing aspect of Power BI only surfaces when utilizing the Power BI service within Microsoft’s cloud environment. This Power BI service licensing can be quite complex. The first step in understanding Power BI licensing is to understand that Power BI can be licensed as shared capacity or as dedicated capacity.

Shared capacity is like an apartment building. While each tenant in the building has their own personal living quarters accessible to only themselves, certain infrastructure such as plumbing, electrical wiring, and stairways are common to everyone in the building. Shared capacity for Power BI is similar. Each tenant within the Power BI Service has their own area for publishing data and reporting assets, but infrastructure such as memory and processing capacity are shared among the tenants. Thus, just like a noisy neighbor in an apartment building can affect other tenants, so too can tenants within shared capacity in the Power BI Service impact performance for other tenants. Thus, if the performance of Power BI reports and dashboards is extremely important to an organization, that organization may wish to consider dedicated capacity.

Dedicated capacity licenses reserve memory and processing capacity solely for the use of a particular tenant. In addition, these licenses enable advanced features such as larger datasets, increased user quotas, more frequent dataset refreshes, and advanced machine learning and artificial intelligence capabilities.

There are three licensing options for shared capacity. These licensing options are as follows:

• Free
• Pro
• Premium Per User (PPU)

The free license allows reports to be published within the Power BI service to an individual’s My Workspace workspace. My Workspace is a special Power BI workspace that is private to each user. However, with only a free license, these reports cannot be shared with others and are viewable only to the author of the reports.

The sole exception to this is the Publish to Web feature, which creates an anonymous link to the report that anyone can access. Obviously, this lacks security and should only be used for demonstration purposes or non-sensitive data.

The Pro license provides the standard Power BI Service features, including the ability to publish and share reports with other users and view reports shared by others. A Pro license also enables users to schedule report refreshes 8 times per day per report, and it allows a maximum dataset model size of 1 GB, with up to 10 GB of dataset model storage per user. Power BI Pro licenses are sold for $10 per user per month.

PPU licenses enable features normally reserved for dedicated capacity licensing, including larger model sizes (100 GB), increased storage per user (100 TB/user), an increased number of refreshes (48 per day), the ability to use artificial intelligence and machine learning with dataflows, and the ability to use the XML for Analysis (XMLA) endpoint to connect to datasets via SQL Server Management Studio or other software. All users accessing a PPU workspace must have a PPU license. In addition, PPU workspaces still run in shared capacity, not dedicated capacity. PPU licenses are sold for $20 per user per month.

Dedicated capacity comes in two different forms:

• Premium
• Embedded

Premium capacity licensing has been retired by Microsoft. Information regarding legacy Premium capacity licensing is covered here briefly for sake of completeness and for comparison to new Fabric licensing. Premium capacity is purchased as P stock keeping units (SKUs). P SKUs range from P1 to P5, with each increment doubling the CPUs and memory of the previous SKU. The price per capacity per month is also double. Thus, a P4 SKU is eight times the price, has eight times as many CPUs, and has eight times the memory of a P1 SKU. A P1 SKU starts at a retail price of $4,995 per month and comes with 8 virtual CPU cores (v-cores) and 25 GB of memory. The available memory of a capacity limits the maximum dataset size of a Power BI model. Using any Premium capacity means that only report authors need to have a Power BI Pro license. Users who only need to view reports only require a free Power BI license.

Power BI Embedded is intended for independent software vendors (ISVs) and organizations that wish to embed Power BI content within external-facing web applications. Embedded SKUs are called “A” SKUs and come in sizes ranging from A1 to A8. An A4 SKU has v-cores and memory equivalent to a P1 SKU, and each increment above an A4 SKU doubles the amount of v-cores and memory available. Each increment is also double the cost. However, A SKUs are billed per hour, not as a fixed monthly fee, meaning that the capacity can be stopped in order to save costs. There are also A1, A2, and A3 SKUs available, which come with 1, 2, and 4 v-cores and 3 GB, 5 GB, and 10 GB of memory, respectively.

How it works

For smaller organizations that wish to publish and share reports, Power BI Pro is the logical choice. At $10 per user per month, the pricing is attractive to organizations with fewer than 500 individuals using Power BI. If the additional features and functionality of Premium are needed, smaller organizations can also choose a PPU license (for a full list of plans, refer to the Power BI pricing and product comparison link in the See also section of this recipe).

Once 500 or more individuals within an organization regularly access Power BI reports in the service (or 250 if an organization uses PPU licenses), it may make sense to move to Power BI Premium capacity. With Premium, only the capacity fee is charged per month, along with any report publisher licenses which still require a Power BI Pro license. However, all other Power BI users who simply view reports in the service only need a free license.

ISVs who wish to expose Power BI content within their applications and service external users should choose Power BI Embedded licensing.

There’s more…

In addition to the licensing SKUs covered thus far, there is also a special Premium license called “EM” SKUs. EM SKUs are intended to provide Power BI embedding capabilities for an organization (internal users only). EM SKUs come in three sizes, EM1, EM2, and EM3, and have equivalent v-cores and memory as their A SKU counterparts.

Finally, Microsoft is starting to roll out Microsoft Fabric pricing. Microsoft Fabric is a superset of Power BI functionality that includes what were previously Azure Synapse workloads, such as Data Warehouse, Data Engineering (data lakes), Data Science (Python notebooks), and Data Factory, as well as additional new workloads such as Data Activator for advanced data alerting capabilities.

Fabric pricing is split into capacity and storage, billed separately. This is different than legacy Premium capacity, which includes fixed capacity in the form of v-cores and memory, as well as a set amount of maximum storage space (100 TB per capacity). Fabric capacity pricing is based on “F” SKUs and ranges between the smallest, F2 SKU, and the largest, F2048 SKU. F SKUs double in capacity and cost with each increment. However, unlike Premium and Embedded SKUs, the number designation for each F SKU also doubles. This means that while there is an F4 SKU and an F8 SKU, there is no F3 SKU or F5–F7 SKUs. Therefore, an F8 SKU has four times the capacity of an F2 SKU and also costs four times as much per hour.

Like A SKUs, F SKUs are billed on a pay-as-you-go basis, meaning that you can stop the capacities or scale capacities down to reduce charges. As mentioned, storage is billed separately as OneLake storage and billed based on GB used per month.

Finally, F SKUs of 64 and above do not require a Pro license for reading reports within assigned workspaces. In effect, an F64 SKU is essentially equivalent to the legacy P1 SKU, an F128 SKU is equivalent to a P2 SKU, and so on.

See also

For additional information on the topics covered in this recipe, refer to the following links:

• Important update coming to Power BI Premium licensing: https://bit.ly/3VVM6mg
• Power BI pricing and product comparison: https://bit.ly/4aPutbL
• What is Power BI embedded analytics?: https://bit.ly/3QY4fgg
• Microsoft Fabric concepts and licensing: https://bit.ly/46cj0R6

In this chapter, we walked through the installation and configuration of the primary tools that BI professionals utilize to design and develop Power BI content, including official Microsoft tools such as Power BI Desktop, Power BI Report Builder, the on-premises data gateway, and third-party tools such as DAX Studio, ALM Toolkit, and Tabular Editor. These tools, coupled with the Power BI service, are the primary resources needed by BI professionals to be productive with Power BI. In addition, we also covered installing Microsoft SQL Server and the AdventureWorks database, which will be used in a variety of recipes throughout this book.

Finally, we discussed how to properly license Power BI for an organization, depending on its size and requirements.

This chapter sets the stage for the rest of this book, as we explore the powerful analytic capabilities of Power BI. In the next chapter, we begin this exploration of Power BI’s capabilities by looking at how to access and retrieve data.

Join our community on Discord

Join our community’s Discord space for discussions with the author and other readers:

https://discord.gg/ebXZqyrxJb