SSIS高级转换任务—渐变维度（Slowly Changing Dimension）

渐变维度（SCD）转换提供了一种维护渐变维度或渐变表的方法，渐变维度的分析处理是一个很大的话题。一般一个维度表通常包含一些描述其他信息的离散值，例如，价格，重量，销售地区。问题是如果随着时间的推移这些信息改变我们将如何维护这个表，特别是在数据转移的过程中。例如我们查看AdventureWorks中的product表，如图1

图1

　　

通常在联机事务处理（OLTP）数据库中，我们会用一行的数据来保存产品信息。如果产品的售价从10美元上涨到15美元，我们直接更新StandardCost字段为15，这样做完成了任务：现在的售价是多少？但是丢失了历史数据，我们不知道涨价之前的价格是多少？价格上涨了多少？解决这个问题有三种基本的选择，具体选择哪一种要基于这个表的我们关注点和想要得到什么样的结果，我们将这些基本的选择简单地命名为类型1，类型2，类型3

  • 类型1：重写历史—通过使用新的产品价格来替换掉当前的产品价格，当然这样会丢失历史值。这个是最简单的做法。
  • 类型2：保留历史—添加一行新的完整的数据，保留历史数据行，当然这样会有一些副作用，这样做使得这个表更加复杂，占用空间更大。
  • 类型3：仅保存上一次历史—添加额外的属性，仅保存上一次历史值，这样我们只能看到产品价格变化中的两次价格。这种方法不常用。

注意：类型3不能直接实现，需要借助其他的task才能实现

　　

和其他task不同，渐变维度转换提供一个向导设置，通过问答的方式来设置，类似于在计算机中安装一个新的程序时使用的安装向导。高级设置提供更多的选择，但是在我们完全掌握它之前最好还是使用向导设置。双击维度转换标签或者右击选择编辑都可以打开向导。设置好之后将会产生一些更新和插入task来完成更新或插入的任务。图2显示的设置好之后的界面。可以看到这个task和其他的不同，它是由多个task组合而成的。

图2

　　

渐变维度是一个自动化的task，概括地说渐变维度接收输入数据和一个维度表数据，通过配置信息产生至少两个输出，并且这些输出总共有6种类型，最后使用OLE DB命令来完成数据库的更新，这6种可能的输出数据流如下：

  • 修改属性更新输出：这种输出属于类型1。在SCD中被选中作为可变项的属性都会和输入数据源做比较。如果匹配主键的两行属性值有差异，输入数据将会从这个输出流输出。
  • 历史属性插入输出：这种属性属于类型2。在SCD中被选中作为可变项的属性都会和输入数据源做比较。如果匹配主键的两行属性值有差异，输入数据将会从这个输出流输出。
  • 固定输出：在SCD中被选作固定的属性都会和输入的数据源做比较。如果匹配主键的两行属性值有差异，输入数据将会从这个输出流输出。这种输出流适用于类型3，但是必须自己编写sql语句。
  • 推断成员更新：在SCD的设置选中推断成员的时候，将会有这个输出流。如果需要将维度表转到另外一个表中需要设置使用这个输出流。这个输出可能在后面的操作中添加到维度表中。
  • 新输出：这个和历史输出一样都要合并到目标表中。
  • 不变的输出：默认没有这个输出。如果SCD发现输入数据和维度表中的数据没有差异，不会有任何操作。如果感兴趣的话你可以为这个输出建一个目标表，查看其中的数据。

说了这么一大堆，我们可以看看这些输出到底在什么地方，如上图2，左边第一个输出是修改属性更新输出，中间那根线是新输出，右边那根线是历史属性插入输出。

　　

在我们这里我们拿比较熟悉的员工工资这个例子来做一个示例。每个工资周期都会有一些费用被扣掉例如，个人所得税，保险税，医疗保险等等。为了模拟这些过程我们可能会新建一个表PayrollDeductItem类似于图3。PayrollEventFact参照PayrollDeductItem来更新。

 

图3

　　

