SCD2 and foreign key in dimension

View previous topic View next topic Go down

SCD2 and foreign key in dimension

Post  remiby on Tue Apr 10, 2012 9:04 am

Let's say I have two dimensions clientDIM and accountDIM. accountDIM contains a FK to clientDIM called client_key (the surrogate key).
Now let's assume clientDIM is modeled as a SCD2. When a change occurs in clientDIM a new row with a new key is generated.
Now, how do I handle client_key in accountDIM? My guess is that I am supposed to update all the client_key in accounts for which clientDIM has been updated with the new client_key. Am I right?

remiby

Posts : 19
Join date : 2012-04-10
Location : Paris, France

View user profile http://www.horus-df.com/

Back to top Go down

Re: SCD2 and foreign key in dimension

Post  ngalemmo on Tue Apr 10, 2012 9:40 am

remiby wrote:
Now, how do I handle client_key in accountDIM?

You don't. You should not have a FK in account to client. Even if you do, you still do nothing.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: SCD2 and foreign key in dimension

Post  BoxesAndLines on Tue Apr 10, 2012 11:48 am

I disagree. You have to update the FK in the type 1 dimension. That's why we avoid these types of relationships.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: SCD2 and foreign key in dimension

Post  ngalemmo on Tue Apr 10, 2012 12:28 pm

BoxesAndLines wrote:I disagree. You have to update the FK in the type 1 dimension. That's why we avoid these types of relationships.

It's the same as referencing a type 2 from a fact. You don't update the fact because a type 2 dimension is updated. I agree you should avoid such relationships, but it has nothing to do dimension types, it's the snowflake argument in general.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: SCD2 and foreign key in dimension

Post  BoxesAndLines on Tue Apr 10, 2012 8:21 pm

But it's not a fact, it is a type 1 dimension. That implies it always reflects current state.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: SCD2 and foreign key in dimension

Post  hang on Tue Apr 10, 2012 9:08 pm

I guess if there is a fact table that also contains both FK's, than the historical correlation is tracked in the fact table and it makes sense to reflect only current client profile in the account dimension with type1 response. I would name it as CurrentClientKey to avoid any confusion.

If there is no such a fact table and you are still interested in historical relationship between two dimensions, you may have to issue a new record in account dimension for any type2 change on the client dimension, implying that any type 2 attribute on client is also a type 2 for account.

hang

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

View user profile

Back to top Go down

Re: SCD2 and foreign key in dimension

Post  ngalemmo on Wed Apr 11, 2012 12:01 am

BoxesAndLines wrote:But it's not a fact, it is a type 1 dimension. That implies it always reflects current state.

Ok. Let's just chalk this one up as reason #35 as to why you shouldn't snowflake.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: SCD2 and foreign key in dimension

Post  remiby on Thu Apr 12, 2012 6:18 am

Thank you for your replies. I wonder how I could avoid snowflaking in my case. Without snowflake how can I get the client(s) linked to an account?
In his book, Kimball recommends to use a bridge table between account and client dimensions which is a king of snowflake schema. And in this case I would face the exact same problem of updating the bridge table every time the client dim changes (SCD2).



remiby

Posts : 19
Join date : 2012-04-10
Location : Paris, France

View user profile http://www.horus-df.com/

Back to top Go down

Re: SCD2 and foreign key in dimension

Post  hang on Thu Apr 12, 2012 7:14 am

remiby wrote:I wonder how I could avoid snowflaking in my case. Without snowflake how can I get the client(s) linked to an account?
In your case, as an account only belongs to one client, you could reflect the relationship in the relevant fact table instead of snowflaking the dimension. I believe the fact table would cover all the correlations.

However in some other cases, you can't avoid some form of snowflaking. If the relationship between account and client is m-m, you cannot even have Client FK in the account dimension, not even in an account based fact table because it would be a multivalued dimension to the fact. The only option is to have a bridge or a factless fact table. Sometimes, the relationship is predetermined and may not appear in any fact table at all, like product promotion, so you might need to load the relationship into a coverage factless fact table.


hang

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

View user profile

Back to top Go down

Re: SCD2 and foreign key in dimension

Post  remiby on Fri Apr 13, 2012 4:56 am

What is the "relevant fact table"? Would you have an example of this kind of fact table I can use to make the link between client and account?

remiby

Posts : 19
Join date : 2012-04-10
Location : Paris, France

View user profile http://www.horus-df.com/

Back to top Go down

Re: SCD2 and foreign key in dimension

Post  Lindell on Fri Apr 13, 2012 8:59 am

remiby wrote:What is the "relevant fact table"? Would you have an example of this kind of fact table I can use to make the link between client and account?
Well any fact table that has a connection to DimAccount. All you do is adding the DimAccount surrogatekey and the DimClient surrogate key to the same fact table. That way you get the relationsship from the fact to the account and client without needing to snowflake anything.
Putting two keys in the fact table also solves your SCD problem (of course as long as the problem isn't m:m which in that case you will need a bridge table).

Lindell

Posts : 6
Join date : 2011-08-02

View user profile

Back to top Go down

Re: SCD2 and foreign key in dimension

Post  remiby on Tue Apr 17, 2012 12:55 pm

OK so I could use my transactions fact table? Or my daily snapshot balance fact table? But these tables are huge. Does that really make sense or should I create a factless fact table to link client and accounts (1:n relationship).

remiby

Posts : 19
Join date : 2012-04-10
Location : Paris, France

View user profile http://www.horus-df.com/

Back to top Go down

Re: SCD2 and foreign key in dimension

Post  ngalemmo on Thu Apr 19, 2012 11:03 am

remiby wrote:OK so I could use my transactions fact table? Or my daily snapshot balance fact table? But these tables are huge. Does that really make sense or should I create a factless fact table to link client and accounts (1:n relationship).

Sure it makes sense. That is what a star schema is. Fact FKs provide the context for the measures. If the context includes client and account, they are dimensions for that fact.

avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: SCD2 and foreign key in dimension

Post  Jeff Smith on Thu Apr 19, 2012 2:03 pm

remiby wrote:OK so I could use my transactions fact table? Or my daily snapshot balance fact table? But these tables are huge. Does that really make sense or should I create a factless fact table to link client and accounts (1:n relationship).

If the fact table is huge, that's all the more reason not to snowflake the design.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: SCD2 and foreign key in dimension

Post  dim67 on Fri May 25, 2012 1:36 pm

If the relationship between account and client is 1-m, you could flatten it out.

dim67

Posts : 15
Join date : 2012-05-05

View user profile

Back to top Go down

Re: SCD2 and foreign key in 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