6 Replies Latest reply: Apr 21, 2014 12:40 PM by Alp Burak RSS

    #MISSING not working consistently

    cameronmcclurg

      I have a clear data script that uses an IF statement.  So, instead of ClearData, I'm setting the member to #MISSING.  This has worked for a long time and just recently was discovered to not be working.  More context: I'm using this to reload actuals every night from our ERP.  So, i clear the data (or set it to #MISSING), then reload.

       

      It looks like a lot of the level 0 members are getting set to #MISSING but some are not.  I've looked to see if there are differences between the good and bad members and can't find any differences.  They are Store members, etc.

       

      To fix, i went into SmartView and submitted a new $ amount, then reran the same calc and it cleared everything within the block (but still not the "bad" members).  So, now i'm wondering if there is some sort of caching thing going on or something like that. 

       

      I've used SET CREATENONMISSINGBLK ON; for moving data across cubes.  But this is like the reverse of that.  So i'm confused.

       

      Any obvious answer to this? 

       

      Thanks-

      Cameron

       

      Code:

       

       

       

      //ESS_LOCALE English_UnitedStates.Latin1@Binary

       

       

      FIX (@Relative("ALL_RESOURCE_TYPES",0),"Incurred")

       

       

      "Final"

      (

        IF(&IncSpanYr == 1)

        IF(&CurMoNm == 12)

        IF(@ISMBR(&CurYr))

        "May" = #MISSING;

        "Jun" = #MISSING;

        ELSEIF(@ISMBR(&NxtYr))

        "Jul" = #MISSING;

        ENDIF

        ELSEIF(&CurMoNm <> 12)

        IF(@ISMBR(&PriYr))

        "Jun" = #MISSING;

        ELSEIF(@ISMBR(&CurYr))

        IF(&NxtMoNm == 1) 

        "Jul" = #MISSING;

        ELSEIF(&NxtMoNm == 2)

        "Jul" = #MISSING;

        "Aug" = #MISSING;

        ELSEIF(&NxtMoNm == 3)

        "Jul" = #MISSING;

        "Aug" = #MISSING;

        "Sep" = #MISSING;

        ELSEIF(&NxtMoNm == 4)

        "Jul" = #MISSING;

        "Aug" = #MISSING;

        "Sep" = #MISSING;

        "Oct" = #MISSING;

        ELSEIF(&NxtMoNm == 5)

        "Jul" = #MISSING;

        "Aug" = #MISSING;

        "Sep" = #MISSING;

        "Oct" = #MISSING;

        "Nov" = #MISSING;

        ENDIF

        ENDIF

        ENDIF

        ELSEIF (&IncSpanYr <> 1)

        IF(@ISMBR(&CurYr))

        &PriMo = #MISSING;

        &CurMo = #MISSING;

        &NxtMo = #MISSING;

        ENDIF

        ENDIF

      )

       

       

      ENDFIX

        • 1. Re: #MISSING not working consistently
          Valerie Pierce-Oracle

          Hello Cameron,

           

           

          I have a feeling that you are not meeting the conditions you have set, and that # missing is not the culprit.   Please try setting MAY to a fixed value, say 555 and see it you are still missing numbers.  If so, then you are not meeting the conditions

           

           

            IF(&IncSpanYr == 1)

            IF(&CurMoNm == 12)

            IF(@ISMBR(&CurYr))

           

          Kind Regards,
          Valerie

          • 2. Re: #MISSING not working consistently
            cameronmcclurg

            I almost didn't want to include the if statement because the focus would be on that.  But it's not the if statement.  I know that because those are global variables that define which periods are touched.  And yet some projects are being cleared while others are not.  If it was a problem in the if, then all projects would be treated the same.  That's not the case here.  It's almost like even though there is data in the intersections, essbase doesn't recognize the block.  As soon as I submit a number to it and rerun, it recognizes that block and clears but not others.  Maybe a data defrag might help?

            • 3. Re: #MISSING not working consistently
              Valerie Pierce-Oracle

              Hi Cameron,

               

              Try a defrag and if that doesn't help, open a Support Ticket with Oracle Support and we can get your model in house and test.

               

              Thanks,

              Valerie

              • 4. Re: #MISSING not working consistently
                Alp Burak

                Hi,

                I suspect that the cause of your problem is that you are trying to clear a dense combination. Can you try it this way?

                SET UPDATECALC OFF;

                FIX (@Relative("ALL_RESOURCE_TYPES",0))

                FIX("May", "Jun")

                "Incurred"

                (

                @CALCMODE(BOTTOMUP);

                  IF(&IncSpanYr == 1)

                  IF(&CurMoNm == 12)

                  IF(@ISMBR(&CurYr))

                  "Final" = #MISSING;

                  ENDIF

                ENDIF

                ENDIF

                )

                ENDFIX

                 

                Cheers,

                Alp

                • 5. Re: #MISSING not working consistently
                  cameronmcclurg

                  Before I saw your post, I was looking at some of my other calcs and wondered if it was one of the calc commands that I was missing.  So, i added SET UPDATECALC OFF; and that did the trick.  I'm still wondering how/why this calc worked for 9 months but recently stopped working.  I know this setting has to do with clean/dirty cells but will need to research why my data was dirty previously and is now recently clean.  Any blog postings on this topic would be helpful.  Thanks!

                   

                  Cameron

                  • 6. Re: #MISSING not working consistently
                    Alp Burak

                    Hi,

                    This was discussed just recently. See below:

                    Intelligent calculation

                    One reason could be that you may have run a calc dim at all dimensipns or entirely restructured the database.

                     

                    Cheers,

                    Alp