Anyone who worked on tableau for awhile can agree to the fact that there is a myriad of information available either in the Tableau knowledge base or the amazing Tableau user forums. However, this information is often restricted to relational databases or excel. Not a lot of information is available on getting Tableau to “play nice” with SSAS or Microsoft’s latest Tabular model. There is a fundamental difference in how Tableau treats cubes and relational databases. These differences are explained to a certain extent in this article through a couple of use cases that prove to be challenging when working with cubes.
Tableau Parameters
Parameters is probably one of the most versatile features available in Tableau. One common use case that we see when working with relation databases in Tableau is configuring a parameter to dynamically select a measure based on the value of a dimension. Although this seems to be a simple enough task, while connected to a cube this kind of calculation is not possible using Calculated fields. One way to achieve this is to leverage MDX within Calculated Members in Tableau.
Let’s say there is a category type dimension with 2 different categories and I have 2 different Measures. I would like to select the measure based on the parameter input for the specific category type. The dimension and measures look like the following:
Using parameters in MDX is not as straight forward as Tableau store parameters, which uses different names than what the user enters during the creation of parameters. To know what this name is one can open the .twb file in a text editor and search for the parameter declaration. Assuming we create a parameter period and it is stored internally as Period (copy) we write the MDX as follows.
The result of this MDX would be the measure value based on the parameter input for the Car Count Type – Type A. We would create a similar calculated member for Type B.
YoY calculations
Tableau provides a YoY calculation as an inbuilt calculation. Unfortunately, this can fall short when the company uses a fiscal year instead of a calendar year, or when the visualization has to show a YoY calculation based on the year the user selects. This is problematic mainly due to the restrictions when using parameters on cubes. If the user is provided with a filter to select a specific year the YoY calculation will not work as there is no data for the previous year in the visualization.
We can leverage MDX to extract the previous time period’s values for any measure using the Lag function. Since MDX works directly on the cube, they are executed before the filters in Tableau. If we like to compare the sales of this year and the previous year on a single worksheet, we can create a Calculated Member using the Lag function to extract previous year’s sales numbers relative to the current year selected by the user.
The Lag (1) in this case fetches the Sales numbers for the previous year. We use this member in the visualization to see a side by side comparison.
If we look at Jan the top data point is from 2015 and the bottom data point is from 2014.
There are many other use cases that needs a different approach in Tableau when connected to cubes many of which are due to the limitations of the cube data source itself, few owing to how Tableau is designed. These workarounds make working with Tableau exciting!
