[Mondrian] ParentChild hierarchy in Mondrian 3.2

Anton Nikitin cybernelly at gmail.com
Tue Jul 6 07:09:26 EDT 2010


I have created a new issue: http://jira.pentaho.com/browse/MONDRIAN-769

Anton

02.07.2010 21:41, Anton Nikitin wrote:
> 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/20100706/bc298884/attachment.html 


More information about the Mondrian mailing list