I am using a parent-child relationship for accounts in the OLAP database icCube. To include financial logic I make use of unary operators. In addition, I have set-up several account hiearchies using the many-2-many relationship and all is working very smoothly, except ....
when I want to apply time logic on the result, e.g. show the YTD value for April,30 2014 by:
Aggregate(crossjoin ({[View].[View].[Periodiek]},PeriodsToDate([Tijd].[Kalender].[jaar],[Tijd].[Kalender].currentmember)))
I get the message:
Aggregate() : the aggregation 'unary-operator' is not supported (measure or calculated measure/member:[Measures].[bedrag])
Apparently, this is not the way to do this.
How can one achieve cumulative figures (periods-to-date) in this setting?
The current version of icCube - 4.8.2 - does not support the Aggregate function for measures with Aggregation type 'unary operator'. See Aggregation function doc here.
The Aggregate function is a bit dodgy if you're using many-2-many relations as well as special measure aggregation types. For example :
Aggregate( { [Account1], [Account2] }, [Measures].[Special] )
If [Special] is a measure with 'Sum' aggregation type and [Account1] and [Account2] have a many-2-many relation we would be counting twice the same 'shared' amount (aka same row is counted twice).
Other measures with aggregation types are just not supported to avoid getting unexpected results. This applies for aggregation types a Open / Close / Distinct Count.
The solution in your case is using the Sum function :
Compact Set allows to compact to set in case you're using days or hours reducing the set. It's a perfomance booster.
If you want to handle properly m2m relations and special measure aggregation types you can use Categories in icCube, see here some doc. Quickly, they allow to define dynamically a members as a set of tuples.
To properly support Aggregate we should add a new method, e.g. icAggregate, that works as using Categories. The Aggregate function it's a bit strange, for the time being we mimic a bit SSAS...