现在假设一年的时间过去了，每个人的工资情况都会有些变动。我们的任务是来更新PayrollDeductItem这个表。我们发现有些工资项减少了，有工资项增加了，如果有些工资项删除了，则记为0。可以在启用新的工资标准之前执行一次更新，但是如果仅仅跟新这样会丢失工资走向信息。在这种情形下我们使用渐变纬度来解决这个问题，参照下面的步骤。

  1. 首先为了跟踪历史变更我们要新建一个表添加一些数据列。即使只有其中的一部分会在执行这个任务的时候用得到，还是要添加所有可能会用到的数据列。使用下面的sql语句创建一个表并添加数据：
    Use AdventureWorks
    GO
    CREATE TABLE [dbo].[PayrollDeductItem](
             [PayrollDeductItemID] [int] IDENTITY(1,1) NOT NULL,
             [EmployeePlanIDNbr] [varchar](50) NULL,
             [DeductionAmount] [money] NULL,
             [EmployeeID] [int] NULL,

             [PayDeductType] [char](10) NULL,
             [HistTextStatus] [char](10) NULL
                  CONSTRAINT [DF_PDI_HistTxtStatus] DEFAULT ('CURRENT'),
             [HistBitStatus] [bit] NULL
                  CONSTRAINT [DF_PDI_HistBitStatus] DEFAULT ('TRUE'),
             [HistStartDate] [smalldatetime] NULL,
             [HistEndDate] [smalldatetime] NULL,
         CONSTRAINT [PK_POLICY] PRIMARY KEY CLUSTERED
    (
          [PayrollDeductItemID] ASC
    )     ON [PRIMARY]
    )     ON [PRIMARY]
    GO
         INSERT INTO PAYROLLDEDUCTITEM(EmployeePlanIDNbr, DEDUCTIONAMOUNT, EMPLOYEEID,
          PAYDEDUCTTYPE, HISTSTARTDATE)
    SELECT '000000001', 200.00, 1, '401K', '01/01/2004'
    UNION
    SELECT 'ZZ0-10001', 10.00, 1, 'LIFE', '01/01/2004'
    UNION
    SELECT '000000002', 220.00, 2, '401K', '01/01/2004'
    UNION
    SELECT 'DC001-111', 10.00, 2, 'BUSPASS', '01/01/2004'
    UNION
    SELECT '000000003', 300.00, 3, '401K', '01/01/2004'
    UNION
    SELECT 'ZZ0-10003', 10.00, 3, 'LIFE', '01/01/2004'
    GO

  2. 创建一个文本文件，使用下面的内容作为工资变更信息：

    EMPLOYEEID,EMPLOYEEPLANIDNBR,DEDUCTIONAMOUNT,PAYDEDUCTTYPE,ENROLLDATE,COMMENT
    1,000000001,225,401K,'01/01/05',INCREASED 401K DEDUCTION
    1,ZZ0-10001,15,LIFE,'01/01/05',INCREASED LIFE DEDUCTION
    2,000000002,220,401K,'01/01/05',NO CHANGE
    2,DC001-111,0,BUSPASS,'01/01/05',TERMINATED BUSPASS DEDUCTION
    3,000000003,250,401K,'01/01/05',DECREASED DEDUCTION
    3,ZZ0-10003,10,LIFE,'01/01/05',NO CHANGE
    4,000000004,175,401K, ‘01/01/05',NEW 401K DEDUCTION
  3. 创建一个package，命名为SlowChangingDemension。创建一个Data Flow Task点击进入Data Flow界面。在Data Flow界面内下面的ConnectionManageers内新建一个Flat File Connection连接上面的文本文件，选择第一行作为列名如图4。在高级标签内设置EmployeePlanNbr和PayDeductType两个列的长度是10，设置EmployeeID的类型是32-bit Integer [DT_I4]，设置DeductionAmount的数据类型是currency[DT_CY]，如图5。

    图4

    图5
  4. 使用上述连接添加一个Flat File DataSource
  5. 添加一个渐变纬度任务，将Flat File DataSource和它连接起来

　　

查看PayrollDeductItem表的内容如图6，你可能会奇怪为什么会有一些多余的字段例如HistTextStatus， HistBitStatus， HistStartDate， HistEndDate，在执行task的时候并不是所有的字段都用得上，我们会根据不同的设置来使用不同的列。注意这个截图是我执行这个SCD之后的表，数据已经被修改。

