[Mondrian] Re: Infinity in results with Calculated Members while using OLAP4J but works fine in JPIVOT

Venkatesh U venkatesh20 at gmail.com
Sat Jan 8 04:43:22 EST 2011


Hi,
Please ignore this query,
The issue was due to the reason that mondrian.properties file was not used
in java app while it was present in webapp. Hence aggregates were not used
and base table has null value for the queried fact.

Thanks,
Venkatesh

On Sat, Jan 8, 2011 at 10:16 AM, Venkatesh U <venkatesh20 at gmail.com> wrote:

> I am trying the below query
>
> String avgMdx = "WITH "
>                 + "SET [ExceptSegments] AS' "
>                 + "{([All NetWorkInformations],[All CallDirections],[All
> TimeOfDays],[All Locations],[All PeriodOfWeeks],[All DayOfWeeks],[All
> UOMs],[All Services],[All SubscriptionTypes])}' "
>                 + "SET [FilterDimensions] AS ' "
>                 +
> "{([NetworkInformation].[Off-Net],[PeriodOfWeek].[Weekday])}' "
>                 + "MEMBER [Measures].[AvgSpend] AS "
>                 +
> "'[Measures].[SumOfCost]/{CrossJoin([Measures].[CustomerCount],[ExceptSegments])}.Item(0)',
> "
>                 + "format_String=\"#,###.000\" "
>                 + "SET "
>                 + "[TimeDimension-CurrentSelection] AS ' "
>                 +
> "{[TimeDimension.MonthLevel].[JAN-2010]:[TimeDimension.MonthLevel].[MAR-2010]}'
> "
>                 + "SET "
>                 + "[TopLocations] AS ' "
>                 + "GENERATE(  "
>                 + "[TimeDimension-CurrentSelection] "
>                 + ", TOPCOUNT( "
>                 + "[TimeDimension.MonthLevel].CURRENTMEMBER "
>                 + "* "
>                 + "[Location].[LocationLabel].Members "
>                 + ",5,[Measures].[SumOfCost]))'  "
>                 + "MEMBER [Location].[Others] AS ' "
>                 + "[Location].[All Locations] -
> Aggregate(extract(Intersect(CrossJoin([TimeDimension.MonthLevel].CurrentMember,[Location].[LocationLabel].Members),[TopLocations]),[Location]))'
> "
>                 + "select [Measures].[AvgSpend] on 0, "
>                 + "{[TopLocations] * [FilterDimensions],
> [TimeDimension-CurrentSelection] *[Location].[Others] * [FilterDimensions]}
>  on 1 "
>                 + "from [SpendAnalytics] ";
>
>  mdxParsetest.parsedObject = mdxParsetest.parser.parseSelect(avgMdx);
>
> CellSet cSet =
> mdxParsetest.statement.executeOlapQuery(mdxParsetest.parsedObject.toString());
>         PrintWriter pw = new PrintWriter(System.out, true);
>         RectangularCellSetFormatter rcsf = new
> RectangularCellSetFormatter(true);
>         rcsf.format(cSet, pw);
>
> The results come with Infinity when i use OLAP4j
>
>                                           AvgSpend
> ======== ================= ======= ======= ========
> JAN-2010 Karongi           Off-Net Weekday
>          NO_NAME MENTIONED Off-Net Weekday
>          Huye              Off-Net Weekday
>          Nyabihu           Off-Net Weekday Infinity
>          Gakenke           Off-Net Weekday Infinity
> FEB-2010 Kicukiro          Off-Net Weekday
>          Nyamagabe         Off-Net Weekday
>          Nyaruguru         Off-Net Weekday
>          Nyagatare         Off-Net Weekday
>          Nyamasheke        Off-Net Weekday
> MAR-2010 Burera            Off-Net Weekday Infinity
>          Nyaruguru         Off-Net Weekday
>          Nyamagabe         Off-Net Weekday
>          Rubavu            Off-Net Weekday Infinity
>          Huye              Off-Net Weekday
> JAN-2010                   Off-Net Weekday Infinity
> FEB-2010                   Off-Net Weekday Infinity
> MAR-2010                   Off-Net Weekday Infinity
>
> But the Same works fine with Jpivot and MondrianQueryServlet
>
> Results:   [Measures].[AvgSpend][TimeDimension.MonthLevel].[JAN-2010][Location].[Karongi]
> [NetWorkInformation].[Off-Net] [PeriodOfWeek].[Weekday][TimeDimension.MonthLevel].[JAN-2010][Location].[NO_NAME
> MENTIONED] [NetWorkInformation].[Off-Net][PeriodOfWeek].[Weekday]
> [TimeDimension.MonthLevel].[JAN-2010] [Location].[Huye]
> [NetWorkInformation].[Off-Net] [PeriodOfWeek].[Weekday][TimeDimension.MonthLevel].[JAN-2010]
> [Location].[Nyabihu] [NetWorkInformation].[Off-Net]
> [PeriodOfWeek].[Weekday] 83.161[TimeDimension.MonthLevel].[JAN-2010][Location].[Gakenke]
> [NetWorkInformation].[Off-Net] [PeriodOfWeek].[Weekday]88.459[TimeDimension.MonthLevel].[FEB-2010]
> [Location].[Kicukiro] [NetWorkInformation].[Off-Net]
> [PeriodOfWeek].[Weekday] [TimeDimension.MonthLevel].[FEB-2010][Location].[Nyamagabe]
> [NetWorkInformation].[Off-Net] [PeriodOfWeek].[Weekday][TimeDimension.MonthLevel].[FEB-2010]
> [Location].[Nyaruguru] [NetWorkInformation].[Off-Net]
> [PeriodOfWeek].[Weekday] [TimeDimension.MonthLevel].[FEB-2010][Location].[Nyagatare]
> [NetWorkInformation].[Off-Net] [PeriodOfWeek].[Weekday][TimeDimension.MonthLevel].[FEB-2010]
> [Location].[Nyamasheke] [NetWorkInformation].[Off-Net]
> [PeriodOfWeek].[Weekday] [TimeDimension.MonthLevel].[MAR-2010][Location].[Burera]
> [NetWorkInformation].[Off-Net] [PeriodOfWeek].[Weekday]102.613[TimeDimension.MonthLevel].[MAR-2010]
> [Location].[Nyaruguru] [NetWorkInformation].[Off-Net]
> [PeriodOfWeek].[Weekday] [TimeDimension.MonthLevel].[MAR-2010][Location].[Nyamagabe]
> [NetWorkInformation].[Off-Net] [PeriodOfWeek].[Weekday][TimeDimension.MonthLevel].[MAR-2010]
> [Location].[Rubavu] [NetWorkInformation].[Off-Net][PeriodOfWeek].[Weekday]70.150
> [TimeDimension.MonthLevel].[MAR-2010] [Location].[Huye]
> [NetWorkInformation].[Off-Net] [PeriodOfWeek].[Weekday][TimeDimension.MonthLevel].[JAN-2010]
> [Location].[Others] [NetWorkInformation].[Off-Net][PeriodOfWeek].[Weekday]1,068.224
> [TimeDimension.MonthLevel].[FEB-2010] [Location].[Others]
> [NetWorkInformation].[Off-Net] [PeriodOfWeek].[Weekday]1,191.337[TimeDimension.MonthLevel].[MAR-2010]
> [Location].[Others] [NetWorkInformation].[Off-Net][PeriodOfWeek].[Weekday]1,134.190
>
> Could anyone help me to understand whats happening? I am using same
> mondrian.jar and olap4j.jar available in the jpivot, where the query returns
> proper results.
>
>
> Thanks,
> Venkatesh
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20110108/a459999e/attachment.html 


More information about the Mondrian mailing list