Automate legacy SAP data obsolescence determination using Access

Determining what master data objects are no longer used is a great start to an SAP implementation. Obsolescence determination is a very important part of data cleansing. To reduce the amount of cleansing required, this should be performed before any other part of cleansing.

An obsolete material isn't just a single material master record, but many records containing the data from many views. The potential to have one fewer Bill of Materials and one fewer Routing is another great incentive to identify obsolete materials before attempting to load. That equates to one fewer BOM and one fewer Routing extracted, cleansed, loaded in each phase of testing, validated in each phase of testing and then ultimately clogging the production system with unnecessary data.

How long without movement is obsolete?

A great cutoff for obsolescence is three years with no movement. For customers that means no sales, shipping or billing. For vendors that means no purchases, receipts or payments. For materials that means no production, purchases, sales or inventory.

All transactional data in SAP is date-stamped. Determining when a master data object last had movement is as easy as comparing the most recent transaction to today's date.

Extracting the appropriate tables

When the legacy system is a flavor of SAP it is possible to perform RFC calls to the system in order to extract the data therein. MS Access is a great tool for extracting SAP tables using Remote Function Calls and VBA. Once the tables from the legacy SAP instance have been imported to Access, a macro with a series of simple queries can make easy work of determining which master data objects need to be as obsoleted (flagged for deletion).

In addition to the tables containing the master data, the tables containing transactional data must also be extracted. The master data to be extracted should include the appropriated organizational levels like plant, purchasing organization and sales organization. Transactional data to be used includes all sales orders, purchase orders, production orders, shipping documents, receiving documents and current inventory levels.

Enriched reports with current status and obsolescence determination

A simple report identifying what objects are obsolete and the current status of each object can then be used to update the objects in the legacy system that are not already flagged for deletion. Flagging a legacy object for deletion is a great way to filter it from extracts. By enriching the report with the current deletion indicator for the master data object will reduce the amount of time spent manually (or automatically) flagging master data objects for deletion.

Excel spreadsheets are a great way to hand off data to decision makers. It is possible to export from Access directly to an Excel spreadsheet. This step, when automated can save countless hours and reduce the risk of data corruption caused by manual processes.

Download the Obsolescence Determination tool

The Obsolescence Determination tool is a free download from SAPLSMW.com and is compatible with MS Access 2002 and later. The only requirements are a relatively-recent copy of MS Access installed on a client computer, the SAP GUI client and a legacy SAP system.

No expertise in MS Access is required, but some knowledge is helpful. Once the Access file is opened, right-click on the mcrObsolete macro and choose Design from the menu that appears.

In the macro be sure to populate the TempVars variables below with the system information and RFC-enabled credentials for the legacy SAP system. The default directory for the creation of the Excel report is "c:\temp" and the cutoff in years is 3, but these can be altered, too.

SAP_ObsolecenseDetermination.mdb