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.
