Hello,
I have a DW with ~ 24 milions rows in fact table. When I process a cube: it
work really fast (few minutes) to process data, but after this it tries to
create some aggregations (7), and after 3 jours it is till creating the
first aggregation... During the 3 hours I saw no activity on CPU but it
creates 42 segments on the first aggregation. I don't understand what it
does, why is so long to create aggregations and why the CPU is around 2-3
%... I supposed that it works hard when it creates them...
Thanks

It could be a couple of things, but my guess is you're using distinct count.
Analysis Services keeps all aggregates in memory during processing, provided
it can, otherwise it has to utilize temp files. You should increase the
process buffer size on the server, but more than likely if you're using
distinct count and you're creating that many segments, you're still going to
run into temp file usage.

Some recommendations.

1. Reduce the aggregates in the cube. For distinct count cubes, I probably
wouldn't go over 10% aggs.
2. If you have other measures that are not based on distinct count and
users would query these without needing the distinct count measure, consider
splitting the cubes into multiple cubes and joining them in a virtual cube.
3. Increase the process buffer size to avoid temp file usage. You are
probably still likely to hit temp files though with distinct count.


I'd also recommend the Analysis Services Performance Guide which is
availalbe on Technet. A search should bring it up.

Thanks,

Sean Boon
Microsoft Office BI

This posting is provided "AS IS" with no warranties, and confers no rights.

"Catalin NASTAC" <cnastac (AT) topinet (DOT) com> wrote