Slowly Changing dimension inside a ForEach Loop container

Question

Slowly Changing dimension inside a ForEach Loop container

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

MMartin1 One Orange Chip Points: 27795 More actions · Answer 1

Hi Romina,

Are you still using the looping? I don't think that will help. Just use a SCD transform and select it as a 'Historical Attribute' for the change type property for those columns you want history on and fixed or changing (as appropriate) for the rest. You may have done this already but I just want to be sure. Does this work any faster on the data load? Each change type will map to a different output from the SCD transform.

I've not really used this, only read about it so my working knowledge is limited. But I understand you need a derived column with the end date for the historically tagged records that changed (after scd transform), and then do a union all after with the new records. (Are you using something like validFrom , validTo in your destination?). I just dont think the looping containers are needed and they may be whats slowing you down.

----------------------------------------------------

romina Old Hand Points: 332 More actions · Answer 2

MMartin1 (8/20/2014)
Hi Romina,
Are you still using the looping? I don't think that will help. Just use a SCD transform and select it as a 'Historical Attribute' for the change type property for those columns you want history on and fixed or changing (as appropriate) for the rest. You may have done this already but I just want to be sure. Does this work any faster on the data load? Each change type will map to a different output from the SCD transform.
I've not really used this, only read about it so my working knowledge is limited. But I understand you need a derived column with the end date for the historically tagged records that changed (after scd transform), and then do a union all after with the new records. (Are you using something like validFrom , validTo in your destination?). I just dont think the looping containers are needed and they may be whats slowing you down.

MMartin,

I stopped using the loop. The reason being was it was overloading the server's memory after about 1 hour of processing.

I am now just using the SCD but it's been now nearly 17 hours that it's been running. Of course, I have had no problem with memory or CPU usage but 17 hours to process 4 millions it's very inefficient.

Any thoughts?

MMartin1 One Orange Chip Points: 27795 More actions · Answer 3

There is a small piece in one of my books about this. I'll do some reading and get back to you tomorrow likely. The SCD transform is process intensive because it has to hit the table for each row and cannot cache that table. So it is for sure not fast, but I think there is an alternate design to avoid the SCD transform, for these situations.

In the meantime I would also mention indexing the PK and associated businessKey in both tables if not already done (clustered is best). But I'm sure you have that covered.

----------------------------------------------------

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

romina (8/20/2014)
MMartin1 (8/20/2014)
Hi Romina,
Are you still using the looping? I don't think that will help. Just use a SCD transform and select it as a 'Historical Attribute' for the change type property for those columns you want history on and fixed or changing (as appropriate) for the rest. You may have done this already but I just want to be sure. Does this work any faster on the data load? Each change type will map to a different output from the SCD transform.
I've not really used this, only read about it so my working knowledge is limited. But I understand you need a derived column with the end date for the historically tagged records that changed (after scd transform), and then do a union all after with the new records. (Are you using something like validFrom , validTo in your destination?). I just dont think the looping containers are needed and they may be whats slowing you down.
MMartin,
I stopped using the loop. The reason being was it was overloading the server's memory after about 1 hour of processing.
I am now just using the SCD but it's been now nearly 17 hours that it's been running. Of course, I have had no problem with memory or CPU usage but 17 hours to process 4 millions it's very inefficient.
Any thoughts?

Koen has already provided a link to an article which he wrote which demonstrates how to do this without using the SCD component. I think that you should invest your time in rewriting your package accordingly.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

At this point, it might be useful to show us some screenshots and maybe some table DDL so we can provide more specific assistance.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

romina Old Hand Points: 332 More actions · Answer 6

Phil Parkin (8/20/2014)
Koen has already provided a link to an article which he wrote which demonstrates how to do this without using the SCD component. I think that you should invest your time in rewriting your package accordingly.

Phil, I tried that. It didn't work. Perhaps you are right and I didn't spend enough time on it but I will today!

Koen Verbeeck (8/21/2014)
At this point, it might be useful to show us some screenshots and maybe some table DDL so we can provide more specific assistance.

I will provide some screenshots and table DDL Koen as soon as I get to work.

Thank you guys so much!

Romina

MMartin1 One Orange Chip Points: 27795 More actions · Answer 7

For example, for a particular employee there might be 141 records in this "huge dumping table" but when I perform the SCD, only 14 records make it to the DW after getting rid of duplicates and updating historical records.

I am interested in seeing some DDL and sample data. Does this 'dumping' table also contain all historical records? Are you loading a dimension table ultimately ? One idea refers back to what Phil and Koen mention when they state "set based" operations. Here I illustrate that in a simple image. This is what the SCD wizard generates. According to what I've read most of your records will be new. The image shows that those that are not can be loaded into separate staging tables. From there you perform execute sql tasks to take care of each of these tables. For example, the Historical output will mean that you , I assume . have something like a validFrom , ValidTo date fields in the destination dimension? If an attribute columns value has changed then change the validTo of the destination table record to the current datetime and insert the new record. All this is done in t-SQL. THis will be a lot faster because the whole tables can be loaded into the memory buffer, rather than only one row at a time with the SCD transform. With sample data and DDLs I can illustrate better.

If this is still a perfromance drag then you will have to bypass the whole SCD entirely. But for now these are not to bad to try.

----------------------------------------------------

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 8

To add to the recommendation to remove it, the SCD component in SSIS (at least in 05 and 08) is a piece of crap. The Inserts are fine. The Updates are Row by Row, as are Deletes, simply because the component has to be generic enough to handle all styles handled to it with the same code.

There are a number of tools in SSIS that are available so they exist, but they are not the best tool to use when you're working against a Database Engine. Sort, Aggregations, SCD, etc. are performance killers in SSIS. This is because they interrupt the flow/stream of data. They are typically there more for when you're using SSIS against non-native systems or simple datastores (DB2?), for example you need to sort a flat file to merge it to another flat file to output a third flatfile. Nowhere in there would you typically touch an engine.

Not all tools are created equal for all jobs.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

romina Old Hand Points: 332 More actions · Answer 9

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

*/