Configuring Power BI Tools

Power BI is a suite of business analytics tools and services that work together to access data sources, shape, analyze, and visualize data, and share insights. Although not all tools are required for all projects or deployments of Power BI, synergies are available by utilizing the unique features of multiple tools as part of integrated solutions encompassing diverse data sources and visualization types.

In this chapter, we walk through the installation and configuration of the primary tools and services business intelligence (BI) professionals utilize to design and develop Power BI content. This includes official Microsoft tools such as Power BI Desktop, the on-premises data gateway, and Analyze in Excel, as well as third-party tools such as DAX Studio, the ALM Toolkit, and Tabular Editor. Since Power BI tools and services are regularly updated with new features and enhancements, resources are identified to stay up to date and to best take advantage...

Technical Requirements

It is assumed that the reader has access to a Power BI Pro license, rights to download and install the development tools on their machine, and has the necessary access and rights to deploy and manage content in the Power BI service and utilize the Power BI mobile applications.

Power BI licensing options and assigning and managing these rights are outside the scope of this book. In addition, the following are required to complete the recipes in this chapter:

• A Windows PC running Windows 10 version 14393 or higher. There is no Power BI Desktop for Apple computers or operating systems.
• Microsoft Excel.
• Ensure that you have 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.
• A Power BI login for the Power BI service.

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

• .NET Framework 4.7.2 (Gateway...

Installing and Configuring Power BI Desktop

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...

Creating an On-Premises Data Gateway

The Microsoft on-premises data gateway (or simply gateway) is a Windows service that runs in on-premises environments or in infrastructure-as-a-service (IaaS) data sources running in the cloud, such as virtual machines running SQL Server databases. The sole purpose of the gateway is to support 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, 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...

Installing Analyze in Excel from the Power BI Service

Excel-based data analysis and reporting artifacts, such as PivotTables, charts, and cell range formula expressions with custom formatting, remain pervasive in organizations. Although a significant portion of this content and its supporting processes can be migrated to Power BI, and despite the many additional features and benefits this migration could provide, Power BI is not intended as a replacement for all Excel-based reporting. Organizations, and in particular those departments that use Excel extensively (such as Finance and Accounting), may prefer to leverage these existing assets and quickly derive value from Power BI by both deploying Excel content to Power BI and analyzing Power BI-hosted data from within Excel.

The Power BI service's Analyze in Excel feature replaces the deprecated Microsoft Power BI Publisher for Excel. The Analyze in Excel feature allows you to use Power BI datasets in Excel and use Excel features...

Installing and Configuring Additional Tools

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, the 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.

DAX (Data Analysis Expressions) is the "language of Power BI," as it is used...

Conclusion

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, the On-premises data gateway, and Analyze in Excel, as well as third-party tools such as DAX Studio, the 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. Later chapters of this book explore the use of these tools in developing and enhancing Power BI solutions.