1 2 Previous Next 16 Replies Latest reply: Jul 9, 2012 5:41 AM by 783124 RSS

    FDM event scripts firing twice during data loads

    783124
      Here's an interesting one. I have added the following to three different event scripts (one at a time, ensuring only one of these exists at any one time), to clear data before loading to Essbase:


      Event Script content:
      ' Declare local variables
      Dim objShell
      Dim strCMD
      ' Call MaxL script to run data clear calculation.
      Set objShell = CreateObject("WScript.Shell")
      strCMD = "D:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseClient\bin\startMAXL.cmd D:\Test.mxl"
      API.DataWindow.Utilities.mShellAndWait strCMD, 0


      MaxL Script:
      login ******* identified by ******* on *******;
      execute calculation 'FIX("Member1","Member2") CLEARDATA "Member3"; ENDFIX' on *******.*******;
      exit;




      However, it appears that the clear is carried out twice, both before and after the data has been loaded to Essbase. This has been verified at each step by checking the Essbase application log:

      No event script:
      - No Essbase data clear in application log

      Adding above to "BefExportToDat" event script:
      - Script is executed once after clicking on Export in FDM Web Client (before the "Target System Load" modal popup is displayed). Entries are visible in Essbase Application log.
      - Script is then executed a second time when clicking on the OK button in the "Target System Load" modal popup. Entries are visible in Essbase Application log.

      Adding above to "AftExportToDat" event script:
      - Script is executed once after clicking on Export in FDM Web Client (before the "Target System Load" modal popup is displayed). Entries are visible in Essbase Application log.
      - Script is then executed a second time when clicking on the OK button in the "Target System Load" modal popup. Entries are visible in Essbase Application log.

      Adding above to "BefLoad" event script:
      - Script is NOT executed after clicking on Export in FDM Web Client (before the "Target System Load" modal popup is displayed).
      - Script is executed AFTER the data load to Essbase when clicking on the OK button in the "Target System Load" modal popup. Entries are visible in Essbase Application log.

      Some notes on the above:
      1. "BefExportToDat" and "AftExportToDat" are both executed twice, before and after the "Target System Load" modal popup. :-(
      2. "BefLoad" is executed AFTER the data is loaded to Essbase! :-( :-(

      Does anyone please have any idea how we might execute an Essbase database clear before loading data, and not after we have loaded fresh data? And perhaps on why the above event scripts appear to be firing twice?! There does not appear to be any logic to this!


      BefExportToDat - Essbase Application Log entries:
      +[Wed May 16 16:19:51 2012]Local/Monthly/Monthly/admin@Native Directory/140095859451648/Info(1013091)+
      +Received Command [Calculate] from user [admin@Native Directory]+

      +[Wed May 16 16:19:51 2012]Local/Monthly/Monthly/admin@Native Directory/140095859451648/Info(1013162)+
      +Received Command [Calculate] from user [admin@Native Directory]+

      +[Wed May 16 16:19:51 2012]Local/Monthly/Monthly/admin@Native Directory/140095859451648/Info(1012555)+
      +Clearing data from [Member3] partition with fixed members [Period(Member1); Scenario(Member2)]+
      +...+

      +[Wed May 16 16:20:12 2012]Local/Monthly/Monthly/admin@Native Directory/140095932016384/Info(1003037)+
      Data Load Updated [98] cells

      +[Wed May 16 16:20:12 2012]Local/Monthly/Monthly/admin@Native Directory/140095932016384/Info(1003024)+
      Data Load Elapsed Time : [0.52] seconds
      +...+

      +[Wed May 16 16:20:12 2012]Local/Monthly/Monthly/admin@Native Directory/140095930963712/Info(1013091)+
      +Received Command [Calculate] from user [admin@Native Directory]+

      +[Wed May 16 16:20:12 2012]Local/Monthly/Monthly/admin@Native Directory/140095930963712/Info(1013162)+
      +Received Command [Calculate] from user [admin@Native Directory]+

      +[Wed May 16 16:20:12 2012]Local/Monthly/Monthly/admin@Native Directory/140095930963712/Info(1012555)+
      +Clearing data from [Member3] partition with fixed members [Period(Member1); Scenario(Member2)]+


      AftExportToDat - Essbase Application Log entries:
      +[Wed May 16 16:21:32 2012]Local/Monthly/Monthly/admin@Native Directory/140095933069056/Info(1013091)+
      +Received Command [Calculate] from user [admin@Native Directory]+

      +[Wed May 16 16:21:32 2012]Local/Monthly/Monthly/admin@Native Directory/140095933069056/Info(1013162)+
      +Received Command [Calculate] from user [admin@Native Directory]+

      +[Wed May 16 16:21:32 2012]Local/Monthly/Monthly/admin@Native Directory/140095933069056/Info(1012555)+
      +Clearing data from [Member3] partition with fixed members [Period(Member1); Scenario(Member2)]+
      +...+

      +[Wed May 16 16:21:47 2012]Local/Monthly/Monthly/admin@Native Directory/140095930963712/Info(1003037)+
      Data Load Updated [98] cells

      +[Wed May 16 16:21:47 2012]Local/Monthly/Monthly/admin@Native Directory/140095930963712/Info(1003024)+
      Data Load Elapsed Time : [0.52] seconds
      +...+

      +[Wed May 16 16:21:47 2012]Local/Monthly/Monthly/admin@Native Directory/140095928858368/Info(1013091)+
      +Received Command [Calculate] from user [admin@Native Directory]+

      +[Wed May 16 16:21:47 2012]Local/Monthly/Monthly/admin@Native Directory/140095928858368/Info(1013162)+
      +Received Command [Calculate] from user [admin@Native Directory]+

      +[Wed May 16 16:21:47 2012]Local/Monthly/Monthly/admin@Native Directory/140095928858368/Info(1012555)+
      +Clearing data from [Member3] partition with fixed members [Period(Member1); Scenario(Member2)]+


      BefLoad - Essbase Application Log entries:
      +[Wed May 16 16:23:43 2012]Local/Monthly/Monthly/admin@Native Directory/140095932016384/Info(1013091)+
      +Received Command [Calculate] from user [admin@Native Directory]+

      +[Wed May 16 16:23:43 2012]Local/Monthly/Monthly/admin@Native Directory/140095932016384/Info(1013162)+
      +Received Command [Calculate] from user [admin@Native Directory]+

      +[Wed May 16 16:23:43 2012]Local/Monthly/Monthly/admin@Native Directory/140095932016384/Info(1012555)+
      +Clearing data from [Member3] partition with fixed members [Period(Member1); Scenario(Member2)]+
      +...+

      +[Wed May 16 16:23:44 2012]Local/Monthly/Monthly/admin@Native Directory/140095929911040/Info(1003037)+
      Data Load Updated [98] cells

      +[Wed May 16 16:23:44 2012]Local/Monthly/Monthly/admin@Native Directory/140095929911040/Info(1003024)+
      Data Load Elapsed Time : [0.52] seconds
      +...+

      +[Wed May 16 16:23:45 2012]Local/Monthly/Monthly/admin@Native Directory/140095860504320/Info(1013091)+
      +Received Command [Calculate] from user [admin@Native Directory]+

      +[Wed May 16 16:23:45 2012]Local/Monthly/Monthly/admin@Native Directory/140095860504320/Info(1013162)+
      +Received Command [Calculate] from user [admin@Native Directory]+

      +[Wed May 16 16:23:45 2012]Local/Monthly/Monthly/admin@Native Directory/140095860504320/Info(1012555)+
      +Clearing data from [Member3] partition with fixed members [Period(Member1); Scenario(Member2)]+
        • 1. Re: FDM event scripts firing twice during data loads
          783124
          I have done further tests, and you should be able to reproduce the same results easily too:

          1. Create a folder at D:\TEST to hold the test files.

          2. Copy the following script into the following four Event Scripts (updating the section "_BefExportToDat.txt" with the name of the script):
          - BefExportToDat
          - AftExportToDat
          - BefLoad
          - AftLoad

          Dim strF, fso, tf, t, temp, m, miliseconds

          t = Timer
          temp = Int(t)
          m = Int((t-temp)*1000)
          miliseconds = String(4 - Len(m), "0") & m
          strF = "D:\TEST\" & Replace(Time, ":", ".") & "." & miliseconds & "_BefExportToDat.txt"

          Set fso = CreateObject("Scripting.FileSystemObject")
          Set tf = fso.CreateTextFile(strF, True)
          tf.WriteLine(strF)
          tf.Close
          Set fso = Nothing


          3. Import/Validate/Export any file in FDM.

          4. See the results in D:\TEST.

          Every export appears to generate eight files. Two are generated before the load to Essbase, and six are generated afterwards. This is the order in which they were generated for me:

          18.58.26.0414_BefExportToDat.txt
          18.58.28.0367_AftExportToDat.txt
          18.58.40.0179_BefLoad.txt
          18.58.40.0507_AftLoad.txt
          18.58.40.0539_BefExportToDat.txt
          18.58.40.0789_AftExportToDat.txt
          18.58.40.0789_BefLoad.txt
          18.58.41.0023_AftLoad.txt
          • 2. Re: FDM event scripts firing twice during data loads
            LarryR
            James, the Export and Load Event scripts will fire four times, once for each file type: The .DAT file (main TB file), -A.DAT file (journal file), -B.DAT, and -C.DAT.

            To get around this and only execute during the load of the main TB file, add the following or something similar to the beginning of your Event scripts. This assumes that strFile is in the Subroutine's parameter list:
            Select Case LCase(Right(strFile,6))
                 Case "-a.dat", "-b.dat", "-c.dat" Exit Sub
            End Select
            • 3. Re: FDM event scripts firing twice during data loads
              783124
              Hi Larry,

              Many thanks for your response, this was very helpful and I will try your suggestion.

              It still seems a little odd to me why the "BefExportToDat" and "AftExportToDat" events should fire again during the load itself, and not during the actual export. This is the sequence of events:

              After clicking on Export in Workflow, the Target System Load modal popup is displayed, and the first two files have been generated:
              18.58.26.0414_BefExportToDat.txt
              18.58.28.0367_AftExportToDat.txt

              After clicking on OK in the Target System Load modal popup, the actual load to Essbase takes place. A further six files are generated, and previous event scripts are re-fired:
              18.58.40.0179_BefLoad.txt
              18.58.40.0507_AftLoad.txt
              *18.58.40.0539_BefExportToDat.txt*
              *18.58.40.0789_AftExportToDat.txt*
              18.58.40.0789_BefLoad.txt
              18.58.41.0023_AftLoad.txt

              Does this still tie in with your suggestion? Do you happen to know why these event scripts are fired in this order? And do you please know what the "-B.Dat" and "-C.Dat" files contain? :-) Our exports do not appear to be generating these at present, but it would be useful to know so that we can code the event scripts accordingly.
              • 4. Re: FDM event scripts firing twice during data loads
                LarryR
                I've never found a use for these files, so I don't know their exact purpose. IMO, this is poorly documented, but, if you look in the API Guide, you will see some detail on Actions ActExportA-C, and ActLoadA-C. These are actions focused on the -A to -C alternate files. The API Guide only describes the Actions as "alternate actions for specialized data such as journals or intercompany." If you want to verify the order of the additional exports and loads, add the writing of the values of variables strFile, etc., to your code.
                • 5. Re: FDM event scripts firing twice during data loads
                  LarryR
                  If you look in the Outbox, you may see the alternate files. -A.Dat is for journal loading, -B.Dat is for cell text loading, and -C.Dat is for Drillable Region Load. As I said, I've never used these, in fact, our code deletes these immediately after Load since they are nothing more than clutter to us.
                  • 6. Re: FDM event scripts firing twice during data loads
                    783124
                    Hi Larry,

                    As mentioned, our exports do not appear to be generating the "-B.Dat" and "-C.Dat" files at present. However, you are correct with the Export and Load event scripts firing twice (once for the main TB file and again for the journal file). Does this also mean it could continue to fire an additional two times for the "-B.Dat" and "-C.Dat" files?

                    On the last run, the output was as follows with the modified scripts:

                    After clicking on Export in Workflow, the Target System Load modal popup is displayed, and the first two files have been generated:
                    14.24.15.0527_BefExportToDat.txt
                    14.24.17.0617_AftExportToDat.txt

                    After clicking on OK in the Target System Load modal popup, the actual load to Essbase takes place. A further six files are generated:
                    14.24.21.0289_BefLoad.txt
                    14.24.22.0117_AftLoad.txt
                    *14.24.22.0152_BefExportToDat-A.txt*
                    *14.24.22.0414_AftExportToDat-A.txt*
                    *14.24.22.0433_BefLoad-A.txt*
                    *14.24.22.0652_AftLoad-A.txt*

                    This makes a lot more sense, since one can see that the event scripts are being run a second time against the journal files during the data load. Many thanks, this solves my problem as I can now place my script where I want in the process chain. It's just a shame that there are not separate event scripts to distinguish between the various .Dat exports/loads, which are clearly occuring at separate times in the process chain.

                    Many thanks! :-)

                    P.S. Updated script below if anyone wishes to use it:

                    Sub BefExportToDat(strLoc, strCat, strPer, strTCat, strTPer, strFile)
                    Dim strF, fso, tf, t, temp, m, miliseconds, strSuf

                    t = Timer
                    temp = Int(t)
                    m = Int((t-temp)*1000)
                    miliseconds = String(4 - Len(m), "0") & m
                    strF = "D:\TEST\" & Replace(Time, ":", ".") & "." & miliseconds & "_BefExportToDat"

                    strSuf = UCase(Left(Right(strFile,6),2))
                    If strSuf = "-A" Or strSuf = "-B" Or strSuf = "-C" Then
                    strF = strF & UCase(strSuf) & ".txt"
                    Else
                    strF = strF & ".txt"
                    End If

                    Set fso = CreateObject("Scripting.FileSystemObject")
                    Set tf = fso.CreateTextFile(strF, True)
                    tf.WriteLine(strFile)
                    tf.Close
                    Set fso = Nothing
                    End Sub
                    • 7. Re: FDM event scripts firing twice during data loads
                      LarryR
                      You're probably not seeing the -B.DAT and -C.DAT files because you've disabled Cell Text Loading and Drillable Region Loading in your Integration Settings. If you want to stop running the ExportA and LoadA Actions, and thereby eliminating the Journal-related BefLoad, AfdtLoad, etc., Action calls, I think disabling Journal Loading will accomplish this.
                      • 8. Re: FDM event scripts firing twice during data loads
                        783124
                        I cannot seem to find a way to switch the Journal .Dat exports off - where is this option located? I have been through the Application, Integration and Configuration settings in the FDM Web Client but cannot see where this would be set. Or does this need to be done via Workbench?
                        • 9. Re: FDM event scripts firing twice during data loads
                          LarryR
                          If this option is available, you should see it under "Integration Options". If it is not there, then the option may have been deleted from the Adapter --> Options in Workbench.
                          • 10. Re: FDM event scripts firing twice during data loads
                            783124
                            The following appear to be the only Intergration Settings options via FDM web, but yes it may be burried somewhere in Workbench - let me know if you discover where - it would be good to turn this off!
                            - Application Name
                            - Essbase DB Name
                            - Default Calculation Method
                            - Enable Load
                            - Enable Drillable Region Load
                            - Enable Consolidate
                            - Logon Method
                            - Global Logon Information
                            - Load Method
                            - Dimension Cache Switch
                            - Enable Base Entity Calc
                            - Browse For All Members
                            - Enable String Load
                            - Enable paging funcitonality in Export Action (gotta love their spelling mistake! Option must've been entered on a Friday...)
                            - Load Rule Name
                            - Calculate Switch
                            - List1
                            - List2
                            - List3
                            - Status1
                            - Status2
                            - Status3
                            • 11. Re: FDM event scripts firing twice during data loads
                              Stuart Game
                              Hi

                              Further to Larry's comment on the API guide, there is a whole section in it that talks about event firing order and shows what scripts run when you perform certain actions (section 11 in the 11.1.2.2 doc that can be found at the link below):

                              http://docs.oracle.com/cd/E17236_01/epm.1112/fdm_api.pdf

                              I found this very useful when developing batch processes and like you I created my own debug scripts to double check that what the doc said was what actually occurred.


                              For what its worth I think that the reason you are only seeing the script fire twice is because you are using the Essbase adapter, I think it is the HFM adapter that fires it up to four times.

                              Regards
                              Stuart
                              • 12. Re: FDM event scripts firing twice during data loads
                                LarryR
                                Good point, Stuart. I don't have much exposure to the Essbase adapter. The fact that the Essbase adapter is being used could also explain why the Journal Load option is not in the Integration options. So, the -A.dat file probably isn't the Journal file. For HFM, the first line of the -A, -B, and -C.dat files details the content type. Is this info detailed in the Read Me, Admin Guide, or some other doc?
                                • 13. Re: FDM event scripts firing twice during data loads
                                  783124
                                  I have searched through the FDM Admin, API and DBA guides, but none of them appears to explain the exported "-A.Dat" files (please correct me if I am wrong).

                                  The below is an example of one of these "-A.Dat" files. Could this be extra metadata, to allow drill-through from Smart View back to FDM? This file accompanied a very small 3-cell test export.

                                  "" "" "" "not" "" "active" "Load" "Region" "LW6" "Drillable" "" "is"
                                  • 14. Re: FDM event scripts firing twice during data loads
                                    Stuart Game
                                    Hi James

                                    I checked the latest adapter readme for Essbase and HFM too and it doesn't mention them. I think (but not 100% sure) that the following is true:

                                    For Essbase the main .dat file is the data and the other .dat file from what you shared must be drillable region

                                    For HFM the main .dat file is the data again and then there are potentially 3 other .dat files for drillable region, line item detail and journals


                                    Hope this helps clarify
                                    Stuart
                                    1 2 Previous Next