Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Using semi-additive measures (account balances) in Mondrian Cube

  1. #1
    Join Date
    Nov 2009
    Posts
    11

    Default Using semi-additive measures (account balances) in Mondrian Cube

    Hello,

    I'm new to both Mondrian & MDX (but very familiar with SQL/ETL).

    What I want to know if following possible using Mondrian or not. Here is a scenario:
    I've Time hierarchy with Year --> Quarter (does not go below that)
    I've Customer hierarchy with Country --> Customer (simplified for this example).
    I've closing account balances by quarters. Say Debit & Credit Balances. These are obviously semi-additive.

    I want to be able to sum balances across Customer dim but only show the closing balance across Time dim. So if I choose "Canada and 2009", it should sum all closing balances for 2009 Q4 across all Canadian customers.

    I think this probably will use ClosingPeriod() function to get 2009 Q4 but I was not sure from reading the forums that I can at the same time add across Customer dim.

    Can this be coded with Mondrian? If so, is hand coding necessary or does the Pentaho Workbench help here? I'll have about 900 measures that behave like this.

    Regards,
    Sean

  2. #2
    Join Date
    Feb 2009
    Posts
    9

    Default

    Mondrian, like any other olap tool I know of, automatically aggregates (normally sums up) all dimensions of a cube that are not present in the query. So as long as you only select one quarter you should be fine.
    Markus Burger-Scheidlin
    ITGAIN Consulting GmbH Hannover, Germany

  3. #3
    Join Date
    Nov 2009
    Posts
    11

    Default

    Quote Originally Posted by mburger View Post
    Mondrian, like any other olap tool I know of, automatically aggregates (normally sums up) all dimensions of a cube that are not present in the query. So as long as you only select one quarter you should be fine.
    Hi, that is exactly what I don't want to do. If I select a quarter, everything is fine as that is the lowest on the Time hierarchy. But I want to select a Year and get the Q4 balance. This is very common to do in financial transactions (Balance Sheet items). E.g. If I want to know what were the Total Assets of Exxon and Chevron for 2009, the answer is (Exxon 2009 Q4 Total Assets + Chevron 2009 Q4 Total Assets). Though the question asked is not for specifically for 2009 Q4. Here we cannot include any numbers from Q1 thru Q3 as it will quadruple count the assets.

    I had a brief discussion on IRC, and it seems ClosingPeriod function may do the trick. I have tried it (it takes me a long time to do this being very new to Mondrian/MDX).

  4. #4
    Join Date
    Feb 2011
    Posts
    8

    Default

    Sean,

    Did you get anywhere with this as I have a similar problem but with daily balances.

    Gordon

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.