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

Venkatesh U venkatesh20 at gmail.com
Fri Jan 7 23:46:07 EST 2011


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/84208860/attachment.html 


More information about the Mondrian mailing list