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.