MDX queries from Reporting Services

I have been having some issues with generating reports in Reporting

Services off Analysis Services, in that the reports take a very long

time and I have to often kill the queries. They are using 100%CPU. I have then replicated as

closely as possible the report using the Cube Analysis add in for

Excel, and the response is almost instantaneous.

It

seems it is the MDX that Reporting Services is very inefficient

compared to that generated by Cube Analysis. It uses Selects in the From clause to restrict the scope of the query using the Filter parameters. Is this something anyone has come across, and are there any suggestions to get round it? I did try

to paste the Cube Analysis MDX into Reporting Services but the parser

rejected it as invalid.

I attach the comparitive MDX below for reference

Cube Analysis
SELECT

NON EMPTY{Hierarchize({CROSSJOIN({CROSSJOIN({{{[Portfolio].[Benchmark

Portfolio].[All],[Portfolio].[Benchmark Portfolio].[All].children} +

{{[Portfolio].[Benchmark

Portfolio].[Benchmark].&[1].children}}}},{{[Scenario].[Scenario].&[1],[Scenario].[Scenario].&[1].children}

+

{[Scenario].[Scenario].&[3],[Scenario].[Scenario].&[3].children}

+

{[Scenario].[Scenario].[Benchmark],[Scenario].[Scenario].[Benchmark].children}

+ {[Scenario].[Scenario].[Variance to

Benchmark],[Scenario].[Scenario].[Variance to

Benchmark].children}})},{{[Measures].[Duration]} +

{[Measures].[Weighting]}})})} on 0, NON

EMPTY{Hierarchize({{[Sector].[Sector Category].[All],[Sector].[Sector

Category].[All].children}})} on 1 from [MIQB27] where

([Period].[Year-

Month].[Month].&[2006-03-01T00:00:00],[Benchmark].[Benchmark].&[1],[Rating].[Rating

Category].[All],[Currency of Exposure].[Currency].&[2]) CELL

PROPERTIES VALUE, FORMATTED_VALUE, FONT_NAME, FONT_SIZE,

FONT_FLAGS,FORE_COLOR, BACK_COLOR, FORMAT_STRING

Reporting Services

SELECT NON EMPTY

{ [Measures].[Duration], [Measures].[Weighting] } ON

COLUMNS, NON EMPTY { ([Sector].[Sector Category].[Sector

Category].ALLMEMBERS *

[Benchmark].[Benchmark].[Benchmark].ALLMEMBERS

* [Portfolio].[Portfolio].[Portfolio].ALLMEMBERS

* [Scenario].[Scenario].[Scenario].ALLMEMBERS

) } DIMENSION PROPERTIES

MEMBER_CAPTION, MEMBER_UNIQUE_NAME

ON ROWS FROM ( SELECT ( {

[Scenario].[Scenario].&[1] } ) ON COLUMNS FROM ( SELECT ( { [Rating].[Rating Category].&[38] } ) ON COLUMNS FROM ( SELECT ( {

[Benchmark].[Benchmark].&[1] } ) ON COLUMNS FROM ( SELECT ( { [Period].[Year-

Month].[Month].&[2006-03-01T00:00:00] } ) ON

COLUMNS FROM

[MIQB27])))) WHERE ( [Period].[Year-

Month].[Month].&[2006-03-01T00:00:00], [Rating].[Rating Category].&[38]

) CELL PROPERTIES

VALUE, BACK_COLOR,

FORE_COLOR, FORMATTED_VALUE,

FORMAT_STRING, FONT_NAME,

FONT_SIZE, FONT_FLAGS

It's hard to say without seeing your cube, but I suspect one reason for the difference is the fact that RS forces you to pivot your queries so that you only have the measures dimension on columns. This means that you have to put all the rest of your dimensions crossjoined on rows, which could potentially mean that the NON EMPTY operation has a lot of tuples to work on. How many members are there in the Sector Category, Benchmark, Portfolio and Scenario attributes?

