Customer Address Change

[obieedev]

We have a requirement where when a customer changes his/her address the OBIEE report has to capture the previous address and the changed address. We are going against the Transactional Tables. Can someone guide me as to what steps I need to follow in EBS..

Regards

 

[dkyrtata]

This is a Type2 slowly changing dimension. That means that your DimCustomer dimension table must store different versions of the same record.

For this, I use a row_effective_start_date and row_effective_end_date to indicate which record was active an any point in time. The first version of a record would have a very early start-date (I use 01-Jan-0050). The last record would have a very late end-date (I use 31-Dec-9000). Every version except the last would have its end-date equal to (or 1 day earlier than) the next record's start-date. (In my case they keep them equal)

When you look for the record you want, you must specify the customer id and the date that you want to find the record for (for the purpose of obtaining the correct surrogate key). Once the surrogate key in your fact table points to the desired DimCustomer record, you won't have to worry about finding the right record in your reporting.

If you choose to maintain equal dates for your end-date and start-date of the previous/next records, you can end up finding 2 records if the search-date supplied falls on that boundary. In my case, I merely use the later of the two records.