SCD, Effective Start Date in initial records

[camboo]

Hello all,

I have an urgent problem with DataManager's (v10.2) slowly changing dimension, with Effective Start Date option. I'm learning these topics so all good advices are welcome!

I have just studying the DataManager's option named 'Effective Start Date in initial records' and wondering if that option could be used to tackle my problem?

So here comes the problem:

When the very first row of some business key is created in dimension (Slowly changing dimension) table, the system sets Effective Start Date of that row to date of data load. However, Effective Start Date of that FIRST row should be set to previous day, as that was the original creation day of that business key in the source system.

So is the option 'Effective Start Date in initial records' answer to my problem? Am I able to configure my DataManager to handle the first database row of every business key with it? The correct date information would be available in the current dataflow / dimension build, but how to configure the system so that the first row (and only the first row) would use that data? For the rest of business key rows (when the type2 changes will happen) it would be ok to save for instance data loading date as Effective Start Date value.

Thanks a lot for You answer in advance!!

[MFGF]

Hello all,

I have an urgent problem with DataManager's (v10.2) slowly changing dimension, with Effective Start Date option. I'm learning these topics so all good advices are welcome!

I have just studying the DataManager's option named 'Effective Start Date in initial records' and wondering if that option could be used to tackle my problem?

So here comes the problem:

When the very first row of some business key is created in dimension (Slowly changing dimension) table, the system sets Effective Start Date of that row to date of data load. However, Effective Start Date of that FIRST row should be set to previous day, as that was the original creation day of that business key in the source system.

So is the option 'Effective Start Date in initial records' answer to my problem? Am I able to configure my DataManager to handle the first database row of every business key with it? The correct date information would be available in the current dataflow / dimension build, but how to configure the system so that the first row (and only the first row) would use that data? For the rest of business key rows (when the type2 changes will happen) it would be ok to save for instance data loading date as Effective Start Date value.

Thanks a lot for You answer in advance!!

Hi,

Depending on whether or not the Effective Start Date is being read into your hierarchy, you may or may not need to use this option. As I see it you have two choices:

1. If you are holding the Effective Start Date in your source hierarchy (the hierarchy being delivered by the dimension build), then you need to go to the source template for that hierarchy and change set the Behavior property for the date attribute to 'Effective Start Date'. You then need to edit the template used in your dimension build, delete the auto-generated eff_date attribute, and change the Behavior property of the Effective Start Date from the hierarchy to 'Effective Start Date'. This will mean that the date being delivered from your hierarchy will then become the effective start date in your dimension table for any initial instances of rows.

2. If you are not holding the Effective Start Date in your source hierarchy, then you're going to need to use the 'Effective Start Date in initial records' property of the dimension build template (as you suggest above). Change this to a value of 'Variable', and add a variable name in the field that appears next to this property. Then go to the properties of your dimension build, go to the Variables tab, add a new variable with the same name you specified in the template, set the Type to be 'Date', and code an initial expression for the variable as AddToDate( SysDate(), -1 )

Cheers!

MF.

[camboo]

Hi and thanks for the fast reply!

Effective Start Date is being read into my hierarchy, so I think the choice number 1 above will be the right one for me.

So I will follow these steps:

1.) I'm are holding the Effective Start Date in my source hierarchy (the hierarchy being delivered by the dimension build)
2.) I need to go to the source template for that hierarchy and change set the Behavior property for the date attribute to 'Effective Start Date'.
3.) I then need to edit the template used in my dimension build, delete the auto-generated eff_date attribute, and change the Behavior property of the Effective Start Date from the hierarchy to 'Effective Start Date'.
4.) This will mean that the date being delivered from my hierarchy will then become the effective start date in my dimension table for any initial instances of rows.


Further question:

I'm wondering what's the meaning of 'effective start date in initial records' setting in Dimension table properties --> Dimension history options?

I assume that if I set that option as 'from source attribute', DataManager will fetch initial record's value from source hierarchy. Then, in case I set that as 'according to reference template', DataManager uses the 'Effective Start Date in initial records' property of the dimension build template (choice 2 of your reply).
Is that assumption correct?

Now comes the interesting part: I have two separate date fields in my source hierarchy, lets say establishment_date and modification_date. Now I need to the set establishment_date as Effective Start Date for any initial instances of rows. Then for the rest instances of rows, I need to set modification_date as Effective Start Date. How can I do that with DataManager?


Thanks!

[camboo]

Hello!

May there be any solutions to my problem in previous post?

Thanks!

[MFGF]

Now comes the interesting part: I have two separate date fields in my source hierarchy, lets say establishment_date and modification_date. Now I need to the set establishment_date as Effective Start Date for any initial instances of rows. Then for the rest instances of rows, I need to set modification_date as Effective Start Date. How can I do that with DataManager?

You would probably need to get creative with the data feeding into your hierarchy for this. Since you can only base the Effective Start Date on a single attribute in your input and output templates, you'd need to read the dimension table at the same time as the source data for your hierarchy, figure out whether a row for that PK already exists in the table, then allocate either the establishment_date or the modification_date to a new item that you define as being the Effective Start Date.

Cheers!

MF.

[camboo]

Hi, and thanks for reply!

I was also considering this kind of solution to handle my initial records:

-I will use the 'Effective Start Date in initial records' property of the dimension build template and change this to a value of 'Null'.

-I will then create one new sql node after my dimension build and write sql which searches all rows where 'Effective Start Date = null'. Then it just updates those rows by setting 'Effective Start Date = establishment_date'