I'm attempting to add a measure with a distinct count
aggregate function on it. However, after I make the
change, the processing of my cube slows down
dramatically. Before the change, the cube would process
in under one hour. After the change, I've let it run
overnight and it only gets through less than 5% of the
calculations.

I figured the distinct count would slow it down, but
wasn't expecting anything like this! Is there something
I can do to help that performance? I tried placing an
index on that particular column, but to no affect.

Thanks,
-Joe

You are correct. Processing time would increase if the
Cube has got distinct count measure.

It is a good practise to have a separate cube with the
Distinct Count measure and Create a Virtual Cube which
comprises of the Distinct Count Cube and the other Source
Cubes (with non distinct measures) for reporting purposes.

Also look at - OLAP Services: DISTINCT COUNT and Basket
Analysis
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnolap/html/distinct2.asp

Cheers,
Sanka

In addition to reading the whitepaper, one of the common things you can do
to speed things up is to increase the process buffer size. If you start
hitting temp files, the processing will slow down significantly, so the
first thing to try is increasing the process buffer size. Then, you'll
probably want to consider lowering the number of aggregates in the cube to a
small performance value...less than 10%. If you have a good idea of the
aggregates you'll need, then I'd remove all of the aggregates and just
design them by hand using the Parititon Aggregation Utility which you can
download at www.microsoft.com/solutions/bi. If you install the Accelarator
a directory will be created on your hard drive for tools. The utility will
be in that folder.



--
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.

"Sanka" <loonysan (AT) mailcity (DOT) com> wrote