2.4. Reflection Checkpoint

Key Takeaways

• Treat connecting as a contract: pick the most trusted upstream source (shared semantic models first, composite models to extend them), manage credentials and Change Source in Data source settings, and set privacy levels to control data movement between sources, not user access.
• Choose storage mode by where data should live at query time: Import by default, DirectQuery for near real-time or oversized sources (accepting folding, 1-million-row, and feature limits), Dual for shared dimensions in composite models, and Direct Lake for Fabric OneLake Delta tables on a Fabric capacity (only the SQL-endpoint flavor falls back to DirectQuery).
• Parameterize anything that changes between environments: servers, paths, and the exact RangeStart/RangeEnd Date/Time pair that incremental refresh requires.
• Profile before you clean, remembering the 1,000-row default and the distinct-versus-unique distinction; standardize case before removing duplicates; prefer Replace Errors over Remove Errors when rows must be retained; fix step-level errors at the step that hard-codes the broken name or path.
• Shape toward a star schema: correct data types first (Text for identifiers, Fixed Decimal for currency), Unpivot Other Columns for wide sheets, expand semi-structured data with grain in mind, build dimensions by referencing a load-disabled staging query, guarantee unique same-typed keys, merge to add columns, append to add rows.

Connecting Forward

Phase 3 takes these clean fact and dimension tables into the Model view. You'll set table and column properties, define relationships with the right cardinality and filter direction, handle role-playing dates, build a common date table, and then write the DAX measures, from simple aggregations to CALCULATE, time intelligence, and calculation groups, that turn the model into answers.

Self-Check Questions

• A monthly budget workbook adds a new month column each month, uses dd/mm/yyyy dates, and includes a title row and a totals row. List, in order, the Power Query steps that make it refresh reliably as a fact table, and name the step most likely to break if done wrong.
• An analyst must report on the company's certified sales semantic model but add a regional targets spreadsheet related to Date. Which connection approach is required, what permission do they need on the certified model, and what can't they change in the shared model?
• A model reads orders from a Fabric lakehouse via Import and must show changes within minutes while staying fast for 500 users. Compare DirectQuery and Direct Lake for this requirement, and state the capacity and fallback considerations for each Direct Lake flavor.