[Mondrian] PC hierarchy and visibility of MemberReader and MemberSource

damien hostin damien.hostin at axege.com
Mon Dec 3 12:08:42 EST 2012



So, here is the case with foodmart.xml

I run those updates in pg_admin, in order to rename the employe id from 
1 to 99999 :

update employee
set employee_id='99999'
where employee_id='1'

update employee
set supervisor_id='99999'
where supervisor_id='1'

update employee_closure set employee_id='99999'
where employee_id='1'

update employee_closure set supervisor_id='99999'
where supervisor_id='1'


With hasAll=true on the employee hirarchy, it seems to have no problems.
But as our hierarchies already have an "all" member, we decided to set 
hasAll=false.
(This also allow us to have a correct total, but I don't know if it is a 
side effect or a feature)


If you set the hierarchy in FoodMart.xml that way :

<Dimension name="Employees" foreignKey="employee_id">
<Hierarchy hasAll="false" defaultMember="Sheri Nowmer"
primaryKey="employee_id">
<Table name="employee"/>
<Level name="Employee Id" type="Numeric" uniqueMembers="true"
column="employee_id" parentColumn="supervisor_id"
nameColumn="full_name" nullParentValue="0">
<Closure parentColumn="supervisor_id" childColumn="employee_id">
<Table name="employee_closure"/>
</Closure>
<Property name="Marital Status" column="marital_status"/>
<Property name="Position Title" column="position_title"/>
<Property name="Gender" column="gender"/>
<Property name="Salary" column="salary"/>
<Property name="Education Level" column="education_level"/>
<Property name="Management Role" column="management_role"/>
</Level>
</Hierarchy>
</Dimension>

and run in a short class that directly query mondrian the following MDX :

select {[Measures].[Org Salary], [Measures].[Count]} ON COLUMNS,
Hierarchize(Union(Crossjoin({[Position].[All Position]}, 
{[Employees].[Sheri Nowmer]}), Crossjoin({[Position].[All Position]}, 
[Employees].[Sheri Nowmer].Children))) ON ROWS
from [HR]
where [Time].[1997]


