70-466 · Question #10
MISSING STEM for Question 10. The options describe actions.
The correct answer is H. Add a hidden measure that uses the Sum aggregate function.. A hidden Sum measure provides the required base aggregate layer that a subsequent calculated measure can reference when aggregating values along the time dimension in SSAS.
Question
Exhibit
Options
- 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.
- IAdd a calculated measure aggregating the measure along the time dimension.
- JCreate several dimensions. Add each dimension to the cube.
- KCreate a dimension. Then add a cube dimension and link it several times to the measure group.
- LCreate a dimension with one attribute hierarchy. Set the IsAggregatable property to False and then set the DefaultMember property. Use a regular relationship between the dimension and measure group.
- MCreate a dimension with one attribute hierarchy. Set the IsAggregatable property to False and then set the DefaultMember property. Use a many-to-many relationship to link the dimension to the measure group.
- NCreate a dimension with one attribute hierarchy. Set the ValueColumn property, set the IsAggregatable property to False, and then set the DefaultMember property. Configure the cube dimension so that it does not have a relationship with the measure group. Add a calculated measure that uses the MemberValue attribute property.
- OCreate a new named calculation in the data source view to calculate a rolling sum. Add a measure that uses the Max aggregate function based on the named calculation.
How the community answered
(19 responses)- G5% (1)
- H79% (15)
- L11% (2)
- O5% (1)
Why each option
A hidden Sum measure provides the required base aggregate layer that a subsequent calculated measure can reference when aggregating values along the time dimension in SSAS.
A DistinctCount measure group is designed to count unique column values and does not provide a Sum-based foundation for time-dimension aggregation.
A calculated measure using Exists and NonEmpty filters members in a set expression for distinct counting scenarios and is not a mechanism for producing a hidden base Sum aggregate.
In SSAS, marking a Sum measure as hidden keeps it invisible to end-user tools while making it available as a reference for calculated measures. This pattern is used when the raw additive aggregate must exist in the cube's storage layer so that a calculated expression - such as one that aggregates the measure along the time dimension - can build on it without exposing the intermediate value directly.
Aggregating a measure along the time dimension is the secondary calculated step that depends on the hidden Sum measure in H; it cannot function correctly without the underlying base measure being defined first.
Creating multiple dimensions expands the cube's dimensional model but does not define or store any measure aggregation logic.
Linking a cube dimension multiple times to a measure group establishes role-playing relationships and has no bearing on creating a Sum-based measure.
Setting IsAggregatable to False with a DefaultMember and a regular relationship configures a non-aggregatable snapshot dimension, not a base Sum measure for further calculation.
Using IsAggregatable False with a many-to-many relationship addresses fact-to-dimension cardinality and does not create the Sum aggregate layer needed here.
Using ValueColumn, IsAggregatable False, and MemberValue without a measure group relationship is a pattern for financial account-type hierarchies, not for providing a hidden Sum base measure.
A named calculation for a rolling sum combined with Max aggregation computes a rolling maximum rather than the hidden additive Sum aggregate required as the base layer.
Concept tested: SSAS hidden Sum measure as foundation for calculated time aggregation
Source: https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/calculations-in-multidimensional-models
Topics
Community Discussion
No community discussion yet for this question.
