Contents

Title and Copyright Information

Preface

• Purpose
• Documentation Accessibility
• Audience
• How This Guide Is Organized
• New in 10g Release 1 (10.1)
• Added in Release 9.0.4
• Conventions
• Related Publications
• Contacting Oracle

1 Introduction to Warehouse Builder Transformations

• Overview
• Transforming Data with Warehouse Builder
• SQL Standards
• How SQL Works
• SQL as the Common Language for Relational Databases

2 Transformations

• Regular SQL Operators
  • Deduplicator (DISTINCT)
  • Filter (WHERE)
  • Joiner (FULL OUTER JOIN)
  • Key Lookup
  • Pivot Operator
    • Example: Pivoting Sales Data
  • Sequence (CURRVAL, NEXTVAL)
  • Set (UNION, UNION ALL, INTERSECT, MINUS)
  • Sorter (ORDER BY)
  • Splitter (Multiple Table WHERE)
  • Table Function
  • Unpivot Operator
    • Example: Unpivoting Sales Data
  • Aggregator (GROUP BY, HAVING)
    • AVG
    • COUNT
    • MAX
    • MIN
    • STDDEV
    • STDDEV_POP
    • STDDEV_SAMP
    • SUM
    • VAR_POP
    • VAR_SAMP
    • VARIANCE
  • Constant
    • SYSDATE
    • SYSTIMESTAMP
• Data Cleansing Operators
  • Name and Address
  • Match-Merge Operator
    • Example: Matching and Merging Customer Data
    • Designing Mappings with a Match-Merge Operator

3 SQL Transformations

• Introduction
  • About Transformations
  • About Oracle Transformation Libraries
    • Global Shared Library
    • Oracle Library
    • Accessing Transformation Libraries
    • Importing PL/SQL Packages
• Administrative Transformations
  • WB_ABORT
  • WB_ANALYZE_SCHEMA
  • WB_ANALYZE_TABLE
  • WB_COMPILE_PLSQL
  • WB_DISABLE_ALL_CONSTRAINTS
  • WB_DISABLE_ALL_TRIGGERS
  • WB_DISABLE_CONSTRAINT
  • WB_DISABLE_TRIGGER
  • WB_ENABLE_ALL_CONSTRAINTS
  • WB_ENABLE_ALL_TRIGGERS
  • WB_ENABLE_CONSTRAINT
  • WB_ENABLE_TRIGGER
  • WB_TRUNCATE_TABLE
• Character Transformations
  • ASCII
  • ASCIISTR
  • CHARTOROWID
  • CHR
  • CONCAT
  • CONVERT
  • INITCAP
  • INSTR / INSTRB
  • LENGTH/LENGTHB
  • LOWER
  • LPAD
  • LTRIM
  • NLSSORT
  • NLS_INITCAP
  • NLS_LOWER
  • NLS_UPPER
  • REPLACE
  • RPAD
  • RTRIM
  • SOUNDEX
  • SUBSTR
  • TO_DATE
  • TO_MULTI_BYTE
  • TO_NUMBER
  • TO_SINGLE_BYTE
  • TRANSLATE
  • TRIM
  • UPPER
  • WB.LOOKUP_CHAR
  • WB.LOOKUP_CHAR
  • WB_IS_SPACE
• Date Transformations
  • ADD_MONTHS
  • LAST_DAY
  • MONTHS_BETWEEN
  • NEW_TIME
  • NEXT_DAY
  • ROUND (date)
  • SYSDATE
  • TO_CHAR (datetime)
  • TRUNC (date)
  • WB_CAL_MONTH_NAME
  • WB_CAL_MONTH_OF_YEAR
  • WB_CAL_MONTH_SHORT_NAME
  • WB_CAL_QTR
  • WB_CAL_WEEK_OF_YEAR
  • WB_CAL_YEAR
  • WB_CAL_YEAR_NAME
  • WB_DATE_FROM_JULIAN
  • WB_DAY_NAME
  • WB_DAY_OF_MONTH
  • WB_DAY_OF_WEEK
  • WB_DAY_OF_YEAR
  • WB_DAY_SHORT_NAME
  • WB_DECADE
  • WB_HOUR12
  • WB_HOUR12MI_SS
  • WB_HOUR24
  • WB_HOUR24MI_SS
  • WB_IS_DATE
  • WB_JULIAN_FROM_DATE
  • WB_MI_SS
  • WB_WEEK_OF_MONTH
• Number Transformations
  • ABS
  • ACOS
  • ASIN
  • ATAN
  • ATAN2
  • COS
  • COSH
  • CEIL
  • EXP
  • FLOOR
  • LN
  • LOG
  • MOD
  • POWER
  • ROUND (number)
  • SIGN
  • SIN
  • SINH
  • SQRT
  • TAN
  • TANH
  • TO_CHAR (number)
  • TRUNC (number)
  • WB.LOOKUP_NUM (on a number)
  • WB.LOOKUP_NUM (on a varchar2)
  • WB_IS_NUMBER
• OLAP Transformations
  • WB_OLAP_LOAD_CUBE
  • WB_OLAP_LOAD_DIMENSION
  • WB_OLAP_LOAD_DIMENSION_GENUK
• XML Transformations
  • WB_XML_LOAD
  • WB_XML_LOAD_F
• Conversion Transformations
  • CASE
  • NVL
• Other Transformations
  • NLS_CHARSET_DECL_LEN
  • NLS_CHARSET_ID
  • NLS_CHARSET_NAME
  • UID
  • USER

A Using Slowly Changing Dimensions

• About Slowly Changing Dimensions
• Case Study Scenario
  • Source System
  • Target System
• Using Type 1 Slowly Changing Dimensions
  • Step 1: Populate the Surrogate Key
  • Step 2: Configure the Target Properties
  • Step 3: Generate Code
• Using Type 2 Slowly Changing Dimensions
  • Step 1: Detect a Match
  • Step 2: Split Join Results
  • Step 3: Determine Merge Rows
  • Step 4: Use the Expression UPDATE_DELTA_ROW
  • Step 5: Use the Expression MERGE_DELTA_ROW
  • Step 6: Populate Surrogate Keys
  • Step 7: Configure Target Properties
  • Step 8: Generate Code
• Using Type 3 Slowly Changing Dimension
  • Step 1: Detect a Match
  • Step 2: Populate Current Values
  • Step 3: Populate Previous Value Columns by Expression
  • Step 4: Populate Surrogate Keys
  • Step 5: Configure Target Properties
  • Step 6: Generate Code
• Deploying and Loading Slowly Changing Dimensions

Index