Shrinking a Power BI model for monthly transaction totals with a star schema

Design and implement a data model Transform and load the data
Answer Correct answer: A, B, D — star-schema relationships, dropping TransactionID, and monthly grouping deliver the totals at minimal size.

You have a Power BI semantic model that contains the following data. You need to create a solution that meets the following requirements: • Presents transaction amount totals for each month for the current and prior year • Follows the star schema modeling approach • Minimizes the data model size Which three actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point. - image

  1. Create one-to-many relationships between the tables. Correct Answer
  2. In the Transactions query, delete the TransactionID column. Correct Answer
  3. In the Date query, group by MonthNumber.
  4. In the Transactions query, group by AccountID and MonthStartDate for TransactionDate. Aggregate by summing the TransAmt column. Correct Answer
  5. In the Transactions query, add a column that contains the MonthStartDate value for TransactionDate.
power bi star schema modeling reduce semantic model size pl-300 data transformation

Community Votes

ABD
57%
ADE
43%

57% of anonymous learners picked answer ABD. Votes are pick records left by other test-takers — they are not the verified answer.

Community Insight

Star-schema relationships plus dropping the high-cardinality TransactionID column cut size without touching reporting capability, and grouping Transactions by account and month start date in Power Query collapses detail rows into the exact grain the monthly totals need.

A semantic model with Date, Account, and Transactions tables must show monthly transaction totals for the current and prior year, follow a star schema, and minimize model size, with three actions to choose.

Adding a MonthStartDate column to Transactions as a separate step — the grouping operation can derive MonthStartDate directly from TransactionDate, and a standalone added column works against the minimize-size requirement.

Community Discussion (5 comments)

kai_data 👍 1 Selected: AB
Selecting D without E, makes no sense as transactions query have no MonthStartDate to aggregate on. But E indeed increase the size of the model.
5bf040d 👍 2 Selected: ABD
I believe E is not correct because adding MonthStartDate to the Transactions table would increase the data size, and this information is already available in the Date table.
32c0a0b 👍 2 Selected: ADE
I'll go for ADE, As with out creating a start month date column we cannot group or aggregate the table: So its: 1) Create Start month date column 2) Group or aggregate 3) create relationships
SylUK 👍 2 Selected: ABD
A B D are the correct answers . E and F go against the requirement .
BIT2024 👍 1 Selected: ADE
for me is ADE

Comments & Corrections

No comments yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Submitted for moderation before publishing. Keep it helpful and respectful.

Expert Analysis

Why the Answer Is Correct

One-to-many relationships (A) from the dimension tables into Transactions are the backbone of a star schema, letting month and account attributes slice the facts. Deleting TransactionID (B) removes a high-cardinality column that no monthly report needs, directly shrinking the model. Grouping the Transactions query by AccountID and MonthStartDate derived from TransactionDate, aggregating the sum of TransAmt (D), folds daily detail rows into one row per account per month — the precise grain required for current- and prior-year monthly totals, and a massive reduction in fact table size.

Why the Other Options Are Wrong

Grouping the Date query by MonthNumber (C) would break the date dimension's daily grain, destroying the ability to relate and filter by real dates while saving almost nothing, since a Date table is tiny. Adding a MonthStartDate column to Transactions (E) as an additional step contradicts the minimization goal: the grouping in D can derive the month start from TransactionDate directly, and a standalone column merely enlarges the fact table for no benefit the grouping does not already provide.

Community Comment Notes

The most-endorsed voting bloc backs A, B, and D, with one comment arguing that E would increase data size since month-start information already exists in the Date table. A dissenting bloc supports A, D, E on the grounds that D needs a MonthStartDate column to group on; that overlooks the wording 'MonthStartDate for TransactionDate,' which lets the grouping derive the value from TransactionDate directly.

Official Reference

https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

Exam Strategy

To minimize model size while maintaining a star schema, always prioritize aggregating fact tables at the required reporting grain rather than storing daily detail. Identify and remove high-cardinality surrogate keys that are not needed for slicing or filtering to immediately reduce memory footprint.

Frequently Asked Questions

Why delete TransactionID in Power BI?

Deleting high-cardinality, unused columns like TransactionID reduces row count memory usage without affecting monthly aggregation needs.

Can I group by MonthStartDate directly?

Yes, you can derive MonthStartDate from TransactionDate within the grouping step to avoid adding extra columns that increase model size.

Related Analysis

You have a Power BI semantic model that contains item, price

S-Grade

You have a Power BI report that contains a bar chart. The ba

A-Grade

You have a Power BI model that contains two tables named Sal

S-Grade

Practice All PL-300 Questions

Access 116 questions with complete answers and detailed explanations.

View Full PL-300 Practice Test →

← Back to PL-300 Study Guide