Time Dimension Requirements

Time dimension levels have strict ordering requirements. In order to make correct comparisons between time periods it is critical that following requirements are met:

• Levels must have their "Sort Column" set to a column that correctly represents the time members of the level when sorted in ascending order. In most cases it is sufficient to set the "Order by" column to the same column as the level's key.
• If the level uses a compound key, the "Order by" column must point to a field that sorts in the equivalent order as the compound key. If no such column exists, consider creating a single calculated column that combines the components of the level key into a single, sortable field.
• SQL BI Tools such as Tableau frequently ignore the sort order returned by the AtScale OLAP cube, and instead sort rows alphanumerically by member name instead of key value. This tool behavior doesn't affect the time calculations in the AtScale cube. However, it will return rows in a confusing sort order (e.g. "August 2019" before "July 2019"). To prevent this, use a value column that has an alphanumeric sort order that matches the ascending order of time (e.g. "2019-07 (Jul)", "2019-08 (Aug)")

Note: When modeling Date Hierarchy Levels in AtScale, consider that setting Value column bindings to a String column type is not compatible with Looker. To be compatible with Looker's date capabilities, these Value column bindings must reference a date-like database column such as: Date, DateTime, or Timestamp.

Known Limitation for MDX interfaces

For MDX interfaces (such as Excel), multiple time dimension members cannot be used in the same slicer or filter simultaneously. For example, first slice on Year 2017 and Year 2018, and then filter between 1/1/2017 and 1/1/2018.

As a workaround, when you select a filter or slicer you can include the desired offset measure, which uses a calculated measure function as a selected value.

This limitation does not affect SQL interfaces.

Custom Parallel Period Comparisons

You can use the ParallelPeriod function to compare members in the same relative child positions of different time hierarchy levels (for example, comparing this year's April sales to last year's April sales). But sometimes you must compare periods that may not be in the same relative position. For instance, in a 4-4-5 calendar, you'll sometimes have to compare a 53-week year to a 52-week year. By default, Week 53 compares to a NULL value because the previous year only has 52 weeks. In this case, many businesses compare Week 53 to the previous year's Week 52. To accomplish this, define a custom parallel period comparison for each level in your time hierarchy and comparison you wish to make. A custom comparison tells AtScale to use a database column in your time dimension table that contains key values pointing to the desired parallel period.

To define a custom parallel period comparison, you must first create a custom parallel period key column in the source table. This column must have the same type as the level's key column. Then in AtScale, add the custom parallel period comparison to the desired time hierarchy level, along with the comparison period size (e.g. Year, Half-Year, Quarter, etc). To do so, edit the level and click Add Custom Comparison in the Edit Level dialog.

You can add multiple custom parallel period comparisons to a level. Continuing with the 53-to-52 week, one-year comparison example, choose a Key Column that has records pointing to the desired key one year in the past. The column should always represent data offset by one unit of the selected period type. AtScale handles ParallelPeriod offset indexes greater than one by reading multiple rows from this column. For example, to perform a custom five-year comparison, you would still use a key column that has a one-year lookback, but pass an offset value of five to the ParallelPeriod function.

Parallel Period Grouped by Secondary Attributes

As of AtScale version 2020.1.0, AtScale supports the ParallelPeriod function with Secondary Attributes when querying.

It is important to note that grouping ParallelPeriods by secondary attributes only works if the query also uses the hierarchy referenced by the ParallelPeriod expression, such as in a row, column or where constraint.

More information

Use Cases for Time Dimensions