пятница, 15 июня 2012 г.

SCD2: реализация в SSIS.

 

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

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.

blog1

Первым шагом очищаем предварительную таблицу, после делаем заливку данных из источника в 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';
   
   

А  теперь тестовый прогон:

1  Первый прогон –заполняются данные расставляются даты и указывается флаг текущая действующая запись isCurrent.

blog2 

 

2 Теперь в исходниках изменим имя у записи Stefanie (это первая запись на рисунке ClientIDSource=1) и изменим код области у Dennis(это  запись на рисунке ClientIDSource=2). Запускаем пакет и получаем следующие результаты.

blog3

Красным выделено на рисунке строки, которые не являются действующими, в DateTO указана дата до которой эта запись действует, а в флаге isCurrent=0, то есть эта записья уже не является текущей.

И зеленным выделены записи, которые  являются действующими на текущей момент. У них указана дата действия с(DateFrom) и флаг iscurrent равен 1. То есть эти записи с новыми атрибутами начинают действовать с даты, указанной в DateFrom.

Плюсы данного метода:

1 Скорость

Минусы

1  Тяжеловатый и не всегда понятный код реализации логики SCD2 с помощью T-SQL

Второй пакет использует компонент из toolbox Slowly Change Dimension.

blog4

Всю структуру, которая указана на картинке выше, кроме OLE db source, создаст  мастер Slowly Change Dimension. В мастере необходимо указать ключ - bussines key (ключ источника).

image

И атрибуты, которые будут изменятся

image

После указываем, что делать с fail записями

image

и наконец, указываем столбцы, которые отвечают за даты действия.

image

Также  указываем,что делать с столбцом iscurrent

image

В итоге  после нажатия на finish строится набор task-ов.

Плюсы данного метода

1 Понятный мастер, строит логику работы SCD2 сам.

Минус

1 Скорость. При больших объемах данных начинает жутко тормозить.

В своих проектах, я  использую первый метод. Потому что у меня редко когда бывает маленький объем данных в справочниках.  И если кто заинтересуется,  что я изменил после  работы с Slowly Change Dimension(второй пакет) ?Улыбка

Проект SSIS и скрипт по созданию источника  данных:

Проект SSIS

Скрипт на создание базы данных с данными

Комментариев нет: