Re-Implementing PeopleSoft Data Conversion Strategies
Although I strongly believe that my current Customer should not have opted for a re-implementation, I do not intend to squeeze every penny out of his pocket. Now it is my responsibility to reduce the amount of effort required in this project. The effort required in data conversion will define the project and I badly wish that I pull it off. Today I started documenting the various strategies which could be followed for data conversion and I look forward to your valuable suggestions to choose the best out of these. The complexity with this project seems to be never ending - The customer has a single 7.5 instance and he wants to split the single instance into two different 8.9 instances (X and Y). The amount of customer data amounts to approximately 300GB.
Re-Implementation - Data Conversion Strategies
The purpose of this post is to analyze the various data conversion strategies that can be adapted during a PeopleSoft Re-Implementation when the Customer intends to move from PeopleSoft version 7.5 to 8.9. The biggest challenge in a PeopleSoft implementation is data conversion and the right strategy adopted at this stage marks the early success of the project. Let us look into the various data conversion strategies available in brief.
Upgrade-Like Approach
In this approach, data conversion is achieved by using the PeopleSoft delivered Application Engine programs customized to suit our requirement. To achieve our objective of migrating the application data from the Old PeopleSoft system (7.5) to our new system (8.9) we must first upgrade the PeopleTools release of our 7.5 system to 8.46 and then copy the records, fields and record fields from our newly implemented 8.9 (We must have applied the customizations to the new system) system to the 7.5 system. After this we run “Alter without deletes”. Copy the application engine programs delivered by PeopleSoft for data conversion into the 7.5 system. Customize the conversion programs to suit our requirements. Run data conversion programs and then “Alters with Deletes”. The application data in our 7.5 system can now be migrated directly to our new system using database tools for export and import. Finally we can purge the data not required by X and Y in the X and Y systems respectively.
Pros
The effort required to create the data conversion scripts can be greatly reduced.
The delivered data conversion scripts can be used with slight modifications (Modifications are required to handle data conversion programs specific to the upgrade path 7.5 to 8.8 as these programs will be expecting the records to have structures of 7.5+8.8, what we have in our database is 7.5+8.9)
The data conversion process is handled efficiently in this approach and hence the time required for conversion and migration is reduced greatly.
Cons
The PeopleSoft delivered data conversion scripts are path specific, hence effort required to merge the two sets of conversion programs (7.5 to 8.8 and 8.8 to 8.9) into a single program must be taken into account.
During the final move to production there is an added overhead which requires us to perform the PeopleTools upgrade of the 7.5 system.
PeopleTools upgrade will also require database migration from an older release of Oracle to the supported Oracle database release.
‘RE’-Implementation Approach
In this approach we develop SQL scripts which will convert the data in the 7.5 system to the data format required by the 8.9 system. The SQL scripts will convert data by a series of CREATE, ALTER, UPDATE and INSERT statements depending on the data format required by 8.9. The data conversion happens within the 7.5 system and hence it is very efficient. These scripts are created with the help of the PeopleSoft delivered data conversion programs. Once data conversion is complete we can migrate the data from the old system into our new system using data migration tools. Data segregation between the X and Y systems can be achieved by purging the data that is not required in each of these systems (Scripts must be developed for this purpose, these are simple DELETE statements with conditions specific to purge X and Y data).
Pros
The overhead of upgrading the PeopleTools release of the 7.5 system during final move to production is removed.
The data conversion process is optimized by directly migrating the data from 7.5 to 8.9 system after conversion within 7.5 system.
Cons
The effort required to develop the conversion scripts is huge.
The reliability of the conversion scripts cannot be assured until we obtain the comprehensive test results.
DBLinks Approach
Data conversion programs delivered by PeopleSoft for the upgrade paths of 7.5 to 8.8 and 8.8 to 8.9 are analyzed and merged to create a single Data conversion application engine program. The data conversion programs are customized to read data from the old release database using DBLinks and insert the data into our new release systems. Data segregation for X and Y specific data is achieved by customizing these programs to suit our requirements. The tables which are not handled by the conversion programs can be directly migrated from the old release database to the new release database.
Pros
The overhead of upgrading the PeopleTools release of the 7.5 system during final move to production is removed.
Data segregation between the X and Y systems are achieved in a single step.
Cons
The migration of data between databases using DBLinks is not desirable for large amounts of data as this may require a large amount of CACHE to hold the selected data from the old release database.
DBLinks in Application Engine programs is not recommended by PeopleSoft.
Migrating data from the old release database could be time consuming if the Servers are in different locations, this would be the scenario with X and Y systems.
Database Schema Approach
The application data is migrated from Copy of Production to a new schema in the database server containing the new release database. The delivered data conversion scripts must be customized to read data from the tables that exist on the database schema for copy of production and insert the data into the tables present in the new release database schema. This would increase the efficiency of data migration. Data segregation for X and Y specific data is achieved by customizing the delivered programs to suit our requirements.
Pros
The overhead of upgrading the PeopleTools release of the 7.5 system during final move to production is removed.
Data segregation between the X and Y systems are achieved in a single step.
Cons
Although this sounds logically feasible, the complexity involved can only be addressed by a PeopleSoft DBA.
The conversion programs must be tediously analyzed and the schema names must be added to each of the conversion scripts.
Pure Implementation Approach
Data conversion and migration are achieved in this approach by developing Application Engine programs in the 8.9 system to migrate and convert data from the 7.5 system. The migration and conversion can be achieved by developing Component Interfaces. Separation of X and Y data can be handled from within these conversion programs.
Pros
The overhead of upgrading the PeopleTools release of the 7.5 system during final move to production is removed.
Data segregation between the X and Y systems are achieved in a single step.
Cons
Migration and conversion can be achieved using Component Interfaces or Application Engines only if application data is present in a flat file format, hence during final move to production all the data in the 7.5 system must be converted to flat file format.
Cut off time required during final move to production is considerably increased due to the requirement of converting the data into flat file.
The conversion process will not be as efficient as the conversion within the database as in the previous case.
Partial Implementation Approach
In this approach we identify all the tables which do not require any structural changes for data migration (Customer created tables are the best examples to this class of tables). Migrate all these tables from 7.5 to 8.9 using database migration tools. For the tables requiring conversion we develop the conversion programs as described in the Pure Implementation Approach. Data segregation for X and Y systems can be achieved in the conversion scripts for tables that have a structure change between the old and new releases. For the tables which do not have structural modifications, data segregation can only be achieved by purging the data that is not required in each individual system.
Pros
The overhead of upgrading the PeopleTools release of the 7.5 system during final move to production is removed.
The data conversion process is optimized by directly migrating the data which do not need conversion.
Cons
Although data conversion is optimized by directly migrating data from tables which do not need any data conversion, ideally the amount of data within these tables will only amount to a small percentage of the entire application data in the system.
Cut off time required during final move to production is considerably increased due to the requirement of converting the data into flat file.
The conversion process will not be as efficient as the conversion within the database.
Kindly vote for the approach that you would be following or if you have a better approach do let me know.
7 comments:
I sincerely appologize for this work related post, the chief intention of this blog was to share my ideas rather than experiences, but this time I think saving the customer has a higher priority.
I feel an Upgrade Like Approach is better. The main reason behind this reccommendation is that you don't have to prepare the conversion scripts which is very time consuming. I feel the time it takes to develop and test these conversion scripts (Create, Alter etc.)is far more than the time it takes to upgarde the tools version.
DB Links are slower and not advisable as you said.
In schema approach I feel the time it takes to customize the data conversion scipts to read from 7.5 tables to insert into 8.9 tables will discourage you from taking up this approach; the same is the case with Implementation approaches.
-Ramesh
(Note: What I said above is not out of my experience; that's just what I feel)
Ramesh,
Thanks for your response. We are still discussing each of these approaches in detail and looking forward to provide the customer with the best possible approach. Our discussion focussed a lot on the upgrade-like approach because we had a strong belief that this would save us a lot of time. BUT, these are the challenges;
1. The conversion scripts delivered by PeopleSoft are path specific - that is "7.5 to 8.8" AE cannot be used directly because they will be expecting table structures of 7.5+8.8 - this should involve certain amount of effort.
2. Effort saving could be a lesser consideration to the client compared to cut over time, take this for example - we use the customized AEs and save the overall effort but ask for an unrealistic cut over time compared to develop our custom PL-SQLs or SQLs with a lot of initial effort but bring down the cut over time to minimal.
3. The delivered data conversion scripts work well during an upgrade, but are there any pre-requisites for successful executuion of these programs which we perform using the upgrade template - Renames scripts must be run in addition to copies prior to data conversion.
We are still pondering if we could pull this off! We are optimistic!
Thanks.
We are doing a 2-step upgrade approach. 7.5 to 8.3, then 8.3 to 8.9.
Currently there is not a path from 7.5 to 8.8, only 7.51 to 8.8.
We feel doing the duplicate upg path's will be cleaner than trying to re-implement which would require data conversion scripts (yuk!).
anonymous,
yuk! That's precisely what I've been telling right from day one....check out the post titled "God Save Customers".
Now we are finding out how complex it is to understand a code written by somebody!
Thanks for your comment.
I just finished upgrading a customer's Fin/SCM from 7.52 to 8.9. I did a "simple" 7.52 to 8.8 and 8.8 to 8.9 upgrade.
Of the two, you will find that 7.52 is the more demanding/complex upgrade. If you get the 7.52 step right, 8.8 to 8.9 will be a breeze (YMMV).
If I was in your shoes, I would go with the upgrade route.
Though the client's concerns are valid to some extent in terms of multiple points of failure viz-a-viz testing, it must be pointed out that 8.8 is an intermediate upgrade. They don't need to do separate testing for each phase.
I know a large bank which decided to go implementation and then conversion route and it took them a good 3 years to get to that point. Obviously, Project management had something to do with it too, but this route is almost always more costly for the client.
Personally, I find the tools upgrade part the easier part of the upgrade. Its the conversion which is always a bear. I would try to remind the client that Peoplesoft has put its considerable resources into the conversion scripts and the fact that you would essentially be re-inventing the wheel when it comes to implementation-then-conversion route.
The only thing you'd be getting is squeaky-clean tools tables and you'd get those from the 8.9 demo anyways when you do the 8.8 to 8.9 upgrade.
Like you said, these decisions are always hard, especially when the client has their mind set on a certain way of doing things.
good luck with your decision.
If you want to be the most sophisticated Upgrade consultant - A re-implementation project will do a lot of justice to your cause. This is something that I found out after starting to work with this project.
Thanks for your comment.
For the Blog readers -
I've not posted anything for sometime cause I'm looking at too many things at this point in time.
1. Philosophy
2. Business Processes
3. Building a working code for my dream.
4. Analyzing all the possibilities of acceptance from the market for an ERP product going the MS way!
5. Working with the Re-Implementation project.
I'll let everbody know about what I've learnt from each of those experiences. The first point is just for my personal satisfaction and don't expect anything from that front.
Thanks,
ps-guy
Post a Comment