On Premise PowerBI, what’s in it for the organization?

About a year and a half ago I have witnessed a very interesting interaction between a consultant and a client. The client kept insisting that they wanted a PowerBI based solution, the free dashboard one, and that they cannot join the cloud. PowerBI was only just getting started. The consultant, created an Excel report, using Power Pivot and Power query from the clients SQL data base and…he added a black background. I found it genious, the client was super excited and added it in their SharePoint site as the “on premise PowerBI”.

Reminds me of:

Well what was changed since?

The online version has developed into a “connect everything” model and the advantages seem to be enormous comparing to many other platforms. While the on premise version lacks the maturity to be implemented in the organization. (In my humble opinion).

Let’s review the necessary components and the options we are presented with by PowerBI:

  • Connectivity to the on premise DB
  • Synchronization with the DB
  • Access to data according to permissions levels (Autentication)
  • Publishing the data in a portal solution
  • Self Service BI
  • Usability and access log

The closest model that Microsoft is suggesting in the Governance whitepaper is:

Well, it still does include the cloud for:

  1. Authentication
  2. Publishing
  3. Logs

What if we have an organization that does not include an internal SharePoint portal and Azure AD to authenticate with?

So we get the following architecture:

This particular architecture has many disadvantages:

  1. It allows multiple copies by multiple users and the data governance can hardly be implemented- the same problem Excel spreadsheets are creating, and getting read of stand alone Access or Excel solutions just to get a better looking one is not an option
  2. The synchronization is done manually, each time the .pbix file is opening, through a local client that must be installed on the machine
  3. The authentication and synchronization is performed by the DB itself, if the organization doesn’t have the AD implementation
  4. There are no usability logs, they are created in the desktops per user

I have thought of a creative solution for problem no 4, build an aggregator and a timer job that is going to connect and collect the data to a PowerBI report on one of the administrators machines. This is not exactly feasible…but I get some points for creativity. 🙂

What does PowerBI still has to offer?

Well the Self Service BI option, friendly interface comparing to other Enterprise solutions and recently ability to later use the data sets as data sources.

In conclusion, in order for PowerBI to work on premise, I would love to see if the integration can work great on something other than Office 365. Not all the organizations are willing to move to the cloud, and right now I feel that PowerBI lacks a few features to be a leading product.

So if we quickly sum up the current state, I would like to add that

PowerBI on premise? well maybe…not just yet

Requirements PowerBI on premise
Connectivity to DB Direct query direct access to DB
Synchronization Client, slow and authentication against DB each time
Permissions PowerBI gets the permissions form AD application or public to all the users who have access to the cashed data in the report, if the data was imported
Publishing Stand alone files

Can be reused as data sources

Self Service BI Stand alone, flexibility and easy interface
Activity log Log only, on premise don’t have a viewing tool

 

 

Connecting PowerBI Desktop to Oracle?

One of PowerBI’s biggest strengths is the connectivity to almost everything. (They say that through PowerApps it is going to connect to your home espresso machine soon enough), the challenge is when things don’t go as expected.

What is the business need?   Data visualization from ORacle data base.
What are the constrains? No cloud is allowed, everything is on premise.
The proposed solution? Use PowerBI to create a local dashboard that I can save and share internally in the company. The data displayed will be from ORACLE database that is local in the company.

Simple enough? well as I have later discovered not that simple…

In order to connect Oracle and PowerBI desktop there is a need in a connection on the Oracle side, otherwise we get the following error:

In the excellent blog post from the PowerBI community, the instructions are:

  1. Make sure you have 32bit/64 bit matching windows and PowerBI
  2. Install the ODAC from Oracle site (32bit or 64 bit)
  3. Go to PowerBI desktop
  4. Add new data connection, choose data base and Oracle
  5. Add the servers name and that’s it 🙂

So I have followed, but what happens when it doesn’t work? I got the same error as if nothing was installed to begin with!

It is recommended to install and uninstall all the Oracle client, install diffrent version, make sure you have matching 32/64 bit versions and so on…

I have notice something different, between the connection properties in my Oracle SQL developer tool and the PowerBI data connection window:

So what am I writing in the server name? Nothing seemed to work and worse, I was prompted to use my machine access credentials instead of the DB access credentials. Meaning the connection that PowerBI is trying to establish is to some object on my computer instead to the data base.

The easiest path was discovered in this excellent blog about Excel Power Pivot: PowerPivot Pro

Connecting to Oracle Using TNSnames.ora

The process goes like this:

Creating a file named TNDnames.ora either manually or using the ODAC release 4 which can be found right here for the 64 bit version 

Download the Zip file-> unzip-> run the file setup.exe setup as administrator

The setup will ask you to enter the exact same data you have entered in your database connections properties.

  • Connection Name (Please do not put space or special characters in there)
  • Hostname
  • Port (usually 1521)
  • Service Name
  • User Name
  • Password

There will be a file created in the following directory:

C:\app\client\YOURCLIENTSNAME\product\12.1.0\client_1\Network\Admin

The files name is: tnsbanes.ora

In the blog post I have mentioned before, contains the full file created.

Mine looked very similar:

MYCONNECTIONNAME =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = HostName )(PORT = PortNumber))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ServiceName)))

After this file is created, either manually or by using the ODAC wizard, it is the equivalent of the connection string. I have entered in the PowerBI get data option the following:

Server = MYCONNECTIONNAME

and used the database credentials in the permissions part.

It worked!

More tips around creating the connection:

  1. You do not need to install Visual studio just for the connectivity option. If you are prompted to install VS and the error while installing the connector is:

That means it is not the right connector for the data connection proposes.

2. As recommended in the PowerBI community, clear all the data connections associated with this connection. There might be automatically saved credentials from your previous attempts that will make things not work. In the desktop version go to file-> options and settings-> Data Source Settings; simply delete the not working connections.

Good Luck! and don’t forget to visit the powerBI community: https://community.powerbi.com