Microsoft SQL Server Analysis Services provides an easy method for creating Time dimensions in a cube. The default formats of the date columns created do not lend themselves easily to the kind of visualizations you may be used to creating in Tableau.
This document describes one method for configuring attributes of a time dimension in an SSAS cube so that measures can be displayed against a given time unit (month in this example) with a standard naming format (January, February, etc) while retaining their logical sort order.
The SSAS Dimension wizard creates a time table directly in the underlying server. The result is a table similar to the following:
The default date formats are difficult to use as fields such as Quarter_Name, Month_Name etc. have the year attached. This makes it difficult to show monthly sales in Tableau. "January 2006" is not easily aggregated with"January 2007". Note the image below from Tableau's"Describe" dialog box, the Month field's domain has 72 members, as opposed to the 12 distinct months we would like to display:
Setting up the data in the cube
Next step is to format the data in our cube the way we would like it displayed.
Changing the date fields
There are a few options. One method is to create a named calculation in our time dimension similar to the following:
You can also modify the data in the underlying time table using a similar SQL statement, so that your underlying data contains the date fields as you would like to see them:
After you have implemented the method you need, you then need to redeploy your cube.
Viewing in Tableau
How does this now affect Tableau? As you can see below, the domain of our Month_Name field now has just the 12 members we desire (plus unknown).
However the default order of the field is incorrect:
Changing the sort order
To setup the correct sort order, you will need to modify the Month_Name field in the cube's Time dimension. To do this, set the KeyColumn value to the Month_Of_Year field, and the NameColumn value to the Month_Name field
Viewing in Tableau
This results in the proper ordering of the Month_Name field.
Note that the ordering is correct in both the ‘Describe Field' dialog box and the Visualization.
You are now able to create visualizations by month across years, as below!
What about Hierarchies?
Hierarchies defined with your newly named date fields will also perform as expected in Tableau, if you set the Unique Values > By Name:
In the example above, defined a hierarchy in the cube with Year Name, Quarter Name, and Month Name all formatted as described earlier. After setting the ‘Unique Values' to ‘By Name', you can work with the dates much more easily and the resulting visualizations are much more presentable.