Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

MDX and response time in ProClarity and Excel 2007

Posted by thomasivarssonmalmo on December 12, 2008

I have heared some talk, from other consultants here in Sweden, about the MDX generated by Excel 2007, when querying SSAS 2005 cubes, is slow in some scenarios.  The worst performance killers, from what I have been told are arbitraty shape queries and calculated members.
 
In this first blog post about client performance I will compare the MDX generated by ProClarity 6.3 and Excel 2007 (sp1) in two other scenarios.
 
It will help with finding a baseline of each clients performance under one standard scenario and one that is more extreme.
 
The first is a simple query with the customers and products dimensions nested together on higher levels in the hierarchy in the Adventure Works cube. 
 
The second scenario is the "query from hell" when customer and products are nested on leaf levels. I will take the MDX generated by the clients and paste it into MDX studio and run each query on a cold and warm cache. What we will miss is the time for each client to render the result sets graphically in grids. We will only measure the performance of the MDX generated in each client.
 
The first version of ProClarity was released at the same time as OLAP Services, the first SSAS version that shipped with SQL Server 7 in 1999. Excel 2007 was released one year after SSAS 2005, at the end of 2006.  
 
For Excel 2007 I have used the Pivot table add in that you can download from Codeplex. The hardware is a one year old laptop with Windows XP sp2 and 4 GB RAM.
 
This is the first query in ProClarity.
 
 
This is the MDX generated by ProClarity.
 
SELECT { [Date].[Calendar].[All Periods].CHILDREN } ON COLUMNS ,
{ { { [Customer].[Customer Geography].[All Customers].CHILDREN } * { [Product].[Product Categories].[All Products].CHILDREN } } } ON ROWS 
FROM [Adventure Works]
WHERE ( [Measures].[Internet Sales Amount] )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL
 
On a cold cache in MDX I have got the following counters.
 
Time              : 3 min 25 sec 334 ms
Calc covers       : 182114
Cells calculated  : 198643
Sonar subcubes    : 3664
SE queries        : 182110
Cache hits        : 185750
Cache misses      : 18
Cache inserts     : 11
Cache lookups     : 185768
Memory Usage KB   : 65408
 
On a warm cache I have got these counters.
 
Time              : 109 ms
Calc covers       : 3
Cells calculated  : 96
Sonar subcubes    : 1
SE queries        : 1
Cache hits        : 1
Cache misses      : 0
Cache inserts     : 0
Cache lookups     : 1
Memory Usage KB   : 0
 
In Excel 2007 the same tablee looks like this.
 
 
The MDX generated by Excel 2007 looks like this.
 
SELECT NON EMPTY Hierarchize({DrilldownLevel({[Date].[Calendar].[All Periods]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,
NON EMPTY CrossJoin(Hierarchize({DrilldownLevel({[Customer].[Customer Geography].[All Customers]})}),
Hierarchize({DrilldownLevel({[Product].[Product Categories].[All Products]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS 
FROM [Adventure Works] WHERE ([Measures].[Internet Sales Amount])
 CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
 
In MDX studio I have got these counters for the Excel 2007 MDX on a cold cache.
 
Time              : 3 min 26 sec 540 ms
Calc covers       : 182159
Cells calculated  : 198687
Sonar subcubes    : 3675
SE queries        : 182117
Cache hits        : 185757
Cache misses      : 18
Cache inserts     : 11
Cache lookups     : 185775
Memory Usage KB   : 63936
 
And for the warm cache I have got these counters.
 
Time              : 124 ms
Calc covers       : 48
Cells calculated  : 140
Sonar subcubes    : 8
SE queries        : 8
Cache hits        : 8
Cache misses      : 0
Cache inserts     : 0
Cache lookups     : 8
Memory Usage KB   : 0
 
When we compare ProClarity and Excel 2007 we can hardly see any differences in performance. Some counters are a little bit higher in Excel 2007 but this can depend on that I have the grand totals active in Excel but not in ProClarity.
 
Now to the second scenario, combining the leaf levels in the customer and product dimensions.
 
The result look like this in ProClarity.
 
 
The MDX generated by ProClarity looks like this.
 
SELECT { [Date].[Calendar].[Calendar Year].&[2003] } ON COLUMNS ,
NON EMPTY { { { DESCENDANTS( [Customer].[Customer Geography].[All Customers], [Customer].[Customer Geography].[Customer] ) } *
 { DESCENDANTS( [Product].[Product Categories].[All Products], [Product].[Product Categories].[Product] ) } } } ON ROWS 
FROM [Adventure Works]
WHERE ( [Measures].[Internet Sales Amount] )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL
 
The counters for the cold cache
 
Time              : 3 min 28 sec 296 ms
Calc covers       : 182114
Cells calculated  : 222671
Sonar subcubes    : 3665
SE queries        : 182110
Cache hits        : 185750
Cache misses      : 18
Cache inserts     : 11
Cache lookups     : 185768
Memory Usage KB   : 53696
 
And the counters for the warm cache.
 
Time              : 1 sec 781 ms
Calc covers       : 3
Cells calculated  : 24124
Sonar subcubes    : 1
SE queries        : 1
Cache hits        : 1
Cache misses      : 0
Cache inserts     : 0
Cache lookups     : 1
Memory Usage KB   : 3328
 
The Excel 2007 generated for "the query from hell" scenario is not possible to show here because it is a very large list. A fragment of that code is possible to show.
 
SELECT NON EMPTY Hierarchize({DrilldownLevel({[Date].[Calendar].[All Periods]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,
NON EMPTY CrossJoin(Hierarchize(DrilldownMember({{DrilldownMember({{DrilldownMember({{DrilldownMember({{DrilldownLevel({[Customer].[Customer Geography].[All Customers]})}},
{[Customer].[Customer Geography].[Country].&[Australia]})}}, {[Customer].[Customer Geography].[State-Province].&[NSW]&[AU], 
FROM (SELECT ({[Date].[Calendar].[Calendar Year].&[2003]}) ON COLUMNS  FROM [Adventure Works]    –This is the subcube statement at the end
 
Performance counters on a cold cache are these.
 
Time              : 3 min 27 sec 375 ms
Calc covers       : 182399
Cells calculated  : 224175
Sonar subcubes    : 3739
SE queries        : 182158
Cache hits        : 185842
Cache misses      : 18
Cache inserts     : 11
Cache lookups     : 185860
Memory Usage KB   : 62656
 
And on a warm cache they are these.
 
Time              : 3 sec 93 ms
Calc covers       : 288
Cells calculated  : 25628
Sonar subcubes    : 52
SE queries        : 48
Cache hits        : 92
Cache misses      : 0
Cache inserts     : 0
Cache lookups     : 92
Memory Usage KB   : 9216
 
On a warm cache the response time is three times higher for Excel 2007  than for ProClarity, but we are talking about a few seconds. On a cold cache responsetime are the same for both clients, like the number of cells calculated and SE queries.
 
Summary: For these two query scenarios the performance of the MDX generated by the two clients are almost identical, except for the difference for the cold cache scenario for the "query from hell". It i more or less what you should expect. 
 
The next step is to test how the two clients handle calculated measures and calculated members.
 
 
Advertisements

2 Responses to “MDX and response time in ProClarity and Excel 2007”

  1. eynav said

    hi, a very interesting comparison. can you provide me the database which you used?i want to test it also with Panorama NovaView and also with Panorama NovaView SaaS solution?my email is eynav 3 at panorama dot com (without the spaces ofcourse)tnx

  2. Thomas said

    Hi, It is the Adventure Works cube on my local SSAS 2005 SP3 Developer Edition. You might have to add a trace with Profiler to get the complete picture. I might write about this in a new blog entry.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: