Slowly Changing Dimension Stage and initial Effective Date

[meriem_ens]

Hi,

In the scd stage is it possible to set the effective date column to a value (during the intial dimension load) and then set it to currentTimeStamp after.

The problem that I have now is that it is always set to CurrentTimestamp while for the initial load it should be 1900.01.01.

Is there an option in datastage to set an initial value for that column when the table is empty or should I do something like:

Execute Command :
test if dimension table is empty, if yes set v_effective_date to 1900.01.01 else set v_effective_date to currentTimeStamp

thanks

[priyadarshikunal]

Intelligent SCD Stage?

Don't remember trying that one, but you may be able to pass that value in parameter? Sorry can't check it at the moment either.

[ray.wurlod]

You could always perform an update immediately after the initial load.

[kaps]

We use separate set of jobs for initial load to handle such requirements. You can just copy the same set of jobs as initial load jobs and just change what you need to change and run only once.

[meriem_ens]

Intelligent SCD Stage?

Don't remember trying that one, but you may be able to pass that value in parameter? Sorry can't check it at the moment either.

Yes, just like in SAP DataServices where the stage detect if it is the first load then assign a default effective date to the newly generated row.

[meriem_ens]

You could always perform an update immediately after the initial load. ...

Hi,

I don't want to update 1 000 000 rows.

Thanks

[meriem_ens]

We use separate set of jobs for initial load to handle such requirements. You can just copy the same set of jobs as initial load jobs and just change what you need to change and run only once.

There will be some code duplication, am I right ? or am I missing something.