Best Way for work with Multiple Fact Tables

[alexanderg]

Hi everybody!!

which is the best technique for working with models that have multiple fact tables and so get a star schema?. For example, in QlikView there is a technique called "linktable".

Thanks in advance, any comment would be helpful

[Jaaap]

Have a look at https://maxpowerbi.pro/facts-facts-facts-five-ways-of-combining-several-fact-tables-in-a-powerbi-rep...

[michaelSBI]

Thats exactly what i need to do and i also need to do the data schema, but the problem is that i cant create a linked table as I need direct query as my datatables are huge.

[a4apple]

Did you happen to find any help for your question? @alexanderg @anagnostes

[TalAruety]

I had the same situation.

What solved it for me was that i turned all the "Two Way Filtration" on relationships going out of my fact tables.

I eneded up with all relation ships from the dimension tables to the fact tables as one sided filtration: from the one side to the many side.

After that, Slicers from one dimension table filtered both fact tables well.

Only downside of this is that now dimension tables does not filter each other, so i remain with many values to pick from inside my slicers allthough not all are relvant after few filters have been applied to my fact tables.

If any one has ideas how to get that done i will be greatful. 🙂

This post helped me:

https://community.powerbi.com/t5/Desktop/relationship-between-multiple-fact-tables-with-date/td-p/11...

[jprdynamicapps]

"Only downside of this is that now dimension tables does not filter each other, so i remain with many values to pick from inside my slicers allthough not all are relvant after few filters have been applied to my fact tables."

Create a measure in your fact table with countrows.

Then, filter the slicer (using the slicers applied to this visual feature) by that measure being > 0.

-jpr

[richard-powerbi]

Sounds interesting, but do not fully undertstand. Could you explain in more detail? 🙂

[james_m]

I recommend looking at the following link:

https://powerpivotpro.com/2016/02/data-modeling-power-pivot-power-bi/. While link tables can be done, i think it's much easier and better to keep things simple. Hope this helps.

[jprdynamicapps]

@richard-powerbi Here's a good explanation.   

https://www.kasperonbi.com/showing-only-slicer-data-that-have-facts-in-power-bi-and-other-fun-tricks

[anagnostes]

I have a model that worked fine. Now I have to add some new fact tables (Adwords data) and I ended up with circular dependencies that I should solve so I can use the data in the dashboard:

The problems in my new model is something like this:

So what could be the solution to remove the circular dependencies? My issue is that my current model is working now, so i would like to fix the Adwords part without touching the less possible of the Orders part.

Thank you. 

[Chris12]

I'm new to this so take it with a grain of salt...

Seems to me that duplicating the Calendar table would solve your problem, indeed improve your model since the order dates and "adword dates" shouldn't be collapsed?!

[vikrantarora25]

@Chris12 that's not required, if you have 2 or more date columns in same table (like order date & ship date) then you need another date dim table. A single date dim table can be connected to 2 fact tables no prblem with that.

[alexanderg]

 This image ilustrate the Linktable

[james_m]

I recommend looking at the link below:

https://powerpivotpro.com/2016/02/data-modeling-power-pivot-power-bi/

[Anonymous]

Hi @james_m - I have similar requirement, can you please tell me the process how to create the link table with multiple columns from more than 1 tables

[james_m]

Power BI has a new feature to view and work with complex datasets that contain many tables

https://docs.microsoft.com/en-us/power-bi/desktop-modeling-view

[ddumas]

This diagram is strictly to overcome a modeling issue in qlikview and NOT associated with dimensional modelng at the database level, or even with many  BI tools.  PowerBI, Tableau and other tools do not have this modeling issue.  That said, I love QlikView and can model that scenario without the link table.  ALWAYS concatenate facts in qlikview.

[alexanderg]

Certain, @itchyeyeballs @Phil_Seamark you are right, but when working with multiple fact tables that have multiple tables dimension in common, to relate, circular references are generated, resulting in inactive relationships. then I am aware that even the measures I can use the inactive relationships with USERELATIONSHIP function, but do not think that's the best way. I was considering working a technique used in QLIKVIEW called "Linktable". This basically is to create an additional table that centralize the keys of dimensions that have in common the fact tables fields. But I'm not sure if it is the best in Power BI. Perhaps someone with more experience in modeling could clarify this.

[itchyeyeballs]

I'm not sure how you are hitting an issue with circular references, can you post some more specific detail on your model?

Ensure you have common links from the fact tables to the dimension tables you are using and then make 100% sure you are using the dimension tables to populate rows, columns and slicers etc

Using a link table in PBI is not best practice and will fall over if it results in a many to many join (all joins need to be 1 to many)

If you need to join fact and dimension tables on multiple fields (i.e. more than one link between each pair) then you will have to create a compound link field as PBI will only accept 1 active link at a time. For example you could merge "year" and "department" fields into a single field and link on that.

The current model I'm working on has 4 fact tables and 8 dimensions, I have not hit any issues so far

[alexanderg]

@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

[itchyeyeballs]

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