Ramunas Balukonis Blog

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 genetares cumbersome MDX wits lots of except:

SELECT NON EMPTY HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers(Except({AddCalculatedMembers(DrillDownMember({Except({AddCalculatedMembers(DrillDownMember({DrillDownLevel({[Product].[Prod
uct Categories].[All Products]})}, {[Product].[Product Categories].[Category].&[1]}))}, {[Product].[Product Categories].[Category].&[2], [Product].[Product Categories].[Category].&[3], [Product].[Product
Categories].[Category].&[4]})}, {[Product].[Product Categories].[Subcategory].&[1]}))}, {[Product].[Product Categories].[Subcategory].&[2], [Product].[Product Categories].[Subcategory].&[3]}))},
{[Product].[Product Categories].[Product].&[600], [Product].[Product Categories].[Product].&[599], [Product].[Product Categories].[Product].&[598], [Product].[Product Categories].[Product].&[597],
 [Product].[Product
Categories].[Product].&[596], [Product].[Product Categories].[Product].&[595], [Product].[Product Categories].[Product].&[594], [Product].[Product Categories].[Product].&[593], [Product].[Product
Categories].[Product].&[592], [Product].[Product Categories].[Product].&[591], [Product].[Product Categories].[Product].&[590], [Product].[Product Categories].[Product].&[589], [Product].[Product
Categories].[Product].&[588], [Product].[Product Categories].[Product].&[587], [Product].[Product Categories].[Product].&[367], [Product].[Product Categories].[Product].&[366], [Product].[Product
Categories].[Product].&[365], [Product].[Product Categories].[Product].&[364], [Product].[Product Categories].[Product].&[363], [Product].[Product Categories].[Product].&[362], [Product].[Product
Categories].[Product].&[361], [Product].[Product Categories].[Product].&[360], [Product].[Product Categories].[Product].&[359], [Product].[Product Categories].[Product].&[358], [Product].[Product
Categories].[Product].&[357], [Product].[Product Categories].[Product].&[356], [Product].[Product Categories].[Product].&[352], [Product].[Product Categories].[Product].&[351], [Product].[Product
Categories].[Product].&[350], [Product].[Product Categories].[Product].&[349], [Product].[Product Categories].[Product].&[348], [Product].[Product Categories].[Product].&[347], [Product].[Product
Categories].[Product].&[346], [Product].[Product Categories].[Product].&[345], [Product].[Product Categories].[Product].&[344], [Product].[Product Categories].[Product].&[311], [Product].[Product
Categories].[Product].&[310], [Product].[Product Categories].[Product].&[606], [Product].[Product Categories].[Product].&[605], [Product].[Product Categories].[Product].&[604], [Product].[Product
Categories].[Product].&[584], [Product].[Product Categories].[Product].&[583], [Product].[Product Categories].[Product].&[582], [Product].[Product Categories].[Product].&[581], [Product].[Product
Categories].[Product].&[580], [Product].[Product Categories].[Product].&[390], [Product].[Product Categories].[Product].&[389], [Product].[Product Categories].[Product].&[388], [Product].[Product
Categories].[Product].&[387], [Product].[Product Categories].[Product].&[386], [Product].[Product Categories].[Product].&[385], [Product].[Product Categories].[Product].&[384], [Product].[Product
Categories].[Product].&[383], [Product].[Product Categories].[Product].&[382], [Product].[Product Categories].[Product].&[381], [Product].[Product Categories].[Product].&[380], [Product].[Product
Categories].[Product].&[379], [Product].[Product Categories].[Product].&[378], [Product].[Product Categories].[Product].&[377], [Product].[Product Categories].[Product].&[376], [Product].[Product
Categories].[Product].&[375], [Product].[Product Categories].[Product].&[374], [Product].[Product Categories].[Product].&[373], [Product].[Product Categories].[Product].&[372], [Product].[Product
Categories].[Product].&[371], [Product].[Product Categories].[Product].&[370], [Product].[Product Categories].[Product].&[369], [Product].[Product Categories].[Product].&[368], [Product].[Product
Categories].[Product].&[343], [Product].[Product Categories].[Product].&[342], [Product].[Product Categories].[Product].&[341], [Product].[Product Categories].[Product].&[340], [Product].[Product
Categories].[Product].&[339], [Product].[Product Categories].[Product].&[338], [Product].[Product Categories].[Product].&[337], [Product].[Product Categories].[Product].&[336], [Product].[Product
Categories].[Product].&[335], [Product].[Product Categories].[Product].&[334], [Product].[Product Categories].[Product].&[333], [Product].[Product Categories].[Product].&[332], [Product].[Product
Categories].[Product].&[331], [Product].[Product Categories].[Product].&[330], [Product].[Product Categories].[Product].&[329], [Product].[Product Categories].[Product].&[328], [Product].[Product
Categories].[Product].&[327], [Product].[Product Categories].[Product].&[326], [Product].[Product Categories].[Product].&[325], [Product].[Product Categories].[Product].&[324], [Product].[Product
Categories].[Product].&[323], [Product].[Product Categories].[Product].&[322], [Product].[Product Categories].[Product].&[321], [Product].[Product Categories].[Product].&[320], [Product].[Product
Categories].[Product].&[319], [Product].[Product Categories].[Product].&[318], [Product].[Product Categories].[Product].&[317]}))}, {[Product].[Product Categories].[Category].&[2], [Product].[Product
Categories].[Category].&[3], [Product].[Product Categories].[Category].&[4], [Product].[Product Categories].[Subcategory].&[2], [Product].[Product Categories].[Subcategory].&[3], [Product].[Product
Categories].[Product].&[600], [Product].[Product Categories].[Product].&[599], [Product].[Product Categories].[Product].&[598], [Product].[Product Categories].[Product].&[597], [Product].[Product
Categories].[Product].&[596], [Product].[Product Categories].[Product].&[595], [Product].[Product Categories].[Product].&[594], [Product].[Product Categories].[Product].&[593], [Product].[Product
Categories].[Product].&[592], [Product].[Product Categories].[Product].&[591], [Product].[Product Categories].[Product].&[590], [Product].[Product Categories].[Product].&[589], [Product].[Product
Categories].[Product].&[588], [Product].[Product Categories].[Product].&[587], [Product].[Product Categories].[Product].&[367], [Product].[Product Categories].[Product].&[366], [Product].[Product
Categories].[Product].&[365], [Product].[Product Categories].[Product].&[364], [Product].[Product Categories].[Product].&[363], [Product].[Product Categories].[Product].&[362], [Product].[Product
Categories].[Product].&[361], [Product].[Product Categories].[Product].&[360], [Product].[Product Categories].[Product].&[359], [Product].[Product Categories].[Product].&[358], [Product].[Product
Categories].[Product].&[357], [Product].[Product Categories].[Product].&[356], [Product].[Product Categories].[Product].&[352], [Product].[Product Categories].[Product].&[351], [Product].[Product
Categories].[Product].&[350], [Product].[Product Categories].[Product].&[349], [Product].[Product Categories].[Product].&[348], [Product].[Product Categories].[Product].&[347], [Product].[Product
Categories].[Product].&[346], [Product].[Product Categories].[Product].&[345], [Product].[Product Categories].[Product].&[344], [Product].[Product Categories].[Product].&[311], [Product].[Product
Categories].[Product].&[310], [Product].[Product Categories].[Product].&[606], [Product].[Product Categories].[Product].&[605], [Product].[Product Categories].[Product].&[604], [Product].[Product
Categories].[Product].&[584], [Product].[Product Categories].[Product].&[583], [Product].[Product Categories].[Product].&[582], [Product].[Product Categories].[Product].&[581], [Product].[Product
Categories].[Product].&[580], [Product].[Product Categories].[Product].&[390], [Product].[Product Categories].[Product].&[389], [Product].[Product Categories].[Product].&[388], [Product].[Product
Categories].[Product].&[387], [Product].[Product Categories].[Product].&[386], [Product].[Product Categories].[Product].&[385], [Product].[Product Categories].[Product].&[384], [Product].[Product
Categories].[Product].&[383], [Product].[Product Categories].[Product].&[382], [Product].[Product Categories].[Product].&[381], [Product].[Product Categories].[Product].&[380], [Product].[Product
Categories].[Product].&[379], [Product].[Product Categories].[Product].&[378], [Product].[Product Categories].[Product].&[377], [Product].[Product Categories].[Product].&[376], [Product].[Product
Categories].[Product].&[375], [Product].[Product Categories].[Product].&[374], [Product].[Product Categories].[Product].&[373], [Product].[Product Categories].[Product].&[372], [Product].[Product
Categories].[Product].&[371], [Product].[Product Categories].[Product].&[370], [Product].[Product Categories].[Product].&[369], [Product].[Product Categories].[Product].&[368], [Product].[Product
Categories].[Product].&[343], [Product].[Product Categories].[Product].&[342], [Product].[Product Categories].[Product].&[341], [Product].[Product Categories].[Product].&[340], [Product].[Product
Categories].[Product].&[339], [Product].[Product Categories].[Product].&[338], [Product].[Product Categories].[Product].&[337], [Product].[Product Categories].[Product].&[336], [Product].[Product
Categories].[Product].&[335], [Product].[Product Categories].[Product].&[334], [Product].[Product Categories].[Product].&[333], [Product].[Product Categories].[Product].&[332], [Product].[Product
Categories].[Product].&[331], [Product].[Product Categories].[Product].&[330], [Product].[Product Categories].[Product].&[329], [Product].[Product Categories].[Product].&[328], [Product].[Product
Categories].[Product].&[327], [Product].[Product Categories].[Product].&[326], [Product].[Product Categories].[Product].&[325], [Product].[Product Categories].[Product].&[324], [Product].[Product
Categories].[Product].&[323], [Product].[Product Categories].[Product].&[322], [Product].[Product Categories].[Product].&[321], [Product].[Product Categories].[Product].&[320], [Product].[Product
Categories].[Product].&[319], [Product].[Product Categories].[Product].&[318], [Product].[Product Categories].[Product].&[317]})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY
HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers(DrillDownMember({Except({AddCalculatedMembers(DrillDownMember({Except({AddCalculatedMembers(DrillDownMember({Except({Add
CalculatedMembers(DrillDownMember({DrillDownLevel({[Date].[Calendar].[All Periods]})}, {[Date].[Calendar].[Calendar Year].&[2003]}))}, {[Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Calendar
Year].&[2002], [Date].[Calendar].[Calendar Year].&[2001]})}, {[Date].[Calendar].[Calendar Semester].&[2003]&[2]}))}, {[Date].[Calendar].[Calendar Semester].&[2003]&[1]})}, {[Date].[Calendar].[Calendar
Quarter].&[2003]&[3]}))}, {[Date].[Calendar].[Calendar Quarter].&[2003]&[4]})}, {[Date].[Calendar].[Month].&[2003]&[7], [Date].[Calendar].[Month].&[2003]&[8]}))}, {[Date].[Calendar].[Month].&[2003]&[9]}))},
{[Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Calendar Year].&[2002], [Date].[Calendar].[Calendar Year].&[2001], [Date].[Calendar].[Calendar Semester].&[2003]&[1], [Date].[Calendar].[Calendar
Quarter].&[2003]&[4], [Date].[Calendar].[Month].&[2003]&[9]})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS FROM [Adventure Works] WHERE ([Measures].[Sales Amount])

