Data Retention on Upgrades - Strategy?

[cassh1]

Hi Listers,
We are thinking of finally upgrading from Xe to 9.0. Those of you who are either new to JDE or have upgraded recently, I would like to hear from you about what you did with your data.

We would like to propose this as a plan:
Keep current year opening balances and current year details only (current year being go live year).
Prior years to be available read-only in Xe for 1 year after upgrade.
After that, database query access only for Xe history.
And for how long do we need that for?
There is a lot of discussion about the mythical 7 year rule for financial data - does anyone know if this is real?

Or that HR data has to be kept forever. True?

My goal (techie view) is to have as little data as possible be upgraded. I see it as a chance to do some cleanup that may not be possible otherwise.

I am hoping that some of you will be willing to let me know what you did at a high level and whether it worked for you or would you do things differently if you were to do it again.

Thanks in advance for your help.

Sue Shaw
Xe, platform doesn't matter for this question.
(long time list lurker, no longer with the large oil company with the recognizable logo but moved to another fair sized oil company with similar issues)

 

[cdawes]

Sue,

the solution depends on how much data and what the budget is.

I have customers with only 100 GB production instances that want to archive before upgrading and it's always turns out to be cheaper to throw more hardware at the issue and do some database tuning than to invest in a commercial archiving solution for JDE.

Other customers where we have has a HUGE amount of Sales info.........well JDE has many summarize and purge utilities that operate in 2 different manners:

1) Wipe the data out and put in a summary entry
2) Copy the data to an "archive environment" and remove it from the current production environment.

Both of these are low cost and internal to JDE.

The next option is commercial products:

