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.
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
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).
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.
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.
Pin an SSRS report part to a Power BI dashboard
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
- Data Origin for reporting gets heterogeneous
- Diverging reporting requirements (Standard reports, self-service, Mobile, KPIs, ..)
- Harmonization with cloud services / functionality
- Focus on SQL Server Reporting Services
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.
PowerBI.com – Analyze in Excel
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).
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
