web page hit counter SCEIS -- Data Cleansing Guidelines

top =

Home

Rollout schedules for agencies
Implementation support
Agency Advocates  
Data Preparation Guidelines u
Agency Technical Infrastructure Readiness Guide ( pdf 44kb)
Document Retention Guidelines

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

top =

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

top =

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

top =

 

 

 

 

 

 

 

 

 

 

 

 

SCEIS Data Preparation Guidelines

The SCEIS Change Management Team will work closely with each agency before its Go-Live, to guide and assist the Data Preparation effort.

Intro  |  Master Data Preparation Objects  |  Transactional Data Preparation Objects  | 

Data that can be prepared without knowing SAP requirements

Data that should be prepared based on SAP requirements

Objective

This document outlines the course of actions to prepare data in the legacy systems or in the corresponding staging area before it is loaded into SAP.

It defines general guidelines, which may be customized for each conversion object when detailed preparation instructions are rolled out. This is a living document that will be updated as Blueprint and Data Conversion decisions are made.

Data Preparation

Data Preparation is the process of reviewing and maintaining legacy application data so that it can be converted into the SCEIS SAP solution without intervention at final conversion time. Data preparation is one of the most important processes for data conversion.

Preparation of the data must occur prior to loading it into the Production SAP environment. Loading poor quality data into SAP could result in incorrect business decisions and may be more difficult to correct later. As part of the SCEIS Deployment Strategy, legacy data must be prepared before loading it into the SAP solution.

State Agencies will prepare their own data per scope indicated in the Data Preparation Scope charts below. Resources will be needed from the Agencies who are currently using the legacy data. The Deployment team will coordinate this process.

top

Guiding Principles and Assumptions

  • Legacy data must undergo data preparation to improve quality, minimize data
    integrity issues, and reduce data volume and extract-program run time.

  • State Agencies will be responsible for preparation of master and transactional data to be converted to SAP.

  • If necessary, Agencies will be required to supply additional resources to complete high volume, low complexity manual preparation activities.

  • Agencies will ensure that extracted data is validated before and after data are loaded to SAP.

  • An Agency data owner will be assigned for each conversion and will be responsible for the proper preparation of the source data to be converted.

  • It is the responsibility of the Agency data owners to communicate with one another to identify dependencies between preparation efforts.

  • SCEIS Functional Teams will provide the SAP data requirements and the corresponding support to help Agencies to understand SAP data fields and map legacy systems data to SAP.

  • Work plan and metrics will be used by the Deployment SCEIS team to track progress over the course of the implementation.

top

Data in scope to be prepared by State Agencies

ONLY the following data objects need to be prepared by Agency resources. The rest of Master and Transactional data objects will either be loaded in SAP by the SCEIS functional teams (such as Chart of Accounts or Material Master), derived from other data objects (such as Commitment Items and Fund Centers) or entered manually in SAP as part of final Cutover (such as Open Purchase Orders, current year Budget).

Master Data Preparation Objects (in Scope for State Agencies)

Business Process / SAP Module Conversion Object Source System / Input File Data to be Prepared Responsible
Assets Management Fixed Assets Master & Balances. Also include Capital and Operational Leases GAFRS, BARS< Excel template All active assets Agency Finance Department
Accounts Receivable Customer Master Excel template Active agency Customer list Agency Finance Department
Cash Management Bank / Bank Accounts Excel template Bank files / Current Bank Accounts STO only
Cost Control / Controlling Cost Centers Excel template New SAP Cost centers based on agency org structure Agency Finance Department
Cost Control / Controlling Internal orders Excel template New SAP Internal Orders based non-capital and capital projects Agency Finance Department
Grants Management Sponsor Excel template, CFDA website Agency Active Sponsor lists combined with CFDA Information Agency Finance Department
Grants Management Sponsored Programs Excel template New SAP Sponsored Programs Agency Finance Department
Grants Management open Grant Excel template Active agency Grants list Agency Finance Department
Purchasing & SRM/MM/FI Vendor Master STARS / Extract program Active Vendors in the last 24 months Agency Finance Department

 top
Transactional Data Preparation Objects (in Scope for Agencies)

Business Process / SAP Module Conversion Object Source System / Input File Data to be Prepared Responsible
Budget Budget STARS for legal level / Excel template for Agency level Budget Balance at the date of conversion. Calculate Budget by loading original and posting adjusted appropriations, transfers and other changes at a summarized level Agency Finance Department
General Ledger GL Balances STARS/GAFRS/Excel template

Conversion Package

 GL year to Go Live date balances. Revenues and Expenditures extracted from STARS. Balance Sheet accounts extracted from STARS when applicable and Conversion Package prepared by Agency. Agency Finance Department
Accounts Payable Vendor OPen items Manual / Excel Template Outstanding vendor invoices, notes payable and IDTs Agency Finance Department
Accounts Receivable Cost AR Open items Manual / Excel template Outstanding customer invoices, loan receivables and IDTs Agency Finance Department
Procurement Contracts APS / Extract Program, or excel template Contract Balances by Go-Live date Agency Procurement Department
Procurement Purchasing Orders Manual / Excel template Open POs Agency Procurement Department

 

top

General Preparation Guidelines

Data that can be prepared in the legacy system without knowing SAP requirements

Issue Explanation Resolution
Duplicates The same data entity (fixed asset, vendor, customer, etc.) is named two or more times in the same system. Data preparation required. Flag one or more of the data elements so that it is not included in the "to be" extract file.
Obsolete or inactive records Data that is not up to date, or no longer active. Obsolete data should remain in the legacy system, as it is not needed in SAP (e.g., vendors no longer purchased from).

