Loading Future Dated Rows to Dimensions

View previous topic View next topic Go down

Loading Future Dated Rows to Dimensions

Post  DM_SUPPORT on Thu Nov 25, 2010 10:49 am

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?

DM_SUPPORT

Posts : 1
Join date : 2010-11-25

View user profile

Back to top Go down

Re: Loading Future Dated Rows to Dimensions

Post  hang on Thu Nov 25, 2010 4:36 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Loading Future Dated Rows to Dimensions

Post  warrent on Wed Dec 01, 2010 8:52 am

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...
avatar
warrent

Posts : 41
Join date : 2008-08-18

View user profile

Back to top Go down

Re: Loading Future Dated Rows to Dimensions

Post  obiapps on Sun Dec 05, 2010 1:15 am

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?

obiapps

Posts : 21
Join date : 2010-09-28

View user profile

Back to top Go down

Re: Loading Future Dated Rows to Dimensions

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum