Monday, March 12, 2012

open cube to browse takes very long time

I broke up my cube into 24 partitions. There are about 630M total fact rows in that cube.

When I open the cube to browse in BIDS or SQL Management Studio it takes very long time to open (I think 30 minutes).

Profiler does not show that it's running a query, but messages like this keep appearing throughout the time it's opening to browse:

Progress Report Begin, 14- Query, Started reading data from the 'p0' partition.
Progress Report End, 14- Query, Finished reading data from the 'p0' partition.
Progress Report Begin, 14- Query, Started reading data from the 'p10' partition.
Progress Report End, 14- Query, Finished reading data from the 'p10' partition.

and goes on like that....

Looks like you have quite a bit data in your cube. Try designing aggregations for your cube to speed any operations.

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

|||Thank you, yes, adding more aggregations helped opening the cube faster.

A follow up question - based on cube performance in the future is there a way to
Find long running AS queries in trace|||

For that setup a quiery log (http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/config_ssas_querylog.mspx)

And then use "Usage-Based optimization" wizard to tune your aggregations for longest running queries.

HTH

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

|||At starting of browse, the profiler shows this query:

select filter([Measures].members, [Measures].currentmember.member_caption = [Measures].currentmember.member_caption) on columns from [MYCUBE]

After during that query, I see that from each partition SSAS reads from the same aggregation:

(this repeats for each partition)
Finished reading data from the 'Aggregation 3f' aggregation.

This aggregation includes one attribute each from 2 dimensions out of 19 total dimensions.

The query I provided above does not query by any particular dimension.
So my question is, can I create a single specific aggregation in my design which will always ensure that cube is opened for browsing without a huge delay?

(or is it safest to just copy this specific aggregation and make sure it's always included).

The reason I ask is that I want to reduce the number of aggregations in my design to improve processing time, but I don't want to remove aggregations that might be needed to open the cube quickly.|||

While we're on this subject, does anyone from Microsoft know why the cube browser decides to retrieve all the measures with their cell values? Why not just:

select {} on columns,
[Measures].members on rows
from [Adventure Works]

That way I don't think it will actually retrieve the cell value, just the measure names.

|||

While we're on this subject, does anyone from Microsoft know why the cube browser decides to retrieve all the measures?

This is old limitation/design choice of OWC which is used by cube browser. It is unfortunate - I agree.

|||can anyone tell if I can create just one aggregation to make sure the browser can open cube quickly enough?

what kind of aggregation is ideal for that?|||Yes - you can create a single aggregation which will include only non-aggregatable attributes and exclude all aggregatable attributes (assuming you didn't define any default members hitting aggregatable attributes - if you did - you will need to include them as well).|||Hi Mosha,

Yes

- you can create a single aggregation which will include only

non-aggregatable attributes and exclude all aggregatable attributes

What do you mean by a single aggregation? Are aggregatable attributes has the same meaning with aggregation usage?

FYI, I have the similar problem with performance issues. I have a cube with 40 dimensions. One of then has over than 5 millions members. I create aggregation based on attributes commonly used by users (I set the AggregationUsage for the attributes that are usually queried to be Unrestricted). I also make several partitions per month and each partition has about 2,5 million records. When I create aggregation design it created only 12%. I use MOLAP storage mode.

At the first I processed the data for 2 months and it went well. But after I processed the data for 2 years, the performance dropped. When I want to browse the cube, it takes so long. When I checked in the performance monitor, the I/O read was 100% but the memory that the SSAS use was only 2-3 GB (FYI, the server is 64 bit with memory 8GB).

I hope you or somebody else can help me...

Thanks in advance,
A. Imamuddin|||Ashari,

have you tried "Usage-Based Aggregation design". It is supposed to create aggregations based on the actual stored queries and their performance.

No comments:

Post a Comment