[Mondrian] ParentChild hierarchy in Mondrian 3.2

Anton Nikitin cybernelly at gmail.com
Fri Jul 2 12:49:21 EDT 2010


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/ee8cfc2d/attachment.html 


More information about the Mondrian mailing list