[Mondrian] ParentChild hierarchy in Mondrian 3.2

Anton Nikitin cybernelly at gmail.com
Fri Jul 2 13:41:25 EDT 2010


After some additional research I found that after switching from Oracle 
to Sybase DBMS everything started to work fine.

So it seems that your guess about floating value could be correct.

Oracle returns metadata about integer columns as java.sql.Types.NUMERIC 
(with appropriate precision and scale).
It seems that somewhere Mondrian handles it incorrectly...

Anton

02.07.2010 20:49, Anton Nikitin wrote:
> I can't agree with you about nullParentValue attribute -- this one is 
> used to indicate which member is a root (real root, not the allmember).
> Our hierarchy has a single root and it has "-1" in PARENTID column.
> However, I removed it and tried to drill into AllMember -- no effect.
>
> I removed hideMemberIf -- no effect.
>
> ANCESTORID in Oracle DDL is defined as Integer, but probably returned 
> in JDBC metadata as NUMBER (one of these funny Oracle tricks).
> I have checked SQL:  no difference when typing "1000.0" or "1000".
>
> The first things which seems to be very surprising is why it logs that 
> query returns 0 rows, while it doesn't?
> What is the logic around this output? Doesn't it simply return the 
> number of rows in ResultSet?
>
> Also 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...
>
> Thanks.
>
> Anton
>
> 01.07.2010 3:32, Julian Hyde wrote:
>> The attributes hideMemberIf and nullParentValue are usually only used 
>> in ragged hierarchies. Do the problems go away if you remove them?
>> I notice that it is joining to '... = 1000.0'. Is ANCESTORID an 
>> integer in the database? Does that query return different results if 
>> you change 1000.0 to 1000?
>> Julian
>>
>>     ------------------------------------------------------------------------
>>     *From:* mondrian-bounces at pentaho.org
>>     [mailto:mondrian-bounces at pentaho.org] *On Behalf Of *Anton Nikitin
>>     *Sent:* Wednesday, June 30, 2010 5:49 AM
>>     *To:* mondrian at pentaho.org
>>     *Subject:* [Mondrian] ParentChild hierarchy in Mondrian 3.2
>>
>>     I am experiencing some difficulties after upgrading from 3.1.2 to
>>     3.2.0 with parent-child hierarchy.
>>
>>     I have a hierarchy defined as follows:
>>
>>     <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" hideMemberIf="Never">
>>     <Closure parentColumn="ANCESTORID" childColumn="NODEID">
>>     <Table name="RISKGROUPCLOSURE" />
>>     </Closure>
>>     </Level>
>>     </Hierarchy>
>>     </Dimension>
>>
>>     And with such definition it returns empty cells if MDX includes
>>     any member from this hierarchy other than AllMember.
>>     If I comment out closure definition it starts to work correctly.
>>
>>     During analysis I found the following in Mondrian log:
>>
>>
>>     [2010-06-30 16:26:51,896] [DEBUG] (RolapUtil             )
>>     Segment.load: 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 15 ms
>>
>>     [2010-06-30 16:26:51,896] [DEBUG] [/lrm230]
>>     [OTIDO\aln at 192.168.1.171] - (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] [/lrm230]
>>     [OTIDO\aln at 192.168.1.171] - (FastBatchingCellReader$Batch)
>>     Batch.loadAggregation (millis) 15
>>     [2010-06-30 16:26:51,896] [DEBUG] [/lrm230]
>>     [OTIDO\aln at 192.168.1.171] - (FastBatchingCellReader)
>>     loadAggregation (millis): 15
>>     [2010-06-30 16:26:51,897] [DEBUG] [/lrm230]
>>     [OTIDO\aln at 192.168.1.171] - (ResultBase            )
>>     RolapResult<init>: FREE_MEMORY: 197383kb 63.82%
>>     [2010-06-30 16:26:51,897] [DEBUG] [/lrm230]
>>     [OTIDO\aln at 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.
>>
>>     Could anybody help me with it? We didn't change schema definition
>>     and database content, so it worked in 3.1.2.
>>     Any hint why closure could stop working would be much appreciated...
>>
>>     Thanks.
>>
>>     Anton Nikitin
>>
>>
>> _______________________________________________
>> Mondrian mailing list
>> Mondrian at pentaho.org
>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>    
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20100702/dc70f2f5/attachment.html 


More information about the Mondrian mailing list