Information Integration and Multiple Slowly Changing Dimensions Modeling

Abstract

Information integration for analytics and business intelligence activities from difference data sources in different formats and different database systems necessitates the use of data warehouses. Different data format and coding of the data sources requires extract, transfer, load, or ETL operations to enterprise data warehouses. Fact and dimension tables are main data structures in typical data warehouses. A typical fact table relates to several dimension tables, one of which is a time dimension. A fact instance is based on a point in time. The time granularity depends on the users’ requirements. Dimension tables comprises several attributes, some of which may be time varying over periods of time. These dimensions with time-varying attributes are called slowly changing dimensions (SCD). SCD may cause incorrect analytic problems. Known proposed solutions still have deficiencies. This paper presents a temporal data warehouse. It is a data warehouse which allows multiple temporal attributes for each time varying dimension and solve the SCD-related problems. The proposed design can be implemented by using temporal relational database technology which is currently a part of the SQL standard. Thus, improves productivity, reduces development time, and ease application maintenance. Key temporal data warehouse operations using the temporal features of SQL:2011 are demonstrated.