Connect Tableau with PowerBI Dataset

 With bigger enterprises having different reporting tools it becomes increasingly difficult to confine the users to a single reporting tool. This scenario can pose a different challenge to the IT department

1. Different semantic layers.

2. Data security can be challenging to implement in different semantic layers.

3. Changes will need to be done in different semantic layers.

4. Syncing up definition especially KPI definition can be difficult.

 

This is where semantic layer comes into picture. There are various tools which perform this activity some of them are -

1. Microsoft Analysis Services

2. AtScale

3. Cubejs

 

We all know that some of these are virtual layers which means they do not hold data but rather sit on top of database(s) and get the query at runtime (direct query). Although they can claim to have caching and optimized querying, the performance provided by in-memory systems can be hard to match especially with high volume of data.

We have analysis services which provides this facility, but what if we have a scenario where you as an organisation have started with PowerBI and built PowerBI datasets and now want to use Tableau which is the other famous reporting tool.

 

Before you think you need to do things from scratch in Tableau with the thought that you have been confined to PowerBI world, let us remember that PowerBI dataset is an Analysis Services. With this glimmer of hope, let us see if this really works.

 

What do you need to test this -

 

PowerBI -

                PowerBI premium workspace or PowerBI Premium Per User

                Deploy a dataset in PowerBI workspace

               

Tableau -

                Tableau Desktop - Licensed or 14 trial version.

               

Step 1 - Go to settings of workspace, copy the workspace connection. This is nothing but an XMLA connection for the Analysis Service, which in turn will have different datasets as different models in the workspace. Copy the workspace connection we will need it while connecting to analysis services.

 

Step 2 - Open Tableau desktop, click on More in To a Server and choose Microsoft SQL Server Analysis Services.

 

Step 3 - Paste the link we copied in Step1 and provide the credentials for it to connect (Ideally this needs to be a service account). Once done click on Sign In.

 

Step 4 - If it is successful, you will be able to see the models inside the workspace

Step 5 - Go to the sheet and let us test if you are able to pull the data.

 

We have now seen how we can connect to PowerBI dataset, since this is SQL Server Analysis services mode this means it will be firing MDX queries on the dataset rather than DAX which is faster than MDX. Hopefully there is an Azure Analysis Services connector in future or they allow PowerBI datasets to be exposed to other reporting tools.

This definitely opens up new possibilities for using other reporting tools with the advantage of having an enterprise grade semantic layer which has matured over the years.