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.
No comments:
Post a Comment