Time Dimension Design

In Oracle Analytics, fact tables often include many dates, and therefore many potential time dimensions. Oracle recommends that you follow the most efficient way to handle and configure time dimensions in your semantic model as described here.

Best Practices

• Create a single generic time dimension for each fact table

  For each fact table, identify one date which is the used the most with this fact. To help you identify the best date, ask yourself questions like, “if I select this amount for June, what does ‘June’ mean?”.

  After you’ve identified a specific date for each fact table, use that date to join each fact to a generic time dimension. A generic time dimension is required for reports that includes multiple facts and is much easier for end-users than creating a separate time dimension for each fact table.

  
  
  
  Description of the illustration ceal_fact_tables_joined_to_time_dimension.jpg
  
  

• Only create secondary time dimensions if needed

  A time dimension is only useful for a date if you want to simplify user selection at the date level (such as the year or quarter) or if you need to drill down the date hierarchy at runtime. For both these cases, we recommend that you create a secondary time dimension with joins to only the specific fact tables. Otherwise, displaying the date itself as a single attribute in the Presentation layer is often enough.

• Configure your time dimension for time series

  Time series functions like Ago or ToDate are often used to easily calculate metrics such as Year-Ago or YearToDate. These time series functions are available only if you configure chronological keys for the corresponding time dimension. These chronological keys must be unique at each level of the dimension hierarchy.

  
  
  
  Description of the illustration ceal_logical_level_chronological_key.jpg
  
  

  You can use time series functions after chronological keys are defined. However, these time series functions can have an impact on performance. One way you can minimize the performance impact is to define sequence numbers on the time dimension. Sequence numbers are optional and used only for performance reasons.

  There are two types of sequence:

  • Absolute: Integer chronological keys with values that increment by 1 and range from N to M. For example, a 4-digit year.

  • Relative: Integer values with values that increment by 1 and range from 1 to N. These values represent the chronological order within a higher level. For example, month number (1 – 12).

    As absolute and relative sequences cover different use cases, you can define both types of sequence. Logical columns must be at the appropriate level in the hierarchy to be available for the sequence definition.

• In the Presentation layer, always display the generic time dimension as the first folder

  You can place secondary time dimensions as folders or sub-folders next to their corresponding dimension attributes.

  
  
  
  Description of the illustration ceal_presentation_layer_time_dimension.jpg