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 BI professionals utilize to design and develop Power BI content including Power BI Desktop, the On-Premises Data Gateway, DAX Studio, and the Power BI Publisher for Excel. Additionally, as 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 of these tools for your projects.

It's assumed that the reader has access to a Power BI Pro license, rights to download and install (or allow installation) 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 is outside the scope of this book.

Power BI Desktop is the primary tool used to develop the visual and analytical content which can then be deployed and collaborated on in the Power BI Service and optionally 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 Get Data and Query Editor experience, with its underlying M language and data mashup engine
• The SQL Server Analysis Services (SSAS) tabular data modeling engine and its DAX analytical language
• The interactive reporting and visualization engine formerly known as Power View

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

Most organizations set policies restricting downloads of software from the internet and many choose to centrally distribute a specific version of Power BI Desktop. For example, the March 2017 version of Power BI Desktop would be available on a corporate IT portal and it would be the approved version for 1-2 months while the April 2017 version is internally evaluated. Additionally, BI organizations may define policies restricting the use of native queries, custom visualizations, and establishing source privacy level settings.

Power BI Desktop is a relatively large download at 110 MB but can be installed simply and provides an intuitive Options and Settings interface for configuration.

1. Download the Power BI Desktop installer package. The Windows installer package (.msi) can be downloaded from the Power BI Service or from the Power BI downloads page (https://powerbi.microsoft.com/en-us/downloads/).

The Download dropdown, along with Settings and Notifications, is available in the upper-right corner of the Power BI Service window. The On-Premises Data Gateway and Power BI Publisher for Excel described later this chapter are also available for download.

The web service will determine whether the 64-bit (x64) or 32-bit version of Power BI Desktop is appropriate for your machine's operating system. If Power BI Desktop has already been installed on your machine and notifications of new versions are enabled, you will have the option to initiate the download of the latest Power BI Desktop version when it's available. Notifications of new versions are enabled by default and available under Global Options | Updates.

2. Install Power BI Desktop. Launch the installation wizard from the .msi package and complete the installation after accepting the license agreement and choosing the file directory.

Developers of Power BI content should become familiar with the settings available in Power BI options and data source settings as these configurations determine available functionality, user interface options, default behaviors, performance, and the security of the data being accessed.

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, 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 the 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 at design time.

2. On the Query Editor tab, display both the query settings pane and the formula bar. This will allow for greater visibility to the structure and specific M functions utilized by individual queries.

3. On the DirectQuery tab, enable the Allow unrestricted measures in DirectQuery mode setting.

4. On the Security tab, select the option to require user approval for new native database queries. Native queries are the user-specified SQL statements passed to data sources as opposed to the queries Power BI generates internally.

• Optionally, set Show security warning when adding a custom visual to a report as well.

5. On the privacy tab, configure the privacy levels for all data sources and enable the option to Always combine data according to your Privacy Level settings for each source. See How it Works... for details on these settings.

6. From the Data Source settings, select an individual source and choose Edit Permissions to configure the privacy level:

7. Enable Preview features for evaluation purposes:

8. On the Data Load tab for the CURRENT FILE, disable the automatic detection of column types and relationships. These model design decisions should be implemented explicitly by the Power BI developer with knowledge of the source data.

• As the current file options, it's necessary to apply these settings with each Power BI Desktop file to avoid default behaviors.

Defining data source privacy levels such as Organizational or Private prevents the data from these sources being exposed to external or inappropriate data sources during data retrieval processes. For example, if a query calls for merging a Private data source with a Public data source, the join operation will be executed locally--the private data will not be sent to the public source. In the absence of Privacy Level settings set for data sources, the M query engine will look to optimize for performance by utilizing source system resources.

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 provides details and examples of these new features. Usually a restart of the Power BI Desktop application is required once a new preview option has been activated, and tooltips and dialogs in Power BI Desktop will advise you if a preview feature is being used.

The enable tracing option in the Diagnostic Options section writes out detailed trace event data to the local hard drive and thus should only be activated for complex troubleshooting scenarios.

As a modern cloud and service-oriented analytics platform, Power BI delivers new and improved features across its toolset on a monthly cadence. These scheduled releases and updates for Power BI Desktop, the Power BI Service, the On-Premises Data Gateway, Power BI mobile applications, and more reflect customer feedback, industry trends, and the Power BI team's internal ideas and product roadmap.

BI professionals responsible for developing and managing Power BI content can both stay informed of these updates as well as review detailed documentation and guidance on implementing the features via the Microsoft Power BI Blog (http://bit.ly/20bcQb4), Power BI Documentation (http://bit.ly/2o22qi4), and the Power BI Community (http://bit.ly/2mqiuxP).

The Power BI Community portal provides a robust, searchable hub of information across common topics as well as an active, moderated forum of user experiences and troubleshooting. The community also maintains its own blog featuring examples and use cases from top community contributors, and links to local Power BI User Groups (PUGs) and relevant events such as Microsoft Data Insights Summit.

Although Power BI Desktop and Excel 2016 both contain the same data transformation and modeling BI engines (M and DAX, respectively), features exclusive to Power BI Desktop and the features in the Power BI Service exclusive to datasets created from Power BI Desktop create an incentive to migrate existing Excel data models and queries to Power BI Desktop. At the time of writing, those top incremental features and benefits are the following:

The documentation and the official Power BI Security white paper are available here: http://bit.ly/22NHzRS and detailed documentation on data source privacy levels is available here: http://bit.ly/2nC0Lmx