Data Model

[AlexDawson111]

Hi. I’m looking for some advice on how to connect two star schemas together. 

The scenario is as follows:-

I have an existing star schema which is holding all my actual costs and revenues FactCosts from my job costs database. In this schema the grain is at Job Level. 

I have an existing star schema that is holding all my job scheduling data from my scheduling database. In this schema the grain is as a sub task level. One job from the first star schema contains many tasks. The tasks within this schema have many cost codes. One task can have many cost codes. 

The cost code from this schema can match a cost code from the cost code dimension in the first star schema. 

Ideally what I want to achieve is linking two star schemas in one pbix. However the grain is different in each fact table. 

Any advice would help

Thanks

[AlexDawson111]

@Greg_Deckler 

[AlexDawson111]

Hi Greg,

Thanks for the reply. 

So there are some common/shared dimensions but not all. 

The problem I'm having is say for example:-

1 Job has a total cost of 10k (provided from the factcost table)

I then want to show a breakdown of these costs per sub task level which is in the other fact table. 

Really I only want to me able to slice and include dimensions from the cost star schema. For example create a table visual that consists of my costs and my scheduling data combined. 

hope that makes sense. 

alex

[Greg_Deckler]

@AlexDawson111 OK, so what if you built a relationship between your jobs table and your subtasks table based on job code? Is it Many-to-Many (*:*) if you do that or 1:*?

Super difficult to visualize without sample data.

[Greg_Deckler]

@AlexDawson111 So do you have the same dimensions in each of these star schemas?