图6

 初次打开SCD的时候，设置向导会展开四个设置步骤，这些步骤如下：

  1. 维度表和维度选择步骤：这个步骤中设置维度表的位置，输入数据和维度表中对应字段，和使用那些字段作为主键以便一一对应。那些需要对应的字段将会被覆盖或者更新，还需要设置那些字段作为主键字段，以便对应。双击打开Slow Changing Demension进入向导设置界面，点击下一步进入Select a Dimension and Keys界面，首先选择数据库AdventureWorks和这个数据库下的表PayrollDeductItem作为Dimension Table，Imcoming columns这一列中的数据一部分被作为Business key来匹配Dimension Columns中的对应列，剩下的作为更新数据源，这里EmployeePlanIDNbr，EmployeeID两列作为Business key，剩下的两列默认Not a key column。这里注意到必须选择一列作为Business key才能进入下一步设置。设置好的界面如图7

    图7
    　　
  2. 渐变维度设置步骤：这个步骤只关心在第一个步骤中未被设置为主键字段的的那些字段。在这个步骤中将设置按照什么样的策略来更新这些字段。在运行的时候目标表中的数据将按照这些策略来更新。这些cerulean如下：
    固定属性:  在维度表中的值是固定的，如果输入数据源中的数据有变动，将会报错。
    更改属性：维度表中的数据总是被输入数据源中的 数据覆盖掉。这是相面讨论的类型2。
    历史属性：维度表中的数据和输入源中的数据会有不同，但是有重大意义，将会被保存起来。
    如图8

    图8
    在这个例子中，点击下一步进入Slow Changing Dimension Clumns界面，上一个步骤中设置为默认Not a key column的两个列将会出现在这个步骤中，在这里他们作为更新Dimension Table的两个候选列。在Changing Type中选择修改属性为更新属性的或者历史属性，如果选择更新的，遇到匹配项的时候这一列的值将会被修改。如果选择为历史的将会评估变更，如果检测到更新，将会根据选择的历史变更添加一个新的行。这里我们做一个试验，将DeductionAmount和PayDeductType都选择为更新的。
    　　
  3. 固定和更新属性设置：点击下一步会看到如图9。在这里如果选择了固定属性，如果数据不同通过这个设置可以在运行时报错。前一个步骤我们没有选择更新属性，所以这里是灰色的不可用。另外一个选项设置是否覆盖当前活动数据，或者覆盖活动和实效的数据。

    图9
    　　
  4. 历史属性设置步骤：只有在第2步骤选择历史属性会有这二个设置步骤。如果设置了历史的属性，那就是类型2。有两种方法来保存历史数据。每一种都会新插入一个行。我们来看这两个设置。
    使用单独的行来显示当前的何过期的数据：这个选项允许在维度表中选择一个列，这个列用来标识这一行数据是老旧的，而另一行数据是更新的。在SCD中有两组值用来标识数据的时效性：True/False，Current/Expired。
    使用开始时间和结束时间来标识更新的和老旧的数据：这个选项会使用维度表中的的两个列来标识这一行的有效期的开始时间和结束时间。要注意的一点是要使用一个时间变量来给这些列赋值。
    这里既然DeductionAmount字段为历史的，PayrollDeductItem表中的一些看起来冗余的字段就有用了，这些字段可以标记这一行数据的不同的生效时间。选择变更类型为历史的时候字段HistStartDate和HistEndDate用来记录有效时间。当使用新加列的方法时这两个字段会变成false和Expired，新添加的一行将会是ture和Current。如图6-23示例说明如何使用HistStartDate和HisEndDate。设置时间值选项有一些系统变量，也可以使用自定义的变量，这里我们设置为System::StartTime然后点击下一步。
    　　
  5. 推断成员选项设置：当你从其他表中将数据导入到维度表中，但是维度表中的数据列不全或者你想过一段时间再执行它，可以在这个步骤中设置。这里我们不设置这个选项，如图10。

    图10
    　　
  6. 完成向导步骤：使用这个步骤来完成整个SCD的设置，如图11。

    图11

在这个例子中，我们要判断维度表中那些字段作为主键，在PayrollDeductItem表中我们可以判断出[EmployeeIDNbr]字段是员工号，这个是不会改变的，另外[EmployeeID]也是不会改变的，这两个建组合成一个主键可以唯一标识一行数据。[PayrollDeductItemID]字段在这里不选择作为主键，因为在输入数据源中没有这样的一个字段。当运行的时候这个字段不能帮助我们判断这个行应该更新或插入到维度表中。

