Microsoft
70-466 · Question #9
70-466 Question #9: Real Exam Question with Answer & Explanation
The correct answer is E: Add a measure that uses the LastNonEmpty aggregate function. Use a regular relationship between the time dimension and the measure group.. LastNonEmpty is the correct semi-additive aggregate for stock quantity because it returns the cumulative balance at a specific point in time instead of summing values across all time periods.
Question
You are developing a SQL Server Analysis Services (SSAS) cube. The data warehouse has a table named FactStock that is used to track movements of stock. A column named MovementQuantity contains quantities of stock. A positive quantity is used for input and a negative quantity is used for output. A column named MovementDate is related to the time dimension. The quantity in stock, at a given point in time, can be evaluated as the sum of all MovementQuantity values at that point in time. You need to create a measure that calculates the quantity in stock value. What should you do?
Options
- AUse role playing dimensions.
- BUse the Business Intelligence Wizard to define dimension intelligence.
- CAdd a measure that uses the Count aggregate function to an existing measure group.
- DAdd a measure that uses the DistinctCount aggregate function to an existing measure group.
- EAdd a measure that uses the LastNonEmpty aggregate function. Use a regular relationship between the time dimension and the measure group.
Explanation
LastNonEmpty is the correct semi-additive aggregate for stock quantity because it returns the cumulative balance at a specific point in time instead of summing values across all time periods.
Common mistakes.
- A. Role-playing dimensions allow one dimension table to participate in multiple relationships but do not affect how the measure's MovementQuantity values are aggregated over time.
- B. The Business Intelligence Wizard annotates dimension attributes with standard account or time intelligence metadata and cannot compute a cumulative inventory balance.
- C. The Count aggregate function counts the number of rows in the fact table rather than summing MovementQuantity values, so it cannot calculate the quantity in stock.
- D. DistinctCount counts unique occurrences of a column value and is used for distinct member scenarios, not for deriving a running sum of positive and negative movement quantities.
Concept tested. SSAS LastNonEmpty semi-additive aggregate for inventory balance
Community Discussion
No community discussion yet for this question.