You have the tables shown in the following table. The Impressions table contains approximately 30 million records per month. You need to create an ad analytics system to meet the following requirements: Present ad impression counts for the day, campaign, and Site_name. The analytics for the last year are required. Minimize the data model size. Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

Keeping the detailed Impressions fact table but restricting it to the last 12 months preserves the required grain while a star-schema one-to-many relationship to the Date, Ad, Campaign, and Site dimension tables (B) enables slicing by day, campaign, and Sitename without duplicating data.

Row counts are best returned through an explicit DAX measure that uses COUNTROWS (C); a measure occupies only the formula cache, so it adds virtually nothing to the in-memory model size, meeting the minimisation requirement. Creating additional aggregated or calculated tables would increase memory use and are therefore avoided.

A Grouping into a separate query/table adds another fact table; even if smaller than the raw data, it is still additional storage and not required when a measure can aggregate on demand.

D A calculated table is materialised in the VertiPaq store, duplicating columns already present and therefore increasing, not reducing, model size.

1. Microsoft Docs – “Designing a star schema in Power BI”: Relationships should be one-to-many between dimension and fact tables. (Power BI guidance

p. 3

section “Star schema overview”)

2. Microsoft Docs – “Data reduction techniques for import models”: Use measures instead of calculated tables/columns to minimize model size. (p. 2

paragraph “Prefer measures over calculated columns”)

3. Microsoft Docs – DAX Function Reference: COUNTROWS returns the number of rows in a table; used in measures without increasing model storage. (COUNTROWS article

Examples section)

4. Russo

Ferrari. “Optimizing DAX Models.” SQLBI White-paper

2020

§2.1 “Memory footprint of measures vs. tables.”