Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [Solved] Building SCD Type 2 Using Histroic Data
Go
New
Search
Notify
Tools
Reply
  
[Solved] Building SCD Type 2 Using Histroic Data
 Login/Join
 
Member
posted
I'm trying to figure out how best to use DMC built-in SCD Type 2 dimension features using historic data i.e. I want to capture the change that has occured over the last 5 years.

I'm having difficulty using the SCD feature as the begin date defaults to the date and time of the dimension load and unlike the end date, there doesn't appear to be options to define this date using your source data. Has anyone else had this need?

Secondly, it looks like you need to select either select tracking type 2 SCD using begin/end dates or by using an activation flag. I like to use both date ranges and a current flag in my Type 2 SCD. Has anyone been able to incorporate both of these using the built-in SCD functionality.

Thanks,

David

This message has been edited. Last edited by: Kathleen Butler,


Webfocus 8.0
HTML
 
Posts: 20 | Location: Guelph, Ontario | Registered: May 17, 2013Reply With QuoteReport This Post
Guru
posted Hide Post
Answering your questions in reverse order.

Actually you can select BOTH activation flag and begin/end dates. I would recommend doing just that.

You can specify alternate values for SCD dates as global variables. Search online help for "Slowly Changing Dimension Variables" and you'll see that you can change the Run Date (defaults to today) as well as the end date for Type II active (defaults to NULL, many users prefer a day far in the future) and inactive (defaults to today, many users prefer yesterday).

Thus if you have a timestamped source for historic data you can run your dimension load repeatedly for all the dates in the last five years on which there were changes. Use DataMigrator's iterator capability to run for multiple run dates.
 
Posts: 380 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Member
posted Hide Post
Thanks for the quick reply Clif.

I have both the activation flag and begin/end date populating correctly. I also have been able to set the modify the start date by setting the &&CM__SCDATE in a procedure prior to the data flow as you suggested . . . Thanks.

However, if I'm trying to track something like changes to employee appointments and have historic data similiar to below:

DATE EMP POSITION SALARY
01-JUL-2010 TED SECURITY GUARD 30,000
12-DEC-2012 TED PORTER 35,000
01-OCT-2012 TED IT ANALYST 50,000
01-FEB-2012 TED MGR IT 80,000
01-AUG-2009 RALPH MGR IT 81,000
20-JAN-2012 RALPH CIO 120,000

How can I use the date field to modify the &&CM__SCDATE during the data flow. Your suggestion of running the job iteratively works when you have a series of snapshots . . . however, in this situation we would have to run the job for every unique date value . . . Is this the best way.


Webfocus 8.0
HTML
 
Posts: 20 | Location: Guelph, Ontario | Registered: May 17, 2013Reply With QuoteReport This Post
Guru
posted Hide Post
Yes, although not every day for the past five years, just those on which there were changes.

So run SELECT DISTINCT DATE FROM SOURCE to create a parameter file to use with the iterator.

That's the best I've come up with. Presumably you only getting the historic data once to get up-to-date. Then you are just processing new changes.
 
Posts: 380 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Member
posted Hide Post
Thanks for the help Clif


Webfocus 8.0
HTML
 
Posts: 20 | Location: Guelph, Ontario | Registered: May 17, 2013Reply With QuoteReport This Post
Member
posted Hide Post
Clif,

Is there restriction on the data type used for the dimension start and end dates . . . the reason I'm asking, is that I created a test dimension load yesterday that included setting the &&CM__SCDATE variable and it worked. Now I just ran my dimension loads none of them were able to reference the &&CM__SCDATE value that I set in a pre-flow procedure.

Here's the contents of my pre-flow procedure:

-SET &&CM__SCDATE=20100501;
-SET &&CM__SCDEADT=30000101;

Any ideas?


Webfocus 8.0
HTML
 
Posts: 20 | Location: Guelph, Ontario | Registered: May 17, 2013Reply With QuoteReport This Post
Member
posted Hide Post
Ignore the last post.

I figured out what I did differently today and yesterday. Yesterday I put the pre-flow process within same data flow file. Today, I created a master flow process and tried to set the &&CM__SCDATE in a procedure and then called another data flow.


Webfocus 8.0
HTML
 
Posts: 20 | Location: Guelph, Ontario | Registered: May 17, 2013Reply With QuoteReport This Post
Member
posted Hide Post
Hi,
I have implemented SCD2 Type using in built IWAY functionality. I am using all SCD fields which are current flag,begin date , end date.
but my problem is after the first run for any subsequnt change to the record I want the startdate for the new record to be the end date of the previos record.

Please suggest how can I implement this ?

Regards
Venkat

This message has been edited. Last edited by: VS4,


WebFOCUS8,IWAY DM
Windows, All Outputs
 
Posts: 9 | Registered: July 29, 2014Reply With QuoteReport This Post
Guru
posted Hide Post
That's the default behaviour. When a Type II change is detected the end date of the then current record is changed from NULL (by defualt) to TODAY (by defualt); the new record is inserted with begin date of TODAY and end date of NULL. It's now the current (active) record.
 
Posts: 380 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Member
posted Hide Post
Yes thanks Clif.It was my mistake earlier while passing the varibles , its working fine now.

Regards
Venkat


WebFOCUS8,IWAY DM
Windows, All Outputs
 
Posts: 9 | Registered: July 29, 2014Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [Solved] Building SCD Type 2 Using Histroic Data

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.