[Mondrian] semi-additive measures with a shared time dimension
Stefano Celati
stefano.celati at bnova.it
Mon Jan 11 11:52:14 EST 2016
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
More information about the Mondrian
mailing list