Hi,
I know that there is already a post with this question. but I might have additional info and it might be realted to some other problem of mine.
specs:
ssas 2005 sp1,
backend database connects to a UDB(DB2) set of views,
these UDB views get data from many sybase database tables across different databases.
Cube has one partiton per day for one month
the current week partitions are in days,
and prev week partitions are merged to one single partition.
so as of now, for feb we would have the following partitions:
Feb 22 to 28 > 5 partitions (no weekends, 1 partition per day)
Feb 01 to 21 > 1 partition (merged from other days at the end of the week)
3 dimension> date, customer, product
customer and product having about 25 attributes each.
date is a stereotype time dimension with 2 hierarchies, attribute relationships defined
to form natural hierarchies. multiple pkeys for month, quarter.
the customer dimension has 2 natural hierarchies, one user defined un-natural hierarchy,
about 10 attribute relationships defined.
there are about 65 measures, some are derived as calculations from the DSV itself.
in the cube dimensions, the all hierarchy is set to "default" AggregationUsge
the key attributes, and other frequently used attributes including customer name, customer account no
are set to "unrestricted" AggregationUsage.
I processed the dimensions and one of partition,
dimensions contain about 400,000 and 700,000 rows.
the cube contains about 5,000,000 rows.
When I tried to count this using the "count" feature in agg design wizard ,
it gives a system full error after a long time.
so I did a count(*) from the database and gave appx counts.
when I click on create aggregations, after some time it just gives 0 aggregations.
I have tried 500MB of storage for aggregations, then 30% gain and "until I stop" option.
all give only 0 aggregations.
what could be the problem.
before I had designed attribute relationships,
I had tried this and had got about 9 aggregations,
should I not be gettig more now?
please help me not only to rectify this problem
but also to understand the details
Regards
When you run the agg design wizard, which attributes are bold? It only considers the bold ones.
Do you have any user defined hierarchies (as in multi-level hierarchies you built in the center pane of the dimension designer)? Are they natural hierarchies? (i.e. which the lowest level attribute is related to the next lowest all the way up)
The agg design wizard is simply looking at statistics to determine the most useful aggs. Have you considered looking at usage based optimization to use the query log to determine which aggs would be helpful?
Digest the following links and then post back if you still aren't having any luck:
http://www.sqlskills.com/blogs/liz/2006/07/03/InfluencingAggregationCandidates.aspx
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!907.entry
I doubt it will come to it, but the SQL Server Samples that came out with SP2 include a new sample called Aggregation Manager that helps you design your own aggregations. If we can't figure anything else out, that's a last resort.
|||Hi ,
Elizebeth Vitt's blog helped me,
I was able to get about 8 aggregations with 48% optimization.
the aggregations take about 150MB per partition.
how should I achieve a balance between the two?
my requirement is to have maximum performance, the size of the aggregations do not matter. in this case how should I go about designing the aggregations?
Regards
|||
glad that helped
I can't answer that question other to say that you should just try different aggregation designs and test query performance. Designing too many aggs slows performance during cube processing, and it also might have a negative impact on query performance.
Review the performance guide (see link at top of this forum) for best practices in this area, too.
No comments:
Post a Comment