Need Help (Multiple Fact tables)

[DeGiacomo7272]

I am new to the HR Data Space and I am setting up our "HR Scorecard" and want to use Star Schema but would love some help from the community. The process I have is we have a rolling headcount so I have these "Master Files" which contain point in time data at the end of each month going back to 2018, and we use the live files (that connect to our HR software via Odata), which on the last day of the month I use Power Automate to take a snapshot and I append that months data onto the master files. 

So in reality I have 10 fact tables ( i think): Headcount (Current + Master), Hires (Current + Master), Terms(Current + Master), Job Changes (Current + Master),  Promotions (Current + Master), and I have 3 dim tables, Date table, Location, and Diversity. 

I would like some guidance into how to best set this up: 

- Do i even need a relationship between the Live and Master files? 

- The reason I have duplicate dim tables is becuase my connections vs the live data kept becoming in active but my connections with my master files for the dim table was fine without any inactive relationships, should I delete the live to master relationship or If I keep it?

-Can I filter the Live table by the dim tables (diversity & location) that have a relationship to the master files? ex. Live Promo Table (--> one to many) Promo Master Table (--> many to one with location dim table) 

Diversity to Fact Tables = 1 to 1 relationship

Location to Fact Tables = 1 to many relationship 

Live tables to Fact Tables = 1 to many 

[v-yinliw-msft]

Hi @DeGiacomo7272 ,

For your questions:

1.If you need some interactive calculations between two tables, yes.

2.You can just keep it.

3.Yes, of course you can. But if you want to use slicers, I suggest that it's not necessary to keep the relationship.

Hope these help you.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.