Hi,

I have a cube built on a star schema with about 3,5M records, it has 16
dimensions, 2 of them have 100.000 members, the rest are quite small. Would
I gain anything by creating aggregations on such a small cube? To do
incremental update I want to define my dimensions as changing, so there can
be no aggregations...

Tore

I am a great believer in the saying "If it ain't broke, then don't fix it".
If you get good performance out of your cube, e.g. sub-second or low-second
response time for queries, then don't change anything. However, if you are
finding that queries slow down as you browse the cube, then read on.

The first point is that you can certainly have aggregations with a changing
dimension. Why would you say "so there can be no aggregations.."? The
overhead is that there will be background processing by the lazy aggregator
when you do your incremental processing. But only 3.5M records, it will
process very quickly. Yes, on a large cube, with many, many millions of
records, the lazy aggergator has scalability issues since it is single
threaded, but that does not imply that changing dimensions should never be
used. As a worst-case, even if you have several changing dimensions, this
does not imply that aggregations cannot be used. Simply set the aggregation
usage property on the changing dimensions to be Top-Level Only or
Bottom-Level Only -- which will force all of your aggregations to be rigid.

Having a high complexity cube like this (with lots of dimensions), means
that aggregations are very important. Without them the system must do all of
its summarization calculations at runtime. While it is true that with only
3.5 million records, this might be reasonably fast, it also may mean that it
isn't (particularly if the application starts to scale above 3.5M. There are
two primary techniques which SSAS provides to improve performance: 1)
aggregations and 2) partitioning. With only 3.5M records, partitioning
doesn't apply here. I would use aggregations. See the section on "Optimizing
Analysis Services to Improve Query Responsiveness" in the SSAS Performance
Guide at:
http://www.microsoft.com/technet/pro.../ansvcspg.mspx

Hope this helps.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Tore Johnsen" <naturamo (AT) hotmail (DOT) com> wrote