I am having issue with Performance of the Pivot Table connecting to OLAP Cubes. I am wondering if any of you have links to how to improve Pivot Table performance? Basically, I have done the backend optimization on the schema, but there should tricks on the Excel such as Connection Properties that I could use that could relate to MDX or Threshold Level that could speed up the Pivot Table performance. Below is some initial thoughts...
Non Empty Threshold=1
Any help is appreciated!
-Lawrence
I would guess you are talking about Analysis Services 2000.
The shema optimization in the Analysis Services is not a single optimization avaliable. For instance designing aggregations should be big help.
Check out performance guide for AS2000 on http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx it should plenty of information to go over. See if applies to your case.
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
That is a possible answer
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=379271&SiteID=1
Did you try This version of Excel?
I would be glad to know if this fixes most of these Excel perfomance issues mentioned in this forum.
Make sure you use native Excel2007 file format. When starting from an older Excel format, save as Excel 2007, close, re-open and try.
Philippe
|||I am currently using Excel 2003 for pivot table functionality of AS 2000 & AS 2005 cubes.
-Lawrence
|||Me too.
Here is my personnal experience with this.
I went through all dimensions optimizations like attributes relationships cascades, Rigid properties, cardinality, key/values pairs and the like.
I also spent time closing in on calculated measures non empty behavior and MDX optimization
then I limited the number of dimensions members to the bare minimum.
After that, I have created dynamic partitioning of my cubes by quarters.
Lastly I used the wizard build a 30 to 40 % aggregations optimization and after one week or two I added usage based optimization.
The lowdown is that from an Excel 2003 point of view, the performance gain is almost nothing.
With Excel 2007, the performance gain is enormous. Excel2007 works much faster even on a non-optimized cube and when I use Excel2007 against an optimized version of the cube, it is simply uncomparably faster, no matter how complex the view is and how large the result set is.
My best 3-5 power users will install Excel2007 B2TR ($1.50 a pop) on a separate folder than 2003 and use 2007 for the cubes.
We will use this UAT to build a business case that will help convince our IT to switch all my cube users to 2007 soon after it becomes public release.
BTY, We are going to build a 64bit server in the next few weeks and see what will be the outcome. This may help the 2003 users who have to wait next year untill they get the update.
There would be another thing to try to boost Excel2003 performance. I remember than in AS2000 excel was performing much better against "denormalized" cubes. This may sound to be the opposite of best practice cube design but if it helps, why not.
Regards,
Philippe
|||I don't think spending a lot of money to upgrade to Excel 2007 just for Pivot Table performance gain is worth it. There are some settings on Pivot Table connection could increase the performance. Thanks!
|||Hi,That is very interesting. I would love to know what are these setting, could you please share this information? I tried all what was available from the GUI. Can your setting be part of the initial template I send the users? Can I give them a macro that would update their existing Excel views?
On a cube build at top of a 10M rows fact table and with quite a few cross dimensions having up to 20000 members, the "out of of the box" performance of Excel 2003 compared to Excel 2007 for one specific case is 45 minutes in Excel 2003 and 15 milliseconds in Excel 2007.
For me, any smart setting would be more than welcome even if I get "only" a 50% speed increase however upgrading to 2007 is clearly the long term solution.
Thanks,
Philippe
No comments:
Post a Comment