Partial dimension tables (same dimension from two (or more) dim tables)

[El_Servas]

Hello! I have this Issue where my fact table has a ClientID, their Balances, a "Kind" and a Branch.

We have the concept of Regions and of course we would like to filter on them.

The thing is, for some Kind of clients, the Region is based on it's Branch. And for the other Kind, it's a direct list by clientID.

So I guess my question is if we can have a dimension come from two different dimTables, one for some records on some condition, and another for the other records on other condition.

I don't know how to call this scenario. Partial dimensions? Complex dimentions? Partitioned? Composite? I hope I'm being clear.

Thank you for your help.

[v-jingzhang]

Hi @El_Servas 

It depends on how you want to create visuals in the report. If you are going to display two kinds of clients data in different visuals, it's ok to have two dimension tables in the model. Both dimension tables are connected to the fact table and filter the fact table separately. 

If you want to have both kinds of clients in the same dimension table, for the kind whose Region is not based on Branch, does it mean that its "Branch" data is null? If so, you can replace null in Branch with its clientID. That is to treat "ClientID" as "Branch" or treat "ClientID" as "Region". Is this acceptable?

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.