Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Multiple aggregators in a measure

  1. #1

    Question Multiple aggregators in a measure

    Hello. I am starting to use Mondrian cubes with Pentaho, and I have a doubt:

    How could I define multiple aggregators in a measure column? I have columns with numeric values, and I only can show one aggregator on the top of the column. I'd like to show max, min and avg aggregators without replicate columns. Is this possible? Do I have to modify the mondrian.xml file or the mdx query in the xaction file?

    Thanks in advance.

    Mario.

  2. #2
    wgorman Guest

    Default

    You will need to add additional measures to the mondrian schema file:

    http://mondrian.pentaho.org/document...a.php#Measures

    Will

  3. #3

    Default

    Thank you Will for your quick answer. I still have doubts. I tried to define new measures in my cube schema, but doing this my table has duplicated columns. I only want to show new rows at the top of the table with the new aggregators. Here is my cube schema and the MDX query. Hope it helps to understand my problem:

    Cube schema:

    Code:
    <?xml version="1.0" encoding="UTF-8"?>
    <Schema name="CuboNotas">
        <Cube name="CuboNotas">
            <Table name="test"/>
            <Dimension name="Nombre" foreignKey="id_alumno">
                <Hierarchy name="Nombre" hasAll="true" allMemberName="Alumnos" primaryKey="id">
                    <Table name="datos_personales"/>
                    <Level name="Nombre" table="datos_personales" column="nombre_completo" uniqueMembers="false"/>
                </Hierarchy>
            </Dimension>
            <Dimension name="Asignatura" foreignKey="id_asignatura">
                <Hierarchy name="Asignatura" hasAll="true" allMemberName="Asignaturas" primaryKey="id_asignatura">
                    <Table name="asignaturas"/>
                    <Level name="Asignatura" table="asignaturas" column="nombre" uniqueMembers="false"/>
                </Hierarchy>
            </Dimension>
            <Measure name="Nota1max" column="nota1" aggregator="max" datatype="Integer" formatString="#.##0"/>
            <Measure name="Nota2max" column="nota2" aggregator="max" datatype="Integer" formatString="#.##0"/>
            <Measure name="Nota3max" column="nota3" aggregator="max" datatype="Integer" formatString="#.##0"/>
            <Measure name="Nota4max" column="nota4" aggregator="max" datatype="Integer" formatString="#.##0"/>
            <Measure name="Nota5max" column="nota5" aggregator="max" datatype="Integer" formatString="#.##0"/>
            <Measure name="Nota1min" column="nota1" aggregator="min" datatype="Integer" formatString="#.##0"/>
            <Measure name="Nota2min" column="nota2" aggregator="min" datatype="Integer" formatString="#.##0"/>
            <Measure name="Nota3min" column="nota3" aggregator="min" datatype="Integer" formatString="#.##0"/>
            <Measure name="Nota4min" column="nota4" aggregator="min" datatype="Integer" formatString="#.##0"/>
            <Measure name="Nota5min" column="nota5" aggregator="min" datatype="Integer" formatString="#.##0"/>
            <Measure name="Nota1avg" column="nota1" aggregator="avg" datatype="Integer" formatString="#.##0"/>
            <Measure name="Nota2avg" column="nota2" aggregator="avg" datatype="Integer" formatString="#.##0"/>
            <Measure name="Nota3avg" column="nota3" aggregator="avg" datatype="Integer" formatString="#.##0"/>
            <Measure name="Nota4avg" column="nota4" aggregator="avg" datatype="Integer" formatString="#.##0"/>
            <Measure name="Nota5avg" column="nota5" aggregator="avg" datatype="Integer" formatString="#.##0"/>
        </Cube>
    </Schema>
    MDX query:

    Code:
    select NON EMPTY {[Measures].[Nota1max], [Measures].[Nota2max],[Measures].[Nota3max],[Measures].[Nota4max],[Measures].[Nota5max],[Measures].[Nota1min], [Measures].[Nota2min],[Measures].[Nota3min],[Measures].[Nota4min],[Measures].[Nota5min],[Measures].[Nota1avg], [Measures].[Nota2avg],[Measures].[Nota3avg],[Measures].[Nota4avg],[Measures].[Nota5avg]} ON COLUMNS,
               NON EMPTY {([Nombre.Nombre].[Alumnos], [Asignatura.Asignatura].[Asignaturas]) }  ON ROWS
    
    from [CuboNotas]

  4. #4
    wgorman Guest

    Default

    Try this:

    Code:
    <Measure name="Nota1max" column="nota1" aggregator="max" datatype="Integer" formatString="#.##0"/>
    <Measure name="Nota1min" column="nota1" aggregator="min" datatype="Integer" formatString="#.##0"/>
    Note that there are two different measures, but they both reference the same column in the SQL table.

  5. #5

    Default

    Sorry Will, but I don't understand your solution. I have already defined these measures in the cube schema. What I want to know is how I should modify the mdx query to get something similar to this picture (desired table). Click on the picture to see it with normal size:



    Sorry for the quality of the image, it's a simple and quick Paint edit. Hope this help to understand my problem.

    Thanks a lot.

    Mario.

  6. #6

    Exclamation

    Is there any ideas?

    I want to know if, at least, it is possible to build the table shown in my last post.

    Thanks.

    Mario.

  7. #7
    Join Date
    Oct 2007
    Posts
    26

    Default Subtotal MDX

    I don't believe you can do what you are asking with JPivot out of the box. If you can write MDX, you can define calculated members in your Nobre dimension that AVG, MIN or MAX on the child members. You are basically asking for subtotals. (Note the aggregation would be done in Mondrian not the DB.)

    Here's a screenshot of how it looks:

    http://www.lucidera.com/application/...ycle_large.jpg

    See the "2007-Q1 Total". In order to get "2007-Q1 Max", we would use a caculated member that does a MAX over the months in that quarter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.