Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Data Pre-Validation TOOL – Envision Perfect SAP Master Data Validation

Former Member
‎09-19-2014 6:11 PM

Let me ask one question before we touch base to Data Pre-Validation tool: What is a core business principle to run a successful company?


Answer is “Assets must be managed meritoriously”.  However there has been often overlooked asset, particularly with wide business management systems such as SAP ERP. This asset is “Core Data” which includes Master data and Transactional data.


Managing large amounts of data can be significant challenge to most organizations. Some of data management tasks include:




Above are some common data management tasks. Among these data validation is most important task where “Data Pre-Validation TOOL” comes into picture.


  • Data Migration : Loading legacy system data to SAP applications
  • Data Validation: Validate and ensure clean data is getting loaded to SAP system
  • Data Maintenance: Mass load of data to SAP
  • Data Creation: Creating new master data (i.e. Customer/Vendor/Materials, etc.) or Transactional Data (Sales Document/Invoice, etc.)


Every data loading project should have a plan that includes quality and user acceptance as the top priorities. Preparation is the key to the success of any operation and data loading is no exception. So before loading data to SAP systems data must be validated correctly and we are introducing similar excel based data validation tool here.



When business users make mass changes to data as part of SAP data maintenance, they often first extract the data into a more user-friendly format, such as an Excel spreadsheet, make the changes, and then upload the data back into SAP.


We are going to see how we can implement excel based Data Validation tool.


Pre-requisites to implement the tool: Microsoft Excel VBA coding / ABAP coding knowledge


Introduction:  This blog will help to understand and implement Data Pre-Validation tool for SAP Master Data. As we are aware about importance of clean and correct data, this tool will help data validation with user friendly approach.

What we will cover in this blog?


  1.   What is Data Pre Validation TOOL?
  2.   Major Pain-point in day to day master data management
  3.   Prerequisites for Implementing the TOOL 
  4.   Concept Behind the TOOL
  5.   DEMO
  6.   Benefits of the TOOL



Let’s begin!!!


  1. What is Data Pre-Validation TOOL?


  • Data validation ensures clean, correct and useful data. However due to user interface complexities, inflexible business processes, ineffective mass maintenance of master data and poor governance can severely hinder the process, leading to dirty data and large financial losses. This validation tool will help easier master data validation.
  • Automation Tool:   This simplifies Master Data Validation process.
  • SAP Data Management: Data pre-validation tool is designed to make SAP Data Management easier using everyday tool.
  • Well Suited for Non-Technical workforce: Easy user interface
  • Increases productivity and Reduces Efforts: Simplified tool will increase productivity.
  • Less ABAP code/Zero ABAP code.



   2. Major Pain-point in day to day master data management

    Below are the major pain points in SAP Master Data Management.


These are major pain points in Master Data Management in SAP which can be overcome via Data Validation Tool.


Let us see prerequisites for implementing this tool:


  3. Prerequisites for Implementing the TOOL  : Prerequisite to implement this tool is

  • Microsoft Excel Version 2010 and above
  • Excel VBA coding and SAP ABAP coding knowledge

We have seen pre-requisites to implement the tool, let us see concept behind the tool and implementation steps:



  4. Concept behind the TOOL: Below picture depicts the connectivity between MS-EXCEL and SAP.


Example with implementation steps:

Implementation divided into 2 parts (Excel & SAP side)

  • Excel File steps
  • Create excel file for user input.
  • Create user interface in excel sheet to input data for the vendor master data and output.
  • Write VB code to make connection with SAP.
  • Read the data from excel.
  • Validate data with SAP table.
  • Display results in excel sheet itself.

  • SAP side steps
  • Create function module in ABAP and keep it remote enabled i.e. RFC.
  • Add import, export and tables parameters as per requirement from MS Excel.
  • Write logic in RFC to validate the data.
  • Return validation results.


  • MS Excel – User Interface
  • Design excel as per below screenshot:


Input -> Sheet 1

Output -> Sheet2


 

  • Performing DATA validation:

  • We can add two types of validations:
  • MACRO Based: Write Macro for validations such as ’Mandatory field value', ‘Enter value in capital letters only’, ‘Length of the field’, ’Enter Only Characters, ’Enter Only Numeric value’.


  • Such validations can be handled via Excel Macro without SAP connection.

       

         Let us see below example of MACRO: Write code on Worksheet_Change event.

         Below validation is checking whether particular column is empty which a   mandatory field then below validation is to highlight the column with Red color          and add comment.


  • SAP data validations: SAP side validations will come into picture when we want validate data from SAP tables. Example: Validate the country (here we want validate if user has entered correct country code) from T005 SAP table.

       

        Below are the high level steps on how we excel and SAP interface can be done.

  • Excel File steps
  • Create excel file for user input.
  • Create user interface in excel sheet to input data for the vendor master data and output.
  • Write VB code to make connection with SAP.


       Below are the high level steps on how we excel and SAP interface can be done.


  • Excel File steps
  • Create excel file for user input.
  • Create user interface in excel sheet to input data for the vendor master data and output.
  • Write VB code to make connection with SAP.
  • Read the data from excel.
  • Validate data with SAP table.
  • Display results in excel sheet itself.

  • SAP side steps
  • Create function module in ABAP and keep it remote enabled i.e. RFC.
  • Add import, export and tables parameters as per requirement from MS Excel.
  • Write logic in RFC to validate the data.
  • Return validation results.

   5. DEMO:


Please see below demo how the data can be validated:

Example 1: These are MACRO based validations:


  1. STRAS (Street) field is mandatory: So whenever field is kept blank, macro validation will highlight field with RED and will add comment “This is Mandatory field. Please fill in value.”


    2. NAME1 (Name) field should have capital letters and length should not be greater than 40 characters: So whenever value entered in small letters or      digits or characters more than 40 chars, macro validation will highlight field with RED and will add comment “Max 40 Chars Caps only.”

Example 2: These are SAP based validations:

  1. SAP validations will work on validations which are included in RFC (function module) code and return results to Excel.

        Below is small demo how the results are returned from SAP to excel: For these validations “RFC_READ_TABLE” FM can be used.

       

        So without any coding in ABAP these validations can be performed.



   6. Benefits of the TOOL: Below are the benefits of the tool which are self-explanatory.



This way we can use Excel to SAP interface for Data Validation. This technique will be beneficial when data is huge. Furthermore we can also design dynamic rules to perform validations.

I hope this blog gave helpful insight on SAP data validation concept.

Labels in this area
Popular Blog Posts