Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3
3 posters
Page 1 of 1
Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3
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!
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
Re: Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3
No. You need to use surrogate keys. The only textual attributes in the fact are ideally degenerate dimensions.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3
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
Re: Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3
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.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3
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
Re: Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3
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.
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
Similar topics
» Not so slowly changing dimensions
» Slowly changing heterogeneous dimensions
» Slowly changing fact with SCD2 Dimensions
» Unsure about relationship with Slowly Changing dimensions.
» Bridging Tables and Slowly Changing Dimensions
» Slowly changing heterogeneous dimensions
» Slowly changing fact with SCD2 Dimensions
» Unsure about relationship with Slowly Changing dimensions.
» Bridging Tables and Slowly Changing Dimensions
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum