[Mondrian] semi-additive measures with a shared time dimension

Stefano Celati stefano.celati at bnova.it
Tue Jan 12 08:43:28 EST 2016


Hi guys and thanks for the prompt answer. I have some news (some bad, some good) but unfortunately none of them solved.

@Matt: your suggested test, gave (as figured from SQL log) [Time].[xxxx].[12] (where xxxx are the various years in the DB).
@Manuel: your solution works like a charm, but only as soon as we have the data into cache (I guess) because the first time gives an error: "query requires more than 12 iterations". Having looked at JIRA, I found several cases about this issue: some were closed-solved, but I'm getting this error even if I'm using a newer (5.2) version than those. 
I don't know if as an EE subscriber I can ask for help to support, since this is an un-documented function, but...

Thanks again
Stefano
----------------------

Date: Mon, 11 Jan 2016 21:10:04 +0000
From: Matt Campbell <mcampbell at pentaho.com>
Subject: Re: [Mondrian] semi-additive measures with a shared time
        dimension
To: Mondrian developer mailing list <mondrian at pentaho.org>
Message-ID: <3E21A66D-F89E-48F8-9CB8-68DA64EDC337 at pentaho.com>
Content-Type: text/plain; charset="utf-8"


One thing I?d try is to run a query with a calculated member which gives you the unique name of the member returned by ClosingPeriod? e.g. Something like

  With member measures.cp as ?ClosingPeriod(Time.Month, Time.CurrentMember).UniqueName?
  Select measures.cp on 0,
     Time.year.members on 1
   From myCube

This should give you the unique name of the last Month present under each Year, regardless of whether the Year has a [December].  This might help identify why your formula is giving you unexpected results.

---------------------------

Date: Mon, 11 Jan 2016 18:15:26 -0300
From: Manuel Aristar?n <manuel at jazzido.com>
Subject: Re: [Mondrian] semi-additive measures with a shared time
        dimension
To: Mondrian developer mailing list <mondrian at pentaho.org>
Message-ID: <2ED10756-EF16-4C70-8DDE-CFA6053DB21D at jazzido.com>
Content-Type: text/plain; charset="utf-8"

I've solved that using the undocumented LastNonEmpty function. The formula, in your case, would look something like:

        ([Measures].[SEMIADD_MEASURE], LastNonEmpty(Descendants([Time].CurrentMember), [Measures].[SEMIADD_MEASURE]))

Hope this helps,

--
Manuel Aristar?n
http://jazzido.com <http://jazzido.com/>



More information about the Mondrian mailing list