1. IBM Optim (fka Princeton Softech)
2. Solix (http://www.solix.com/jd_edwards_data_archiving_solution.htm)
3. Arctools

(you can Google the vendor web site).

Now unless you have a massive amount of data adding a few extra disk arms, a few hours of building database indices and some extra CPU and RAM will prove far cheaper than any of the above commercial solutions.


Also the biggest reason I find why people want to archive is to prevent people from sending the database into a loop when they open up something like the P0911 and simply hit "find" without specifying any data.

Thanks to tools 8.98.1-3 we now have Application Query Security or "dumb user hitting find without entering data selection" prevention. This allows you to force a user either through a warnign or hard error to enter in one or more fields or enter in specific field before a find can be executed.

.............and voila we now have a way to force them to enter a field that hits an index.

No more searching on the description field and no ensuing table scan.

I've used both JDE methods extensively and also have the odd customer using a commercial solution.



Although everyone talks about purging and archiving only 2 of my 15+ 8.12/9.0 straight upgrades have done any sort of data archiving or purging.

However, almost all of my ending coexistence or World migrations have purged or archived data.

Hope this helps,


Colin

 

[Softserv]

Sue, I don't know where you are doing your financial reporting, but year over year comparisions are hard with only one year of data. Budgets are often future years. I think the 7 year rule is related to taxes and marriages. Both of which can be dealt with off line.

For new implementations when converting from a third party system the rule of thumb is that you need at least the following:

Two years of G/L balances, supporting G/L details, Open A/R, no AP, sales history to support forecasting and sales reporting needs often 2 to 5 years depending on the client.

What you lose by going so thin is you ability to report prior years quickly (are you with a public company because IFRS kicks in your next fiscal year), A/R you loose your credit/collection stats and history, A/P it is your payment history and stats.

 

[gregglarkin]

[ QUOTE ]

We would like to propose this as a plan:
Keep current year opening balances and current year details only (current year being go live year).
Prior years to be available read-only in Xe for 1 year after upgrade.
After that, database query access only for Xe history.
And for how long do we need that for?
There is a lot of discussion about the mythical 7 year rule for financial data - does anyone know if this is real?



[/ QUOTE ]

Hi Sue "formally from Shell and darn glad that you don't work for BP"

Good to hear from you again. Purging and Archiving, what a tarpit. JDE data does not purge nicely. We tried a project with Optim and even after doing extensive testing, that turned out to not be extensive enough, purging data from the GL and from the AP tables threw balances way out of whack. We purged very old data one weekend. I spent the next weekend putting it all back. As Colin said, there are some sales tables that lend themselves nicely to purging once the data has been summerized, but that can be done with the tools inside JDE.

We are doing an XE to 9.0 upgrade. We will only be bringing over summary data and the new system will be a "do-over". Which has an added benefit of purging the unpurgable, the mystery thing called media objects.

The XE system will still be around for history since only one country is going live in this round. Eventually XE will fade away, but that is a few years out.

- Gregg

 

[cassh1]

Colin, Gregg,
Thanks. (and yes I am darn glad that I don't work for BP).

I am not so worried about archiving - leaving the old database around is a good enough plan for this event. I do understand that it might worth looking at a tool to keep the old data if it turns out that we only need some of it 'forever'. Good point but not really the question that I am asking.

I am more interested in what others can justify in leaving behind. Assuming that we can keep it somewhere else. Gregg it sounds like you are doing what we propose 'We will only be bringing over summary data and the new system will be a "do-over". Which has an added benefit of purging the unpurgable, the mystery thing called media objects.' Can you give me a little more detail? We would like to apply your MO theory to lots of other data too. This company was formed by copying another and they have lots of data that they don't need and we will have to use the upgrade to get rid of it. So to be more specific, summary what? I am really only interested in the following data:
Financials - this I think should be 902 opening balances and 911 GL details for GL stuff. Any related pieces of data such as business units have to come too if they have a balance. If not, leave them behind.
HR - this is the sticky one really. Do we need the current system to hold all historical payroll data? Colin, being Canadian - are there 'rules'?
Asset and Equipment Mgmt - so this is things like Work Orders, Asset Masters, Purchase Orders.

Any thoughts appreciated.

Sue

 

[gregglarkin]

[ QUOTE ]


So to be more specific, summary what? I am really only interested in the following data:
Financials - this I think should be 902 opening balances and 911 GL details for GL stuff. Any related pieces of data such as business units have to come too if they have a balance. If not, leave them behind.
Sue

[/ QUOTE ]

Sue from NOT BP,

I would share the details, but afterwards I would have to kill you. But seriously, the summary data stuff is being handled elsewhere, so I have no details to share.

The business unit that is going first runs financials and procurement. They are using "Purchase to Pay". On 9.0, they will add in "Order to Cash". So that stuff is not transfering over. From what I've gathered, financial balances and adress book records are the two biggest areas coming across.

In this conversion we are going from SQL to Oracle, Windows to Linux, XE to 9.0, and implimenting a whole new chart of accounts. Piece of cake.....

Oh yea, as an added bonus, going from a case insensitive database (sql) to a case sensitive one (oracle). The DBAs from Oracle can't understand why that last one is freaking us out. Wheee! Gonna be a fun ride.....

- Gregg

 

[cassh1]

Thanks again.
Colin - good points on the two year comparison.
And yes it is public company and we know about IFRS. Cenovus. New split of EnCana. So they inherited all EnCana data that is not needed - that will be a nuance to deal with.
Gregg - good luck with the case thing. One suggestion - at that other company we made searchable descriptions upper case only to avoid what you are worried about. Also users learn quickly the options to search for with QBE otherwise.

See you sometime.

Sue

 

[jstooge]

As much as it pains me I must agree with Sir GL. Purging can become a monster project in itself, so it almost needs to be a project in most cases. In the worst case, I generally advise for a test data only upgrade (usually what will happen on go-live) or two to see what the timing is. If the timing is out of scope for the allocated time for go-live, then target the biggest tables for purge. Usually though, most upgrade windows I've been involved with have been 3 day weekends, performing a data only upgrade, and with the hardware today, have not really faced a need to purge prior to the upgrade.

 

[jstooge]

[ QUOTE ]
[ QUOTE ]


So to be more specific, summary what? I am really only interested in the following data:
Financials - this I think should be 902 opening balances and 911 GL details for GL stuff. Any related pieces of data such as business units have to come too if they have a balance. If not, leave them behind.
Sue

[/ QUOTE ]

Sue from NOT BP,

I would share the details, but afterwards I would have to kill you. But seriously, the summary data stuff is being handled elsewhere, so I have no details to share.

The business unit that is going first runs financials and procurement. They are using "Purchase to Pay". On 9.0, they will add in "Order to Cash". So that stuff is not transfering over. From what I've gathered, financial balances and adress book records are the two biggest areas coming across.

In this conversion we are going from SQL to Oracle, Windows to Linux, XE to 9.0, and implimenting a whole new chart of accounts. Piece of cake.....

Oh yea, as an added bonus, going from a case insensitive database (sql) to a case sensitive one (oracle). The DBAs from Oracle can't understand why that last one is freaking us out. Wheee! Gonna be a fun ride.....

- Gregg

[/ QUOTE ]

Gregg...what, you aren't moving every office to a new location, attempting to perfect human cloning so there is a Gregg in each office, and decipher the first 7 petabytes of the large hadron collider at the same time? You are such a lacker!!!

 

[gregglarkin]

[ QUOTE ]

Gregg...what, you aren't moving every office to a new location, attempting to perfect human cloning so there is a Gregg in each office, and decipher the first 7 petabytes of the large hadron collider at the same time? You are such a lacker!!!

[/ QUOTE ]

Who sez we're not? That's happening over in R&D. I spend my time in IT.

Just what the world needs, clones of me {shudder}......

Or worse, clones of Altquark.......

Or even worse, a mashup of Altquark and Jstooge.... {oh the horror!!!!}

 

[jstooge]

Didn't you know that the premise for the current movie Splice, and the horrors involved, came from the idea of the offspring of Altquark and Jstooge and BrotherOfKaramazov's DNA???

 

[gregglarkin]

[ QUOTE ]
Didn't you know that the premise for the current movie Splice, and the horrors involved, came from the idea of the offspring of Altquark and Jstooge and BrotherOfKaramazov's DNA???

[/ QUOTE ]

So which one of you was the mom?

 

[jstooge]

I am SO not touching this one. Why don't you get back to your ERP/PLATFORM/OS/HARDWARE/BUSINESSPROCESS/AUTOMOBILE/PERSONALCHECKINGACCOUNT/CANINERABIESVACCINATION/LIBRARYCARD upgrade?

 

[cdawes]

Ahhhhhh.......the divesting of business units. Okay I forgot thanks for reminding me.

Okay at the risk of being bold (very bold) your best option may be to:
1. Get Xe current to SP24 and move it to the latest O/S, DB & whatever else you're running

2. Reimplement the whole ERP on 9.X and get what you want.

Here's my rationale:
1. Get Xe current so you can run it on the smae hardware as 9.X which will help sell your project. No one like legacy systems running on legacy hardware.

2. Reimplement on 9.0 because it really is the simplier option. The chart of accounts, customers, vendors and everything else might be too big and irrelevant. The security model is.......well it's Xe so it sucks. Just do it right on 9.X

3. Custom mods that applied to "them" don't apply to "us". Custom mods are expensive to make and even more expensive to upgrade. If you still really want some reports or apps then go get Boomerang (YES ALEX I DID SAY BOOMERANG).

4. New implementations where users are already famaliar with JDE can cost less than an upgrade

5. Chart of Accounts. New company = new chart of accounts. Accept this law of nature and go with the flow.

6. Data retentiona. You don't need to worry about retention laws in Canada if you retain all the data. Just keep the legacy system running for "historic" purposes. This also makes the entire audit easier as auditors freak out more on upgrades than on new implementations.

Also on a new system you don't have the old data so smaller tables = faster queries.

I have done several company splits and each time we get the same issues with
1) Security model is too complex
2) Chart of accounts is too big/wrong
3) Too many customers, vendors, etc that do not apply to us but we can't delete them and maintain data integrity
4) Transaction files are too big.
.....and many others.

