Timeless Dimensional Model

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?

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:

• Metadata repository. Andrea Vincenzi, data warehouse architect, says, “I can have a factless fact table linking different metadata (for example, a field on a flat file to a field on a table in the staging area). This fact table can be used to do impact analysis, and it doesn’t need time unless we also want to analyze the history of these relationships.” Warren Cotton, manager, supports this example. “Actually, we have such a database in our group. For cross-reference purposes, it relates logical model files, metadata repository models and change management model names to a rather abstract data model entity.”
• Text analysis. Tim Graham says, “Assume my requirement was to analyze the text of all the books in my collection to find word frequencies, character name frequencies and identify key concepts. Then I can come up with various dimensions and facts. But I don’t necessarily need a time dimension unless the requirements involve analysis by date of publication, age of author or something else time related.”
• Test questions. Barry McConnell, data architect, says, “We can create a data mart to analyze the results of students taking that test and would want a time dimension to see if we’re improving or not. However, we may also want to analyze the integrity of the test questions themselves. For that, we need to know the questions, the correct answer and all of the answers provided by students this year. As both the questions and the students change from year to year, there is no value in analyzing across time and therefore no need for a time dimension.”

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