Uploaded image for project: 'Pentaho Analysis - Mondrian'
  1. Pentaho Analysis - Mondrian
  2. MONDRIAN-769

Closure table doesn't work when connecting to Oracle

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Open
    • Severity: Medium
    • Resolution: Unresolved
    • Affects Version/s: 3.2.0 GA (3.6.0 GA Suite Release)
    • Fix Version/s: Not Planned
    • 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

      We have found an issue when connecting Mondrian 3.2.0.13661 to Oracle 10g Express database.

      Our schema defines the following parent-child hierarchy:

      <Dimension name="Liquidity Risk Group">
      <Hierarchy hasAll="true" allMemberName="All Nodes" primaryKey="NODEID">
      <Table name="LIQUIDITYRISKGROUP"/>

      <Level approxRowCount="100" name="Risk Group" column="NODEID" nameColumn="RISKGROUPNAME"
      parentColumn="PARENT" nullParentValue="-1" type="Numeric" uniqueMembers="true"
      levelType="Regular">
      <Closure parentColumn="ANCESTORID" childColumn="NODEID">
      <Table name="RISKGROUPCLOSURE" />
      </Closure>
      </Level>
      </Hierarchy>
      </Dimension>

      The tables are defined in database as follows:

      CREATE TABLE LiquidityRiskGroup (
      NodeId number(22) NOT NULL,
      Parent number(22) NOT NULL,
      RiskGroupName varchar2(255) NOT NULL,
      DimensionValue varchar2(255) NOT NULL,
      DefaultCollector number(1) NOT NULL, – boolean
      Cumulative number(1) NOT NULL, – boolean
      Behavior varchar2(255) NOT NULL,
      AssetLiability varchar2(255) NOT NULL – Possible values are (A|L|CA|CL)
      );
      ALTER TABLE LiquidityRiskGroup ADD CONSTRAINT PK_LiquidityRiskGroup PRIMARY KEY (
      NodeId
      );
      CREATE TABLE RiskGroupClosure (
      NodeId integer NOT NULL,
      AncestorId integer NOT NULL,
      Distance integer NOT NULL
      );
      ALTER TABLE RiskGroupClosure ADD CONSTRAINT PK_RiskGroupClosure PRIMARY KEY (
      NodeId,
      AncestorId
      ) ;

      With this configuration any MDX query which includes regular (not all-member) members from this parent-child hierarchy returns empty results.

      The log says:

      [2010-06-30 16:26:51,896] [DEBUG] - (RolapUtil ) Segment.load: done executing sql [select "RISKGROUPCLOSURE"."ANCESTORID" as "c0", sum("DAILYCFBYRISKGROUPHIERARCHY"."STRESSCFAMOUNTPLUS"+"DAILYCFBYRISKGROUPHIERARCHY"."STRESSCFAMOUNTMINUS") as "m0" from "RISKGROUPCLOSURE" "RISKGROUPCLOSURE", "DAILYCFBYRISKGROUPHIERARCHY" "DAILYCFBYRISKGROUPHIERARCHY" where "DAILYCFBYRISKGROUPHIERARCHY"."NODEID" = "RISKGROUPCLOSURE"."NODEID" and "RISKGROUPCLOSURE"."ANCESTORID" = 1000.0 group by "RISKGROUPCLOSURE"."ANCESTORID"], exec+fetch 15 ms, 0 rows

      [2010-06-30 16:26:51,896] [DEBUG] - (FastBatchingCellReader$Batch) Batch.loadAggregation (millis) 15
      [2010-06-30 16:26:51,896] [DEBUG] - (FastBatchingCellReader) loadAggregation (millis): 15
      [2010-06-30 16:26:51,897] [DEBUG] - (ResultBase ) RolapResult<init>: FREE_MEMORY: 197383kb 63.82%
      [2010-06-30 16:26:51,897] [DEBUG] [/lrm230] [OTIDO\aln@192.168.1.171] - (mdx ) 1: exec: 18 ms

      It reports that query returned 0 rows (if I understand it correctly).
      But when I tried to execute the same SQL manually against my database it returned one row.

      The following things were also discovered:

      1. After removing closure definition from a schema the hierarchy starts to work correctly.

      2. There were no such problem in Mondrian 3.1.2

      3. Everything works correctly when connecting Mondrian 3.2 to Sybase database instead of Oracle.

      The strange point is that in log file we see that Mondrian interpretes ID value from closure table as a floating point number.
      Oracle always return integer data types as java.sql.Types.NUMERIC in JDBC metadata (with appropriate precision and scale).
      So it seems that somewhere Mondrian handles it incorrectly...

      I noticed a recent bug: http://jira.pentaho.com/browse/MONDRIAN-767
      Although it talks about virtual cubes, the description looks very similar to my case...

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            anikitin anikitin
            Votes:
            1 Vote for this issue
            Watchers:
            0 Start watching this issue

              Dates

              Created:
              Updated: