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 following:
- 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 that 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.