Automation: Key to a Faster Clean Up of Orphan and Unused Objects in Informatica

Yogesh AgrawalNovember 23, 2015

Orphan and unused objects are a major headache for most developers. Despite best efforts to keep the data environment clean, no company is immune to orphan and unused objects.

What puts companies at risk? During the development phase, it’s not uncommon for a developer to forget to delete child objects that aren’t used in parent files, or to delete a mapping and be left with orphan sources and targets unassociated with any other mapping. Other times, a developer might intentionally choose not to delete objects, thinking they may be needed in the future. But over time, as the objects remain unused, no one remembers why they exist.

Getting rid of your repository’s orphan objects is a tedious, time-consuming process — yet integral to the success of your operation. Unfortunately, many developers overlook or ignore the issue for as long as possible, despite the best practice of a quarterly clean-up of orphan objects in an active development environment. When this happens, the metadata eventually becomes too large and cluttered, slowing down the repository’s performance and adding to your overall development time. The accumulation of unused and orphan objects also makes it difficult to understand how the database objects work together, leading some developers to spend time fixing and maintaining objects that are not even used.

The Standard Approach to Orphan Clean Up
Even when it’s clear that unused and orphan objects are interfering with your repository’s performance, orphan clean up isn’t a task many of us want to undertake. While Informatica offers some tools to help, clean-up is still a manual, lengthy process with multiple steps. These include:

  • Identifying orphans and unused objects – Developers must go to the repository and identify all orphan and unused objects. Depending on how many exist and the size of the metadata, this process can be quite cumbersome.
  • Creating a backup of all orphans and unused objects – Once the orphans and unused objects are identified, a backup of each object must be created.
  • Deleting orphans and unused objects – Deleting the orphans is a step-by-step, manual process. A script can be created for the deletion process to automate this step somewhat, but writing a script requires additional time.
  • Verifying that unused objects don’t have dependencies – Once the clean-up process is complete, the database should be checked to ensure that all objects deleted were not associated and that the database is functioning correctly.

A Better Way: Automation
To help make the cleanup of orphans and unused objects in Informatica less painful and time-consuming, our team at InfoCepts developed a way to automate the entire clean-up process in any Windows-based Informatica environment. To start the process, you first need to set up the parameters for the clean-up. Typically, this includes:

  • Name of the domain
  • Metadata repository details
  • Credentials to connect
  • Control file
  • Specifying the location to store backup files

Once these parameters are set, our solution is applied to the environment, enabling the remaining steps to be automated, including:

  • Identifying orphans
  • Generating a list of orphan objects
  • Creating a backup XML file
  • Deleting orphans
  • Cross verification of unused objects to ensure the objects don’t have dependencies

With our automated solution in place, developers can complete the clean-up process at an 80 percent faster rate than they can with the standard manual process. In addition, the simplicity of the solution increases the chance of adopting and sticking with the best practice of a quarterly clean up.

Eliminating unused and orphan objects from the repository can have a significant impact on your repository’s overall performance. It can also reduce maintenance hassles, make your database easier to navigate, reduce the need to fix unused objects, and allow your development team to work more efficiently.

Want to learn more about how InfoCepts can automate the clean-up of orphan and unused objects in your metadata? Find specs and details in the Informatica Marketplace.