Making the Date dimension ready for TableauPosted: August 27, 2013
This is a technical follow up on the previous article on designing and working with cubes in Tableau.
The data set and structure is still the same as described in the previous article, however we are adding one calculated measure in the cube to illustrate some points regarding the Date dimension.
CREATE MEMBER CURRENTCUBE.[Measures].[Sales YTD]
AS Aggregate(YTD([Order Date].[YQMD].CurrentMember),
In order to use the relative date filter in Tableau it is important that the Date dimension is setup right. In this article walkthrough a Date dimension that is set up correctly for using relative date functionality of Tableau.
For setting the dimension up we need the following:
- Date (Example values: 2013-08-12)
- Year, Quarter, Month, Day (Example values: 2013, 3. Quarter, August, 12)
- First Day of Year, First Day of Quarter, First Day of Month (Example values: 2013-01-01, 2013-07-01, 2013-08-01)
First create a dimension as usually with a hierarchy like Year – Quarter – Month – Day. This should be standard stuff.
Settings for the dimension
My minimum settings for the Year attribute are
Some functions in the cube can use the type settings and the same for some frontend tools (but not Tableau)
Please notice the settings for Key Columns, Name Column and Value Column.
- For the Key Column I have added the First Day of Year. It is a quite good candidate as it is unique for the year attribute
- For the Name I have added the Year field from the relational date dimension. Even though it is an integer t will be converted to a text in the cube dimension.
- The Last thing to add is the Value Column. And it is because of the Value Column that the magic happens. Add the First Day of Year to this column again and check that the data type is Date.
This also gives us new possibilities in Excel as Excel use the value column for the same purpose.
The same has been done for Quarter, Month and Day (for Day the key column is Date)
Using the Date dimension in Tableau
If we turn our attention to Tableau again and look at how we can use the date dimension:
First try to do create this table
Use the new Sales YTD measure and add Year and Month from the YQMD-hierarchy
It looks nice and our Sales YTD is working.
However if you move Month to the Columns-shelf it will no longer look nice and condensed.
Select Uniqueness to be by Name and not by Key for the Month attribute
This doesn’t depend on any specific setup of the data dimension and it can be used on all dimension attributes
When you are using a relational data source and add a date to the filter shelf it looks like this
We get a special dialog box where we can choose how we want to filter the dates. Please notice that the Order Date-attribute is green.
On a cube the date will look just like any other dimension – Tableau has no knowledge of this dimension being special.
Notice that the Order Date.Month filter is blue. That means it is a discrete field and that there exists only this values. If the pill is green it means that field is continuous.
Because of the added value column in the Date cube dimension we can change the data type to date and the field will work as a continuous field.
And press OK and you get the last 6 month relative to today – meaning that the viz will be updated every time the month changes – automatically.
Also notice that March 2013 includes the sales from January and February 2013.
This way you can make the visualizations much more dynamic and there is no need for the end user to change the filters just because of month change or day change.
One word of caution.
Be aware that the cube will return data at the level of the selected dimension attribute.
Saying that Date.Month will return 12 results per Year and Date.Day will return 365 days a Year. And Tableau can’t aggregate the numbers from the cube. If the visualization is not at the level same level as the filter Date-attribute then we will then get the dreaded stacked marks.