Forum Discussion
Best Way for work with Multiple Fact Tables
itchyeyeballs the problem isn't to join fact and dimension tables on multiple fields, as this is solved easily by concatenating the fields, the issue is when there are two or more fact tables that need to join to several dimension tables in common, becasue this generate some inactive relationships, causes to the circular reference. This diagram illustrates more or less what I say
I cant see anything wrong with the layout you propose in the diagram, although I have had similar issues in the past with inactive relationships due to what I assume is a bug. To resolve I had to remove all the tables and reimport them without automatic linking then recreate the links manually.
edit - one other thing to check is that your cross filter direction is set to single
Edit 2 - mine is shown below, all the dimensions are at the top
itchyeyeballs well.. inactive relationships are not a bug, this occurs when there is ambiguity in the direction as must propagate the filter one table to another. In your case you do not have that issue because you use the filter in single direction, but this makes the filter can only go in one direction and there are certain scenarios in which it is necessary that the direction of the filter is set in both directions. At the beginning of a project it may not be necessary to use both direction, but applications and customer requirements can change over time. For this reason for a more scalable model, I prefer to use the filter settings in both directions.
Sorry, I didn't mean that inactive relationships were a bug, what was happening in my case was there seemed to be ghost relationships that I couldn't see or get rid of, PBI kept making my chosen relationships inactive even though there wew no other relationships between the tables.
I conciously avoid having more complex relationships when working with multiple fact tables, even the experts have similar concerns (note about two way relationships half way down the article) http://www.sqlbi.com/articles/the-space-report-under-the-hood/?utm_source=wysija&utm_medium=email&utm_campaign=4-2016
itchyeyeballs Thanks for your replys, are very helpful, So I am going continue investigating about this, because I think there must be some modeling technique to get a star schema, even in complex models.