[Mondrian] MONDRIAN-1280 and the behaviour of VisualTotals()

Paul Stoellberger p.stoellberger at gmail.com
Thu Oct 25 21:11:45 EDT 2012


On the general issue here is what SSAS / Analyzer do:

1) SSAS
Ok after some more reading I finally understand the concept of sub-selects in SSAS better.
It seems like by using the sub-select they overcome the problem of having visual totals and selecting only specific members in a very elegant way.

This is a good explanation of it: http://sqlblog.com/blogs/mosha/archive/2006/11/12/slicer-and-axis-interaction-in-mdx-part-2-implicit-exists.aspx

So for example you can do a query like:
SELECT
    [Measures].[Unit Sales] ON COLUMNS,
    { [Product].[Product Family].Members, [Product].[Product Department].Members } ON ROWS
   FROM
      ( SELECT [Product].[Drink].[Dairy] ON COLUMNS FROM [Sales] )

This will not only take care of the VisualTotals but also restrict the space to Product.Drink.Dairy.
An equivalent of todays mondrian mdx would be:

SELECT
    [Measures].[Unit Sales] ON COLUMNS,
    VisualTotals(Hierarchize(Exists(
	{ [Product].[Product Family].Members, [Product].[Product Department].Members }
	, { [Product].[Drink].[Dairy] }
     ))) ON ROWS
   FROM
	[Sales]


Is there any plan for allowing sub selects like that in mondrian?
SSAS seems to support it since SSAS 2005 at least.

2) Analyzer
It seems like Benny (Analyzer) is creating new calculated members within the levels to create visual totals.
So for each parent member selected in the query there is a separate Aggregate() function. As you add levels they get more and more, so you can easily end up with that kind of mdx: http://pastebin.com/4BH6hy2n (quite verbose and ugly)

This is practically the same thing as VisualTotals() is using internally to calculate the totals, its just hidden in a nice MDX function.
Thats why I like it so much, it fits perfectly my needs.


Do you have some other input on visual totals?
This is one of the most requested features and before I start doing it all manually I would much rather prefer an MDX solution.

-Paul


On Oct 26, 2012, at 2:10 AM, Paul Stoellberger wrote:

> Since I'm trying to fix VisualTotals() I would like to know what the desired behaviour is. Especially regarding the order in the set.
> 
> MSDN says:
> Totals are visually totaled for sets ordered in hierarchy order. If the order of members in sets breaks the hierarchy, results are not visual totals. For example, VisualTotals (USA, WA, CA, Seattle) does not return WA as Seattle, but rather returns the values for WA, CA, and Seattle, then totals these values as the visual total for USA, counting the sales for Seattle twice.
> 
> So this means that VisualTotals() will only work if they are ordered. I disagree but I can live with that.
> What I don't understand is what happens when I execute it on a set thats Hierarchize(<set>, POST). 
> 
> Technically the set is ordered hierarchically, just in the reverted order.
> Should VisualTotals work than as expected, summing up the preceding descendants?
> If so, can we find out using the Exp[] if a hierarchize was applied and if so in which direction?
> 
> I'm a bit surprised that they have designed it that way.
> This makes this nice little feature less usable than anticipated.
> 
> I assume you want to keep it close to SSAS and stick to the same behaviour?
> 
> -Paul
> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20121026/843506b4/attachment.html 


More information about the Mondrian mailing list