9 Replies Latest reply on May 11, 2017 5:03 PM by TimG

    MaxL ASO clear data statement does not clear data

    Jedi Knight

      We are on version 11.1.2.4, Win Server 2012 Standard 64-bit. The following ASO MaxL statement:
          alter database ''.'' clear data in region "{...}" physical;

      does not work if {...} contains anything more than 1 tuple. The statement executes succesfully (and unusually fast) but the data does not get cleared from the cube!

      Did anyone have such issue and what can be advised?

       

      Thanks!

        • 1. Re: MaxL ASO clear data statement does not clear data
          Dayalan Punniyamoorthy

          I am sure you have read these but just making sure you are following this statement

           

          The region must be symmetrical. Members in any dimension in the region must be stored members. When physically clearing data, members in the region can be upper-level members in alternate hierarchies. (If the region contains upper-level members from alternate hierarchies, you may experience a decrease in performance.) Members cannot be dynamic members (members with implicit or explicit MDX formulas), nor can they be from an attribute dimension.

          https://docs.oracle.com/cd/E57185_01/ESBTR/maxl_altdb_as.html

           

          Can you confirm that the members are symmetrical, Not Dynamic & not attribute dimensions?

           

          Regards,

          Dayalan P.

           

          1 person found this helpful
          • 2. Re: MaxL ASO clear data statement does not clear data
            DrFosterMan

            It does seem odd that it completed successfully, I would just double check the log and make sure you see the OK/INFO message stating how many cells were removed.  Also, to troubleshoot, I would copy the contents of the {...} into a select query to double check what I am actually clearing and whether it makes sense.

            1 person found this helpful
            • 3. Re: MaxL ASO clear data statement does not clear data
              Jedi Knight

              If I clear {(Actual, FY13)}, that slice gets cleared successfully. If I clear {(Actual, FY12),(Actual, FY13)}, MaxL completes successfully but nothing actually gets cleared. The application log only says "Database was altered' with no more information.

              I checked the tuples and the set - it's correct (I guess otherwise MDX would end with an error message)

              I ran the MDX query on {(Actual, FY12),(Actual, FY13)}, and it completes successfully.

              • 4. Re: MaxL ASO clear data statement does not clear data
                TimG

                Yeah,  {(Actual, FY12),(Actual, FY13)} seems like a valid, symmetrical set to me. I'm assuming FY12 and FY13 are members in the same dimension, but I think that's a pretty safe assumption.

                 

                You could try CrossJoin({[Actual]}, {[FY12], [FY13]}) just to see if it behaves differently. But that ought to be equivalent.

                 

                Which patch is this? It's sounding like it might be a bug.

                • 5. Re: MaxL ASO clear data statement does not clear data
                  TimG

                  I thought I had seen this before, see:

                   

                  Essbase Users: Yet another ASO question...

                   

                  However, your set (if that is your exact, actual code above) still seems symmetric to me. There's another current Network54 thread asking a very similar question but with an asymmetric set: clear data in region

                   

                  The TechRef (partially*) defines symmetric sets like this:

                   

                  A cluster is a type of set derived using the CrossJoin function, where the arguments to CrossJoin are sets from one dimension only.

                  A cluster can also be thought of as a symmetric set.

                   

                  Well, your example can definitely be derived using the CrossJoin function with arguments to CrossJoin that are sets from one dimension only.

                   

                  I don't see why you should have to create the set with CrossJoin for that to be true (it's logically the same set whether specified manually or created with CrossJoin), but I would definitely try CrossJoin.

                   

                  *I say partially because a set of members just from one dimension also counts as a symmetric set (it certainly works in the clear command if you just list members from one dimension), even though you can't use CrossJoin to create it.

                  • 6. Re: MaxL ASO clear data statement does not clear data
                    DrFosterMan

                    Is it possible that it interprets the statement almost like an AND. Clear where a cell is this (Actual, FY12) and this (Actual, FY13)?  i always use crossjoin by default as I ran into some odd issues like this too long ago to remember what caused it.

                    1 person found this helpful
                    • 7. Re: MaxL ASO clear data statement does not clear data
                      TimG

                      Yeah, definitely possible - but it's a bug if so, because the documentation is clear that the "clear" command takes a set for the region specification (and that's not how a set is interpreted - it would be more like a filter).

                      1 person found this helpful
                      • 8. Re: MaxL ASO clear data statement does not clear data
                        Jedi Knight

                        I actually tried Crossjoin and it worked fine. I tend to agree that Essbase for some reason interprets a set of more than 1 tuple as being joined through AND instead of OR... I guess the only way out of it is to escalate with Oracle and only with Oracle itself...

                        • 9. Re: MaxL ASO clear data statement does not clear data
                          TimG

                          Yeah, please do report as a bug if you can, because I'm pretty certain it is. It's crazy that the command differentiates between two identical sets depending on how you constructed them.