首页 > 代码库 > 使用SSIS Slow Changing Transformation组件管理缓慢变化维

使用SSIS Slow Changing Transformation组件管理缓慢变化维

2024-07-15 06:08:53   225人阅读

最近尝试用SSIS自带的 Slow Changing Transformation组件处理缓慢变化维，看到有一篇文章写的很详细，就按照步骤进行操作同时进行翻译。原网址来自：Managing Slowly Changing Dimension with Slow Changing Transformation in SSIS。

 

介绍

作为数据库专家或者ETL的开发者你可能偶尔会碰到需要维护和管理缓慢变化唯的场景。在SQL Server中有多种方法来实现，最简单的是使用SSIS 数据流组件中的Slowly Changing DimensionTransformation。

在这片文章中，我会通过一个例子提供如何使用SSIS的Slowly Changing DimensionTransformation管理缓慢变化唯的步骤和指导。

 

理解缓慢变化维的场景

 

维度是数据管理和数据仓库中的术语。它指逻辑分组数据比如地理位置，客户或者产品信息。通过缓慢变化维(SCDs),数据缓慢变化而不是基于时间，定期的变化。~Wikipedia

有不同类型的缓慢变化唯独：

  • SCD Type 0     (Fixed) – 这种类型是最不常用的，在第一次插入后就固定了不接受变化。这意味着一旦写入，这些数据就不会被覆盖。
  • SCD Type 1     (Changing) – 这种类型，如果数据被更改，她会被新的值覆盖。

相关文章：

    • SQL Server 2012 Integration Services - Package      Deployment
    • SQL Server 2012 Integration Services - Package and      Project Parameters
    • SQL Server 2012 Integration Services - Package      Variables
    • SQL Server 2012 Integration Services - Package and      Project Configurations
    • SQL Server 2012 Integration Services - Unattended      Execution of SSIS Packages
    • SQL Server 2012 Integration Services - GUI-Friendly      Ways of Managing Execution of SSIS Packages

例如考虑这个例子：　

 

SupplierCode

SupplierName

Address

S0000001

ABC Company

USA

S0000002

XYZ  Corporation

USA

 

如果供应商的名字随着时间的推移被更改，正如你在下面看到的供应商的名字已经被新的记录更新了。这种看起来非常简单去实现，但是无法追踪历史记录。

 

SupplierCode

SupplierName

Address

S0000001

ABC Company Ltd.

USA

S0000002

XYZ  Corporation

USA

 

  • SCD Type 2     (Historical) –在这个类型中，如果数据被更改，它将会保存一个新的记录，旧的记录被标志位过时的。

 

SupplierCode

SupplierName

Address

EffectiveDate

Expiration  Date

S0000001

ABC  Company

USA

3/2/2013

3/2/2013

S0000002

XYZ  Corporation

USA

3/2/2013

S0000001

ABC Company Ltd.

USA

3/3/2013

 

  • 为了维护SCD     type 2，不同的人采用不同的方法。比如，一种方法是通过增加有效日期和过期日期表示记录是活跃的。如果截止日期为NULL表示当前的记录是活跃的。另外一种方法是添加一个标志列表示当前活动记录。通常人们会使用第一种方法或者两者的结合。
  •  
  • SCD Type 4     (Limited history) –这不是一个常用的类型因为只能维护有限的更改。在这种ＳＣＤ类型中，通过表中添加额外的列保存旧值。

 

SupplierCode

SupplierName

Address

OldSupplierName

S0000001

ABC  Company

USA

ABC Company Ltd.

S0000002

XYZ  Corporation

USA

 

在SQLServer中有多种方法实现缓慢变化维度，最简单的是使用SSIS 数据流组件中的Slowly Changing Dimension Transformation，尽管会有一些限制，文章结尾的时候会提到这些限制。

在我开始Slowly Changing Dimension Transformation组件解释之前，让我先解释一下代理键并且为什么它对数据仓库很重要。我们经常会在维度中增加一个没有意义的键叫做代理键。代理键通常是整数，充当唯独表的唯一键或者主键，并且作为事实表外键约束。代理键对于管理缓慢变化唯变得非常重要。

 

使用Slowly Changing Dimension Transformation

 

我们首先创建一个供应商表并添加一些数据。你应该可以看到，我增加了SupplierCode字段作为主键，当作业务键。

 

USE [AdventureWorks2012]

GO

CREATE TABLE[dbo].[Supplier](

       [SupplierCode] CHAR(8) PRIMARY KEY,

       [SupplierName] [varchar](50)NULL,

       [Address] [varchar](50)NULL,

) ON[PRIMARY]

GO

INSERT INTO[dbo].[Supplier]([SupplierCode],[SupplierName],[Address])

VALUES

(‘S0000001‘,‘ABC Company‘,‘USA‘),

(‘S0000002‘,‘XYZ Corporation‘,‘USA‘)

GO

SELECT *FROM[dbo].[Supplier]

 

