Transforming Data

During the data migration step, it is often necessary to transform operational data into a separate format appropriate to the data warehouse design. Transformation examples include:

• Changing all alphabetic characters to uppercase.
• Calculating new values based on existing data, including data aggregation and summarization.
• Breaking up a single data value into multiple values, such as a product code in nnnn-description format into separate code and description values, or a date value in MMDDYY format into separate month, day, and year values.
• Merging separate data values into a single value, such as concatenating a first name value with a surname value.
• Mapping data from one representation to another, such as converting data values (1, 2, 3, 4) to (I, II, III, IV).

Data transformation also involves formatting and modifying extracted data from operational systems into merged or derived values that are more useful in the data warehouse. For example, copying the OrderDate value from the order entry system to the data warehouse star schema, involves splitting the date into Month, Quarter, and Year components. These date components are required for the type of business analysis performed on the data warehouse.

The transformation process usually takes place during the migration process: when data is copied either directly from the operational sources or from an intermediate database, because the data has been scrubbed. For complex data migrations, DTS provides skip return values to assist in splitting data into multiple tables.

Data transformation and migration can be completed in a single step using the DTS Import and DTS Export wizards. Transforming and migrating data from the order entry OLTP operational system schema to the data warehouse star schema involves using the DTS Import and DTS Export wizards to:

• Create a query to extract all the required detail level (fact) data.
• Split OrderDate in the Orders table into Month, Quarter, and Year components and add to Period using a Microsoft® ActiveX® script.
• Extract the City, StateOrProvince, and Country data relating to the detail data and add to Region using an ActiveX script.
• Perform a simple table copy of Products.
• Create a query to generate the data for Summary.

Each step, for example, can be built as a separate package, which is stored in the Microsoft SQL Server™ msdb database, and scheduled to be executed every Friday night at midnight.

In addition to performing insert-based transformations of data, DTS provides data-driven-queries, in which data is read from the source and transformed, and a parameterized query is executed at the destination, using the transformed values in the destination row.

Note When using DTS to create fact tables for use with Microsoft SQL Server OLAP Services, do not create any aggregations while migrating the data. OLAP Services is specifically designed to create the optimal aggregations after the data warehouse has been populated with DTS. It is also unnecessary to segment a date into week, month, quarter, or year columns in the Time dimension table. The OLAP Services Time Wizard provides an automated facility for this type of time transformations.

See Also