Analyses and Evaluation of Responses to Slowly Changing Dimensions in Data Warehouses

Analyses and Evaluation of Responses to Slowly Changing Dimensions in Data Warehouses

Lars Frank (Copenhagen Business School, Denmark) and Christian Frank (Copenhagen Business School, Denmark)
DOI: 10.4018/978-1-60566-232-9.ch016
OnDemand PDF Download:


A Star Schema Data Warehouse looks like a star with a central, so-called fact table, in the middle, surrounded by so-called dimension tables with one-to-many relationships to the central fact table. Dimensions are defined as dynamic or slowly changing if the attributes or relationships of a dimension can be updated. Aggregations of fact data to the level of the related dynamic dimensions might be misleading if the fact data are aggregated without considering the changes of the dimensions. In this chapter, we will first prove that the problems of SCD (Slowly Changing Dimensions) in a datawarehouse may be viewed as a special case of the read skew anomaly that may occur when different transactions access and update records without concurrency control. That is, we prove that aggregating fact data to the levels of a dynamic dimension should not make sense. On the other hand, we will also illustrate, by examples, that in some situations it does make sense that fact data is aggregated to the levels of a dynamic dimension. That is, it is the semantics of the data that determine whether historical dimension data should be preserved or destroyed. Even worse, we also illustrate that for some applications, we need a history preserving response, while for other applications at the same time need a history destroying response. Kimball et al., (2002), have described three classic solutions/responses to handling the aggregation problems caused by slowly changing dimensions. In this chapter, we will describe and evaluate four more responses of which one are new. This is important because all the responses have very different properties, and it is not possible to select a best solution without knowing the semantics of the data.
Chapter Preview


A data warehouse is an OLAP (On Line Analytical Processing) database (Codd, 1993 and Codd et al. 1993), where the data is loaded/updated periodically. In other words, a data warehouse is not an OLTP (On Line Transaction Processing) database (e.g., Gray and Reuter, 1993). The data warehouse drill functions (e.g., Kimball et al., 1998) have been developed to accommodate the special needs for aggregating the data stored in the fact table of a data warehouse.

The traditional drill-down functions use the one-to-many relationships of the data warehouse to find more detailed information. If we take accumulated data as an example, the drill-down function will show the more detailed data elements of the accumulated data. The roll-up function can use the one-to-many relationships of the data warehouse to generate an overview of the detailed information. However, the aggregating drill functions may give misleading results as old fact data may be aggregated to dimension levels that have changed since the fact data were created. In this paper, we will evaluate the following 7 different techniques for handling the aggregation problems of slowly changing dimensions (Responses to SCD). The first three responses are the classic techniques described by Kimball et al. (1998).

  • Type 1 response: Overwrite the dimension record with the new values by which historical information is lost.

  • Type 2 response: Create a new additional dimension record with the current information.

  • Type 3 response: Create a “Previous” field in the dimension record to store the immediate previous attribute value.

  • Type 4 response: Create a new dimension using a dynamic dimension hierarchy

  • Type 5 response: Store dynamic dimension data as fact data

  • Type 6 response: Use finer granularity in combination with response 1 or 3

  • Type 7 response: Store the dynamic dimension data as static facts in another data mart

Table 1 gives an overview of the most important properties of the different responses, and in section 4 the evaluation is described in more details. The most important evaluation criterion for responses to Slowly Changing Dimensions is whether the responses preserve historical information. In the next section, we will illustrate by examples that it is the semantics of the data that determine whether historical information should be preserved. That is, both history preserving and history destroying responses are important. Therefore, it is important to know that only the Type 1 and 3 responses can overwrite historical information in an effective way. The Type 3 and 6 responses do not preserve all the details of historical information but may be a good choice in special situations. Response Types 2, 4, 5, and 7 preserves historical information in an effective way but it is often application dependent to select the best solution as e.g. performance depends on what data is aggregated.

Complete Chapter List

