Create Measures for the 'number of zerostock events' and the 'Avg. Recovery time' of the item using a mdx query

Question

We have a scenario in which we keep track of our items in inventory and want to know how often an item falls on zero stock and what is the Recovery time of it. Let me give an example,

Let's say the user selects Item A,

                    QTY OnHand         Number of Zerostock Events              Avg. Recovery time

Date

3/1/2012            5                                   

3/2/2012            6

3/3/2012            0                                          1

3/4/2012            0

3/5/2012            0

3/6/2012            7

3/7/2012            0                                          1

3/8/2012            0

3/9/2012            8

3/10/2012         10

3/11/2012         9

3/12/2012         0                                           1

3/13/2012         7

Grand Total                                                    3                                               2 

The explanation is

- Number of Zerostock Events = Any continuous series of '0 or less Qty' is 1 event.(As is this example 1+1+1=3)

-Avg. Recovery time = Number of zero days/Sum of Zerostock Events. (As in this example 6/3=2)

All this needs to be done in mdx and the user can select any item.

Answer 1

Hi Devang,

STEP 1:
-------
For [Number of Zerostock Events] ,

1. Add a field [Number of Zerostock Events] in fact table.

2. Add the logic in ETL process, update the value of [Number of Zerostock Events] as you process the data

In that case [Number of Zerostock Events] will be a measure.


STEP 2:
-------

1. Add a field [Is Zero QOH] in fact table.

2. Add the logic in ETL process, update the value of [Is Zero QOH] as you process the data

In that case [Is Zero QOH] will be a measure.


STEP 3:
-------

For Avg. Recovery time, Create a calculated member

Create Member CurrentCube.[Measures].[Avg. Recovery time] AS

SUM([Measures].[Is Zero QOH])/ SUM([Measures].[Number of Zerostock Events]);

You may need to tune the code according to your implementation.

Thanks,
Ashim

---

Note : If this is helpful, Do not forget to mark as "Answered" or "Helpful"

Answer 2

Hi Ashim,

I could have done what you recommended but our ETL process is handled by a 3rd party tool. I cannot change the logic in the package as they're created/processed automatically by the tool.

Well, I did try to come up with my own set of logic and I've reached so far and still a little bit more help now needed on the mdx side as I'm not too sure how to do it.

Here are the measures that I created.

1. ZeroStock Days

iif([Measures].[QTY Onhand]=0 or [Measures].[QTY Onhand] is null,1,0)

2. Number of Zerostock Events   

iif([Measures].[ZeroStock Days]=0 AND ([Physical Date].[Time].NextMember,[Measures].[ZeroStock Days])=1,1,0)

I do get the required output but there problems with it.

Problem - I do not get a sum of Zerostock Days and Zerostock events if I roll up to week\month\year. (I think it has got to do with my 'Time' dimension and the Hierarchy or the mdx of that measure.)  

It looks somthing like this in Excel,

                   QTY OnHand                ZeroStock Days           Number of Zerostock Events      

Date

  Feb

2/1/2012            6

   nnn

2/29/2012          9

  March

3/1/2012            5                                   

3/2/2012            6                                                                             1

3/3/2012            0                                     1

3/4/2012            0                                     1

3/5/2012            0                                     1

3/6/2012            7                                                                             1

3/7/2012            0                                    1

3/8/2012            0                                    1

3/9/2012            8

3/10/2012         10

3/11/2012         9                                                                              1 

3/12/2012         0                                    1

3/13/2012         7

          

Answer 3

Exactly , That will be the issue and in that case you need to recalculate for week, month and year.

I will still say ,

If not ETL,  do it in "post processing" of ETL. But do it in Relational Data Warehouse.

That will make your life easy.

Thanks,

-Ashim

---

Note : If this is helpful, Do not forget to mark as "Answered" or "Helpful"

Answer 4

Hi Devangparikh26

I believe you can achieve the desired result by applying what I will show you in the following MDX:

