Hi!

I have a cube with ~30 dimensions. When doing storage design for
current year partition with 75% performance gain, I face very legthy
work. After 10 hours I got only 7% performance gain with ~2700
aggregations. These aggregations were processesd in 20 minutes during
cube processing. Microsoft states that I can have up to 128
dimensions per cube and up to 65535 aggregations per partition. So I
am very far from the limits. The fact that makes me most suprised is
that the cube processing takes very little time, compared to storage
design.
What am I doing wrong?

Thanks.

30 dimensions will take some time to design aggregations, but in general I
would say 75% is probably way too high. I'd recommend starting even as low
as no aggregations or maybe 5% aggregations and then use UBO to design the
rest. This topic, and much more about aggs, is covered in
http://www.microsoft.com/technet/pro.../ansvcspg.mspx


--
Sean Boon
Microsoft Office BI

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

"Roust_m" <roustam (AT) hotbox (DOT) ru> wrote

"30 dimensions will take some time to design aggregations"

- Is this time for several days? I could non even reach 8%. I'll
think about UBO, but users want it running fast by the time they get
it. What time it usualy takes to design aggregations for this number
of dimensions and for at least 50% performance gain?


"Sean Boon [MS]" <seanboon (AT) online (DOT) microsoft.com> wrote

It could definitely take days. Consider the possible number of aggregations
is equal to the sum of the product of the number of levels. Then AS needs
to compute tradeoffs between the potential size of the aggregate versus it's
perf value. As that list gets longer, making those tradeoffs means looking
through that entire list.

The reality is that as you add more levels to your cube, the probabability
that an aggregation will support a user's query decreases at a greater rate.
And typically, when you add a dimension, the total number of likely queries
against that dimension, doesn't increase by the amount of potential aggs
that will be created. As you add incrementally add dimensions, that ratio
only gets worse. If you have 30 dimensions, the likelihood that any
aggregation will cover a particular is extremely small. That's why we
recommend starting at 0% and the using UBO. Otherwise, you're just creating
a bunch of aggregates and you can't be sure how many of them are actually
useful (which maximizes processing time without any consideration for the
actual usefulness of the aggs). If you know of specific queries that you
need to optimize for, I'd recommend that you design the aggregates by hand,
or run them through the server making sure they are logged and then use UBO
to design for those specific queries.

-Sean


--
Sean Boon
Microsoft Office BI

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





"Roust_m" <roustam (AT) hotbox (DOT) ru> wrote