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