Tuesday, September 10, 2013

How does SET AGGMISSG make big difference to your calculation??

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 .
So i will always keep it ON to get  accurate data.


 
 

9 comments:

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

    ReplyDelete
  2. so, 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
  3. Thank you for the detailed by simple explanation

    ReplyDelete
  4. Thank you for the detail Explanation

    ReplyDelete
  5. Thanks for detailed and simple explanation

    ReplyDelete
  6. thank for your shareing.

    In conclusion,the benefit of using "SET AGGMISSG off" is reduced the calculating time.

    But when using this you need to clear the parant level to get correct data.

    ReplyDelete