Pentaho Analysis - Mondrian
  1. Pentaho Analysis - Mondrian
  2. MONDRIAN-8

Problem getting children in hierarchy based on join

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Severe Severe
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.

      Description

      The following MDX causes an exception

      select {[Measures].[Unit Sales], [Measures].[Store
      Cost], [Measures].[Store Sales]} ON columns,
        ToggleDrillState({([Promotion Media].[All Media].
      [Radio], [Product].[All Products].[Drink].[Alcoholic
      Beverages]),
        ([Promotion Media].[All Media].[Radio], [Product].[All
      Products].[Drink].[Beverages]),
        ([Promotion Media].[All Media].[Radio], [Product].[All
      Products].[Drink].[Beverages].[Carbonated Beverages]),
        ([Promotion Media].[All Media].[Radio], [Product].[All
      Products].[Drink].[Beverages].[Drinks]),
        ([Promotion Media].[All Media].[Radio], [Product].[All
      Products].[Drink].[Beverages].[Drinks].[Flavored Drinks]),
        ([Promotion Media].[All Media].[Radio], [Product].[All
      Products].[Drink].[Beverages].[Hot Beverages]),
        ([Promotion Media].[All Media].[Radio], [Product].[All
      Products].[Drink].[Beverages].[Pure Juice Beverages]),
        ([Promotion Media].[All Media].[Radio], [Product].[All
      Products].[Drink].[Dairy]),
        ([Promotion Media].[All Media].[TV], [Product].[All
      Products].[Drink].[Alcoholic Beverages]),
        ([Promotion Media].[All Media].[TV], [Product].[All
      Products].[Drink].[Beverages]),
        ([Promotion Media].[All Media].[TV], [Product].[All
      Products].[Drink].[Beverages].[Carbonated Beverages]),
        ([Promotion Media].[All Media].[TV], [Product].[All
      Products].[Drink].[Beverages].[Drinks]),
        ([Promotion Media].[All Media].[TV], [Product].[All
      Products].[Drink].[Beverages].[Drinks].[Flavored Drinks]),
        ([Promotion Media].[All Media].[TV], [Product].[All
      Products].[Drink].[Beverages].[Hot Beverages]),
        ([Promotion Media].[All Media].[TV], [Product].[All
      Products].[Drink].[Beverages].[Pure Juice Beverages]),
        ([Promotion Media].[All Media].[TV], [Product].[All
      Products].[Drink].[Dairy]),
        ([Promotion Media].[All Media].[Sunday Paper],
      [Product].[All Products].[Drink].[Alcoholic Beverages]),
        ([Promotion Media].[All Media].[Sunday Paper],
      [Product].[All Products].[Drink].[Beverages]),
        ([Promotion Media].[All Media].[Sunday Paper],
      [Product].[All Products].[Drink].[Beverages].[Carbonated
      Beverages]),
        ([Promotion Media].[All Media].[Sunday Paper],
      [Product].[All Products].[Drink].[Beverages].[Drinks]),
        ([Promotion Media].[All Media].[Sunday Paper],
      [Product].[All Products].[Drink].[Beverages].[Drinks].
      [Flavored Drinks]),
        ([Promotion Media].[All Media].[Sunday Paper],
      [Product].[All Products].[Drink].[Beverages].[Hot
      Beverages]),
        ([Promotion Media].[All Media].[Sunday Paper],
      [Product].[All Products].[Drink].[Beverages].[Pure Juice
      Beverages]),
        ([Promotion Media].[All Media].[Sunday Paper],
      [Product].[All Products].[Drink].[Dairy]),
        ([Promotion Media].[All Media].[Street Handout],
      [Product].[All Products].[Drink].[Alcoholic Beverages]),
        ([Promotion Media].[All Media].[Street Handout],
      [Product].[All Products].[Drink].[Beverages]),
        ([Promotion Media].[All Media].[Street Handout],
      [Product].[All Products].[Drink].[Beverages].[Carbonated
      Beverages]),
        ([Promotion Media].[All Media].[Street Handout],
      [Product].[All Products].[Drink].[Beverages].[Drinks]),
        ([Promotion Media].[All Media].[Street Handout],
      [Product].[All Products].[Drink].[Beverages].[Drinks].
      [Flavored Drinks]),
        ([Promotion Media].[All Media].[Street Handout],
      [Product].[All Products].[Drink].[Beverages].[Hot
      Beverages]),
        ([Promotion Media].[All Media].[Street Handout],
      [Product].[All Products].[Drink].[Beverages].[Pure Juice
      Beverages]),
        ([Promotion Media].[All Media].[Street Handout],
      [Product].[All Products].[Drink].[Dairy])},
        {[Product].[All Products].[Drink].[Beverages].[Drinks].
      [Flavored Drinks]}) ON rows
      from [Sales]
      where ([Time].[1997])

      shorter, Java:
          String mdxQuery =
        "select {[Measures].[Unit Sales], [Measures].[Store
      Cost], [Measures].[Store Sales]} ON columns,"+
        "ToggleDrillState({"+
        "([Promotion Media].[All Media].[Radio], [Product].[All
      Products].[Drink].[Beverages].[Drinks].[Flavored
      Drinks])"+
        "}, {[Product].[All Products].[Drink].[Beverages].
      [Drinks].[Flavored Drinks]}) ON rows "+
        "from [Sales] where ([Time].[1997])";

      works fine with Microsoft MDX

      the call stack is:

      com.tonbeller.jpivot.olap.model.OlapException:
      mondrian.resource.ChainableRuntimeException: Internal
      error: while building member cache; sql=[select
      `product`.`brand_name` as `c0` from `product` as
      `product` where `product_class`.`product_subcategory`
      = 'Flavored Drinks' and
      `product_class`.`product_category` = 'Drinks' and
      `product_class`.`product_department` = 'Beverages' and
      `product_class`.`product_family` = 'Drink' group by
      `product`.`brand_name` order by `product`.`brand_name`]
      com.tonbeller.jpivot.olap.model.OlapException:
      mondrian.resource.ChainableRuntimeException: Internal
      error: while building member cache; sql=[select
      `product`.`brand_name` as `c0` from `product` as
      `product` where `product_class`.`product_subcategory`
      = 'Flavored Drinks' and
      `product_class`.`product_category` = 'Drinks' and
      `product_class`.`product_department` = 'Beverages' and
      `product_class`.`product_family` = 'Drink' group by
      `product`.`brand_name` order by `product`.`brand_name`]
       at
      com.tonbeller.jpivot.mondrian.MondrianResult.executeQu
      ery(MondrianResult.java:36)
       at
      com.tonbeller.jpivot.mondrian.MondrianModel.getResult
      (MondrianModel.java:114)
       at com.tonbeller.jpivot.mondrian.HHTest.main
      (HHTest.java:111)
      Caused by:
      mondrian.resource.ChainableRuntimeException: Internal
      error: while building member cache; sql=[select
      `product`.`brand_name` as `c0` from `product` as
      `product` where `product_class`.`product_subcategory`
      = 'Flavored Drinks' and
      `product_class`.`product_category` = 'Drinks' and
      `product_class`.`product_department` = 'Beverages' and
      `product_class`.`product_family` = 'Drink' group by
      `product`.`brand_name` order by `product`.`brand_name`]
       at
      mondrian.olap.MondrianResource.newInternal
      (MondrianResource.java:45)
       at
      mondrian.rolap.SqlMemberSource.getMemberChildren
      (SqlMemberSource.java:551)
       at
      mondrian.rolap.SqlMemberSource.getMemberChildren
      (SqlMemberSource.java:518)
       at
      mondrian.rolap.SmartMemberReader.readMemberChildre
      n(SmartMemberReader.java:235)
       at
      mondrian.rolap.SmartMemberReader.getMemberChildren
      (SmartMemberReader.java:154)
       at
      mondrian.rolap.SmartMemberReader.getMemberChildren
      (SmartMemberReader.java:127)
       at
      mondrian.rolap.RolapHierarchy.getChildMembers
      (RolapHierarchy.java:331)
       at
      mondrian.rolap.RolapMember.getMemberChildren
      (RolapMember.java:70)
       at
      mondrian.olap.fun.BuiltinFunTable$54.evaluate
      (BuiltinFunTable.java:1994)
       at mondrian.olap.fun.FunkFunDef.evaluate
      (MultiResolver.java:93)
       at mondrian.rolap.RolapEvaluator.xx
      (RolapEvaluator.java:116)
       at mondrian.olap.FunCall.evaluate
      (FunCall.java:244)
       at mondrian.rolap.RolapResult.executeAxis
      (RolapResult.java:154)
       at mondrian.rolap.RolapResult.<init>
      (RolapResult.java:70)
       at mondrian.rolap.RolapConnection.execute
      (RolapConnection.java:99)
       at
      com.tonbeller.jpivot.mondrian.MondrianResult.executeQu
      ery(MondrianResult.java:32)
       ... 2 more
      Caused by: java.sql.SQLException: [Microsoft][ODBC
      Microsoft Access Driver] 4 Parameter wurden erwartet,
      aber es wurden zu wenig Parameter �bergeben.
       at mondrian.rolap.RolapUtil.executeQuery
      (RolapUtil.java:151)
       at
      mondrian.rolap.SqlMemberSource.getMemberChildren
      (SqlMemberSource.java:535)
       ... 16 more

        Activity

        Hide
        Mondrian Importer User added a comment -
        {jhyde}, 12/02/2002: resolution_id, 100 |
        {jhyde}, 12/02/2002: assigned_to, 100 |
        {jhyde}, 12/02/2002: summary, Problem with ToggleDrillState and Tuple |
        {jhyde}, 12/02/2002: status_id, 1 |
        {jhyde}, 12/02/2002: close_date, 0
        Show
        Mondrian Importer User added a comment - {jhyde}, 12/02/2002: resolution_id, 100 | {jhyde}, 12/02/2002: assigned_to, 100 | {jhyde}, 12/02/2002: summary, Problem with ToggleDrillState and Tuple | {jhyde}, 12/02/2002: status_id, 1 | {jhyde}, 12/02/2002: close_date, 0
        Hide
        Mondrian Importer User added a comment -
        {jhyde}, 12/02/2002: Logged In: YES
        user_id=312935

        Minimal test-case is

        select {[Measures].[Unit Sales]} ON columns,
        {[Product].[All Products].[Drink].[Beverages].[Drinks].
        [Flavored Drinks].children} ON rows
        from [Sales]

        The problem occurs because the children of [Flavored Drinks]
        are sourced from a different table, product as opposed to
        product_class, yet the SqlMemberSource forgets to join the
        table in.

        Fixed in change 240.
        Show
        Mondrian Importer User added a comment - {jhyde}, 12/02/2002: Logged In: YES user_id=312935 Minimal test-case is select {[Measures].[Unit Sales]} ON columns, {[Product].[All Products].[Drink].[Beverages].[Drinks]. [Flavored Drinks].children} ON rows from [Sales] The problem occurs because the children of [Flavored Drinks] are sourced from a different table, product as opposed to product_class, yet the SqlMemberSource forgets to join the table in. Fixed in change 240.

          People

          • Assignee:
            Julian Hyde
            Reporter:
            hhaas
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: