1. Overview
Most migrations of data from old (source) to new (target) systems require more than just mapping / transforming old to new values for equivalent columns. Often the new system “thinks” of the data in a different way and has thus modeled it completely differently, requiring more fundamental changes which entail serious data reorganization, and not just “column to column mappings”. A few examples of these would be mapping multiple rows to a single row, eliminating rows altogether, normalizing or denormalizing structures, i.e., turning multiple tables into a single one, or vice versa. Some data wrangling or ETL tools have evolved to do a fair job for the simpler migration data reorg efforts, but even the best still make it cumbersome or impossible in more challenging situations. In these cases, actual procedural code almost always beats off-the-shelf ETL tools for simplicity and time-to-implement. Often the off-the-shelf tool cannot do the job, period. This case study shows one such migration example and how the problem was solved using Oracle procedural language invoked from shell scripts.
2. Problem
The source system contained Parts List data that did not fit the new Windchill Parts List model, and in fact would simply not load to Windchill without structural transformation. Specifically, Windchill disallows the same reference designator value to be loaded twice for the same Bill of Materials (This is understandable since a reference designators (ref des for short) is commonly used to unambiguously identify a component within an electrical schematic or printed circuit board).
However the Parts List in this particular source system used the reference designators to denote call-outs on the drawing for design or assembly specifics, and when those specifics were common between different component part numbers on the same assembly, these call-out notes were shared by having both or several separate component part numbers share the same reference designator(s). For example:
| Row | Parent Part# | Child Part# | Find# | Reference Designator |
|---|---|---|---|---|
| 1 | 03-0800 | M11467S | 25 | R1-R3 |
| 2 | 03-0800 | M11468S | 49 | R1-R3 |
| 3 | 03-0800 | M11469S | 48 | R1-R3 |
| 4 | 03-0800 | M11470S | 24 | R1, R3 |
| 5 | 03-0800 | M11471S | 26 | R1, R3 |
| 6 | 03-0800 | M11472S | 47 | R1, R3 |
| 7 | 03-0800 | M11480 | 29 | R2 |
| 8 | 03-0800 | M11481 | 44 | R2 |
Windchill will attempt to load this Bill Of Materials into its two tables WTPartUsageLink, which is at the child part and find number level, and PartUsesOccurrence, which is a child table of WTPartUsageLink, containing that child component’s reference designators. Many PartUsesOccurrence rows can exist for one WTPartUsageLink, containing multiple reference designators. However our target Windchill implementation will not allow the same reference designator to appear more than once in the BOM, thus this data will be rejected by Windchill during loading.
Because no two child components of the same parent can share any reference designator, our source data could not be migrated into Windchill without further transformation.
3. Solution - Overview
The general solution path as decided in conjunction with the Windchill SMEs was to transform the source data before loading, so that just ONE child component on a BOM would carry any specific reference designators, and the other components which also needed to refer to that designator for assembly callouts, would have their find numbers modified to match the first (carrying) component’s find number. Further, the shared find number would be marked, for those non-carrying components, with a custom attribute (IBA) to inform the user that this new find number was a REFERENCE back to another component’s shared reference designators.
Thus our transformation would turn this:
| Row | Parent Part# | Child Part# | Find# | Reference Designator | Find# for Reference Only Flag (IBA) |
|---|---|---|---|---|---|
| 1 | 03-0800 | M11467S | 10 | U1-U4 | |
| 2 | 03-0800 | M11468S | 25 | U1-U4 | |
| 3 | 03-0800 | M11469S | 30 | U1-U4 |
Into this:
| Row | Parent Part# | Child Part# | Find# | Reference Designator | Find# for Reference Only Flag (IBA) |
|---|---|---|---|---|---|
| 1 | 03-0800 | M11467S | 10 | U1-U4 | |
| 2 | 03-0800 | M11468S | 10 | R | |
| 3 | 03-0800 | M11469S | 10 | R |
A document was produced as well during the transformation listing the old and new find numbers and reference designators for all components on the parent part, and stored in Windchill as an attachment to the parent part.
4. Solution - Detailed Transformation Rules
Of course, much more complicated reference designator sharing existed in our source data and our solution had to accommodate these. Returning to our first example:
| Row | Parent Part# | Child Part# | Find# | Reference Designator |
|---|---|---|---|---|
| 1 | 05478-00 | G88367S | 25 | R1-R3 |
| 2 | 05478-00 | G88368S | 49 | R1-R3 |
| 3 | 05478-00 | G88369S | 48 | R1-R3 |
| 4 | 05478-00 | G88370S | 24 | R1, R3 |
| 5 | 05478-00 | G88371S | 26 | R1, R3 |
| 6 | 05478-00 | G88372S | 47 | R1, R3 |
| 7 | 05478-00 | G88380 | 29 | R2 |
| 8 | 05478-00 | G88381 | 44 | R2 |
From the table we see 8 children of the same parent, with their find numbers and reference designators. Together they share the three reference designators of R1, R2 and R3. Rows 1-3 have the common set of R1, R2 and R3. Rows 4-6 have R1 and R3 only, while the child components in rows 7 and 8 have only the ref designator of R2. On the source system’s related CAD drawing, there is a single specification callout for R1, one for R2 and one for R3. The R1 and R3 callouts are meant to apply to the child components named here in rows 1 through 6, while the R2 callout applies to the child components shown in rows 1, 2, 3, 7 and 8.
The first set of transformation rules we applied was:
Transformation Step 1:
1a. Break apart sequenced reference designator designations to create one row for each ref design (e.g., R1-R3 value generates three distinct rows… R1, R2, R3)
1b. Identify children WTPartUsageLink rows which share reference designators with other children of same parent.
1c. Select ONE WTPartUsageLink child row to be the PRIMARY PARTUSAGELINK, and carry the reference designator. The one selected must not itself have reference designators which are not included in other children which share ANY of its ref designators (“all who share any must share all” rule).
1d. Take other children which share (all of) the selected primary’s refdesigs, remove their refdesigs, and update their PARTUSAGELINK rows to be REFERENCE ONLY.
After Transformation Step 1:
| Row | Parent Part# | Child Part# | Find# | Reference Designator | Find# for Reference Only Flag (IBA) |
|---|---|---|---|---|---|
| 4 | 05478-00 | G88370S | 24 | R1, R3 | |
| 1 | 05478-00 | G88367S | 24 | R | |
| 2 | 05478-00 | G88368S | 24 | R | |
| 3 | 05478-00 | G88369S | 24 | R | |
| 5 | 05478-00 | G88371S | 24 | R | |
| 6 | 05478-00 | G88372S | 24 | R | |
| 7 | 05478-00 | G88380 | 29 | R2 | |
| 8 | 05478-00 | G88381 | 29 | R | |
| 1 | 05478-00 | G88367S | 29 | R | |
| 2 | 05478-00 | G88368S | 29 | R | |
| 3 | 05478-00 | G88369S | 29 | R |
Before transformation step 1, above, about 30% of the components rows in our source data shared reference designators with another component for the part and therefore would not load to Windchill. Following step 1, that percentage was down to 2%.
Examining this remaining 2%, the reason was discovered to be a “deadlock” condition as illustrated below:
| Parent# | Child# | Find Number | REFDES |
|---|---|---|---|
| 61-70277-002 | RNC55H1004FS | 50 | R69, R103 |
| 61-70277-002 | RNC55H1274FS | 51 | R69, R103 |
| 61-70277-002 | RNC55H1474FS | 52 | R69, R103 |
| 61-70277-002 | RNC55H1604FS | 53 | R69, R103 |
| 61-70277-002 | RNC55H1964FS | 54 | R69, R103 |
| 61-70277-002 | RNC55H9533FS | 55 | R60, R103 |
Neither the R69,103 pair nor the R60,103 pair can be used due to the ‘all who share any must share all’ rule, so these must be split up into 3 separate PartUsageLink rows carrying R69, R103 and R60 ref designators, respectively.
Transformation Step 2 (to cover above ‘deadlock’ cases):
2a. After application of transformation rule 1, identify those component parts which still contain reference designators shared with other components
2b. For each such component part, identify its’ “Problem Reference Designators” (those still shared with other component part).
2c. Creating new PartUsageLink row for each unique component and problem reference designator
2d. Re-apply transformation Step 1, to take advantage of newly created find numbers and allow duplicate reference designators to be removed
For example, after the initial application of step 1, our code identified the following problem data, with the problem reference designators identified (steps 2a and 2b):
| PARENT | CHILD | FIND | REFDESIGNATORS | Problem (shared) Reference Designators |
|---|---|---|---|---|
| 6055283-001-01 | 7018023-001 | 108 | U28-U29, U40-U41 | U28, |
| 6055283-001-01 | 7018023-002 | 109 | U1-U9, U19-U27, U31-U33, U43-U45 | U21,U31,U32 |
| 6055283-001-01 | 7018023-003 | 117 | U21, U28, U31-U32 | U21,U28,U31,U32 |
Step 2c then creates the following new PartUsageLinks each with single reference designator:
| PARENT | CHILD | FIND | REFDESIGNATORS | Problem (shared) Reference Designators |
|---|---|---|---|---|
| 6055283-001-01 | 7018023-001 | 108 | U28-U29, U40-U41 | U28, |
| 6055283-001-01 | 7018023-002 | 109 | U1-U9, U19-U27, U31-U33, U43-U45 | U21,U31,U32 |
| 6055283-001-01 | 7018023-003 | 117 | U21, U28, U31-U32 | U21,U28,U31,U32 |
After re-running the pass 1 transforms (step 2d), the PUL/finds look like this:
| PARENT | CHILD | FIND | REFDESIGNATOR | Find# for Reference Only Flag (IBA) |
|---|---|---|---|---|
| 6055283-001-001 | 7018023-001 | 108 | U29, U40, U41 | |
| 6055283-001-001 | 7018023-002 | 109 | U1-U9, U19-U20, U22-U27, U33, U43-45 | |
| 6055283-001-001 | 7018023-001 | 142 | U28 | |
| 6055283-001-001 | 7018023-003 | 142 | R | |
| 6055283-001-001 | 7018023-002 | 143 | U32 | |
| 6055283-001-001 | 7018023-003 | 143 | R | |
| 6055283-001-001 | 7018023-002 | 144 | U31 | |
| 6055283-001-001 | 7018023-003 | 144 | R | |
| 6055283-001-001 | 7018023-002 | 145 | U21 | |
| 6055283-001-001 | 7018023-003 | 145 | R |
This illustrates how the code isolated those reference designators which were causing the “deadlock”, isolated them off to their own PartUsageLink / Find Number rows, and pointed back up to them with the “Reference” find number flag (IBA).
Wrap-up transformation rules then removed holes (compressed) new find numbers and looked for opportunities to combine multiple reference designators into a single find number when possible (opportunities created after splitting into “one find number per reference designator”. These are not detailed here).
After these transformations were applied no duplicate reference designators remained and the data could be 100% loaded to Windchill without errors.
5. Solution - Code
These transformations were implemented as a series of Oracle Steps, which were statements, and, in some cases procedural language implemented as stored procedures. These Oracle steps were invoked sequentially from an invoking Unix Shell script. They were broken up such that each one did a small and distinct piece of work, and they generally updated “working” tables to be used in the next Oracle step. Multiple Oracle statements were performed for each above-listed “transformation rule step”, with about 30 Oracle statements in total for the entire transformation.
As an example, here’s a SQL statement for step 1b which identifies children WTPartUsageLink rows which share reference designators with other children of the same parent:
/*
Build table of shared refdesignators containing:
Parent info (partnumber/rev/cage) + child info (partnumber/cage/find) +
refdesig + ‘shared child info’ (partnumber/cage/find)
This is done by linking refdesig back to refdesig, looking for same parent, refdesig, but different
child+findnumber
*/
INSERT INTO refdesig_sharing_trans (
parentpartnumber,
parentpartorganization_name,
parentpartrevision,
childpartnumber,
childpartorganization_name,
findnumber,
schildpartnumber,
schildpartorganization_name,
sfindnumber,
refdesignator
)
SELECT
r1.parentpartnumber,
r1.parentpartorganization_name,
r1.parentpartrevision,
r1.childpartnumber,
r1.childpartorganization_name,
r1.findnumber,
r2.childpartnumber,
r2.childpartorganization_name,
r2.findnumber,
r1.refdesignator
FROM refdesig_trans r1
INNER JOIN refdesig_trans r2 ON (
r2.parentpartnumber=r1.parentpartnumber AND
r2.parentpartorganization_name=r1.parentpartorganization_name AND
r2.parentpartrevision=r1.parentpartrevision AND
(
r2.findnumber <> r1.findnumber OR
r2.childpartnumber <> r1.childpartnumber OR
r2.childpartorganization_name <> r1.childpartorganization_name
)
AND r2.refdesignator=r1.refdesignator;
6. Conclusion
This case study illustrates our migration “90-10 rule”, wherein 10% of the source data requires 90% of the effort to make it conform to the rules of the target system. Nevertheless by a detailed investigation of the source data, a thorough understanding of the business and data modeling rules driving the non-uniqueness (done in conjunction with engineering SMEs), and utilization of some custom Oracle coding, we were able to transform the data to an acceptable state and achieve 100% loading to WTPartUsageLink and PartUsesOccurrence with a 2 week effort, including investigation, rule enumeration, coding and testing.
Enter your email below: