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

[alexanderg]

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

[itchyeyeballs]

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&ut...

[alexanderg]

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

[Pastor]

Hi,

@alexanderg We are facing a similiar issue in Tabular model. Can you please let me know, if the concept of Link Table worked for you?

Where there any ambiguous results because of Link Table?

Thanks in advance.

[smizgier]

Try concatening them in just 1 big fact table. I used this technique in a model with 5 fact tables and it worked great.

You can add a column with an identifier for each concatened fact table so when you use a formula you can filter in DAX only the rows that met the condition for the fact table you want.

Tell me if it works for you.

Cya

[alexanderg]

thanks @smizgier for your suggestion, at the moment I have two alternatives for this situation, one is that you mention, concatenate tables into one, but I think this technique is most useful when table structures are similar, the other option I am considering is to create a central table containing the fields in common dimension, allowing the joining of two or more fact tables against a common set of dimensions. To be more specific this technique consists of:
1- In the query editor get the fact tables and concatenate the common key dimension fields into one compuond key.
2- create a new aditional table by appending the distinct values from the fact tables. this will be the central table that contain the new compound key with the key dimension fields.

3- in the model, you have to link each fact table and each dimension table to the new central table

it seems that this works...

[mim]

@alexanderg i have to admit when I first read your question( a long time ago), it sounds very strange, I did not get it, now that I have worked a little bit with Qlik, i see where you are coming from, Tabular handle multiple fact tables very gracefully , no need for concatenation or the Link table technique.

[smizgier]

That's like creating a linked, right? it shud works.

Hope it works

[itchyeyeballs]

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/

[Phil_Seamark]

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