SCD2 на TSQL

Кратко, немного теории:

SCD( slowly change dimension ) называются таблицы измерений, в которых некоторые атрибуты могут изменить свои значения по истечении некоторого периода времени, причем частота таких изменений является небольшой.

Существует три типа таких измерений:

Type 1 - относятся те измерения, в которых не поддерживается история изменений атрибута. То есть запись в таблице просто обновляется.

Type 2 - относятся те измерения, в которых поддерживается история изменений атрибута. То есть старая запись помечается как утратившая актуальность, и добавляется новая запись с тем же идентификатором, но уже с обновленными полями. Так же указывается время действия данной записи

Type 3 - относятся те измерения, в которых поддерживается история изменений атрибута, но в отличие от второго типа, в таблице добавляются новые поля, которые хранят старые значения.

Более подробней по поводу SCD-измерений можно почитать у Ralph Kimball. Информации очень много в сети по данной тематике, так что ищите.

Теперь вернемся к реализации SCD type2 с помощью ssis .

Создаем таблицу dim_Clients в MS SQL server 2012

CREATE TABLE [dbo].[dim_Clients](
    [id] [int] IDENTITY(1,1) NOT NULL, --id клиента – уникальная запись, инкрементальная
    [ClientIDSource] [int] NOT NULL,  -- id клиента в источнике
    [NameClients] [varchar](250) NOT NULL, –- имя клиента
    [TypeClients] [int] NULL, –-тип клиента
    [KodObl] [varchar](3) NULL, –-код области
    [DateRegistration] [int] NULL, –- дата регистрации
    [DateFrom] [datetime] NOT NULL, –- дата начала действия даты с
    [DateTo] [datetime] NOT NULL, –- дата окончания действия даты до
    [IsCurrent] [bit] NOT NULL, –- является текущей датой(0 – нет, 1 – да)
 CONSTRAINT [PK_dim_Clients] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO



ALTER TABLE [dbo].[dim_Clients] ADD  CONSTRAINT [DF_dim_Clients_DateFrom]  DEFAULT ('30000101') FOR [DateFrom]
GO

Теперь нам необходима дополнительная  таблица, куда мы будем сливать данные из  исходного источника, а потом обрабатывать и сохранять в таблицу в хранилище.

CREATE TABLE [dbo].[dim_ClientsStaging](
    [id] [int] NOT NULL,
    [NameClients] [varchar](250) NOT NULL,
    [TypeClients] [int] NULL,
    [KodObl] [varchar](3) NULL,
    [DateRegistration] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[dim_ClientsStaging] ADD  CONSTRAINT [DF_dim_ClientsStaging_DateRegistration]  DEFAULT (datediff(day,'',getdate())) FOR [DateRegistration]
GO

Создаем новый проект в SSIS 2012. Добавляем источник откуда мы получаем данные, и источник, куда мы будем заливать данные. После создаем два пакета.

В первом пакете для работы SCD2 используем конструкцию T-SQL merge.

Первым шагом очищаем предварительную таблицу, после делаем заливку данных из источника в staging таблицу, а потом с помощью скрипта T-SQL реализуем логику заполнения для SCD2. Скрипт ниже:

  insert into dbo.dim_Clients
   (  
      [ClientIDSource]
     ,[NameClients]
     ,[TypeClients]
     ,[KodObl]
     ,[DateRegistration]
     ,[DateFrom]
     ,[DateTo]
     ,[IsCurrent]
   )
   select 
      [ClientIDSource]
     ,[NameClients]
     ,[TypeClients]
     ,[KodObl]
     ,[DateRegistration]    
      ,getdate()
      ,'30000101'
      ,1     
      from      
      (
      merge into  dim_Clients  as dst
      using  dbo.dim_ClientsStaging as src
      on
      (
       src.id=dst.[ClientIDSource]
       )
      --new records inserted
      when not matched then 
      insert (
      [ClientIDSource]
     ,[NameClients]
     ,[TypeClients]
     ,[KodObl]
     ,[DateRegistration]    
     ,[DateFrom]
     ,[DateTo]
     ,[IsCurrent]
      )
      values(
      src.id
      ,src.[NameClients]
      ,src.[TypeClients]
      ,src.[KodObl]
      ,src.[DateRegistration]
      , getdate()
      , '30000101'
      ,1
      )
      --existing
     when matched
     and  isCurrent=1
     and
     (src.[NameClients]<>dst.[NameClients]
     or src.[TypeClients]<>dst.[TypeClients]
     or src.[KodObl]<>dst.[KodObl]    
      )
     then update 
     set
     dst.IsCurrent=0,
     dst.DateTo=getdate()
     output 
      src.id as clientidsource
      ,src.[NameClients]
      ,src.[TypeClients]
      ,src.[KodObl]
      ,src.[DateRegistration]
     ,$Action as MergeAction) as  mrg 
      where mrg.MergeAction='update';