Sharepoint Forum

Ask Question   UnAnswered
Home » Forum » Sharepoint       RSS Feeds

SSRS question: Control the MDX generated by Proclarity for performance

  Asked By: Roy    Date: Nov 06    Category: Sharepoint    Views: 1376

I have a proclarity report which takes longer to run than a similar SSRS report
Report needs to show, Sales, Qty for 2009 Jan for couple of Product & Customer Attributes

1. MDX Query Generated by Reporting Services

SELECT NON EMPTY { [Measures].[Pocket Sales USD], [Measures].[Reporting Quantity] } ON COLUMNS,

NON EMPTY { ([Material].[Key Product Aggregation].[Key Product Aggregation].ALLMEMBERS * [Sales Organization].[Region].[Region].ALLMEMBERS * [Customer Sales].[Customer Ownership].[Customer Ownership Level 5 Name].ALLMEMBERS ) } ON ROWS

FROM ( SELECT ( { [Time].[Year].&[2009] } ) ON COLUMNS FROM ( SELECT ( { [Material].[Material Business Unit].&[D3] } ) ON COLUMNS FROM [CM Sales Analysis]))

WHERE ( [Material].[Material Business Unit].&[D3], [Time].[Year].&[2009] )

2. MDX Query Generated by Proclarity

SELECT NON EMPTY { [Measures].[Pocket Sales USD], [Measures].[Reporting Quantity] } ON COLUMNS ,

NON EMPTY { { { [Material].[Key Product Aggregation].[All].CHILDREN } *
{ [Sales Organization].[Region].[All].CHILDREN } *
{ [Customer Sales].[Customer Ownership Level 5 Name].[All].CHILDREN } } } ON ROWS

FROM [CM Sales Analysis]

WHERE ( [Time].[Year].&[2009], [Material].[Material Business Unit].&[D3] )

There seem to be two major differences

1. Allmembers keyword is being used by SSRS vs [ALL].Children in Proclarity
2. From clause
a. SSRS seems to be creating a smaller slice of cube based on Selections on Time and Business Unit

Can I make Proclarity use the allmembers keyword(query seems to execute faster when using allmembers)
Can I make Proclarity to use similar From clause ?

Proclarity Reports are created by users so can not ask them to modify the MDX



1 Answer Found

Answer #1    Answered By: Deana Lowery     Answered On: Nov 06

The main performance  gain in the ssrs  report is from the sub-select. This creates essentially a sub-cube. Time is limited to 2009, and Material Business Unit is limited to &[D3]. The subsequent set operations are performed on a narrower data set.

ALLMEMBERS is a more efficient key word than [All].children. I think it has the same semantic meaning, but you would need to check.

There are also extra curly-brackets in the proclarity query.

The reason why SSRS was able to narrow down the set is because - The SSRS report  is the result of a Report Model. So the model contains information that is likely not available to Proclarity. E.G. entity definitions. The proclarity report can be made to look the same way only if we manage to change the MDX ourselves. And I don’t think there is any other way to make it generate a similar  query.