Continue in 2 seconds

We are looking at the future architecture of our DW.

  • August 01 1999, 1:00am EDT


We are looking at the future architecture of our data warehouse. We currently have several account-level fact tables developed for different applications. These duplicate a lot of the data, and I am wondering whether this is the correct approach. Would it not be better to consolidate these into one fact table for the enterprise and then have different aggregates and access methods for each application? Should the detailed level fact tables be part of the enterprise data warehouse, or should they be data marts for specific purposes?


Doug Hackney's Answer: The first rule is that there is no one, single "right way" to do data warehousing, so I encourage you to not get lost in the morass of fanatical viewpoints surrounding this seemingly simple question you pose. Be pragmatic, and avoid signing up to be an acolyte in the religious wars of data warehousing.

A couple of basic guidelines:

  • There are several valid approaches to addressing your challenge.
  • The overriding factor must be the needs of the users. You must manifest to them whatever level of detail and history required to address their needs, regardless of the amount of replication, denormalization, etc. required.

If you have a moderate to high amount of end-user access into the entire collections of data (the data warehouse data set), then it makes sense to have a star DW. If this is the case, then a master fact table containing all the records that you subsequently partition to create subset data marts would be the likely choice. If your user interaction with the data is almost exclusively at the data mart level, then a more normalized staging area (in a "bottom up" incremental architected data mart scenario) or normalized parent DW is a valid choice. In this latter option, you will likely have multiple transaction tables that are fairly normalized, thereby limiting the chances that you would have a single source for your data mart fact tables.
The key issue here is do not fall prey to the "urban legend" that data marts do not contain detail. This is a fallacy. Data marts contain whatever level of detail and history is required to meet the needs of the users. If this necessitates replication of detail, then so be it. If your infrastructure supports remote drill through into the detail with no loss of performance or capability from the user perspective, then that is also a valid option.

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