Hello,
I suppose that for some of you is a very known problem, but it gave me
troubles...
I have a fact table with stock variations by date, product, store. That fact
table has 1 measure: Qty.
On another table I have the history of Unit price, by store, product & From
date / To Date.
In the cube I have a calculated measure Qty OnHand (or Qty 2Date) which
makes PeriodsToDate To compute the stock for a specific date. I need to have
also Value OnHand, which is SUM (QtyOnHand * UnitPrice) for each store,
product.
I created a recursive measure as:
ValueOnHand = Iif (StoresDim.Level Is Stores.Store,
Iif (ProductsDim.Level Is Products.Product,
QtyOnHand * UnitPrice,
SUM (Descendants (ProductsDim.CurrentMember, Products.Product),
ValueOnHand)),
SUM (Descendants (StoresDim, Stores.Store), ValueOnHand))

It seems to work, but it takes A LOT to execute the query... (more than a
minute).
I also tried by custom rollup in Store and Products dimensions, but it's
more dramatically...
Any other ideea?
Thanks a lot in advance.
Catalin

ValueOnHand can be written non-recursively, but that may not necessarily
improve performance by much:

SUM(Descendants(StoresDim.CurrentMember,Stores.Sto re),
SUM(Descendants(ProductsDim.CurrentMember,Products .Product),
QtyOnHand * UnitPrice))

How many store/product combinations are typically being summed when
querying for ValueOn Hand? Another performance issue likely is how
QtyOnHand and UnitPrice are derived (presumably both are calculated
measures), and whether anything can be done to improve performance
there...


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

And this will be supported natively with SQL Server 2005 Analysis Services.
One of the new aggregate functions (like you have to today with sum, min,
max, count, etc.) will be "last child". To implement it, you must have a
dimension (and only one dimension) which has been labelled as "Time" -- you
can have multiple hierarchies in the dimensions, but it must only one Time
dimension.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Wouldn't this be a classic case, whereby despite the fact that it can
be done in Analysis Services, you are far better off doing some work in
the database itself (via a derived table) and then providing the data
to the cube in a more efficient format?

Maybe add the closing balance to your transactions table (after each
transaction), then you can get the last child that is less than the
current date member.

Assuming of course that you have access to update the original
database.

Cheers,
Peter.

It's not very easy to keep updated values OnHand on DW when the transaction
history can change (my case). But I will consider it.. Thanks
"Peter" <peterr (AT) intellimaxsolutions (DOT) com> wrote

When you said: " To implement it..." you refer to the new aggregate function
(last child)? That means that it can't be used on any dimension? I don't
understand the relation to Time dimension...
Thanks


"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote