This week in the social media chatter, I noticed tweets regarding a new Microsoft white paper by Joseph D'Antoni and Stacia Misner published to TechNet on Hybrid Business Intelligence with Power BI. This white paper is a fantastic technical overview and a must-read for groups looking at Power BI, wondering how to best implement it with existing on-premises business intelligence BI, or Azure Infrastracture as a Service (IaaS) hosted BI. Covered topics include:

  • hybrid BI technical architecture options
  • data management gateway
  • best practices for:
    • integrating security
    • identity management
    • networking
    • Office 365

Aside from small businesses that may only have cloud hosted solutions, many businesses currently have a combination of cloud and on-premises data sources. Just think about how many groups use Salesforce.com, Google Analytics, Constant Contact, and other departmental cloud applications. Typically, I see those groups leveraging APIs or connectors to bring cloud data back on site into a local data warehouse for creating reports. We are taking those same concepts quite a bit further with Microsoft Azure and Power BI.

Related: WPC 2014 Showcases New Power BI Experience

Ideally, we are no longer moving all of the data in our big data world. Concepts like data virtualization, for example, are becoming more popular. Most likely, we are now tasked to deliver a transparent Microsoft BI experience across Office 365 and existing on-premises SharePoint portals or data sources.

Understanding how to architect hybrid-BI scenarios is becoming a more important skill to master in our profession. However, prior to this new white paper, finding the answers and best practices for it was fairly challenging.

Security in a Hybrid World

Upon a brief skim through this new technical whitepaper, I noticed a lot of content around networking and identity management. Historically, identity management and security in Microsoft BI has not been easy to master. In a hybrid BI world, these topics appear to be comparable or even a bit more complex.

Let's face it, getting through a SharePoint 2013 BI farm installation and configuration can be a daunting process for even the top talent in the world. I usually advise to folks considering a new SharePoint 2013 BI farm installation to first read Kay Unkroth's incredible white paper to understand SharePoint security, Microsoft BI security, and Kerberos delegation concepts.

Managing user security in Office 365 looks comparable to on-premises SharePoint security. There are options to federate Active Directory (AD) to Office 365 and use Single Sign On (SSO). There are additional alternatives for multi-factor authentication in scenarios where you require additional layers of security.

In hybrid BI scenarios where you have Analysis Services or Reporting Services hosted on Microsoft Azure VMs, you might also need to configure Azure AD, AD Federation Services (ADFS), and the Azure Active Directory Sync tool to synchronize passwords, users, and groups between on-premises AD and Azure AD supporting the Office 365 installation. The new Hybrid Business Intelligence with Power BI white paper goes into detail on those concepts and includes links to a plethora of excellent resources.

Data Management Gateway for Power BI

At the moment, Data Management Gateway appears to be the key to hybrid BI with Office 365 Power BI. The Data Management Gateway is a client agent application that is installed on an on-premises server and copies data from internal data sources to the Power BI cloud data source format.

Office 365 Power BI data sources are a bit of a cloud data island per se, but over time it should continue to evolve. Present Power BI Data Refresh capabilities, basically Excel workbooks deployed to a Power BI site, can have a single data refresh schedule from the following supported data sources:

  • On-premises SQL Server (2005 and later)
  • On-premises Oracle (10g and later)
  • Azure SQL Database
  • OData feed
  • Azure VM running SQL Server

Now, if you have a VPN connection and Azure virtual network, it opens up many more potential data sources for Power BI. In that case, accessing data sources with Power BI data connections and scheduled refresh is similar to on-premises Power Pivot except it sure looks like you still need Data Management Gateway to get that data into Power BI-land. The white paper section labeled "Power BI Data Refresh" goes into deep detail on supported data sources, data refresh schedules, and various data location scenarios.

Sending Feedback to Microsoft

We are just beginning to see Microsoft BI and Power BI in a cloud and hybrid world. Groups that are using Power BI and hybrid BI today are early adopters. We would all benefit from hearing about their tips, tricks, and lessons learned. I see a lot of continual changes in Azure and total confusion out here especially around Azure cloud BI and Power BI with on-premises data sources.

If you have Microsoft technical content requests, you can send feedback to the teams that develop these resources to get new topics on their radar. Don't assume someone else has already expressed a need. If no one asks or complains, the folks in Redmond may be completely unaware of that need. It really is that simple.

Related: Amazing Data Visualization with Apps for Office