How to bring complex relations between dimensions (Matrix Organization) into star schema (SSAS tab)

[GertW]

Hi community,

I'm reasonably familiar with the star schema design. However, I'm currently working on a project where I need to reflect a very complex business reality (best described as "Matrix Organization") in a data model - and I could really need a hand here. 

Here's an ERD of our business reality:The only fact table are journal entries (orange), everything else are dimensions. The accounting software tracks the G/L account, and the corresponding cost centre. The cost centre itself is linked to several other dimensions:

- Researcher
- Program (research topic)
- Institute (research facility)

On top of this, there are also other relationships between dimensions, for example:

- Researchers are linked directly to programs (those can be different from the programs to which their cost centres are linked).

- Researchers are linked to one institute (which can be different than the institutes to which the cost centres are linked)

- One program can be linked to several institutes (which can different from the programs of the related cost centres).

Details don't really matter at this point. My problem is that overall I'm unsure how to translate those complex relationships into a star schema. (I'm also investigating on our end if such complexity is really necessary, or if there are opportunities for simplification. But for now, it seems that "this is it").

Basically I'm looking for some advice regarding the correct approach.

I believe that probably I need to push the corresponding keys into the fact table, for example like this:

In this example "Staff" has two roles, "Responsible for Cost Centre" and "Responsible for Principal Investigator (researcher)". But then, I know that at any given time, only one relationship can be active, either "Staff_FCC_Key" or "Staff_PI_Key". If I'm not mistaken, this would mean that I need to create different measures and use one or the other. Clearly, I'd like to avoid this and allow to use both "roles" at the same time.

Would someone have an idea how to tackle this?

[v-lid-msft]

Hi @GertW ,

Could you please share some sample to clarify your issue more explicitly? It should work well when filter RFS using PI and FCC table across the your fact table.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GertW]

Just a quick update (in case someone's interested): I believe the best approach might be "role-playing dimensions". That's what I'm working on, it starts looking like a decent star schema. I seem to need one or the other bridge table (for many-to-many relationships), but as far as I know, those don't contradict the star schema definition.