SCD Type 2 - effective/expiration dates
pnoskoAug 19 2011 — edited Aug 25 2011OWB 11g 11.2.0.2
I'm coming in cold to an environment where there's a business need to have a pair of start/end dates in a type 2 DIM, very simillar to the way the effective/expiration dates are maintained by OWB. The difference is that the end date in the closed record (becoming history) and the open date in the active record are not (the default) SYSDATE, but come from a source table/column in the mapping. There is no gap in these date/times.
None of them seem to be working as intended. I know the concepts of type 2, but am not well-versed on working with them. I guess the first question is-- is it even possible to manipulate a column in a record as the dim controls the creation of a history record? It seems to me we are trying to mix manual and automated, and it won't work. But maybe there is a trick to this that I haven't yet learned.
Any insights would be greatly appreciated.
~Peter