Slow Cube Performance in Excel

Slow Cube Performance in Excel

• danni123

  Good luck. Another advice is to run the same MDX-query in Management Studio.

  Regards

  Thomas Ivarsson

  
  

• Amjath

  Thanks for the replies guys. To answer your points individually.

  1. I obtained the connection string by copying the connection value for the pivotcaches object that Excel generates when creating a pivottable based on a cube. I have tried removing the parts you suggest, but it doesn't seem to make much difference. I read somewhere else that adding 'Cache Policy = 7;Cache Ratio = 0.001' but this didn't help either.

  I also ran the MDX generated by Excel directly in SQL Studio, and the performance was also poor. This would seem to indicate that it is the vastly different MDX that is causing the problem, and not the connection string at all.

  Moving over to Reporting Services is definitely something we want to do, but recreating these complex spreadsheets could take some time as I have hardly used the package at all. I have heard that there are some third party add ins for Excel specifically designed for OLAP reporting. One is called XLCubed. Would any of these tools help my situation i.e would they produce their own MDX or would it still be generated by Excel

  2. I will certainly try Excel 2007 (if I get the I.T department to let me install it!). I am not actually using any calculated fields in my cube so would changing 'AddCalculatedMembers' affect me in any way I am also unsure of what you mean by NONEMPTY. How would this impact performance, and how would I go about doing it

  Many thanks,

  Dave

  
  

• prasad_8104

  Thomas,

  I have requested a demo version of Proclarity and am planning to try out Excel 2007 this weekend. Thank you very much for all your advice.

  Dave

  
  

• ikarkaroff

  As far as considering alternatives, you may also want to try Excel 2007 Beta 2, the MDX it generates is quite different from Excel 2003.

  You can also change some settings in Excel 2003 that will result in slightly different MDX, but you don't have much control here (mostly it's some small tweaks you may be able to achieve, e.g. turning off AddCalculatedMembers can be done through VBA). You can also get rid of NON EMPTY.

  
  
  

• Jeroen ter Heerdt

  Hi David:

  The performance difference between Excel and BI Studio comes from the MDX which each tool issues for your query. You can see the MDX generated by each tool (BI Studio, and Excel) is radically different. The biggest differences between the MDX, which is probably causing the slower response in Excel, is likely the use of CrossJoin() and Hierarchize().

  The connection string may have an impact on performance, but it's hard to say because you have a lot of detail. How did you come up with the connection string Was it generated by Excel, or did you handcraft it If you hancrafted it I recommend removing the following to see if it affects performance: (1) Mode=ReadWrite... do you need readwrite access (2) Auto Synch Period = 20000 (3). If you didn't handcraft the connection string then you're at the mercy of the the tool which did create it.

  To determine if the connection string has an impact on query performance you can take the MDX generated from Excel and exercise it within the SQL Manager as an MDX command. How is performance My bet is that the MDX from Excel has about the same execution performance as when run from Excel.

  Finally - I think you're between a rock and a hard spot because the tools generate the MDX and you don't have control over it. You may want to consider moving to Reporting Services to generate the report you're creating in Excel. In Reporting Services you can override the auto-generated MDX with your own so you can tune for performance. However, I don't know your reporting requirements so moving away from Excel may not be an option.

  PGoldy

  
  
  

• aguess

  Thomas,

  To pick up on the subject of aggregations:

  I am pretty confident that my attribute relations are correct, but i'll run them past you. For dimsenions with no user hierarchies, I just have a one to one relation between the key and it's lookup value. For those with user hierarchies, I have one to many relations between each attribute and the one above it in the hierarchy. Does this sound correct Regarding the rigid/flexible setting, I noticed that all my relations were set to flexible. Are you saying I should change these to rigid How will this affect the aggregations and performance

  I have also altered the AllMemberIsAggregatable property for each dimension used as a page filter to 'Unrestricted', and my time dimension to 'None'. This is because my spreadsheet shows values for each measure down to the granularity of my time key (reporting weeks).

  For each attribute that is available in my workbook page filter section, I have set the IsAggregatable poroperty to 'Unrestricted'. I have set Reporting Weeks to 'Full'. All the others are set to 'None'.

  Does all this sound reasonable I am currently waiting for the I.T department to let me install Excel 2007 so I can investigate the performance. However, I have run profiler through a friend's version to capture the MDX it generates for a similar cube query, and it is identical to that generated by Excel 2003.

  Dave

  
  

• ottogbg

  Hi Dave,

  Another factor to consider with Excel is whether Non Empty Behavior is defined for calculated measures in the query - are any of these measures calculated:  [Measures].[Collections], [Measures].[Rate], [Measures].[IFRS BDC], [Measures].[Balance]
  

  
  
  

• JJOSHI

  I think that you might have four issues here that you need to separate.

  1. MDX generated from Excel 2003 and how efficient it is.

  2. Caching of data in SSAS2005 when the same query is executed several times.

  3. Running new queries with different selections.

  4. Running the same query several times without or even with response time not improving.

   

  Number four can be related to the problem of defining proper aggregations i SSAS2005.

  Without consistent attribute relations, and correct types(rigid over flexible) in your dimensions you will not have efficient aggregations.

  Please review your attribute relations and later your aggregations, based on these attribute relations.

  Even with these properly set number one can cause problems.

  Have a try with Excel2007 and see the differences.

  Many different problems/subjects in the same discussion.

  Regards

  Thomas Ivarsson

   

  
  

• enric vives

  After taking a look at your query one more time, I am still not sure why such a simple query should be so slow. My suggestion is that you try to remove any fields you have in Page area of the pivot, and see if this improves the performance. Also check if having just one measure on the pivot will speed it up (as it may remove the need for CrossJoin). Basically you want to investigate this issue by elimination.

  In pivot table options dialog, there are settings for "Show columns with no data" and "Show rows with no data". You can select one of these options or both, and measure performance. It may speed things up, however you may wind up with pivot having a lot of empty rows.

  I am not familiar with XLCubed, however it is entirely conceivable that this add-in will generate its own MDX. You may give it a try.

  
  
  

• Raba Shani

  You will need to carefully examine if an attribute relationship is one to one or one to many. Try to change the relation to one to one in attribute hierarchies and use rigid as default as much as possible. With using rigid attribute relations you will have do a full process of each of your dimension every time.

  Download a free trial version of ProClarity, Excel 2007 or any other third party and see if the client MDX i causing the problem. Compare with your current Excel client. If performance is the same in with other clients then it can be a cube design question.

  Also. Start logging the queries and see if usage based optimization can help you to build more efficient aggregations.

  Regards

  Thomas Ivarsson

  
  

• David Mc Dermid

  No, both of these measures exist in the fact table. I don't have any calculated measures at all.

  I have been experimenting further with the pivottable in Excel and when I initially run queries, they are very slow. However, as I continue they seem to speed up considerably. Could this be due to some sort of caching I wouldn't expect to have to 'warm' the data up before it reaches an acceptable performance level. Having said this, even after achieving acceptable performance in some queries, I might run another one and be right back to waiting for one minute. There doesn't seem to be any consistency.

  Dave

  
  
• Slow Cube Performance in Excel