Data modeling basics
Explain the purpose of a star schema: fact tables store measurable events, while dimension tables provide descriptive context. A clear model simplifies DAX, improves performance, and reduces ambiguous filtering.
- Identify the grain of every fact table.
- Use stable keys to connect dimensions.
- Avoid combining unrelated business processes in one fact table.
Relationships
Discuss cardinality, filter direction, active versus inactive relationships, and the risks of many-to-many designs. Be prepared to explain how a filter travels from a dimension to a fact table.
Power Query and data preparation
Power Query is used to connect, profile, reshape, combine, and clean data before it enters the model. Explain the value of repeatable transformation steps, appropriate data types, query folding when supported, and separating staging queries from final model tables.
- Remove unnecessary columns and rows as early as practical.
- Use clear step names and preserve traceability.
- Avoid hiding important business rules inside unexplained transformations.
Measures versus calculated columns
Measures are evaluated in filter context and are usually preferred for aggregations. Calculated columns are stored during refresh and are useful for row-level attributes. Choose based on the required behavior, model size, and refresh cost.
Total Revenue = SUM(Sales[Revenue])\n\nRevenue per Customer =\nDIVIDE([Total Revenue], DISTINCTCOUNT(Sales[CustomerID]))DAX basics
Review filter context, row context, CALCULATE, iterators, DIVIDE, time intelligence, and variables. Explain the business definition before presenting a formula.
Revenue Prior Year =\nCALCULATE(\n [Total Revenue],\n SAMEPERIODLASTYEAR('Date'[Date])\n)Filter context and CALCULATE
A common interview topic is how slicers, visual rows, relationships, and DAX modify filter context. CALCULATE evaluates an expression under changed filters. Explain the context in plain language before attempting a complex measure.
Enterprise Revenue =\nCALCULATE(\n [Total Revenue],\n Customer[Segment] = \"Enterprise\"\n)Date tables and time intelligence
Reliable time intelligence usually requires a continuous date table related to the relevant fact date. Discuss fiscal calendars, incomplete periods, multiple business dates, and when an inactive relationship may be activated in a measure.
Dashboard design
Start with the audience and decision. Use a clear visual hierarchy, consistent definitions, accessible colors, useful comparison periods, and limited interaction. Avoid decorative visuals that compete with the main message.
KPI reporting
A KPI needs a definition, owner, target or comparison, refresh schedule, and interpretation. Explain how you would distinguish a meaningful movement from normal variation or incomplete data.
Refresh, gateways, and data freshness
Know the difference between Desktop authoring and Service refresh. Be prepared to discuss credentials, scheduled refresh, on-premises gateways, refresh failures, and how users can see when data was last updated. Do not claim real-time reporting unless the architecture actually supports it.
Row-level security and sharing
Row-level security restricts model data based on user roles; workspace and app permissions control access to content. Explain that security should be tested as the intended user and should not rely only on hiding report pages or visuals.
Performance and maintainability
Prefer a clean star schema, reusable measures, appropriate data types, reduced model size, and limited high-cardinality columns. Use Performance Analyzer and model diagnostics to investigate slow visuals instead of guessing.
- Avoid unnecessary calculated columns and bidirectional relationships.
- Reduce the number of visuals and expensive interactions on a page.
- Create a dedicated measures table and document important definitions.
Explaining insights to stakeholders
Summarize what changed, the strongest supported driver, the expected business impact, and the recommended next step. Clearly label assumptions and avoid implying causation when the analysis only shows association.