1 2 Previous Next 15 Replies Latest reply: Oct 24, 2013 1:13 PM by CL RSS

    Essbase Error 1200416 in calculation script

    789069
      Hi All,

      I have the following script, where I am trying to roll-up a particular hierarchy.

      FIX(&CurrYr, "Actual", "Draft1", "TotalUnits",@idescendants("Geography"))

      "Local"
      IF(@ISMBR("Jun"))
      (
      @IDescendants("Desigs");
      )
      ENDIF

      ENDFIX

      I have skipped some dimensions in FIX since the script is to run for all the members under them. There is no issue with the syntax, but i get the following error:
      "Essbase Error 1200416 cannot assign double objects of different length"

      I want to keep "jun" in IF, since it is a dense dimension.
      Please help!I cannot get around this error.
      Thanks!
        • 1. Re: Essbase Error 1200416 in calculation script
          803603
          Hi,

          Move "Jun" from IF to FIX. I don't think you can call IDESCENDANTS in an IF block.

          HTH,
          Gerd
          • 2. Re: Essbase Error 1200416 in calculation script
            ghytiger
            "Local"
            IF(@ISMBR("Jun"))
            (
            @IDescendants("Desigs");
            )

            <=>

            "Local"
            IF(@ISMBR("Jun"))
            (
            "Local" = @IDescendants("Desigs");
            )
            • 3. Re: Essbase Error 1200416 in calculation script
              789069
              Yes, putting the "Jun" in FIX works. But it is a dense dimension and I do not think it is recommended to FIX on a dense dimension.
              Would it not affect the performance?
              • 4. Re: Essbase Error 1200416 in calculation script
                CL
                But it is a dense dimension and I do not think it is recommended to FIX on a dense dimension. Would it not affect the performance?
                ^^^You have to think about it within the context of the number of passes through the database. An IF test will cycle all of the blocks within your sparse outer FIX. So will a FIX on a dense dimension. The result being there's no difference in efficiency.

                That whole FIX-on-sparse, IF-on-dense rule has to be interpreted within the context of what's actually being calculated. I break the rule all the time based on logic requirements.

                Regards,

                Cameron Lackpour
                • 5. Re: Essbase Error 1200416 in calculation script
                  user10986157

                  Cameron,

                   

                  I have a similar question.  Can I contact you directly?  I will search for you on Linked-In and send you an invite.

                   

                  Thanks,

                  Todd Cox

                  • 6. Re: Essbase Error 1200416 in calculation script
                    AmarnathK

                    Not really

                    There is no implication / hard and fast rule that you have to always put a Dense Dimension in an IF. You can move it and it all depends on how and what you are calculating. In your example, you are trying to AGGREGATE rather than performing CALCULATION.

                    FIX(&CurrYr, "Actual", "Draft1", "TotalUnits",@idescendants("Geography"),"Local","Jun")

                    @IDescendants("Desigs");

                      ENDFIX

                     

                    Try this and it will work

                     

                    Regards

                    Amarnath

                    ORACLE | Essbase

                    • 7. Re: Essbase Error 1200416 in calculation script
                      SreekumarHariharan

                      Off course you could FIX on a dense dimension as the Essbase parsing engine will allow.While doing ,it will be a performance hit because multiple passes through the same blocks will take place.IF on dense dimensions members will allow the IF logic command to be performed, while the block is in memory & will end up being a single pass operation.Best Practice would be Put the IF on a Dense dimension member

                       

                       

                       

                       

                      Thanks,

                      Sreekumar Hariharan

                      • 8. Re: Essbase Error 1200416 in calculation script
                        GlennS_3

                        Sreekumar,

                        having a dense dimension will not necessarily have a performance hit. It would only do so IF you ONLY have dense dimensions in the FIX.  Having a combination of sparse and dense dimensions means the sparse dimensions will restrict the number of blocks and the the dense dimension will restrict the cells within the block that are looked at. I have proven in the past (for the case I tested) that it is faster if the dense dimension is in the fix than in an IF. If statements are slow to evaluate compared to the FIX. What you describe as best practice is a myth. It is/was a generalized recommendationin old Essbase versions and has been caried forward. As the wise and benevolent Cameron  said, you have to evaluate the context of what is being calculated and what is in the FIX

                        • 9. Re: Essbase Error 1200416 in calculation script
                          TimG

                          I don't think it's so much that it no longer applies in current versions as that people don't understand that it only ever applied if you had multiple conditions testing more than one dense member.

                           

                          I still think that...

                           

                          FIX("Dense1", "Sparse1")

                               Do something to Dense1

                          ENDFIX

                           

                          FIX"Dense2", "Sparse1")

                               Do something to Dense2

                          ENDFIX

                           

                          ...is often going to be slower than...

                           

                          FIX("Sparse1")

                               IF "Dense1"

                                    Do something to Dense1

                               IF "Dense2"

                                    Do something to Dense2

                          ENDFIX

                           

                          ...unless the subset of blocks is small enough that caching (Essbase or OS) results in the "Sparse1" blocks not having to be read from / written back to disk twice.

                          • 10. Re: Essbase Error 1200416 in calculation script
                            GlennS_3

                            TimG,

                            Ah the emailed version I got had a typo that it appears you fixed. I agree with you that having two fix statements will be slower than the the fix and the IF, BUT  if you did something like

                            FIX(Sparse1,Dense1,Dense2)

                                ( calc1  * @ISMBR(dense1)

                                  Calc2 * @ISMBR(Dense2)

                                )

                            ENDFIX

                            will be faster as you are only making one pass through the database.

                             

                            In your example, I'm assuming you have seperate IF statements and not ELSEIFs or nested IFs or the calculatsions won't work right.

                            • 11. Re: Essbase Error 1200416 in calculation script
                              TimG

                              Yes, I had a "Sparse2" in there.

                               

                              I agree it's all about reducing passes through the database, but two distinct IFs within a single FIX still only touches each block once (if the log information is to be believed).  If using TRUE = 1 trick is faster, I think it can only be because it's quicker to evaluate in one step than the IF statement and additional calculation.  I'm not sure that we're disagreeing on that though.

                              • 12. Re: Essbase Error 1200416 in calculation script
                                CL

                                I agree with Tim -- it really is all about how many bits of data get loaded in, manipulated, and written out.

                                 

                                Glenn, while I am loathe to quibble with someone who calls me "wise and benevolent" as those properties are all too rarely ascribed to me, I would caution that the trick with the boolean value is not intuitive and could potentially end up being a painful trip into too-clever code.  Or maybe I don't give Hyperion admins enough credit -- I certainly had to look at it twice.  It certainly is a clever hack.

                                 

                                Regards,

                                 

                                Cameron Lackpour

                                • 13. Re: Essbase Error 1200416 in calculation script
                                  GlennS_3

                                  Cameron,

                                  The OP's question had to do with performance not readability. I agree Tim's suggest is sound and good.

                                  • 14. Re: Essbase Error 1200416 in calculation script
                                    TimG

                                    How are consultants supposed to get repeat business without obfuscating their code?

                                     

                                    Seriously, if testing showed a worthwhile benefit, I would definitely comment that one!

                                    1 2 Previous Next