[Mondrian] Weird MDX result set in CDA and PRD

Matt Campbell mcampbell at pentaho.com
Tue Oct 14 15:44:31 EDT 2014


Hi Diethard,
I ran a quick test and I'm seeing some funkiness with PRD tabularizing MDX results when a set includes members from multiple levels of the same dim, as in your queries.  Conversion to a table model appears to fall down under certain circumstances.  I created PRD-5278 for this-feel free to add your example to it as well.

(BTW- this doesn't appear to be a new problem.  I saw the same behavior w/ 5.0 and 5.1)

-matt

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On Behalf Of Diethard Steiner
Sent: Monday, October 13, 2014 1:31 PM
To: Mondrian developer mailing list
Subject: [Mondrian] Weird MDX result set in CDA and PRD

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.

[cid:image001.png at 01CFE7C5.B7B8AA20]
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/20141014/fd17e12e/attachment-0001.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 18192 bytes
Desc: image001.png
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20141014/fd17e12e/attachment-0001.png 


More information about the Mondrian mailing list