Data Transformation

Creating an effective data transformation phase is usually a critical success factor in most Information Delivery solutions. This is the data factory that takes the raw materials and turns them into finished goods. This phase must always be closely tied to the evolving user requirements and may provide the largest training challenge. The outcome of this phase is the data warehouse container. That holds the selected, prepared, transaction details and entity characteristics.

• Data Models – This work analyzes the business requirements and the "data inventory" that results from the Acquisition Phase and maps them into the main delivery plan. There is usually a significant amount of exploration and experimentation done here. Some gaps (between the available data sources and the business requirments) may not be able to be accomplished. The Information Delivery data model will need to evolve, forever. Data quality will usually improve as the Information Delivery system educates staff of the new issues. New data, user groups, and business requirements will also need to be incorporated into the master plan.
• Process – Repeatable steps that can be performed to continually adjust and transform data into usable information that is standardized and consistent in format, content, relationships, and timing.
• Synthesis - Decisions and logic on which data elements to include and how to merge and adjust them. This work needs a new kind of data analyst. These persons are data publishers. They need to have skills that include data architecture, modeling, DBA, and business intelligence requirements.
• Tools – Data can be transformed in many different ways and by different tools. Each tool choice has different costs and benefits. The tool decision and implementation plans can make or break the final usability of the data and drastically affect the final ROI of your system.
• Containers – are used here to hold the various interim stages of data as it moves through the entire transformation process. Trying to do everything in one step is not usually a wise approach. There are some significant efficiencies and optimization available by de-coupling the work into discrete tasks.
• Metadata – This is the evolving heart of the entire solution. It must now include the data selection and transformation logic. This is also where the beginnings of the user metadata start being defined.

Samples:

• Merging data from disparate systems
• Address Standardization
• De-Duplicating entities
• Managing hierarchies (insuring consistent filtering and sorting values)
• Creating optimized reporting structures (i.e. star schema)
• Fixing problems and inconsistencies in the data