Focal Point
[CLOSED] Type II Begin Date

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/5787057176

July 19, 2014, 04:02 PM
noleary
[CLOSED] Type II Begin Date
Here is what I am trying to accomplish with a type II dimension. When a new record comes into the dimension i would like to set the begin_date to 1900-01-01 and end_date = 9999-12-31. When a type II field changes for that record I want to set the begin_date = &YYMD and end_date = &YYMD - 1. I have the end dates working properly but I cannot seem to get the begin dates to behave properly.

I have tried setting the variable &&CM_SCDATE
'(IF D.BEGIN_DATE EQ &LO_DATE.EVAL
THEN &&TYPE_II_DT_NAME.EVAL
ELSE IF D.END_DATE EQ &HI_DATE.EVAL
THEN &LO_DATE.EVAL
ELSE &&TYPE_II_DT_NAME.EVAL)'

And I have tried this as well:
'(IF D.END_DATE EQ &HI_DATE.EVAL
THEN &&TYPE_II_DT_NAME.EVAL
ELSE &LO_DATE.EVAL)'
but niether seems to work right.

&LO_DATE = 1900-01-01
&HI_DATE = 9999-12-31
&&TYPE_II_DT_NAME = &YYMD-1


Has anyone attempted something like this? Basically I need to identify if the record is a new or existing record so that I can set the CM__SCDATE correctly.

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


WebFOCUS 7.6
Windows, All Outputs
July 20, 2014, 07:58 AM
Juan Romero
Hello noleary,

Try with:

-SET &LO_DATE = DATECVT('19000101','A8YYMD','YYMD') ;
-SET &HI_DATE = DATECVT('99991231','A8YYMD','YYMD') ;

Regards,


FOCUS 7.1.1
OS/390 and Windows
Excel, Txt
July 21, 2014, 08:26 AM
noleary
That is not my issue. I can get the HI_DATE and LO_DATE to be inserted. What I cannot do is have the LO_DATE inserted in the Begin_Date when it is a new record and &YYMD inserterd in the Begin_Date when it is a changed record.

Here is how I have the variable set in the data flow.

-Big GrinEP_2
-TYPE (ICM18097) DEP_2: Set variables
-DEFAULT &&PROCESS_DATE = &YYMD
-SET &LO_DATE = 19000101;
-SET &HI_DATE = 99991231;
-SET &&TYPE_II_DT_NAME = 'EFF_DATE';
-SET &&CM__SCDEIDT = DATE_B4_UPDATE;
-SET &&CM__SCDEADT = &HI_DATE;
-SET &&CM__SCDATE = '(IF D.END_DATE EQ &HI_DATE.EVAL THEN &&TYPE_II_DT_NAME.EVAL ELSE &LO_DATE.EVAL)';
-GOTO Big GrinEP_MAIN;

EFF_DATE is defined in the flow as &&PROCESS_DATE and DATE_B4_UPDATE is defined as EFF_DATE - 1

My end dates for the Type II work fine. It is the Begin_Date I am struggling with


WebFOCUS 7.6
Windows, All Outputs
July 21, 2014, 04:31 PM
Clif
DataMigrator support for Slowly Changing Dimension Type II was designed to assign the "current" date as the begin date for active records.

As you know you can use some other date by settig the global variable &&SCM__SCDATE for re-runs or simply running the flow as though it were some date in the past. This can be easily done in the variables object in a process flow.
Note that I'm sorry we can't support editing the generated focexec; all changes will get lost when you reopen the flow.

So you could initialize the begin date values by running the flow the first time with it set to the turn of the last century. Then for subsequent runs remove the setting and any updates will be assigned the current date.