I have a view set up that has approx 40 rows and 15 columns. Prior to applying the view - i have filtered the grid to remove empty rows and columns. The data comes back very quickly. However - when I add grand totals to the columns and rows and apply - the application says "Running Query" but never comes back. Is there an option that I can set?
Sorry I don't have a quick answer for this.
I opened up Proclarity on my desktop and connected to AdventureWorks and followed steps similar to what you've desribed. The Grand Totals are calculated in the MDX and not by Proclarity itself. Here is the MDX Proclarity generated for this:
Code Snippet
WITH
MEMBER [Date].[Calendar].[All Periods].[ Grand Total] AS
'AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { [Date].[Calendar].[All Periods].CHILDREN }, [Date].[Calendar] ) } }, { [Date].[Calendar].[All Periods].CHILDREN } ) )',
SOLVE_ORDER = 1000
MEMBER [Product].[Product Categories].[All Products].[ Grand Total] AS
'AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { [Product].[Product Categories].[All Products].CHILDREN }, [Product].[Product Categories] ) } }, { [Product].[Product Categories].[All Products].CHILDREN } ) )',
SOLVE_ORDER = 1000
SELECT
NON EMPTY { { [Date].[Calendar].[All Periods].CHILDREN }, ( [Date].[Calendar].[All Periods].[ Grand Total] ) } ON COLUMNS ,
NON EMPTY { { [Product].[Product Categories].[All Products].CHILDREN }, ( [Product].[Product Categories].[All Products].[ Grand Total] ) } ON ROWS
FROM [Adventure Works]
WHERE ( [Measures].[Reseller Sales Amount] )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE
So, I think you need to first determine if this is a Proclarity issue or if this is an SSAS issue. Open an MDX query window in SQL Server Management Studio and run this query. Does it behave just as poorly in the query window as it does in Proclarity? If it does, then you can focus on SSAS (kinda).
I suspect the problem is in how the MDX is attempting to assemble the Grand Totals. In the example above, you could get the same values back with a much simpler expression.
So, what options do you have? Well, always make sure you are on the latest service pack of SSAS to insure you get the best performance possible. If you are publishing these results as a "static" view in a Proclarity workbook, you could rewrite the query for better performance. Other than that, I'm not sure what else you can really do with this.
B.
|||
A colleague of mine contacted ProClarity and they said the Grand Totals display had known bugs.
I know its not the same issue, but presumably its related.
Its to do with how aggregations are handled.
