One Model supports the deployment of multiple time dimensions. This means that in addition to the "normal" or Gregorian calendar groupings, you can load in alternative time models. A common use case for this would be grouping data by fiscal years, quarters, or months rather than the regular civil calendar. Really, however, any system for grouping dates will work, as the example below will show. In that video I show how I have loaded in the Age, Year, and Month groupings from J.R.R. Tolkien's Middle Earth. Please do let me know if anyone asks you to group data using that particular system :)
Below the video you will find notes on recommended fields to include in your date mapping table. Be sure to check those out before creating your own time dimension.
Here is a SQL snippet listing the recommended fields to include in your own time dimension table. Thanks for that Jess!
date -- format as a date field when creating your custom time periods table
, yearname --user friendly level 1 name on site
, dayofyear --e.g. 1 Jan = 1, 2 Jan = 2 etc
, quartername --user friendly level 2 name on site
, dayofquarter --similar to dayofyear but for the quarter
, monthname --user friendly level 3 name on site
, dayofmonth ---similar to dayofyear but for the month
, dayname --user friendly level 4 name on site
, weekday -- 1 if a week day, else 0.
, weekend --1 if a week end day, else 0.
, daysinyear -- # days in the full year. If we are part way through a year it is still the number for the entire year, NOT the number so far. Used for Year to date type calculations.
, daysinquarter -- same as above but for quarter
, daysinmonth --same as above but for month
, ordering --determines the ordering of nodes on site (earliest date to most recent)
, iscurrentdate -- Will be 1 on a single record only, representing 'today' based on when the logic was last run.