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:
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.
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
Already have an account? Log In
Don't have an account? Register for Free Unlimited Access