My most recent split project was from the North American instance of a Cement Company. 5,000+ users down to 500 users and 5 TB+ Production Instance down to 1.5 TB Production instance.

This was a quantum shift and obviously a security model for 5000+ users doesn't work well for 500 users.

Keep in mind the delta changes from where you were to where you are now in your decision to either UPGRADE or RE-IMPLEMENT.


Colin

 

[gregglarkin]

[ QUOTE ]


Also on a new system you don't have the old data so smaller tables = faster queries.



[/ QUOTE ]

And while you're at it, move the company to an Exadata so the queries are REALLY REALLY fast. And then we can form our own Quest SIG. Right now it's lonely being the only JDE shop implimenting a Deathstar....

But we do have really cool t-shirts and lightsabers.

 

[jstooge]

Oh Gregg...I always thought you of you wearing a helmet and breathing heavy...I just never knew you could handle your schwartz as well as Lone Star.

 

[cassh1]

Well that was an interesting sidetrack off into cloning and deathstars.

Colin,
We have already decided to do a re-implementation so are right there with you. Unfortunately we will still need to get a lot of data from Xe to 9.x. So I think we still need to do an 'upgrade' for the data. Probably along with some additional transformation for new COA or whatever. I am trying to get some early thinking generated on a data conversion plan and was wondering how others have done it.

