Forum Discussion
Best Way for work with Multiple Fact Tables
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
30 Replies
Power Bi allows you to use several fact tables in the same analysis (even with different granualities such as budget vs actual sales) as long as you have common dimension tables. Do not try to link the fact tables directly.
DAX will calculate the measures against the common dimensions and then let you compare the results for the different fact tables in the same analysis
Have a look at this link
http://www.powerpivotpro.com/2012/01/salesbudget-integrating-data-of-different-grains/
I agree, most of your measures will naturally fit a specific Fact table and there will be little benefit to linking Fact tables for most measures.
However you can still create a Measure on FactA that references a calculated measure on FactB if required
This image ilustrate the Linktable
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.
I recommend looking at the link below:
https://powerpivotpro.com/2016/02/data-modeling-power-pivot-power-bi/
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
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.
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
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 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.
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?!
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.
Did you happen to find any help for your question? alexanderg anagnostes
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:
"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
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.
