Cure to Distinct Counts in SQL AS 2000

So I have had a chance to work with aggregation settings to optimize performance in SQL AS 2000 of distinct counts. So far tweaking the aggregations has gotten me the best performance. Dare I say I have the cure to the distinct count blues in SQL 2000? Well give it a try. You will notice significant cube processing times. Basically here is the rundown:

Aggregations:
1. Start at a lower aggregation level like 15% or even no aggregations level.
(Lower aggregation levels will also save the space needed for the cubes.)
2. Let users query the cubes (also known as Warm Caching). Once the queries are warm and toasty you get incredible query performance even though in step 1 above you created very low aggregations.
3. Use the usage-based optimization (UBO) wizard to build aggregates that support those queries.

Reference:
OLAP Distinct Counts and Performance Analysis

Curse of the distinct count

Distinct counts are a pain in SQL AS.

DISTINCT COUNT
AS2k – based on the dimension structure.
AS2k5 – based on hierarchy and attribute structure. This allows the distinct count measure to be calculated on much larger volumes of data.
Some issues: distinct counts are typically calculated at run-time – i.e. at the moment the user asks the question.

OPTIMIZE DISTINCT COUNTS
The basic run down of these distinct count optimization techniques are to:
· SQL2005 – Create a separate measure group for each distinct count measure
. SQL2000 – Create a separate cube for each distinct count
· Create custom aggregations
· Create a partitioning strategy for your OLAP cube allowing yourself to “distribute” the data.

In my experience, larger data sets of distinct counts are very slow to process even when creating separate cubes. Unless maybe you create partitions. Have not done much with custom aggregations. That’s on my list to do.

More about distinct counts here from Denny Lee.
More on building aggregations manually here from Chris Webb.

Also, look at the Microsoft SQL Server 2000 Analysis Services Performance Guide
for Storage Design Wizard, Aggregation Usage, and Cube Editor.