70-466 · Question #8
You are designing a SQL Server Analysis Services (SSAS) cube. You need to create a measure to count unique customers. What should you do?
The correct answer is F. Add a measure group that has one measure that uses the DistinctCount aggregate function. Add a calculated measure based on an expression that counts members filtered by the Exists and NonEmpty functions.. Counting unique customers in SSAS requires a DistinctCount measure in its own dedicated measure group, supplemented by a calculated measure using Exists and NonEmpty for accurate cross-dimensional filtering.
Question
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. Add a calculated measure based on an expression that counts members filtered by the Exists and NonEmpty functions.
- GAdd a hidden measure that uses the Sum aggregate function. Add a calculated measure aggregating the measure along the time dimension.
- HAdd a calculated measure that uses the Max aggregate function based on the named calculation.
- 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. Configure the relationship to use different dimension attributes.
- 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 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.
- NCreate 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
(47 responses)- D6% (3)
- F62% (29)
- G2% (1)
- H11% (5)
- I17% (8)
- J2% (1)
Why each option
Counting unique customers in SSAS requires a DistinctCount measure in its own dedicated measure group, supplemented by a calculated measure using Exists and NonEmpty for accurate cross-dimensional filtering.
Role-playing dimensions allow one dimension table to serve multiple relationships in a cube but have no effect on how measure values are aggregated or counted.
The Business Intelligence Wizard applies account or time intelligence metadata to dimension attributes and does not define distinct count aggregation logic.
The Count aggregate function counts all rows including duplicates and therefore cannot return the number of unique customers.
Placing a DistinctCount measure inside an existing additive measure group forces the storage engine to use a degraded scan strategy; DistinctCount measures must be isolated in their own measure group.
LastNonEmpty is a semi-additive aggregate for snapshot or balance measures and does not count distinct dimension members.
SSAS processes DistinctCount measures using a separate storage scan that is incompatible with additive measures, so isolating it in its own measure group is required for correct behavior and acceptable query performance. The additional calculated measure using Exists and NonEmpty functions scopes the count to non-empty member intersections within the current filter context, ensuring the distinct customer count remains accurate when sliced by any dimension.
A hidden Sum measure with time-dimension aggregation is a pattern for cumulative balance scenarios, not for counting distinct customers.
Aggregating a Sum measure along the time dimension produces a cumulative total rather than a count of unique customers.
Adding multiple dimensions to the cube defines dimensional structure but creates no counting logic for unique members.
Linking a cube dimension multiple times to a measure group establishes role-playing relationships and does not produce distinct count results.
Using different dimension attributes in multiple regular relationships creates role-playing behavior but does not aggregate a distinct count.
Setting IsAggregatable to False with a DefaultMember configures a non-aggregatable snapshot dimension, which is unrelated to counting distinct customers.
Using ValueColumn and MemberValue without a direct measure group relationship is a pattern for financial account-type hierarchies, not distinct member counting.
A named calculation for a rolling sum combined with Max aggregation produces a running maximum value, not a count of unique customers.
Concept tested: SSAS DistinctCount isolated measure group for unique member counts
Source: https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/use-aggregate-functions
Topics
Community Discussion
No community discussion yet for this question.