F1 F1 F1::: Dimension Update Lookup

[kettle_anonymous]

Hello Matt,
Having trouble using Dimention Update Lookup step for ETL.
Problem is the Date Range Start field and Date End fields.


Lets say I have a Customer dimention that has two date columns
Date Created and Date Deleted.
Now lets say I want to answer the questions:
How long has "XYZ" been our customer?
Is "XYZ" a valid / active customer?
Using Dimension Lookup Update, is there any way I can
answer both these questions using the two date columns mentioned above.
In other words can I use the date columns from Table Input to populate these fields.

[MattCasters]

Hi Anonymous,

This is not possible by design. That is because it is not the primary use of the from-to date range.
The from-to date range is saying something about the validity of that *version* of the customer record. It is not per-se the range on which the customer was with the company. (created/deleted)

For example you might have a creation date of 2001/01/01.
That doesn't mean that you can't have multiple versions of the record in cases where you want to capture changes. (address changes for example).
All the versions of this record will have the same creation date. That is because you want to answer business questions like:

What's the sales in February of customers created in January? Even if the customers properties change, you still want to know about the fact that the customer was created in January.

I have similar reservations about the end-date being used for Date Deleted although the difference is more subtle there.
For example, in cases of re-activated customers, descriptions and type changes that trigger the creation of extra records etc.

So, to capture my opinion on this: Creation date is out of the question, deletion date: maybe we can make this an optional feature... maybe.
It is possible to convince me... sometimes ;-) Perhaps if you do the work of filing for a feature request in the trackers ;-))

Hope this clarifies this,

All the best,
Matt

[kettle_anonymous]

Thanks Matt,

We have decided to go with different dates for representing creation and validity.
In other words we will have a Created_Date and also will have the Valid_From and Valid_To.

Thanks for the reply though, It validates the decision taken.

As far as giving you more work to do, maybe

Thanks,
Carl Daver