Time Dimension: Different Flavours an Introduction

Any DW / BI initiativeTimeDim1 must allow users to navigate across time while analyzing different KPI’s. A data model is not complete unless “Time” is factored into the design and data models sans time dimension are 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 to time in BI space. In reality, time is singular and linear by nature, but when looked from viewpoint of how data is collated for reporting purposes, multiple angles as to how time dimension has to be handled .

Standard Calendar: Gregorian calendar is the most widely adopted calendar in use today and has become the de facto standard. As bare minimum, all data models will have Time Dimension modeled around this calendar. The structure of this calendar is based on 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 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 USA the fiscal year is between January 1st and December 31st. Primary usage of fiscal calendars are 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 of measuring days, months and years. This can be either based on lunar cycles or solar cycles and even Gregorian calendar started out as a religious calendar before becoming global standard. These types of calendars are quite rare to encounter and Nepal is an example for this calendar type.

MOC Calendar: Known as Monthly Operating Cycle calendar or in general Operating Cycle calendars for short. These calendars are exclusively used for internal management and reporting purposes. Basic philosophy of this calendar is to remove inconsistencies in the length of each month and normalize it with constant value. This is usually 30 days per cycle with base quantum of measurement being a single calendar day. Starting point of the calender is fist finalized and all other reference points such as week, month, quarter etc. are derived. The beginning of 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 is 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 specific set of weeks as mentioned in the name. For example, first and second period 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 case by case basis. Base quantum of measurement for this calendar type is day and week. Similar to MOC calendar, there are residual time periods that are handled based on business requirement or sometimes rolled over to the subsequent year.