EDS365 - duplicate injections found in multiple projects - WKB118754

SYMPTOMS

• The same injection id, results id, sample set id, and so on are found in multiple projects

ENVIRONMENT

• EDS365, all versions

CAUSE

When a project is created in Empower, a schema is also created in the Oracle database. This schema is created using the project name, but it also includes a prefix and a suffix. For example, if the project name is 'Project_A', the schema name will be 'W_Project_A_000'. If you then make a child for this project, e.g., 'Child_JAN_20', the child's schema then becomes 'W_Child_JAN_20_000'. From now on, this child will think that 'W_Project_A_000' is its parent, and this can not be changed.

While 'Project_A' is in situ in Empower, if you go ahead and create another 'Project_A', Empower will automatically assign a schema name of 'W_Project_A_001'; the suffix will be incremented each time. Therefore, if you now create a child for this, 'Child_AUG_20', the child knows that the 'W_Project_A_001' is the parent and NOT the 'W_Project_A_000' schema.

We find the issue when the following occur:

• 'Project_A' is created, (W_Project_A_000) and the child is created also (W_Child_JAN_20_000).
• 'Project_A' is removed from Empower.
• 'Project_A' is recreated again, some time later. It is then given the schema name 'W_Project_A_000' again, as there is no reason to increment the suffix due to the original project no longer being in Empower. 
• The second version of 'Project_A' has a child created 'Child_AUG_20', (W_Child_AUG_20_000).
• Now 'W_Child_AUG_20_000' and 'W_Child_JAN_20_000' both have a unique parent name as 'W_Project_A_000'. However, these are two separate parent projects in Empower. 

EDS365 cannot handle this scenario due to the fact it is a data warehouse and it retains all records. The data warehouse allows the customer to be able to trend data over time and run metrics on data that is no longer available in Empower. In the example above, because two child projects are both linking themselves to the same parent project, this is causing errors when building the project hierarchy, leading to duplication of data across the schemas. CRI-2843 has been created for this situation to see if there is a way to better handle this in the future. At present, the tables are built on unique schema names, but the example above shows that what Empower sees as a unique schema name does not work in EDS365.

FIX or WORKAROUND

There is currently no fix for this issue. Adding the original parent back into Empower would give the parent a suffix of _001 in the schema name and neither of the child schema would relate to it. Also, if you removed the second project and reinstated the first, both child projects would again link the first project. 
 

The only way to handle this issue is to recommend that the customer change their procedures in either of two ways: 

• Use a unique method of nomenclature for every project.
• Do not archive and reuse names for projects. If they are going to use the same project name, ensure that the original is kept in Empower.

ADDITIONAL INFORMATION