Current Flag in a Dimension

View previous topic View next topic Go down

Current Flag in a Dimension

Post  1dwbi on Sun Mar 04, 2012 8:00 pm

This I think could be a dumb question, nonetheless here it is:

I have a customer dimension that has type 1 and type 2 attributes. I have included effective and expiration dates for the type 2 columns.

When I join this customer dimension to a fact table:

Do I need to use a "current flag" column to pick up the latest column without working with dates?

Or will the fact table associated with this dimension automatically pick up the largest surrogate key for that customer ID?

Please let me know.

Thanks

1dwbi

Posts : 8
Join date : 2012-03-01

View user profile

Back to top Go down

Re: Current Flag in a Dimension

Post  ngalemmo on Mon Mar 05, 2012 1:00 am

The fact is going to reference the version of the row at the time the fact row was added to the table. Getting the current version of the dimension row requires a self join on the dimension using the natural key. The current flag is a conveience and is recommended.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Current Flag in a Dimension

Post  1dwbi on Mon Mar 05, 2012 1:06 am

Thank you very much for clearing that up. Another loose end tied up. (Columbo style)

1dwbi

Posts : 8
Join date : 2012-03-01

View user profile

Back to top Go down

Re: Current Flag in a Dimension

Post  Vishy on Mon Mar 05, 2012 1:35 am

Once you populate the fact row with the latest surrogate key then you can just join fact and dim and get your correct data, but if you have current flag also , you can use that in the query and it will make query a bit faster as this filter filters out already history record and provides fact latest rows to join to.

I have not seen many projects implement this but you would be implementing ideal solution of you have surrogate key,start date,end date and current flag ....

I have seen few projects where start date and end date were used but no surrogate keys..it killed report performance and in last phase a cognos consultant from the cognos company itlsef was hired to help.

Vishy

Posts : 75
Join date : 2012-02-28
Location : India

View user profile

Back to top Go down

Re: Current Flag in a Dimension

Post  1dwbi on Mon Mar 05, 2012 4:41 pm

Thanks for your reply. I agree that it is better to err on the safe side and have a current flag,star date, end date and a surrogate key.

1dwbi

Posts : 8
Join date : 2012-03-01

View user profile

Back to top Go down

Re: Current Flag in a Dimension

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