Create a Time Dimension

Time series functions provide the ability to compare business performance with previous time periods, enabling you to analyze data that spans multiple time periods. For example, time series functions enable comparisons between current sales and sales a year ago, a month ago, and so on. To use time series functions, the semantic model must include a time dimension

When you create a time dimension, the Create Time Dimension wizard creates a table in the database, populates it with time data, creates a corresponding time dimension table in the semantic model, and creates a time hierarchy.

The Create Time Dimension wizard populates the source table with time data from 01-JAN-1970 to 31-DEC-2020.

1. In Data Modeler, lock the model for editing.
2. In the Dimension Tables area, click Add, then Create Time Dimension.
   
   Description of the illustration data_mod20.gif
3. In the Create Time Dimension wizard, specify names for the database table, the dimension table, and the hierarchy.
4. In the Hierarchy Levels, specify which levels to include, such as Year, Quarter, and Month.
   
   Description of the illustration data_mod24.gif
5. Click Next.
6. On the next page, review the tasks that the wizard will perform to create the time dimension.
   
   Description of the illustration data_mod21.gif
7. Click Create to enable the wizard to create the dimension.

   The wizard adds a time dimension with data to the database and creates a corresponding dimension in the semantic model. This action might take up to 30 seconds.

8. Click Done.
9. To create joins between columns in the fact table and columns in the Time dimension table, click Create Join in the semantic model.

   The time dimension has two unique columns. The DAY_TS column has the type TIMESTAMP, and the DATE_ID column has the type NUMBER. When you create a join, you specify either the column with the timestamp format or with the numeric format (depending on whether the column in the fact table has a date or number type).

10. In the Joins area for the new definition, select the appropriate fact column, then select the appropriate timestamp or numeric column from the Time dimension.

    After you create the joins, you can display the Hierarchies tab in the Time Dimension editor to view the default hierarchies and levels.

11. Edit the tables in the model.
12. Click Done to return to the semantic model.