Crossing the Streams: Connecting from Tableau Desktop to a Power BI Desktop Data Model
This seemed like an interesting scenario, so I thought I’d post how to do it. That said, you should not do it, as it is highly impractical.
Why would you connect to a data model in Power BI Desktop from Tableau Desktop? I imagine you might need a proof-of-concept leveraging a complex DAX calculation that already exists in Power BI.
The basic principal here is that Tableau Desktop has the capability to consume data from Microsoft SQL Server Analysis Services (SSAS). When started, Power BI Desktop spawns an instance of msmdsrv.exe, the SSAS process, for use as its query engine and data store. If you can determine the port on which that SSAS process is running, you can connect to it and query it. This is how tools like DAX Studio operate.
Below is a new Power BI desktop report connected to a Wikipedia page containing a table of cities and their populations. In the data model we have a couple of simple DAX measures: average city population (AvgPop) and average city size (AvgArea). For you knit-pickers, yes we could have connected to that web page and calculated those averages in Tableau. This is just a simple example to illustrate the connectivity concept.
Finding the port
Now to connect to the model from Tableau. We need to find the port on which Power BI’s SSAS instance is running. First, on a command line, type [tasklist /fi “imagename eq msmdsrv.exe”] (don’t type the square brackets). Then, take the process ID (PID) that this command returns, and use that in the next command: [netstat /ano | findstr “[PID]”] (don’t type the square brackets). The string of numbers you see after 127.0.0.1: is the port number you’re looking for. Are we having fun?
Now, in Tableau Desktop we connect to Microsoft Analysis Services (in the “To A Server” list). For the server, we type [localhost:[port]], where port is the number we found above.
Leave “Use Windows Auth….” Selected. Click “Sign In”, and we’re connected! The name of the database is a meaningless machine generated id, and the model will always be called “Model”. You can now use the entire Power BI model as though it were hosted on an on premise server in an Analysis Services tabular instance, or on an instance of Azure Analysis Services.
After connecting, I decided to create another DAX measure, the count of cities. After creating “CityCount” in Power BI, I refreshed the data source in Tableau, and my new measure appeared. We can see it here in the Tableau tooltip.
Why You Should Not Do This
Tableau’s connection to the Power BI SSAS instance is not durable through a Power BI Desktop close event. When you close Power BI, the instance of the SSAS process on that port goes away.
Next time Power BI Desktop starts up, its SSAS instance will be talking on a different port. You’d think, as I did, that you could restart Power BI with that pbix file, go through the steps to find the port again, and edit the connection from Tableau to reconnect. However, this doesn’t work. Tableau doesn’t gracefully handle a port change on the SSAS connection. I don’t blame Tableau – this certainly isn’t a design criteria they should be testing against.
Connecting Tableau to an Analysis Services Tabular instance is a great idea. These two technologies are great together. What we’ve done here is connect to the temporary SSAS instance used by Power BI. If you would like to do this in order to justify the idea of moving a Power BI model to Azure Analysis Services, now you know how.