Time-Based Dimension Calculation Views

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Create a time-based dimension calculation view using the graphical calculation view editor

Time-Based Dimension Calculation Views

There are two types of dimension calculation view: Standard and Time.

You use time-based dimension calculation views to automatically generate various date-related attributes from a base date. For example, from the base date dd-mm-yy, the time-based dimension calculation view automatically provides:

  • The number of the day in the week. For example, Wednesday = day 3
  • The week number in the year. For example, 19 Feb = week 7
  • The quarter. For example, 12th December = Q4
  • The half-year. For example, 12th December = second half
  • The financial period. For example, 27th April 2022 = fiscal period 01/22

This means that you do not have to provide all possible time attributes in the source record or create complicated SQL functions to generate the additional date-related attributes. All that is needed is the base date in the source record. From that we can derive all possible time attributes automatically.

The main use case for time-based dimension calculation views is to allow a business user to aggregate measures of a cube by any date-relates attribute. The modeler can even define a time hierarchy in the dimension calculation view so that a drill-down through time is possible. For example, drill from year to half-year, then to quarter, then to month, then to week, and finally to day. You can even go further and drill right down to hour, minute and seconds.

When you create a time-based dimension calculation view, a table is also generated that is automatically filled with records that represent the data attributes for a range of dates you specify. For example you can choose to generate records between 2020 - 2024. You can regenerate the data at any time to keep the table up-to-date. The generated time table is used as the data source to the time-based dimension calculation view. You then consume the time-based dimension calculation view in your cube calculation view.

Different Calendar Types in Time Calculation View

Save progress to your learning plan by logging in or creating an account