Relationships joining on multiple columns | Help and How-To

Tim von Ahsen · 2024-10-10T01:31:31.457000Z

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.QuantitySoldfactSales.CountryNamefactSales.ProductNamefactSales.OrganisationNameRows: [10, USA, Bike, Org1]dimCountry.CountryNamedimCountry.OrganisationNameRows: [USA, Org1], [GB, Org1], [USA, Org2], [GB, Org2]dimProduct.ProductNamedimProduct.OrganisationNameRows: [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.

MoreDiscussions

Relationships joining on multiple columns | Help and How-To | Sisense Community