Time Dimensions
A time dimension in AtScale is a type of normal dimension that uses a time-based hierarchy. Use time dimensions to support time-based analysis, such as comparing data in parallel periods.
You can designate a time dimension when you create a new dimension or edit an existing one in Design Center. To do so, in the Edit Dimension panel, set the Type field to Time. You can also change an existing normal dimension's type from standard to time. For more information, see Edit a Dimension.
Note: You can build tabular reports that use hierarchy-specific calculations, such as time calculations, without specifying the hierarchy in the report or query. For more information, see the AtScale SML Object Documentation on GitHub.
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 model, and instead sort rows alphanumerically by member name instead of key value. This behavior doesn't affect the time calculations in the model. However, it will return rows in a confusing sort order (for example, "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 (for example, "2019-07 (Jul)", "2019-08 (Aug)")
Note: When modeling date hierarchy levels in AtScale, be aware 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 columns such as Date, DateTime, or Timestamp.
Known Limitation for MDX interfaces
For MDX interfaces (such as Microsoft 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.
Parallel Period Grouped by Secondary Attributes
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.