[Mondrian] Weird MDX result set in CDA and PRD

Diethard Steiner diethard.steiner at gmail.com
Tue Oct 14 17:13:14 EDT 2014


Thanks a lot Matt! I did google for this problem and only found one mention on the Pentaho forum and one jira case ( although not stating completely the same problem ). I am quite astound that this problem has not been observed by more users - this was also the reason why I originally thought that I was doing something wrong. 
I created this morning a jira case as well and sent Thomas additional details. He could replicate the problem as well. I'll link your jira case to mine. Many thanks again for your help - this problem has kept me busy for the last 3 days and it is good to know now that it is indeed a bug.
Best regards,
Diethard

> On 14 Oct 2014, at 20:44, Matt Campbell <mcampbell at pentaho.com> wrote:
> 
> 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.
>  
> <image001.png>
> 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
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20141014/721352fa/attachment.html 


More information about the Mondrian mailing list