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.