Data preparation required. The rules to declare a record obsolete:

  • Vendors: No activity in the last two years.

  • Fixed Assets; Retired or scrapped assets after X years.

  • Customers: TBD

  • Bank Accounts: TBD

  • Projects: TBD

  • Grants: TBD

Data Preparation involves using a field in the legacy system to identify the record and using it to sort out these files when extracting data.

Incorrect data Inconsistencies that are related to typing or data entry errors. Typical problems include spelling errors (e.g., Bank of America, vs. Banc of America0 and reference inconsistencies (e.g., 2nd Street vs. Second Street, or Inc vs. Corporation) Data preparation required. Review file and correct manually. If the error is present in multiple records, there may be a way to correct this automatically. Consult with Agency Technical Support.
Incomplete records Missing data in current legacy system Data preparation required. Correct incomplete records, since some of this data may be required by SAP
Preparation Process

  1. Run corresponding Legacy System report and download it to an Excel spreadsheet.

  2. Depending on the size and/or complexity of the data file, determine, either programmatically or manually, any duplicates, obsoletes, incorrect or incomplete records.

  3. Correct records per suggested solutions in the chart above. If necessary, consult with your Agency Technical Support and/or corresponding SCEIS Team member.

  4. Report status to Deployment team per project plan and metrics sheet.

top

Data that should be prepared based on SAP requirements

  • Detailed Data Mapping and an understanding of SAP data fields are required.

  • Agencies will be given the corresponding support from the SCEIS team to understand SAP requirements and to complete mapping.

  • The following guidelines may be revised and customized for each conversion object.

Issue Explanation Resolution
Missing required values or intermittent data The current system does not require a certain field, so it has been left blank. Or a given field should be filled per up-to-date procedure, but it is skipped when information is not known at the time of data entry. This field is required in SAP per defined business process. Data Preparation required. It might be possible to automatically populate the field (a) by plugging in a constant value, or (b) by referencing some other file to "look up" the information. If not, manual data preparation will be needed. Consult with Agency Technical Support for assistance.
Overloaded data fields Two organizations use the same field to store two different elements of information. Data Preparation required in one database or the other, or in both based on what the field will be used for in SAP.
Compound data fields The current system does not provide a separate field for some desired piece of information, so that piece of information is being stored along with another one in a single field.

E.g., current system includes a field named "Contact" which would typically contain the "name" of the appropriate contact individual. Because the system does not use a separate field for the contact's telephone number, both the "name" and "phone number" are being stored in the "Contact" field.

 Manual data preparation may be required. It may not be possible to reliably separate the two values.
Inconsistent similar data Similar data entered into separate or independent systems.

E.g., consider two departments defining projects in their systems. Same type of data (project-related) is entered into different systems, but since it is not validated against each other or a central system, the data formats are different.

Data Preparation required in one database or the other, or in both, based on what the field will be used for in SAP.
Free form text fields Free form text fields may have data that varies in meaning, based on the user who enter the data into the system. Data Preparation may be required based on SAP requirements.
Different data values to represent the same information. Inconsistencies due to different data structures used in different source systems. Typical problems include using different data values to represent the same thing.

E.g., System A uses "1" for "yes," System B uses "Y" for "yes," and System C uses a flag for "yes."

 Data Preparation required in one database or the other, or in all, based on what the field will be used for in SAP.

 
Intelligent data fields Various positions in the data field imply additional information. SAP typically provides a separate field for the implied additional information.

E.g., consider a system which includes a 7-character field named "invoice number." A value of "G" in the first position indicates a sale to the US Government; a value of "D" indicates a sale to a non-government US customer. The remaining characters in the field contain a unique serial number. Thus, it is possible to determine some additional information from the invoice number.

If there is a regular pattern to the coding, the separation can probably be done programmatically. If not, manual conversion may be required. SCEIS functional team will determine the solution.
Encoded data fields The data field in the current system contains a code to represent a full value. SAP requires the full value or SAP uses a different code to represent the same full value.

E.g., Consider a system which includes a 1-character field named "Name Prefix," where a code of "1" indicates "Mr.," a code of "2" indicates "Miss," a code of "3" indicates "Mrs." SAP wants the full value (Mr., Miss or Mrs.), not the code.

The full value can be programmatically generated from a look-up table. SCEIS functional team will propose solution.
Formatting A data field in the current system contains a value not allowed by the corresponding SA field.

E.g., Consider a field where the current system allows alpha-numeric values, but the SAP field is only numeric.

 Manual data preparation will be required.
Field lengths The length of the data field in the current system is longer than the corresponding field in SAP.

E.g., Consider a current system with description field of length 30. Suppose SAP provides a description field of length 24.

Should the field be unilaterally truncated? Or should each description be evaluated by a human and abbreviated to retain maximum readability? Per proposed solution, manual data preparation may be required.
Data requiring translation tables A valid field entry in legacy system is not valid in SAP. Establish the need for a translation table in the data preparation procedures and describe its fields and valid entries.

 
Data Preparation Process

  • Attend meeting to gain understanding of SAP field requirements.

  • Team up with SCEIS functional team member to develop legacy system vs. SAP fields mapping.

  • Excel spreadsheet tool will be used to create to be file.

  • Run corresponding Legacy System report and download data to an excel spreadsheet per previously defined data file.

  • Depending on the size and/or complexity of the data file, determine, either programmatically or manually, data to be prepared as per guidelines indicated before in this document.

  • Correct records per suggested solutions in the chart above. If necessary, consult with your Agency Technical Support and/or corresponding SCEIS Team member.

  • Report status to Deployment team per project plan and metrics sheet 

top

For more information about Data Preparation procedures and requirements, contact Richard Wicker, Change Management Team Lead.