Does changing the MDX in RS to be something like the following make a difference?

SELECT NON EMPTY { [Measures].[Duration], [Measures].[Weighting] } ON COLUMNS,
NON EMPTY { (
NONEMPTY(
[Sector].[Sector Category].[Sector Category].ALLMEMBERS
, { [Measures].[Duration], [Measures].[Weighting] })
* [Benchmark].[Benchmark].[Benchmark].ALLMEMBERS * [Portfolio].[Portfolio].[Portfolio].ALLMEMBERS *
[Scenario].[Scenario].[Scenario].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
( SELECT ( { [Scenario].[Scenario].&[1] } ) ON COLUMNS FROM ( SELECT ( { [Rating].[Rating Category].&[38] } ) ON COLUMNS FROM ( SELECT ( { [Benchmark].[Benchmark].&[1] } ) ON COLUMNS FROM ( SELECT ( { [Period].[Year- Month].[Month].&[2006-03-01T00:00:00] } ) ON COLUMNS FROM [MIQB27])))) WHERE ( [Period].[Year- Month].[Month].&[2006-03-01T00:00:00], [Rating].[Rating Category].&[38] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Chris

|||I have changed the cube slightly - I had one dimension called Benchmark

which I removed it only has 3 leaf members and the ALL level. I had

the Benchmark code on another dimension as an attribute so I thought I

would use this instead. The RS query now worlks very quickly. On the

face of it the MDX query looks just as complex (see below). The only

thing I canm think of is as Benchmark was an attribute of Portfolio and

a dimension in its own right, it might have been doing some Cartesian

Product. But even so there were only 3 members.

Anyway, I am glad the issue has gone away - I was beginning to get worried about the use of RS on top of AS.

Regards

Paul

SELECT NON EMPTY

{ [Measures].[Weighting], [Measures].[Duration] } ON

COLUMNS, NON EMPTY { ([Rating].[Rating Category].[Rating

Category].ALLMEMBERS * [Sector].[Sector

Category].[Sector Category].ALLMEMBERS *

[Portfolio].[Benchmark Portfolio].[Portfolio].ALLMEMBERS

* [Scenario].[Scenario].[Scenario].ALLMEMBERS

) } DIMENSION PROPERTIES

MEMBER_CAPTION, MEMBER_UNIQUE_NAME

ON ROWS FROM ( SELECT ( STRTOSET(@.CurrencyofExposureCurrency, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Scenario].[Scenario].&[1],

[Scenario].[Scenario].&[3] } ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.PortfolioBenchmark,

CONSTRAINED) ) ON

COLUMNS FROM ( SELECT ( STRTOSET(@.PeriodYearMonth,

CONSTRAINED) ) ON

COLUMNS FROM

[MIQB28])))) WHERE ( IIF(

STRTOSET(@.PeriodYearMonth, CONSTRAINED).Count =

1, STRTOSET(@.PeriodYearMonth, CONSTRAINED), [Period].[Year- Month].currentmember ), IIF(

STRTOSET(@.PortfolioBenchmark, CONSTRAINED).Count =

1, STRTOSET(@.PortfolioBenchmark, CONSTRAINED), [Portfolio].[Benchmark].currentmember ), IIF(

STRTOSET(@.CurrencyofExposureCurrency, CONSTRAINED).Count =

1, STRTOSET(@.CurrencyofExposureCurrency, CONSTRAINED), [Currency of Exposure].[Currency].currentmember ) ) CELL

PROPERTIES VALUE,

BACK_COLOR, FORE_COLOR,

FORMATTED_VALUE, FORMAT_STRING,

FONT_NAME, FONT_SIZE,

FONT_FLAGS

|||

I am curious about your table structure. It looks like you are trying to do the same thing I am. Comparing portfolios to benchmarks for various groupings. Are weightings a calculated field? Do you have an overweight/underweight calculation? Do you use a PortfolioBenchmark dimension table or break them out?

Thanks for any help.