Is it possible to make Calculation-values persistent in a Cube !?

Hello

Is it possible to make Calculation-values persistent in a Cube !?

I made an very intensive calculation based on historic data. This resolves rather in a complex MDX-statement and becomes slow at retrieving time

I was wondering.... Is there a way to calculate the value at processing time (by MDX-expression) and store it in the cube ?

I know there is a field 'MeasureExpression' but this field is not full MDX suitable !
Actually... Except for the aggregation-functions (sum, max, min, ...) I want to use a MDX-calculation-expression ?!Well, this functionality is present in AS2005 (in the form of the CACHE statement) but it's unsupported and pretty much undocumented, so I wouldn't recommend using it.

It might be a better idea to focus on tuning your MDX and/or your cube. Can you give us some more details on what you want to do?

Chris|||

Okay if you ask for it

Calculating the today's stocking:
By summerizing all historic mutations till today !

Sum({[Posting Period].[All Posting Period].FirstChild.FirstChild.FirstChild.FirstChild : Head(Descendants([Posting Period].CurrentMember, [Posting Period].[Day])).Item(0)}, [Measures].[Mutation])

[Posting Period] levels => [Year].[Quarter].[Month].[Day]

Can you tune up this MDX-expression ?

Kind regards

|||It depends on what kind of measure [Measures].[Mutation] is. Is it a regular measure of type Sum? If so, then the following technique might be useful:
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!107.entry
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!111.entry

If [Measures].[Mutation] is a calculated measure, what is the definition?|||[Measures].[Mutation] is not a calculated measure but a fact ! Yes, a regular measure of type Sum.

At my first sight ytd seems to be the function I need !?
ytd is a server-side calculation and not client-side !?

Thank you for your usefull reply |||

Well, no, YTD is a function which does something similar to what you've done in your calculated member, but it probably won't perform much better than what you've done.

The performance optimisation comes from the fact that in your calculated member, you are summing up a large set of Days. This is likely to perform badly for two reasons:
1) There might be a lot of Days to sum up
2) There are unlikely to be any aggregations built at the Day level

Therefore what the articles I linked to suggest doing is trying to replace any Days in the set to be summed up with their common ancestors. So, for example, say you wanted to sum up all the Days in 2005 up to 7th December. What you could do is find the set of Days from 1st January to 7th December, then go through it replacing all the Days in Q1 with the Q1 member, all the Days in Q2 with the Q2 member, all the Days in Q3 with the Q3 member, all the Days in October and November with those members, and then leave the rest. So your set would be something like:
{[Time].[2005].[Q1], [Time].[2005].[Q2], [Time].[2005].[Q3], [Time].[2005].[Q4].[OCTOBER], [Time].[2005].[Q4].[NOVEMBER], [Time].[2005].[Q4].[DECEMBER].[1], [Time].[2005].[Q4].[DECEMBER].[2], [Time].[2005].[Q4].[DECEMBER].[3], [Time].[2005].[Q4].[DECEMBER].[4], [Time].[2005].[Q4].[DECEMBER].[5], [Time].[2005].[Q4].[DECEMBER]., [Time].[2005].[Q4].[DECEMBER].[7]}

You can probably guess that summing up 12 values (which is what would happen if you have the correct aggregations built) is going to be quicker than >300 values!

|||

But my issue is more intensive than that....
For every day... I want to sum up from the very first mutation till today... Even if this very first mutation begins at the year eg. 1998 !

[Time].[2005].[Q1].[jan].&[1] = Sum(all days of 98, 99, ..., 05 day 1)
[Time].[2005].[Q1].[jan].&[2] = Sum(all days of 98, 99, ..., 05 day 1, 2)
[Time].[2005].[Q1].[jan].&[3] = Sum(all days of 98, 99, ..., 05 day 1, 2, 3)

For every day in a year: I have to go back to the very first mutation (and the very first year) and sum all (previous days of every previous year) up till this year and this day !
That's why I was wondering... can you make calculations persistent ? or even cache it in the background !?

|||Well yes, but with this technique each extra complete year only means one more member to be added up (all days in 1998 are replaced with the 1998 member on the year level), so it doesn't make much difference. Try it if you can work out the MDX: I've seen it make a massive difference to query performance, and to be honest even if you did work out how to cache calculations then I think this would be the better approach.|||The following article also covers the issue:

http://www.sqlserveranalysisservices.com/OLAPPapers/InventoryManagement%20in%20AS2005v2.htm

I have found it very useful...|||Yes indeed.... it's a very very interested article
Many thx Michael.