Loading Future Dated Rows to Dimensions

Hi All,

Our OLTP system supports effective dated concept. So users can enter Future Dated Rows also. And in our Warehouse we have both Type1 Dimesnions and Type2 Dimensions.

When loading the data to Type1 dimesnions, if we load future dated rows then we will have only Future Dated record only in our dimension, and if we are loading the data to Type2 Dimensions, how do we have to handle the CURRENT FLAG? We cannot put the CURRENT FLAG as YES to a Future dated row?

How do we have to handle this scenario in warehouse?

The CURRENT FLAG column is really a derived field based on SCD Start and SCD End date. Having a physical field for it is really for convenience purpose and it normally defaults to 'Y' when new dimension record is added. However in your case, you may have to update the field value based on the other two SCD date fields in the dimension on nightly base, if SCD time is not significant. Or you may remove the physical field and present it as a logical field through calculated field or view, if scheduling an update for such a minor work is too much hassle.

As hang says, the Current field is meant as a convenience when you want to identify the current rows to use in the nightly incremental ETL process, or when you want to present users with a view of only the Current rows in the dimension (so they don't double count when they work with the dimension on a standalone basis).

Note that having the future rows in the dimension has no effect on any query that joins to the fact table because each fact table row should tie to the dimension row that was in effect when the fact occurred. Future dimension rows should not have any associated fact rows.

The UPDATE at the end of the dimension load process to calculate this based on the Effective_Date and End_Date fields might look like this:

DECLARE @Current_Date Date;
SET @Current_Date = '12/01/2010';
UPDATE dbo.Customer_Master
SET Current_Flag = CASE WHEN @Current_Date BETWEEN Eff_Date AND End_Date
THEN 'Y'
ELSE 'N'
END;

WRT the Type 1 dimensions, you may need to hold these values in a separate table until they become current. Or, maybe these should be converted to Type 2 attributes as well...

Why would you want to bring these future dates in the Datawarehouse? Cant the OLTP system change these "future" effective dates in between each ETL load?