11 Replies Latest reply on Aug 15, 2012 8:05 PM by Stuart Game

    No validation information logged by FDM

      We have scheduled the FDM batch process to run overnight using upsShell.exe (executed from a windows cmd batch script), which essentially does the same thing as the Hyperion FDM Task Manager, but also allows one to script the recording of additional useful historic information such as execution times etc (I have asked Oracle to look at adding this functionality to the Hyperion FDM Task Manager going forward).

      However, we have had several data files fail on the Validate step of the workflow, which resulted in the files not being loaded at all. The only way I discovered this was by manually counting the number of .Dat export files in the Outbox and comparing with the number of data files dropped into the OpenBatch folder (some 70+ files). I discovered that several files had not been loaded at all, but that there was no evidence or clues in the <username>.err file, no error files in the Outbox or Outbox\Logs folders, or any clues in the import logs.

      So am I right in suspecting that if a data file fails to validate when using the scheduled batch process functionality, absolutely no information is logged anywhere?! :'-( I have since had to add custom code to the AftFileImport, AftValidate and AftLoad event scripts just to record information to a log file to inform us whether or not the files actually made it through the batch load!

      Are we missing something here?

      Event Script:
      Sub AftValidate(strLoc, strCat, strPer, lngProcState)
      'Oracle Hyperion FDM EVENT Script:
      'Created By:      admin
      'Date Created:      2012-08-07 17:25:30
      'Purpose:     Log information that FDM fails to provide...

      ' Declare local variables
      Dim fso, tf
      Dim strLPath
      Dim strShare

      ' Get system environment variable values.
      Set objShell = CreateObject("WScript.Shell")
      strShare = objShell.ExpandEnvironmentStrings("%ENV_SHARES%")

      ' Set log file path.
      strLPath = strShare & "\<foldername>\Logs"

      ' Declare file system Object.
      Set fso = CreateObject("Scripting.FileSystemObject")
      Set tf = fso.OpenTextFile(strLPath & "\FDMAPP_BatchLoad.log", 8, RES.FDMTrue)

      ' Write to log file. A status of 11 means SUCCESS, 12 means FAILURE.
      tf.WriteLine(Now & " Validate      Status: " & lngProcState)

      ' Destroy file system Object
      Set fso = Nothing

      End Sub
        • 1. Re: No validation information logged by FDM
          Stuart Game
          Hi James

          All batch information is logged in tables in the relational database, worth taking a look at the following to see whether it contains the information you are looking for:
          (the other batch tables I think contain only summary info / lookup values used in the tables above)
          May also be in tLogActivity (?)

          We had a similar question (although with fewer files, only 6 per day) and we wrote a script in the BatchAction event script using specific strEventName events to write the status of each file at each of the process steps, Import, Validate and Export/Load. We also performed certain pre-process checks so that only files passing specific criteria were attempted to load (e.g. # records matched to record count in the file, control total balanced i.e. TB balanced per entity). When we came to produce our custom log files we had issues in merging the pre-import and process logs as the filename did not match, e.g. SourceFile20120320.txt had changed to have the required FDM batch name format.

          We eventually achieved that with custom SQL but took a little while. That means it should be possible to write your own log in SQL tables, I know that there are FDM API commands to add entries to the tLogActivity table for example, we chose to use our own custom tables so as not to interfere with standard functionality.

          Hope this helps
          1 person found this helpful
          • 2. Re: No validation information logged by FDM
            Hi Stuart,

            Thank you very much for your response. This is very useful to know, however unfortunately on the current project we do not have sight of or access to the SQL database, and are having to rely on log files dropped into the FDM application folder structure.

            It does not make sense that the import process logs detailed information to log files in the Outbox\Logs folder, but that no information is logged by the validation process of the workflow. I raised an SR with Oracle on this earlier today, and hopefully they will look into implementing an enhancement here. If they do, hopefully they will consider logging detailed validation information to the log files, to list the specific mappings etc that failed.

            It appears that the only way to identify validation problems during an FDM data load is to use the FDM Web Client interface. If one schedules batch loads using the Hyperion FDM Task Manager or upsShell.exe, the load completes with no errors, despite the fact that several files may not have loaded at all as they failed on the validation stage! I am now having to re-load each of the failed files (now identified by my script above) manually using FDM Web Client, just to get sight of the problems to fix them! :-(

            Do you possibly know if any validation information (specifically) is logged to the tLogActivity table you mentioned?
            • 3. Re: No validation information logged by FDM
              Stuart Game
              Hi James

              I don't have access to an image where I am today but I will try to check. I'm pretty sure that there are entries for import and validation, I seem to remember seeing an entry that states how many members is attempted to map and how many passed / failed.

              Try having a look at one of the following reports, I cannot tell from my notes which may point to the tLogActivity table or what exactly the reports contain but a good start point:

              Utility Reports > System Log for Location
              System Reports > System Activity Log
              System Reports > System Activity Log for Key

              remember that the the mapping occurs on Import so although the validation highlights members with no entry in the mapping tables and also highlights intersection validation (for HFM, if turned on), it is at the import stage that the target members are assigned (the exception being those fixed at validation when manually processing through the web client)

              Hope this helps
              1 person found this helpful
              • 4. Re: No validation information logged by FDM
                It is a few months since i did any FDM work and also just come back form holiday, but have you checked the FDQM Batch reports. From memory, i am sure they do provide the sort of information you are after although not necessarily easily to read. If you do not have access to the SQL tables, then you could copy the standard reports and redevelop to give appropriate information.
                • 5. Re: No validation information logged by FDM
                  Stuart Game

                  Yes sorry I forgot the Batch Reports > Batch Procesing Status report should give you a view of the table that I mentioned earlier, definitely worth a try as well

                  • 6. Re: No validation information logged by FDM
                    Many thanks for your responses. I will look into the batch reports you mentioned and post back again to update.

                    Edited by: JamesD on Aug 10, 2012 12:38 PM
                    • 7. Re: No validation information logged by FDM

                      Depending on the type of file you are importing (Multiload or Single Period) mapping is generally applied during import.

                      Thakn you,
                      • 8. Re: No validation information logged by FDM
                        Stuart Game
                        Hi James

                        For single period files the mapping happens during import, you can see this in the underlying tables if you have access and there is a report that analyses the import process giving you the time it takes to perform each mapping (I assume to help if performance is an issue).

                        The screen displayed in the FDM web client after import is just a view of the data table and it shows the Account, Entity, UD1 etc field values (source metadata), what you don't see is that the AccountX, EntityX, UD1X etc fields are already populated (target metadata) as these are only displayed when you get to view the validate screen.

                        Hope this helps
                        1 person found this helpful
                        • 9. Re: No validation information logged by FDM
                          Many thanks for your responses. Its is good to get clarification on when in the Workflow these mappings are taking place, thanks for this!

                          I will look into the batch reports you mentioned and post back again to update.
                          • 10. Re: No validation information logged by FDM
                            To update, I have now got the batch reports working, however it is a little frustrating that none of this is documented in the FDM admin guide. I have asked Oracle to update - apparently it was included in the documentation for 9.3!

                            Furthermore, unfortunately this does not really solve the problem for us. It appears that the generation of FDM reports is a manual process by logging on to FDM Web Client, and cannot be automated or scheduled. Consequently, establishing whether or not any loaded files failed on validation using reports is as manual a process as counting the number of .Dat files in the Outbox and comparing the number of data files in the Inbox. Futhermore, our client wishes to use third party software to analyse log and error files, and then act accordingly (i.e. email relevant parties). However this cannot be achieved with the batch reports.

                            Ideally, FDM should be logging any validation errors (or mapping errors in the import stage) to an error file in the Outbox, as it does with other errors. I have asked Oracle to look into implementing an enhancement here.

                            The method above (event script) is still the best workaround for us at present, but thank you for all your responses. Please let me know if you have any further thoughts on this.
                            • 11. Re: No validation information logged by FDM
                              Stuart Game
                              Hi James

                              I'm pretty sure that there is a piece of code that you can grab from the Supplemental API in the workbench that will execute a report for you. There is also one to send email so you may be able to combine the two (I've not used either personally though.

                              In Workbench go to the scripts tab and create a new custom script. Then in the object browser (bottom left of screen) expand Accelerators > Task Manager and double click on Report Publishing - Active Reports

                              This should automatically insert the code needed to generate a report, it uses certain parameters for report ID and publish type so you would need to investigate what the correct values for those would need to be to run the report you wanted (you can find the report ID by browsing in Workbench on the reports tab to the report you want and double clicking to open it)

                              Similarly in the object browser under Accelerators > General Utilities > Send Mail is the code to send an email, not sure it includes the ability to attach the report but you could probably get it to send a link to it or at least advise that the report has been run.

                              Hope this helps