Exposing #shared in Azure Analysis Services
While I enjoy that now I can write PowerQuery expressions everywhere in Analysis Services, the fact that I don’t know what is available at hand when I embed an M script in my model — is a bit of an annoyance. Especially when that script ends up working in Data Tools and Power BI but spits up various errors during processing of a table.
Expression in partition ‘Partition’ in table ‘SharedExpressionTable’ references an unknown entity.
Being able to access the #shared variable would solve the issue. However the only way to access it is by creating a cube level expression and call it from a partition level expression. This can be achieved in 3 ways:
- Via clicky-click Data Tools
- By creating a new cube with all the required expressions via a single TMSL script
- Or by creating an expression and a table based on that expression with some TMSL and XMLA (because TMSL doesn’t allow you to create all the objects that are available in tabular)
I’ll leave the first option for the brave ones, as I kept experiencing various problems when trying to use Power Query in SSDT 2017. As for the remaining two options here are the scripts.
Option 2
In order to create a new cube [Mcube] with all the required prerequisites, run the following command in SSMS as an XMLA script:
The interesting bits are at line 8, a construct which creates a cube level Expression and line 36 which is an M script that calls the previously created expression via #sections[main] section of the document.
Option 3
If you’re not happy with polluting your AS instance with various transient cubes you can always add a cube level expression to an existing table with the XMLA template below:
It is worth pointing out that the Kind of this expression is 0 (line 24), which could only mean that M is a first class citizen in this new world.
Don’t forget to create a table that sources the data from SharedExpression expression.
The rest of the action
In order to populate the SharedExpressionTable with the contents of # shared you will have to process the [Partition] with another TMSL command or clicky-click SSMS which is also a reliable choice.
To view the results use DAX or navigate to SharedExpressionTable in your reporting tool of choice (which has to be Power BI 🙂 )
evaluate 'SharedExpressionTable'Originally published at http://querypower.com on August 16, 2017.