Relationships joining on multiple columns

[Tim]

The query engine didn't work the way I want it to for this example. I want feedback on:
- Have I correctly understood what the query engine is doing?
- Is my solution sensible?
- Why does Sisense create three-way relationships when I want two separate relationships to the fact table?
- Other thoughts/opinions/advice?

---

Model (attached picture might be easier than reading):
factSales.QuantitySold
factSales.CountryName
factSales.ProductName
factSales.OrganisationName
Rows: [10, USA, Bike, Org1]

dimCountry.CountryName
dimCountry.OrganisationName
Rows: [USA, Org1], [GB, Org1], [USA, Org2], [GB, Org2]

dimProduct.ProductName
dimProduct.OrganisationName
Rows: [Bike, Org1], [Laptop, Org1], [Bike, Org2], [Laptop, Org2]

---

My widget queries ([factSales.QuantitySold], [dimCountry.CountryName], [dimProduct.ProductName]).

I expect to see [10, USA, Bike].

Instead, I get [10, USA, Bike] and [10, GB, Bike].

---

The reason is:
When I created the model, I wanted the relationships to be
[factSales to dimCountry on CountryName and OrganisationName] and
[factSales to dimProduct on ProductName and OrganisationName]
Instead, Sisense created
[factSales to dimCountry on CountryName]
[factSales to dimProduct on ProductName]
[Three-way relationship on OrganisationName]
When I run the JAQL Analyzer, it shows that the joins are:
[dimCountry.CountryName to factSales.CountryName]
[dimProduct.ProductName to factSales.ProductName
[dimProduct.OrganisationName to factSales.OrganisationName]
[dimProduct.OrganisationName to dimCountry.OrganisationName]
My widget shows all the countries. I suspect that Sisense decided the path is factSales-->dimProduct-->dimCountry, and that it ignores the factSales-dimCountry relationship when doing so.

---

Solution:
I'll create composite key fields CountryID=OrganisationName+CountryName and ProductID=OrganisationName+ProductName. I'll use those for relationships.

I'll advise my colleagues that dims must never have relationships with other dims (except in parent-child relationships), even as an accidental three-way relationship.

We will continue using the join-all-facts-to-all-dims convention; that results in three-way relationships, but that's ok because there's only one dim in each of those relationships, and we'll never cause Sisense to track a path through one fact to get to another fact.

For livemodels, composite keys are bad, but Sisense doesn't produce three-way relationships, so for a livemodel I would stick to the two-field joins.

[DRay]

Hi @Tim,

This is a pretty complex question. I think it could use a more in-depth review than we can provide in the Community. I would like to get you connected with a technical resource who can get on a call with you. Is it ok if I get that started?

[Tim]

Yes please.

[DRay]

Thank you. I have reached out to your account team to get that process started.