Inventory cube and semi-additive measures

[Database Administrator]

Hi !

I have been running with an inventory cube for some time now, that is based
on movements, meaning that to find the ending stock at some point in time, I
just sum all the movements from the "start of time", till the end of that
day and then I've got the ending stock for that day.

At first, there were not any performance problems, but as data increases the
cube has simply become far too slow!

So, I've been working on a solution. I have created a "derived" table that
contains the sum of all movements per item, per warehouse per day, for every
day that does have any movements, and for each such record I have calculated
the ending stock for that day for each item+warehouse.

My time dimension has Year, Month and Date levels (no quarter) and it's
"complete" in that sense, it has an entry for every day of the year.

Creating the cube mesures has become a headache. I have been trying with the
semiadditive measure stuff but don't quite find it working correctly. There
are two ways of getting the right function though:
a) Every item + warehouse + date must have an entry in the table for the
semiadditive LastNonEmpty or LastChild to work correctly.
b) Have the EndStock as a Sum aggrigate (not semiadditive) and create a
calculated measure that looks at the last date of the period and then walks
backwards using PreviosMember until it find a value.

The first way can mean a huge table and the latter just leavs the cube still
slow!

So how is this really done and what MDX formulas can be used to get the
speed to acceptible levels.

Hope someone can give some advice.

Palli