Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jerosmith
Starting Member
1 Post |
 Posted - 2005-09-30 : 12:28:36
|
| Hello all,I have a cube with 17 dimensions, 1 level per dimension. One of the dimensions has 1000 members. When I query the cube from Excel, placing 3 dimensions on one axis, I get a 65 second response time. This is unacceptable for the users.I have tried 50% and 100% aggregation, with no improvement.Any ideas on how to improve perfomance?Please don't suggest removing dimensions; they are all necessary for our corporate solution.I would really appreciate somebody's help.Regards,Jerome Smith |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-10-03 : 07:34:15
|
| You haven't stated what type of equipment you are using....and/or have you identified where the bottleneck is?Can you identify which item is causing the scaling problem...1000 members or the multiple dimensions....have you tested performance levels for smaller datasets/reduced sized models?Could you have a slow network or an overworked server?I think you need to go back and build/determine some metrics so that you can scientifically identify the problem and then gravitate towards a solution. From our point of view your problem as described is too brief to give 'the ultimate' answer. |
 |
|
|
anuj164
Starting Member
49 Posts |
Posted - 2005-10-14 : 15:12:43
|
| whats is your storage type; |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-10-14 : 15:41:20
|
| Are there many dimensions with 1000 members (this is not very high btw) ?Do you have a DISTINCT COUNT measure (that slows performance considerably) ?How many rows in the fact table ?I have never seen any considerable performance improvement after 30% aggregation.For large cubes I usually set it to 10-20%.Small ones max 50%.You could copy/paste the cube and remove some dimensions just to test the 17 dim aspect.If there are many children in the dimension directly after the All level (20+) this tends to slow down the the olap clients,it could be a client problem with presenting a lot of data simuiltaneously.Grouping the dimensions into more levels might help.The 3 dimensions you are talking about with the slow response time, please describe them:__________________________________________________Good algorithms are expensive, hardware is cheaperrockmoose |
|
|
|
|
|
|
Performance Problem with OLAP Cube