Contact us for additional information

Business Intelligence Architects are well versed in the values of time dimensions. With query performance and the need to support complex analyses being the two most important considerations of BI, a flattened set of time dimensions provide a multitude of options to represent and standardize time with limited overhead.

It is easy to see the value of having a flexible, consistent, and integrated time representation when thinking of business activities. Aspects such: as when a transaction or activity occurs in relationship to other transactions, activities, or even pre-defined thresholds form the basis of Business Process Management activities. Grouping transactions into time periods occur each financial reporting period by accounting departments.

So, just how valuable can the same time dimensions be to a Master Data Management solution? If you are well versed in MDM at this point, you are saying "Yea, but what you have talked about thus far is for relating transactions and does not relate to mastering business objects like customers, products, or locations". Remember that mastering those objects requires standardization during information acquisition and publishing and that the various input and outputs to an MDM system are often diverse. Also, don't underestimate the value of mastering "Time Tables" themselves as a component to your MDM universe.

First, let's define just what I mean by a set of time tables before we apply them to MDM. A typical implementation would have two distinct groups of fairly static tables to represent time: day, and time-of-day. At the lowest level of the day group is a day-level table with every imaginable way the business can identify a day, such as: by its day of year, week, month, quarter, advertising week (for retail), same day last year (in some special context), or special tags like holiday, weekend, season, positional sunrise/sunset times, or even astrological sign and full moon cycles. And that just covers the calendar view of the business. There is an equally important and extensive set of calendar hierarchies and attributes associated with the business fiscal reporting needs. Add to that every way you want to represent attributes like day of the week or month of the year (number, 3-letter abbreviation, full name) and ending up with over 100 attributes in the day-level table is not uncommon.

For BI, hierarchy tables at levels such as: month, quarter, year (and their fiscal counterparts). Each of the hierarchy tables contains all the attributes that define that level and higher levels. For example, the calendar month table would contain attributes defining month of year, month of quarter, month of fiscal period, and month overall, in addition to the quarter of the year and year and itself. Primary keys for the higher level hierarchy tables, like month, would have child entries in the lower level tables, like day, for every entry that rolls into the higher level.

The same holds true for time of day, with hierarchies like hour, minute of hour, shift, peak time, off-peak time, and others.

Because all the higher-level attributes are repeated in the lower-levels, there is typically not a compelling need to join the two tables. The relationships are there for flexibility. Having the various hierarchy tables as stand-alone entities allows you to attach them to business tables at all of the levels you collect or report time values. These tables and hierarchy relationships allow you to easily merge data of different time grains and use the structures for different business purposes.

The best attributes about time is that time is constant. There are always sixty seconds to the minute, sixty minutes to the hour, twenty-four hours to the day (excluding Daylight Savings Time adjustments), seven days to the week, the number of days to the month is fixed, the number of days in a year is predictable. Except for adjustments to fiscal calendars and special events, most of the information related to time hierarchies is static.

BI uses these techniques to conform information allowing it to readily apply to many views of the business....which sounds a lot like the same business issues we try to solve when integrating data within an MDM solution. It is a natural expansion of MDM to include reference data such as time. Introducing robust set of Master Time dimensions into an MDM architecture opens up flexibility in how you consolidate information and also how you can manage one source for your business calendar across your operations.  Lastly, if your business is global, there is even more reason to standardize on a global calendar that either conforms time into a single time zone or seamlessly allows local and global calendars to coexist.