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
Power BI Designer Companion (you gotta love the term) : 2.18.3881.203
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
How to use and configure it is described here : http://support.powerbi.com/knowledgebase/articles/471577-create-an-analysis-services-connector
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.
On the second try I get this very nice error message:
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.
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:
And then on to the next step. Success !!
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.
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
And now it is working : I can see my newly added instance on the Power BI dashboard site :
I am really excited, would I be able to connect to my local tabular instance, is that really all that it takes (kidding) ? :
- removing existing data management gateway
- synchronizing Azure AD with local AD
3 – Close but no cigar !
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 !!