When creating a multidimensional analytical object, sometimes you might need to use the distinct count measure. Aggregating measures now allow you to calculate the Distinct Count for the field data you are aggregating. For example, to know the number of unique users who buy products of certain type. Since one customer may buy multiple products, the distinct count measure will be useful in such cases.
In case of distinct count field selection, it can be aggregated by ignoring hierarchy or considering hierarchy of the selected dimension field.
Example, in case of a hierarchy as Country > State > City; if cube designer creates a distinct count measure for “City” field and City name is common in two states then at Country level, the distinct count can be aggregated to 1 if “Ignore Hierarchy” check box is checked and can be counted to 2 if “Ignore hierarchy” check box is unchecked.
The below image shows Analytical Object in case the hierarchy is ignored.
Figure 9: Distinct Count Measure
The count of cities with a common name would appear as 1 at the Country level on the report in case the hierarchy is ignored.
Figure 10: Distinct Count Measure example when Hierarchy is ignored
Alternately, the count of cities with a common name would appear as 2 at the Country level in case the hierarchy is not ignored.
Figure 11: Distinct Count Measure example when Hierarchy is not ignored
Distinct Count Measure for Median, Mode and Range aggregate functions
The supported statistical functions to aggregate data are as follows:
- Median- the middle value separating the higher half of the dataset from the lower half.
- Mode- the number which is repeated the most in the dataset.
- Range- the difference between the highest and lowest values.
To achieve this, you have to specify either primary key of the Fact table or a composite key that can uniquely identify a measure value.
This key when combined with the measure values will create distinct measure values.
Figure 11: Primary key for Median/Mode/Range
To calculate the median/mode/range you need to add another new calculated measure with following MDX respectively:
- median([Measures].[Measure Name])
- mode([Measures].[Measure Name])
- range([Measures].[Measure Name])
Figure 12: Calculated Measure with MDX
A sample report showing Median, Mode and Range of product price is given below:
Figure 13: HSV Report showing Median, Mode and Range of product price