Are there limitations to OLAP in Excel?

Hi,

Now, I use Analysis Services 2005 and Excel 2003 SP2 to deploy my OLAP model.

In Excel sheet, when creating a pivot table based on a Cube in Analysis Services, everything appears to work until I add a fourth dimension to the pivot table. Once I do that, I get a message that says "Running OLAP Query...(Press Esc to cancel)" so I must wait for a long time (about 5 minutes) to get the result. But while browsing the same data dimensions in Analysis Services Window, everything appears immediately.

Any ideas?

Best Regards!

You might qualify the question to read: "Are there limitations to using AS 2005 cubes in Excel 2003?"

Choosing client for SSAS 2005: Excel 2003 or Excel 2007

By Ramunas Balukonis

After we migrated Analysis Services from 2000 to 2005 we discovered that the same (or analogous) queries against SSAS 2005 runs even slower than AS 2000. Our users used MS Excel 2003 as front end on AS 2005. After some time I installed MS Excel 2007. Then I tried to do the same Pivot Table actions on both Excel versions (2003 and 2007): make Pivot Table from Advenure Works database placing days of [Date].[Calendar] [July 2003] and [August 2003] on rows, [Mountain-200 Silver, 38] and [Mountain-200 Silver, 42] from product [Product].[categories] on columns and [Sales Amount] as facts

Excel 2003 generates cumbersome MDX wits lots of except:

...

Excel 2007 generates elegant MDX:

...

The 1’st query from Excel 2003 runs 5 times longer!

After some time I gathered statistics from profiler and querylog. The results are as follows:

Avg MDX text length (Excel 2003): 2403
Avg MDX text length (Excel 2007): 1567
Avg query duration (Excel 2003): 507 ms
Avg query duration (Excel 2007): 96 ms

|||Thanks Ramunas!
Did you try to use the browser in Analysis Services or the Reporting Services to query your OLAP data . In my opinion, the queried time of using two ways above is better than the time of using Excel pivot table.
Could you explain me the reason why?

|||

Yeah, Excel 2003 is horrible with OLAP. It's the MDX issue, it does tons of EXCEPTS(), etc. Instead of scripting what it wants in the pivottable, it scripts what it doesn't want. Not sure how this ever got passed.

For your last reply, you will be hitting cache if you run from Excel, then run the same query somewhere else. You would have to execute the ClearCache command after every MDX statement for a true test. Or could be a network slowup if your using Excel externally (off the server) and running Management Studio on the server directly, for example.