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

Matt Campbell mcampbell at pentaho.com
Mon Jan 11 16:10:04 EST 2016


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.





On 1/11/16, 11:52 AM, "mondrian-bounces at pentaho.org on behalf of Stefano Celati" <mondrian-bounces at pentaho.org on behalf of stefano.celati at bnova.it> wrote:

>Hi,
>I'm stuck on trying to solve this quiz and I hope someone could help me 
>shade a light on it.
>
>I'm working on a sort of semi-additive measure that is an 
>already-calculated YtD (at month level) value in the source table, so I 
>need to expose this measure as a sum when the user asks for 
>(year/)months details and as a sum of the last available month in the 
>year, when they ask only for year level.
>
>I read the very good Diethard Steiner's article on his blog talking 
>about this (in this case I'm using the CLOSINGPERIOD function), but I'm 
>not getting what I would.
>
>I believe this is because my calendar is already filled with all the 
>years/months ten years in advance and Mondrian is generating a query on 
>that table that leads it to ask for figures of December on every year.
>
>Let me explain:
>---------------------
>Time Dimension: (pre-filled with years/months from 2010 to 2020)
>ID_CAL
>Year
>Month
>
>Fact Table:
>ID_CAL
>OTHER_IDS (to join other dimensions)
>.
>.
>SEMIADD_MEASURE (aggregation: sum)
>
>I built this NONADD_MEASURE with a CM and the formula looks like this:
>
>([Measures].[SEMIADD_MEASURE], ClosingPeriod ([Time].[Month], 
>[Time].CurrentMember))
>
>When I use this into Analyzer I see from log that Mondrian is looking up 
>for Childrens of Years on the Time Dimension (remember it's always 
>full-filled) then builds the final query against the fact table using a 
>"WHERE MONTH=12" clause for every year, which makes some sense but 
>obviously gives no results when the year is not ended yet.
>
>I don't know if this could help (because I haven't tried anything else 
>at the moment), but my fact table also has its own Year and Month 
>columns (the "external" time dim is because this is a multi-cube schema 
>and I need to analyze data across this shared calendar).
>
>Thanks in advance for any kind of help
>Stefano
>_______________________________________________
>Mondrian mailing list
>Mondrian at pentaho.org
>http://lists.pentaho.org/mailman/listinfo/mondrian


More information about the Mondrian mailing list