另外一个重要的设置是输入数据源中有匹配的数据的时候选择什么样的策略来更新维度表。举一个例子，在输入源数据中[EmployeePlanIDNbr] = "0000000001" [EmployeeID]= "1"， [PayDeductType] ="401"这一行数据中对应的DeductionAmount是225.00，而在维度表中对应的值是200.00，我们应该怎么设置呢？下面的表格给出了选择项和对应的结果。

图12

按照表中提示的信息，我们双击打开SCD，点击下一步转到选择主键选择设置。首选必须新建一个OLE DB数据源，并从数据源中选择维度表PayrollDeductItem。所有作为主键的字段和作为更新的字段都砸这里选择设置。默认的设置是“Not a key column”。在这里[EmployeePlanIDNbr]和[EmployeeID]选为business key。注意这里至少要选择一个列作为主键，否则不能点击下一步。最后的设置效果如图7。

下一个步骤将设置那些没有被选择作为主键的字段，这些字段将作为更新和覆盖的候选字段。每个字段需要设置更新属性或者历史属性。如果选择更新属性，维度表中的值将被直接更新。如果选择历史属性，列将会评估更新。当发现有任何变动，根据设置一个新的行将被写入。这里我们如果把[DeductionAmount]设置为历史属性，[PayDeductType]字段都设置为更新属性。

既然选择了一个字段作为历史属性，在后面的设置中将会出现历史属性向导设置。如果DeductionAmount发生了变动，我们可以选择两种方式来保存历史数据。现在PayRollDeductionItem表中的那些额外的字段就开始起作用了。这些字段不是必须的，但是这里我们为了做一些练习把他们加在维度表中。在设置历史属性时会用到HistStartDate和HistEndDate这两个字段，在设置单独列保存的时候，HistBitStatus 和HistTextStatus字段会被用到，它将把已经过时的字段标识为false或者expired，新的数据行将会被标识为true和current。这里要注意，如果我们选择Column to indicate current record属性值为HistBitStatus的时候因为它是bit类型的，那么Value when current和Expiration value将会被默认的设置为True和False如图13。如果我们选择Column to indicate current record属性值为HistTextStatus的时候，那么Value when current和Expiration value应该相应的设置为True和False如图14

图13

图14

下一个步骤是推断成员选项设置。在这例子中，在维度表中没有一个新的工资扣除项，所以不需要设置推断选项。如果在输入源数据中有一个新的扣除选项需要添加的话设置这个选项可以为维度表添加一个占位符。如图10，如果设置了推断成员，必须选择将历史属性或者变更属性字段设置为null或者使用一个bool列来表示数据是推断成员。

点击下一步如图11，在这个步骤中预览SCD有哪些输出项，可以在使用这些输出项定制自己的task，但是一旦这样整个SCD将会被重新配置，把以前的设置打乱，页不能使用这个向导。

完成整个设置向导之后如图2，整个SCD共有三个输出，最左边的是Changing Attribute Updates Output，这个输出使用OLE DB Command来更新维度表中的数据，如图15。我们可以看到有一个SQL语句来更新[PayDeductType]字段。中间的输出时New Output，这里将处理那些新出现的行，最终它将和右边的历史属性输出合并起来，并输入到维度表中。最右边的输出的作用是当识别出有更新数据的时候要更新[HistEndDate]这个列，如图16。


图15


图16

　　

最后我们来看看运行后的效果。

图17


图18

在图18中我们可以发现第1,3,4,5行现在是老旧的无效的数据，他们的[HistEndDate]字段都被设置成当前时间，对应的新的有效地的数据分别是第8,11,10,9行。第2，6行虽然有匹配的主键，但是输入数据源中和维度表中的DeductionAmount值是一样的，所以没有更新，但是他们的[HistStartDate]被重新设置成当前时间。

在实际的生产环境中如果要使用SCD，建议认真检查输入数据源，看里面是否有脏数据。使用SCD来讲OLTP中的数据更新到数据仓库中的时候会很省力。如果想这个例子这样，可以检查最后输出中的OLE DB命令，但是总的来说SCD已经为我们做了大部分的工作。

posted @ 2011-07-17 17:42  nd  阅读(5960)  评论(4)  编辑  收藏  举报