I have been meaning to share this tip for a long time and stumbled upon it again backing up files to my off-site server this past week. A while ago I was pinged by a large soft drink manufacturer’s BI group that was using Analysis Services cubes with Tableau. Within their cubes, they used Member Properties for attribute characteristics and metadata that did not aggregate such as packaging specs. If you are unfamiliar with the concept of Analysis Services Member Properties and want to learn about it, please review User-Defined Member Properties and Creating and Using Property Values. Another resource that is easier to follow than the TechNet Online docs on this specific topic is Sorna’s blog.
This BI group wanted to be able to see and use their Custom Member Properties data along with the Analysis Services cube data in Tableau dashboards but they could not see them nor could they figure out how to get them. Turns out that getting those Member Properties was a little tricky since they are not available in the default Analysis Services connection data. After some experimentation, I was able to get them using Tableau Custom SQL with Open Query…my favorite Tableau go-to tricks.
The following example uses the Adventure Works cube so you can try this yourself. I created two connections to the Analysis Services cube – the usual one for the Analysis Services cube data and one more using Custom SQL with Open Query to query the Analysis Services cube Member Properties. The Analysis Services Member Properties Custom SQL with Open Query query syntax is shown below:
Then I created two Tableau worksheets. One for the Analysis Services cube report that contained a Tool Tip to display an Action to get the selected Member Properties. The other worksheet was used to display the selected Member Properties within the dashboard.
On the dashboard that contained these two Tableau worksheets, I used a Filter Action. I wired the Filter Action to source the Analysis Services cube report worksheet, target the Member Properties worksheet and mapped the Target Filter Source Field to the Analysis Services cube Product dimension [Product].[Product].[Product] level where the Member Properties are defined.
The final solution is shown in the very first image of this blog. In the final solution, the Tableau dashboard user can now see Analysis Services Custom Member Properties for any Product that they select by clicking on the Tool Tip Action. It is not ideal but it works. Ironically, it is a similar (not exact) approach to querying Analysis Services Member Properties within Reporting Services reports.
For further reference, improvements or to simply reverse engineer what I did, you can download the Tableau packaged workbook. Note that you will need to update the Analysis Services and SQL Server data source connections to point to your own instance of Adventure Works.
Hope that Tableau tip is useful for other Analysis Services fans using Tableau.