Lakeshore University: Fixing a Fan Trap and Making 900 Million Rows Fast
Lakeshore University's advancement office has a Power BI semantic model over a Synapse dedicated SQL pool. FactGift holds 900 million gift transactions and FactEventAttendance 120 million rows. DimConstituent has 6 million rows and changes slowly; the warehouse keeps a Type 2 version with 11 million rows. Forty dashboards serve 250 fundraisers; most filter by fiscal year and school. Users report that a matrix of constituent by year takes 90 seconds, and that gift totals double whenever a constituent holds two alumni degrees. The current model is a snowflake of 14 tables with bidirectional relationships on almost every one, plus 300 calculated columns. The capacity is Premium P2 and month-end close is the busiest period. The data team wants to reduce model size and query time without changing the numbers users already trust.
1. Explain why gift totals double and specify the model change that fixes it without breaking existing reports.
Read the worked answer
Root cause
The degree table has two rows per constituent, and the bidirectional relationship lets the filter travel from DimDegree into DimConstituent and then into FactGift. Each gift row is counted once per degree, so a donor with two degrees doubles. This is a fan trap created by relationship direction plus a dimension whose grain does not match the fact.
Fix
- Remove bidirectional cross-filtering. Set every relationship to single direction, from dimension to fact.
- Model degree as a dimension of the constituent, not of the gift: DimDegree relates to DimConstituent, and DimConstituent relates to FactGift on the constituent key. A filter on degree then reaches the fact exactly once per constituent.
- If the business genuinely needs gifts attributed to a degree, define the primary-degree rule and store DegreeKey on the fact at load time. That is a business decision and must be documented, because it changes reported numbers.
Do not just change the measure
Switching to DISTINCTCOUNT(GiftID) hides the duplication but costs performance at 900 million rows and leaves the model structurally wrong. Fix the relationship first.
Validation
Compare SUM of gift amount by fiscal year against warehouse control totals before and after. Also set assume referential integrity on the fact relationships, which lets the engine use inner joins.
2. Design an aggregation strategy that brings the 90-second matrix under ten seconds at 900 million rows.
Read the worked answer
Aggregate design
- Build an aggregate table in Synapse at fiscal year, school and gift type grain with SUM amount, gift count and distinct donor count. Ten years by 40 schools by 20 types is roughly 8,000 rows.
- Import that table into the model and use Manage aggregations to map SumAmount to FactGift[Amount] and Count to FactGift[GiftCount]. Set FactGift to Dual so the engine can choose.
- Power BI rewrites matching queries to the aggregate table, which is typically a 20-100x reduction for the dashboard workload.
Requirements that people miss
- Every slicer column must exist in the aggregate. A missing column sends the visual back to detail, which looks like the aggregation never worked.
- Keep detail rows enabled for drillthrough so users can still reach individual gifts.
- Remove the 300 calculated columns. They inflate memory and can prevent the optimizer from matching queries to the aggregate.
Memory check
At 900 million rows the compressed fact likely fits P2, so an import-mode detail table is viable. If it does not fit, use a composite model: detail in DirectQuery, aggregate in import, with the detail table marked Dual.
Verification
Use Performance Analyzer and check for the aggregate-hit indication, then reconcile totals against the detail-only result.
3. Should the reporting model expose the Type 2 constituent dimension or a Type 1 view? Give the decision rule and the consequences of each choice.
Read the worked answer
Decision rule: what question must the report answer?
- If reports must show attributes as they were when the gift was made - for audit, stewardship history or regulatory reporting - expose Type 2 with surrogate keys. The fact joins on the surrogate key, and the dimension carries IsCurrent. Default every report filter to IsCurrent = TRUE so users see today's view unless they deliberately choose history.
- If users only need current attributes, expose a Type 1 view from the current-record table. The model is simpler and half the rows.
Consequences
- Type 2 gives correct historical attribution but produces a larger dimension and invites double counting if the fact also joins on the business key. Never join the fact on the natural key when a Type 2 dimension exists.
- Type 1 is easy to use but restates history: when a donor's school changes, last year's totals move to the new school. Finance must agree to that explicitly, because it will surface as an unexplained variance.
Recommendation for this case
Publish Type 1 for the 40 fundraiser dashboards - the 11 million row dimension is not a size problem, but the confusion is - and keep a separate historical model for the research team that needs Type 2. Document the restatement rule in the dataset description.