Before data is extracted from the operational systems, it may be necessary to ensure that the data is completely valid. If the data is not valid, the integrity of the business analysis relying on the data may be compromised. For example, a value representing a monetary transfer between banks in different countries must be in the correct currency.
Data should be validated at the source by business analysts who understand what the data represents. Any changes should be made in the operational systems, rather than the data warehouse, because the source data is incorrect regardless of where it is located.
Validating data can be a time-consuming process. The validation process can be automated by writing stored procedures that check the data for domain integrity. However, it may be necessary to validate data manually. If any invalid data is discovered, determine where the fault originated and correct any processes contributing to the error.
For example, the data in the order entry system should be validated, to ensure that:
This information can be validated using the Data Transformation Services import and export wizards. A Microsoft® ActiveX® script, executed by the DTS Import and DTS Export wizards when copying data from the source to the destination, can determine if the region and product information is valid. Any invalid data can be saved to the exception log for later examination by business analysts, to determine why it is incorrect.
| Data Transformation Services Import and Export Wizards | Column Mappings |