Each table or export object column can have a transformation associated with it. For all table types, except for load tables, the transformation will be included in the generated procedure for the table. These are executed as part of the procedure update. The transformation must therefore be a valid SQL construct that can be included in a Select statement. For example, we could have a transformation of 'load_order_line.quantity * 0.125' to calculate a tax column of 12.5%. Click the Transformation tab on the Column Properties window to enter a transformation.
- Transformations added to an existing table object that has an update procedure are only put into effect when the procedure is re-generated and re-compiled.
- A common transformation can be applied to several columns via the Change Columns window.
Column transformations in Load tables are more complex, due to the unique nature of Load tables. Refer to Load Table Column Transformations for details.
Dimension View transformations are included in the Database Definition Language (DDL) that creates the view in the database. Any changes to Dimension View column transformations require the view to be dropped and recreated.
Export object column transformations are dynamically applied for file exports. If the export object is executed via a Host Script, then the script needs to be regenerated for changes to transformations to take effect. Refer to Exporting Data for details.