November 16, 2005 at 1:41 pm
We have an excel pivot table linked to an Analysis Service Cube that slows down tremendously (from 2 minutes to 30 minutes) after around 2 months. We then recreate another pivot table, same format, same filtering, same cube and the query is back to 2 minutes. We have examples of 2 exact same pivot tables....the one created 2 months ago takes 30 minutes and the one created a few days ago takes 2 minutes. Does anyone know how to research what is going on with the older pivot table. The older pivot table was not writing to a log file and we don't know if you can change it now to write to a log file. It does not have an oqy file since the worksheet copied from another one.
I appreciate any suggestions that you have. Thank you.
November 16, 2005 at 4:19 pm
Do people manipulate the pivottable (ie change the query) over time? I remember looking at the Excel Accellerator (the one that enhances the OLAP reporting capabilities) and it has a neat 'exclude this member' type function, but this 'exclusion' remains with the query basically forever.
To do some research on tis, you may want to write a macro that extracts the MDX from the pt and compare it with the pt MDX that has just been created. Failing being able to do this, you'll need to log the mdx. Seeing as the *.oqy has gone, you may be able to create a new connection and redirect the pt to this connection via code.
Steve.
November 17, 2005 at 7:03 am
Hi.
To help you get the MDX.
Inside the PivotTable's connectionstring insert the following: "Query Log=C:\MDX.log;". You can write this file to any other drive or folder, and change the name if you want.
This will write all commands sent by the PivotTable into MDX.log file.
I'm used to do this with Office Web Components and is very usefull.
Hope this help.
November 18, 2005 at 2:36 pm
Thank you for your response. We ended up writing to the log file. The pivot table has 3 customers selected out of approx 20,000. The mdx in the log had "Except... and then listed all the customers that were NOT selected". We found that the difference with the 2 pivot tables was that as new customers were added after the pivot table was created, the new customer was 'clicked off' in the pivot table and was not part of the 'except statement'. The 'new' customers did not fit the other filter criteria so it was not obvious it was clicked off. We have this issue with other pivot tables as well and always with the customer name dimension.... which is a shared dimension with a full reprocess every night. Have you ever had that issue with members automatically being clicked off on the pivot table?
November 21, 2005 at 5:29 am
Hello.
I guess this because the XMLData Property of the PivotTable keeps the cube information (before process). I don't know how to turn around this specific problem. Maybe a script saving the XMLData????
Best Regards.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply