Monday, June 20, 2016

SSIS:SCD Type1 Step by Step

Scenario: - Slowly changing dimension type 1 is useful when need to insert new data or update existing data in table columns basically it overwrites the existing data if any updates present in the data

Following are the functionalities of SCD
  • ·         Matching incoming rows with rows in the lookup table to identify new and existing rows.
  • ·         Identifying incoming rows that contain changes when changes are not permitted.
  • ·         Identifying inferred member records that require updating.
  • ·         Identifying incoming rows that contain historical changes that require insertion of new records and the updating of expired records.
  • ·        Detecting incoming rows that contain changes that require the updating of existing records, including expired ones.
Implementation:-
Following table is used to implement SCD Type scenario.

Source table 

CREATE TABLE [dbo].[MyEmployees](
       [EmployeeID] [smallint] NOT NULL,
       [FirstName] [nvarchar](30) NOT NULL,
       [LastName] [nvarchar](40) NOT NULL,
       [Title] [nvarchar](50) NOT NULL,
       [DeptID] [smallint] NOT NULL,
       [ManagerID] [int] NULL,
 CONSTRAINT [PK_EmployeeID] PRIMARY KEY CLUSTERED
(
       [EmployeeID] 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

Insert sample data into table

INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (1, N'Ken', N'Sánchez', N'Chief Executive Officer', 16, NULL)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (16, N'David', N'Bradley', N'Marketing Manager', 4, 273)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (273, N'Brian', N'Welcker', N'Vice President of Sales', 3, 1)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (274, N'Stephen', N'Jiang', N'North American Sales Manager', 3, 273)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (275, N'Michael', N'Blythe', N'Sales Representative', 3, 274)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (276, N'Linda', N'Mitchell', N'Sales Representative', 3, 274)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (285, N'Syed', N'Abbas', N'Pacific Sales Manager', 3, 273)
INSERT [dbo].[MyEmployees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (286, N'Lynn', N'Tsoflias', N'Sales Representative', 3, 285)

Destination table 

CREATE TABLE [dbo].[MyEmployees_Destination](
       [EmployeeID] [smallint] NOT NULL,
       [FirstName] [nvarchar](30) NOT NULL,
       [LastName] [nvarchar](40) NOT NULL,
       [Title] [nvarchar](50) NOT NULL,
       [DeptID] [smallint] NOT NULL,
       [ManagerID] [int] NULL)

Step 1:-Go to control flow drag DFT
Step2:- Double click on DFT and Drag OLEDB source to dataflow flow Configure as per below shown image

Step3:- Drag slowly changing dimension to control flow and configure as per below show image
Select destination table, Key type
Business key is used to identify unique rows in table columns Ex: - we will match EmployeeID from source to destination if the incoming row employee id matched with existing ID It will check for updates in table and updates the existing data otherwise it will insert the new row
Select business key EmployeeID and click next.

Step4:- Select dimensions column’s and Change type

Step 5:- Click next, next to screen, Uncheck inferred dimension members click next and finish. SSIS will insert required tasks automatically

Testing the package: - Execute the package, if you execute for one more time It won’t insert the data


Update data in source table with below query

UPDATE [MyEmployees] set Title='Sales Cordinator' where EmployeeID='286'

Now execute the package it will overwrite the tittle of employeeid 286,


Conclusion:- Update,insert