Forum Discussion
Connect same dimensions to multiple fact tables
I have five dimension tables (lets call it DimA, DimB and so on).
I am trying join them to two fact tables (Fact1 and Fact2).
Dim A and Dim B are connected to Fact1 has 1 to many relationship.
However these dimensions cannot connect to Fact2 has 1 to many as we are joining them on a different columns.
Rather many to many if joining them directly or building an intermediary table to make it 1 to many from intermediary table to dimensions and from intermediary table to Fact2.
Neither allows all the dimensions to filter Fact2 at the same time as you would expect due to the limitation of allowing only one bi-direction.
So tried separating out as two different models and used Treatas but that performance has been horrilbe, same goes for inactive realtionship and using use relationship.
I can't combine the Facts into one table as Fact 1 has RLS and Fact 2 does not besides columns being different.
Is there any other option for better performance?
Both fact tables are pretty good size, Fact 1 is 20 million records and Fact 2 is 50 million records.
2 Replies
Hi acbg
I would suggest rather building 2 seperate datasets so that you can have DimA going to Fact1 and DimA going to Fact2
When trying to combine multiple fact tables with multiple dimension tables creates a lot of performance issues as well as potential issues for data to not be accurate if using many - many relationships.
Hi GilbertQ ,
So I did try that and used Treatas to ensure the filtered values are being based through so that when we filter on one dimension (DimA connected to fact1) it filters the other (DimA connected to fact2). Performance was horrible. It does not make sense to display the same column slicers from both DimA tables. Any ideas on how to filter both tables at the same time?
