My question pertains to slowly changing dimensions. We need to keep history so we plan on inserting a new row when the dimension changes. What are some techniques to capture the change on the OLTP database and apply that change to the warehouse? What are some surrogate keys structures to use to identify the changed dimension?
Chuck Kelley's Answer: This is one of the most interesting issues (from a data management perspective) in data warehousing. There are a number of ways to capture changing data. The only question is which is best for me. Some (but certainly not all!) of the ways include:
In terms of surrogate key structures, you can use a dual primary key with the first part being the surrogate and the last being a version control. This is sometimes used, but doubles the amount of keys for the fact table. Another option is to create two surrogates - 1) one that is truly sequential that links to the fact file and 2) a combination of sequential number (which is a new "key") and some type of version.
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