Integrating Power BI with Other Applications

Power BI tools and services including Power BI Desktop, the Power BI web service, and Power BI mobile applications form a modern, robust business intelligence and analytics platform by themselves. Power BI Premium further extends the scalability and deployment options of Power BI solutions, enabling organizations to deliver Power BI content to large groups of users via apps in the Power BI service, embedded within custom applications, the on-premises Power BI Report Server, or some combination of these distribution options.

However, many organizations either already have extensive self-service and corporate BI assets and skills in other tools, such as Excel, Analysis Services (Azure Analysis Services (AAS) or SQL Server Analysis Services (SSAS)), and SQL Server Reporting Services (SSRS), or are interested in utilizing the unique features of these tools as part of their Power BI solutions. As one example, an organization may choose to...

Technical Requirements

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

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

Integrating SSRS and Excel

Power BI Desktop is the primary report authoring tool for content published to the Power BI service as well as for Power BI report visuals embedded in custom applications. However, for many organizations a significant portion of existing or legacy reporting workloads built with SSRS and Excel must be maintained. In many cases, existing SSRS and Excel reports can be converted to modern Power BI reports and dashboards but Power BI is not intended as a full replacement for all the features and use cases for these other tools. In addition to supporting paginated reports (aka SSRS reports) via Power BI Premium and data refresh of Excel reports built against Power BI datasets, the Power BI service offers further methods of integrating content from Excel and SSRS into Power BI. Additionally, given the common database engine and DAX language of Power BI, Power Pivot for Excel, and Analysis Services, BI teams can take full control of reports rendered in SSRS and...

Migrating from Power Pivot for Excel Data to Power BI

As Power BI has become more mature as a product and as business users become more comfortable with the platform, it is often beneficial to migrate data models (formerly Power Pivot) and M queries from Excel to Power BI. From a data management and governance standpoint, it is preferable to consolidate data models to either Power BI and/or Analysis Services models and to limit Excel's role to ad hoc analysis such as pivot tables connected to datasets in the Power BI service via Analyze in Excel.

In this brief recipe a data model and its source M queries contained in an Excel workbook are migrated to a Power BI dataset via the Import Excel Workbook to Power BI Desktop migration feature. Additional details on the workbook content imported and other options and considerations for Excel-to-Power BI migrations are included in the How it works... and There's more... sections.

Getting ready

Analyze the Excel workbook...

Accessing and Analyzing Power BI Datasets in Excel

With a centralized and potentially certified Power BI dataset hosted in the Power BI service, Excel users with both Free and Pro licenses can take full advantage of Excel's familiar interface as well as advanced features and use cases such as cube formulas and custom DAX queries.

Although these Excel reports, like SSRS paginated reports, are only a supplement to the Power BI reports and dashboards in the Power BI service, they are often useful for scorecard layouts with custom formatting and many measures and columns.

In this scenario, an experienced Excel user with deep business knowledge can leverage the performance, scale, and automatic refresh of the published Power BI dataset to create custom, fully formatted Excel reports. Additionally, the Excel report author has the flexibility to apply report-scoped logic on top of the dataset using familiar techniques and these customizations can inform BI teams or dataset...

Building Power BI Reports into PowerPoint Presentations

Microsoft PowerPoint remains a standard slide presentation application and the integration of data analyses and visualizations from external tools is very commonly an essential component of effective presentation decks. In response to the volume of customer requests, the Power BI service supports the ability to export Power BI reports as PowerPoint files. Each page of the Power BI report is converted into an independent PowerPoint slide and the Power BI service creates a title page based on the report and relevant metadata, such as the last refreshed date. There are certain current limitations, such as the static nature of the exported file and the visuals supported, but the feature is available to all Power BI users to streamline the creation of presentation slides. However, even deeper integration is planned for the future, including the ability to embed dynamic Power BI reports into PowerPoint.

This recipe contains a preparation...

Connecting to Azure Analysis Services

Power BI Premium is now a superset of Analysis Services and powerful new features such as composite models and aggregations are exclusive to Power BI Premium. Nonetheless, it wasn't long ago in which Azure Analysis Services (AAS) was Microsoft's flagship semantic modeling tool and thus many organizations' production BI workloads currently leverage AAS models as the source for Power BI and other report types.

Given the additional features and product roadmap of Power BI Premium, deep compatibility between AAS and Power Premium, as well as a straight forward migration path, many AAS (and SSAS) models will eventually be re-deployed to Power BI Premium. Nonetheless, Azure Analysis Services will remain a fully supported and common cloud service that BI developers should be familiar with.

"I think it is fair to say that we're the only vendor that can claim a strong presence in self-service business intelligence with...

Integrating with Power Automate and Power Apps

Power BI's tools and services are built to derive meaning and insights from data as well as making those insights accessible to others. While these are both essential functions, Power BI itself is not intended to take action or execute a business decision on the data it analyzes and visualizes. Additionally, information workers regularly interface with many applications or services and thus, to remain productive, there is a need to automate workflows and embed logic between Power BI and these applications in order to streamline business processes. Power Apps and Power Automate, both Microsoft 365 applications and part of the Business Application Platform along with Power BI, serve to address these needs by enabling business users to create custom business applications and workflow processes.

In this recipe a Power Automate workflow is created to support a streaming dataset in the Power BI service. Specifically, the Power Automate...

Leveraging Dataverse and Dynamics 365

The Microsoft Power Platform includes Dataverse, what was formerly called the Common Data Service (CDS). Dataverse stores business data securely in the cloud and allows you to manage the data used by business applications. Dataverse stores data in tables and includes base tables for common scenarios but also allows the creation of new, custom tables. Dynamics 365 applications such as Dynamics 365 Sales, Dynamics 365 Talent, and Dynamics 365 Customer Service store data natively in Dataverse and in addition, Power Apps can be used to build applications against this core data without the need for integration. Finally, Dynamics 365 Business Central and Dynamics 365 Finance and Operations also have native integrations with Dataverse.

This recipe demonstrates how to use Power BI with Dataverse and Dynamics 365.

Getting ready

To prepare for this recipe, follow these steps:

1. If you have not already done so, configure a connection...

Connecting Dynamics 365 Finance and Operations and the Power Platform

Power BI is part of Microsoft's Power Platform family of products. The Power Platform includes Power BI, Power Apps, Power Automate, and Power Virtual Agents. However, the Power Platform is also part of a larger family of products, Microsoft Business Applications. Microsoft Business Applications are applications built around Dynamics 365 for Sales, Dynamics 365 Business Central, and Dynamics 365 Finance and Operations. Microsoft actually breaks down Business Applications into the following:

• Microsoft Power Platform
• Customer Service
• Field Service
• Finance
• Marketing
• Operations
• Commerce
• Human Resources
• Sales

However, note that Customer Service, Field Service, Marketing, Commerce, Human Resources, and Sales are all built around the Microsoft customer relationship management (CRM) system, which used to be called Microsoft CRM. The extensible Microsoft...

Conclusion

This chapter highlighted powerful integration points between Power BI, SSRS, Analysis Services, Excel, PowerPoint, Power Apps, Power Automate, Dataverse, and Dynamics 365. This included connecting Power BI to Analysis Services, leveraging DAX as a query language to support custom reports in Excel, pinning reporting service visuals to Power BI dashboards, and utilizing cube formulas to build templates or scorecard report layouts. Additionally, an example was provided of designing an automated workflow with Power Automate to push data from a relational database to a streaming dataset in the Power BI service, thus delivering real-time visibility to source data changes via common Power BI visualization and data alert capabilities.