Hi,

While implementing an Inventory Cube, stuck with a problem with the
fact table. The fact table has only the movements (deltas) of stock.
The movements may not be available on all days. The current stock
closing balance is available as a property of the product dimension.

Need help to display daily stock balance on a stock/sales analysis.
Time dimension is Year, Month, Day. The time span where the analysis is
required could be anything - couple of days, months etc in the past.

I tried the topsum(), and the example in Spofford's book (page 94), but
couldn't make it to work.

My intention was to get the daily stock as a calculated member by
summing backwards from current stock and then do the analysis.
Otherwise i could aggregate all the deltas and arrive at opening stock
from the beginning the could sum forward.

Lost...Please help.

Cheers !!

Jimmy

Assuming that there is a measure to sum the deltas like [StockChange],
the backward balance could be computed either by summing the deltas
directly backwards, or by summing the deltas forward from the start
date, and subtracting this sum from the total delta across all dates.
The choice would depend on which range is smaller - in the case of
recent dates and summing directly backwards:

CDbl([Product].Properties("ClosingBalance"))

[Measures].[StockCurrent]:
[Measures].[StockClose]- Sum(Descendants(
[Product].CurrentMember, [Product].[Product]),
Sum(Except([Time].CurrentMember.Level.Members,
PeriodsToDate([Time].[(All)])),
[Measures].[StockChange]))

If performance is a problem, there are techniques to optimize the set of
time members over which to sum:

http://spaces.msn.com/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!107.entr
y

http://spaces.msn.com/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!111.entr
y


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

PS: since the "ClosingBalance" property may not be defined at higher
levels of the [Product] dimension, it may need to be summed across
products as well:

Sum(Descendants([Product].CurrentMember,
[Product].[Product]),
[Measures].[StockClose]-
Sum(Except([Time].CurrentMember.Level.Members,
PeriodsToDate([Time].[(All)])),
[Measures].[StockChange]))

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,

It worked. Thanks!!
But bit too heavy on the system performance wise.

I couldn't get the StockClose to work, becuase the Property - Closing
Balance is available only at the "Products" Level of the dimension

I used the following :

[Measures].[StockClose]:
IIF ([Product].Currentmember.Level.Name = "Products",
Cdbl([Product].CurrentMember.Properties("Closing Balance")),Null)

- Jimmy

Jimmy George
Melbourne




Deepak Puri wrote:

Hi Jimmy,

Glad to hear that you got somewhere - but when you say: "But bit too
heavy on the system performance wise", is it the time or the resources?
As I mentioned, there are some ways to improve the sum over time
members. However, if there are many products that have to be summed,
that's a different issue.

Another way to get the stock close at any level would be:

[Measures].[StockClose]:
Sum(Descendants([Product].CurrentMember,
[Product].[Products]),
Cdbl([Product].Properties("Closing Balance"))


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

I am assuming that you are talking about the first edition of George's
book and hence that you are using AS2k.

If you are trying to sum from the current time member, back to the first
time member on the same level, you could do something like the
following:

SUM({Time.CurrentMember.Level.FirstChild:Time.Curr entMember},
Measures.StockDelta)

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1150102736.033659.139800 (AT) y43g2000cwc (DOT) googlegroups.com>,
benjim (AT) iinet (DOT) net.au says...

Hi,

The calculations seems to take forever - more than an hour. the cpu
utilisation around 30%.

I am on AS2K standard edition. Eventhough the hardware got lot of
memory, being in a standrad edition limits memory usage.

The fact table count is alomost half a million records, the product
dimension table count to more than half a million. (it is 10000
products in 50+ warehouses).

There are not many dimensions in the cube, Product :warehouse-product
and Time :Year-month-day are the major ones.

Any suggestions ?

Thanks

Jimmy George

It's hard to say, but if it was at all possible - I would think that
breaking the warehouse-product dimension into 2 dimensions would help
your performance a lot.

Half a million records is not a lot for a fact table, but it is
reasonably big for a dimension. In AS2k all the dimensions are cached in
RAM, so if nothing else, a dimension of this size will really limit the
size of the data cache and the efficiency of the data structures that
are built during processing.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1150676745.992419.177820 (AT) c74g2000cwc (DOT) googlegroups.com>,
benjim (AT) iinet (DOT) net.au says...

Hi Jimmy,

What MDX calculations did you end up using; and did you try to optimize
the time series, along the lines of the referenced links (eg:
Sum(May1:June15) could be replaced by Sum(May) + Sum(June1:June15))?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***