6 Replies Latest reply: Jun 17, 2010 4:46 PM by 615687 RSS

    Calculation performance when executing copy calc script

    Javi M
      Hello everybody:

      I'm executing this calc script:

      SET MSG DETAIL;

      SET CREATEBLOCKONEQ ON;
      SET CREATENONMISSINGBLK OFF;
      SET CALCPARALLEL 3;

      FIX("MES_01", "Y_2010", "NE_PA_ESPA")
           FIX(@LEVMBRS ("INDICADORES",0))
                FIX(@LEVMBRS ("TIPO_LLAMADA",0))
                     FIX(@LEVMBRS ("TOT_CANAL",0))
                          FIX(@LEVMBRS ("TOT_PROD",0))
                               FIX(@LEVMBRS ("TOT_EMP",0))
                                    FIX(@LEVMBRS ("TOT_SERV",0))
                                         "ES_EC" = "ES_REAL";
                                    ENDFIX
                               ENDFIX
                          ENDFIX
                     ENDFIX
                ENDFIX
           ENDFIX
      ENDFIX

      And it spends hours and hours. I can not explain myself how is possible if the database has 41382 level 0 members with a block size of 21696 (600 Mb). I am trying to copy data from one scenario (ES_REAL) to another (ES_EC). I can not use 'Datacopy' because I have to copy only the first five months of the year (PERIODO = PERIOD = dense) and when I use 'Datacopy' it copies full blocks of data. 'INDICADORES' and 'TIPO_DATO' are also dense dimensions. 'TIPO_DATO' is not in the FIXes because it has only two dense members.

      This is not my first try, that is why this script has this structure. I made it that way because I hoped that I could retrieve more information about what the calc is doing in the log file. I am also monitoring with Windows Task Manager the I/O write/read bytes and it seems as if it was doing nothing.

      Any ideas about what is happening?

      Thank you in advance

      Regards

      Javier
        • 1. Re: Calculation performance when executing copy calc script
          774005
          here is my 2 cents of information.

          I think you are set msg detail for testing purpose. try to remove that because it will significantly improve the performance. Why are you using so many fix statements? Try using one fix statement.
          • 2. Re: Calculation performance when executing copy calc script
            774005
            Have you tried using Datacopy by fixing the required 5 months in the fix statement?
            • 3. Re: Calculation performance when executing copy calc script
              615687
              "And it spends hours and hours. I can not explain myself how is possible if the database has 41382 level 0 members with a block size of 21696 (600 Mb)".

              Correct me if I am wrong. As per the above info, Your block size is 21696 B and Database size is 600 MB. Is your database is tuned and how long does it take to Export the data what you are trying to copy now? Do you see bulk of your data in those intersextions. Don't see any issue to use DATACOPY if you want to copy only months of data. Did you check the CPU and Memory utlization of the process as well? Obviously there are many factors that will derive your calculation times.

              How long does your Level 0 export takes? Is that also takes hours? It completes in minutes I would take columnar export and find the estimate of the data blocks you are trying to copy using calc script.

              Thanks,
              Naveen Mikkilineni
              • 4. Re: Calculation performance when executing copy calc script
                Javi M
                Hello:

                About this question:

                here is my 2 cents of information.

                I think you are set msg detail for testing purpose. try to remove that because it will significantly improve the performance. Why are you using so many fix statements? Try using one fix statement.

                I use so many FIXes hoping that this shows more information in the log file. But it actually do not. Due to the fact that no information is registered in the log file, I think "SET MSG DETAIL" do not have any performance effect in this case.

                My first try was with just one FIX and it did not work.

                More ideas?

                Thank you

                Javier
                • 5. Re: Calculation performance when executing copy calc script
                  Javi M
                  Hello again:

                  Have you tried using Datacopy by fixing the required 5 months in the fix statement?

                  I usually work with 'Datacopy' for copying information within the cube. But last time I tried to copy just 5 months (dense dimension) with 'Datacopy', users told me that the rest of the months were overwritten.

                  Can you assure me that 'Datacopy' can work with just a few members of dense dimensions?

                  Correct me if I am wrong. As per the above info, Your block size is 21696 B and Database size is 600 MB. Is your database is tuned and how long does it take to Export the data what you are trying to copy now? Do you see bulk of your data in those intersextions. Don't see any issue to use DATACOPY if you want to copy only months of data. Did you check the CPU and Memory utlization of the process as well? Obviously there are many factors that will derive your calculation times.

                  How long does your Level 0 export takes? Is that also takes hours? It completes in minutes I would take columnar export and find the estimate of the data blocks you are trying to copy using calc script.

                  NM, this database spends less than one minute to export all level 0 blocks. That is why I could not understand how it spends hours without ending. I started to use just 1 CPU, but now I use 3 CPUs with the same results. There are no lack of memory resources at all. As I have asked before, could you confirm me that 'Datacopy' works perfectly with dense dimensions meaning that you can include them in a FIX statement?

                  Thank you for your help

                  Regards

                  Javier
                  • 6. Re: Calculation performance when executing copy calc script
                    615687
                    Javi,

                    I use DATACOPY command most of the times to forecast and budget the data. Time periods being dense dimsnion members I can copy the data from current month to all future months using DATACOPY command without effecting prior months data.

                    That being said, Did you see increase in databse size since it is executing for the long time? Some thing seems to be not right with your calculation. By chance did you turn on CREATENONMISSINGBLK command. Just double checking.

                    Take small slice of database where you have data and run the copy data again and see if that atleast finishes quick

                    Thanks,
                    Naveen Mikkilineni