Search this Book:
Editorial Advisory Board
Table of Contents
David Taniar
Chapter 1
Laila Niedrite, Maris Solodovnikova Treimanis, Liga Grundmane
There are many methods in the area of data warehousing to define requirements for the development of the most appropriate conceptual model of a data... Sample PDF
Development of Data Warehouse Conceptual Models: Method Engineering Approach
Chapter 2
Stefano Rizzi
In the context of data warehouse design, a basic role is played by conceptual modeling, that provides a higher level of abstraction in describing... Sample PDF
Conceptual Modeling Solutions for the Data Warehouse
Chapter 3
Hamid Haidarian Shahri
Entity resolution (also known as duplicate elimination) is an important part of the data cleaning process, especially in data integration and... Sample PDF
A Machine Learning Approach to Data Cleaning in Databases and Data Warehouses
Chapter 4
Maurizio Pighin, Lucio Ieronutti
Data Warehouses are increasingly used by commercial organizations to extract, from a huge amount of transactional data, concise information useful... Sample PDF
Interactive Quality-Oriented Data Warehouse Development
Chapter 5
Dirk Draheim, Oscar Mangisengi
Nowadays tracking data from activity checkpoints of unit transactions within an organization’s business processes becomes an important data resource... Sample PDF
Integrated Business and Production Process Data Warehousing
Chapter 6
Jorge Loureiro, Orlando Belo
OLAP queries are characterized by short answering times. Materialized cube views, a pre-aggregation and storage of group-by values, are one of the... Sample PDF
Selecting and Allocating Cubes in Multi-Node OLAP Systems: An Evolutionary Approach
Chapter 7
Jorge Loureiro, Orlando Belo
Globalization and market deregulation has increased business competition, which imposed OLAP data and technologies as one of the great enterprise’s... Sample PDF
Swarm Quant' Intelligence for Optimizing Multi-Node OLAP Systems
Chapter 8
Franck Ravat, Olivier Teste, Ronan Tournier
With the emergence of Semi-structured data format (such as XML), the storage of documents in centralised facilities appeared as a natural adaptation... Sample PDF
Multidimensional Anlaysis of XML Document Contents with OLAP Dimensions
Chapter 9
Hanene Ben-Abdallah, Jamel Feki, Mounira Ben Abdallah
Despite their strategic importance, the wide-spread usage of decision support systems remains limited by both the complexity of their design and the... Sample PDF
A Multidimensional Pattern Based Approach for the Design of Data Marts
Chapter 10
Concepción M. Gascueña, Rafael Guadalupe
The Multidimensional Databases (MDB) are used in the Decision Support Systems (DSS) and in Geographic Information Systems (GIS); the latter locates... Sample PDF
A Multidimensional Methodology with Support for Spatio-Temporal Multigranularity in the Conceptual and Logical Phases
Chapter 11
Francisco Araque, Alberto Salguero, Cecilia Delgado
One of the most complex issues of the integration and transformation interface is the case where there are multiple sources for a single data... Sample PDF
Methodology for Improving Data Warehouse Design using Data Sources Temporal Metadata
Chapter 12
Shi-Ming Huang, John Tait, Chun-Hao Su, Chih-Fong Tsai
Data warehousing is a popular technology, which aims at improving decision-making ability. As the result of an increasingly competitive environment... Sample PDF
Using Active Rules to Maintain Data Consistency in Data Warehouse Systems
Chapter 13
Marcin Gorawski, Wojciech Gebczyk
This chapter describes realization of distributed approach to continuous queries with kNN join processing in the spatial telemetric data warehouse.... Sample PDF
Distributed Approach to Continuous Queries with kNN Join Processing in Spatial Telemetric Data Warehouse
Chapter 14
Maria Luisa Damiani, Stefano Spaccapietra
This chapter is concerned with multidimensional data models for spatial data warehouses. Over the last few years different approaches have been... Sample PDF
Spatial Data Warehouse Modelling
Chapter 15
Jérôme Darmont
Performance evaluation is a key issue for designers and users of Database Management Systems (DBMSs). Performance is generally assessed with... Sample PDF
Data Warehouse Benchmarking with DWEB
Chapter 16
Lars Frank, Christian Frank
A Star Schema Data Warehouse looks like a star with a central, so-called fact table, in the middle, surrounded by so-called dimension tables with... Sample PDF
Analyses and Evaluation of Responses to Slowly Changing Dimensions in Data Warehouses
About the Contributors