Time-Based Dimension Calculation Views

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

Login or Register