Time Dimension: Different Flavours an Introduction

Any DW / BI initiative must allow users to navigate across time while analyzing different KPIs. A data model is not complete unless ‘Time’ is factored into the design and data models’ sans time dimension is extremely rare to encounter. This is an elementary topic and typically covered as part of Data Warehousing basics, but as the title suggests, there are different perspectives on time in BI space. In reality, time is singular and linear by nature, but when looked at from viewpoint of how data is collated for reporting purposes, multiple angles as to how the time dimension has to be handled.

Standard Calendar

The Gregorian calendar is the most widely adopted calendar in use today and has become the de facto standard. At a bare minimum, all data models will have Time dimensions modeled around this calendar. The structure of this calendar is based on a solar cycle with 365 days and 12 months in a year with January 1st being the starting reference point for time. Other reference points such as weeks, quarters, etc. are computed based on the reference point, days, and months.

Fiscal Calendar

A derived calendar based on the standard calendar where the beginning and ending dates are either the same or different. Different financial years are sometimes mandated by law or internally adopted by an organization, for example, in India it is April 1st to March 31st whereas in the USA the fiscal year is between January 1st and December 31st. The primary usage of fiscal calendars is for purpose of accounting and finance to compute and release financial statements. The base quantum of measurement and reference points are still days and months identical to the standard calendar.

Religious Calendar

It is a well-known fact that each and every religion has its own nomenclature for measuring days, months, and years. This can be either based on lunar cycles or solar cycles and even the Gregorian calendar started out as a religious calendar before becoming a global standard. These types of calendars are quite rare to encounter, and Nepal is an example of this calendar type.

MOC Calendar

Known as the Monthly Operating Cycle calendar or in general Operating Cycle calendars for short. These calendars are exclusively used for internal management and reporting purposes. The basic philosophy of this calendar is to remove inconsistencies in the length of each month and normalize it with a constant value. This is usually 30 days per cycle with the base quantum of measurement being a single calendar day. Starting point of the calendar is first finalized and all other reference points such as a week, month, quarter, etc. are derived. The beginning of the calendar changes based on the first working day of the year or first Sunday or first Monday, which again is determined by the business teams. One problem with this type of calendar is the residual days for each year, which are typically collected under an extended week that is uneven.

445 Calendar

The name looks a bit cryptic, but it actually denotes the number of weeks in a month. This is a type of normalization of calendar periods where the first quarter of a year has three months, wherein each month has a specific set of weeks as mentioned in the name. For example, the first and second periods of the year will have four weeks each whereas the third month will span five weeks and this cycle repeats four times in a year. The starting date of the year and the span of a week i.e., Monday to Sunday or Sunday through Saturday are determined on a case-by-case basis. The base quantum of measurement for this calendar type is day and week. Similar to the MOC calendar, there are residual time periods that are handled based on business requirements or sometimes rolled over to the subsequent year.