CODEWITHSUNDEEP
mdxolapmondrian
tomkani
tomkani

Reputation: 13

MDX: Aggregate last member

I have a data cube with one hierarchical dimension "MyTime", in each level the elements are ordered. This dimension is somehow a time dimension, but does not fit 100% to gregorian calendar. There is one cube using this dimension. Extract of my OLAP-schema:

<Dimension name="MyTime">
    <Hierarchy hasAll="true">
        <Level name="MyYear" type="Numeric" uniqueMembers="true"/>
        <Level name="MyMonth" type="Numeric" uniqueMembers="true"/>
        <Level name="MyDay" type="Numeric" uniqueMembers="true"/>
        <Level name="MyShift" type="Numeric" uniqueMembers="true"/>
    </Hierarchy>
</Dimension>
<Cube name="MyCube">
    <DimensionUsage name="MyTime" source="MyTime"/>
    <Measure name"Price" aggregator="avg"/>
</Cube>

DB-Tables look like this:

MyTimeDim
id | myYear | myMonth | myDay | myShift | ... other fields
---+--------+---------+-------+---------+-----------------
1  | 2014   | 6       | 3     | 1       |
2  | 2014   | 6       | 3     | 2       |
3  | 2014   | 6       | 3     | 3       |
4  | 2014   | 6       | 4     | 1       |
5  | 2014   | 6       | 4     | 2       |
6  | 2014   | 6       | 4     | 3       |

MyFact
id | timeDim | price
---+---------+------
1  | 1       | 20
2  | 2       | 9
3  | 3       | 25
4  | 4       | 3
5  | 5       | 37
6  | 6       | 5

The task is, to show a hierarchical evaluation to drill down by MyTime. On each level different aggregates of the price have to be build. Easy ones are min and max. But I also have to show the first and the last member.

That means on Day-level, the result should look like this:

Date       | Min | Max | First | Last
-----------+-----+-----+-------+-----
2014-06-03 | 9   | 25  | 20    | 25
2014-06-04 | 20  | 37  | 3     | 5

I think, to provide this, I have to define a calculated member. But I could not figure out, how to setup a "user defined" aggregate.

Upvotes: 1

Views: 480

Answers (1)

nsousa
nsousa

Reputation: 4544

There is no First or Last aggregate in Mondrian as of now.

The only way to do it, IMO, is on query time with a calculated member like this:

  MEMBER [Measures].[Last Period Measure] as ( [Measures].[My Measures], [MyTime].CurrentMember.LastChild)

Upvotes: 1

Related Questions