you get in console :
...
DEBUG [main] - Util.lookupCompound: parent.name=Employees, 
category=member, names=[Sheri Nowmer]
DEBUG [main] - 2: SqlTupleReader.readTuples [[Employees].[Employee Id]]: 
executing sql [select "employee"."supervisor_id" as "c0", 
"employee"."employee_id" as "c1", "employee"."full_name" as "c2", 
"employee"."marital_status" as "c3", "employee"."position_title" as 
"c4", "employee"."gender" as "c5", "employee"."salary" as "c6", 
"employee"."education_level" as "c7", "employee"."management_role" as 
"c8" from "employee" as "employee" group by "employee"."supervisor_id", 
"employee"."employee_id", "employee"."full_name", 
"employee"."marital_status", "employee"."position_title", 
"employee"."gender", "employee"."salary", "employee"."education_level", 
"employee"."management_role" order by "employee"."supervisor_id" ASC 
NULLS FIRST, "employee"."employee_id" ASC NULLS LAST]
...
DEBUG [main] - 2: , exec 23 ms
DEBUG [main] - SqlTupleReader.readTuples [[Employees].[Employee Id]]: 
executing sql [select "employee"."supervisor_id" as "c0", 
"employee"."employee_id" as "c1", "employee"."full_name" as "c2", 
"employee"."marital_status" as "c3", "employee"."position_title" as 
"c4", "employee"."gender" as "c5", "employee"."salary" as "c6", 
"employee"."education_level" as "c7", "employee"."management_role" as 
"c8" from "employee" as "employee" group by "employee"."supervisor_id", 
"employee"."employee_id", "employee"."full_name", 
"employee"."marital_status", "employee"."position_title", 
"employee"."gender", "employee"."salary", "employee"."education_level", 
"employee"."management_role" order by "employee"."supervisor_id" ASC 
NULLS FIRST, "employee"."employee_id" ASC NULLS LAST], exec 23 ms
WARN [main] - The level [Employees].[Employee Id] makes use of the 
'parentColumn' attribute, but a parent member for key 2 is missing. This 
can be due to the usage of the NativizeSet MDX function with a list of 
members form a parent-child hierarchy that doesn't include all parent 
members in its definition. Using NativizeSet with a parent-child 
hierarchy requires the parent members to be included in the set, or the 
hierarchy cannot be properly built natively.
WARN [main] - The level [Employees].[Employee Id] makes use of the 
'parentColumn' attribute, but a parent member for key 2 is missing. This 
can be due to the usage of the NativizeSet MDX function with a list of 
members form a parent-child hierarchy that doesn't include all parent 
members in its definition. Using NativizeSet with a parent-child 
hierarchy requires the parent members to be included in the set, or the 
hierarchy cannot be properly built natively.
...
etc, lot of warn messages
...
DEBUG [main] - 2: , exec+fetch 135 ms, 1155 rows
DEBUG [main] - SqlTupleReader.readTuples [[Employees].[Employee Id]]: 
done executing sql [select "employee"."supervisor_id" as "c0", 
"employee"."employee_id" as "c1", "employee"."full_name" as "c2", 
"employee"."marital_status" as "c3", "employee"."position_title" as 
"c4", "employee"."gender" as "c5", "employee"."salary" as "c6", 
"employee"."education_level" as "c7", "employee"."management_role" as 
"c8" from "employee" as "employee" group by "employee"."supervisor_id", 
"employee"."employee_id", "employee"."full_name", 
"employee"."marital_status", "employee"."position_title", 
"employee"."gender", "employee"."salary", "employee"."education_level", 
"employee"."management_role" order by "employee"."supervisor_id" ASC 
NULLS FIRST, "employee"."employee_id" ASC NULLS LAST], exec+fetch 135 
ms, 1155 rows
DEBUG [Mondrian Monitor] - SqlStatementEndEvent(2)
DEBUG [main] - HierarchyBase.lookupChild: name=Employees, 
childname=[Sheri Nowmer] returning elementname=Sheri Nowmer
DEBUG [main] - Util.lookupCompound: found child.name=Sheri Nowmer, 
child.class=mondrian.rolap.SqlMemberSource$RolapParentChildMember
DEBUG [main] - Normal cardinality for [Employees]
DEBUG [main] - HierarchyUsage: kind=PRIVATE, 
hierarchyName=Employees$Closure, fullName=Employees, 
foreignKey=employee_id, source=null, level=null, name=Employees, 
cubeDim=mondrian.olap.MondrianDef$Dimension
DEBUG [main] - HierarchyUsage: kind=PRIVATE, 
hierarchyName=Employees$Closure, fullName=Employees, 
foreignKey=employee_id, source=null, level=null, name=Employees, 
cubeDim=mondrian.olap.MondrianDef$Dimension
DEBUG [main] - RolapCube.createUsage: cube=HR, 
hierarchy=Employees$Closure, usage=HierarchyUsage: kind=PRIVATE, 
hierarchyName=Employees$Closure, fullName=Employees, 
foreignKey=employee_id, source=null, level=null, name=Employees
DEBUG [main] - RolapCube.createUsage: register HierarchyUsage: 
kind=PRIVATE, hierarchyName=Employees$Closure, fullName=Employees, 
foreignKey=employee_id, source=null, level=null, name=Employees
DEBUG [main] - Normal cardinality for [Employees$Closure]
DEBUG [main] - Normal cardinality for [Employees$Closure]
DEBUG [main] - RolapCube.getUsages: name=Employees$Closure
...
etc
...
Axis #0:
{[Time].[1997]}
Axis #1:
{[Measures].[Org Salary]}
{[Measures].[Count]}
Axis #2:
{[Position].[All Position], [Employees].[Sheri Nowmer].[Derrick Whelply]}
{[Position].[All Position], [Employees].[Sheri Nowmer].[Michael Spence]}
{[Position].[All Position], [Employees].[Sheri Nowmer].[Maya Gutierrez]}
{[Position].[All Position], [Employees].[Sheri Nowmer].[Roberta Damstra]}
{[Position].[All Position], [Employees].[Sheri Nowmer].[Rebecca Kanagaki]}
{[Position].[All Position], [Employees].[Sheri Nowmer].[Darren Stanz]}
{[Position].[All Position], [Employees].[Sheri Nowmer].[Donna Arnold]}
Row #0: 36 494,07 €
Row #0: 7 236
Row #1:
Row #1:
Row #2:
Row #2:
Row #3: 428,76 €
Row #3: 36
Row #4: 234,36 €
Row #4: 24
Row #5: 832,68 €
Row #5: 60
Row #6: 577,80 €
Row #6: 24


So you get the WARN message that tells the hierarchy is not in the 
correct order, but the result seems ok.
In JPivot, when you try to use the navigator component, you get all the 
record directly (like a sql resultset) instead of a well built 
hierarchy. (I haven't get how it queries mondrian yet)

I hope it make sense, if I can help in any way...


Damien




More information about the Mondrian mailing list