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.
For example find below outline
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.
