Mondrian parent-child hierarchy not working

Question

Some issues to make mondrian work with a parent-child hierarchy.

My table structure is as follows (simplified, as the Category table is actually a MPTT):

RESPONSE             QUESTION                   CATEGORY
--------------       -------------------        ----------
id             ¡---> identifier (String)  ¡---> id     <---¡
question_id ___|     category_id     _____|     parent_id _|
value (Measure)      title                      name_en

My closure table is a simple setup: child_id, parent_id, distance (with the primary key being the tuple (child_id, parent_id) ).

My cube's schema is as follows:

<Cube cache="true"
    defaultMeasure="Value" enabled="true" name="mycube">
    <Table name="response" schema="public"/>
    <Dimension foreignKey="question_id" name="Category">
        <Hierarchy allMemberName="All Categories" hasAll="true"
            primaryKey="identifier" primaryKeyTable="question">
            <Join leftKey="category_id" rightKey="id">
                <Table name="question"/>
                <Table name="category"/>
            </Join>
            <!-- works fine with the category itself: <Level column="name" type="String" name="Category Name" table="category" uniqueMembers="true"/> -->
            <Level column="id" name="Category ID"
                nameColumn="name_en" nullParentValue="NULL"
                parentColumn="parent_id" table="category"
                type="Numeric" uniqueMembers="true">
                <!-- type="Numeric" ordinalColumn="lft" parentColumn="parent_id" nullParentValue="NULL"  -->
                <Closure childColumn="child_id" parentColumn="parent_id">
                    <Table name="category_closure"/>
                </Closure>
            </Level>
        </Hierarchy>
    </Dimension>
   <Measure aggregator="avg" caption="Value"
        column="actual_value" formatString="#,###.00" name="Value"/>
</Cube>

Now, based on the mondrian FoodMart test pages, I have set up a simple jsp pages for my cube, which I want to use as a starting point for my tests. It has the following MDX:

select {[Measures].[Value]} ON COLUMNS,
 {( [Category] )} ON ROWS
from [mycube]

The result it shows at first is "All Categories". When I try to drill down or hierarchize in the Categories, it returns nothing but [All Categories]. I have tried also with Descendants() to no avail. Equally, when I try to list the members of Categories, it returns nothing.

I see that in the background it runs a query as follows to start the drilling down:

05/12/13 23:53:10,967 postgres: [3-1] LOG: execute : select "category"."id" as "c0", "category"."name_en" as "c1" from "question" as "question", "category" as "category" where "question"."category_id" = "category"."id" and "category"."parent_id" IS NULL group by "category"."id", "category"."name_en" order by "category"."id" ASC NULLS LAST

Obviously this query has an empty result because it joins question with root-level categories whilst only the leaves of my tree are attached some Questions. It also shows that the closure table is not used here.

Any clue on what I can do to fix this?

Thanks ahead lai

lai lai · Accepted Answer · 2014-03-05T14:28:30

Following a few experiments, I shall conclude that my use case is probably not supported by Mondrian. Here is the test I did to come to this conclusion: - ensure I have 3 levels in my tree (level 0->2) - create a fake question related to a root category (i.e. whose parent_id = NULL) - create a response attached to this fake question - at this stage, only level 0 and level 2 Category records have questions and responses related to them - go ahead with a query

Here is the result I got in the logs:

14:37:09,082 WARN [SqlTupleReader] The level [Category].[Name] makes use of the 'parentColumn' attribute, but a parent member for key 3 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.

"key 3" relates to one of my level-2 records i.e. tree leaves (similar messages show for the other level-2 records).

Conclusion: not supported :-(

Enclosing the "working" schema below:

<Schema name="Foo">
    <Cube name="Foo" caption="Cube to report on the Foo quizz dimensions" visible="true" defaultMeasure="Rating" cache="true" enabled="true">
        <Table name="response" schema="public">
        </Table>
        <Dimension type="StandardDimension" visible="true" foreignKey="question_id" highCardinality="false" name="Category">
            <Hierarchy name="Category" visible="true" hasAll="false" allMemberName="All Categories" primaryKey="identifier" primaryKeyTable="question">
                <Join leftKey="category_id" rightKey="id">
                    <Table name="question" schema="public">
                    </Table>
                    <Table name="category" schema="public">
                    </Table>
                </Join>
                <Level name="Name" visible="true" table="category" column="id" nameColumn="name" ordinalColumn="tree_id" parentColumn="parent_id" nullParentValue="NULL" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                </Level>
            </Hierarchy>
        </Dimension>
        <Measure name="Ratings" column="actual_value" formatString="#,###.00" aggregator="avg" caption="Ratings">
        </Measure>
    </Cube>
</Schema>

Mondrian parent-child hierarchy not working

1 Answers