Creating Multiple Fact Tables with Shared Dimensions in Qlik Sense App for Filtering

[parviz_asoodehfard]

How can I create multiple fact tables in one Qlik Sense app with shared dimensions for filtering, without displaying more than one fact field at a time? My goal is to be able to filter all facts by the common dimensions, but not to create any visualizations or tables with more than one fact field. What steps should I follow to achieve this in Qlik Sense?

I should mention that I don't like to joining two tables on a common field, since could result in a Cartesian product or a large number of duplicates if the common field is not unique.

[marcus_sommer · Accepted solution]

The recommended approach is to create the data-model as star-scheme with just a single fact-table with n dimension-tables. The most tables respectively table-parts should be merged vertically by a concatenation with appropriate harmonizing of the field-names and field-values and the the others with joins and/or often better with mappings.

[parviz_asoodehfard]

May I ask you to explain with this sample:

fact1:
LOAD * INLINE
[dim_a, dim_b, dim_c
John, a, USA
Mary, b, Canada
Peter, c, UK];

fact2:
LOAD * INLINE
[dim_a, dim_b, dim_d, measure_1
John, b, abc,1
Mary, c, def,2
Mary, e, def,4
Peter, d, ghf,3];

when I show fact1 in a table, I see this:

[edwin]

there are at least two ways to model this, the simplest is to concatenate these two facts into one table, but you may want to add a field that makes a disinction between the two data sets:

fact:
LOAD 'fact1' as dataSet, * INLINE
[dim_a, dim_b, dim_c
John, a, USA
Mary, b, Canada
Peter, c, UK];

concatenate (fact)
LOAD 'fact2' as dataSet, * INLINE
[dim_a, dim_b, dim_d, measure_1
John, b, abc,1
Mary, c, def,2
Mary, e, def,4
Peter, d, ghf,3];

if you want to create an expression for the first data set use the set analysis 

sum({<dataSet={'fact1'}>}...

this way you are controlling which data set you wish to use

[marcus_sommer]

Just follow the example from @edwin. It avoids trouble with synthetic keys and/or circular loops, provides the access to all exists data (which could be never ensured by joining/associating tables) and enables also the possibility to differentiate between the sources.

Beside this it's the most simplest and fastest approach to build a data-model and could be applied as general solution. All other ways are more complex and more complicated and are requiring much more knowledge and experience - without any guarantee to get a better working or more performant data-model.