| Oracle® Warehouse Builder User's Guide 10g Release 2 (10.2.0.2) Part Number B28223-05 |
|
|
View PDF |
| Oracle® Warehouse Builder User's Guide 10g Release 2 (10.2.0.2) Part Number B28223-05 |
|
|
View PDF |
The Mapping Editor provides a set of pre-built mapping operators. These operators enable you to define common transformations that specify how data moves from the source to the target.
This chapter provides details on how to use operators in a mapping to transform data. Some operators have wizards that assist you in designing the mapping. And some operators allow you to launch the Expression Builder as an aide to writing SQL expressions.
This chapter includes the following topics:
For operators that require you to make numerous design decisions, Warehouse Builder provides wizards that guide you in defining the operator. Each wizard begins with a welcome page that provides an overview of the steps you must perform. And each wizard concludes with a summary page listing your selections. Use Next and Back to navigate through the wizard. To close an operator wizard, click Finish on the any of the wizard pages.
Warehouse Builder provides wizards for the following operators:
Match-Merge Operator, see Using the Match-Merge Operator
Name and Address Operator, see Using the Name and Address Operator in a Mapping
Once you become proficient with defining an operator, you may prefer to disable the wizard and use the Operator Editor instead. To launch the Operator Editor, right-click the operator on the Mapping Editor and select Open Details. The Operator Editor displays the same content as the wizard except in tab format rather than wizard pages.
Whether you are using an operator wizard or the Operator Editor, complete the following pages for each operator:
Use the General page to specify a name and optional description for the operator. By default, the wizard assigns the operator type as the name. For example, the default name for a new pivot operator is "Pivot".
Edit group information on the Groups tab.
Each group has a name, direction, and optional description. You can rename groups for most operators but cannot change group direction for any of the operators. A group can have one of these directions: Input, Output, Input/Output.
Depending on the operator, you can add and remove groups from the Groups tab. For example, you add input groups to Joiners and output groups to Splitters.
The Operator Editor displays a tab for each type of group displayed on the Groups tab. Each of these tabs displays the attribute name, data type, length, precision, scale and optional description.
Depending on the operator, you may be able to add, remove, and edit attributes. The Mapping Editor greys out properties that you cannot edit. For example, if the data type is NUMBER, you can edit the precision and scale but not the length.
Use the Input Connections page to copy and map attributes into the operator. The attributes you select become mapped members in the input group. The Available Attributes panel displays a list of all the operators in the mapping.
To complete the Input Connections page for an operator:
Select complete groups or individual attributes from the Available Attributes panel.
To search for a specific attribute or group by name, type the text in Search for and select Go. To find the next match, select Go again.
Hold the Shift key down to select multiple groups or attributes. If you want to select attributes from different groups, you must first combine the groups with a Joiner or Set operator.
Use the left to right arrow button between the two panels to move your selections to the Mapped Attributes panel.
You can use the right to left arrow to remove groups or attributes from the input connections list. Warehouse Builder removes the selection from the input group and removes the mapping line between the source operator and the current operator.
Some of the data flow operators require that you create expressions. An expression is a statement or clause that transforms data or specifies a restriction. These expressions are portions of SQL that are used inline as part of a SQL statement. Each expression belongs to a type that is determined by the role of the data flow operator. You can create expressions using Expression Builder, or by typing them into the expression field located in the operator or attribute property windows.
You can open the Expression Builder from the <Operator Name> Properties panel of the Mapping Editor for operators such as filters, joiners, and aggregators.
You can open the Expression Builder from the Attribute Properties panel of the Mapping Editor in the operators such as expressions, data generators, splitters, and constants.
To open the Expression Builder:
From the Properties panel of the operator or an attribute, click the Ellipsis button in the Expression field. Figure 26-1 shows the Attribute Properties panel.
The Expression Builder displays as shown in Figure 26-2.
Create an expression by:
Typing text into the Expression field.
Dragging items from the Inputs and Transformations tabs on the left panel and dropping them into the Expression field on the right.
Double clicking on items from the Inputs and Transformations tabs on the left panel.
Clicking arithmetic operator buttons available under the Expression field.
Click Validate.
This verifies the accuracy of the Expression syntax.
Click OK to save the expression and close the Expression Builder.
The Expression Builder contains the following parts:
In the left panel, the navigation tree displays two tabs:
Inputs Tab: A list of input parameters.
Transformations Tab: A list of predefined functions and procedures located in the Oracle Library, the Global Shared Library, and a custom Transformation Library. For more information about transformations, see Chapter 9, "Using Oracle Warehouse Builder Transformations".
Expression Field: At the top of the right panel is the Expression field. Use this field to type and edit expressions.
Arithmetic Operator Buttons: Below the Expression field are buttons for arithmetic operators. Use these buttons to build an expression without typing. The arithmetic operators available vary by the type of data flow operator that is active.
Others: A drop-down list of available SQL clauses that are appropriate for the active expression type.
Beginning in Oracle9i, the CASE function is recommended over the DECODE function because the CASE function generates both SQL and PL/SQL while DECODE is limited to SQL. If you use the DECODE function in an expression, Warehouse Builder promotes it to CASE where appropriate during code generation. This enables you to deploy the DECODE functionality in all operating modes (such as setbased or rowbased) and transparently across Oracle Database releases (8.1, 9.0 and higher).
For example, Warehouse Builder converts the function
DECODE (T1.A, 1, 'ABC', 2, 'DEF', 3, 'GHI', 'JKL')
to the following:
CASE T1.A WHEN 1 THEN 'ABC' WHEN 2 THEN 'DEF' WHEN 3 THEN 'GHI' ELSE 'JKL'
Validate Button: Use this button to validate the current expression in the Expression Builder. Validation ensures that all mapping objects referred to by the expression have associated repository objects. The expressions you create with the Expression Builder are limited to the operator inputs and to any transformations available in a project. This limitation protects the expression from becoming invalid because of changes external to the operator. If the deployment database is different from the design repository, it may not accept the expression. If this happens, the expression may be valid but incorrect against the database. In this case, expression errors can only be found at deployment time.
Validation Results Field: At the bottom of the right panel is the Validation Results field. After you select the Validate button to the right of this field, this field displays the validation results.
The Aggregator operator calculates data aggregations, such as summations and averages, on the input data. It provides an output row set that contains the aggregated data.
The Aggregator operator has one input group and one output group. For the output group, define a GROUP BY clause that specifies the attributes over which the aggregation is performed. You can optionally specify a HAVING clause that restricts the aggregated data. Each attribute in the output group has the same cardinality. The number of rows in the output row set is less than or equal to the number of input rows.
You can use a single aggregator operator to perform multiple aggregations. Although you can specify a different aggregation function for each attribute in the output group of an aggregator, each aggregator supports only one GROUP BY and one HAVING clause.
Figure 26-3 shows a mapping that uses the Aggregator operator to aggregate the total sales over channels and products. Use the Expression property of the output attribute to specify that the aggregate function to be applied to the attribute TOTAL_SALES is SUM. Use the Group By property of the Aggregator operator to specify that the sales are aggregated over channel ID and product ID. The output of the Aggregator operator is mapped to the target table SALES_FACT.
Figure 26-3 Aggregator Operator in a Mapping
To define an Aggregator operator in a mapping:
Drag and drop an Aggregator operator onto the Mapping Editor canvas.
On the canvas, connect source attributes to the input group of the Aggregator operator.
Right-click the Aggregator operator and select Open Details.
Warehouse Builder displays the Aggregator Editor.
On the Output Attributes tab, click Add to add an output attribute.
Warehouse Builder adds an output attribute with the data type NUMBER. You can change both the name and the data type of this attribute.
In the example displayed in Figure 26-3, you add an output attribute and rename it to TOTAL_SALES.
Click OK to close the Aggregator Editor.
Define expressions for each output attribute by using the Properties Inspector window of the attribute. For detailed instructions, see "Aggregate Function Expression".
In the example displayed in Figure 26-3, you define the expression as SUM(amount_sold).
Define a Group By clause and an optional Having clause for the operator. For detailed instructions, see "Group By Clause" and "Having Clause".
Map the attributes in the output group of the Aggregator operator to the input group of the target.
The Group By clause defines how to group the incoming row set to return a single summary row for each group. An ordered list of attributes in the input group specifies how this grouping is performed. The default GROUP BY clause is NONE.
To define the Group By Clause:
Select the Aggregator operator on the Mapping Editor canvas.
The Aggregator Properties panel displays the properties of the Aggregator operator.
Click the Ellipsis button to the right of the Group By Clause property.
The Group By Clause dialog is displayed.
Move the attributes from the Available Attributes list to the GROUP BY Attributes list.
In the example displayed in Figure 26-3, move the input attributes channel_id and prod_id from the Available Attributes list to the Group By Attributes list.
Click OK.
The Having clause is a boolean condition that restricts the groups of rows returned in the output group to those groups for which this condition is true. If this clause is not specified, all summary rows for all groups are returned in the output group.
To define the Having Clause:
Select the Aggregator operator on the mapping canvas.
The Aggregator Properties panel displays the properties of the Aggregator operator.
Click the Ellipsis button to the right of the Having Clause property.
The Expression Builder dialog for the Having Clause displays as shown in Figure 26-4.
Create an expression for the Having Clause of the Aggregator operator. For example Figure 26-4 shows a sample Having Clause expression.
Click OK to close the Expression Builder.
Map the attributes you edited from the output group of the Aggregator operator to the attributes in the target.
The Expression property of an attribute defines the aggregation functions to be performed on the attribute. For each ungrouped output attribute, select whether the aggregation expression should be a DISTINCT or ALL result. ALL is the default setting. For example,
A DISTINCT result removes all duplicate rows before the average is calculated.
An ALL result returns an average value on all rows.
If no aggregation function is necessary, specify NONE for the function. Specifying NONE on the attribute aggregation automatically adds the attribute to the resulting GROUP BY function.
To define expressions for output attributes:
In the Aggregator operator on the mapping canvas, select the output attribute for which you want to define an aggregate function.
The Attribute Properties panel displays the properties of the selected output attribute.
Click the Ellipsis button to the right of the Expression property.
The Expression dialog displays as shown in Figure 26-5.
Select an aggregate function from the Function drop-down list.
The aggregate functions you can select in Warehouse Builder are as follows: AVG, COUNT, MAX, MIN, NONE, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, and VARIANCE.
In the example displayed in Figure 26-3, you select SUM as the aggregate function.
Select either ALL or DISTINCT as the aggregation expression.
Select the attribute that should be aggregated from the Attribute drop-down list.
In the example displayed in Figure 26-3, you select the attribute amount_sold from the drop-down list.
Click OK.
Anydata Cast operator enables you to convert an object of type Sys.AnyData to either a primary type or to a user defined type. The Anydata Cast operator accepts an Anydata attribute as a source and transforms the object to the desired type.
The Anydata Cast operator is used with user defined data types and primitive data types. The cast operator acts as a filter. The number of attributes in the output group is n+1 where n is the number of attributes in the input group. This operator has one input group and one output group. The input group is editable. The output group is not editable. In an output group, you can only rename the attributes and change the data type of only the cast target. You cannot change the data type of any other output group attribute.
You can connect attributes to the input group. Each output group gets a copy of the input group attributes, including the Anydata attributes. You must choose an Anydata attribute of the input group as the source of the Cast operation.
If you change the data type to which you are going to cast the Anydata attribute, then you must:
Edit the output group attribute that is the target of the Cast operation
Change the data type of the attribute.
Because the Cast operator is unbound, it will not support any reconciliation operations.
To define a Anydata Cast operator in a mapping:
Drop an Anydata Cast operator onto the Mapping Editor canvas.
The AnyData Cast dialog is displayed. The tree inside the dialog has one parent node that will open to display the primary data types (other than Anydata). Each of the other parent nodes will correspond to the modules.
Select the target type for casting and click Finish.
Right-click the ANYDATA CAST operator and select Open Details.
Warehouse Builder displays the ANYDATA_CAST Editor.
On the Input Attributes tab, click Add and specify the attribute name, data type, and other properties.
Click OK to close the Operator Editor.
Map the attributes of the output group of the Anydata Cast operator to the target.
The Deduplicator enables you to remove duplicate data in a source by placing a DISTINCT clause in the select code represented by the mapping.
For example, when you load data from a source table into a dimension, the higher levels within a dimension may be duplicated in the source. Figure 26-7 displays a mapping that uses the Deduplicator operator to remove duplicate values in the source while loading data into the PRODUCTS dimension. The source table contains duplicate values for category ID because more than one products may belong to the same category. The Deduplicator operator removes these duplicates and loads distinct values of category ID into the PRODUCTS dimension.
All rows that are required by the target must pass through the deduplicator. No row set can bypass the deduplicator and hit the target directly.
To remove duplicates:
Drop the Deduplicator operator onto the Mapping Editor canvas.
Connect the attributes from the source operator to the input/output group of the Deduplicator operator.
Connect the attributes from the Deduplicator operator group to the attributes of the target operator.
Use the Expression operator to write SQL expressions that define non-procedural algorithms for one output parameter of the operator.
The expression text can contain combinations of input parameter names, variable names, and library functions. Use the Expression operator to transform the column value data of rows within a row set using SQL-type expressions, while preserving the cardinality of the input row set. To create these expressions, open the Attribute properties window for the output attribute and then open the Expression Builder.
By default, the Expression operator contains one input group and one output group.
Figure 26-8 shows a mapping that uses the Expression operator. The transaction table ORACLE_ORDERS contains order details such as product ID, unit price, and quantity sold. The ORDERS_FACT table contains an aggregation of the total sales amount across channels, products, and orders. The Expression operator is used to compute the amount of sale for each product by multiplying the unit price by the quantity sold. The Aggregator operator aggregates the sale amounts over channel code, product ID, and order ID before loading the target table.
Figure 26-8 Expression Operator in a Mapping
Do not use the expression operator to write aggregation functions. Use the Aggregator operator. See "Aggregator Operator".
To define an Expression operator in a mapping:
Drag and drop an Expression operator onto the Mapping Editor canvas.
Right-click the Expression operator and select Open Details.
Warehouse Builder displays the Expression Editor.
On the Output Attributes tab, click Add and specify the attribute name, data type, and other properties.
Click OK to close the Operator Editor.
From the Expression operator, select the output attribute.
The Attribute Properties panel window displays the properties of the selected output attribute.
Click the Ellipsis button to the right of the Expression field.
The Expression Builder is displayed. Define an expression in the Expression Builder.
For code generation, the input attributes are replaced by the input attribute names in the expression template.
Connect the Expression output attribute to the appropriate target attribute.
You can conditionally filter out rows using the Filter operator.
You connect a source operator to the Filter operator, apply a filter condition, and send a subset of rows to the next operator. The Filter operator filters data from a source to a target by placing a WHERE clause in the code represented by the mapping. You specify the filter condition using the Expression Builder. The filter condition can be based on all supported data types and can contain constants.
A Filter operator has only one input/output group that can be connected to both a source and target row set. The resulting row set is a filtered subset of the source row set based on a boolean filter condition expression. All rows that are required at the target must pass through the filter operator. No row set can bypass the filter and hit the target directly.
For a mapping that contains a Filter operator, Warehouse Builder generates code that displays the filter condition expression as a WHERE clause for set-based view mode. The filter input names in the original filter condition are replaced by actual column names from the source table, qualified by the source table alias.
Figure 26-9 shows the mapping that uses the Filter operator to move selected data to the target table. The ORACLE_ORDERS table contains orders data. Use the Filter Condition property of the Filter operator to move only the booked orders which were last updated on the current system date into the ORDERS table.
To define a Filter operator in a mapping:
Drag and drop the Filter operator onto the Mapping Editor canvas.
Connect source attributes to the input/output group of the Filter operator.
Select the Filter operator header.
The Filter Properties panel of the Mapping Editor displays the properties of the Filter operator.
Click the Ellipsis button the right of the Filter Condition property.
Warehouse Builder displays the Expression Builder dialog for the filter condition.
Define a filter condition expression using the Expression Builder.
Click OK to close the Expression Builder.
Connect the Filter operator outputs to the input/output group in the target.
You use the Joiner operator to join multiple row sets from different sources with different cardinalities, and produce a single output row set.
The Joiner operator uses a boolean condition that relates column values in each source row set to at least one other row set. The Joiner operator results in a WHERE clause in the generated SQL query. Warehouse Builder supports inner joins, outer joins, self joins, equijoins, and non-equijoins. When run on Oracle9i, Warehouse Builder supports full outer joins. For more information on joins refer the Oracle SQL Reference.
Note:
Operators placed between data sources and a Joiner can generate complex SQL or PL/SQL.
If the input row sets are related through foreign keys, that relationship is used to form a default join condition. You can use this default condition or you can modify it. If the sources are not related through foreign keys, then you must define a join condition.
If two tables in a join query do not have a join condition specified, Warehouse Builder returns the Cartesian product of the two tables and combines each row of one table with each row of the other table.
If the default foreign keys result in duplicate WHERE clauses, the Joiner operator will remove the duplicate clauses. This can happen if the join condition references several foreign keys. For example, if table T1 has a foreign key FK1 point to unique key UK1 in table T2 and table T2 has a foreign key FK2 pointing to unique key UK2 in T1, the resulting join condition
T1.A = T2.A AND T1.B = T2.B /*All instances of FK1 -> UK1 are reduced to one WHERE clause*/ AND T2.B = T1.B AND T2.C = T1.C /*All instances of FK2 -> UK2 are reduced to one E-Business Suite clause*/
is generated by the Joiner operator as
T2.A = T2.A AND T1.B = T2.B AND T2.C = T1.C
If you define a join condition before you map attributes to the input group of the Joiner operator, the generated code treats the join condition as a literal. Since the attributes are not yet mapped to the Joiner operator, the code generator does not recognize these attributes. To avoid this problem, it is recommended that you first map the input groups of the Joiner operator and then define the join condition.
The join condition is defined in a PL/SQL context. For SAP sources, Warehouse Builder can generate ABAP code by interpreting the PL/SQL join condition in the ABAP context. ABAP can only join over defined foreign key relationships.
Figure 26-10 shows a mapping that contains a Joiner operator. The two source tables ORACLE_ORDERS and ORACLE_ORDER_LINES are joined to combine the data from these tables into one table. The output of the Joiner operator is passed to the target table DAILY_ORDERS.
To define a Joiner operator in a mapping:
Drag and drop the Joiner operator onto the Mapping Editor canvas.
Connect an output group from the first source to the Joiner input group INGRP1.
The output attributes are created with data types matching the corresponding input data types.
Connect a group from the second source operator to the INGRP2 group of the Joiner operator.
Select the Joiner operator header.
The Joiner Properties panel of the Mapping Editor displays the properties of the Joiner operator.
Click the Ellipsis button to the right of the Join Condition property.
The Expression Builder dialog is displayed.
Define the join condition.
Click OK to close the Expression Builder.
Map the attributes of the output group of the Joiner operator to the target.
Do not include aggregation functions in a join condition.
A Joiner can have an unlimited number of input groups but only one output group.
The order of input groups in a joiner is used as the join order. The major difference between ANSI join and an Oracle join is that ANSI join must clearly specify join order. An Oracle join does not require it.
SELECT ...
FROM T1 FULL OUTER JOIN T2 ON (T1.A=T2.A)
JOIN T3 ON (T2.A=T3.A);
If you create input groups in another order, such as T1, T3, T2. Warehouse Builder will generate the following:
SELECT ...
FROM T1 JOIN T3 ON (1=1)
JOIN T2 ON (T1.A=T2.A and T2.A=T3.A);
When T1 and T3 are joined, there is no join condition specified. Warehouse Builder fills in a condition 1=1 (essentially a boolean true) and the two conditions you specified are used to join T2.
The filter condition is applied after join. For example, consider the following join:
Input1.c --- + Input2.c --- +---> Joiner Input3.c --- +
with the following conditions:
Condition 1: Input1.c (+) = Input2.c (+)
Condition 2: Input2.c = Input3.c
Condition 3: Input1.c is null
The first two conditions are true joins while the third is a filter condition. If ANSI code is to be generated, Warehouse Builder interprets the statement as
select ... from Input1 full outer join Input2 on (Input1.c = Input2.c) join Input3 on (Input2.c = Input3.c) WHERE Input1.c is not null;
If your target warehouse is based on Oracle9i or a later version, the Warehouse Builder Joiner operator also supports the full outer join. To specify a full outer join condition, you must place the (+) sign on both sides of a relational operator. The relational operator is not restricted to equality. You can also use other operators such as, >, <, !=, >=, <= .
T1.A (+) = T2.B (+)
The results of the full outer join are as follows:
Rows from sources T1 and T2 that satisfy the condition T1.A = T2.B.
Rows from source T1 that do not satisfy the condition. Columns corresponding with T2 are populated with nulls.
Rows from source T2 that do not satisfy the condition. Columns corresponding with T1 are populated with nulls.
When using the Oracle SQL syntax for partial outer join such as T1.A = T2.B (+), if you place a (+) sign on both sides of the relational operator, it is invalid Oracle SQL syntax. However, Warehouse Builder translates any condition with the double (+) sign into ANSI SQL syntax. For example,
SELECT ... FROM T1 FULL OUTER JOIN T2 ON (T1.A = T2.B);
When using full outer join, keep in mind the following:
Do not specify a full outer join condition for versions earlier than Oracle9i.
The ANSI join syntax is generated only if you specify a full outer join condition in the joiner. Otherwise, the following Oracle proprietary join syntax is generated:
SELECT ... FROM T1, T2 WHERE T1.A = T2.B;
You can specify both full outer join and join conditions in the same joiner. However, if both conditions are specified for the same sources, the stronger join type is used for generating code. For example, if you specify:
T1.A(+) = T2.A(+) and T1.B = T2.B
Warehouse Builder will generate a join statement instead of a full outer join because T1.B = T2.B is stronger than the full outer join condition between T1 and T2.
You cannot specify a full outer join and partial outer join condition in the same joiner. If you specify a full outer join, then you cannot specify a partial outer join anywhere in the join condition. For example, T1.A (+) = T2.A (+) and T2.B = T3.B (+) is not valid.
In an equijoin, key values from the two tables must match. In a full outer join, key values are matched and nulls are created in the resulting table for key values that cannot be matched. A left or a right outer join retains all rows in the specified table.
In Oracle8i, you create an outer join in SQL using the join condition variable (+):
SELECT ... FROM A, B WHERE A.key = B.key (+);
This example is a left outer join. Rows from table A are included in the joined result even though no rows from table B match them. To create a full outer join in Oracle8i, you must use multiple SQL statements.
The Expression Builder allows the following syntax for a full outer join:
TABLE1.COL1 (+) = TABLE2.COL2 (+)
This structure is not supported by Oracle8i. Oracle Database is ANSI SQL 1999 compliant. The ANSI SQL 1999 standard includes a solution syntax for performing full outer joins. The code generator translates the preceding expression into an ANSI SQL 1999 full outer join statement, similar to:
SELECT ... FROM table1 FULL OUTER JOIN table2 ON (table1.col1 = table2.col2)
Because the full outer join statement complies to ANSI SQL 1999, it is only valid if the generated code is deployed to an Oracle9i database. Specifying a full outer join to an Oracle8i database results in a validation error.
A full outer join and a partial outer join can be used together in a single SQL statement, but it must in an AND or an AND/OR condition. If a full outer join and partial outer join are used in the OR condition, an unexpected AND condition will result. For example,
SELECT ... FROM table1 FULL OUTER JOIN table2 ON (A = B or C = D)
is evaluated by Oracle Server as A (+) = B (+) AND C = D.
To use a full outer join in a mapping:
Follow steps one through four for adding a Joiner operator.
Click the Ellipsis button to the right of the Join Condition property to define an expression for the full outer join using the Expression Builder.
Click OK to close the Expression Builder.
Use the Key Lookup operator to lookup data from a table, view, cube, or dimension. For example, use the Key Lookup operator when you define a mapping that loads a cube or when you define surrogate keys on the dimension. For more information about surrogate identifiers, see "Defining Levels".
The key that you look up can be any unique value. It need not be a primary or unique key, as defined in an RDBMS. The Key Lookup operator reads data from a lookup table using the key input you supply and finds the matching row. This operator returns a row for each input key. You can have multiple Key Lookup operators in the same mapping.
The output of the Key Lookup operator corresponds to the columns in the lookup object. To ensure that only a single lookup row is found for each key input row, use keys in your match condition.
Each output attribute for the key lookup has a property called DEFAULT VALUE. The DEFAULT VALUE property is used instead of NULL in the outgoing row set if no value is found in the lookup table for an input value. The generated code uses the NVL function. The Key Lookup always results in an outer-join statement.
The table, view, or dimension from which the data is being looked up is bound to the Key Lookup operator. You can synchronize a Key Lookup operator with the repository object to which it is bound. But you cannot synchronize the repository object with the Key Lookup operator. For more information on synchronizing operators, refer "Synchronizing Operators based on Repository Objects".
Figure 26-11 shows a mapping that is used to load a cube. Data from four source tables is joined using a Joiner operator. But the data in the source tables only contains a channel name. To load the cube, we need the value of the surrogate identifier. A key lookup operator is used to lookup the surrogate identifier of the CHANNELS dimension and then load this value into the cube.
You have the following options for using a Key Lookup operator:
Define a new Key Lookup operator: Drag a Key Lookup operator from the Palette onto the mapping. The Mapping Editor launches a wizard.
Edit an existing Key Lookup operator: Right-click the Key Lookup operator and select Open Details.
Whether you are using the operator wizard or the Operator Editor, complete the following pages:
Use the General page to specify a name and optional description for the key lookup operator. By default, the wizard names the operator "Key_Lookup".
Use the Groups page to specify one input and one output group.In a Key Lookup operator, the input group represents the data from the source that is contained across multiple attributes. The output group represents that data transformed into rows.You can rename and add descriptions to the input and output groups. Since each Key Lookup operator must have exactly one input and one output group, the wizard prevents you from adding or removing groups or changing group direction.
Use the Input Connections page to copy and map attributes into the Key Lookup operator. The attributes you select are used to perform a lookup on the lookup object and find matching rows. The left side of the page displays a list of all the operators in the mapping. Figure 26-12 shows an attribute from the table ORACLE_PRODUCTS table selected as input for the key lookup operator.
Figure 26-12 Input Connections Page of the Key Lookup Operator
To complete the Input Connections page for a Key Lookup operator:
Select complete groups or individual attributes from the left panel.
To search for a specific attribute or group by name, type the text in Search for and select Go. To find the next match, select Go again. You can select multiple attributes by pressing the Shift key.
Use the right arrow button in the middle of the page to move your selections to the right side of the wizard page.
Use the left arrow to remove groups or attributes from the input connections list. Warehouse Builder removes the selection from the input group and removes the mapping line between the source operator and the pivot operator.
Use the Lookup page to provide details about the object on which the lookup is being performed. This object is referred to as the lookup result. You can perform a lookup on any table, view, or dimension that belongs to the current project. Figure 26-13 displays the Lookup page for the mapping shown in Figure 26-11.
Figure 26-13 Lookup Page of the Key Lookup Operator
To provide the lookup details, select values for the following:
Object that has the lookup result
Use the Select the object which has the lookup result drop-down list to select the object on which you want to perform the lookup. This drop-down list displays all the modules in the current project. Expand a module node to display the objects in the module. Select the object that contains the lookup result from this list.
Level that has the lookup result
When you select a dimension as the lookup result, you must specify which level within the dimension contains the lookup result. The Select the level which has the lookup result drop-down list displays the levels in the selected dimension. Select the dimension level that contains the lookup result.
Lookup Condition
Specify the condition used to match the input attributes with the records in the lookup result. The Lookup Table key drop-down list displays the unique constraints in the lookup result. The Input Attribute drop-down list displays the attributes you selected on the Input Connections page. Use these drop-down lists to specify the attributes used to match records.
If you select a dimension level for the lookup, the options displayed are the surrogate and business identifier of the dimension level and the primary key of the database table that stores the dimension data.
Use this page only if you selected a Type 2 SCD as the lookup result on the Lookup page. When the lookup result is a Type 2 SCD, you must specify which version of a particular record is to be used as a lookup. The options you can choose are as follows:
Use the most current record
This option returns the current record that corresponds to the attribute being looked up using the lookup condition. The current record is the one with the latest timestamp.
Specify the historic date as a constant value
This option returns the record that contains the constant value that is specified using the Date and Time drop-down lists.
Choose an input attribute that holds the historic value
This option enables you return records that pertain to a date and time that is contained in one of the input attributes. Use the Input Attribute drop-down list to select the attribute that contains the historic value.
Use the No-match Rows page to indicate the action to be taken when there are no rows that satisfy the lookup condition specified on the Lookup page. Select one of the following options:
Return no row
This option does not return any row when no row in the lookup result satisfies the matching condition.
Return a row with the following default values
This option returns a row that contains default values when the lookup condition is not satisfied by the lookup result. Use the table below this option to specify the default values for each lookup column.
The pivot operator enables you to transform a single row of attributes into multiple rows.
Use this operator in a mapping when you want to transform data that is contained across attributes instead of rows. This situation can arise when you extract data from non-relational data sources such as data in a crosstab format.
The external table SALES_DAT, shown in Figure 26-14, contains data from a flat file. There is a row for each sales representative and separate columns for each month. For more information on external tables, see "Using External Tables".
Table 26-1 shows a sample of the data after Warehouse Builder performs a pivot operation. The data that was formerly contained across multiple columns (M1, M2, M3...) is now contained in a single attribute (Monthly_Sales). A single ID row in SALES_DAT corresponds to 12 rows in pivoted data.
Table 26-1 Pivoted Data
| REP | MONTH | MONTHLY_SALES | REGION |
|---|---|---|---|
|
0675 |
Jan |
10.5 |
4 |
|
0675 |
Feb |
11.4 |
4 |
|
0675 |
Mar |
9.5 |
4 |
|
0675 |
Apr |
8.7 |
4 |
|
0675 |
May |
7.4 |
4 |
|
0675 |
Jun |
7.5 |
4 |
|
0675 |
Jul |
7.8 |
4 |
|
0675 |
Aug |
9.7 |
4 |
|
0675 |
Sep |
NULL |
4 |
|
0675 |
Oct |
NULL |
4 |
|
0675 |
Nov |
NULL |
4 |
|
0675 |
Dec |
NULL |
4 |
To perform the pivot transformation in this example, create a mapping like the one shown in Figure 26-15.
In this mapping, Warehouse Builder reads the data from the external table once, pivots the data, aggregates the data, and writes it to a target in set based mode. It is not necessary to load the data to a target directly after pivoting it. You can use the pivot operator in a series of operators before and after directing data into the target operator. You can place operators such as filter, joiner, and set operation before the pivot operator. Since pivoted data in Warehouse Builder is not a row-by-row operation, you can also execute the mapping in set based mode.
In the pivot operator, the row locator is an output attribute that you create to correspond to the repeated set of data from the source. When you use the pivot operator, Warehouse Builder transforms a single input attribute into multiple rows and generates values for a row locator. In this example, since the source contains attributes for each month, you can create an output attribute named 'MONTH' and designate it as the row locator. Each row from SALES_DAT then yields 12 rows of pivoted data in the output.
Table 26-2 shows the data from the first row from SALES_DAT after Warehouse Builder pivots the data with 'MONTH' as the row indicator.
You have the following options for using a pivot operator:
Define a new pivot operator: Use the Pivot Wizard to add a new pivot operator to a mapping. Drag a pivot operator from the Palette onto the mapping. The Mapping Editor launches the Pivot Wizard.
Edit an existing pivot operator: Use the Pivot Editor to edit a pivot operator you previously created. Right-click the operator and select Open Details. The Mapping Editor opens the Pivot Editor.
Whether you are using the Pivot Wizard or the Pivot Editor, complete the following pages:
Use the General page to specify a name and optional description for the pivot operator. By default, the wizard names the operator "Pivot".
Use the Groups page to specify one input and one output group.
In a pivot operator, the input group represents the data from the source that is contained across multiple attributes. The output group represents that data transformed into rows.
You can rename and add descriptions to the input and output groups. Since each pivot operator must have exactly one input and one output group, the wizard prevents you from adding or removing groups or changing group direction.
Use the Input Connections page to copy and map attributes into the pivot operator. The attributes you select become mapped to the pivot input group. The left side of the page displays a list of all the operators in the mapping. Figure 26-16 shows a group from the external table SALES_DAT selected as input for the pivot operator.
Figure 26-16 Pivot Operator Input Connections Tab
To complete the Input Connections page for a Pivot operator:
Select complete groups or individual attributes from the left panel.
To search for a specific attribute or group by name, type the text in Search for and select Go. To find the next match, select Go again.
Press the Shift key to select multiple attributes. If you want to select attributes from different groups, you must first combine the groups with a Joiner or Set operator.
Use the right arrow button in the middle of the page to move your selections to the right side of the wizard page.
Use the left arrow to remove groups or attributes from the input connections list. Warehouse Builder removes the selection from the input group and removes the mapping line between the source operator and the pivot operator. Figure 26-17 shows a group from SALES_DAT copied and mapped into the PIVOTSALES operator.
Figure 26-17 Attributes Copied and Mapped into Pivot In Group
Use the Input Attributes page to modify the attributes you selected in the Input Connections tab or wizard page.
You can perform the following tasks from the Input Attributes page:
Add attributes: Use the Add button to add input attributes.
Change attribute properties: You can change the attribute name, data type, length, precision, and scale.
Add an optional description: Type a description for the input attributes.
Designate attribute keys: As an option, use the Key check box to indicate an attribute that uniquely identifies the input group.
Use the Output Attributes page to create the output attributes for the pivot operator. If you designated any input attributes as keys on the Input Attributes tab or wizard page, Warehouse Builder displays those input attributes as output attributes that you cannot edit or delete. Figure 26-18 displays the output attributes with MONTH selected as the row locator.
You can perform the following tasks from the pivot Output Attributes Page:
Change attribute properties: Except for attributes you designated as keys on the previous tab or wizard page, you can change the attribute name, data type, length, precision, and scale.
Add an optional description: Type a description for the output attributes.
Designate a row locator: Although you are not required to designate a row locator for the pivot operator, it is recommended. When you identify the row locator on the Output Attributes page or tab, it is easier for you to match your output data to the input data.
In the pivot operator, the row locator is an output attribute that corresponds to the repeated set of data from the source. For example, if the source data contains separate attributes for each month, create an output attribute 'MONTH' and designate it as the row locator.
Use the Pivot Transform page to write expressions for each output attribute.
By default, Warehouse Builder displays two rows. Use Add to specify how many rows of output you want from a single row in the source. For example, if your source contains an attribute for each quarter in a year, you can specify 4 rows of output for each row in the source. If the source data contains an attribute for each month in the year, you can specify 12 rows of output for each row in the source.
Figure 26-19 shows the Pivot Transform tab with the pivot expressions defined for a source with an attribute for each month.
Write pivot expressions based on the following types of output:
Row locator: Specify a name for each row where the name is a value you want to load into the table. For example, if the row locator is 'MONTH', type 'Jan' for the first row.
Pivoted output data: Select the appropriate expression from the list box. For example, for the row you define as 'Jan', select the expression that returns the set of values for January.
Attributes previously specified as keys: Warehouse Builder defines the expression for you.
Unnecessary data: If the Pivot Transform page contains data that you do not want as output, use the expression 'NULL'. Warehouse Builder outputs a repeated set of rows with no data for attributes you define as 'NULL'.
When using the wizard to create a new pivot operator, click Finish when you want to close the wizard. The Mapping Editor displays the operator you defined.
When using the Pivot Editor to edit an existing pivot operator, click OK when you have finished editing the operator. The Mapping Editor updates the operator with the changes you made.
Use a Post-Mapping Process operator to define a procedure to be executed after running a PL/SQL mapping. For example, you can use a Post-Mapping Process operator to reenable and build indexes after a mapping completes successfully and loads data into the target.
The Post-Mapping Process operator calls a function or procedure that is defined in Warehouse Builder after the mapping is executed. The output parameter group provides the connection point for the returned value (if implemented through a function) and the output parameters of the function or procedure. There are no restrictions on the connections of these output attributes
The Post-Mapping Process operator contains groups corresponding to the number and direction of the parameters associated with the selected PL/SQL procedure or function. This list of groups and attributes can only be modified through reconciliation.
You can only define one Post-Mapping Process operator for a mapping. If you want to run more than one procedure after a mapping, you must wrap the procedures into one procedure.
You can map constants, data generators, mapping input parameters, and output from a Pre-Mapping Process into a Post-Mapping Process operator. The Post-Mapping Process operator is not valid for an SQL*Loader mapping.
After you add a Post-Mapping Process operator to the Mapping Editor, use the operator properties dialog to specify run conditions in which to execute the process.
To use a Post-Mapping Process operator in a mapping:
Drag and drop a Post-Mapping Process operator onto the Mapping Editor canvas.
Warehouse Builder displays the Add Post-Mapping Process dialog.
Use the Add Post-Mapping Process dialog to select or create a transformation. For more information on how to use the Add Post-Mapping Process dialog, see "Adding Operators that Bind to Repository Objects".
Connect the output attribute of a source operator to the input/output group of the Post-Mapping Process operator.
Set the run conditions for the operator.
To set run conditions for a Post-Mapping Process operator:
From the mapping canvas, select a Post-Mapping Process operator.
The Post-Mapping Process Properties panel displays the properties of the Post-Mapping Process operator.
Click Post-Mapping Process Run Condition and select one of the following run conditions:
Always: The process runs regardless of errors from the mapping.
On Success: The process runs only if the mapping completes without errors.
On Error: The process runs only if the mapping completes with errors exceeding the number of allowed errors set for the mapping.
On Warning: The process runs only if the mapping completes with errors that are less than the number of allowed errors set for the mapping.
If you select On Error or On Warning and the mapping runs in row based mode, you must verify the Maximum Number of Errors set for the mapping. To view the number of allowed errors, right-click the mapping in the Project Explorer, select Configure, and expand Runtime Parameters.
Use a Pre-Mapping Process operator to define a procedure to be executed before running a mapping.
For example, you can use a Pre-Mapping Process operator to truncate tables in a staging area before running a mapping that loads tables to that staging area. You can also use a Pre-Mapping Process operator to disable indexes before running a mapping that loads data to a target. You can then use a Post-Mapping Process operator to reenable and build the indexes after running the mapping that loads data to the target.
The Pre-Mapping Process operator calls a function or procedure whose metadata is defined in Warehouse Builder prior to executing a mapping. The output parameter group provides the connection point for the returned value (if implemented with a function) and the output parameters of the function or procedure. There are no restrictions on the connections of these output attributes.
When you drop a Pre-Mapping Process operator onto the Mapping Editor canvas, a dialog opens displaying the available libraries, categories, functions, and procedures. After you select a function or procedure from the tree, the operator displays with predefined input and output parameters.
The Pre-Mapping Process operator contains groups corresponding to the number and direction of the parameters associated with the selected PL/SQL procedure or function.
A mapping can only contain one Pre-Mapping Process operator. Only constants, mapping input parameters, and output from a Pre-Mapping Process can be mapped into a Post-Mapping Process operator.
After you add a Pre-Mapping Process operator to the Mapping Editor, use the operator property dialog to specify conditions in which to execute the mapping.
To use a Pre-Mapping Process operator in a mapping:
Drag and drop a Pre-Mapping Process operator onto the Mapping Editor canvas.
The Add Pre-Mapping Process dialog is displayed.
Use the Add Pre-Mapping Process dialog to select or create a transformation. For more information on how to use this dialog, see "Adding Operators that Bind to Repository Objects".
Connect the output attribute of the Pre-Mapping Process operator to the input group of a target operator.
Set the run conditions for the operator.
To set run conditions for a mapping with a Pre-Mapping Process operator:
In the mapping canvas, select the Pre-Mapping Process operator.
The Pre-Mapping Process Properties panel displays the properties of the Pre-Mapping Process operator.
Click Mapping Run Condition and select one of the following run conditions:
Always: Warehouse Builder runs the mapping after the process completes, regardless of the errors.
On Success: Warehouse Builder runs the mapping only if the process completes without errors.
On Error: Warehouse Builder runs the mapping only if the process completes with errors.
Set operations combine the results of two component queries into a single result.
While a joiner combines separate rows into one row, set operators combine all data rows in their universal row. In set operators, although the data is added to one output, the column lists are not mixed together to form one combined column list.
The Set Operation operator enables you to use following set operations in a mapping:
By default, the Set Operation operator contains two input groups and one output group. You can add input groups by using the operator editor. The number of attributes in the output group matches the number of attributes in the input group containing the most number of attributes.
To use the Set Operation operator, all sets must have the same number of attributes and the data types of corresponding attributes must match. Corresponding attributes are determined by the order of the attributes within an input group. For example, attribute 1 in input group 1 corresponds to attribute 1 in input group 2.
You must apply the set operation in top-down order. The order of the input groups determines the execution order of the set operation. This order only affects the minus operation. For example, A minus B is not the same as B minus A. The order of the attributes within the first input group determines the structure of a set. For example, {empno, ename} is not the same as {ename, empno}.
Figure 26-20 shows a mapping that uses the Set Operation operator. The data from the two source tables is combined using the Intersect set operation. The output of this operation is mapped to the target. The target table only contains the rows that are common to both the input tables.
Figure 26-20 Set Operation Operator in a Mapping
To use the Set Operation operator in a mapping:
Drag and drop a Set Operation operator onto the Mapping Editor canvas.
Connect source attributes to the Set Operation operator groups.
Select the Set operator header.
The Set Operation Properties panel displays the properties of the Set operator.
Click the drop-down list on the Set Operation property and select an operation from the drop-down list.
Connect the Set Operation output group to a target input group.
You can produce a sorted row set using the Sorter operator.
The Sorter operator enables you to specify which input attributes are sorted and whether the sorting is performed in ascending or descending order. Warehouse Builder sorts data by placing an ORDER BY clause in the code generated by the mapping.
The Sorter operator has one input/output group. You can use the Sorter operator to sort data from any relational database source. You can place any operator after the Sorter operator.
The Sorter operator contains the Order By clause. This clause is an ordered list of attributes in the input/output group to specify that sorting is performed in the same order as the ordered attribute list. You can set ascending or descending sorting for each attribute.
Most data in warehouses is loaded in batches. There can be some problems with the loading routines. For example, a batch of orders might contain a single order number multiple times with each order line representing a different state of the order. The order might have gone from status 'CREATED' to 'UPDATED' to 'BOOKED' during the day.
Because a SQL statement does not guarantee any ordering by default, the inserts and updates on the target table can take place in the wrong order. If the 'UPDATED' row is processed last, it becomes the final value for the day although the result should be status 'BOOKED'. Warehouse Builder enables you to solve this problem by creating an ordered extraction query using the Sorter operator. The ORDER BY clause can use the last updated attribute. This will ensure that the records appear in the order in which they were created.
Figure 26-21 shows a mapping that uses the Sorter operator to sort the records from the ORACLE_ORDERS table. Use the Order By Clause property of the Sorter operator to sort the input records on the ORDER_ID and the LAST_UPDATED attributes.
Figure 26-21 Sorter Operator in a Mapping
To use the Sorter operator in a mapping:
Drag and drop the Sorter operator onto the Mapping Editor canvas.
Connect a source operator group to the Sorter input/output group as shown in Figure 26-21.
Select the Sorter operator header.
The Sorter Properties panel displays the properties of the operator.
Click the Ellipsis button in the Order By Clause field.
The Order By Clause dialog is displayed.
Select the attributes you want to sort.
Select an attribute from the Available Attributes list and click the right arrow button. Or, click the double right arrow button to select all of the Available Attributes.
Apply an ORDER BY clause to the attribute.
Select the attribute in the ORDER BY Attributes list and select ASC (ascending) or DESC (descending) from the ASC/DESC drop-down list.
Click OK.
Connect the output of the Sorter operator to the target.
You can use the Splitter operator to split data from one source to several targets.
The Splitter operator splits a single input row set into several output row sets using a boolean split condition. Each output row set has a cardinality less than or equal to the input cardinality. This is useful when you want to move data to different targets based on a data driven condition. Instead of moving the data through multiple filters, you can use a splitter.
As an option, you can optimize mappings that split data from one source to multiple targets for improved performance. For more information, see "Example: Creating Mappings with Multiple Targets".
The Splitter operator contains one input group and three output groups. The output groups are OUTGRP1, OUTGRP2, and REMAINING_ROWS. The output group REMAINING_ROWS contains all input rows that are not included in any output group. You can delete this output group, but you cannot edit it. You can create additional output groups, if required.
The Splitter Operator contains the split condition. For code generation, the source columns are substituted by the input attribute names in the expression template. The expression is a valid SQL expression that can be used in a WHERE clause.
Figure 26-22 shows the mapping that uses the Splitter operator to split customer data from the source table CUSTOMERS into two separate tables. One table contains only the customer addresses and the other table contains the remaining customer details. Use the Split Condition property of each output group in the Splitter operator to specify which data should be moved to a particular target table.
Figure 26-22 Splitter Operator in a Mapping
To use the Splitter operator in a mapping:
Drag and drop the Splitter operator onto the Mapping Editor canvas.
Connect a group from a source operator to the input group of the Splitter operator.
The output attributes are created with data types matching the corresponding input data types.
Select the output group of the Splitter operator.
The Group Properties panel displays the properties of the output group.
Click the Ellipsis button to the right of the Split Condition field.
The Expression Builder dialog is displayed.
Define the split condition.
For example, the split condition can be UPPER(INGRP1.OR_CHANNEL) = 'DIRECT'.
Define expressions for the split condition of each output group except the REMAINING ROWS group.
Connect the output groups to the targets.
When you design a mapping with multiple targets, you have the option to optimize for improved performance. You may decide to not optimize if you require accurate auditing details for the mapping. If you decide to not optimize, Warehouse Builder generates separate insert statements for each target.
To optimize a multiple target mapping, you must take additional steps for Warehouse Builder to generate a single insert statement for all targets combined. In this case, Warehouse Builder generates a multi_table_insert SQL statement that takes advantage of parallel query and parallel DML services available in versions 9i and higher of the Oracle Database server.
To optimize a mapping with multiple targets:
Define a mapping in an Oracle target module configured to generate Oracle9i or higher SQL.
From Warehouse Builder, right-click the target module on the Project Explorer and select Configure. Under Deployment System Type and PL/SQL Generation Mode, select Oracle9i or higher.
In the Mapping Editor, design a mapping with a single source, a Splitter operator, and multiple targets.
For Warehouse Builder to optimize the mapping, the targets must be tables, not views or materialized views. Each target table must have less than 999 columns. Between the Splitter operator and the targets, do not include any operators that change the cardinality. For example, you can place a Filter between the Splitter and the targets as shown in Figure 26-23, but not a Joiner or Aggregator operator. These restrictions only apply if you choose to optimize the mapping.
Figure 26-23 Example Mapping with Multiple Targets
From the Project Explorer, select the mapping and select Design from the menu bar, and select Configure. You can also right-click the mapping you want to configure and select Configure.
Warehouse Builder displays the configuration properties dialog for a mapping.
Expand Runtime Parameters and set Default Operating Mode to set based.
Expand Code Generation Options and select Optimized Code.
When you run this mapping and view the generation results, Warehouse Builder returns one total SELECT and INSERT count for all targets.
Table function operators enable you to manipulate a set of input rows and return another set of rows of the same or different cardinality.
While a regular function only works on one row at a time, a table function enables you to apply the same complex PL/SQL logic on a set of rows and increase your performance. Unlike conventional functions, table functions can return a set of output rows that can be queried like a physical table.
The execution of the table function can also be parallelized where the returned rows are streamed directly to the next process without intermediate staging. Rows from a collection returned by a table function can also be pipelined or output one by one, as they are produced, instead of being output in a batch after processing of the entire table function input is completed.
Using table functions can greatly improve performance when loading your data warehouse.
To define a Table Function operator in a mapping:
Before you deploy the mapping containing the Table Function operator, you must manually create the table function in the target warehouse. The Table Function operator is bound to the actual table function object through the code generated by the mapping.
Drag and drop a Table Function operator onto the Mapping Editor canvas.
A table function operator called TABLEFUNCTION is added to the Mapping Editor canvas.
Connect the appropriate source attributes to the input group of the table function operator.
Right-click the Table Function operator and select Open Details.
The Table Function Editor is displayed.
From the Groups tab, select Add to add an output group.
Figure 26-24 shows a mapping that uses a Table Function operator to load data into a table.
Figure 26-24 Table Function Operator in a Mapping
Characteristics of Table Functions
They do not support the passing of parameters by name.
If the return type is TABLE of PLS Record, the name you select must match the name of PLS Record field. It is possible to select only one subset of the fields of the PLS Record in the select list.
If the return type is TABLE of T1%ROWTYPE, the name you select must match the name of the columns of the table T1.
If the return type is TABLE of Object Type, the name you select list must match the name of Object Type attribute.
If the return type is TABLE of Scalar (like TABLE of NUMBER), only Select COLUMN_VALUE can be used to retrieve the scalar values returned by the table function.
Before you can use the Table Function operator in a mapping, create the table function in your target schema, external to Warehouse Builder. The table functions in the database that are supported by the unbound table function operator must meet the following requirements:
Ref Cursor returning PLS Record (the fields of the PLS Record) must be scalar data types supported by Warehouse Builder (0..n).
There must be at least one input parameter.
PLS Record (the fields of the PLS Record should be scalar data types supported by Warehouse Builder).
Object Type (the attributes of the Object Type should be scalar data types supported by Warehouse Builder).
Scalar data types supported by Warehouse Builder.
ROWTYPE
For a Table Function operator in a mapping:
You must add one parameter group for each ref cursor type parameter.
Multiple scalar parameters can be part of a single scalar type parameter group.
The parameter groups and the parameters in a group can be entered in any order.
The positioning of the parameters in the table function operator must be the same as the positioning of the parameters in the table function created in your target warehouse.
You access the Table Function operator properties using the Properties panel of the Mapping Editor. The Properties panel displays the properties of the object selected on the canvas. For example, when you select the input group of the table function operator, the Properties panel displays the properties of the input parameter group.
The Table Function operator has the following properties.
Table Function Name: Represents the name of the table function. The name specified here must match the actual name of the table function.
Table Function is Target: Select this option to indicate that the table function is a target. By default, this property is selected.
The table function operator accepts the following types of input parameters:
Input Parameter Type: Valid input parameter types are REF_CURSOR_TYPE or SCALAR_TYPE.
REF_CURSOR_TYPE: Returns a PLS Record { 0...N }. The fields of the PLS Record must be a scalar data type supported by Warehouse Builder.
SCALAR_TYPE: Scalar data types supported by Warehouse Builder.
Parameter Position: Indicates the position of the parameter in the table function signature corresponding to this parameter group. This property is applicable only to REF_CURSOR attribute groups and is used in conjunction with the scalar parameter positions.
Parameter Position: The position of the parameter in the table function signature. This property is only applicable to scalar parameters.
Return Table of Scalar: This property specifies whether the return of the table function is a TABLE of SCALAR or not. This information is required because the select list item for TABLE of SCALAR must be Select COLUMN_VALUE while in the other cases it should be an appropriate name.
You use the Transformation operator to transform the column value data of rows within a row set using a PL/SQL function, while preserving the cardinality of the input row set.
The Transformation operator must be bound to a function or procedure contained by one of the modules in the repository. The inputs and outputs of the Transformation operator correspond to the input and output parameters of the bound repository function or procedure. If the Transformation operator is bound to a function, a result output is added to the operator that corresponds to the result of the function. The bound function or procedure must be generated and deployed before the mapping can be deployed, unless the function or procedure already exists in the target system.
Warehouse Builder provides pre-defined PL/SQL library functions in the runtime schema that can be selected as a bound function when adding a Transformation operator onto a mapping. In addition, you can choose a function or procedure from the Global Shared Library.
The Transformation operator contains the following properties:
Function Call: The text template for the function call that is generated by the code generator with the attribute names listed as the calling parameters. For the actual call, the attribute names are replaced with the actual source or target columns that are connected to the attributes.
Function Name: The name of the function or procedure, to which this operator is bound.
Procedure: A boolean value indicating, if true, that the bound transformation is a procedure rather than a function with no returned value.
Data Type: Indicates the data type of the input, output, or result parameter of the bound function that corresponds to the given attribute. If the output of a mapping transformation is of CHAR data type, then Warehouse Builder applies an RTRIM on the result before moving the data to a target. This ensures that no extra spaces are contained in the output result.
Default Value: The default value (blank if none) for the given attribute.
Optional Input: A boolean value indicating, if true, that the given attribute is optional. If the attribute is optional, it need not be connected in the mapping.
Function Return: A boolean value indicating, if true, that the given output attribute is the result attribute for the function. The result attribute is a named result. Use this property if another output is a named result, or if you change the name of the result output.
To use a Mapping Transformation operator in a mapping:
Drag and drop a Mapping Transformation operator onto the Mapping Editor canvas.
The Add Mapping Transformation dialog is displayed.
Use the Add Mapping Transformation dialog to create a new transformation or select one or more transformations. For more information on these options, see "Adding Operators that Bind to Repository Objects" beginning.
Connect the source attributes to the inputs of the Mapping Transformation operator.
(Optional step) Right-click one of the inputs and select Open Details.
The Attribute Properties panel displays the properties of the attribute.
Select an input attribute. If the Procedure property is set to True, then do not connect the input parameter.
Connect the Transformation operator output attributes to the target attributes.
The unpivot operator converts multiple input rows into one output row.
The unpivot operator enables you to extract from a source once and produce one row from a set of source rows that are grouped by attributes in the source data. Like the pivot operator, the unpivot operator can be placed anywhere in a mapping.
Table 26-3 shows a sample of data from the SALES relational table. In the crosstab format, the 'MONTH' column has 12 possible character values, one for each month of the year. All sales figures are contained in one column, 'MONTHLY_SALES'.
Table 26-3 Data in a Crosstab Format
| REP | MONTH | MONTHLY_SALES | REGION |
|---|---|---|---|
|
0675 |
Jan |
10.5 |
4 |
|
0676 |
Jan |
9.5 |
3 |
|
0679 |
Jan |
8.7 |
3 |
|
0675 |
Feb |
11.4 |
4 |
|
0676 |
Feb |
10.5 |
3 |
|
0679 |
Feb |
7.4 |
3 |
|
0675 |
Mar |
9.5 |
4 |
|
0676 |
Mar |
10.3 |
3 |
|
0679 |
Mar |
7.5 |
3 |
|
0675 |
Apr |
8.7 |
4 |
|
0676 |
Apr |
7.6 |
3 |
|
0679 |
Apr |
7.8 |
3 |
Figure 26-25 depicts data from the relational table 'SALES' after Warehouse Builder unpivoted the table. The data formerly contained in the 'MONTH' column (Jan, Feb, Mar...) corresponds to12 separate attributes (M1, M2, M3...). The sales figures formerly contained in the 'MONTHLY_SALES' are now distributed across the 12 attributes for each month.
Figure 26-25 Data Unpivoted from Crosstab Format
When you use the Unpivot operator, Warehouse Builder transforms multiple input rows into a single row based on the row locator. In the Unpivot operator, the row locator is an attribute that you must select from the source to correspond with a set of output attributes that you define. A row locator is required in an unpivot operator. In this example, the row locator is 'MONTH' from the 'SALES' table and it corresponds to attributes M1, M2, M3... M12 in the unpivoted output.
You have the following options for using an unpivot operator:
Define a new Unpivot operator: Drag an Unpivot operator from the Palette onto the mapping. The Mapping Editor launches a wizard.
Edit an existing Unpivot operator: Right-click the Unpivot operator and select Open Details. The Mapping Editor opens the Unpivot Editor.
Whether you are using the Unpivot Wizard or the Unpivot Editor, complete the following pages:
Use the General page to specify a name and optional description for the Unpivot operator. By default, the wizard names the operator "Unpivot".
Use the Groups page to specify one input and one output group.
In an Unpivot operator, the input group represents the source data in crosstab format. The output group represents the target data distributed across multiple attributes.
You can rename and add descriptions to the input and output groups. Since each Unpivot operator must have exactly one input and one output group, the wizard prevents you from adding or removing groups or changing group direction.
Use the Input Connections page to select attributes to copy and map into the unpivot operator.
To complete the Input connections page for an Unpivot operator:
Select complete groups or individual attributes from the left panel.
To search for a specific attribute or group by name, type the text in Search for and click Go. To find the next match, click Go again.
Hold the Shift key down to select multiple groups or attributes. If you want to select attributes from different groups, you must first combine the groups with a Joiner or Set operator.
Use the left to right arrow button in the middle of the page to move your selections to the right side of the wizard page.
You can use the right to left arrow to move groups or attributes from the input connections list. Warehouse Builder removes the selection from the input group and removes the mapping line between the source operator and the unpivot operator.
Use the Input Attributes page to modify the attributes you selected in the Input Connections tab or wizard page.
You can perform the following tasks from the Unpivot Input Attributes page:
Add attributes: Use the Add button to add input attributes.
Change attribute properties: You can change the attribute name, data type, length, precision and scale.
Add an optional description: Type a description for the input attributes.
Designate key attribute(s): You must designate one or more key attributes for unpivot operators. Use the Key check box to indicate the attribute(s) that uniquely identifies the input group. Input rows with the same value in their key attribute(s) produce one unpivoted output row.
Use the Row locator page to select a row locator and assign values to the distinct values contained in the row locator. Figure 26-26 shows the attribute MONTH selected as the row locator with values such as 'Jan', 'Feb', or 'Mar'.
To complete the Unpivot Row Locator page:
Select an attribute from the Row locator list box.
In the Unpivot operator, the row locator is the attribute from the source data that corresponds to a set of output attributes.
Use Add to specify the number of distinct values that exist in the row locator.
For each row locator value, type in the value as it appears in your source dataset.
For string values, enclose the text in single quotes. For example, if the row locator is 'MONTH', there would be a total of 12 distinct values for that attribute. Click Add to add a row for each distinct value. For row locator values, type values exactly as they appear in the source dataset. For instance, the row locator values as shown in Table 26-3 are 'Jan', 'Feb', and 'Mar.'
Use the Output Attributes page shown in Figure 26-27 to create the output attributes for the Unpivot operator.
Figure 26-27 Unpivot Output Attributes Page
If you designated any input attributes as keys on the Input Attributes tab or wizard page, Warehouse Builder displays those input attributes as output attributes that you cannot edit or remove.
You can perform the following tasks from the Unpivot Output Attributes page:
Add attributes: Use Add to increase the number of output attributes to accommodate the rows you specified on the Row locator tab or wizard page. If you specified 12 rows, specify 12 output attributes plus attributes for any other input attributes that you did not designate as a key.
Change attribute properties: Except for attributes you designated as keys on the Input Attributes tab or wizard page, you can change the attribute name, data type, length, precision, and scale.
Add an optional description: Type a description for the output attributes.
Use the Unpivot Transform page shown in Figure 26-28 to write expressions for each output attribute.
For attributes you designated as keys, Warehouse Builder defines the matching row and expression for you. Warehouse Builder displays the first row as the match for a key attribute. For all other output attributes, specify the matching row and the expression.
Matching row: Select the appropriate option from the list box. For example, for the attribute you define as the first month of the year, 'M1', select 'Jan' from the list box.
Expression: Select the appropriate expression from the list box. For all the new attributes you created to unpivot the data, select the same input attribute that contains the corresponding data. For example, the unpivot attributes M1, M2, M3... M12 would all share the same expression, INGRP1.MONTHLY_SALES. For all other output attributes, select the corresponding attribute from the list of input attributes.
|
 Copyright © 2000, 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
