How to model multiple fact tables against related dimensions

[HarrisonBi]

Hi guys,

I really am trying to understand the best practice and way that I can solve the following problem I am having. I have created a dummy data model of my problem (omitting a couple fact tables) but this is where the problem lies. How do I model the following situation correctly?

Companies have monthly budgets set per Country

One Company can have many Stores

One Store can only belong to one Company

Stores have sales recorded per month 

Basically I don't know how to relate these tables correctly, I have attempted to create a bridging table for Companies and Stores.

What I want to be able to do is; select a country code in a slicer and it only show the companies, stores, budgets and sales pertaining to the entities that belong within the country. I want to set row level security against country code so users can only see data for their assigned countries.

Here is my current data model and tables, but it doesn't work correctly. Can someone please advise on what the best way to model this is?

Thanks.

[samratdevkota]

Hello @HarrisonBi ,
I have a similar problem,
have you figured out any solution to this?

[Anonymous]

HI @HarrisonBi,

Perhaps you can take a look at the following link about power bi star schema relationship designs if helps:

Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

For this scenario, you need to extract the most similar field type values and store them to a dimension table at the bridge to link other tables. 

BTW, you can also check the following link about AS data mode relationship. (power bi data model are based on AS tabular instance)

Relationships in analysis services tabular models 

Regards,

Xiaoxin Sheng