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

Pedro Alves pmgalves at gmail.com
Fri Oct 26 03:21:39 EDT 2012


Subselects on mdx? Spooky!!

On Friday, October 26, 2012, Paul Stoellberger wrote:

> 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/25824fa8/attachment-0001.html 


More information about the Mondrian mailing list