3 Replies Latest reply: Feb 24, 2012 6:45 PM by Gregory Misiorek RSS

ODBC MDX engine performance

Gregory Misiorek
Currently Being Moderated

ODBC experts,

 

i have closed my other MDX question due to no answers, so here's a more elaborated version:

 

1. i noticed an interesting and very consistent behavior when my analytical view is ODBC connected to an Excel pivot table. the first time (and second and third) i'm trying to move dimensions across filters, columns, and rows i get  a different kind of Excel OLAP/MDX error, out of which "assert" occurs the most often.

 

2. then, once the 'drag' change becomes accepted, the refresh takes between 100-110 seconds before the results get returned in the Excel client.

 

3. the complexity of the MDX query doesn't seem to impact this performance as it is pretty consistent whether it's a simple filter or a more complex join like this one:

 

MDX SELECT

NON EMPTY CrossJoin

(Hierarchize(AddCalculatedMembers({DrilldownLevel({[DIM1].[DIM1].[All].[(all)]})}))

,Hierarchize(AddCalculatedMembers({DrilldownLevel({[DIM2].[DIM2].[All].[(all)]})})))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[DIM1].[DIM1].[DIM1].[DIM1],[DIM2].[DIM2].[DIM2].[DIM2]

ON COLUMNS ,

NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[DIM3].[DIM3].[All].[(all)]})}))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[DIM3].[DIM3].[DIM3].[DIM3]

ON ROWS 

FROM [VIEW]

WHERE ([Measures].[MEASURE])

 

any theories about this?

Actions