FREE email newsletters 
Timeless Dimensional Model
Design Challenge
Information Management Magazine, February 2008
When I teach dimensional modeling, I always stress the need for a time
dimension. Recently in a session, a participant (Bill Nagel, managing
consultant) challenged the group by asking if a dimensional model can ever exist
without a time dimension. What do you think? Advertisement The Response A time dimension can add substantial value to business questions. Steve
Turnock, database engineer, states that without the integrity from a time
dimension, the users may be responsible for applying the appropriate business
rules for summarizing the data at different time granularities. In terms of the
importance of a time context, Stephen Pace, senior consultant, submitted an
actual dimensional model he developed using business intelligence (BI) tools,
which proved the uselessness of certain queries that do not constrain time. For
example, the question, “What do we pay our employees?” without a time constraint
produces meaningless results. Despite the importance of a time dimension, there are three situations where
a time dimension may be unnecessary: snapshot, time independent and timestamp
sufficient. Snapshot Johnny Gay, data analyst, and Ken Hansen both believe that when the
requirements only need the event data captured as of a particular time, a
snapshot of the event data may be sufficient. Wade Baskin, senior database
architect, gives an example, “Let’s say that we want to track sales by week, for
a rolling 52 weeks. You could create fact tables called Week 1 Sales, Week 2
Sales, Week 3 Sales.” Bhargav Mantha, architect, also provides a snapshot
example, “In the volatile stock market, where it is known the time averages out
most of the fluctuations, it might help some traders to view stock performance
through other parameters only for the current day. So, the fact table gets
refreshed every day at the close of market and has data pertaining to the
current day trades.” Ernie Loomis, industry data modeler, offers this example,
“If the model were the current configuration of a network topology for a telco,
you would not need a time dimension since the domain of interest is the current
state only.” Time Independent There are a number of applications that do not require reporting by time,
such as: Timestamp Sufficient Norman Daoust, business and data analyst consultant and trainer, says it can
be possible in certain situations to get by with a time attribute in the fact
table instead of a time dimension. “Here’s a real-life example from a system I
worked on. Its purpose was to return a count or list of patients with a
specified diagnosis and having other relevant attributes. There was no time
dimension table, although there was a date of diagnosis in the fact table.” If
you would like to become a Design Challenger and have the opportunity to submit
modeling solutions, please add your email address at http://www.stevehoberman.com/. If you
have a challenge you would like our group to tackle, please email me a
description of the scenario at mailto:me@stevehoberman.com
Steve Hoberman is one of the world's most well-known data modeling gurus. He understands the human side of data modeling and has evangelized “next generation” techniques. Steve taught his first data modeling class in 1992 and has educated more than 10,000 people about data modeling and business intelligence techniques since then. He has presented at more than 50 international conferences, and his third book, Data Modeling for the Business, is hot off the press.
For more information on related topics, visit the following channels:
