蒙德里安父子等级不起作用

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

使mondrian与父子层次结构一起工作的一些问题。

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

我的表结构如下（简化，因为Category表实际上是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) ).

我的闭包表是一个简单的设置：child_id，parent_id，distance（主键是元组（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:

现在，基于mondrian FoodMart测试页面，我为我的多维数据集设置了一个简单的jsp页面，我想将其作为测试的起点。它有以下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.

它最初显示的结果是“所有类别”。当我尝试在类别中向下钻取或层次结构时，它只返回[所有类别]。我也试过Descendants（）无济于事。同样，当我尝试列出类别的成员时，它什么都不返回。

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

05/12/13 23：53：10,967 postgres：[3-1]日志：执行：选择“类别”。“id”为“c0”，“category”。“name_en”为“c1”，来自“问题”为“question”，“category”为“category”，其中“question”。“category_id”=“category”。“id”和“category”。“parent_id”IS NULL group by“category”。“id”，“category” 。“name_en”按“类别”排序。“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

谢谢赖先生

1 个解决方案

#1

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

经过一些实验，我将得出结论，蒙德里安可能不支持我的用例。以下是我得出这个结论的测试： - 确保我的树中有3个级别（级别0-> 2） - 创建与根类别相关的假问题（即其parent_id = NULL） - 创建附加响应对于这个假问题 - 在这个阶段，只有0级和2级类别记录有与之相关的问题和答案 - 继续查询

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.

14：37：09,082 WARN [SqlTupleReader]级别[Category]。[Name]使用'parentColumn'属性，但缺少键3的父成员。这可能是由于使用了NativizeSet MDX函数，其成员列表形成了父子层次结构，该层次结构不包括其定义中的所有父成员。将NativizeSet与父子层次结构一起使用需要将父成员包含在集合中，否则无法在本机中正确构建层次结构。

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

“密钥3”涉及我的二级记录之一，即树叶（类似的消息显示其他二级记录）。

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>