1.5. Reflection Checkpoint

Key Takeaways

• Place every requirement on the pipeline (connect → transform → model → calculate → visualize → share and secure) before choosing a tool; the segment tells you whether the answer lives in Power Query, the model, DAX, the report, or the service.
• Keep the semantic model and the report distinct: models hold data, relationships, measures, and security; reports are visual layers that query them. Shared models plus thin reports give consistency, and composite models let analysts extend a shared model without changing it.
• Shape data into star schemas: fact tables at a single declared grain with keys and numbers, dimension tables with unique keys and descriptive attributes. Flatten snowflakes and reduce high-cardinality columns to help VertiPaq compress.
• Put preparation in Power Query (as far upstream as possible, keeping foldable steps early) and filter-sensitive calculation in DAX measures; reserve calculated columns for values you slice, group, sort, or relate by.
• Read every DAX result through context: row context for calculated columns and iterators, filter context for measures, context transition where they meet, and one-way filter flow from dimension to fact by default.

Connecting Forward

Phase 2 begins the pipeline in earnest. You'll choose connectors and storage modes (including Direct Lake), configure credentials and privacy levels, profile and clean the data, and use Power Query to build the fact and dimension tables this phase described, so that Phase 3's model has clean, well-shaped tables to relate.

Self-Check Questions

• A sales manager complains that the Margin % total on a report doesn't match finance's figure. Using the ideas of grain, measures versus calculated columns, and filter context, list the three things you'd check first and why.
• Your team wants every regional analyst to build their own reports but use identical definitions of revenue and the same security rules. Describe the architecture in terms of semantic models, reports, and where security is defined and assigned.
• A model's refresh against SQL Server has slowed dramatically since someone added steps in Power Query. What concept would you investigate first, how would you check it, and what reordering might fix it?