4 Replies Latest reply: Oct 3, 2013 7:57 AM by AmarnathK

# ASO Rates Doubt

Hi everyone!

I'm working in an ASO cube in Essbase. My cube is composed by 15 dimensions and I need to calculate productivity (Sales / Quantity Employees). Both are accounts and the calculation is a simple division. My issue is that as long that I'm working with ASO, the number of employees is consolidated in top levels.

My dimensions are:

Accounts

Products (5 levels)

Packages (3 levels)

Combos (4 levels)

Clients (4 levels)

Type of Clients (3 levels)

Geography (3 levels)

What is happening? I have a level 0 data (2,000) and I assigned a "1" to the employee A in product X, besides I have the employee A with sales (3,000) in product Y assigned with a "1".

Emp1 -> X = 2,000      1

Emp1 -> Y = 3,000      1

When I see the sum of product for employee A the sales are equal to 5,000, that it is correct. But in the case the quantity of employees is "2" but I want to see a "1" as long as it is still employee A.

This is happening in all the possible combinations, and I don't know if its possible to formulate a member in order to count the unique employees in all possible combinations (like a NonEmpotyCount but I'm not familiar to MDX)

Regards,

• ###### 1. Re: ASO Rates Doubt

Did you look at NonEmptyCount? It has a example which checks how many markets are selling Cola and so on.

Regards

Celvin

http://www.orahyplabs.com

• ###### 2. Re: ASO Rates Doubt

Thank you Celvin,

Actually, I found NonEmptyCount very useful. I used this function and it worked perfectly.

NONEMPTYCOUNT(CrossJoin({Descendants ([Employees]  . CurrentMember , [Employees] . Levels(0) )} ,  {[Accounts] . [Sales]} ))

With this, the member with this formula is counting the number of employees according to the number of sales for all possible dimension combinations:

As I'm using a CrossJoin function I was not able to add the "Exclude_Missing" setting that it would improve performance probably.

Regards,

• ###### 3. Re: ASO Rates Doubt

For you to use exclude_missing then you should specify numeric_value_expression.

So something like this will verify correctly

NONEMPTYCOUNT(CrossJoin({Descendants ([Products]. CurrentMember , [Products] . Levels(0) )} ,  {[Measures] . [Units]} ), Jan, exclude_missing)

Regards

Celvin

http://www.orahyplabs.com

• ###### 4. Re: ASO Rates Doubt

I think you can use NonEmptySubset Witin CrossJoin I guess. have a look at the link

http://docs.oracle.com/cd/E40248_01/epm.1112/essbase_tech_ref/mdx_nonemptysubset.html

Amarnath