Cleaning FDMEE Artifacts in Bulk Using This Easy and Quick Method in 3 Steps

Bulk Cleanup of FDMEE Artifacts

While working with metadata, the usual habit involves the creation of multiple artifacts as and when required for testing. These are created by the users in an application during their development, testing, or enhancement process. These FDMEE artifacts stay in the system unless you perform regular maintenance and cleanup. But cleanup usually happens when all the thorough testing is complete and the users are sure that they will not the unwanted artifacts any further.

When few users are involved in this process, the number of artifacts would be less in the system and cleanup would not take too long. But what if the user base is large and the unwanted artifacts are not cleaned up periodically? Then it becomes a huge mess.

These unwanted artifacts not only reduce the overall system performance but also cause issues within an application. So it is better to take a look and perform a cleanup of artifacts that are no longer required. But let’s say that maintenance is not performed periodically and the accumulated artifacts become large in number, what kind of effort does it take to clean up these artifacts?

Now let’s consider a scenario in which an infrastructure person is working on an upgrade and they bring over the FDMEE artifacts through LCM from an old environment to the new environment. Some of the artifacts that are brought over don’t make sense as those were created way before and are no longer required. So the client wants us to perform cleanup and ensure that only relevant artifact stays in the new environment.

The InfraStructure person goes into the application and finds that the list is huge. All the unwanted artifacts including import formats, locations, and data load rules are in thousands and it would take days or weeks to perform a cleanup if an individual has to do it manually. In that case, an SQL query can be generated to delete all the unwanted artifacts from the backend.

The steps remain the same for both Oracle and SQL DBs and it’s always good to have the appropriate DBs backup just in case you need something back.

Considering your database is Oracle, follow the below steps to clean up the artifacts from the backend table.

1. Login to the FDMEE DB through SQL Developer and expand Tables.

2. Find below tables from where artifacts can be deleted.

AIF_BALANCE_RULES( contains Data Load Rules)

TPOVPARTITION( contains Locations)

TBHVIMPGROUP( contains Import Formats)

You can run the select query on the above tables and export that data to Excel to filter which entries you want to delete. Once you have that information, include that in the query and delete it.

3. Run the below SQL to delete the artifacts from the table.

#To remove unwanted Data Load Rules, run the below query.

DELETE FROM AIF_BALANCE_RULES
WHERE RULE_NAME IN (‘Rule_Name1’,‘Rule_Name2’,‘Rule_Name3’);

#To remove unwanted Import Formats, run the below query.

DELETE FROM TBHVIMPGROUP
WHERE IMPGROUPKEY IN (‘Import_Format1’,Import_Format2′,’Import_Format3′);

#To remove unwanted Locations, run the below query.

DELETE FROM TPOVPARTITION
WHERE PARTNAME IN (‘Location1’,’Location2′,’Location3′);

Add as many entries of Data Load Rules, Import Formats, and Locations in the above query as you want to delete.

This would take only a few minutes to clear all the unwanted artifacts which otherwise would take a long time if you had to do it manually.

Happy Learning…!!!

Also, Check out the article – FDMEE Mappings Migrations Issue to learn about how you can migrate the mappings correctly in the FDMEE system and avoid any issues.