Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Weird MDX result set - Total showing instead of values

  1. #1
    Join Date
    Aug 2008
    Posts
    563

    Default Weird MDX result set - Total showing instead of values



    The problems shows up in PRD as well as in CDA. The sample query shown below uses 3 dimensions: date, brand group and brand (but the problem is not restricted to these dimensions). In PRD I used the standard Pentaho Analysis data source.


    The problem is that the brand group column will just show one value: Total. However, Saiku shows proper values. I’ve been investigating this problem for several hours but haven’t yet come to a conclusion as to why this is happening:


    SELECT
    {[Date.Weekly Calendar].[2014].[35] : [Date.Weekly Calendar].[2014].[39]} ON COLUMNS,
    NON EMPTY(
    { [Brand Group].Members} * {[Brand].Members }
    ) ON ROWS
    FROM [Subscriber Base]
    WHERE [Measures].[Subscribers]



    No brand group names are displayed, all just labeled ‘Total'.


    This works however:
    SELECT
    {[Date.Weekly Calendar].[2014].[35] : [Date.Weekly Calendar].[2014].[39]} ON COLUMNS,
    NON EMPTY(
    { [Brand Group].[Brand Group].Members} * {[Brand].Members }
    ) ON ROWS
    FROM [Subscriber Base]
    WHERE [Measures].[Subscribers]




    So in this case we do not have the total for brand group and brand.


    This query works as well:


    SELECT
    {[Date.Weekly Calendar].[2014].[35] : [Date.Weekly Calendar].[2014].[39]} ON COLUMNS,
    NON EMPTY(
    Union ( { [Brand Group].[Brand Group].Members} * {[Brand].[Total], [Brand].[Brand].Members} , { [Brand Group].[Total] } * {Brand.Total})
    ) ON ROWS
    FROM [Subscriber Base]
    WHERE [Measures].[Subscribers]




    So why does the first one not work as expected? The result set looks wrong … is this down to how I wrote the query or what exactly is causing this?


    Another example:


    SELECT
    {[Date.Weekly Calendar].[2014].[35] : [Date.Weekly Calendar].[2014].[39]} ON COLUMNS,
    NON EMPTY(
    Union (
    [Brand Group].[Total] * [Brand].[Total]
    , [Brand Group].Children * {[Brand].[Total], [Brand].Children}
    )
    ) ON ROWS
    FROM [Subscriber Base]
    WHERE [Measures].[Subscribers]



    Brand Group column doesn’t show proper values, only ‘Total’ value.



    Slightly changing the query by mentioning the totals last in the UNION makes PRD/CDA happy:
    SELECT
    {[Date.Weekly Calendar].[2014].[35] : [Date.Weekly Calendar].[2014].[39]} ON COLUMNS,
    NON EMPTY(
    Union (
    [Brand Group].Children * {[Brand].[Total], [Brand].Children}
    -- totals have to be mentioned last as otherwise PRD will not display some col values correctly
    , [Brand Group].[Total] * [Brand].[Total]
    )
    ) ON ROWS
    FROM [Subscriber Base]
    WHERE [Measures].[Subscribers]



    This time the Brand Group column displays the proper values.



    All the ‘wrong' results show a similar pattern: not every row has a Brand (All) and Brand Group value (see attached screenshot). Also the column positions are different.


    Name:  Totals-shown.png
Views: 23
Size:  17.8 KB


    Do you have any ideas why this is happening?

    I tried similar queries on other cubes and I get a similar behaviour.


    Environment: PRD 5.2
    Best regards,
    Diethard
    ===============
    Visit my Pentaho blog which offers some tutorials mainly on Kettle, Report Designer and Mondrian
    ===============

  2. #2
    Join Date
    Aug 2008
    Posts
    563

    Default

    I created following Jira case.

    Ok, I seem to have figured out a workaround, but this one is ugly. It seems like you have to prepare one layer at a time, starting with the most detailed layer and then working your way upwards.
    Layer: details, subtotals, totals
    The most detailed layer has to be listed first otherwise PRD messes up the values.


    Example

    SELECT
    {[Date.Weekly Calendar].[2014].[35] : [Date.Weekly Calendar].[2014].[39]} ON COLUMNS,
    NON EMPTY(
    UNION (
    UNION (
    UNION (
    UNION (
    { [Brand Group].Children } * { [IMSI Range].Children } * { [Service].Children } * { [Brand].Children }
    , { [Brand Group].Children } * { [IMSI Range].Children } * { [Service].Children } * { [Brand].[Total] }
    )
    , { [Brand Group].Children } * { [IMSI Range].Children } * { [Service].[Total] } * { [Brand].[Total] }
    )
    , { [Brand Group].Children } * { [IMSI Range].[Total] } * { [Service].[Total] } * { [Brand].[Total] }
    )
    , { [Brand Group].[Total] } * { [IMSI Range].[Total] } * { [Service].[Total] } * { [Brand].[Total] }
    )
    )
    ON ROWS
    FROM [Subscriber Base]
    WHERE [Measures].[Subscribers]
    Last edited by diddy; 10-14-2014 at 05:30 AM.
    Best regards,
    Diethard
    ===============
    Visit my Pentaho blog which offers some tutorials mainly on Kettle, Report Designer and Mondrian
    ===============

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.