JD Edwards E1 Upgrade SIG presentation

We were delighted to present at the JD Edwards E1 Upgrade SIG meeting.

Here’s a run-down of what was covered in the presentation.

If you’re a Quest member and you belong to the JDE E1 Upgrade SIG then the presentation and product demo are available on demand.

The presentation kicked off with a focus on customer example, Marek.

Reasons Marek needed to archive

• System performance was getting slower, especially around payroll tables
• Taking too long to bring back some queries
• Reports were taking longer to run
• Upcoming 64-bit upgrade. A requirement to remove some data to make the upgrade process more seamless.

Background information

20 years had passed since Marek’s last custom in-house archiving.  This had unfortunately caused some issues around data integrity.

What were Marek's options for archiving?

1. Use the existing tools available within EnterpriseOne
2. The DIY approach
3. Select an off-the-shelf data archiving solution.

6 considerations to keep in mind if using the existing tools in EnterpriseOne

1.       No audit trail.  You don’t know when data was archived and you won’t know who archived the data.

2.      It’s not possible to reverse data that’s been archived.  If an error is made, and you need to get the archived data back, you’d need to write another UBE to send the data back, or get your DBA to write some SQL.  There's no fallback position.

3.     No inquiries over the archived data. Once it's moved to another table, if you want to inquire over the data, you have to create your own applications to do this.

4.     In some cases data is deleted as opposed to archived. If a mistake was to happen, there's no way to come back.

5.     Multi-step process. For example, there are 6 different UBEs for archiving Sales Orders!

6.     In the majority of the modules that are covered, there are a number of related tables that are not archived, creating orphan data.

The Do-It-Yourself approach

Let’s look at how you could tackle this.

§  Are you going to write complex SQL at the database level to do your archiving for you?

§  Are you able to write your own audited archiving UBEs to do the job?

§  The DIY approach requires a strong knowledge of your internal processes.

§  It’s likely you’ve got custom tables that you want to archive at the same time.

§  Consider whether there’s a particular kind of development that was done where you change a standard object that then changes relationships between the tables.

§  You’ll need an experienced team of Business Analysts, DBAs, Project Managers, Developers and Testers.

Further considerations

How will you handle internal validations?

For example, SQL routines would have to be written to check the order activity rules.

How would you handle cross-modular validations?

For example, if there’s a Work Order that's related to a Sales Order and that Sales Order is not at a completed status, do you still want to archive the Work Order?

How will you handle relationships with other tables?

When building a framework for this, you need to give thought to commitment control. For example, audit trails are needed to have visibility of who's been archiving the data and when it was archived.

What about access to the archived data?

Despite being a crucial point, this is often overlooked.

Archiving projects in some organizations are led by IT.  From an IT perspective, it may be acceptable to assume if data is more than 5 years old, the business won’t need to look at it.  However, some industries need access to data for legal reasons for specific amounts of time.  Let’s take a Homebuilder example. They may need to go back 10 or 15 years to view the archived data due to industry regulations and guidelines.

What if someone in the business wants the archive data put back into production?

Is everything re-runnable and reversible?

These are examples of possible requirements that need to be considered with the DIY approach to data archiving JD Edwards.

Off-the-shelf archiving products

These are tools specifically developed to archive your JD Edwards EnterpriseOne data.
They fall into two categories.

Generic archiving tool

• Runs at the database layer
• Requires frequent updates when there are changes to the way EnterpriseOne handles data.
  For example there was recently a change to order activity rules for Work Orders.  With a generic archiving tool you’d need the archiving tool to be changed at the SQL level in order to handle the changes to the logic in EnterpriseOne.

Specific EnterpriseOne tools that run at the application level

Consider selecting an archiving solution that includes:

• Audited approach to archiving
• Safe data archiving using commitment control
  If you archive a Sales Order, and there's a problem with committing the data in one of the tables, you don't want half a Sales Order in archive and half in production. You need to use commitment control so that the transaction rolls back
• Data boundaries protected
• Data integrity maintained within the system
• Functionality to allow data that's archived to be reversed (a safety net in case of errors).
• Access to archived data
  Having a mechanism for providing access to archived data is crucial for the business and data owners.

How Purge-it addresses these challenges

Purge-it is a third party solution developed specifically for JD Edwards EnterpriseOne (also available for World).

What’s different about Purge-it by Klik IT?

• 100% built in the JD Edwards toolset
• Oracle validated integration solution
• 8 Standard Modules; Manufacturing, Purchasing, Sales, Inventory, AP, AR, Payroll and General Ledger
• Custom Module. The Customer Module is incredibly powerful.
  Allows you to archive, copy or purge data from any table you can see in JD Edwards.
  Examples include; EDI tables, address book, media objects, interoperability tables, plus any of your custom F55 to F59 tables.

Customer examples of using the Custom Module

Customers have used the Custom Module to develop an archiving process for homebuilder tables. There are about 55 - 60 homebuilder tables.

Other customers have used the Custom Module to archive item location table, because it runs quicker than the standard EnterpriseOne UBE.

Why Marek chose Purge-it

• Easy to learn
• Straightforward to use

These reasons relate directly to Purge-it being written and developed in the JD Edwards toolset. It's a familiar interface to everyone.

Marek likes the fact that data integrity issues are highlighted by Purge-it.  This enabled them to identify data integrity issues from custom in-house archiving processes many years earlier.

• Archived data is easily accessible.

Easy access to the archived JD Edwards data means users don’t have to log out of their existing environment.

For example, if you log into JPD920 and you want to look at current sales orders, you go to P42101. If you want to look at archived sales orders, still logged into JPD920, you go to the purge it sales menu and run application PYK51901.

• You don't require any SQL expertise and you won't need anyone to do anything at the database level.

Archiving can be automated

You can add the archive routines to the JD Edwards scheduler and set them up to run according to your organization’s requirements.  This can be once a year, once a month or every day. Some customers run Purge-it 24/7, round the clock.

In a situation where an organization is generating large quantities of data, daily automated archiving is highly effective. For example, customers in the manufacturing industry that are adding 10 million plus records into single tables every day choose to archive small amounts of data every day.  This allows a pro-active, automated approach to data management that is highly effective and efficient (using little to no resource).

A brief overview of the Marek project

• Marek’s data retention policy was 5 years of data in production and 5 years of data in the archive.
  Anything older than 10 fiscal years plus was a candidate to be purged.
• The archiving was completed in two weeks.
• 46% of the data covered by the standard Purge-it modules was archived.

Access the full Marek Case Study here