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?
ReplyDeleteThank you for the detailed by simple explanation
ReplyDeleteThank you for the detail Explanation
ReplyDeleteThanks for detailed and simple explanation
ReplyDeleteThanks
ReplyDeletethank for your shareing.
ReplyDeleteIn 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.