Hybrid Power BI – What is Possible? What Do you Need?

PowerBI.com was introduced as a cloud-based SaaS solution for self-service business intelligence (BI). With its built-in and custom connectors, Power BI datasets can get their data from various sources irrespective of being on-premises and/or cloud-based data sources.

Power BI Dataset Development Cycle

When looking at the development cycle, Power BI Desktop is the tool for connecting to data sources, transforming data using Power Query (M) and building the data model using DAX. If the dataset is finalized, the Power BI report (pbix file) is published to powerbi.com. Within powerbi.com, report authors can share those datasets and reports with other users (report consumers).

Data Origin

In todays enterprise environments a huge mixture of data sources is found in the fields. The mixture lies in the purpose of the systems, the technical data storage and the settling of the source systems. Not many enterprises can call them 100% cloud-only and therefore a mixture of cloud-based and on-premises data sources are brought into those enterprises reporting solutions. When looking at some of our customers, I think that this mixture will continue to last …

This mixed adoption of data origins (cloud and on-premises) lead to the implementation of hybrid reporting systems – in my blog post I will talk about Hybrid Power BI solutions.

Why Hybrid (Power BI)?

There are several reasons why hybrid Power BI solutions are needed and why even mainly on-premises focused enterprises can benefit from a hybrid architecture.

One of the main reasons is that there is already existing on-premises infrastructure / systems in place like an Enterprise Data Warehouse (DWH) which cannot be easily transferred into the cloud. Some other industries are not allowed to move their data into the cloud because of regulatory reasons. And other enterprises (or sometimes only the CEO / CIO) are afraid of moving their data into the cloud for reporting.

On the other side – the fast-moving development and release cadence of Power BI (monthly & bi-weekly release cadences) leads to an uncatchable advance for cloud-based reporting solutions compared to traditional on-premises reporting solutions.

Coming back to the arguments some of the opponents of cloud-based reporting are arguing with – I think that one of the main reasons for hybrid reporting (Power BI) solutions is to take the best approaches from both sides and combine them to build the best solution!

Paths to Hybrid Power BI

In the following paragraphs I would like to tell you more about the different paths to Hybrid Power BI and which questions can be solved with those different paths.

Path #1: Use On-Premises Data Sources & On-Premises Data Gateway

When using on-premises systems as data sources for your Power BI reports published to powerbi.com you’ll need a connector program that is able to connect the cloud with the on-premises world.

The following schematic architecture shows powerbi.com (running as a SaaS solution in the Microsoft Azure cloud) and different kinds of on-premises data sources like files and/or databases. The On-Premises Data Gateway acts as a connector between those two worlds.

hybridPBI_01

Power BI and On-Premises Data Sources

The On-Premises Data Gateway (named Application Gateway in the following architecture) is not limited to only connecting PowerBI.com to on-premises data sources. Additionally, the Data Gateway can also be used by

  • Azure Analysis Services
  • PowerApps
  • Microsoft Flow and
  • Azure Logic Apps
hybridPBI_02

On-Premises Data Gateway – Architecture

The Data Gateway can be downloaded directly from powerbi.com and needs to be configured for your Power BI tenant with your login (i.e. email-address).

hybridPBI_03

On-Premises Data Gateway Download

Installation and configuration of the data gateway is separated. The installation process only copies the appropriate binaries onto the gateway server. During the configuration actions you’ll need to enter your (or a system-user) Power BI credentials to register the Data Gateway in your Power BI tenant.

There are two different installation modes for the data gateway: Personal versus Enterprise Gateway. The differences between those modes are depicted in the following table. Be reminded – personal mode really means personal! 😊

Enterprise Gateway Personal Gateway
Power BI, PowerApps, Flow, Logic Apps Power BI only
Multiple Users can share and reuse gateway Personal mode only
Central source management & access control Sources only on gateway-computer
Schedule, on-demand refresh and DirectQuery On-Demand refresh and scheduled refresh
Central Monitoring and control No central monitoring / control
Installed & configured by (BI) Admins Installed & configured by Analysts
Gateway Clusters (Failover)

 

Data Gateway configuration is split into two different actions. Configuration of the data gateway itself and the configuration and definition of data sources. A Data Gateway can only work with on-premises sources when those sources are configured as data sources in the context of the gateway. This configuration is done in the powerbi.com web-portal and requires the connection information as well as the definition of the (Power BI) users that are allowed to work with those data sources.

