SCD2 and foreign key in dimension

[remiby]

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?

[ngalemmo]

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.

[BoxesAndLines]

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

[ngalemmo]

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.

[BoxesAndLines]

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

[hang]

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.

[ngalemmo]

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.

[remiby]

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).

[hang]

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.

[remiby]

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?

[Lindell]

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).

[remiby]

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).

[ngalemmo]

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.

[Jeff Smith]

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.

[dim67]

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

[Sponsored content]