Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: discrepancies in MDX query result and chart output

  1. #1
    Join Date
    May 2010
    Posts
    13

    Default discrepancies in MDX query result and chart output

    Could I please kindly seek your assistance again with the following issue.

    I have a MDX query which gives me certain values, but when I put this
    same query to a graphs (jFreeChartComponent) the values I see in the
    graph are totally different.

    Here is my MDX query:

    with member [Measures].[Avg per Month] AS [Measures].
    [Quantity]/Descendants([DemographicsDim].[WA],3).count select NON
    EMPTY {[Measures].[Avg per Month]} ON COLUMNS, NON EMPTY
    {[WeekL].Members} ON ROWS from [drillgpbill] where ([ItemDim].[GPMP],
    [StateL].[WA])

    Output in summary:
    [2010].[1].[Week 3] = 1.333
    [2010].[2].[Week 1] = 1
    [2010].[2].[Week 2] = 1
    [2010].[2].[Week 4] = 10
    [2010].[3].[Week 1] = 1
    [2010].[3].[Week 2] = 2.333
    [2010].[3].[Week 3] = 1
    [2010].[3].[Week 4] = 1
    [2010].[3].[Week 5] = 1.5
    [2010].[4].[Week 2] = 2

    Then to draw graphs, I use following code:

    gpmpStateGPGenerationAverageChartDefinition =
    {
    width: 500,
    height: 250,
    chartType: function(){return cType},
    datasetType: "CategoryDataset",
    is3d: false,
    byRow: false,
    isStacked: false,
    includeLegend: true,
    domainLabelRotation: .9,
    backgroundColor: "#FFFFFF",
    plotbgcolor:"#edf7d0",
    title: "GPMP: State Average vs GP Total",
    orientation: 'vertical',
    queryType: 'mdx',
    catalog: 'solution:CDMSCube/drillgpbill.xml',
    jndi: "CDMSDrillDataMart",
    query: function(){
    var query = "with member [Measures].[Avg per
    Month] AS [Measures].[Quantity]/Descendants([DemographicsDim].
    [WA],3).count select NON EMPTY {[Measures].[Avg per Month]} ON
    COLUMNS, NON EMPTY {[WeekL].Members} ON ROWS from [drillgpbill] where
    ([ItemDim].[GPMP],[StateL].[WA])";
    return query;
    }
    }

    gpmpStateGPGenerationAverageChart =
    {
    name: "gpmpStateGPGenerationAverageChart",
    type: "jFreeChartComponent",
    chartDefinition:
    gpmpStateGPGenerationAverageChartDefinition,
    htmlObject: "gpmpStateGPGenerationAverageChart_object",
    executeAtStart: false
    }

    However, the graph plots have follwoing values:

    [2010].[1].[Week 3] = 0.222
    [2010].[2].[Week 1] = 0.056
    [2010].[2].[Week 2] = 0.056
    [2010].[2].[Week 4] = 1.111
    [2010].[3].[Week 1] = 0.056
    [2010].[3].[Week 2] = 0.389
    [2010].[3].[Week 3] = 0.167
    [2010].[3].[Week 4] = 0.056
    [2010].[3].[Week 5] = 0.167
    [2010].[4].[Week 2] = 0.222

    How can I get rid of this discrepancy?

    Thanks a lot.

  2. #2
    Join Date
    May 2010
    Posts
    13

    Default

    I did further investigations and found that when these is 'Descendants' and 'count' in a mdx, the graph output using jfreechart is different.

    This is the MDX query I used
    with member [Measures].[Number of Descendants] AS Descendants([DemographicsDim].[WA],3).count select NON EMPTY {[Measures].[Number of Descendants]} ON COLUMNS, NON EMPTY {[WeekL].Members} ON ROWS from [drillgpbill] where ([ItemDim].[GPMP],[StateL].[WA])

    MDX query output:
    [2010].[1].[Week 3] = 3
    [2010].[1].[Week 3] = 1
    [2010].[1].[Week 3] = 1
    [2010].[1].[Week 3] = 2
    [2010].[1].[Week 3] = 1
    [2010].[1].[Week 3] = 3
    [2010].[1].[Week 3] = 3
    [2010].[1].[Week 3] = 1
    [2010].[1].[Week 3] = 2
    [2010].[1].[Week 3] = 2
    [2010].[1].[Week 3] = 2
    [2010].[1].[Week 3] = 2
    [2010].[1].[Week 3] = 2
    [2010].[1].[Week 3] = 3
    [2010].[1].[Week 3] = 1



    But the graph plots 18 for all the weeks.

    Does this mean that the jfreechart interpret descendants and count in a different way?

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.