hybridPBI_04

Gateway Overview in PowerBI.com

The overall topic of the on-premises Data Gateway is a huge topic and definitely goes beyond the scope of this article. Maybe someday in the future I will find some time to write more deep-dive articles about the data gateway.

For those of you that need more information – take a look at the On-Premises Data Gateway documentation: https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem

 

 

Path #2: SQL Server Reporting Services (SSRS) and Power BI

SQL Server Reporting Services (SSRS) are part of the SQL Server product since many versions. In enterprise reporting solutions, SSRS is very often used for serving as a foundation for standardized, pixel-perfect reports.

SSRS developers have the full control over content generation (with expressions that can be used to configure almost every property) as well as exporting options. With a full set of export options (like Excel, PDF, ..) SSRS reports very often serve as a centralized starting point for further reporting pipelines.

SQL Server 2016 (and with it SSRS 2016) brought a huge overwork on the UI side (touch friendly and HTML5) and some (minor) feature extensions.

What SSRS lacks is a dashboard functionality (sure, you can build an SSRS report that looks and feels like a dashboard) that combines report parts from multiple reports into a single dashboard.

But wait – PowerBI.com has this functionality and newer SSRS versions include a feature of pinning parts of a report to a powerbi.com dashboard. After configuring and registering your Power BI tenant to your SSRS Server instance (in the Reporting Services Configuration Manager) you are able to start pinning with a simple click on the Power BI logo within your SSRS UI.

hybridPBI_05

Pin an SSRS report part to a Power BI dashboard

hybridPBI_06

Dashboard with SSRS report part

 

Path #3: Bring Power BI to On-Premises

In October 2015 Microsoft announced and published their reporting roadmap which contained the plans and directions how Microsoft sees the future of reporting with their own tools. The roadmap announcement included several main points like

  1. Data Origin for reporting gets heterogeneous
  2. Diverging reporting requirements (Standard reports, self-service, Mobile, KPIs, ..)
  3. Harmonization with cloud services / functionality
  4. Focus on SQL Server Reporting Services
hybridPBI_07

Reporting Roadmap Blog post

And there was one thing in the roadmap announcement – Microsoft will bring Power BI reports to On-Premises. In June 2017 the first version of Power BI Report Server (PBIRS) was released. PBIRS contains SSRS as core plus the functionality of rendering Power BI reports.

The Power BI Report Server gets updated every 3-4 months and those updates includes new features every time. As Power BI (in the cloud) is a fast moving product and features are added constantly, PBIRS also needs some updates from time to time to stay current 😊

 

Path #4: Excel and Power BI

 

Last, but not least – Excel and Power BI. Excel is the world most used Business Intelligence and reporting tool from Microsoft. Many users build very powerful solutions within Excel and those users are very often familar with the concept of Pivot tables.

Maybe you’ve already seen the option Analyze in Excel in the context menu of a Power BI dataset or report. With that action users can directly connect to Power BI datamodels hosted in the cloud and use those data models as data sources for Pivot tables.

hybridPBI_08

PowerBI.com – Analyze in Excel

hybridPBI_09

Analyze a Power BI dataset in Excel

With the Power BI Publisher for Excel, users can start their analysis journey out of Excel and connect to Power BI datasets / reports. In addition, parts of your Excel workbooks can be pinned to a Power BI dashboard (like the approach with SSRS report parts pinning).

hybridPBI_10

Power BI Publisher for Excel

Conclusion

When talking about Hybrid Power BI, most users think about the On-Premises Data Gateway and the connection between on-premises data sources and Power BI as a first option.

But I think, Hybrid Power BI is more than that. There are many ways of building a hybrid reporting / Power BI solution. You only have to choose your way and options:

  • On-Premises to Power BI -> On-Premises Data Gateway
  • Power BI to On-Premises -> Power BI Report Server
  • Connect On-Premises with Power BI -> SSRS and Excel Integration

 

If you want more information about the topic “Hybrid Power BI” I recommend you the recording of my Power BI Usergroup webinar.

  • Link will be posted when available!

Happy Power BI-ing,

Wolfgang

About wolfgang

Data Platform enthusiast
This entry was posted in Business Intelligence, PowerBI, Presentations, SSRS. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s