cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alexanderg
Advocate II
Advocate II

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

29 REPLIES 29
michaelSBI
Frequent Visitor

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
Helper I
Helper I

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:

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

"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

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

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.

anagnostes
Frequent Visitor

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.

alexanderg
Advocate II
Advocate II

 This image ilustrate the Linktable

linktable.JPG

 

Anonymous
Not applicable

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

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

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
Advocate II
Advocate II

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

inactive.JPG

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

 

screen.gif

@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. 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors