50.1 Composite Models: Import, DirectQuery, Dual
Chala mitrano — mix Import and DirectQuery safely; know Dual mode and aggregation hit/miss. Last chapter of this arc — finish strong.
चला मित्रांनो — Import आणि DirectQuery सुरक्षित मिक्स; Dual mode आणि aggregation hit/miss जाणा. या कमानीचा शेवटचा अध्याय — मजबूत संपवा.
चला मित्रों — Import और DirectQuery सुरक्षित मिक्स; Dual mode और aggregation hit/miss जानें। इस चाप का अंतिम अध्याय — मज़बूत खत्म करें।
- Compare Import, DirectQuery, Dual, and Live connection.
- State composite model limits at a practical level.
- Explain Dual mode relationship behaviour.
- Describe aggregation hit vs miss.
- Know when Live connection still wins.
- Sketch a hybrid SQL fact + Import dimension model.
Start with a problem (fictional). Huge SQL fact cannot fully Import overnight. Dimensions are small. Priya builds a composite: DirectQuery fact + Import City/Date, Dual where needed, aggs for City-Day cards. Live connection to a central dataset still wins for some self-serve teams — she documents both paths for Cloud.
1. Storage modes side by side
Why: One word Import is not enough when facts are huge.
What: Import caches data in the model. DirectQuery sends queries to the source. Dual can act as either. Live connects to an already published model/AS.
How — pick with eyes open:
Vertical steps (one under another)
- Small/medium + need speed: Import.
- Huge fact + need freshness: consider DirectQuery.
- Mixed: composite with Dual dimensions often.
- Org already has a certified dataset: Live may win.
- Write the choice on the architecture one-pager.
Tiny example: City and Date Import; Sales fact DirectQuery (fictional MH).
Ravindra Bagale's Tip
Friends, DirectQuery freshness is not free — source must answer quickly.
Ravindra Bagale's Tip – मराठी
मित्रांनो, DirectQuery ताजेपणा मोफत नाही — source जलद उत्तर देईल का.
Ravindra Bagale's Tip – हिंदी
दोस्तों, DirectQuery ताज़गी मुफ़्त नहीं — source तेज़ जवाब देगा क्या।
2. Composite model limits (practical)
Why: Not every feature loves composite.
What: Some interactions, calculations, and source types have limits — verify current docs for your sources.
How — stay practical:
Vertical steps (one under another)
- List sources in the composite.
- Check Training/Cloud for known blocked patterns.
- Test RLS behaviour with DQ.
- Watch query reduction and timeouts.
- Keep a fallback Import extract for critical morning cards if needed.
Tiny example: Timeout on a wild cross-join visual — redesign visual, do not only raise timeout (fictional).
Ravindra Bagale's Tip
Friends, read current Microsoft limits — this lesson teaches caution, not a frozen SKU sheet.
Ravindra Bagale's Tip – मराठी
मित्रांनो, सध्याच्या Microsoft limits वाचा — ही सावधानता आहे, गोठलेली यादी नाही.
Ravindra Bagale's Tip – हिंदी
दोस्तों, वर्तमान Microsoft limits पढ़ें — यह सावधानी है, जमी सूची नहीं।
3. Dual mode and relationship behaviour
Why: Dimensions often set to Dual so they can satisfy both Import aggs and DQ facts.
What: Dual tables can be treated as Import or DirectQuery depending on the query path.
How — place Dual wisely:
Vertical steps (one under another)
- Common dims (Date, City), then Dual.
- Large fact, then DirectQuery.
- Understand relationships still define filter flow.
- Test a card (Import path) and a detail table (DQ path).
- Document Dual choices for the next author.
Tiny example: Date Dual lets City-Day agg Import-hit while detail stays DQ (concept).
Ravindra Bagale's Tip
Friends, Dual is not automatic magic — wrong relationships still hurt.
Ravindra Bagale's Tip – मराठी
मित्रांनो, Dual जादू नाही — चुकीचे relationships अजून दुखवतात.
Ravindra Bagale's Tip – हिंदी
दोस्तों, Dual जादू नहीं — गलत relationships अभी भी चोट पहुँचाते हैं।
4. Aggregation tables with DirectQuery sources
Why: Morning cards should not scan the entire SQL fact.
What: Aggregations can answer summarised queries from Import agg tables; detail misses fall back to DQ.
How — plan hit/miss:
Vertical steps (one under another)
- Define grains users need on home pages.
- Build matching agg tables.
- Manage aggregations mapping.
- Prove hits with tools/Training.
- Accept miss for rare drills to detail.
Tiny example: Home City-Day card = hit; invoice-level matrix = miss to SQL (fictional).
Ravindra Bagale's Tip
Friends, a miss is OK for rare drills — a miss on every home card is a design bug.
Ravindra Bagale's Tip – मराठी
मित्रांनो, दुर्मिळ drill ला miss ठीक — प्रत्येक home card ला miss म्हणजे design bug.
Ravindra Bagale's Tip – हिंदी
दोस्तों, दुर्लभ drill पर miss ठीक — हर home card पर miss यानी design bug।
5. When Live connection still wins
Why: Not every team should own a composite.
What: Live to a certified enterprise dataset gives one version of truth with less local modelling power.
How — choose Live when:
Vertical steps (one under another)
- A central model already has RLS and measures.
- Your team should only build reports.
- Governance wants fewer composite snowflakes.
- You still can use some thin report patterns.
- Document why you did not composite.
Tiny example: Finance self-serve Live to certified Margin model; Ops keeps hybrid composite for SQL fact (fictional split).
Ravindra Bagale's Tip
Friends, Live is governance-friendly — composite is power with responsibility.
Ravindra Bagale's Tip – मराठी
मित्रांनो, Live governance-friendly — composite शक्ति + जबाबदारी.
Ravindra Bagale's Tip – हिंदी
दोस्तों, Live governance-friendly — composite शक्ति + ज़िम्मेदारी।
6. Worked hybrid model + course close
Why: You need one architecture sketch you can draw in interviews.
What: SQL fact DQ + Import/Dual dimensions + City-Day agg + App on shared workspace — then stop and revise chapters 37-50.
How — draw and revise:
Vertical steps (one under another)
- Sketch boxes: SQL, then DQ Sales; Import City/Date Dual; Agg CityDay.
- Note refresh: dims schedule + source latency for fact.
- Note RLS still tested as role.
- Link back to capstone checklist (ch 44).
- Write three interview bullets for composite vs Live.
Tiny example: Course arc close: Service governance (37-44) + DAX/model power (45-50).
Ravindra Bagale's Tip
Friends, end with a drawing — interviewers remember sketches more than jargon.
Ravindra Bagale's Tip – मराठी
मित्रांनो, चित्राने संपवा — interviewers sketches आठवतात.
Ravindra Bagale's Tip – हिंदी
दोस्तों, चित्र से खत्म करें — interviewers sketches याद रखते हैं।
Quiz (5 questions)
1. Import mode mainly:
2. DirectQuery mainly:
3. Dual mode is often used for:
4. Aggregation hit means:
5. Live connection is attractive when:
Practice tasks (12) — hints only, no full answers
- Task. Table Import vs DQ vs Dual vs Live — one line each.
Hint: section 1. - Task. Why source speed matters for DQ.
Hint: section 1 tip. - Task. List two practical composite risks to test.
Hint: section 2. - Task. Which tables often Dual in a star?
Hint: Date City. - Task. Home card hit vs invoice miss — explain.
Hint: section 4. - Task. When do you choose Live over composite?
Hint: section 5. - Task. Draw hybrid MH model boxes (text sketch).
Hint: section 6. - Task. Refresh story for Import dims + DQ fact.
Hint: latency. - Task. Does composite remove need for RLS Test as role?
Hint: no. - Task. Interview three bullets: composite vs Live.
Hint: section 6. - Task. Why agg miss on every home card is bad.
Hint: section 4 tip. - Task. Course close: name two themes from 37-44 and 45-50.
Hint: governance / DAX.
Ravi Sir's cards (Excel card style)
Standalone cards — no tip or photo sections. QR points to this lesson. Download PNG to revise offline.
Think like a professional
In a real tenant, rights and sharing are controlled by Fabric / Power BI admins. Always follow your company policy. This lesson uses fictional people and numbers so you can practise the ideas safely.