The Slowly Changing Dimension Transformation could be in use in SSIS for the ETL process . SSIS
coordinates the updating and inserting of records in data warehouse dimension tables. For example, you can use this transformation to configure the transformation outputs that insert and update records in the DimProduct table of the AdventureWorksDW2008R2 OLAP database with data from the Production.Products table in the AdventureWorks2008R2 OLTP database. However, since it has the mechanism of each single row comparison and accordingly update row by row, the speed is very slow when it process a very large dimension table with huge number of records. Therefore the open-source plug in 'Kimball Slowly Changing Dimension'could be used to raise up the performance, along with some valuable functionalities.
Two main types of SCD in use.
SCD Mapping:
SCD 1 : 插入新的维度。可选择用已改变的维度的值覆盖原有的值。
SCD2: 插入新增的维度和改动的维度的值。创建一个有效的数据范围以跟踪数据改动。
SCD2举例:Dimension Customer中有两列Start_Date, End_Date.
Customer Example1, 含有列Name, City, Address, Phone NO.,Start_Date, End_Date.
Wang, Beijing,GuangHua Road,123456, 1753-01-01,9999-12-31
当这个Customer电话号码从123456更新到654321时,我们插入新的一行:
Wang, Beijing,GuangHua Road,654321, 2011-06-09,9999-12-31
并将原来的一行更新为
Wang, Beijing,GuangHua Road,123456, 1753-01-01,9999-12-31
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12374161/viewspace-697514/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12374161/viewspace-697514/