27.4 Relationships
Start with a problem
A report slowed down after Raja set all relationships to Both directions.
Explain simply
Prefer one-to-many, single direction relationships in a star schema. Both-direction and many-to-many add work and ambiguity.
Show the data
| Relationship | Direction |
|---|---|
| City[City], then Orders[City] | Single |
| Date[Date], then Orders[Order Date] | Single |
About this data: This table uses columns: Relationship, Direction. This table uses columns: Setting, Matrix time (sample). This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).
Fictional sample data. Practice file: visuals_orders_apr_may_2026.csv (source: src/practice/).
Demonstrate
- Model view › open each relationship.
- Set Cross-filter direction Single unless needed.
- Re-run Performance Analyzer.
Show the result
Your result should look like this (fictional sample):
| Setting | Matrix time (sample) |
|---|---|
| Both | slower |
| Single | faster |
Explain what happened
Single direction gives one filter path and simpler queries.
Explore further
- Use CROSSFILTER in a measure for one-off needs.
Detailed reference for this lesson:
- Use one-to-many, single-direction relationships in a star schema.
- Avoid bi-directional cross-filtering: it makes the engine propagate filters in more directions, can create ambiguous (संदिग्ध) paths and slows queries. Use
CROSSFILTERin specific measures when needed. - Avoid many-to-many relationships where a bridge table or a proper dimension would work.
- Avoid long snowflake chains – flatten dimensions.
Ravindra Bagale's Tip
Friends, many students switch on bi-directional filtering and many-to-many relationships to "make things work", and performance drops. Keep single-direction, one-to-many relationships on a star schema wherever possible, and use bridge tables deliberately. Try it once more, then move on.
Ravindra Bagale's Tip – मराठी
मित्रांनो, बरेच students "काम चालावं" म्हणून bi-directional filtering आणि many-to-many relationships चालू करतात, आणि performance घसरतो. शक्य तिथे star schema वर single-direction, one-to-many relationships ठेवा, आणि bridge tables विचारपूर्वकच वापरा. पुन्हा एकदा करून बघा, मग पुढे जा.
Ravindra Bagale's Tip – हिंदी
दोस्तों, बहुत से students "काम चलाने के लिए" bi-directional filtering और many-to-many relationships चालू कर देते हैं, और performance गिर जाता है. जहाँ हो सके star schema पर single-direction, one-to-many relationships रखो, और bridge tables सोच-समझकर ही इस्तेमाल करो. एक बार फिर करके देखो, फिर आगे बढ़ो.
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Ambiguous path error | Many Both relationships | Single direction |
| Slicer shows unrelated values | Expected with single | Use measure filter if needed |
| Many-to-many on City | Duplicate keys in dimension | Clean dimension |
Practise independently
Practice task
Default direction for dimension, then fact?
Hints
- Star schema.
Solution
Single, one-to-many.
Think like a professional
Star schema + single direction is the safest default.
Revision card