Slowly Changing Dimension Handling in Data Warehouses Using Temporal Database Features
This paper presents the use of temporal database features to solve the Slowly Changing Dimension (SCD) problem of data warehouses. The SCD problem is presented and existing solutions, together with their limitations are shown. Temporal database features of SQL are described. Temporal data retrieval and temporal data manipulations, together with illustrated examples are demonstrated. The solution to the SCD problem is shown with illustrated examples. The data warehouse whose dimension tables are validtime state tables, but the fact table is a conventional fact table without any timestamp or validtime period, is proposed. The identifier integrity of dimension instances is preserved. The sample fact table, dimension tables, and the SQL codes which perform temporal operations to solve the problem are presented. The proposed solution gives correct results regardless of the number of changes made to the attribute of the dimension table, thus completely solves the Slowly Changing Dimension problem.
KeywordsSlowly Changing Dimension Temporal data warehouse Temporal SQL
- 1.Kimbal, R., Ross, M.: The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling, 3rd edn. Wiley, Indianapolis (2013)Google Scholar
- 4.Santos, V., Belo, O.: No need to type slowly changing dimensions. In: Proceedings of IADIS International Conference Information Systems 2011, Avila, Spain, pp. 11–13 (2011)Google Scholar
- 8.Workspace Manager Valid Time Support. https://docs.oracle.com/database/121/ADWSM/long_vt.htm
- 9.Snodgrass, R.T.: Managing temporal data – a five part series, Database programming and design, TimeCenter technical report (1998)Google Scholar