Are aggregations really needed for a cube with 1 measure (distinct
count)? We have been told that aggregations WILL and WILL NOT improve
query time for DISTINCT COUNTS.

Cube Specs:
Fact rows - 8,000,000
Dims - 28
Measures - 1 (distinct count)
Aggs - 2% (67 aggregations)
Processinging time - 65 minutes

This would not be a problem if it was only 1 partition. We have 40
partitions.

Whereas, we have a duplicate cube with SUM measures and the specs are:
Fact rows - 8,000,000 (same as above)
Dims - 28 (same as above)
Measures - 8 (all SUMs)
Aggs - 3% (580 aggregations)
Processing time - 17 minutes

Thanks

Aggregations can help distinct count queries, but you'll want to be
selective in which ones you use. I would recommend that use Usage Based
Optimization to design those aggregations or add them manually via DSO using
a tool like the Partition Explorer that ships with the SQL 2k resource kit.

All other things being equal, distinct count aggregations are larger and
take longer to process than non distinct aggregations. One of the things
you can check on the distinct count cube would be the creation of temp files
during processing. You can monitor that with either perfmon counters or a
tool like filemon. Analysis Services keeps all aggregations in memory
during processing and since distinct count aggregations tend to be larger
than non distinct aggregations, it's very possible that you're using temp
files to process the partitions that use distinct count. The best ways to
keep that from happening are to

1. Only create aggregations that you need. Again, use UBO or design them by
hand.
2. Allocate more memory for Analysis Services and/or consider 64-bit.
3. Reconsider the partitioning strategy so that the aggregations will be
smaller.


--
Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.





"Mike" <mike.major (AT) marriott (DOT) com> wrote

if 1 of the dimensions used the same level of the distinct count related
column, then optimise this dimension at the bottom level only

For example, when you count the number of customers and if you have a
customers dimension , then optimize only the lowest level.
instead, try to optimize only the upper level of your dimensions.


"Mike" <mike.major (AT) marriott (DOT) com> a écrit dans le message de
news:bf990ac.0310031243.129b88ac (AT) posting (DOT) google.com...

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.522 / Virus Database: 320 - Release Date: 2003-09-29