Understanding Associations

[Bindiya]

Hi,

I have 2 tables which have some common fields. Now I want to add filters on those common fields and then when I select the filter values, both the table visuals in my dashboard should show relevant data - something as below.

To achieve this functionality, in data manager if I associate both tables by month_id then it will work fine when I make a selection in month filter alone. But if I make a selection in County filter alone (say I select Japan), then the flow would be  -

1. from table_1 filters for Japan
2. identifies corresponding months - which would be 202301, 202302
3. looks for information related to these 2 months in table_2
4. fetches first 4 records from table_2 which would be incorrect as it fetched USA too from 202301 and 202302 but not Japan from 2023

I tried to create new filed concatenating both month_id and country fields. Then associated 2 tables based on this new field, but would still have the same issue as above.

Joining tables will not be an option because in my actual data there are 3 more dimensions in table_1 which are not in table_2 and I have 5 more dimensions in table_2 that are not in table_1. In that case, joining by those 2 common dimensions (month_id & country) will multiply and create many unwanted rows. Moreover, joining fact tables is not ideal either.

Appreciate any assistance on how to achieve this. Thanks!

[JonnyPoole · Accepted solution]

I think you want this data model for that use case. Then you add other table specific dimensions to each table:

I concatenated Country and Month to form a key. Then I hash it with autonumber() which make the model more optimized at scale. Leave the key and the measure (cnt_1 or cnt_2) in their respective tables and then create a table with the common dimensions plus the key. 

When forming the table with the common dimensions, make sure you load it with all pairs of month and country found in BOTH table1 and table2 and of course keep the key in the common table too. 

I created the sample below by putting the tables in a spreadsheet first. 

Table1:

LOAD

    autonumber(month_id_1 & country_1,'MonthCountryKey') as MonthCountryKey,

    cnt_1

FROM [lib://Community Answers:DataFiles/2434926.xlsx]

(ooxml, embedded labels, table is Table1);

Table2:

LOAD

    autonumber(month_id_2 & country_2,'MonthCountryKey') as MonthCountryKey,

    cnt_2

FROM [lib://Community Answers:DataFiles/2434926.xlsx]

(ooxml, embedded labels, table is Table2);

TempCommonDims:

LOAD

    month_id_1 as Month,

    country_1 as Country

FROM [lib://Community Answers:DataFiles/2434926.xlsx]

(ooxml, embedded labels, table is Table1);

Concatenate (TempCommonDims)

LOAD

    month_id_2 as Month,

    country_2 as Country

FROM [lib://Community Answers:DataFiles/2434926.xlsx]

(ooxml, embedded labels, table is Table2);

CommonDims:

LOAD DISTINCT

Month,

    Country,

    AutoNumber(Month&Country,'MonthCountryKey') as MonthCountryKey

RESIDENT TempCommonDims;

DROP TABLE TempCommonDims;

[pravinboniface]

@JonnyPoole The only thing I may do differently is leave month_id_1&2 and country_1&2 in their respective tables since in the visualization, @Bindiya is showing the values in different tables.

[marcus_sommer · Accepted solution]

Both tables contain the same data and therefore they should be simply concatenated by harmonizing the field-names which means removing the _1 respectively _2 suffixes and adding an extra field Source with the information of table_1 or table_2 which could be then used as dimension and/or selection and/or set analysis condition to differentiate between the sources..

[Bindiya]

Appreciate all your responses. Thank you!

[Bindiya]

In my actual tables, the number of dimensions are different and have only 2 common ones. So I doubted if concatenation would work. 

[JonnyPoole]

Table concatenation might work. The table would be very sparse. I've used that approach in the past if my data volumes get really high and having multiple large fact tables and/or a really large link table starts to cause slowness in the UI.  Then I concatenate everything into 1 sparse fact table that uses more RAM but performs better for users.  Something to keep in your back pocket if you start dealing with 100s of millions of rows and the performance slows down.

[marcus_sommer]

You showed 3 common fields and I could imagine that there are further ones which contain the same data - that the fields have different names doesn't mean that couldn't be merged.

That concatenated tables are not completely synchron to each other is neither technically nor logically a mandatory problem and even if it's not perfect often the best solution and mostly also the easiest one. 

[Bindiya]

That's awesome! I will give it a try. Thank you!