65.9K
CodeProject 正在变化。 阅读更多。
Home

使用 SSIS 中的 SQL 执行任务加载维度（SCD 类型 1 和类型 2）

Gyanendra Jaiswal
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.95/5 (5投票s)

2013年6月10日

CPOL

3分钟阅读

viewsIcon

32356

downloadIcon

196

使用SQL任务加载维度比使用SCD转换性能更好。

引言

在BI环境中使用执行SQL任务加载维度表比在SSIS转换中使用SCD转换性能更好。

  • SCD代表缓慢变化维度
  • 此代码已在SQL Server 2008上测试过

为什么需要它？

在BI环境中，有几种方法可以加载维度表，例如，使用缓慢变化维度转换或使用合并联接转换等，但是这两种方法都非常适合少量数据，因为这些转换是异步的，它们对数据源的每一行数据执行操作，因此假设我们在数据源中有数百万条记录，那么加载维度表将花费很长时间，我认为您的业务不允许这样做。 因此，我们可以在SSIS中使用一个简单的执行SQL任务来减少处理时间，即使我们在数据源中有数百万条记录。

缓慢变化维度有三种类型：SCD类型1（更改属性），这意味着对于定义为SCD类型1的列不维护任何历史记录；例如，员工的电话号码。 第二种是SCD类型2（历史属性），这意味着通过使用开始日期/结束日期或通过使用版本号来维护历史记录； 例如，当任何员工的工作地点发生更改时，我们将通过使用开始日期/结束日期来维护旧员工位置的历史记录。 第三种SCD类型是我们在本文中考虑的内容。

在使用此方法（即SSIS中的执行SQL任务转换）加载维度表时，我们还可以维护SCD类型1和SCD类型2。

了解将在执行SQL任务下运行的脚本

对于SCD类型2:

对于SCD类型2，我考虑了以下列

城镇

客户名称，出生日期，电话号码，驾驶执照号码，职业

----For SCD type 2 Historical Attribute---
Declare @tempHistoricalAttribute As Table(CustomerId Int)
 
Insert Into @tempHistoricalAttribute(CustomerId)
select src.CustomerId from 
(
select CustomerId,town from Customer
EXCEPT
select CustomerId,town from CustomerUpdate Where EndDate Is NULL
) src 
Inner Join CustomerUpdate dst On src.CustomerId = dst.CustomerId
Where dst.EndDate Is NULL
 
Update CustomerUpdate Set EndDate = GETDATE()  from 
 @tempHistoricalAttribute  src
Inner Join CustomerUpdate dst On src.CustomerId = dst.CustomerId
Where dst.EndDate Is NULL
 
Insert Into CustomerUpdate(CustomerId,CustomerName,DateOfBirth,Town,TelephoneNo,
DrivingLicenceNo,Occupation,StartDate,EndDate)
Select CustomerId,CustomerName,DateOfBirth,Town,TelephoneNo,
DrivingLicenceNo,Occupation,GETDATE(),null From Customer where CustomerId in 
(Select customerid from  @tempHistoricalAttribute)

对于SCD类型1:

对于SCD类型1，我考虑了以下列

客户名称，出生日期，电话号码，驾驶执照号码，职业 

-- To Maintain SCD type 1 Changing Attribute----
Update CustomerUpdate SET  
CustomerName=src.CustomerName,DateOfBirth=src.DateOfBirth,
TelephoneNo = src.TelephoneNo,DrivingLicenceNo = src.DrivingLicenceNo,
Occupation = src.Occupation
from
(
select CustomerId,CustomerName,DateOfBirth,TelephoneNo,DrivingLicenceNo,
Occupation from Customer
EXCEPT
select CustomerId,CustomerName,DateOfBirth,TelephoneNo,DrivingLicenceNo,
Occupation from CustomerUpdate where EndDate Is Null
)src 
INNER JOIN CustomerUpdate dest ON src.CustomerId = dest.CustomerId
where dest.EndDate Is Null

对于新记录:

--To Insert new Record------
Insert Into CustomerUpdate(CustomerId,CustomerName,DateOfBirth,Town,TelephoneNo,
DrivingLicenceNo,Occupation,StartDate,EndDate)
select src.CustomerId,src.CustomerName,src.DateOfBirth,src.Town,
src.TelephoneNo,src.DrivingLicenceNo,src.Occupation,GETDATE(),NULL 
from
(
select CustomerId,CustomerName,DateOfBirth,Town,TelephoneNo,DrivingLicenceNo,
Occupation from Customer
EXCEPT
select CustomerId,CustomerName,DateOfBirth,Town,TelephoneNo,DrivingLicenceNo,
Occupation from CustomerUpdate where EndDate Is Null
)src

需要注意的重要事项

脚本应按以下顺序运行。 执行SQL任务将按以下顺序运行脚本

  • 顺序 1：SCD类型2脚本
  • 顺序 2：SCD类型1脚本
  • 顺序 3：新记录脚本

创建项目

表脚本

if exists (select * from sys.tables where name = 'Customer')
drop table Customer
go
create table Customer
( 
CustomerId int not null primary key,
CustomerName varchar(30), DateOfBirth date, Town varchar(50),
TelephoneNo varchar(30), DrivingLicenceNo varchar(30), Occupation varchar(30)
)
go
 
-- Populate Customer Source
 
declare @i int, @si varchar(10), @startdate date
set @i = 1
while @i <= 10
begin
 set @si = right('0'+CONVERT(varchar(10), @i),2)
 insert into Customer
 ( CustomerId, CustomerName, DateOfBirth, Town, TelephoneNo, DrivingLicenceNo, Occupation)
 values
 ( @i, 'Customer'+@si, DATEADD(d,@i-1,'2000-01-01'), 'Town'+@si, 
       'Phone'+@si, 'Licence'+@si, 'Occupation'+@si)
 set @i = @i + 1
end
go
 
-- Create customer table
 
if exists (select * from sys.tables where name = 'CustomerUpdate')
drop table CustomerUpdate
go
 
create table CustomerUpdate
(
Dim_CustomerId int identity(1,1), 
CustomerId int not null,
CustomerName varchar(30), DateOfBirth date, Town varchar(50),
TelephoneNo varchar(30), DrivingLicenceNo varchar(30), Occupation varchar(30),
StartDate DateTime,EndDate Datetime
)
go
 
-- Populate Customer Destination
 
declare @i int, @si varchar(10), @startdate date
set @i = 1
while @i <= 5
begin
 set @si = right('0'+CONVERT(varchar(10), @i),2)
 insert into CustomerUpdate
 ( CustomerId, CustomerName, DateOfBirth, Town, TelephoneNo, 
        DrivingLicenceNo, Occupation,StartDate,EndDate)
 values
 ( @i, 'Customer'+@si, DATEADD(d,@i-1,'2000-01-01'), 'Town'+@si, 
         'Phone'+@si, 'Licence'+@si, 'Occupation'+@si,GETDATE(),NULL)
 set @i = @i + 1
end
go

步骤1. 创建SSIS包

打开SQL Server商业智能开发工作室并创建新项目作为SQL Server Integration Services，添加新包后。

步骤 2. 从工具箱中添加执行SQL任务，重命名为加载维度，然后编辑SQL任务。 然后在连接属性中设置连接凭据和数据库。

步骤 3：单击SQL语句属性中的按钮，然后编写SQL脚本

测试包

测试步骤 1:

运行包并查看结果

已插入5条新记录，客户ID为6到10。

测试步骤 2:  

使用脚本更新记录

--for SCD type 1-- 
UPDATE [Customer]
   SET [CustomerName] = 'aaa1'
      ,[DateOfBirth] = '2012-01-01'
 WHERE [CustomerId] =1
GO
 
--for SCD type 2--
UPDATE [Customer]
 SET [Town] = 'ttt1'
 WHERE [CustomerId] =2

现在再次运行包，您将看到结果

客户ID = 1的CustomerName和dateOfBirth记录将被更新，但不会插入任何新记录因为SCD类型1。

此外，客户ID = 2的结束日期将被更新，并且将为客户ID = 2插入一条新记录，但将生成新的代理键，即Dim_CustomerId = 11，结束日期为null因为SCD类型2。

关注点

这种方法比用于BI环境中维度加载的 SCD转换 和合并联接表现更好。

© . All rights reserved.