Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3

View previous topic View next topic Go down

Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3

Post  mlapenna on Thu Jan 17, 2013 10:07 pm

Good day all,
I have a question on this article by Margy Ross. I read it because I was trying to solve a requirement for user. I am developing a star schema but the user is coming from a design where codes are used throughout (instead of surrogate keys). This introduces much larger queries, with joins from facts to many dimensions. So I thought "Type 2 with Natural Keys in the Fact Table" would work. I have two questions:
1) Is the natural key stored liked a measure in the fact or in the PK? (I think the former)
2) Can just the natural key be stored and leave out the Type 1 SCD? My thinking is that if the user needs more than the code, they can just join to the dimension using the dimension's surrogate key.

Am I on the right track?
Thank you!

mlapenna

Posts : 3
Join date : 2013-01-17

View user profile

Back to top Go down

Re: Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3

Post  BoxesAndLines on Thu Jan 17, 2013 10:19 pm

No. You need to use surrogate keys. The only textual attributes in the fact are ideally degenerate dimensions.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3

Post  mlapenna on Fri Jan 18, 2013 7:15 am

Readers, please disregard the answer from "Boxes and Lines". It appears he quickly scanned my post, without looking at Margy's article. There are cases when you can add the natural (or durable) key to a fact. If anyone has encountered this requirement, or observed it in practice, please comment.

mlapenna

Posts : 3
Join date : 2013-01-17

View user profile

Back to top Go down

Re: Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3

Post  BoxesAndLines on Fri Jan 18, 2013 9:52 am

No worries mlapenna, no one listens to me. Anyways, this topic was discuss awhile back, http://forum.kimballgroup.com/t955-current-and-historic-dimensions-one-table-or-two.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3

Post  mlapenna on Fri Jan 18, 2013 10:19 am

BandL, Thanks for referencing that other thread. It kind of discusses my post but doesn't address Margy's point. If anyone has an opinion, specifically regarding my two questions, I'd be happy to hear it.

mlapenna

Posts : 3
Join date : 2013-01-17

View user profile

Back to top Go down

Re: Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3

Post  hang on Sat Jan 19, 2013 8:18 pm

It might be more helpful if you could also refer us to the site in your post: http://www.kimballgroup.com/2005/03/10/slowly-changing-dimensions-are-not-always-as-easy-as-1-2-3/

However the NK in fact table is not like a measure, neither is it a degenerate dimension. It's a convenient way to retrieve different versions of dimension attributes without self join. It's rather a key to a dimension with date range or current flag constraint. It's only worth it if the dimension is big and type 2. I guess as a general guideline, SK in fact table and self join for current version should apply to most dimensions. Any other structure would be special response to special case, especially to monster type 2 dimension.

I would still stick to SK in the fact table, even if it's type 1 dimension. SK is not only to facilitate type 2 but also minimize impact on DW caused by any change on NK in business. For instance if a NK has been redefined in business, you could just realign much smaller dimensions without messing up the fact tables.


hang

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

View user profile

Back to top Go down

Re: Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3

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