I'm becoming crazy with a calculated measure into OLAP Analytic Workspace Manager, that is defined like next OLAP Expression syntax:
NVL(CUBE.MEASURE1[DIM1= 'A'], 0) + NVL(CUBE.MEASURE2[DIM2= 'B'], 0)
Where:
CUBE.MEASURE1 and CUBE.MEASURE2 are not calculated measures, they are stored measures.
DIM1 and DIM2 are edges of the CUBE, A and B values both exist in their dimensions.
In most of all my queries, the calculated measure retrieves correct results, when both members of the sum retrieves data. But I other cases the calculated measure retrieves null !!!
In these cases, the calculated measure retrieves null when CUBE.MEASURE2[DIM2= 'B'] retrieves NO results. But I expect that if any of both QDR expresions retrieves no results, NVL function will replace it by 0.
I was reading about it, situations when QDR expressions retrieves no results, by default it throws and error and not null o NA value. I found that exist 2 ORACLE DML options that can manage this type of situations:
LIMITSTRICT = NO
(http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_options043.htm#OLADM384)
OKNULLSTATUS = YES
(http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_options077.htm#OLADM418)
I tried to create a DML Function in the AW to set both options, first to NO and second to YES, calling this function with OLAP_DML_EXPRESSION('MyFUNCTION', NUMBER) statement, but it doesn`t work
Please, I need a workaround of that, how I can catch these situations? Must I create a DML program to solve it? Where can I set this options (LIMITSTRICT,OKNULLSTATUS) by default and not set them in each measure calculation?
