Delta view generation for incremental loading of large dimensions in a data warehouse

Incremental load is the preferred approach in efficient ETL processes. Fact tables are the ones who benefit the most from this approach, since they are large in terms of row count. For the sake of simplicity, dimension tables are often ignored and populated in a full reload manner. However, big dimensions (e.g. Client) can also have a significant impact on the ETL process and should also be considered for incremental load. Although they have much smaller cardinality than a typical fact table, it usually takes much more resources to calculate one dimension table row than to calculate one fact table row. Large dimension tables are based on multiple source tables, and it is not trivial to determine the changed records that should be considered for the incremental load because changes in any and all of underlying source tables must be considered. In this paper, we present an algorithm for the dimension's delta view generation. Delta view for a dimension encompasses all its source tables and produces a set of keys (e.g. ClientIds) that should be incrementally processed. We have employed this approach in a real world project and have noticed a significant reduction in the loading time of big dimensions.