WITH
	SET [Periods] AS NONEMPTY(DESCENDANTS([Date].[Calendar], , LEAVES), [Internet Sales Amount])
	MEMBER [Measures].[Rank] AS IIF( [QTY OnHand] = 0, RANK( [Date].[Calendar].CurrentMember, [Periods] ), NULL )
	MEMBER [Measures].[QTY OnHand] AS IIF( [Measures].[Internet Order Quantity] = 1, 0, [Measures].[Internet Order Quantity] )
	MEMBER [Measures].[Number of Zerostock Events] AS COUNT( TopCount( Filter( null:[Date].[Calendar].CurrentMember, [Rank] <> NULL ), [Rank]) )
SELECT 
	{[Measures].[QTY OnHand], [Number of Zerostock Events]} ON 0
	, {[Periods]} ON 1
FROM [Adventure Works]
	WHERE { [Customer].[Customer Geography].[Postal Code].&[77680]&[Roissy en Brie] }

This query runs on the Adventure Works DW Standard Edition.
In the example you need to regard the [Measures].[Internet Order Quantity] as your [Measures].[QTY OnHand], the trick in this example is to treat 1s as your 0s. That way we get a data set that looks kind of like yours. You should replace this calculated member with your original measure.

The next thing is to count the rows containing zeros.
We do so by assigning a rank to those [QTY OnHand] = 0 and otherwise assign a NULL. We then Filter( [Rank] <> NULL ) the set, TopCount( [Rank] ) it and counts the members in the resulting set.

If you have any questions, please do ask

---

HTH - please mark correct answers

Answer 5

Thanks for that Jens. I will try that soon in my environment but before that, what should I replace this measure with (in Bold)--

"SET [Periods] AS NONEMPTY(DESCENDANTS([Date].[Calendar], , LEAVES), [Internet Sales Amount])"

Should it be [QTY OnHand].

Also, could I create separate measures for Periods,Rank, and Count and then create a final calculated measure. I cannot create that query in my environment as we use a 3rd party tool to create measures and then users only drag&drop dimensions/measures in the excel spreadsheet.

Answer 6

Hi Devangparikh26

A) Yes, you should replace with [QTY OnHand]

B) Yes, you should be able to create these as calculated members in the cube script, and the access them through your 3rd party tool

---

HTH - please mark correct answers

Answer 7

Thanks for the prompt response.

I did create 3 separate measures and it seems to be not working because I guess the Periods measure (which is supposed to be a date) is not populating in the excel spreadsheet. Should I be using a particular format string to match to my datevalue.

Periods :

NONEMPTY(DESCENDANTS([Physical Date].[Time], , LEAVES), [Measures].[QTY])  

Answer 8

Hi Devangparikh26

Yes, you should also replace the time dimension, to query your specific dimension.hierarchy, eg.:

SET [Periods] AS NONEMPTY(DESCENDANTS([Your Dimension].[Your Hierarchy], , LEAVES), [QTY OnHand])

---

HTH - please mark correct answers

Answer 9

Hi,

I tried but its not working when i pull the periods across my time dimension.(as per attached image)

Here are my 3 calculated measures.   (for this example, I'm testing for value 643 instead of 0 as i don't have any 0 value).

Also, is the syntax for all mdx correct. Like for example I see you have used SET [Periods] AS... but I've not.

Periods:    NONEMPTY(DESCENDANTS([Physical Date].[Time], , LEAVES), [Measures].[QTY])

No of zerstock events: COUNT( TopCount( Filter( null:[Physical Date].[Time].CurrentMember, [Measures].[Rank] <> NULL ), [Measures].[Rank]) )

Rank: IIF( [Measures].[QTY] = 643, RANK( [Physical Date].[Time].CurrentMember,[Measures].[Periods] ), NULL )

Answer 10

After many tries,I finally got the answer to this puzzle. 

Zerostock days = sum(Descendants([Physical date].[Time].CurrentMember,3),iif([Measures].[QTY]<=0 or [Measures].[QTY] is null,1,0))

Zerostock Events =

sum(Descendants([Physical date].[Time].CurrentMember,3),IIF([Measures].[ZeroStock Days]=0 AND ([Physical Date].[Time].NextMember,[Measures].[ZeroStock Days])=1,1,0))

Avg Recovery Time =

[Measures].[ZeroStock Days]/iif([Measures].[Zerostock Events]=0 or [Measures].[Zerostock Events] is null,1,[Measures].[Zerostock Events])