Hi:

I have a cube with multiple partitions. For each 15 minutes interval, a new
partition is created and then processed (using DSO). Each partition contains
new data which is different than all previous partitions. I have
aggregations designed which suppose to happen across those partitions; but
the aggregation is just not happening; we knew that because the query
response is VERY slow (minutes). If aggregation did happen, it would take a
few seconds. I did not do a full cube processing at each interval, that is
the thing we try to avoid; we design in such a way in the hope that the
aggregations designed for cross partitions will AUTOMATICALLY be taken care
of by such design.

Could you give your opinion about this case? Basically, is it "By Design of
the AS" that there is no aggregation across multiple partitions? If not that
case, do you see anything I might do wrong?

Thanks for your info

First of all, you need to check whether the partions you're creating
actually do have any aggregations. In Analysis Manager, right click on the
partition and select 'Design Storage' - after the Welcome screen you should
get a screen saying 'Aggregations Already Exist' which lets you know how
many there are and other details. If this screen doesn't appear, then I
guess there's something wrong with the code you're using to create the new
partitions.

If there are aggregations and performance is still slow then you need to
investigate other possibilities. One common thing that people forget is
setting the data slice property on partitions, and that can have a big
impact on performance for a large cube. Check out the excellent Analysis
Services performance tuning white paper on Technet for more details on this
and other related topics:
http://www.microsoft.com/technet/tre...e/ANSvcsPG.asp

HTH,

Chris

--

Chris Webb
Consultant, Microsoft Services Switzerland
(to email: remove 'online.' from the address this was posted with)

Microsoft Services Switzerland and our partners can help you with your
Analysis Services project!

Disclaimer: This posting is provided 'AS IS' with no warranties, and confers
no rights.

"Polaris" <etpolaris (AT) hotmail (DOT) com> wrote

Adding to Chris' comments....

You can also check the aggregations on each partition by using either the
Partition Aggregation Utility (downloaded with the SQL Server Accelerator
for BI at www.microsoft.com/solutions/bi) or if you already have the SQL 2k
resource kit, there is an earlier version of the tool with that that will
allow you to view the actual aggregations in a partition. Typically we
recommend that you use a single partition that has no data to "hold" your
aggregation design and when you create your new partitions, you clone it off
of this "base" partition.

And as Chris stated, very often we see that the data slice property has not
been set for partitions. This can have a huge impact on query performance.

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.





"Chris Webb [MS]" <chwebb (AT) online (DOT) microsoft.com> wrote