nerdexam
Microsoft

70-466 · Question #11

You are creating a SQL Server Analysis Services (SSAS) cube. You need to create a time dimension. It must be linked to a measure group named Sales at the day granularity level. You need to ensure that

The correct answer is H. Add a hidden measure that uses the Sum aggregate function. Add a calculated measure aggregating the measure along the time dimension.. This question tests knowledge of semi-additive measure implementation in SSAS multidimensional cubes. The correct approach uses a hidden base measure combined with a calculated measure to handle non-additive aggregation along the time dimension.

Build an analysis services database

Question

You are creating a SQL Server Analysis Services (SSAS) cube. You need to create a time dimension. It must be linked to a measure group named Sales at the day granularity level. You need to ensure that users can view the Service Quality measures by FirstServiceDate, SecondServiceDate, and ThirdServiceDate. 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.
  • FAdd a measure group that has one measure that uses the DistinctCount aggregate function.
  • GAdd a calculated measure based on an expression that counts members filtered by the Exists and NonEmpty functions.
  • HAdd a hidden measure that uses the Sum aggregate function. Add a calculated measure aggregating the measure along the time dimension.
  • ICreate several dimensions. Add each dimension to the cube.
  • JCreate a dimension. Then add a cube dimension and link it several times to the measure group.
  • KCreate a dimension. Create regular relationships between the cube dimension and the measure group.

How the community answered

(38 responses)
  • A
    8% (3)
  • B
    3% (1)
  • E
    3% (1)
  • F
    21% (8)
  • H
    61% (23)
  • J
    5% (2)

Why each option

This question tests knowledge of semi-additive measure implementation in SSAS multidimensional cubes. The correct approach uses a hidden base measure combined with a calculated measure to handle non-additive aggregation along the time dimension.

AUse role playing dimensions.

Role-playing dimensions allow a single dimension to be reused with different roles, but they do not address the semi-additive aggregation requirement for Service Quality measures along time.

BUse the Business Intelligence Wizard to define dimension intelligence.

The Business Intelligence Wizard defines time intelligence calculations like period-over-period comparisons, not semi-additive measure aggregation behavior.

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

A Count aggregate measure counts rows but does not provide semi-additive behavior for Service Quality measures aggregated over time.

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

DistinctCount counts unique values, which does not address the requirement to aggregate Service Quality measures correctly along the time dimension.

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

LastNonEmpty used directly as an aggregate function with a regular relationship is not the recommended pattern for implementing semi-additive measures and lacks the flexibility of the hidden-plus-calculated approach.

FAdd a measure group that has one measure that uses the DistinctCount aggregate function.

A separate DistinctCount measure group is used to optimize distinct count performance, not to implement semi-additive time-based aggregation.

GAdd a calculated measure based on an expression that counts members filtered by the Exists and NonEmpty functions.

Calculated measures using Exists and NonEmpty are used for set-based filtering, not for implementing semi-additive aggregation along a time dimension.

HAdd a hidden measure that uses the Sum aggregate function. Add a calculated measure aggregating the measure along the time dimension.Correct

Adding a hidden measure with Sum aggregation provides the raw numeric base value, while the calculated measure applies the correct semi-additive logic (such as LastNonEmpty or AverageOfChildren) when aggregating along the time dimension. This two-measure pattern is the standard SSAS approach for measures like Service Quality that should not simply sum across time periods. The hidden measure prevents users from seeing the raw sum while the calculated measure exposes the correctly aggregated value.

ICreate several dimensions. Add each dimension to the cube.

Creating several separate dimensions adds redundant objects without solving the semi-additive aggregation requirement for the measure group.

JCreate a dimension. Then add a cube dimension and link it several times to the measure group.

Linking a cube dimension multiple times to a measure group implements role-playing dimensions for multiple date relationships, but does not resolve the semi-additive aggregation requirement.

KCreate a dimension. Create regular relationships between the cube dimension and the measure group.

Creating regular relationships between a cube dimension and a measure group is standard cube setup, not a solution for semi-additive measure aggregation.

Concept tested: Semi-additive measures with hidden base and calculated measure

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

Topics

#role-playing dimensions#multiple date relationships#measure group#time dimension

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-466 Practice