12 Replies Latest reply: Sep 12, 2012 2:26 PM by HypSvc RSS

    Calc Script to clear data for a month

    HypSvc
      Hi

      I am trying to work on a cleardata script to clear the data for a specific month like March so i try to create 2 simple scripts but i had no luck can someone please advise. any help is greately appericiiated.

      1)

      CLEARDATA Actul->Year->Month;

      2)

      FIX(Actual,Year)

      CLEARDATA Month;

      ENDFIX;

      Please note
      Actual and year is a sparse dimension and Month is a dense dimension.

      Thanks,
        • 1. Re: Calc Script to clear data for a month
          GlennS_3
          I prefer the second script over the first. You say you have had no luck, I'll guess you need to include set Updatecalc Off; in the beginning of the script. If intelligent calc is on, then it is possible it would not find :dirty" blocks and would not clear anything.
          • 2. Re: Calc Script to clear data for a month
            715835
            I agree with Glenn, I prefer the second script.

            You might want to consider creating a Substitution Variable such as CurMonth.

            FIX("Actual","Year")
            CLEARDATA &CurMonth;
            ENDFIX

            Creating a variable allows you to set it in one place without having to maintain the calc script, with the bonus that you can reuse the same variable in other scripts without having to maintain those scripts either.

            Good Luck,

            Tim Young
            • 3. Re: Calc Script to clear data for a month
              HypSvc
              Thank You GlennS_3 & tyoung358

              i dont know why that didnt work either i have tried these approches i did create 3 Substitution Variable for month year and scenario and the script just runs for like 20 hrs and cleans nothing.

              on top of the head how long do you think it should take to clear the data of one month with calcparallel set to 3.

              Thanks again
              • 4. Re: Calc Script to clear data for a month
                GlennS_3
                it is impossible for me to tell. I don't know how many dimensions or member you have in the cube. IT could be a few seconds to many hours. Your clear is clearing all levels of the database
                • 5. Re: Calc Script to clear data for a month
                  HypSvc
                  Yes clearing all the level for that month and we have 13 standard dimensions and 5 attributes dimension.
                  • 6. Re: Calc Script to clear data for a month
                    GlennS_3
                    attribute dimensions don't play into it, but with 13 standard dimenions could take a long time.
                    • 7. Re: Calc Script to clear data for a month
                      715835
                      If the previous suggestions won't work, try another approach (using Glenn's excellent suggestion about turning off intelligent calc):

                      SET UPDATECALC OFF;

                      FIX("Actual","Year")
                      "Month" = #Missing;
                      ENDFIX

                      Or something along these lines:

                      SET UPDATECALC OFF;

                      "ACTUAL"
                      (
                      IF(@ISMBR("Month"))
                      "Year" = #Missing;
                      ENDIF
                      )

                      Glenn can point out any logical errors I may have made. Also as he mentioned, it is impossible to tell how long it will take to run a given Essbase calc.

                      Tim Y
                      • 8. Re: Calc Script to clear data for a month
                        HypSvc
                        Thanks alot guys i really appericiate your help i will try these approches as soon as i can and update you to see which one worked out best for me.

                        Thanks again,
                        • 9. Re: Calc Script to clear data for a month
                          GlennS_3
                          Tyoung358,
                          The first calc would be faster than the second as IF stamtents can be slow.
                          • 10. Re: Calc Script to clear data for a month
                            715835
                            I'm inclined to agree with you Glenn. What gave me pause was that Month is dense, which (1) means everyblock would have to be tested by the Fix statement and (2) it may explain why the calc won't run properly.

                            My thought process behind suggesting the second approach goes back to the old "Use IF statements on dense members" theory - that the calc may run faster by ignoring blocks where the IF member doesn't have data.

                            Is that theory no longer valid? I know that Essbase theory has changed over the years.

                            I'll see you in San Antonio next month.

                            Thanks,

                            Tim
                            • 11. Re: Calc Script to clear data for a month
                              GlennS_3
                              Tim,
                              While I have no hard and fast evidence, If you fix on a combination of sparse and dense dimensions, it seems the sparse dimensions limit the number of blocks that are selected and the dense limits further to only the cells in the block that meet the dense criteria. As I said in an earlier post, using IF can slow down calculations as it has to evaluate every time and will go through all the cells in the block rejecting a lot of them. . Having it in the fix removes that evaluation. On a big database I tested on (A long while ago) putting the dense dimension in the fix saved about 20% calculation time over using an IF statement.
                              • 12. Re: Calc Script to clear data for a month
                                HypSvc
                                It turned out that the desing of the application was causing the script to run for days. So we actually skipped this part but i did really found the scripts very usefull.

                                Thanks,