Overview
Data entering the data warehouse can be manipulated if required. This manipulation can occur at any stage, but is supported via a number of methods during the Load stage. Load tables provide options to transform data. If multiple pass transformations are required then a load table can be created from another load table, i.e. multiple load tables can be supported in the data flow path.
The options available differ depending on the type of load but in most cases the "after" transformation and post load procedure can be utilized. Specifically:
|
Database Link Load
|
During Load transformations
After Load transformations
Post Load procedure
|
|
ODBC Based Load
|
During Load transformations
After Load transformations
Post load procedure
|
|
File Based Load
|
During Load transformations
After Load transformations
Post load procedure
|
|
Script Based Load
|
During Load transformations
After Load transformations
Post load procedure
|
|
Externally Loaded
|
After Load transformations
Post Load procedure
|
The Transformation tab of a column's properties is used to define "during" and "after" load transformations. It can only be one or the other for a specific column. One column can be used to build another, so an "after" can be based on the results of a "during", if different columns are used.
Note: The "During" transformations use Source Table columns. The "After" transformations use the Load Table columns.
All 'After' transformations take place in the native SQL language of the data warehouse database. The 'During' transformations differ in terms of which language is used. This is particularly true for file based loads. Normally the 'During' transformation will occur in the native SQL language of the source database.
Database Link During Load Transformations
The 'during' load transformation allows the manipulation of column data as it enters the data warehouse.
By default, Database link loads and ODBC based loads have 'During' and 'After' transformations enabled.
When transformations are enabled, the contents of a source table/source column for each column are used as the basis of the loading statement.
If source table and source column are null, then a null is used.
If data exists in the transformation tab of a column's properties, then this transformation data is used instead of the source table/source column combination.
