SET AGGMISSG works with sparse
dimensions, As given in the technical reference if it is set to OFF it will not
aggregate #Missing values ,which makes big difference to our calculations. If we
look at the below detailed .................example it can explain how it works.

If I have an outline like below,

Account(Dense)

A1

A2

A3

Department(Sparse)

D1

D2

D3

And I want to load some data to
Department level 0 and aggregate the dimension to get data at parent level as
my cube is BSO I have to write some calculation to get it aggregated. Assume I
have data like below.

Case 1:

A1->D1 =10

A1->D2 =20

A1->D3 =20

And I will use AGG function or CALC
DIM to aggregate Department data .

SET AGGMISSG OFF

Fix(Account)

AGG(Department);

ENDFIX;

Now the data looks like

A1->Department = 50

Case 2 : Again run the calculation
by sending # Missing to one of the intersections like below.

A1->D1=10

A1->D2 =#Missing

A1->D3 =20

SET AGGMISSG OFF

Fix(Account)

AGG(Department);

ENDFIX;

Now the data looks like

A1->Department = 30,which is
correct .

Case 3 :

Now clear all the data and load like
below, and run the calculation.

A1->D1=10

A1-> D2 =20

A1-> D3 =20

SET AGGMISSG OFF

Fix(Account)

AGG(Department);

ENDFIX;

Now the data looks like

A1-> Department = 50,which is
correct.

Then send #Missing to all the
intersections and run the calc like below

A1->D1=#Missing

A1-> D2 =#Missing

A1-> D3 =#Missing

SET AGGMISSG OFF

Fix(Account)

AGG(Department);

ENDFIX;

Now the data looks like

A1-> Department = 50,which
is Not correct because we don’t have data for Department children but still it
is showing 50 .We have explanation about how it will work but not in a
elaborated way to show how exactly it make difference to a calculation results.

Now run the same calculation by keeping AGGMISSG
ON

SET AGGMISSG ON

Fix(Account)

AGG(Department);

ENDFIX;

Now the data looks like below

A1->
Department = #Missing which is correct.

So it will not aggregate #Missing values when you
set it to OFF only when you don’t have
data for all the combinations. It will only make difference when you don’t have data for all its children of particular
member ,it doesn't matter whether or not you set it to OFF or ON if we have
data for

atleast one child .

atleast one child .

So i will always keep it ON to get accurate data.

well explained.

ReplyDeleteThanks well explained. otherwise it doesn't make sense to me

ReplyDeleteCLW

ReplyDeleteso, I might be missing something here but when is it a benefit to have it switched off? and if there is no benefit why does it even exist?

ReplyDelete