How to configure your Data Model to support our new Split Time Dimension functionality.
Background
You can split the Time Dimension across both the X and Y axis of a chart. Below are details for how to configure the Processing Scripts in your instance to support this functionality
What's a Non-Unique field?
By default, our Time Dimensions include both the Year in all Time Periods that fit within a year (Quarters, Months, Weeks, Days, etc.). This makes it clear exactly what Time Period is being returned by the query. For example - in the below query, despite the number of nodes, it's possible to tell which September is for 2021, vs. 2022.
However, the purpose of Split Time functionality requires us to show the same inner Time Node (in this case Months) for each different outer Time Node (in this case, years), so we can have a visualisation of the same time nodes that looks like this:
Instead of coming from the standard "name" fields as in the first example, these Month Names for the Split Time View are coming from Non-Unique fields in the Dimension, so they too are customisable. This allows for different names that may want to be used, through to even having different number of Months (for example), whilst still showing the Split Time View correctly.
Which fields should be Non-Unique?
Any Time Dimension level where end-users may want to see multiple results from different outer Time Nodes in a single inner Time Node. For example,
-
If users want to see the same Month for different Years (as above), then a monthnonunique is required
-
If users want to see the same Week for different Years, a weeknonunique field is required.
-
If users want to see the same Day for different Weeks, a daynonunique field is required (but a weeknonunique field - at least for this query - is not).
We recommend creating a nonunique field for all levels below year in your Time Dimensions so that end-users can always build the queries that they want.
How do I configure Non-Unique Fields?
Adding Non-Unique fields is done in the Processing Scripts. For each level in the Time Dimension below Year where end users might want to do a split, we need to add a nonunique field. This should be at least based on the Name for that level, but with the Year (or anything else that makes it unique) being removed.
For example, if your quartername field is configured like this:
, to_char(dates."date", 'YYYY - ')
+ case when date_part(month, dates."date") in (1,2,3) then 'Quarter 1'
when date_part(month, dates."date") in (4,5,6) then 'Quarter 2'
when date_part(month, dates."date") in (7,8,9) then 'Quarter 3'
when date_part(month, dates."date") in (10,11,12) then 'Quarter 4'
else null end as quartername
Then set your quarternonunique field to this:
, case when date_part(month, dates."date") in (1,2,3) then 'Quarter 1'
when date_part(month, dates."date") in (4,5,6) then 'Quarter 2'
when date_part(month, dates."date") in (7,8,9) then 'Quarter 3'
when date_part(month, dates."date") in (10,11,12) then 'Quarter 4'
else null end as quarternonunique
Similarly if your monthname field is configured like this:
, trim(to_char(dates."date", 'YYYY - Month')) as monthname
Then set your monthnonunique field to this:
, trim(to_char(dates."date", 'Mon')) as monthnonunique
And so-on, based on the requirements of your end-users. These fields will need to make it into the table that is referenced in your Time Dimension. So for example, if your Time Dimension is created like this:
timeperiods:
create time_dimension as 'Calendar'
from ${timeperiods_final}
category('Timeperiods')
levels(
(1, year, year, 'Year', year),
(2, quarter, quartername, quarternonunique, 'Quarter', quarter),
(3, month, monthname, monthnonunique, 'Month', month),
(4, day, dayname, 'Day', day)
)
id(date)
order(ordering)
unknown('1900-01-01','Unknown')
isdefault(true)
keys(date)
then the fields will need to be in the timeperiods_final: table that this is created from.
Note: if your Timeperiods dimension doesn't use the optional levels() syntanx, then "quarternonunique", "monthnonunique", "weeknonunique", and "daynonunique" will automatically be used if they exist with the correct name in the Time Dimension. If the levels() syntax is used, they will need to be added after the "name" field - see the above as an example.
Comments
0 comments
Please sign in to leave a comment.