As I have discussed in the past, it is critical that you design and build single grain fact tables. This requires that there is one and only one level of detail represented in the fact table for each dimension. This also means that there must be a value in every row for every dimension key - no nulls are allowed! Also, the logical primary key of a fact table is the collection of foreign keys from your dimensions. A physical primary key is the collection of columns that will uniquely identify a row in a table. Most of the time, the logical and physical primary keys will be the same. However, they do not need to be.

Consider a basic purchase order (P.O.) scenario. The purchase order is described by customer, order date, requested ship date and sales representative. These are the dimensions. To uniquely track the individual order, the purchase order number is also a dimension. There would be several fact columns as well. See Figure 1 for an example of the purchase order fact table.

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