MS SQL Server OLAP - MDX to calculate dead stock.

Author

MDX to calculate dead stock.

Mahesh Shrestha

2005-11-17, 8:24 pm

Hi,

I have a cube with Date, Product and Activity dimension which will give
me what activity was carried out on which product and when. The measure
in this cube is quanity which will be minus if it is out and plus if it
is it type of activity.

My requirement: for a given date, I need to find the stock that do not
have activity for previous 30 days.

Any help on how to write MDX for this will be greatly appriciated.

Thanks
Mahesh

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

Darren Gosbell

2005-11-17, 8:24 pm

The following is psuedo code, you will have to fill in some of the
blanks to get it to work. It should return a list of products on the
rows for the date specified in the where clause that have not had any
activity in the last 30 days.

I am getting the entire set of product members and then for each member,
getting the set of days from the last 30 days that has a quantity
greater than 0. If the count of the members in this second set equals
zero then there has been no activity for that month.

SELECT

Filter(Product.[<level>].Members, Count(Filter(Time.Currentmember.Lag
(30):Time.CurrentMember, Measures.Quantity > 0)) = 0) ON ROWS,

{Measures.Quantity} ON COLUMNS

FROM [<Cube>]
WHERE ([Time].[Day].[18 Nov 2005])
[color=darkred]

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

In article <ON07au86FHA.3588@TK2MSFTNGP15.phx.gbl>, maheshnp@hotmail.com
says...[color=darkred]
> Hi,
>
> I have a cube with Date, Product and Activity dimension which will give
> me what activity was carried out on which product and when. The measure
> in this cube is quanity which will be minus if it is out and plus if it
> is it type of activity.
>
> My requirement: for a given date, I need to find the stock that do not
> have activity for previous 30 days.
>
> Any help on how to write MDX for this will be greatly appriciated.
>
> Thanks
> Mahesh
>
>
>
> *** Sent via Developersdex http://www.droptable.com ***
>

Deepak Puri

2005-11-18, 3:23 am

Might want to try a NonEmptyCrossJoin() approach, since activities on a
given day may cause quantity to be <=> 0:
[color=darkred]
SELECT
{Measures.Quantity} ON COLUMNS,
Except(Descendants(& #91;Product],,LEAVES
),
NonEmptyCrossJoin(De
scendants(& #91;Product],,LEAVES
),
{[Time].Lag(30):[Time].PrevMember}, 1)) ON ROWS

FROM [<Cube>]
WHERE ([Time].[Day].[18 Nov 2005])[color=darkred
]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Darren Gosbell

2005-11-18, 7:23 am

Hi Deepak, Maybe I misunderstood the original post.

I thought that the original poster was saying that if the measure was
positive it meant one thing and if it was negative it meant another (not
an ideal setup) and that they were only after products that did not have
a positive value, hence my use of the filter instead of the
nonemptycrossjoin approach.

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

In article <u4ChOp#6FHA.1032@TK2MSFTNGP11.phx.gbl>,
deepak_puri@progress
ive.com says...
> Might want to try a NonEmptyCrossJoin() approach, since activities on a
> given day may cause quantity to be <=> 0:
>
> SELECT
> {Measures.Quantity} ON COLUMNS,
> Except(Descendants(& #91;Product],,LEAVES
),
> NonEmptyCrossJoin(De
scendants(& #91;Product],,LEAVES
),
> {[Time].Lag(30):[Time].PrevMember}, 1)) ON ROWS
>
> FROM [<Cube>]
> WHERE ([Time].[Day].[18 Nov 2005])
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server
>
> *** Sent via Developersdex http://www.droptable.com ***
>

Deepak Puri

2005-11-18, 11:24 am

Hi Darren,

For sure, parsing the semantics of questions posed here is not an exact
science; so maybe we'll have to wait to hear from Mahesh, on what
scenarios actually apply.

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Mosha Pasumansky [MS]

2005-11-18, 1:23 pm

I agree with Darren's interpretation here. The problem sounds like classic
inventory scenario with things moving in and out - so NonEmptyCrossJoin
seems to be not appropriate here, but the Filter testing on non-zero values
is.

--
====================
==========_=========
===========
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebL_og/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
====================
==========_=========
===========

Mahesh Shrestha

2005-11-18, 8:24 pm

Hi Everybody,

Thank you very much for all the suggestions. I really appreciate such a
quick and detailed response.

I am trying out all the suggestions that have been made here and will
get back once i find out how they work.

Thank a lot.

Kind Regards
Mahesh

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

Mahesh Shrestha

2005-11-18, 8:24 pm

Hi,

