Which three actions create monthly star-schema totals with minimal model size?

Design and implement a data model Transform and load the data Optimize model performance

Answer Correct answer: A, D, E — Add MonthStartDate, group Transactions by AccountID and MonthStartDate summing TransAmt, then create one-to-many relationships to Date and Account.

You have a Power BI semantic model that contains the following data. exhibit 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.

Create one-to-many relationships between the tables. Correct Answer
In the Transactions query, delete the TransactionID column.
In the Date query, group by MonthNumber.
In the Transactions query, group by AccountID and MonthStartDate for TransactionDate. Aggregate by summing the TransAmt column. Correct Answer
In the Transactions query, add a column that contains the MonthStartDate value for TransactionDate. Correct Answer
Create many-to-many relationships between the tables.

Community Insight

Tests star-schema aggregation in Power Query: the trap is assuming adding MonthStartDate bloats the final model when it is actually the intermediate key that option D groups on, making option B redundant.

This PL-300 question asks for three actions that produce monthly transaction totals for current and prior years while preserving a star schema and minimizing model size. The correct approach is A, D and E: derive MonthStartDate, aggregate the fact table to that monthly grain, then relate it one-to-many to Date and Account.

Picking B instead of E: deleting TransactionID removes a column, but it does not create the monthly grain; option D cannot group by MonthStartDate until E adds it.

Community Discussion (11 comments)

e68b25f 👍 1 Selected: ADE

Because option B is redundant if you perform D. B. Delete TransactionID only removes the column. You still have 3 rows. So D already eliminates TransactionID and reduces the number of rows.

Sana92 👍 1 Selected: ABE

D. Group by AccountID and MonthStartDate in Transactions This is pre‑aggregating the fact table, which: Breaks the star schema grain Prevents detailed analysis later Is NOT required to reduce model size (you lose flexibility) PL‑300 exams avoid this unless explicitly required. E. In the Transactions query, add a column that contains the MonthStartDate value for TransactionDate. This is essential for: Grouping transactions by month Joining to the Date dimension at the month granularity Supporting current year vs prior year comparisons You need a MonthStartDate key to aggregate properly.

Mi4as 👍 4 Selected: ADE

A-E-D. Point D will automatically do point B. Everything that is not in the group will be removed (transaction ID and transaction date), never listen to GPTchat and people without checking yourself. Impotent. Point D is impossible without point E. We need a column to aggregate data on the first day of the month and reduce the model size.

omath 👍 3 Selected: ABD

Answer: A,B,D A. Create one-to-many relationships between the tables - In a star schema, fact tables (Transactions) connect to dimension tables (Date, Account) via one-to-many relationships. - This is the canonical design pattern for Power BI semantic models. B. In the Transactions query, delete the TransactionID column - TransactionID is a high-cardinality, surrogate key that is not needed for aggregations. - Removing it reduces model size significantly without affecting reporting requirements D. In the Transactions query, group by AccountID and MonthStartDate for TransactionDate. Aggregate by summing the TransAmt column - This reduces the row count in the fact table by pre-aggregating to the monthly level. - Since the requirement is only monthly totals (not daily transactions), this is the best way to minimize model size.

Lulu_2022 👍 2 Selected: ABE

This are the correFirst, you should create one-to-many relationships between the tables. This aligns with best practices for building a star schema — the fact table should link to dimension tables via one-to-many relationships for optimal performance and clarity. Second, in the Transactions query, you should delete the TransactionID column. Since you're not analyzing or filtering based on TransactionID, and it's likely a high-cardinality field, removing it helps minimize model size and improves performance. Third, still within the Transactions query, you should add a new column that contains the MonthStartDate value for the TransactionDate. This helps group transactions by month and prepares your data for efficient time-based analysis, like comparing this year to last year.

Trixy 👍 1 Selected: ADE

I would do in this order E -> D -> A. Add MonthStartDate to Transactions query -> aggregate Transactions query by AccountID and MonthStartDate (TeansactionID will need to be excluded in this step) -> set up one-to-many relationship between Transactions and Account on AccountID column and Transactions and Date on MonthStartDate column.

kai_data 👍 2 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 👍 4 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.

Expert Analysis

Why the Answer Is Correct

Option E is the necessary first step: it derives MonthStartDate from TransactionDate so every transaction can be assigned to a month. Option D then groups the Transactions query by AccountID and MonthStartDate and sums TransAmt, collapsing daily rows into a much smaller monthly fact table. Option A completes the star schema by creating one-to-many relationships from the Date and Account dimensions to this aggregated Transactions table. The final model retains neither the original TransactionID nor the daily TransactionDate, so E does not leave extra bloat and B becomes redundant.

Why the Other Options Are Wrong

Option B alone only removes TransactionID; the fact table would still contain one row per transaction date, so it does not achieve the required monthly grain or the largest size reduction. Option C groups the Date table by MonthNumber, which collapses different years into the same month and destroys the ability to compare current and prior year totals. Option F is wrong because a many-to-many relationship is not the star-schema pattern and introduces ambiguity and slower filtering. A star schema here needs one-to-many links from dimensions to the aggregated fact table.

Community Comment Notes

Mi4as argued that D automatically handles B, noting that grouping removes unneeded columns, and stated "Point D is impossible without point E." That captures the dependency: the Transactions table needs MonthStartDate before it can be grouped at monthly grain. 5bf040d worried that E would increase the model size, but after aggregation MonthStartDate is the key that replaces the daily date, not an extra retained column. Trixy described the practical order as E then D then A, which matches the logical build sequence.

Official Reference

Exam Strategy

Treat each selected action as a dependency in the solution rather than an isolated optimization: E creates the month key, D uses it to shrink the fact table, and A wires the result into the star schema. Do not choose B just because deleting a column sounds size-friendly when D already removes that column and many more rows.

Frequently Asked Questions

Why not just delete TransactionID instead of grouping by MonthStartDate?

Deleting TransactionID removes only a column; daily transaction rows remain. Grouping by AccountID and MonthStartDate both shrinks the fact table and drops TransactionID automatically.

Why is grouping the Date table by MonthNumber wrong for prior-year totals?

It collapses multiple years into one month number, so the Date dimension cannot distinguish current-year from prior-year values or support proper time intelligence.

More PL-300 FAQ →

Related Analysis

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