Excel 2007 generates elegant MDX:

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Product].[Product Categories].[All Products]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY
Hierarchize(DrilldownMember({{DrilldownMember({{DrilldownMember({{DrilldownMember({{DrilldownLevel({[Date].[Calendar].[All Periods]})}}, {[Date].[Calendar].[Calendar Year].&[2003]})}},
{[Date].[Calendar].[Calendar Semester].&[2003]&[2]})}}, {[Date].[Calendar].[Calendar Quarter].&[2003]&[3]})}}, {[Date].[Calendar].[Month].&[2003]&[7],[Date].[Calendar].[Month].&[2003]&[8]})) DIMENSION
PROPERTIES PARENT_UNIQUE_NAME,[Date].[Calendar].[Calendar Semester].[Calendar Year],[Date].[Calendar].[Calendar Quarter].[Calendar Quarter of Year],[Date].[Calendar].[Calendar Quarter].[Calendar
Semester],[Date].[Calendar].[Calendar Quarter].[Calendar Semester of Year],[Date].[Calendar].[Calendar Quarter].[Fiscal Quarter],[Date].[Calendar].[Month].[Calendar Quarter],[Date].[Calendar].[Month].[Month of
Year],[Date].[Calendar].[Date].[Day Name],[Date].[Calendar].[Date].[Day of Year],[Date].[Calendar].[Date].[Day of Month],[Date].[Calendar].[Date].[Day of Week],[Date].[Calendar].[Date].[Month
Name],[Date].[Calendar].[Date].[Week of Year] ON ROWS  FROM (SELECT ({[Product].[Product Categories].[Product].&[352], [Product].[Product Categories].[Product].&[353], [Product].[Product
Categories].[Product].&[354], [Product].[Product Categories].[Product].&[355]}) ON COLUMNS , ({[Date].[Calendar].[Month].&[2003]&[8], [Date].[Calendar].[Month].&[2003]&[7]}) ON ROWS  FROM [Adventure Works])
WHERE ([Measures].[Sales Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

The 1′st query from Excel 2003 runs 5 times longer! Profiler shows lots of “Started/finised reading data from the … partitions

So, we started to move client to new Excel 2007 version. 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

From my expirience, almost impossible to work with SSAS 2005 with Excel 2003. Avoid using this as a client and move to new Excel 2007. 

Excel 2007 also has backward compatibility mode. It means Excel 2007  generates queries for AS 2000, as example with lots of except. To take advantages working with Excel 2007 ensure you have converted Excel 2007 from “compatibility mode” and recreated pivot tables. Among all other Excel 2007 features you’ll get the best performance.