Episode 2 – Getting your own data in Power BI Dashboards and compromising your existing work !!

This post is the second one in the series about Power BI dashboards which was released a few days ago.

Previous post : POWER BI PREVIEW – A JOURNEY – Episode 1

All posts in the series.

Today is Christmas Eve and before I put the goose (Yes Mark Broadbent (T|B), we’re having goose) in the oven I want to write down these few lines that have been running in my mind since I woke up early this morning.

First of all I want to establish a baseline for my writings so here are the product versions I am using as of today December 24th.

Power BI Dashboards (web interface) : 11.0.9165.392

image

Power BI Designer Companion (you gotta love the term) : 2.18.3881.203

image

I expect that these numbers will change on a regular basis. Specially the Power BI Designer Companion tool has some annoying glitches/bugs/features or whatever we want to call those.

I really want to see my own data in a Power BI dashboard and I really don’t want to export a Excel workbook. What would be very cool and usable would be to grab some data from my on-premises SQL Server Analysis Services Tabular instance and display it on a Power BI dashboards.

So let’s get started and see how this can be achieved:

According to the Power BI dashboard site this is the only form of SSAS which is supported. No support for Multidimensional data models (yet).

Again according to the site :

With Power BI, you can create dynamic reports and mashups with data and metrics you already have in your on-premises SQL Server Analysis Services solutions. To get started, click Connect, select a server, then select a data model.

Before I can connect to anything I actually need to install a piece of software that is downloadable from the Power BI dashboard site: The Power BI Analysis Service Connector

image

How to use and configure it is described here : http://support.powerbi.com/knowledgebase/articles/471577-create-an-analysis-services-connector

image

But there are some pretty tough prerequisites for making it work which will compromise my existing work :

1- No Data Management Gateway on the same machine

The biggest hurdle in my opinion is that you can’t have the Data Management Gateway installed and running on the same computer. If you don’t know and don’t use the Data Management Gateway then it’s not an issue, but if you do so this is a major issue.

The Data Management Gateway is a client agent that provides access to on-premises data sources in your organization. You must have at least one gateway installed in your corporate environment and register it with the Power BI for Office 365 Admin Center portal before creating data sources in the portal. Unfortunately and according to the site it is not possible/allowed to run both the Data Management Gateway and the Power BI Analysis Service Connector on the same machine:

Installing the Power BI Analysis Services Connector and the Data Management Gateway on the same computer is not supported. If you already have the Data Management Gateway installed, uninstall it before installing the Power BI Analysis Services Connector, or install the Connector on different computer.

This mean that I either have to choose between Office 365 and Power BI dashboards, or I need to install gateways on different machines.

Running the installer on a server is a pain the neck due to the security restrictions that apply to browsing on servers, no Cookies, no Javascript, Enhanced Security and so on. So before you can configure anything you need to arm yourself with patience and find a solution to this problem – only 2 sites need to be added before you get to the sign-in page…could be worse !!

image

On the second try I get this very nice error message:

image

A separated COM object, hmm the gateway is doing a reverse Oedipus on me !

I am probably screwed ! Do I need to remove the software or just start from scratch ?

After allowing the sites on my server I finally manage to get it running.

image

Probably not the smartest move from my side to run a tabular instance on a Domain controller, but hey it’s a VM and my resources are sparse.

After that you get to configure a few friendly information:

image

And then on to the next step. Success !!

image

2 – Configuring Active Directory Synchronization

Again according to the knowledgebase article this is a requirement. Probably one of those where you need to buy a lot of flowers / beers to your domain admin !!

There is even a “learn more” broken link. So here is a working walkthrough from technet : http://blogs.technet.com/b/canitpro/archive/2014/09/30/step-by-step-setting-up-the-new-aad-sync.aspx

Follow those steps and you should be able to synchronize your local AD with with Azure AD.

SNAGHTML4af3517d

The fact that it is unverified shouldn’t be a hurdle for synchronization. For verifying you need to add a TX record at your hosting provider. Which of course is not working when using a local VM hosted domain.

My users (from local SharePoint 2013 environment and a lot of demo sessions) have been synchronized…so far so good

image

And now it is working : I can see my newly added instance on the Power BI dashboard site :

SNAGHTML4af8728a

I am really excited, would I be able to connect to my local tabular instance, is that really all that it takes (kidding) ? :

  1. removing existing data management gateway
  2. synchronizing Azure AD with local AD

Drum roll………

3 – Close but no cigar !

image

So I didn’t succeed. My user is not allowed to connect or something else is wrong. Which in a way is good since it will allow me to do some more posts about the subject!

Happy Power BIing !!

Advertisements

2 comments

  1. Donald Parish · · Reply

    Good luck with this. I’m very interested in this capability, but haven’t succeeded at work yet, because of the AD requirement and a permissions issue. Tried at home on my personal machine, but haven’t quite succeeded yet in getting to the AD part.

  2. […] Episode 2 – Getting data from on-premise into Power BI dashboards […]

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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: