This is a common Power BI (Data Modeling) interview and practice question. Below is the full answer with a clear, step-by-step explanation.

Question

Problem Your organization has a data warehouse containing the following tables: FactSales (500 Million Rows) FactReturns (35 Million Rows) DimDate DimCustomer DimProduct DimStore DimSalesPerson Business users have reported that the Power BI report takes more than 25 seconds to load and several visuals timeout during peak usage. As the Power BI developer, answer the following questions: How would you design the data model for optimal performance? Would you use a Star Schema or Snowflake Schema? Why? How should relationships be configured? Would you use single-direction or bi-directional filtering? How would you handle multiple fact tables? What techniques would you use to reduce model size? When would you create calculated columns versus measures? How would you optimize DAX performance in this model? Which Power BI performance analysis tools would you use? What additional optimizations would you recommend?

Answer & Explanation

Below is a complete, step‑by‑step guide that shows the best way to model the warehouse for Power BI, why each choice is optimal, and how you can prove it works fast. Read each numbered section as the answer to the corresponding interview question.

Design the data model for optimal performance
• Keep the model as narrow as possible – only the columns you need for reporting.
• Load the two fact tables (FactSales ≈ 500 M rows, FactReturns ≈ 35 M rows) in Import mode whenever the data size fits in the Power BI capacity (the VertiPaq engine compresses numeric columns very well).
• Surround the facts with a set of conformed dimension tables – DimDate, DimCustomer, DimProduct, DimStore, DimSalesPerson.
• Each dimension should contain a single surrogate key (integer) that the facts reference. All descriptive attributes (name, city, product category, etc.) live only in the dimension.
• Do not bring in any transactional or operational columns that are not used for slicing, filtering or calculations.
• If the source system already contains a grain that is finer than you need (for example sales at the line‑item level but the report only ever shows daily totals), create an aggregated staging view in the source or a Power BI aggregation table so the model stores the coarser grain.
Star schema or snowflake schema?
Star schema is the preferred choice.
– A star schema has a single‑level dimension, which means each visual query touches only one join (Fact ↔ Dimension). That minimizes the number of relationship hops Power BI must resolve and gives the VertiPaq engine the most opportunity to compress columns.
– Snowflaking (normalising dimensions into sub‑dimensions) adds extra relationship paths, increases cardinality of the join columns, and forces the engine to materialise more intermediate tables during query execution. In practice this leads to slower DAX and larger model size.
– The only time you would deliberately snowflake is when a dimension is truly shared across many fact tables and you need to enforce a single source of truth for a hierarchy (e.g., a very deep product hierarchy). Even then you can keep the hierarchy in a single dimension and use calculated columns for the parent‑child path.
How relationships should be configured
• All relationships are one‑to‑many with the dimension on the “one” side and the fact on the “many” side.
• Set the relationship direction to single (forward) filter by default. This means filters flow from dimension → fact, which is exactly what you need for slicers, page filters, and visual level filters.
• Mark the primary relationship between each dimension and each fact as Active. If you need an alternate path (for example OrderDate vs ShipDate), create an inactive relationship and activate it in DAX with USERELATIONSHIP.
• Do not create direct relationships between the two fact tables. They should be linked only through the shared dimensions, which guarantees consistent filtering and avoids ambiguous many‑to‑many paths.
Single‑direction vs bi‑directional filtering
Use single‑direction filtering everywhere unless you have a specific requirement that cannot be expressed otherwise (e.g., a many‑to‑many relationship, a role‑playing dimension that must filter both ways, or a calculation that needs the fact to filter the dimension).
Bi‑directional filters cause the engine to evaluate the filter context twice, can create circular dependency errors, and dramatically increase memory pressure. When you do need bi‑directional, limit it to a single relationship and keep the rest of the model single‑direction.
Handling multiple fact tables
• Treat FactSales and FactReturns as independent fact tables that share the same conformed dimensions.
• Because they share the same keys, a slicer on DimDate, DimProduct, DimCustomer, DimStore or DimSalesPerson will automatically filter both tables, giving you a unified view without any extra work.
• If you need a combined metric (e.g., NetRevenue = Sales – Returns), create a measure that adds the two fact measures together; the engine will evaluate the two tables separately and then combine the results, which is far more efficient than trying to union the tables.
Techniques to reduce model size
– Remove unused columns from both facts and dimensions before loading.
– Choose the smallest data type: use Whole Number (Int32) for keys, Decimal Number with appropriate scale for monetary values, and Boolean for true/false flags.
– Replace high‑cardinality text columns with integer surrogate keys (e.g., CustomerCode → CustomerKey). Text columns are the biggest source of memory consumption.
– Disable Auto Date/Time and create a single DimDate table that covers the required range.
– Aggregate historic data: keep detailed rows for the most recent period (e.g., last 12 months) and store older periods at a higher grain (monthly or quarterly). Use Power BI aggregation tables to let the engine automatically switch between detailed and aggregated storage.
– Enable Incremental Refresh so that only new or changed partitions are refreshed, keeping the model size stable over time.
– Compress columns by removing leading/trailing spaces, using consistent case, and ensuring that columns with few distinct values are stored as integers or booleans.
When to create calculated columns vs. measures
Calculated columns are evaluated during data refresh and stored in the model. Use them when:
• The result must be used as a filter, slicer, or relationship key (e.g., Year = YEAR([OrderDate]), CustomerSegment = LOOKUPVALUE(...)).
• The value is needed for sorting or grouping and does not change based on user interaction.

Measures are evaluated at query time and never increase model size. Use them for any business calculation that depends on the current filter context (e.g., Total Sales = SUM(FactSales[SalesAmount]), Return Rate = DIVIDE([Total Returns], [Total Sales])). Measures are also the only way to take advantage of VertiPaq’s columnar compression because they are not stored.
Optimizing DAX performance in this model
• Use variables to store intermediate results so the engine does not recompute the same expression multiple times.
• Prefer simple aggregation functions (SUM, COUNTROWS, AVERAGE) over iterator functions (SUMX, FILTER) whenever possible.
• Filter on dimension tables, not on the fact, because dimension columns are highly compressed and have lower cardinality. Example: CALCULATE([Total Sales], DimProduct[Category] = "Bikes") is faster than FILTER(FactSales, FactSales[ProductCategory] = "Bikes").
• Avoid context transition when you can. For instance, use CALCULATE with explicit filter arguments instead of wrapping a measure inside a row‑context function.
• Leverage the built‑in time‑intelligence functions (TOTALYTD, SAMEPERIODLASTYEAR) which are already optimized.
• Reuse existing measures inside other measures rather than rewriting the same logic.
• Keep the cardinality of filter arguments low – avoid filtering on high‑cardinality columns unless absolutely necessary.
Power BI performance analysis tools you would use
– Performance Analyzer (built into Power BI Desktop) to capture the time each visual takes, the DAX query generated, and the backend storage engine time.
– DAX Studio to run the captured queries, view the query plan, and see metrics such as CPU time, memory usage, and query duration.
– VertiPaq Analyzer (or the “Storage Engine” view in DAX Studio) to examine column cardinality, compression ratios, and identify columns that are inflating the model.
– Query Diagnostics (Power BI Desktop) to see the exact steps Power BI takes to retrieve data from the source, useful when you are using DirectQuery or Composite models.
– SQL Server Profiler / Extended Events if the source is SQL Server and you need to verify that source queries are being folded correctly.
Additional optimizations you would recommend
• Import mode is the default for best performance; only fall back to DirectQuery for tables that are too large to fit or that must be near‑real‑time.
• Create aggregation tables for the most common grain (e.g., monthly sales per store). Power BI can automatically route queries to the aggregation, cutting scan time dramatically.
• Use composite models to keep the most recent month in Import mode and older history in DirectQuery, giving you both speed and freshness.
• Limit visual interactions – turn off cross‑filtering between visuals that are not needed, which reduces the number of DAX recalculations on each click.
• Apply Row‑Level Security (RLS) sparingly and base it on dimension keys rather than on calculated columns; RLS adds extra filter propagation overhead.
• Ensure source tables are indexed on the foreign keys (e.g., DateKey, ProductKey) so that any DirectQuery or query‑folding step runs fast.
• Turn off “Auto Date/Time” and use a single DimDate table; this removes the hidden 400‑plus date tables that waste memory.
• Refresh strategy – schedule incremental refresh during off‑peak hours, and use the “Parallel loading” option in Power BI Service to load multiple partitions simultaneously.
• Monitor the dataset size after each change (File → Options and settings → Options → Current File → Data Load) to verify that you stay well below the capacity limits.

By following this blueprint you will end up with a narrow, highly compressed star‑schema model, single‑direction relationships, minimal use of bi‑directional filters, and DAX that runs in milliseconds. The combination of proper modeling, disciplined column selection, and the use of Power BI’s built‑in performance tools will bring the report load time well under the 25‑second threshold, even during peak usage.