Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

ProClarity totals and MDX AGGREGATE()

Posted by thomasivarssonmalmo on July 13, 2007

In several AS2000 cubes a customer of mine had totals calculated for columns by ProClarity’s feature, Totals.
This worked great even with calculated members. My customer had reports with several budget and actual sales values on the columns, both measures and calculated member, and the dates of the last month on the rows in a table. All these days were then summed on the last row with column totals in ProClarity Professional.
When we migrated the cubes from AS2000 and AS2005 I did not think that these ProClarity Totals would be a problem but I was wrong. ProClarity also did a change with Totals between earlier releases(before SSAS2005) and started using the AGGREGATE function instead of SUM for the totals.  Using AGGREGATE was very good news because we could build totals even on calculated measures with ratios, provided that you had the SolveOrder set correctly.
But in SSAS2005 we got this problem with totals returning null.
To recreate the problem you will have to enter these three calculated members in the calculations tab in the cube editor of the Adventure Works cube project. The calculations are not exactly like the one my client used but the general idea is the same. I have added YTD only to check if PARALLELPERIOD was the problem.
AS Aggregate(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar]),
[Measures].[Internet Sales Amount]), FORMAT_STRING = "### ### ### ###",
AS SUM(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar]),
[Measures].[Internet Sales Amount]),
FORMAT_STRING = "### ### ### ###",
AS SUM(YTD([Date].[Calendar].CurrentMember),
[Measures].[Internet Sales Amount]),
FORMAT_STRING = "### ### ### ###",
Process the cube (default) and open ProClarity Professional or Management Studio. After this is finished you can copy and paste the following MDX into anyone of these two client tools.
WITH MEMBER [Date].[Calendar].[All Periods].[ Grand Total] AS
‘AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month] ) }, [Date].[Calendar] ) } }, { DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month] ) } ) )’,
 SOLVE_ORDER = 1000 
SELECT { [Measures].[Internet Sales Amount], [Measures].[ParallelPeriodSalesAggregate],
[Measures].[ParallelPeriodSalesSum], [Measures].[YTDSalesSum] } ON COLUMNS ,
{ { DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month] ) },
( [Date].[Calendar].[All Periods].[ Grand Total] ) } ON ROWS 
FROM [Adventure Works]
It is the MDX created in ProClarity Professional 6.3 when you use the column grand totals feature but it will also work in Management Studio.
You will see that the ProClarity Total ([Date].[Calendar].[All Periods].[ Grand Total]) will work for the measures, Internet Sales Amount but not for the calculated members that we have entered previously. You can see this in the second picture.
When you look at the calculated member created by the ProClarity column totals, it is a mess of advanced functions that do not make a sence. One way to learn more about what is going on is to recreate parts of the code as named sets and calculated members in the cude editors calculations tab.
You can create this as a named set:
EXISTING INTERSECT( { { EXTRACT( { DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month] ) }, [Date].[Calendar] ) } }, { DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month] ) } )
What you will see is the same months that are part of the members I have choosen in ProClarity(2004 months). I think that most of this MDX is created to secure that only unique months will be included in the calculation for totals.
Next, you can create this as a calculated member with [Date].[Calendar].[Calendar Year] as the parent in the calculations tab.
AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month] ) }, [Date].[Calendar] ) } }, { DESCENDANTS( [Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month] ) } ) )
Here you will see a member under [Date].[Calendar].[Calendar Year] that you can use together with the 2004 months on the row axis and it will return correct totals for the Internet Sales Amount Measure but nulls for the ParallelPeriod calculations that we started with.
This is the way I debug MDX. Break it down into parts and see what happens to these parts.
I have posted this problem to the Analysis Services newsgroup more than year ago and first I did not receive any answers except on less important problems in the MDX. After a while the always helpful Deepak Puri posted an answer that solved parts of the problem.
Like Deepak told me you can change to SUM instead of AGGREGATE in the calculated member that ProClarity creates ( [Grand Total])  and you will get correct totals and not null. You can see this in the first picture.
So be aware of this limitation in ProClarity Professional totals and that AGGREGATE do not work correctly when you have calculated members with time functions like YTD or PARALLELPERIOD. Other calculated members without time functions will work correctly.
So why can you create totals with (AGGREGATE) calculated members that do not include MDX time functions? Why does it work with SUM?
I hope that this behaviour will change in Katmai!

5 Responses to “ProClarity totals and MDX AGGREGATE()”

  1. Mosha said

    The problem here is that Aggregate doesn’t know which aggregation function to apply when running on top of calculated measures. One way to resolve it is to use explicit Sum, another way to resolve it is to reverse solve orders. In AS2005 it is usually not possible to have query calc members to have lower solve orders than the MDX Scripts one. Aggregate function is one of the exceptions, but this exception doesn’t work in this case. Another way to deal with it is to include SCOPE_ISOLATION=Cube.

  2. Thomas said

    Hello Mosha! It would be nice if Aggregate would be the single way to solve the totals problem. I am saying this from the point of business requirements and without knowing if it is possible to implement in Katmai.

  3. Doc said

    Hi Thomas i am having the same problem in Proclarity 6.3 (2213) and SQL 2005 SP2 with normal measures.I am using my measures on rows and have months on columns. If i check column total or "Percent of Total". It shows empty totals and even empty columns/rows i filtered. For example months with no sales.I haven’t even started adding my previous year calculations. Plz help me out

  4. Unknown said

    Percent of total can be obtained via calculated cell.
    So i write the following statement in the cube’s mdx script to refer to the ProClarity’s total to show the percent of the total for a meathure:

    create cell calculation
    for ‘[Вычисления].[Вычисления].[Процент от общего итога по номенклатуре]’
    [Вычисления].[Вычисления].&[1]/([Вычисления].[Вычисления].&[1],[Номенклатура].[Номенклатура].[Вся номенклатура].[ Суммарный итог])
    FORMAT_STRING = "Percent";

  5. Robert said

    Here is my code that I am having an issue with getting a Sub-Total. I am using PAS SP3(6.3.2222.144) and SQL Server 2008 SP2.sum({[Date].[Date].currentmember.level.members}.item(0): [Date].[Date].currentmember,[Measures].[Closing])[Measures].[Closing] is a Count measure from our Claim measure group.I typically will not just throw up code, but I have been at this for about 3 months and we are about to go into production, any help would be great.Robert

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: