Calendar and MDM

The Hub Designs Blog welcomes a guest post by Rob DuMoulin, an information architect with more than 26 years of IT experience, specializing in master data management, database administration and design, and business intelligence.

Most business intelligence architects are well versed in the value of the time dimension.

With query performance and the need to support complex analyses being the two most important considerations in BI, a flattened set of time dimensions provides a multitude of options to represent and standardize time with limited overhead.

It’s easy to see the value of having a flexible, consistent, and integrated representation of time 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. And accounting departments group transactions into time periods every financial reporting period.

So, how valuable can this same time dimensions be to a Master Data Management solution? If you are well versed in MDM at this point, you’re probably saying “What you’ve talked about so far is useful for relating transactions but it doesn’t tie back to mastering business objects like customers, products, or locations”.

But remember that mastering those objects does require standardization during information acquisition and publishing and that the various inputs and outputs to an MDM system are often diverse. Also, don’t underestimate the value of mastering “Time Tables” themselves as a component in your MDM universe.

First, let’s define just what we mean by a set of time tables before we apply them to MDM. A typical implementation would have two distinct groups of 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.

Related to the day-level table are 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, and month overall, in addition to quarter and year and all the ways to call the month. 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.

The best thing 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.

Introducing a robust set of Master Time dimensions into an MDM architecture opens up flexibility in how you consolidate information and also how you can apply it to many business purposes. It’s a natural expansion of MDM to include a master version of the corporate calendar (particularly the fiscal calendar) using a common set of time-related identifiers complete with any time references relevant to business operations.

Please let us know what you think of mastering the Time dimension or other types of corporate reference data in the MDM hub by leaving a comment here.