现在我们创建一个维度表存储供应商信息，你应该注意到我增加了SupplierId字段作为代理键，生效时间和截止时间用来追踪历史变化。另外我增加了CurrentFlag列用来标注当前记录是否活跃。

 

USE [AdventureWorks2012]

GO

CREATE TABLE[dbo].[DimSupplier](

       [SupplierId] [int] IDENTITY(1,1)NOTNULL,

       [SupplierCode] CHAR(8),

       [SupplierName] [varchar](50)NULL,

       [Address] [varchar](50)NULL,

       [EffectiveDate] [date]  NULL,

       [ExpirationDate] [date]  NULL,

       [CurrentFlag] [char](1) NULL,

       CONSTRAINT [PK_DimSupplier]  PRIMARY KEY CLUSTERED ([SupplierId]ASC)

) ON[PRIMARY]

GO

 

到目前为止一切顺利，现在我们创建一个SSIS包，增加一个数据流任务，拖入数据源组件从原始表获取数据。现在新增一个SlowlyChanging Dimension Transformation组件连接到上面的数据源组件。双击SlowlyChanging Dimension Transformation进行修改，向导界面如下：

　


Slowly Changing Dimension Wizard

 

单击下一步按钮进入到下一个界面，在新的界面首先要选择目标维度表和字段映射。紧接着你需要从源表中指定例作为业务键。在我的例子中SupplierCode是源表的主键因此我把它作为业务键，如下：

 


Business key

 

点击Next按钮进入到向导的下一界面，在这个界面中你需要指定维度表中的字段是否被当作SCDType 0, Type 1 or Type 2处理。

 


Specify each column of the dimension

 

在我的例子中我选择地址列作为SCD Type 1处理，名字作为SCD Type 2处理，如下：　

 


SCD Type 1 and SCD Type 2

 

点击Next进入向导的下个界面，在这个界面需要指定（因为我们一个列作为SCD Type 2处理）开始日期（生效日期）结束日期列（有效期），和设置产生日期的变量，如图：


Start and End Dates

 

点击Next进入向导的下个界面，在这个界面指定推断维度成员设定如图：

 


Inferred Dimension Members

 

点击Next进入向导的下个界面，点击完成按钮完成配置。下面就是在数据流任务中看到的：

 


Complete the Wizard

 

缓慢变化维度向导会根据你的选择和配置添加几个任务管理缓慢变化维。在上面屏幕中“Changing Attribute Updates Output”路径将为SCDType 1（基本覆盖）更新记录。“New Output”路径将增加新条目到唯独表，为了维护历史记录。“Historical Attribute Inserts Output”路径会更新之前过期日期列的记录。

 

当你第一次执行包你会注意到源表中的两条记录被加载到维度表，如图：

 


The Dimension Table

 

现在执行下面的语句验证Supplier维度表的数据：　

 

USE [AdventureWorks2012]

GO

SELECT *FROM[dbo].[DimSupplier]

GO

 

这是你在执行上面的脚本后看到的结果，跟我们预期的差不多：

 


Results of executed query

 

现在我们打开源表使用下面的脚本更新一些记录。我将要更改SupplierCode = ‘S0000001’的供应商名称。

 

USE [AdventureWorks2012]

GO

UPDATE [dbo].[Supplier]

SET [SupplierName]=‘ABC Company Ltd.‘

WHERE [SupplierCode]=‘S0000001‘

GO

SELECT *FROM[dbo].[Supplier]

GO

 

现在再次执行包，你会看到一条记录（新）已经插入并且一条记录（旧的）已经被更新或者标记为过时。这是因为更新的列被配置成SCD Type 2：

 


One record inserted and one record outdated

 

现在执行上面的查询语句验证数据。正如我们预测的，SupplierCode = ‘S0000001’有两条记录。前面的记录已经更新了截至日期表面数据已经是过时的，最新的记录对应了最新的供应商名称：

 

USE [AdventureWorks2012]

GO

SELECT *FROM[dbo].[DimSupplier]

GO

 


Query results

 

限制：

Slowly Changing Dimension transformation 被设计为简单易用，主要为了小的维度表。如我们上面看到的，Slowly Changing Dimension Transformation是一个开箱即用的SSIS组件，对于小的维度可以快速配置。但是Slowly ChangingDimension Transformation并不适合所有的情况尤其是你的维度很大，下面是一些原因：

·        Slowly ChangingDimension transformation根据你的配置将组件添加到数据流任务去管理缓慢变化维。如果在这些组件上做了一些定制化，然后再次修改Slowly Changing Dimension transformation你做的定制化会丢失。

  • 对于大的维度因为没有缓存查找数据，性能会很慢。
  • 只可以用到SQL     Server。
  • 它使用OLEDB命令进行行更新而不是批量更新。

Conclusion结论

在这篇文章中,我谈到了缓慢变化维度转换,在SSIS提供开箱即用的工具包,可以轻松快速地配置用于管理较小的缓慢变化维度。在下一篇文章中我将讨论一些备选方案,您可以使用管理更大的缓慢变化维度。

 


联系
我们