PL-300 Prepare the data Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✗

  Add a calculated column extracting hour from TransactionDate in the fact table

  Why it's wrong here

  A calculated column in the fact table that extracts the hour from TransactionDate stores the value as a degenerate dimension: it is just an attribute on the transaction rows. Such a column cannot power a reusable time hierarchy (hour, period, shift) and forces any time-of-day analysis to filter directly on that fact table, which bloats the table and makes cross-measure slicing clumsy. It also recomputes at each refresh and offers no relationship-based filtering or consistent time intelligence across multiple fact tables.

• ✗

  Create a date dimension only and ignore time

  Why it's wrong here

  Building only a date dimension and ignoring time leaves the transaction time buried in the fact table, so you can analyze daily/monthly trends but cannot drill into intraday patterns such as peak shopping hours or morning-vs-evening performance. Because there is no separate time key in the schema, you cannot create a Time hierarchy, and any hour-based report requires either custom DAX or a calculated column, defeating the purpose of a dimension. This option fails the business requirement for hour-level analysis.

• ✗

  Use DAX measures to extract hour on the fly

  Why it's wrong here

  Computing the hour on the fly inside DAX measures—for example with HOUR(MAX(TransactionDate))—means the engine must evaluate and aggregate the time component row by row at query time, eliminating the benefit of pre-joined dimension tables and degrading performance on large fact tables. These measures cannot be reused to drive slicers, filter pane selections, or a coherent time hierarchy, and each measure must replicate the same logic, leading to inconsistent time groupings. It is the least maintainable design and is therefore wrong.

• ✓

  Create a separate Time dimension table with a one-to-many relationship to the fact table

  Why this is correct

  Creating a separate Time dimension table with a TimeKey (for example, an integer 0–86399 representing seconds since midnight) and attributes such as hour, half-hour slot, and time band, then relating it to the fact table via a one-to-many relationship, gives every measure a consistent, reusable way to slice and drill by time of day. This follows star-schema best practice: the dimension stores the descriptive time attributes, the fact only stores the key, and users can navigate from hour to minute or across time periods without custom DAX. It fully supports the retail requirement of analyzing sales by hour while integrating cleanly with the date dimension.

Go deeper

Related to this question