Database Reference
In-Depth Information
Distinct count measure groups
As you learned earlier in this chapter, the DISTINCT COUNT aggregation function
supplies the count of unique values within a fact table. Analysis Services handles
DISTINCT COUNT measure groups somewhat differently than measure groups that
include measures with other aggregation functions.
How to do it...
To implement a distinct count measure, perform the following steps:
1. Right-click on an existing measure group and select New Measure .
2. Change the Usage to Distinct count and choose the column Analysis Ser-
vices , which should be used for counting distinct values.
3. SSDT will create a separate measure group for the distinct count measure.
4. Give the new measure a descriptive name and set the necessary properties.
There's more...
The SQL query for processing a partition using a DISTINCT COUNT measure group
includes an ORDER BY clause to ensure that the data is sorted based on the column
identifying the DISTINCT COUNT measure. Therefore, the data in each DISTINCT
COUNT measure group is sorted based on the DISTINCT COUNT measure column.
Although this may not sound like a huge limitation, it does affect how SSAS resolves
DISTINCT COUNT MDX queries. If you partition the DISTINCT COUNT measure
group by the date dimension attribute (which is the most commonly used approach
for normal measure groups), Analysis Services will have to check every partition for
every query because distinct count data values could be spread across all parti-
tions. This also means that SSAS cannot eliminate any partitions during queries; if
your measure group has 100 partitions, querying each partition for every query can
severely lengthen the query execution time. An alternative approach is to partition
DISTINCT COUNT measure groups based on the DISTINCT COUNT column value.
Doing so effectively serves as a query hint because SSAS only has to check for spe-
cific partition(s) and not others.
For example, the Adventure Works 2012 sample database contains the Internet or-
ders measure group, which is partitioned by year so that the SQL query defining
Search WWH ::




Custom Search