Mondrian3.x的schema中,如何做到同一纬度的不同level数据作为查询条件来用?


如下schema代码片段:

<?xml version="1.0"  encoding="UTF-8" ?>
<Schema name="报表">
    <cube name="cube_qc_pass_item" caption="报表1" encoding="UTF-8">
        <table name="fact_qc_pass_record_item_join">
        <Dimension name="models" foreignKey="model_id" caption="模板">
            <Hierarchy hasAll="true" allMemberName="model_name" primaryKey="id" primaryKeyTable="dim_qc_model">
                <Table name="dim_qc_model" />
                <Level name="model_name" column="name" caption="模板"/>
                <Level name="model_id" column="id" caption="model_id"/>
            </Hierarchy>
        </Dimension>
        <Measure name="times" column="id" aggregator="count" formatString="#,###0" datatype="Numeric" caption="总量"/>
    </cube>
</Schema>


我想要在使用mdx查询的时候,使用model_name显示,使用model_id作为查询条件限制某个model_id,该如何书写mdx语句?


已经有如下错误的mdx语句了


mdx查询语句1:


mdx语句

SELECT
NON EMPTY {Hierarchize({{[Measures].[times], [Measures].[notPass], [Measures].[pass]}})} ON COLUMNS,
NON EMPTY {Hierarchize([models].[model_name].Members)} ON ROWS
FROM [cube_qc_pass_model] where [models].[model_id].[5cda6afa-f837-4603-af27-0915cfb812fd]

如下查询报错:

MondrianException: Mondrian Error:Hierarchy '[models]' appears in more than one independent axis.

mdx查询语句2:

mdx语句

SELECT
NON EMPTY {Hierarchize({{[Measures].[times], [Measures].[notPass], [Measures].[pass]}})} ON COLUMNS,
NON EMPTY Hierarchize(Union(CrossJoin([models].[model_name].Members, CrossJoin([times].[minute].Members, [agent_nos].[agent_no].Members)), CrossJoin([models].[model_id].[5cda6afa-f837-4603-af27-0915cfb812fd], CrossJoin([times].[minute].Members, [agent_nos].[agent_no].Members)))) ON ROWS
FROM [cube_qc_pass_model]

结果: 
无法进行查询



请先 登录 后评论

最佳答案 2017-07-27 16:49

首先改写schema文件,将model_name和model_id拆分成两个维度:

<?xml version="1.0"  encoding="UTF-8" ?>

<Schema name="报表">

    <cube name="cube_qc_pass_item" caption="报表" encoding="UTF-8">

        <table name="fact_qc_pass_record_item_join">

        <Dimension name="models" foreignKey="model_id" caption="模板">

            <Hierarchy hasAll="true" allMemberName="model_name" primaryKey="id" primaryKeyTable="dim_qc_model">

                <Table name="dim_qc_model" />

                <Level name="model_name" column="name" caption="模板"/>

            </Hierarchy>

        </Dimension>

        <Dimension name="model_ids" foreignKey="model_id" caption="模板id">

            <Hierarchy hasAll="true" allMemberName="model_id" primaryKey="id" primaryKeyTable="dim_qc_model">

                <Table name="dim_qc_model" />

                <Level name="model_id" column="id" caption="model_id"/>

            </Hierarchy>

        </Dimension>

        <Measure name="times" column="id" aggregator="count" formatString="#,###0" datatype="Numeric" caption="总量"/>

    </cube>

</Schema>

使用如下语句查询,是可以查询到数据的:

SELECT

NON EMPTY {Hierarchize({{[Measures].[times], [Measures].[notPass], [Measures].[pass]}})} ON COLUMNS,

NON EMPTY CrossJoin([models].[model_name].Members, CrossJoin([times].[minute].Members, [agent_nos].[agent_no].Members)) ON ROWS

FROM [cube_qc_pass_model] where [model_ids].[model_id].[5cda6afa-f837-4603-af27-0915cfb812fd]



请先 登录 后评论

其它 0 个回答

  • 1 关注
  • 0 收藏,43 浏览
  • OpenBI官方 提出于 2017-07-27 16:47

相似问题