Please Share your Product Ideas with us!

All ideas are welcome. Just because the Idea doesn't make it into the product immediately does not make it a bad idea.

Autogen - Preload/Postload Reports using zFlag fields

As fast as we can design, map, and autogen target conversions in dspMigrate and have the ability to generate config check reports, we do not have a process to autogen preload and postload reports. At 2 of our clients I have used the the attached sql code to automate the creation of the following reports. The prerequisite is that each target table contains the following fields: zActive, zInSAP, zError, zLoaded. This will also help enforce a standard methodology across our validation efforts.


Detail Explanation of Attached File

Each target will need individual rules to mark any records inactive, mark the records that already exist in SAP by the primary key of the table, and a zError update where you could flag any records that will fail to load due to missing config, invalid values, etc. The zLoaded flag is a postload rule that will drive the delta load when its not flagged.

Create Rules:
zActive - By Default it is set to 1, any rules to inactive records set this value to 0. When zActive = 0 this drives the "Exclusion" report.
zInSAP - Use the SAP primary keys to determine the records that already exist in SAP. This will drive the "Already Loaded" report
zError - When zInSAP = 0 and zActive = 1 flag any records that exist on any of the target error reports that would prevent the data from loading. This drives the "Error" report (Also called Exception in my slide, but i like "Error Report" name better.

When zActive = 1, zInSAP = 0, and zError = 0, then all records go to "Ready to Load" report


The reports should look like the following layout:
tv****_Preload_Exclusion_Sel - A list of all the Key and SAP fields on the target table where zActive = 0

tv****_Preload_Error_Sel - A list of the Key fields and SAP fields on the target table where zActive = 1 and zError = 1

tv****_Preload_Error_Detail_Sel - A list of the Key fields and a description column using the target reports description for where each record appears on the report. The same key record can appear multiple times for multiple different errors where zActive = 1 and zError = 1

tv****_Preload_AlreadyLoaded_Sel - Join the target table to sap table view in the database. List the Key information and then each SAP field Field1_Expected, Field1, Field2_Expected, Field2
-Field1 Expected is the target table value and the plain name is what is already in SAP. this allows the user to see mismatches between what conversion has and what the SAP environment has for each relevant field for conversion

tv****Preload_ReadyToLoad_Sel - Where zActive = 1, zInSAP = 0 and zError = 0 - Show all the target table Key and SAP fields.



After the load, zLoaded will be set to 1 where zActive = 1, zInSAP = 0

tv*****Postload_Success_Sel - Where zLoaded = 1 Show all fields similar to the "already loaded" where you have the Field1_Expected, Field1..etc

tv****Postload_NotLoaded_Sel - Where zActive = 1, zInSAP = 0, zError = 0, zLoaded = 0. This is everything in the preload ready to load that did not load. This gives us indication when we send out the records to load and then we get the results back what we thought should load but didn't. These we need to research for the failed cause and we should have error reports created to catch the reasons why these didn't load. We would always "expect" this report to have 0 records because all the records should have been caught in the tv****Preload_Error_Sel report.

  • Brad Helman
  • Jun 11 2018
  • Future consideration
  • Attach files
  • Admin
    Jon Green commented
    24 Aug, 2018 10:22am

    Brad, could I ask you to discuss this with Kurt V as he owns the BOA360 Migration methodology.  If it is agreed that this will become the standard best practice for the pre load / post load report creation I think potentially it may be better to include this as part of the PSA offerings that are being put together or part of the content kitbag that is being pulled together in EUAF rather than placing it into core product currently.  If it is adopted as a standard by services then we can look to bring this along with the standard fields into the autogen process included in core DSP.