RE: Slowly Changing dimension inside a ForEach Loop container

Old Hand

Points: 332

August 22, 2014 at 1:45 pm

Hi folks,

I sincerely want to thank you first for taking the time to try to help me! Your help is much appreciated!

Ok, since my last post I have gone and spent some more time trying to work with the custom SCD as per Phil's advice. I am almost getting it to work but I have some more tweaking to do.

MMartin you also had some very good points. First I will show you my source and destination table DDLs.

USE [HR_DataMart]

GO

/****** Object: Table [dbo].[EmployeeMaster] Script Date: 8/22/2014 10:25:41 AM ******/

/* Description:

The data on this table comes from an SP run every week that extracts data from

a mainframe HR system into the table below in SQL. This table is used as 'dump'

table. No indexes, no primary keys and no data checking. At the moment the

record count on this table is 4,397,683.

*/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[EmployeeMaster](

[ExtractDate] [datetime] NOT NULL,

[GroupID] [int] NULL,

[DivisionCode] [varchar](4) NULL,

[Division] [varchar](100) NULL,

[RegionCode] [varchar](50) NULL,

[Region] [varchar](100) NULL,

[MarketCode] [varchar](50) NULL,

[Market] [varchar](100) NULL,

[ParentHSPCode] [nchar](10) NULL,

[HRParentId] [int] NULL,

[HRParentName] [varchar](255) NULL,

[ORG] [varchar](8) NOT NULL,

[FacId] [int] NOT NULL,

[Facility] [varchar](48) NULL,

[FacilityAlphaValue] [varchar](3) NOT NULL,

[FacilityName] [varchar](255) NULL,

[WorkFacId] [int] NULL,

[WorkFacilityAlphaValue] [varchar](3) NULL,

[EmpMail Street Address] [varchar](24) NULL,

[EmpMail City] [varchar](16) NULL,

[EmpMail State] [char](2) NOT NULL,

[EmpMail Zip] [varchar](10) NOT NULL,

[FullName] [varchar](24) NULL,

[MaidenName] [char](20) NULL,

[LastName] [varchar](24) NULL,

[FirstName] [varchar](15) NULL,

[MiddleName] [varchar](15) NULL,

[PreferredName] [char](20) NULL,

[SSN] [char](11) NOT NULL,

[EeNum] [int] NULL,

[RequisitionNumber] [char](10) NULL,

[ApplicantID] [char](13) NULL,

[PositionControlNumber] [varchar](20) NULL,

[Status AIT] [char](1) NOT NULL,

[StatusF12Code] [char](1) NOT NULL,

[PerDiemInd] [char](1) NOT NULL,

[EthnicCode] [char](1) NOT NULL,

[BirthDate] [varchar](30) NULL,

[Original Hire Date] [varchar](30) NULL,

[CurrHireDate] [varchar](30) NULL,

[Transfer Date] [varchar](30) NULL,

[Transfer From Facility] [varchar](3) NULL,

[Reinstatement Date] [varchar](30) NULL,

[Acquisition Date] [varchar](30) NULL,

[JobCodeEffDate] [varchar](30) NULL,

[DeptEffDate] [varchar](30) NULL,

[TerminationDate] [varchar](30) NULL,

[TermCode] [char](2) NULL,

[TermReason] [varchar](100) NULL,

[TermStatus] [char](1) NULL,

[TermCategoryDesc] [varchar](30) NULL,

[RIF Date] [varchar](30) NULL,

[Term Security Date] [varchar](30) NULL,

[Gender] [char](1) NOT NULL,

[MaritalStatusCode] [char](1) NOT NULL,

[Job Code] [varchar](8) NULL,

[Jcode] [varchar](4) NULL,

[UnionJobCode] [varchar](1) NULL,

[EEOCode] [char](1) NULL,

[Title] [varchar](24) NULL,

[Job Class] [char](2) NULL,

[Job Class Desc] [varchar](100) NOT NULL,

[ManagerCode] [char](1) NOT NULL,

[BaseRate] [numeric](9, 4) NOT NULL,

[ScheduledHours] [varchar](30) NULL,

[Dept] [char](6) NOT NULL,

[HomeShift] [char](1) NULL,

[EELevelUnionInd] [char](1) NULL,

[UnionCode] [varchar](6) NULL,

[Local Code] [varchar](8) NULL,

[YearsOfExperience] [int] NULL,

[Tenure] [decimal](9, 2) NULL,

[AcquisitionTenure] [decimal](9, 2) NULL,

[Work Location Code] [char](3) NULL,

[Work Location Address] [varchar](50) NULL,

[Work Location City] [varchar](50) NULL,

[Work Location State] [varchar](2) NULL,

[Work Location Zip] [varchar](10) NULL,

[Work Location County] [varchar](20) NULL,

[SupervisorID] [int] NULL,

[SupervisorName] [varchar](50) NULL,

[DeptMgrID] [int] NULL,

[DeptMgrName] [varchar](50) NULL,

[ExecSalaryMgrID] [int] NULL,

[ExecSalaryMgrName] [varchar](50) NULL,

[WorkEmail] [varchar](50) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

I have made a local copy of this table into another database and added keys and indexes as required.

Now the destination table is very different and is more a fact table than a dimension table. The DDL for the fact or destination table is:

USE [HRDataMart_Dev]

GO

/****** Object: Table [dbo].[EmployeeFactMergeInsert$] Script Date: 8/22/2014 10:36:48 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[EmployeeFactMergeInsert$](

[EmployeeRecordID] [int] IDENTITY(1,1) NOT NULL,

[EmployeeContactID] [int] NOT NULL,

[FacilityKeyID] [int] NOT NULL,

[WorkLocationID] [int] NOT NULL,

[EmploymentStatusID] [int] NOT NULL,

[DepartmentKeyID] [int] NOT NULL,

[ManagerCode] [char](1) NOT NULL,

[JobRecordID] [int] NOT NULL,

[EmployeeNumber] [int] NULL,

[SupervisorNumber] [int] NULL,

[DeptManagerNumber] [int] NULL,

[ExecSalaryNumber] [int] NULL,

[OriginalHireDate] [datetime] NULL,

[CurrentHireDate] [datetime] NULL,

[DeptEffectiveDate] [datetime] NULL,

[JobCodeEffectiveDate] [datetime] NULL,

[RIFDate] [datetime] NULL,

[TerminationDate] [datetime] NULL,

[TerminationSecurityDate] [datetime] NULL,

[TerminationCode] [char](2) NULL,

[TransferDate] [datetime] NULL,

[TransferFacilityKeyID] [int] NULL,

[ReinstatementDate] [datetime] NULL,

[AcquisitionDate] [datetime] NULL,

[RequisitionNumber] [varchar](10) NULL,

[PositionControlNumber] [varchar](20) NULL,

[ApplicantID] [varchar](13) NULL,

[BaseRate] [numeric](9, 4) NULL,

[ScheduledHours] [varchar](30) NULL,

[EffectiveDate] [datetime] NULL,

[EndDate] [datetime] NULL,

[IsActive] [bit] NULL,

[ExtractDate] [datetime] NULL,

[MaintDate] [datetime] NULL,

CONSTRAINT [PK_EmployeeFactMergeInsert$] PRIMARY KEY CLUSTERED

(

[EmployeeRecordID] 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].[EmployeeFactMergeInsert$] ADD CONSTRAINT [DF_EmployeeFactMergeInsert$_MaintDate] DEFAULT (getdate()) FOR [MaintDate]

GO

ALTER TABLE [dbo].[EmployeeFactMergeInsert$] WITH NOCHECK ADD CONSTRAINT [FK_EmployeeFactMergeInsert$_ContactDetails$] FOREIGN KEY([EmployeeContactID])

REFERENCES [dbo].[ContactDetails$] ([EmployeeContactID])

GO

ALTER TABLE [dbo].[EmployeeFactMergeInsert$] CHECK CONSTRAINT [FK_EmployeeFactMergeInsert$_ContactDetails$]

GO

ALTER TABLE [dbo].[EmployeeFactMergeInsert$] WITH NOCHECK ADD CONSTRAINT [FK_EmployeeFactMergeInsert$_Department$] FOREIGN KEY([DepartmentKeyID])

REFERENCES [dbo].[Department$] ([DepartmentKeyID])

GO

ALTER TABLE [dbo].[EmployeeFactMergeInsert$] CHECK CONSTRAINT [FK_EmployeeFactMergeInsert$_Department$]

GO

ALTER TABLE [dbo].[EmployeeFactMergeInsert$] WITH NOCHECK ADD CONSTRAINT [FK_EmployeeFactMergeInsert$_EmploymentStatus$] FOREIGN KEY([EmploymentStatusID])

REFERENCES [dbo].[EmploymentStatus$] ([EmploymentStatusID])

GO

ALTER TABLE [dbo].[EmployeeFactMergeInsert$] CHECK CONSTRAINT [FK_EmployeeFactMergeInsert$_EmploymentStatus$]

GO

ALTER TABLE [dbo].[EmployeeFactMergeInsert$] WITH NOCHECK ADD CONSTRAINT [FK_EmployeeFactMergeInsert$_Facility$] FOREIGN KEY([FacilityKeyID])

REFERENCES [dbo].[Facility$] ([FacilityKeyID])

GO

ALTER TABLE [dbo].[EmployeeFactMergeInsert$] CHECK CONSTRAINT [FK_EmployeeFactMergeInsert$_Facility$]

GO

ALTER TABLE [dbo].[EmployeeFactMergeInsert$] WITH NOCHECK ADD CONSTRAINT [FK_EmployeeFactMergeInsert$_Job_H$] FOREIGN KEY([JobRecordID])

REFERENCES [dbo].[Job_H$] ([JobRecordID])

GO

ALTER TABLE [dbo].[EmployeeFactMergeInsert$] CHECK CONSTRAINT [FK_EmployeeFactMergeInsert$_Job_H$]

GO

ALTER TABLE [dbo].[EmployeeFactMergeInsert$] WITH NOCHECK ADD CONSTRAINT [FK_EmployeeFactMergeInsert$_ManagerStatus$] FOREIGN KEY([ManagerCode])

REFERENCES [dbo].[ManagerStatus$] ([ManagerStatusCode])

GO

ALTER TABLE [dbo].[EmployeeFactMergeInsert$] CHECK CONSTRAINT [FK_EmployeeFactMergeInsert$_ManagerStatus$]

GO

ALTER TABLE [dbo].[EmployeeFactMergeInsert$] WITH NOCHECK ADD CONSTRAINT [FK_EmployeeFactMergeInsert$_TerminationCode$] FOREIGN KEY([TerminationCode])

REFERENCES [dbo].[TerminationCode$] ([TerminationCode])

GO

ALTER TABLE [dbo].[EmployeeFactMergeInsert$] CHECK CONSTRAINT [FK_EmployeeFactMergeInsert$_TerminationCode$]

GO

ALTER TABLE [dbo].[EmployeeFactMergeInsert$] WITH NOCHECK ADD CONSTRAINT [FK_EmployeeFactMergeInsert$_WorkLocation$] FOREIGN KEY([WorkLocationID])

REFERENCES [dbo].[WorkLocation$] ([WorkLocationID])

GO

ALTER TABLE [dbo].[EmployeeFactMergeInsert$] CHECK CONSTRAINT [FK_EmployeeFactMergeInsert$_WorkLocation$]

GO

MMartin, you are right. Most of my records are new. Especially those extracted the first time around. There are 59 weeks worth of data, for around 96,000 employee. Not all employee's data is available for all weeks. Similarly, an employee will have more than one record per extract date, especially if the employee has moved from facility to facility.

I am providing you below with some sample data including the CREATE TABLE statement so that you can play around with that.

Please bear in mind I am running SQL Server 2005 Standard Edition on a Windows Server 2003 SP2 Enterprise Edition machine (Intel Xeon 5160 @ 3.00GHz, 4GB RAM).

/*CREATE TABLE dbo.EmployeeMasterForum$Forum$

(

ExtractDate DATETIME NOT NULL,

FacID INT NULL,

Facility VARCHAR(50) NULL,

FacilityAlphaValue VARCHAR(3) NULL,

SSN VARCHAR(11) NULL,

EeNum INT NULL,

[Status AIT] CHAR(1) NULL,

StatusF12 CHAR(1) NULL,

PerDiemInd CHAR(1) NULL,

OriginalHireDate DATETIME NULL,

CurrentHireDate DATETIME NULL,

JobCodeEffectiveDate DATETIME NULL,

RIFDate DATETIME NULL,

TerminationDate DATETIME NULL,

TerminationSecurityDate DATETIME NULL,

ReinstatementDate DATETIME NULL,

AcquisitionDate DATETIME NULL,

DeptEffectiveDate DATETIME NULL,

TransferDate DATETIME NULL,

TransferFacilityID INT NULL,

TerminationCode CHAR(2) NULL,

[Job Code] VARCHAR(8) NULL,

UnionJobCode CHAR(1) NULL,

EEOCode CHAR(1) NULL,

Title VARCHAR(25) NULL,

[Job Class] CHAR(2) NULL,

ManagerCode CHAR(1) NULL,

BaseRate NUMERIC(9,4) NULL,

ScheduledHours INT NULL,

Dept INT NULL,

[Work Location Code] VARCHAR(3) NULL,

[Work Location Address] VARCHAR(255) NULL,

[Work Location City] VARCHAR(100) NULL,

[Work Location State] VARCHAR(2) NULL,

[Work Location Zip] INT NULL,

SupervisorNumber INT NULL,

DeptManagerNumber INT NULL,

ExecSalaryNumber INT NULL,

RequisitionNumber VARCHAR(10) NULL,

PositionControlNumber VARCHAR(20) NULL,

ApplicantID VARCHAR(13) NULL

);

GO

*/