My MSAS cube size is 7GB. The cube build with 2% of aggregations (created
using AS wizard) took 8 hours to run using partitions and running 2
partitions in parallel.

I am struggling now with query performance because I didn't create a lot of
aggregations. I am looking for ways to speed up the queries, but not
increasing too much the build time.

I increased the aggs to 4% and it increased the build time to 15 hours. I
tried to create some custom aggs (216) but this never finished running, I
cancelled after 17 hours because it was still running the 3.o partition.

I have a couple of questions:
1) Is there a way to control the order that the aggregations will be created?
2) If yes to question 1, MSAS will use previous aggregations to create the
other ones or will always go to the base level. For example: My lowest level
is Week, and I created an agg for Month level, then I want to create for the
Year. Is going to use the Month agg or will go again to the week level?
3) On daily basis I will be running only the last 2 partitions (2 Quarters),
so i would like to promote only these partitions (if possible). My
archive/restore of the whole database is taking about 2 hours.

I would appreciate for any help. Thanks

Ercilia

You can't control the order but you can control which aggregates are reated.
In the cube designer, in dimension properties, you can change the
aggregation usage to top level only, bottom level only or custom. If you
have some dimensions that you know aren't going to be queried often,
consider limiting the amount of aggregations that can be performed on the
dimension.


A/S should be smart enough to work that out.

OK, there sounds to be a fundamental problem with the design of the schema
you are building the cube upon, and with the optimisations within your
system.

1) have you used the "optimize schema" tool from the cube design window?
This will reduce the number of joins A/S has to perform to get data from the
underlying database - ideally you can reduce the process to a table scan of
the fact table.

2) Have you changes the server Read-ahead and processing buffers from their
default settings of 4MB? You need to up these depending on the amount of
memory you have available.

3) I am guessing that you have a LOT of dimensions in this cube and that
many of them converge on a single large table (e.g. you have a product table
and all the product attributes are being created as dimensions with Product
at the bottom level). This has the potential for lots of unecessary aggs to
be created and you should consider disabling some of the duplicate product
levels in this case and removing them from the aggregation strategy and cube
visibility. This will also improve query performance.

Either that or you are running this on a 286....

Mick




"Sampa2870" <Sampa2870 (AT) discussions (DOT) microsoft.com> wrote

Answers to your questions:

1) Use of optimize schema
Yes, I already did. No joins, except for the one dimension I am using to
partition the cube.

2) change the default server parameters
Yes, our DBA already tested some numbers and we were able to improve the
cube build in 2 hours.

3) big dimension tables
My dimension tables are not big. I have one table for each
dimension/hierarchy in my cube. For example: Product dimension has 2
hierarchies - one relational table for each hierarchy.

I have 16 shared dimensions. Any comments?

"Sampa2870" wrote:

This is an entire section in the Analysis Services Performance Guide, lots
of good best practices in there.
http://www.microsoft.com/technet/pro.../ansvcspg.mspx

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

"Sampa2870" <Sampa2870 (AT) discussions (DOT) microsoft.com> wrote