Designing tables for slowly-changing dimensions (SCDs) is one of the most difficult tasks in a database supporting BI/DW applications. SCDs can grow quite large; it is not unusual for some to contains hundreds of millions of rows, so how do we meet the performance requirements of the end-user queries against this monstrous object, and how do we do so while continuing to load large amounts of data to it? The answer lies in understanding how a time-variant SCD is queried, and then building an optimal table structure to meet each of those requirements. For example, the run-time users from the DW/BI application need to see the "type-2" or "type-3" time-variant view of the slowly-changing dimension, while the data-loading (ETL or ELT) processing needs to see the current point-in-time (i.e. "type-1") view of the SCD for loading new data. How can these differing requirements be satisfied by one table? Short answer: they cannot. This presentation provides a viable solution for consideration, complete with sample code for implementation.
Become a member of ODTUG to gain access to more than 2,500 files in our technical database.
- Not a member? Click through to the topic of interest to browse a list of available presentations.
- Already a member? Log-in here to access the full database
Click here to see the Full Techincal Resource Database