Scrubbing Data

Scrubbing data involves making the data consistent. It is possible that the same data is represented in different ways in multiple operational systems. For example, a product name might be abbreviated in one operational system, but not in another. If the two values were not made consistent, any queries using the data likely would evaluate the values as different products. If the detail data in the data warehouse is to produce consistent information, the product name must be made consistent for all values.

Data scrubbing can be achieved:

• Using the DTS Import and DTS Export wizards to modify data as it is copied from the operational systems to the intermediate database, or directly to the data warehouse.
• By writing a Microsoft® ActiveX® script, executed by a program using the DTS API, to connect to the data source and scrub the data. Any data manipulation that can be achieved using ActiveX scripting, or a programming language such as Microsoft Visual C++®, can be performed on the data.
• Using a DTS Lookup, which provides the ability to perform queries using one or more named, parameterized query strings that allow a custom transformation to retrieve data from locations other than the immediate source or destination row being transformed.

For example, the data in the order entry system should be scrubbed, such as the values for:

• State (must always be a two-character value, such as WA).
• ProductName (must always be the full product name, with no abbreviations).

Using the DTS Import and DTS Export wizards, an ActiveX script can be executed during the copy process, that checks the State value, and changes it to a known two-character value. Alternatively, the ProductName value could be scrubbed by writing a Visual C++ program that calls the DTS API to execute Microsoft JScript® scripts , and other executable modules.

See Also