les07资料参考 学习.pptVIP

The ETL Process: Transforming Data Objectives After completing this lesson, you should be able to do the following: Define transformation Identify possible staging models Identify data anomalies and eliminate them Explain the importance of quality data Describe techniques for transforming data Design the transformation process List Oracle’s enhanced features and tools that can be used to transform data Lesson Agenda Defining transformation and identifying the available staging models Identifying and eliminating data anomalies Explaining the importance of quality data Describing data transformation techniques Designing the transformation process Listing Oracle’s enhanced features and tools that can be used to transform data Transformation Transformation eliminates anomalies from operational data: Cleans and standardizes data Presents subject-oriented data Remote Staging Model Data staging area within the warehouse environment Data staging area in its own environment On-Site Staging Model Data staging area within the operational environment,possibly affecting the operational system Data Anomalies No unique key Data naming and coding anomalies Data meaning anomalies between groups Spelling and text inconsistencies Transformation Routines Cleaning data Eliminating inconsistencies Adding elements Merging data Integrating data Transforming data before load Transforming Data: Problems and Solutions Multipart keys Multiple local standards Multiple files Missing values Duplicate values Element names Element meanings Input formats Referential integrity constraints Name and address Multipart Keys Problem Multipart keys Multiple Local Standards Problem Problem: There are multiple local standards. Solution: Use the tools or filters to preprocess the data. Multiple Files Problem Problem: There is an added complexity of multiple source files. Solution: Start simple as shown in the following diagram. Missing Values Problem Solution: Ignore Wait Mark rows Extract when tim