How would you design the data model for a new Power BI sales report?

What the interviewer wants to hear

• Uses a star schema with fact and dimension tables
• Adds a proper date table
• Uses single-direction one-to-many relationships
• Writes measures instead of calculated columns for aggregations
• Keeps the model lean and documents definitions

Model answer

I design it as a star schema. The fact table holds one row per order line with keys, quantity, amount and discount. Around it sit dimension tables: Product, Customer, Store and a dedicated Date table marked as the date table, so time intelligence like year to date works. Relationships go one-to-many from dimension to fact with single-direction filtering, which keeps filter behaviour predictable and fast. I avoid one wide flat table and avoid bidirectional relationships unless there is a clear need. All aggregations, such as Revenue, Margin % and Orders, are DAX measures rather than calculated columns, so they respond correctly to slicers. I remove unused columns and high-cardinality text to keep the model small. Finally I document each measure definition in a description field so business users and other analysts use the same numbers.

More medium Data Analyst questions

• Daily orders dropped 20% yesterday. Walk me through how you would investigate.
• The product team wants to test a new checkout button. How would you design the A/B test?
• What are SQL window functions and when have you used them?
• How would you choose the key metrics for a food delivery app?
• When would you use Excel, SQL or Python for an analysis task?
• What is cohort analysis and when would you use it instead of looking at overall totals?
• Three managers send you urgent data requests on the same morning. How do you decide what to do first?
• Checkout conversion has fallen. How would you analyse the funnel to find where users are dropping off?
• How do you decide what to do with outliers in your data?