There are many books and blogs on data migration, several of which even pertain to Agile PLM, but most of what I have seen reads more like a sales presentation or material put together by someone who hasn’t personally dug in to understand how to get the migration done. This blog is the first in a series to address HOW an Agile PLM migration is done from a technical perspective.
- Agile PLM Data Migration – Part 1 (this blog): Anatomy of an Agile PLM Data Migration
- Agile PLM Data Migration – Part 2: Data migration execution – setting the stage
Validation requirements, data sampling sizes, etc. are all important topics for data migration but I consider them more of the business unit’s responsibility as I have never been a fan of having developers performing quality control against the product of their own deliverable. Naturally unit testing should be performed by the migration expert along the way. So what does a real world data migration look like?
The above chart is my “Anatomy of an Agile PLM Data Migration.” Let me try to break things down for you.
Kickoff: It is important for the business unit and IT to know the people involved in the data migration process as the migration analyst/developer will typically need access to source databases, end user tools and their time. The kickoff meeting is the perfect place to discuss the process and access requirements needed. Having authoritative stake holders in this meeting is crucial to reducing any delays in the approval process.
Mapping workshop(s): Once access is granted to both Agile and the source system(s), we normally try to look at the data to get a feel for how it is organized or any gotchas that stand out. After we meet with the business unit and a source system technical expert, if one is available, to “map” the source data attributes to the Agile attributes. To do this we create a spreadsheet that has all of the Agile configuration along with columns for the source attribute, a defaulted value, and transformation notes. We then go through each Agile field and establish where that value will be derived from, if it has a constant value and what transformation needs to take place. A transformation may be as simple as changing the value of a product line in the source to another product line value in Agile or it may be more complex like building a notes field based on several different fields in the source based on other conditional values. The mapping is part of the specification we use to build the code for the migration.
Next there are three tasks that can be done in parallel:
Extract the source data: Every migration is different due to complexity, corporate policies and expertise so we may do the extraction or the customer’s expert may. It really doesn’t matter so long as it is in a consumable format.
User migration: This is the gray block in the flow chart. If users are to be migrated it is preferred that they are done so prior to my involvement. Using the built in Agile import is the preferred method for adding users as using Oracle’s data load tool will require a restart of Agile PLM. If users are to be migrated, a spreadsheet whose columns align with the Agile attributes can be used. If access to the source system is granted then user data that can be extracted can be provided to the business unit so that they can append any missing/required fields. At a minimum login id/username, first name, last name and email should be provided. If users are not to be migrated and users from the source system are not in Agile, it is common to use a dedicated user for object originators, change analysts, etc.
Translation tables: For list attributes a separate spreadsheet is provided containing the Agile list values and a column for the business unit to add the values seen in the source system. This spreadsheet, along with the mapping document, will be used in the code to stage the data for migration.
With the mapping document, translation tables, and extracted data available, its time to start working on the data.
Stage data: Since Agile PLM leverages Oracle, it highly advantageous to use Oracle to assemble the data. The Agile data load tool’s schema gets installed and the source data is imported into staging tables. Depending on the source system, this may be done from code, such as Java or .Net, or via a database to database connection. Having the data in Agile itself allows for preventative validation as well. For example, we can verify item number collisions will not occur and that all referenced users exist. Other migration information can also be assembled for things such as relationships. Code can be written to link existing objects to new objects being migrated if clear rules are established. From this point on all code is PL/SQL with the exception of any auto number assignments (we have a Java utility for this).
First Pass?: If this is the first migration pass then the staging table can be exported and reviewed with the business and migration team to verify that the values look correct. If there is anything that needs changed, the mapping document, translation tables and/or code are updated. If there are no changes, then the data can be loaded into Agile.
Load into Agile: Once the business unit feels the data is close, it’s time to see what it looks like in Agile. The preference is to take a production database dump, load it into a dev system before EACH migration pass. This will verify that changes in attributes do not cause any problems and provides a better test of the migration process. It should be expected to have several migration passes as the mapping rules change but it is highly recommended that the migration pass prior to the production migration require no changes! It is critical that all migration code and the process itself be fully vetted before migrating to production.
Validate data and report issues: In this step, the business needs to validate the metadata and attachments according to their validation plan.
Changes required?: If changes are required the mapping, translation and/or code should be updated and another pass executed.
Production Load: The standard protocol is to migrate over a weekend after a database and file vault backup. Depending on the type of data migrated, the system may need to be restarted (such as users). Additionally while loading data, indexes are dropped which will prevent users performing searches from finding any data. Communication between the business and Agile users is critical. After the production load, the business should validate what they can before allowing users back into the system in case a rollback is required.
Things to consider:
- Expect several migration passes
- Understand limitations exist. The data load tool is not perfect and there is next to no support. In some cases compromises will need to be made or alternatives will need to be explored. Some things are simply out of our hands
- Provide adequate time and resources to the project. The migration is just as much the business unit’s responsibility as it is the migration analyst!
- Don’t expect the first pass to be perfect; it won’t be
- Review your data, don’t assume it’s right
- The Agile data load tool is a controlled software product from Oracle – I cannot distribute it. If you need the tool you will need to contact Oracle
Be the first to post a comment.