We have already bought in to your points 1 thru 5 (SP24/11G goes to PD in Aug).

It is 6 that is the challenge.
Quote - " 6. Data retention a. You don't need to worry about retention laws in Canada if you retain all the data. Just keep the legacy system running for "historic" purposes. This also makes the entire audit easier as auditors freak out more on upgrades than on new implementations."

How long do we have to keep the legacy sytem 'running'?
One year online then data query after? How long does it have to be there for data query? Data retention is such an undefined thing.

And how would we do 2 year comparisons?

Thanks for the ideas - it is good to know that we are on the right track.

Sue (off to a Stampede breakfast - yahoo!)

 

[gregglarkin]

[ QUOTE ]
We have already decided to do a re-implementation so are right there with you. Unfortunately we will still need to get a lot of data from Xe to 9.x. So I think we still need to do an 'upgrade' for the data. Probably along with some additional transformation for new COA or whatever. I am trying to get some early thinking generated on a data conversion plan and was wondering how others have done it.


[/ QUOTE ]

Hey Sue

From what I've been able to gather from my spot in the cheap seats, our strategy is to do a limited amount of data imports. We have a legion of contractors writing UBEs that kick off Z file imports of data from XE into 9.0.

We have set up a "9.0 PD" environment which is where they are doing the setups for the new Chart of Accounts and other setups. Our contractors are busy writing Z file UBEs (so we can reuse them later for other countries) to import data like customers and balances and stuff. Not overly involved in that part of it, sorry.

Our approach is very similar to yours - a new implimentation with some data imports, but not all. Earlier (much much earlier), I think the idea was to make a copy of the business data, then do a purge of old stuff and stuff that pertains to a different country (right now we are upgrading Mexico, leaving the US and Canada on XE). Since purging is such a mess, I think that idea was tossed aside for this new approach.

As for the future, my crystal ball predicts the following. When the other countries (US, Canada and Puerto Rico (Ok, technically it's a territory not a country, let's not nit pick) move over to 9.0, we'll keep the XE server up for a year to close out balances and stuff. The data is already being fed into a data warehouse. That's been around for years. So we'll use the data warehouse for longterm reporting and compliance issues. Or we could just copy the database on to one really big thumb drive and pull it out when we need it. What ever works best...

- Gregg