nerdexam
Microsoft

70-466 · Question #9

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 quantit

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.

Build an analysis services database

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.

How the community answered

(60 responses)
  • A
    12% (7)
  • B
    3% (2)
  • C
    27% (16)
  • D
    7% (4)
  • E
    52% (31)

Why each option

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.

AUse role playing dimensions.

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.

BUse the Business Intelligence Wizard to define dimension intelligence.

The Business Intelligence Wizard annotates dimension attributes with standard account or time intelligence metadata and cannot compute a cumulative inventory balance.

CAdd a measure that uses the Count aggregate function to an existing measure group.

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.

DAdd a measure that uses the DistinctCount aggregate function to an existing measure group.

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.

EAdd a measure that uses the LastNonEmpty aggregate function. Use a regular relationship between the time dimension and the measure group.Correct

LastNonEmpty is a semi-additive aggregate function in SSAS that returns the last non-empty cell value along the time dimension, which correctly represents the running stock balance at any given date when movements are accumulated. A regular relationship between the time dimension and the FactStock measure group ensures SSAS can resolve temporal ordering and evaluate the sum of all MovementQuantity values up to and including the selected time point.

Concept tested: SSAS LastNonEmpty semi-additive aggregate for inventory balance

Source: https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/define-semiadditive-behavior

Topics

#LastNonEmpty#semi-additive measures#stock quantity#time dimension relationship

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-466 Practice