Pentaho Analysis - Mondrian

Problem getting children in hierarchy based on join

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Severe Severe
  • Resolution: Fixed
  • Affects Version/s: None
  • Fix Version/s: None
  • Component/s: None
  • Labels:
    None

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

Ascending order - Click to sort in descending order
Hide
Permalink
Mondrian Importer User added a comment - 02/Dec/02 8:36 AM
{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 - 02/Dec/02 8:36 AM {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
Permalink
Mondrian Importer User added a comment - 02/Dec/02 8:36 AM
{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 - 02/Dec/02 8:36 AM {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

Vote (0)
Watch (0)

Dates

  • Created:
    29/Nov/02 3:36 PM
    Updated:
    02/Dec/02 8:36 AM
    Resolved:
    02/Dec/02 8:36 AM
Atlassian JIRA (4.3.x) | Report a problem
Powered by a free Atlassian JIRA open source license for Pentaho.org. Try JIRA - bug tracking software for your team.