Create Date Dimensions for Better Query Performance

If Date dimension attributes are used in filters (i.e., Year = 2016, 2017), you will get better query performance by creating an AtScale dimension from the date field in the fact table versus using Tableau's built-in time intelligence features.

The example below uses the Order Date from the fact table and creates a hierarchy with attributes for Year, Month, and Day:

Create Date Dimensions for Better Query Performance.png

Create Date Dimensions for Better Query Performance2.png
 

Create Date Dimensions for Better Query Performance3.png

Tableau, the Order Date Hierarchy appears in the Dimension pane and looks like this:
 

Create Date Dimensions for Better Query Performance4.png

It is common practice for the Tableau author to drag and drop the "Order Date" field from the dimension pane to the filter and select the YEARS part if the user wants to filter the data between the years 2015-2017. While this still works when Tableau is connected to AtScale, the resulting query that Tableau sends to AtScale will contain a sub-optimal WHERE clause in the query statement.
 

Create Date Dimensions for Better Query Performance5.png
 

Create Date Dimensions for Better Query Performance6.png

Alternatively, the cube designer can pre-process the YEAR from the Order Date (Year of Order) and make it available as a dimension for the Tableau author to use directly. The "Year of Order" dimension in the filter shelf will present a much cleaner WHERE clause in the query statement.
 

Create Date Dimensions for Better Query Performance7.png
 

Create Date Dimensions for Better Query Performance8.png

Was this article helpful?

0 out of 0 found this helpful