- Written by David Kreiger David Kreiger
- Created: 08 October 2017 08 October 2017
Recently I had the luxury of helping one of my clients that decided to conduct a Business Intelligence (BI) cloud proof-of-concept (POC) project on Azure. The scope of this POC was to gain analytical insights through the Azure cloud connecting directly to an on-premise healthcare dataset consisting of roughly 20 Oracle tables. Like most POCs this one was time sensitive and the plan was to complete it within a few weeks to share the results with the board of directors.
My mission was to identify a low-cost Azure cloud based solution that was within scope and could be deploy rapidly without much intervention from my client’s IT department. Having worked on Amazon Web Services infrastructure extensively for the past 5 years I had to swiftly educate myself on Microsoft’s Azure cloud infrastructure and product options. Quickly it became apparent that my most logical choice on Azure would be to utilize Microsoft Power BI as the POC BI tool.
To reduce development time and complexity I eliminate the need to leverage a data integration tool. Instead I mandated that the POC design leverage the entity relationship modeling capability within Power BI to join the 20+ Oracle tables into a quasi-semantic layer instead. As well enforce the transformation of technical structures and terms into business consumable dimensions and metrics.
I quickly discovered, that I’ll have to jump through a few cloud infrastructure hurdles before I can connect Power BI Service in the cloud directly to an on-premise Oracle database where my 20+ tables reside. The first task would require provisioning and configuration of a few additional Azure network products. These products consisted of setting up an Azure Gateway Cloud Service, Azure Service Bus and lastly an on-premise Application Gateway. Depending on your how you plan on authenticate on-premise to the cloud Microsoft recommends that you provision Azure Active Directory (AD).
Here is a high live landscape diagram outlining the architecture that was recommend by Microsoft and that we deployed.
Moving past the cloud infrastructure hurdles now it is time to setup the database connection, which required an installation of Power BI Desktop application locally. Understand that this local version of Power BI Desktop is primarily for development and connecting to your data sources. The desktop application needs to be installed on either a local or virtual machine that is on the same network where the Oracle Database resides. Once you have Power BI Desktop application setup you can then, and only then, connect directly to your database on the local network, create connection, create a model joining your 20+ Oracle tables and then lastly save your work to a file. This file will include the Power BI Desktop model and connection string that you just created. The next step is to take this file and then imported it into Power BI Service in the cloud. Subsequently, the new imported connection and model in Power BI Service in the cloud will now use DirectQuery to execute SQL over the Azure Gateway to query the Oracle Server on-premise.
To recap here is an overview of Power BI Service (cloud) steps taken to connect directly to on-premise oracle database:
§ Create Azure Account
§ Register & Create Power BI Pro Account with same email account used to Create Azure Account
§ Provision Azure Gateway Cloud Service
§ Provision Azure Service Bus
§ Setup Application Gateway
§ Test Gateway connection through Power BI Service (Cloud)
§ Install Power BI Desktop
§ Determine which version of Power BI Desktop installed 32-bit vs 64-bit
§ Install the Oracle client
§ Connect to an Oracle Database within Power BI Desktop
§ Create a Power BI Desktop model off your database
§ Create a Power BI Desktop file (includes Model and Connection)
§ Import file into Power BI Service in the cloud
§ Test Oracle Connection from Power BI Service
Overall, I’m puzzled by the complexity due to Microsoft’s Power BI Service limitation to connect directly to an on-premise data source. I can only hope that in the near future Microsoft simplifies their infrastructure to connect directly to data sources to be more seamless like other cloud BI tool vendors. I get it, Microsoft did not have the luxury to designing Power BI platform from scratch. Unfortunately, they had to design a cloud base BI tool that complements their existing on-premise tools like SQL Server Analysis Service and SQL Server Reporting Services.
Furthermore, Microsoft dropped the ball when it comes to creativity but instead decided to conform to a similar infrastructure as another industry leader in the BI tool space. Microsoft’s cloud BI tool solution is extremely similar to the model that Tableau has been using for years, which is to develop locally on your desktop, publish content for sharing and collaboration to a server or in Microsoft’s case to the cloud service.
My gratitude and appreciation does however go out to the other cloud based BI tools like Looker, GoodData, Sisiense and Yellowfin who got it right. Collectively each of these products baked everything analytics into a single cloud based application, removed the complexity and simplified connecting directly to data sources. This also affirms my recent decision as a co-founder to partner with Yellowfin and offer a low cost, cloud installation-free data visualization platform called www.GetWholeView.com which is a great fit to run either a POC or for rapid prototyping.