As mentioned earlier by both Deepak and Darren, the quntity aggregation
of an itme for a given period can be 0 when total in quantity and total
out quantity for that period is equal. This will include that item in
the dead stock despite having multiple activities.

However filter method works perfectly if a new measure is created which
aggregates quantity on the basis of count instead of sum.

I am still trying out the NonEmptyCrossJoin method.

I really appreciate all the suggestions.

Thanks
Mahesh

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

Vladimir Chtepa

2005-11-21, 1:23 pm

Hi

I think that Deepak has right.
I have a folowing CM
iif(Product.currentmember.Level is Product.Product,

IsEmpty([Measures].& #91;SalePositions])*
[Measures].[StockValue],

Sum(Except(NonEmptyC
rossJoin(Product.Product.members, {[Measures].[Stock]},
1),

NonEmptyCrossJoin(Pr
oduct.Product.members, {[Measures].& #91;SalePositions]},

1)), [Measures].[StockValue]))

[Measures].[StockValue] is also CM

Vladimir Chtepa

"Mosha Pasumansky [MS]" <moshap@online.microsoft.com> schrieb im Newsbeitrag
news:437e2100$1@news
.microsoft.com...
>I agree with Darren's interpretation here. The problem sounds like classic
>inventory scenario with things moving in and out - so NonEmptyCrossJoin
>seems to be not appropriate here, but the Filter testing on non-zero values
>is.
>
> --
> ====================
==========_=========
===========
> Mosha Pasumansky - http://www.mosha.com/msolap
> Analysis Services blog at http://www.sqljunkies.com/WebL_og/mosha
> Development Lead in the Analysis Server team
> All you need is love (John Lennon)
> Disclaimer : This posting is provided "AS IS" with no warranties, and
> confers no rights.
> ====================
==========_=========
===========
>

Mahesh Shrestha

2005-11-22, 9:23 am

Hi,

I have used both NonEmptyCrossJoin and Filter methods to solve my
problem. The NonEmptyCrossJoin is very slow as it is taking more than a
minute to process it. I am not sure if the time is consumed to find the
leave products by " Descendants(Products
,,Leaves)" or by
NonEmptyCrossJoin.

The result i am getting from filter condition is not right. I think i
have some problem in my filter condition.

In order to dispaly the dead stock for 30 days i have to filter out the
products that do not have any activity for last 30 days. For this i have
created a calculated measure which counts the number of activities. I am
using the following expression.

SELECT
Filter( Descendants(Products
,,Leaves),
(Time.CurrentMember.Lag(30):Time.CurrentMember,
Measures.ActivityCount)= 0) ON COLUMNS, ....

Here i have to filter the " Descendants(Products
,,Leaves)" on the basis
of "Measures.ActivityCount" in
"Time.CurrentMember.Lag(30):Time.CurrentMember". Can it be done with out
cross join.

To generalize this question how can i filter Dimension A (Products), on
the basis of its value for Measure A (Quantity) for a given preiod
(Time.. : Time.. )

Any help in this filter condition and optimimizing the NonEmptyCrossJoin
will be greatly appreciated.

Thanks and Regards
Mahesh

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

Deepak Puri

2005-11-22, 1:23 pm

Hi Mahesh,

For the NonEmptyCrossJoin() option (assuming that it gives the right
results), there's a couple of things to look at:

- What's the structure of the Product dimension, and number of members
at the leaf level? You can get an idea of the performance of
" Descendants(Products
,,Leaves)" by returning just that as the rows in an
MDX query and measuring the response time. If Product is a regular
dimension, then you could compare the performance with using
"Products.LeafLevel.Members" instead.

- If the Time dimension has a Week level, you could optimize
"Time.CurrentMember.Lag(30):Time.CurrentMember" by using an equivalent
set of week and day members, as discussed here in Chris Webb's blog:

http://spaces.msn.com/members/cwebb...w.droptable.com ***

Mahesh Shrestha

2005-11-23, 8:24 pm

Hi Deepak,

Thank you very much for all the help. You idea here answers my question
in other thread about optimizing descendants(products
,,leaves) as well.

Thanks
Mahesh

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

Mahesh Shrestha

2005-11-24, 11:23 am

Hi Deepak,

Thanks for directing me to Chris Webb's blog. The optimization technique
discussed there is very effective and it cut down the time by almost 90%
in my case.

Thanks
Mahesh

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

Deepak Puri

2005-11-25, 11:23 am

Hi Mahesh,

Thanks for sharing your performance results with the newsgroup, since
it's useful to know how well various theoretical approaches work in
practice!

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Sponsored Links