7 Replies Latest reply: Aug 12, 2014 6:50 AM by Savo Marovic RSS

    FDQM LOAD  custom calculation script - ESSBASE adapter

    Savo Marovic

      Hi everybody,

       

      i am trying to modify default Essbase Adapter LOAD script, I entered year in order to avoid clearing data for all months per year. Now i want to add another dimension, a custom8(UD8) into the script in order to avoide clearing all data in custom8 ESSBASE dimension.i just want to limit clearing data for value in dimension custom8.

       

      This is the part of the LOAD script that i modified to add year:

       

      If strEntity <> strCurEnt Or bolFirstLine = RES.FDMTrue Then

       

       

        If Len(strCurEnt) > 0 Then

        strTYear = API.POVMgr.fPeriodKey(strPer(0)).strTargetYear

        '*** CALC SCRIPT ***

        ' NOTE: CALC SCRIPT CAN BE MODIFIED FOR EACH CUSTOMER

        ' This script was modified to also include the year.

        ' Clear the intersection of (Year, Category, Period, Entity)

        strClear = "Fix(""" & strTYear & """, """ & strTCat & """,@Idescendants(""" & strCurEnt & """))" & vbcrlf

        strClear = strClear & "CLEARDATA """ & strTPer(0) & """;" & vbcrlf

        strClear = strClear & "EndFix"

        '***************************************************************************

       

      What should i add in order to expand intersection to custom8 dimension?

       

      Regars,

      Savo

        • 1. Re: FDQM LOAD  custom calculation script - ESSBASE adapter
          Francisco Amores

          Hi,

           

          think that the CLEAR script is executed for each target entity in your DAT file.

           

          Therefore you have to start thinking about designing your solution for your requirement. And which is your requirement?

          Execute a clear for each Entity and all Custom8 associated to it (so intersection with that Entity?)

          If you have a look to the process, the action script is reading the DAT file to get the Entity. Therefore you could also read CUSTOM8, and generate a list with all CUSTOM8 for current entity being processed. You can then adjust the CLEAR script to include that list so you clear only those intersections.

           

          Hope that helps

          • 2. Re: FDQM LOAD  custom calculation script - ESSBASE adapter
            Savo Marovic

            Hi

             

            thanks for response!

             

            Currently, when i export and load data to Essbase with REPLACE option, data in Essbase is being cleared for the intersection (Year, Category, Period, Entity). Also, now, data for all other dimensions(custom 1,2,3,4....8) is being cleared as well, and that is not what i want calc scrip to do. I want it to clear data for the followin intersection (Year, Category, Period, Entity, Custom8). How can modify script in order to add custom8 dimension in intersection?

             

            So, something to look like this:

             

            If strEntity <> strCurEnt Or bolFirstLine = RES.FDMTrue Then

             

             

              If Len(strCurEnt) > 0 Then

              strTYear = API.POVMgr.fPeriodKey(strPer(0)).strTargetYear

              strCust8 = API.POVMgr.fPeriodKey(strPer(0)).strCustom8 ' i know is is not correct but some similar syntax to extract cutom8 dimension...

              '*** CALC SCRIPT ***

              ' NOTE: CALC SCRIPT CAN BE MODIFIED FOR EACH CUSTOMER

              ' This script was modified to also include the year.

              ' Clear the intersection of (Year, Category, Period, Entity)

              strClear = "Fix(""" & strCust8 & """, """ & strTYear & """, """ & strTCat & """,@Idescendants(""" & strCurEnt & """))" & vbcrlf

              strClear = strClear & "CLEARDATA """ & strTPer(0) & """;" & vbcrlf

              strClear = strClear & "EndFix"

              '***************************************************************************

             

            What should be the right syntax for this?

             

            Regards,

            Savo

            • 3. Re: FDQM LOAD  custom calculation script - ESSBASE adapter
              Francisco Amores

              Again,

               

              try to understand the code in the action script.

               

              The current code reads the .DAT file to get the target Entity.

              In the same way you can get the CUSTOM8 (also is CUSTOM8 in the file to be exported?

               

              You can then build a list with each CUSTOM8 value for current entity being processed ...  "CUSTOM8value1","CUSTOM8value2",...

               

              Then you can use that list in the CLEAR SCRIPT.

               

              The best way yo can proceed is by writing in a log file each CLEAR statement being executed. You can adjust the LOAD ACTION to this.

              In that way you will learn how entities are processed. Once you understand, then write your code to get the CUSTOM8 field from the line being read.

              Write each CUSTOM8 value to the log file... next step would be to concatenate all CUSTOM8 values and write the list to log file...

               

              Troubleshooting is a good way of learning.

               

              Hope that helps

              • 4. Re: FDQM LOAD  custom calculation script - ESSBASE adapter
                Savo Marovic

                Hi,

                 

                yes, Custom8(Dimension VERSION in Essbase) is being exported in .dat file. Value for Custom8 in .DAT file is always VR0.

                 

                regards,

                Savo

                • 5. Re: FDQM LOAD  custom calculation script - ESSBASE adapter
                  Francisco Amores

                  Ok,

                  if you always have the same value then your solution is even easier.

                   

                  You would need to evaluate performance of putting VR0 at the beginning of the FIX or at the end.

                   

                  Give a try and let us know.

                   

                   

                  strCust8 = "VR0"

                    '*** CALC SCRIPT ***

                    ' NOTE: CALC SCRIPT CAN BE MODIFIED FOR EACH CUSTOMER

                    ' This script was modified to also include the year.

                    ' Clear the intersection of (Year, Category, Period, Entity)

                    strClear = "Fix(""" & strCust8 & """, """ & strTYear & """, """ & strTCat & """,@Idescendants(""" & strCurEnt & """))" & vbcrlf

                    strClear = strClear & "CLEARDATA """ & strTPer(0) & """;" & vbcrlf

                    strClear = strClear & "EndFix"

                    '***************************************************************************

                  • 6. Re: FDQM LOAD  custom calculation script - ESSBASE adapter
                    Savo Marovic

                    Hi Francisco, sorry for late response. Thanks for the suggestion, i modified the script and its working as expected. Only data for VR0 are cleared now

                     

                    thank you very much!

                    • 7. Re: FDQM LOAD  custom calculation script - ESSBASE adapter
                      Francisco Amores

                      Happy to see it'a working as expected now.

                       

                      Please mark this question as correct/answered so others can see feedback if they have similar issues.

                       

                      Thanks