ARE YOU HAVING PERFORMANCE PROBLEMS WITH DATA INTENSIVE PROGRAMS LIKE THE SALES ORDER DETAIL INQUIRY? IS YOUR SALES ORDER HISTORY FILE HUGE BEYOND BELIEF? What about your Item Ledger (CARDEX)? Is your disk space on your enterprise server running at a critically high level? If you answered yes to any of these questions, you may need to archive data.

Making the decision to archive or purge data is often difficult to do. Your user counts on having information at his or her fingertips at all times, often including historical data. Whether you are a director, manager, analyst or systems administrator, you can confidently make the suggestion that you purge or archive data once you read through this article.

In this article, we will explore a couple of the major places you can, and possibly should, purge data from as well as cover the skill sets you will need to make it happen. Many people have misconceptions of how di-cult it can be to archive or purge data from such an integrated system, but what you will and is that it takes a little know-how, people with the right skill sets, an involved user base and a bit of courage.

What to Purge?

Your first question to yourself is, and should be, “What should I purge?” The answer might be simple, but can be complex. Due to laws and regulations that are either applicable to publicly trading companies or your specific industry, you may be required to “keep” data for a specific period of time. To find what you can spend time to purge, it is important that you follow a specific process.

First, evaluate your database. Use the skills of the database administrator in your organization (you may need expert, outside help), to look at record counts, table size and disk utilization. Also, do not forget to evaluate the indices on the tables. From experience, I know that the indices can take up a significant amount of space as the table records grow. Rank the database from high to low with the highest being the tables with the most records.

Second, evaluate which of the highest ranking tables are read and updated the most frequently. If a table is relatively small, meaning it ranks lower than others, it may still be important to purge if it is touched by the database engine often enough. From these first two steps, you should have a clear idea of how you will get the biggest return on your invested time and money.

Third, review the tables in the context of the rules and regulations you are subject to. Remember, you may still be able to purge or archive data as long as it remains available in some form. This could include pulling it into a Data Warehouse, saving it to tape or moving it or to another media. Evaluate the data and determine what you can do with it.

Finally, start module by module. Do not try to tackle the entire system at once. If you use Sales Order Entry as a starting point, you will get a decent “bang for your buck,” and most people are familiar with the table structure.

Standard EnterpriseOne Purge Routines

PeopleSoft has provided some tools for you to do standard purge routines for Sales Order Entry and General Ledger/Finance. My experience showed me that the process for purging history through the G/L is decent and makes sense. Unfortunately, the process for Sales Order les is not as straight forward and requires that you involve someone with significant CNC experience, plus there may be certain items you wish to archive that it does not support.

My recommendation is that you evaluate your needs first, as stated above, then look into the standard PeopleSoft purge routines. If they fit your needs, gr eat, you have just saved significant time in preparation and processing. If not, you will need to design and develop them from scratch.

Sales Order Entry, Specific Example

In many instances I recommend you create a custom purge routine for the Sales Order Entry processing. The skills and people involved include: process owner, legal/accounting, business analyst and technical developer. If you do not have the skills inhouse for one or more of these areas, do not be afraid to ask for help. There is nothing worse than archiving the wrong data without a proper backout strategy.

Once you have the right people involved, you must determine what you want to purge. Some questions you need to answer include:

What criteria are we going to use to determine what gets archived?
What date are we using to use to determine which records to purge (invoice, G/L, etc.)?
Do we want to keep all records for an order even if one row does not meet the criteria (highly recommended)?

It is up to the team involving business analyst, legal/accounting and process owner to determine that criteria. Total buy-in is required ahead of time to ensure the development team goes down the right path from the start.

Once you know that you want to purge the Sales Order Entry system and what criteria you should use, you may have identified the same tables listed below:

F4211 — Sales Order Detail (records at 999 status that did not go through Sales Update to

F4201 — Sales Order Header (records with no detail lines)

F42119 — Sales Order Detail History

F42019 — Sales Order Header History

F42199 — Sales Order Ledger

F00165 — Media Objects File

F4214 — Sales Order/Purchase Order Text Lines

F4074 — Price Adjustment History File

Also, you may have custom tag files, supplemental data, etc. depending on your configuration.

In this specific example, it may be difficult to determine where to start. The first place I always start is at the beginning. You determine the beginning by finding which file has the detail you wish to remove. In this case, we wish to remove the detail from the Sales Order Detail History and the Sales Order Detail. Once that data is gone we can then remove the headers that have no more detail (this has a side benefit of clearing up headers with no detail to begin with) in the Sales Order Header and Sales Order Header History.

With the detail and header information gone from the existing files and history, we can remove all lines in the ledger that do not exist in the detail or detail history, any media objects related to the header and detail, any sales order text lines and all price adjustment history.

It becomes a “trickle down” effect (see Figure 1) because what you can remove from the les in the system depends on what detail or header records exist. Extreme care should be taken in removing records from the system and a back out strategy should be in place. As with any process that involves changing data on the system, extensive testing should be done in DV and/or PY before even considering moving forward to PD.

In all cases, the business analyst and technical developer can work together to come up with the tables, their keys and how each will be handled.

Where Should I Put the Data?

You have several choices as to where to put the data. The first place to move the data is into a mirror file with the exact same columns as the original table. The G/L Purge process uses a file named F0911P to store data from the F9011. I recommend following this same naming convention. In our specific example the F42119 would be purged to F5542119P (if your system allows you to go over eight characters — if not, trim out one character). In the OMW, copy each of your tables you wish to copy and name it with a meaningful name such as that listed above.

If you are in dire need of disk space, you may want to consider off loading the data to tape or another media (See Figure 2). If you just move the data off to a “P” file, it is merely shuffling the data around, so you will need to move the contents of the “P” file to tape or other media. If you need the data readily accessible, you could construct a data warehouse to keep your files. A database administrator would need to be involved in this case.

Data Warehousing is an excellent way to keep the information at the fingertips of your user. Simple-touse reporting tools can be installed over the top of the data and many organizations already own these tools and have skill sets in-house. If you do not, do not be afraid as there are many qualified professionals to help you through this.

Technical Steps

Once you have purged or archived any records from your tables, you may need to reclaim the space from the deleted records. This depends on the database you have chosen for your enterprise system. Also, it is a good idea to regenerate all of your indices.

You will find that the system that uses these files will run much faster once you have removed a significant amount of data, reclaimed the space and performed a re-index on the tables.

Keep in mind that the purge routines that read from files, write to purge tables, then delete the original records have a lot of work to do over thousands or even millions of records. It will take time to construct and thoroughly test your purge routine. Do not hesitate to put the necessary time in during up-front design and thorough testing. Sometimes, the purge routines could run as much as three hours to one full day. It all depends on your specific situation.

Conclusion

Purging and archiving data is one of several tools you can use to gain extra performance from your system. Once you have purged data, you may see significant speed improvements in your inquiries and update programs. You may also see greater overall system performance as disk utilization usually drops once the data is moved off of the main disk.

This is often a tool that can be used in conjunction with other methods to speed up your system performance to give your user a better experience while using the system. After all, it is the user we wish to satisfy so they can turn their efforts to other, more important tasks that make more money for your organization.

Purge Or Archive Your World Or EnterpriseOne Data