Data validation is the critically important process of ensuring that only valid and clean data is migrated to a new system.
Although there are various ways to validate data and tools to help, the most common problem in Validation arises with Referential Integrity.
Referential Integrity means that relationships between tables in a database remain constant. If they don’t, the data will be inaccurate and, over time, it is common for events to occur that create ‘orphan’ data, with no relationship to other data in the database.
If this is important data, but you are unaware it has become detached and unrelated, it can cause major problems of data redundancy and inaccuracy. This means that decisions may be taken using information you can’t rely on.
Solving this is problematic and is often a manually intensive exercise. Our solution is to use a capability within our Transformation Manager (TM) software platform, called Relational Viewer (RV).
This allows you to easily and quickly browse and check relational data without the need to worry about sql queries to link the data together or the almost impossible task of navigating between large related flat files.
RV provides direct feedback on the structure of the data, highlighting places where there are large numbers of relationships, areas where relationships may have been lost along with areas containing various percentages of related data.
RV is sophisticated and you can select a particular row of data and follow its specific related information through the database. In a manufacturing system, for example, you could look at a product and view the relationships to and from the product table, processes table, and actions table to get a complete picture of all relevant relationships. RV also has a Discovery Wizard that can automate the process and provide valuable feedback on where links are broken.
RV can be used in conjunction with other capabilities in TM or alongside any other data validation application you may be using.
RV provides many advantages over alternative approaches but the biggest is the ability to view and follow relational data in any loaded metadata model including flat files.
This saves a huge amount of time, effort and cost and provides a clear indication of overall data quality within the entire data set being reviewed.
Even to someone who is not familiar with the data, it provides and easy and intuitive way of gaining a rapid understanding of the model.