suchen

Heim  >  Fragen und Antworten  >  Hauptteil

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

如下schema代码片段:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

<code><?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></code>

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

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

mdx查询语句1:

mdx语句

1

2

3

4

<code>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]</code>

如下查询报错:

1

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

mdx查询语句2:

mdx语句

1

2

3

4

<code>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]</code>

结果:
无法进行查询

PHP中文网PHP中文网2814 Tage vor543

Antworte allen(2)Ich werde antworten

  • 大家讲道理

    大家讲道理2017-04-18 10:53:49

    1. 其实根据MDX的语法,是不能将同一纬度的不同level放在多处地方的,如一个在rows中一个在columns中,或者一个在rows中或columns一个放在where子句中,就像提问中的第一个失败的语句

    2. 交叉查询的时候,由于使用了统一维度的不同level(更何况这里的两个level其实是一一对应的),并且一个level进行筛选,一个level没进行筛选,

    Antwort
    0
  • 迷茫

    迷茫2017-04-18 10:53:49

    一直没有找到好的解决办法,变向实现了需求,来分享下:

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

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    <code><?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></code>

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

    1

    2

    3

    4

    <code>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]</code>

    Antwort
    0
  • StornierenAntwort