Кратко, немного теории:
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';
А теперь тестовый прогон:
1 Первый прогон –заполняются данные расставляются даты и указывается флаг текущая действующая запись isCurrent.
2 Теперь в исходниках изменим имя у записи Stefanie (это первая запись на рисунке ClientIDSource=1) и изменим код области у Dennis(это запись на рисунке ClientIDSource=2). Запускаем пакет и получаем следующие результаты.
Красным выделено на рисунке строки, которые не являются действующими, в DateTO указана дата до которой эта запись действует, а в флаге isCurrent=0, то есть эта записья уже не является текущей.
И зеленным выделены записи, которые являются действующими на текущей момент. У них указана дата действия с(DateFrom) и флаг iscurrent равен 1. То есть эти записи с новыми атрибутами начинают действовать с даты, указанной в DateFrom.
Плюсы данного метода:
1 Скорость
Минусы
1 Тяжеловатый и не всегда понятный код реализации логики SCD2 с помощью T-SQL
Второй пакет использует компонент из toolbox Slowly Change Dimension.
Всю структуру, которая указана на картинке выше, кроме OLE db source, создаст мастер Slowly Change Dimension. В мастере необходимо указать ключ - bussines key (ключ источника).
И атрибуты, которые будут изменятся
После указываем, что делать с fail записями
и наконец, указываем столбцы, которые отвечают за даты действия.
Также указываем,что делать с столбцом iscurrent
В итоге после нажатия на finish строится набор task-ов.
Плюсы данного метода
1 Понятный мастер, строит логику работы SCD2 сам.
Минус
1 Скорость. При больших объемах данных начинает жутко тормозить.
В своих проектах, я использую первый метод. Потому что у меня редко когда бывает маленький объем данных в справочниках. И если кто заинтересуется, что я изменил после работы с Slowly Change Dimension(второй пакет) ?
Проект SSIS и скрипт по созданию источника данных:
Проект SSIS
Скрипт на создание базы данных с данными
