[Mondrian] Weird MDX result set in CDA and PRD

Diethard Steiner diethard.steiner at gmail.com
Mon Oct 13 13:31:10 EDT 2014


Good evening,

Although this is not really the right mailing list for this topic, quite likely some of you have created a PRD Report or CDE dashboard based on the result of a MDX query. As you might well know, CDA uses the PRD libs for data access.

The Problem
==========

Manifests its in CDA as well as in PRD. 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 and in CDA the ‘classic’ output method. Note: I have to use the ‘classic’ output method in CDA because I need the long version of the column headers to extract additional info (as CDA does not support Mondrian level captions).

The problem is the brand group column will just show one value: Total. However, Saiku as well as the CDA legacy MDX data source show 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.


Sorry for not sharing any more detailed screenshots, but I am currently working with the clients' data.

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 and a fairly modern version of c-tools.

Thanks,
Diethard
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20141013/36f425c0/attachment-0001.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Screen Shot 2014-10-13 at 18.13.53.png
Type: image/png
Size: 18192 bytes
Desc: not available
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20141013/36f425c0/attachment-0001.png 


More information about the Mondrian mailing list