This is the second in a series of articles on things to look for in evaluating a data warehouse data model. The classic definition of a data warehouse is that it is a "subject-oriented, integrated, non-volatile, and time- variant collection of data in support of management's decisions."1 A good data warehouse data model clearly demonstrates the time-variant characteristics of the data warehouse. Strategic analysis often requires information on events that transpire over a period of time, and the data warehouse should retain the needed history. For some events, the date (and sometimes the time) needs to be part of the key, and for others it can be a non-key attribute. If the entity can only occur at one point in time, then the date may be an attribute. If it can occur at several points in time, then the date and time must be part of the key. A sales transaction, for example, occurs at a specific point in time. If each sales transaction is uniquely identified, then the date of the transaction should not be part of the key. Information retained about a customer, on the other hand, may change over time. Therefore, for the volatile customer information, the date needs to be part of the primary key.

Sometimes, the date is not part of the key, even for volatile information. If no history of the customer data is retained in the data warehouse (e.g., the table is refreshed with each load cycle) then, within the warehouse, there cannot be two instances of the same customer. In this case, the date can be a non-key attribute, but the business users must understand the ramifications of this decision. The history retention is ultimately a business decision; and as long as the users understand the limitations imposed by the decision, the model is valid.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access