| Oracle® Warehouse Builder Data Modeling, ETL, and Data Quality Guide 11g Release 2 (11.2) Part Number E10935-03 |
|
|
PDF · Mobi · ePub |
| Oracle® Warehouse Builder Data Modeling, ETL, and Data Quality Guide 11g Release 2 (11.2) Part Number E10935-03 |
|
|
PDF · Mobi · ePub |
This chapter describes the predefined transformations provided by Warehouse Builder to transform data.
Predefined transformations in the public Oracle Predefined library are categorized as follows:
Administrative transformations provide prebuilt functionality to perform actions that are regularly performed in ETL processes. The main focus of these transformations is in the DBA related areas or to improve performance. For example, it is common to disable constraints when loading tables and then to reenable them after loading has completed.
The administrative transformations in Warehouse Builder are custom functions. The Administrative transformation that Warehouse Builder provides are:
WB_ABORT(p_code, p_message)
where p_code is the abort code, and must be between -20000 and -29999; and p_message is an abort message you specify.
WB_ABORT enables you to terminate the application from a Warehouse Builder component. You can run it from a post-mapping process or as a transformation within a mapping.
Use this administration function to terminate an application. You can use this function in a post-mapping process to terminate deployment if there is an error in the mapping.
WB_COMPILE_PLSQL(p_name, p_type)
where p_name is the name of the object that is to be compiled; p_type is the type of object to be compiled. The legal types are:
'PACKAGE' 'PACKAGE BODY' 'PROCEDURE' 'FUNCTION' 'TRIGGER'
This program unit compiles a stored object in the database.
The following hypothetical example compiles the procedure called add_employee_proc:
EXECUTE WB_COMPILE_PLSQL('ADD_EMPLOYEE_PROC', 'PROCEDURE');
WB_DISABLE_ALL_CONSTRAINTS(p_name)
where p_name is the name of the table on which constraints are disabled.
This program unit disables all constraints that are owned by the table as stated in the call to the program.
For faster loading of data sets, you can disable constraints on a table. The data is now loaded without validation. This is mainly done on relatively clean data sets.
The following example shows the disabling of the constraints on the table OE.CUSTOMERS:
SELECT constraint_name , DECODE(constraint_type,'C','Check','P','Primary') Type , status FROM user_constraints WHERE table_name = 'CUSTOMERS';
CONSTRAINT_NAME TYPE STATUS ------------------------------ ------- -------- CUST_FNAME_NN Check ENABLED CUST_LNAME_NN Check ENABLED CUSTOMER_CREDIT_LIMIT_MAX Check ENABLED CUSTOMER_ID_MIN Check ENABLED CUSTOMERS_PK Primary ENABLED
Perform the following in SQL*Plus or Warehouse Builder to disable all constraints:
EXECUTE WB_DISABLE_ALL_CONSTRAINTS('CUSTOMERS');
CONSTRAINT_NAME TYPE STATUS
------------------------------ ------- --------
CUST_FNAME_NN Check DISABLED
CUST_LNAME_NN Check DISABLED
CUSTOMER_CREDIT_LIMIT_MAX Check DISABLED
CUSTOMER_ID_MIN Check DISABLED
CUSTOMERS_PK Primary DISABLED
Note:
This statement uses a cascade option to allow dependencies to be broken by disabling the keys.WB_DISABLE_ALL_TRIGGERS(p_name)
where p_name is the table name on which the triggers are disabled.
This program unit disables all triggers owned by the table as stated in the call to the program. The owner of the table must be the current user (in variable USER). This action stops triggers and improves performance.
The following example shows the disabling of all triggers on the table OE.OC_ORDERS:
SELECT trigger_name , status FROM user_triggers WHERE table_name = 'OC_ORDERS'; TRIGGER_NAME STATUS ------------------------------ -------- ORDERS_TRG ENABLED ORDERS_ITEMS_TRG ENABLED
Perform the following in SQL*Plus or Warehouse Builder to disable all triggers on the table OC_ORDERS.
EXECUTE WB_DISABLE_ALL_TRIGGERS ('OC_ORDERS');
TRIGGER_NAME STATUS ------------------------------ -------- ORDERS_TRG DISABLED ORDERS_ITEMS_TRG DISABLED
WB_DISABLE_CONSTRAINT(p_constraintname, p_tablename)
where p_constraintname is the constraint name to be disabled; p_tablename is the table name on which the specified constraint is defined.
This program unit disables the specified constraint that is owned by the table as stated in the call to the program. The user is the current user (in variable USER).
For faster loading of data sets, you can disable constraints on a table. The data is then loaded without validation. This reduces overhead and is mainly done on relatively clean data sets.
The following example shows the disabling of the specified constraint on the table OE.CUSTOMERS:
SELECT constraint_name , DECODE(constraint_type , 'C', 'Check' , 'P', 'Primary' ) Type , status FROM user_constraints WHERE table_name = 'CUSTOMERS'; CONSTRAINT_NAME TYPE STATUS ------------------------------ ------- -------- CUST_FNAME_NN Check ENABLED CUST_LNAME_NN Check ENABLED CUSTOMER_CREDIT_LIMIT_MAX Check ENABLED CUSTOMER_ID_MIN Check ENABLED CUSTOMERS_PK Primary ENABLED
Perform the following in SQL*Plus or Warehouse Builder to disable the specified constraint.
EXECUTE WB_DISABLE_CONSTRAINT('CUSTOMERS_PK','CUSTOMERS');
CONSTRAINT_NAME TYPE STATUS
------------------------------ ------- --------
CUST_FNAME_NN Check ENABLED
CUST_LNAME_NN Check ENABLED
CUSTOMER_CREDIT_LIMIT_MAX Check ENABLED
CUSTOMER_ID_MIN Check ENABLED
CUSTOMERS_PK Primary DISABLED
Note:
This statement uses a cascade option to allow dependencies to be broken by disabling the keys.WB_DISABLE_TRIGGER(p_name)
where p_name is the trigger name to be disabled.
This program unit disables the specified trigger. The owner of the trigger must be the current user (in variable USER).
The following example shows the disabling of a trigger on the table OE.OC_ORDERS:
SELECT trigger_name, status FROM user_triggers WHERE table_name = 'OC_ORDERS'; TRIGGER_NAME STATUS ------------------------------ -------- ORDERS_TRG ENABLED ORDERS_ITEMS_TRG ENABLED
Perform the following in SQL*Plus or Warehouse Builder to disable the specified constraint.
ECECUTE WB_DISABLE_TRIGGER ('ORDERS_TRG');
TRIGGER_NAME STATUS
------------------------------ --------
ORDERS_TRG DISABLED
ORDERS_ITEMS_TRG ENABLED
WB_ENABLE_ALL_CONSTRAINTS(p_name)
where p_name is the name of the table for which all constraints should be enabled.
This program unit enables all constraints that are owned by the table as stated in the call to the program.
For faster loading of data sets, you can disable constraints on a table. After the data is loaded, you must enable these constraints again using this program unit.
The following example shows the enabling of the constraints on the table OE.CUSTOMERS:
SELECT constraint_name , DECODE(constraint_type
, 'C', 'Check' , 'P', 'Primary) Type , status
FROM user_constraints WHERE table_name = 'CUSTOMERS'; CONSTRAINT_NAME TYPE STATUS ------------------------------ ------- -------- CUST_FNAME_NN Check DISABLED CUST_LNAME_NN Check DISABLED CUSTOMER_CREDIT_LIMIT_MAX Check DISABLED CUSTOMER_ID_MIN Check DISABLED CUSTOMERS_PK Primary DISABLED
Perform the following in SQL*Plus or Warehouse Builder to enable all constraints.
EXECUTE WB_ENABLE_ALL_CONSTRAINTS('CUSTOMERS');
CONSTRAINT_NAME TYPE STATUS
------------------------------ ------- --------
CUST_FNAME_NN Check ENABLED
CUST_LNAME_NN Check ENABLED
CUSTOMER_CREDIT_LIMIT_MAX Check ENABLED
CUSTOMER_ID_MIN Check ENABLED
CUSTOMERS_PK Primary ENABLED
WB_ENABLE_ALL_TRIGGERS(p_name)
where p_name is the table name on which the triggers are enabled.
This program unit enables all triggers owned by the table as stated in the call to the program. The owner of the table must be the current user (in variable USER).
The following example shows the enabling of all triggers on the table OE.OC_ORDERS:
SELECT trigger_name , status FROM user_triggers WHERE table_name = 'OC_ORDERS'; TRIGGER_NAME STATUS ------------------------------ -------- ORDERS_TRG DISABLED ORDERS_ITEMS_TRG DISABLED
Perform the following in SQL*Plus or Warehouse Builder to enable all triggers defined on the table OE.OC_ORDERS.
EXECUTE WB_ENABLE_ALL_TRIGGERS ('OC_ORDERS');
TRIGGER_NAME STATUS
------------------------------ --------
ORDERS_TRG ENABLED
ORDERS_ITEMS_TRG ENABLED
WB_ENABLE_CONSTRAINT(p_constraintname, p_tablename)
where p_constraintname is the constraint name to be disabled and p_tablename is the table name on which the specified constraint is defined.
This program unit enables the specified constraint that is owned by the table as stated in the call to the program. The user is the current user (in variable USER). For faster loading of data sets, you can disable constraints on a table. After the loading is complete, you must reenable these constraints. This program unit shows you how to enable the constraints one at a time.
The following example shows the enabling of the specified constraint on the table OE.CUSTOMERS:
SELECT constraint_name
, DECODE(constraint_type
, 'C', 'Check'
, 'P', 'Primary'
) Type
, status
FROM user_constraints
WHERE table_name = 'CUSTOMERS';
CONSTRAINT_NAME TYPE STATUS
------------------------------ ------- --------
CUST_FNAME_NN Check DISABLED
CUST_LNAME_NN Check DISABLED
CUSTOMER_CREDIT_LIMIT_MAX Check DISABLED
CUSTOMER_ID_MIN Check DISABLED
CUSTOMERS_PK Primary DISABLED
Perform the following in SQL*Plus or Warehouse Builder to enable the specified constraint.
EXECUTE WB_ENABLE_CONSTRAINT('CUSTOMERS_PK', 'CUSTOMERS');
CONSTRAINT_NAME TYPE STATUS
------------------------------ ------- --------
CUST_FNAME_NN Check DISABLED
CUST_LNAME_NN Check DISABLED
CUSTOMER_CREDIT_LIMIT_MAX Check DISABLED
CUSTOMER_ID_MIN Check DISABLED
CUSTOMERS_PK Primary ENABLED
WB_ENABLE_TRIGGER(p_name)
where p_name is the trigger name to be enabled.
This program unit enables the specified trigger. The owner of the trigger must be the current user (in variable USER).
The following example shows the enabling of a trigger on the table OE.OC_ORDERS:
SELECT trigger_name , status FROM user_triggers WHERE table_name = 'OC_ORDERS'; TRIGGER_NAME STATUS ------------------------------ -------- ORDERS_TRG DISABLED ORDERS_ITEMS_TRG ENABLED
Perform the following in SQL*Plus or Warehouse Builder to enable the specified constraint.
EXECUTE WB_ENABLE_TRIGGER ('ORDERS_TRG');
TRIGGER_NAME STATUS
------------------------------ --------
ORDERS_TRG ENABLED
ORDERS_ITEMS_TRG ENABLED
WB_TRUNCATE_TABLE(p_name)
where p_name is the table name to be truncated.
This program unit truncates the table specified in the command call. The owner of the trigger must be the current user (in variable USER). The command disables and reenables all referencing constraints to enable the truncate table command. Use this command in a pre-mapping process to explicitly truncate a staging table and ensure that all data in this staging table is newly loaded data.
The following example shows the truncation of the table OE.OC_ORDERS:
SELECT COUNT(*) FROM oc_orders;
COUNT(*)
----------
105
Perform the following in SQL*Plus or Warehouse Builder to enable the specified constraint.
EXECUTE WB_TRUNCATE_TABLE ('OC_ORDERS');
COUNT(*)
----------
0
Character transformations enable Warehouse Builder users to perform transformations on Character objects. The custom functions provided with Warehouse Builder are prefixed with WB_.
The character transformations available in Warehouse Builder are listed below. Most of them are implementations of basic SQL functions or procedures. No descriptions are provided for such transformations.
Table 28-1 lists the character transformations that are based on Database SQL functions. The transformations are listed in a columnar table that reads down the columns from left to right to conserve space.
Table 28-1 Character Transformations Based on SQL character functions
| Character Transformation Name | Character Transformation Name (Contd.) | Character Transformation Name (Contd.) |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
For descriptions and examples of these functions, refer to section "Character Functions" in the Oracle Database SQL Language Reference.
Following is the list of custom character transformations.
WB.LOOKUP_CHAR (table_name , column_name , key_column_name , key_value )
where table_name is the name of the table to perform the lookup on and column_name is the name of the VARCHAR2 column that will be returned. For example, the result of the lookup key_column_name is the name of the NUMBER column used as the key to match on in the lookup table, key_value is the value of the key column mapped into the key_column_name with which the match will be done.
To perform a key lookup on a number that returns a VARCHAR2 value from a database table using a NUMBER column as the matching key.
Consider the following table as a lookup table LKP1:
KEY_COLUMN TYPE COLOR 10 Car Red 20 Bike Green
Using this package with the following call:
WB.LOOKUP_CHAR ('LKP1'
, 'TYPE'
, 'KEYCOLUMN'
, 20
)
returns the value of 'Bike' as output of this transform. This output would then be processed in the mapping as the result of an inline function call.
Note:
This function is a row-based key lookup. Set-based lookups are supported when you use the Lookup operator.WB.LOOKUP_CHAR (table_name , column_name , key_column_name , key_value )
where table_name is the name of the table to perform the lookup on; column_name is the name of the VARCHAR2 column that will be returned, for instance, the result of the lookup; key_column_name is the name of the VARCHAR2 column used as the key to match on in the lookup table; key_value is the value of the key column, for instance, the value mapped into the key_column_name with which the match will be done.
To perform a key lookup on a VARCHAR2 character that returns a VARCHAR2 value from a database table using a VARCHAR2 column as the matching key.
Consider the following table as a lookup table LKP1:
KEYCOLUMN TYPE COLOR ACV Car Red ACP Bike Green
Using this package with the following call:
WB.LOOKUP_CHAR ('LKP1'
, 'TYPE'
, 'KEYCOLUMN'
, 'ACP'
)
returns the value of 'Bike' as output of this transformation. This output is then processed in the mapping as the result of an inline function call.
Note:
This function is a row-based key lookup. Set-based lookups are supported when you use the Lookup operator.WB_IS_SPACE(attibute)
Checks whether a string value only contains spaces. This function returns a Boolean value. In mainframe sources, some fields contain many spaces to make a file adhere to the fixed length format. This function provides a way to check for these spaces.
WB_IS_SPACE returns TRUE if attribute contains only spaces.
Control Center transformations are used in a process flow or in custom transformations to enable you to access information about the Control Center at execution time. For example, you can use a Control Center transformation in the expression on a transition to help control the flow through a process flow at execution time. You can also use Control Center transformations within custom functions. These custom functions can in turn be used in the design of your process flow.
All Control Center transformations require an audit ID that provides a handle to the audit data stored in the Control Center workspace. The audit ID is a key into the public view ALL_RT_AUDIT_EXECUTIONS. The transformations can be used to obtain data specific to that audit ID at execution time. When run in the context of a process flow, you can obtain the audit ID at execution time using the pseudo variable audit_id in a process flow expression. This variable is evaluated as the audit ID of the currently executing job. For example, for a map input parameter, this represents the map execution and for a transition this represents the job at the source of the transition.
The Control Center transformations are:
WB_RT_GET_ELAPSED_TIME(audit_id)
This function returns the elapsed time, in seconds, for the job execution given by the specified audit_id. It returns null if the specified audit ID does not exist. For example, you can use this function on a transition if you want to make a choice dependent on the time taken by the previous activity.
The following example returns the time elapsed since the activity represented by audit_id was started:
declare audit_id NUMBER := 1812; l_time NUMBER; begin l_time:= WB_RT_GET_ELAPSED_TIME(audit_id); end;
WB_RT_GET_JOB_METRICS(audit_id, no_selected, no_deleted, no_updated, no_inserted, no_discarded, no_merged, no_corrected)
where no_selected represents the number of rows selected, no_deleted represents the number of rows deleted, no_updated represents the number of rows updated, no_inserted represents the number of rows inserted, no_discarded represents the number of rows discarded, no_merged represents the number of rows merged, and no_corrected represents the number of rows corrected during the job execution.
This procedure returns the metrics of the job execution represented by the specified audit_id. The metrics include the number of rows selected, deleted, updated, inserted, discarded, merged, and corrected.
The following example retrieves the job metrics for the audit ID represented by audit_id.
declare
audit_id NUMBER := 16547;
l_nselected NUMBER;
l_ndeleted NUMBER;
l_nupdated NUMBER;
l_ninserted NUMBER;
l_ndiscarded NUMBER;
l_nmerged NUMBER;
l_ncorrected NUMBER;
begin
WB_RT_GET_JOB_METRICS(audit_id, l_nselected, l_ndeleted, l_nupdated,
l_ninserted, l_ndiscarded, l_nmerged, l_ncorrected);
dbms_output.put_line('sel=' || l_nselected || ', del=' l_ndeleted ||
', upd=' || l_nupdated);
dbms_output.put_line('ins='|| l_ninserted || ' , dis=' || l_ndiscarded );
dbms_output.put_line('mer=' || l_nmerged || ', cor=' ||l_ncorrected);
end;
WB_RT_GET_LAST_EXECUTION_TIME(objectName, objectType, objectLocationName)
where objectName represents the name of the object, objectType represents the type of the object (for example MAPPING, DATA_AUDITOR, PROCESS_FLOW, SCHEDULABLE), and objectLocationName represents the location to which the object is deployed.
This transformation gives you access to time-based data. Typically, you can use this in a Process Flow to model some design aspect that is relevant to "time". For example you can design a path that may execute different maps if the time since the last execution is more than 1 day.
You can also use this transformation to determine time-synchronization across process flows that are running concurrently. For example, you can choose a path in a process flow according to whether another Process Flow has completed.
The following example retrieves the time when the mapping TIMES_MAP was last executed and the if condition determines whether this time was within 1 day of the current time. Based on this time, it can perform different actions.
declare
last_exec_time DATE;
begin
last_exec_time:=WB_RT_GET_LAST_EXECUTION_TIME('TIMES_MAP','MAPPING','WH_LOCATION');
if last_exec_time < sysdate - 1 then
-- last-execution was more than one day ago
-- provide details of action here
NULL;
Else
-- provide details of action here
NULL;
end if;
end;
WB_RT_GET_MAP_RUN_AUDIT(audit_id)
This function returns the map run ID for a job execution that represents a map activity. It returns null if audit_id does not represent the job execution for a map. For example, you can use the returned ID as a key to access the ALL_RT_MAP_RUN_<name> views for more information.
The following example retrieves the map run ID for a job execution whose audit ID is 67265. It then uses this map run ID to obtain the name of the source from the ALL_RT_MAP_RUN_EXECUTIONS public view.
declare audit_id NUMBER := 67265; l_sources VARCHAR2(256); l_run_id NUMBER;begin l_run_id := WB_RT_GET_MAP_RUN_AUDIT_ID(audit_id); SELECT source_name INTO l_sources FROM all_rt_map_run_sources WHERE map_run_id = l_run_id;end;
WB_RT_GET_NUMBER_OF_ERRORS(audit_id)
This function returns the number of errors recorded for the job execution given by the specified audit_id. It returns null if the specific audit_id is not found.
The following example retrieves the number of errors generated by the job execution whose audit ID is 8769. You can then perform different actions based on the number of errors.
declare
audit_id NUMBER := 8769;
l_errors NUMBER;begin l_errors := WB_RT_GET_NUMBER_OF_ERRORS(audit_id);
if l_errors < 5 then
.....
else
.....
end if;
end;
WB_RT_GET_NUMBER_OF_WARNINGS(audit_id)
This function returns the number of warnings recorded for the job executions represented by audit_id. It returns null if audit_id does not exist.
The following example returns the number of warnings generated by the job execution whose audit ID is 54632. You can then perform different actions based on the number of warnings.
declare audit_is NUMBER := 54632;
l_warnings NUMBER;begin l_ warnings:= WB_RT_GET_NUMBER_OF_WARNINGS (audit_id);
if l_warnings < 5 then
.....
else
.....
end if;
end;
WB_RT_GET_PARENT_AUDIT_ID(audit_id)
This function returns the audit id for the process that owns the job execution represented by audit_id. It returns null if audit_id does not exist. You can then use the returned audit id as a key into other public views such as ALL_RT_AUDIT_EXECUTIONS, or other Control Center transformations if further information is required.
The following example retrieves the parent audit ID for a job execution whose audit ID is 76859. It then uses this audit ID to determine the elapsed time for the parent activity. You can perform different actions based on the elapsed time of the parent activity.
declare
audit_id NUMBER := 76859;
l_elapsed_time NUMBER; l_parent_id NUMBER;begin l_parent_id := WB_RT_GET_PARENT_AUDIT_ID(audit_id); l_elapsed_time := WB_RT_GET_ELAPSED_TIME(l_parent_id);
if l_elpased_time < 100 then
.....
else
.....
end if;
end;
WB_RT_GET_RETURN_CODE(audit_id)
This function returns the return code recorded for the job execution represented by audit_id. It returns null if audit_id does not exist. For a successful job execution, the return code is greater than or equal to 0. A return code of less than 0 signifies that the job execution has failed.
The following example retrieves the return code for the job execution whose audit ID is represented by audit_id.
declare audit_id NUMBER:=69; l_code NUMBER;begin l_code:= WB_RT_GET_RETURN_CODE(audit_id);end;
WB_RT_GET_START_TIME(audit_id)
This function returns the start time for the job execution represented by audit_id. It returns null if audit_id does not exist. For example, you can use this in a transition if you wanted to make a choice dependent on when the previous activity started.
The following example determines the start time of the job execution whose audit ID is 354.
declare audit_id NUMBER:=354; l_date TIMESTAMP WITH TIME ZONE;begin l_date := WB_RT_GET_START_TIME(audit_id);end;
The conversion transformations enable Warehouse Builder users to perform functions that allow conditional conversion of values. These functions achieve "if -then" constructions within SQL.
The conversion transformations that Warehouse Builder implements from the SQL conversion functions are as follows:
ASCIISTR
COMPOSE
CONVERT
HEXTORAW
NUMTODSINTERVAL
NUMTOYMINTERVAL
RAWTOHEX
RAWTONHEX
SCN_TO_TIMESTAMP
TIMESTAMP_TO_SCN
TO_BINARY_DOUBLE
TO_BINARY_FLOAT
TO_CHAR (character), TO_CHAR (datetime), TO_CHAR (number)
TO_CLOB
TO_DATE
TO_DSINTERVAL
TO_MULTIBYTE
TO_NCHAR (character), TO_NCHAR (datetime), TO_NCHAR (number)
TO_NCLOB
TO_NUMBER
TO_SINGLE_BYTE
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
UNISTR
For descriptions and examples of these transformations, see "Conversion Functions" in the Oracle Database SQL Language Reference.
Date transformations provide Warehouse Builder users with functionality to perform transformations on date attributes. These transformations include SQL functions that are implemented by Warehouse Builder and custom functions provided with Warehouse Builder. The custom function are in the format WB_<function name>.
Following are the date transformations that are implementations of Database SQL functions:
ADD_MONTHS
CURRENT_DATE
DBTIMEZONE
FROM_TZ
LAST_DAY
MONTHS_BETWEEN
NEW_TIME
NEXT_DAY
ROUND
SESSIONTIMEZONE
SYSDATE
SYSTIMESTAMP
SYS_EXTRACT_UTC
TRUNC
For descriptions and examples of these transformations, refer to the section "Datetime Functions" in the Oracle Database SQL Language Reference.
The custom Date transformations are:
WB_CAL_MONTH_NAME(attribute)
The function call returns the full-length name of the month for the date specified in attribute.
The following example shows the return value on the sysdate and on a specified date string:
SELECT WB_CAL_MONTH_NAME(sysdate)
FROM DUAL;
WB_CAL_MONTH_NAME(SYSDATE)
----------------------------
March
SELECT WB_CAL_MONTH_NAME('26-MAR-2002')
FROM DUAL;
WB_CAL_MONTH_NAME('26-MAR-2002')
----------------------------------
March
WB_CAL_MONTH_OF_YEAR(attribute)
WB_CAL_MONTH_OF_YEAR returns the month (1 to 12) of the year for date in attribute.
The following example shows the return value on the sysdate and on a specified date string:
SELECT WB_CAL_MONTH_OF_YEAR(sysdate) month
FROM DUAL;
MONTH
----------
3
SELECT WB_CAL_MONTH_OF_YEAR('26-MAR-2002') month
FROM DUAL;
MONTH
----------
3
WB_CAL_MONTH_SHORT_NAME(attribute)
WB_CAL_MONTH_SHORT_NAME returns the short name of the month (for example 'Jan') for date in attribute.
The following example shows the return value on the sysdate and on a specified date string:
SELECT WB_CAL_MONTH_SHORT_NAME (sysdate) month
FROM DUAL;
MONTH
---------
Mar
SELECT WB_CAL_MONTH_SHORT_NAME ('26-MAR-2002') month
FROM DUAL;
MONTH
---------
Mar
WB_CAL_QTR(attribute)
WB_CAL_QTR returns the quarter of the Gregorian calendar year (for example Jan - March = 1) for the date in attribute.
The following example shows the return value on the sysdate and on a specified date string:
SELECT WB_CAL_QTR (sysdate) quarter
FROM DUAL;
QUARTER
----------
1
SELECT WB_CAL_QTR ('26-MAR-2002') quarter
FROM DUAL;
QUARTER
----------
1
WB_CAL_WEEK_OF_YEAR(attribute)
WB_CAL_WEEK_OF_YEAR returns the week of the year (1 to 53) for the date in attribute.
The following example shows the return value on the sysdate and on a specified date string:
SELECT WB_CAL_WEEK_OF_YEAR (sysdate) w_of_y
FROM DUAL;
W_OF_Y
----------
13
SELECT WB_CAL_WEEK_OF_YEAR ('26-MAR-2002') w_of_y
FROM DUAL;
W_OF_Y
----------
13
WB_CAL_YEAR(attribute)
WB_CAL_YEAR returns the numerical year component for the date in attribute.
The following example shows the return value on the sysdate and on a specified date string:
SELECT WB_CAL_YEAR (sysdate) year
FROM DUAL;
YEAR
----------
2002
SELECT WB_CAL_YEAR ('26-MAR-2002') w_of_y
FROM DUAL;
YEAR
----------
2002
WH_CAL_YEAR_NAME(attribute)
WB_CAL_YEAR_NAME returns the spelled out name of the year for the date in attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_CAL_YEAR_NAME (sysdate) name
from dual;
NAME
----------------------------------------------
Two Thousand Two
select WB_CAL_YEAR_NAME ('26-MAR-2001') name
from dual;
NAME
----------------------------------------------
Two Thousand One
WB_DATE_FROM_JULIAN(attribute)
WB_DATE_FROM_JULIAN converts Julian date attribute to a regular date.
The following example shows the return value on a specified Julian date:
select to_char(WB_DATE_FROM_JULIAN(3217345),'dd-mon-yyyy') JDate from dual; JDATE ----------- 08-sep-4096
WB_DAY_NAME(attribute)
WB_DAY_NAME returns the full name of the day for the date in attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_DAY_NAME (sysdate) name
from dual;
NAME
--------------------------------------------
Thursday
select WB_DAY_NAME ('26-MAR-2002') name
from dual;
NAME
--------------------------------------------
Tuesday
WB_DAY_OF_MONTH(attribute)
WB_DAY_OF_MONTH returns the day number within the month for the date in attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_DAY_OF_MONTH (sysdate) num
from dual;
NUM
----------
28
select WB_DAY_OF_MONTH ('26-MAR-2002') num
from dual
NUM
----------
26
WB_DAY_OF_WEEK(attribute)
WB_DAY_OF_WEEK returns the day number within the week for date attribute based on the database calendar.
The following example shows the return value on the sysdate and on a specified date string:
select WB_DAY_OF_WEEK (sysdate) num
from dual;
NUM
----------
5
select WB_DAY_OF_WEEK ('26-MAR-2002') num
from dual;
NUM
----------
3
WB_DAY_OF_YEAR(attribute)
WB_DAY_OF_YEAR returns the day number within the year for the date attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_DAY_OF_YEAR (sysdate) num
from dual;
NUM
----------
87
select WB_DAY_OF_YEAR ('26-MAR-2002') num
from dual;
NUM
----------
85
WB_DAY_SHORT_NAME(attribute)
WB_DAY_SHORT_NAME returns the three letter abbreviation or name for the date attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_DAY_SHORT_NAME (sysdate) abbr
from dual;
ABBR
-------------------------------------
Thu
select WB_DAY_SHORT_NAME ('26-MAR-2002') abbr
from dual;
NUM
-------------------------------------
Tue
WB_DECADE(attribute)
WB_DECADE returns the decade number within the century for the date attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_DECADE (sysdate) dcd
from dual;
DCD
----------
2
select WB_DECADE ('26-MAR-2002') DCD
from dual;
DCD
----------
2
WB_HOUR12(attribute)
WB_HOUR12 returns the hour (in a 12-hour setting) component of the date corresponding to attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_HOUR12 (sysdate) h12
from dual;
H12
----------
9
select WB_HOUR12 ('26-MAR-2002') h12
from dual;
H12
----------
12
Note:
For a date not including the timestamp (in the second example), Oracle uses the 12:00 (midnight) timestamp and therefore returns 12 in this case.WB_HOUR12MI_SS(attribute)
WB_HOUR12MI_SS returns the timestamp in attribute formatted to HH12:MI:SS.
The following example shows the return value on the sysdate and on a specified date string:
select WB_HOUR12MI_SS (sysdate) h12miss
from dual;
H12MISS
-------------------------------------
09:08:52
select WB_HOUR12MI_SS ('26-MAR-2002') h12miss
from dual;
H12MISS
-------------------------------------
12:00:00
Note:
For a date not including the timestamp (in the second example), Oracle uses the 12:00 (midnight) timestamp and therefore returns 12 in this case.WB_HOUR24(attribute)
WB_HOUR24 returns the hour (in a 24-hour setting) component of date corresponding to attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_HOUR24 (sysdate) h24
from dual;
H24
----------
9
select WB_HOUR24 ('26-MAR-2002') h24
from dual;
H24
----------
0
Note:
For a date not including the timestamp (in the second example), Oracle uses the 00:00:00 timestamp and therefore returns the timestamp in this case.WB_HOUR24MI_SS(attribute)
WB_HOUR24MI_SS returns the timestamp in attribute formatted to HH24:MI:SS.
The following example shows the return value on the sysdate and on a specified date string:
select WB_HOUR24MI_SS (sysdate) h24miss
from dual;
H24MISS
------------------------------------
09:11:42
select WB_HOUR24MI_SS ('26-MAR-2002') h24miss
from dual;
H24MISS
------------------------------------
00:00:00
Note:
For a date not including the timestamp (in the second example), Oracle uses the 00:00:00 timestamp and therefore returns the timestamp in this case.WB_IS_DATE(attribute, fmt)
To check whether attribute contains a valid date. The function returns a Boolean value which is set to true if attribute contains a valid date. Fmt is an optional date format. If fmt is omitted, then the date format of your database session is used.
You can use this function when you validate your data before loading it into a table. This way the value can be transformed before it reaches the table and causes an error.
WB_IS_DATE returns true in PL/SQL if attribute contains a valid date.
WB_JULIAN_FROM_DATE(attribute)
WB_JULIAN_FROM_DATE returns the Julian date of date corresponding to attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_JULIAN_FROM_DATE (sysdate) jdate
from dual;
JDATE
----------
2452362
select WB_JULIAN_FROM_DATE ('26-MAR-2002') jdate
from dual;
JDATE
----------
2452360
WB_MI_SS(attribute)
WB_MI_SS returns the minutes and seconds of the time component in the date corresponding to attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_MI_SS (sysdate) mi_ss
from dual;
MI_SS
-------------------------------------------
33:23
select WB_MI_SS ('26-MAR-2002') mi_ss
from dual;
MI_SS
-------------------------------------------
00:00
Note:
For a date not including the timestamp (in the second example), Oracle uses the 00:00:00 timestamp and therefore returns the timestamp in this case.WB_WEEK_OF_MONTH(attribute)
WB_WEEK_OF_MONTH returns the week number within the calendar month for the date corresponding to attribute.
The following example shows the return value on the sysdate and on a specified date string:
select WB_WEEK_OF_MONTH (sysdate) w_of_m
from dual;
W_OF_M
----------
4
select WB_WEEK_OF_MONTH ('26-MAR-2002') w_of_m
from dual;
W_OF_M
----------
4
Number transformations provide Warehouse Builder users with functionality to perform transformations on numeric values. These include Database SQL functions that are implemented by Warehouse Builder and custom functions defined by Warehouse Builder. The custom functions are prefixed with WB_.
Table 28-2 lists the number transformations that are based on Database SQL numeric functions. The transformations are listed in a columnar table that reads down the columns from left to right to conserve space.
Table 28-2 List of Number Transformations Based on Database SQL Functions
| Number Transformation Name | Number Transformation Name (Contd.) | Number Transformation Name (Contd.) |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
For descriptions and examples of these transformations, refer to the section titled "Numeric Functions" in the Oracle Database SQL Language Reference.
The custom numeric transformations are:
WB_LOOKUP_NUM (table_name , column_name , key_column_name , key_value )
where table_name is the name of the table to perform the lookup on; column_name is the name of the NUMBER column that will be returned, for instance, the result of the lookup; key_column_name is the name of the NUMBER column used as the key to match on in the lookup table; key_value is the value of the key column, for example, the value mapped into the key_column_name with which the match will be done.
To perform a key look up that returns a NUMBER value from a database table using a NUMBER column as the matching key.
Consider the following table as a lookup table LKP1:
KEYCOLUMN TYPE_NO TYPE 10 100123 Car 20 100124 Bike
Using this package with the following call:
WB_LOOKUP_CHAR('LKP1'
, 'TYPE_NO'
, 'KEYCOLUMN'
, 20
)
returns the value of 100124 as output of this transformation. This output is then processed in the mapping as the result of an inline function call.
Note:
This function is a row-based key lookup. Set-based lookups are supported when you use the Lookup operator.WB_LOOKUP_CHAR(table_name , column_name , key_column_name , key_value )
where table_name is the name of the table to perform the lookup on; column_name is the name of the NUMBER column that will be returned (such as the result of the lookup); key_column_name is the name of the NUMBER column used as the key to match on in the lookup table; key_value is the value of the key column, such as the value mapped into the key_column_name with which the match will be done.
To perform a key lookup which returns a NUMBER value from a database table using a VARCHAR2 column as the matching key.
Consider the following table as a lookup table LKP1:
KEYCOLUMN TYPE_NO TYPE ACV 100123 Car ACP 100124 Bike
Using this package with the following call:
WB_LOOKUP_CHAR ('LKP1'
, 'TYPE'
, 'KEYCOLUMN'
, 'ACP'
)
returns the value of 100124 as output of this transformation. This output is then processed in the mapping as the result of an inline function call.
Note:
This function is a row-based key lookup. Set-based lookups are supported when you use the Lookup operator described in "Lookup Operator".WB_IS_NUMBER(attibute, fmt)
To check whether attribute contains a valid number. The function returns a Boolean value, which is set to true if attribute contains a valid number. Fmt is an optional number format. If fmt is omitted, then the number format of your session is used.
You can use this function when you validate the data before loading it into a table. This way the value can be transformed before it reaches the table and causes an error.
WB_IS_NUMBER returns true in PL/SQL if attribute contains a valid number.
OLAP transformations enable Warehouse Builder users to load data stored in relational dimensions and cubes into an analytic workspace.
The OLAP transformations provided by Warehouse Builder are:
The WB_OLAP_LOAD_CUBE, WB_OLAP_LOAD_DIMENSION, and WB_OLAP_LOAD_DIMENSION_GENUK transformations are used for cube cloning in Warehouse Builder. Use these OLAP transformations only if your database version is Oracle Database 9i or Oracle Database 10g Release 1. Starting with Oracle 10g Release 2, you can directly deploy dimensions and cubes into an analytic workspace.
The WB_OLAP_AW_PRECOMPUTE only works with the Oracle Warehouse Builder 10g Release 2.
The examples used to explain these OLAP transformations are based on the scenario depicted in Figure 28-1.
Figure 28-1 Example of OLAP Transformations
The relational dimension TIME_DIM and the relational cube SALES_CUBE are stored in the schema WH_TGT. The analytic workspace AW_WH, into which the dimension and cube are loaded, is also created in the WH_TGT schema.
WB_OLAP_AW_PRECOMPUTE(p_aw_name, p_cube_name, p_measure_name, p_allow_parallel_ solve, p_max_job_queues_allocated)
where p_aw_name is the name of the AW where cube is deployed, p_cube_name is the name of the cube to solve, p_measure_name is the optional name of a specific measure to solve (if no measure is specified, then all measures will be solved), p_allow_parallel_solve is the boolean to indicate parallelization of solve based on partitioning (performance related parameter), p_max_job_queues_allocated is the number of DBMS jobs to execute in parallel (default value is 0). If 5 is defined and there are 20 partitions, then a pool of 5 DBMS jobs will be used to perform the data load.There is a subtle different between parallel and non-parallel solving. With non-parallel solve, the solve happens synchronously, so when the API call is completed the solve is complete. Parallel solve executes asynchronously, the API call will return with a job id of the job started. The job will control parallel solving using the max job queues parameter to control its processing. The user may then use the job id to query the all_scheduler_* views to check on the status of the activity.
WB_OLAP_AW_PRECOMPUTE is used for solving a non-compressed cube (compressed cubes are auto-solved). The load and solve steps can be done independently. By default, the cube map loads data, then solves (precomputes) the cube. You can load data using the map, then perform the solve at a different point of time (since the solve/build time is the costliest operation).
The following example loads data from the relational cubes MART and SALES_CUBE into a cube called SALES and performs a simple solve execution working serially. This example has parameters for parallel solve and max number of job queues. If parallel solve is performed, then an ASYNCHRONOUS solve job is started and the master job ID is returned via the return function.
declare
rslt varchar2(4000);
begin
…
rslt :=wb_olap_aw_precompute('MART','SALES_CUBE','SALES');
…
end;
/
wb_olap_load_cube::=WB_OLAP_LOAD_CUBE(olap_aw_owner, olap_aw_name, olap_cube_owner, olap_cube_name, olap_tgt_cube_name)
where olap_aw_owner is the name of the database schema that owns the analytic workspace; olap_aw_name is the name of the analytic workspace that stores the cube data; olap_cube_owner is the name of the database schema that owns the related relational cube; olap_cube_name is the name of the relational cube; olap_tgt_cube_name is the name of the cube in the analytic workspace.
WB_OLAP_LOAD_CUBE loads data from the relational cube into the analytic workspace. This allows further analysis of the cube data. This is for loading data in an AW cube from a relational cube which it was cloned from. This is a wrapper around some of the procedures in the DBMS_AWM package for loading a cube.
The following example loads data from the relational cube SALES_CUBE into a cube called AW_SALES in the AW_WH analytic workspace:
WB_OLAP_LOAD_CUBE('WH_TGT', 'AW_WH', 'WH_TGT', 'SALES_CUBE', 'AW_SALES')
wb_olap_load_dimension::=WB_OLAP_LOAD_DIMENSION(olap_aw_owner, olap_aw_name, olap_dimension_owner, olap_dimension_name, olap_tgt_dimension_name)
where olap_aw_owner is the name of the database schema that owns the analytic workspace; olap_aw_name is the name of the analytic workspace that stores the dimension data; olap_dimension_owner is the name of the database schema in which the related relational dimension is stored; olap_dimension_name is the name of the relational dimension; olap_tgt_dimension_name is the name of the dimension in the analytic workspace.
WB_OLAP_LOAD_DIMENSION loads data from the relational dimension into the analytic workspace. This allows further analysis of the dimension data. This is for loading data in an AW dimension from a relational dimension which it was cloned from. This is a wrapper around some of the procedures in the DBMS_AWM package for loading a dimension.
The following example loads the data from the relational dimension TIME_DIM into a dimension called AW_TIME in the analytic workspace AW_WH:
WB_OLAP_LOAD_DIMENSION('WH_TGT', 'AW_WH', 'WH_TGT', 'TIME_DIM', 'AW_TIME')
wb_olap_load_dimension_genuk::=WB_OLAP_LOAD_DIMENSION_GENUK(olap_aw_owner, olap_aw_name, olap_dimension_owner, olap_dimension_name, olap_tgt_dimension_name)
where olap_aw_owner is the name of the database schema that owns the analytic workspace; olap_aw_name is the name of the analytic workspace that stores the dimension data; olap_dimension_owner is the name of the database schema in which the related relational dimension is stored; olap_dimension_name is the name of the relational dimension; olap_tgt_dimension_name is the name of the dimension in the analytic workspace.
WB_OLAP_LOAD_DIMENSION_GENUK loads data from the relational dimension into the analytic workspace. Unique dimension identifiers will be generated across all levels. This is for loading data in an AW dimension from a relational dimension which it was cloned from. This is a wrapper around some of the procedures in the DBMS_AWM package for loading a dimension.
If a cube has been cloned and if you select YES for the Generate Surrogate Keys for Dimensions option, then when you want to reload the dimensions, you should use the WB_OLAP_LOAD_DIMENSION_GENUK procedure. This procedure generates surrogate identifiers for all levels in the AW, because the AW requires all level identifiers to be unique across all levels of a dimension.
Consider an example in which the dimension TIME_DIM has been deployed to the OLAP server by cloning the cube. The parameter generate surrogate keys for Dimension was set to true. To now reload data from the relational dimension TIME_DIM into the dimension AW_TIME in the analytic workspace AW_WH, use the following syntax.
WB_OLAP_LOAD_CUBE('WH_TGT', 'AW_WH', 'WH_TGT', 'TIME_DIM', 'AW_TIME')
Other transformations included with Warehouse Builder enable you to perform various functions which are not restricted to certain data types. This section describes those types.
Other transformations provided by Warehouse Builder are:
DEPTH
DUMP
EMPTY_BLOB
EMPTY_CLOB
NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID
NLS_CHARSET_NAME
NULLIF
NVL
NVL2
ORA_HASH
PATH
SYS_CONTEXT
SYS_GUID
SYS_TYPEID
UID
USER
USERENV
VSIZE
For descriptions and examples of these transformations, see Oracle Database SQL Language Reference.
Spatial Transformation is an integrated set of functions and procedures that enables spatial data to be stored, accessed, and analyzed quickly and efficiently in an Oracle Database.
Spatial transformations included with Warehouse Builder are:
SDO_AGGR_CENTROID
SDO_AGGR_CONVEXHULL
SDO_AGGR_MBR
SDO_AGGR_UNION
For descriptions and examples of these transformations, refer to the Oracle Spatial Developer's Guide.
The Streams transformations category contains one transformation called REPLICATE. The following section describes this transformation.
REPLICATE(lcr, conflict_resolution)
where lcr stands for Logical Change Record and encapsulates the DML change. Its data type is SYS.LCR$_ROW_RECORD. conflict_resolution is a Boolean variable. If its value is TRUE, then any conflict resolution defined for the table will be used to resolve conflicts resulting from the execution of the LCR. For more information about conflict resolution, see Oracle Streams Replication Administrator's Guide.
REPLICATE is used to replicate a DML change (INSERT, UPDATE, or DELETE) that has occurred on a table in the source system on an identical table in the target system. The table in the target system should be identical to the table in the source system in the following respects:.
The name of the schema that contains the target table should be the same as the name of the schema that contains the source table.
The name of the target table should the same as the name of the source table.
The structure of the target table should be the same as that of the source table. The structure includes the number, name, and data type of the columns in the table.
Consider a table T1(c1 varchar2(10), c2 number primary key) in schema S on the source system and an identical table in the target system. Consider the following insert operation on the table T1 on the source system
insert into T1 values ('abcde', 10)
An LCR representing the change following the above insert of a row on the table T1 in the source system will have the following details
LCR.GET_OBJECT_OWNER will be 'S'
LCR.GET_OBJECT_NAME will be 'T1'
LCR.GET_COMMAND_TYPE will be 'INSERT'
LCR.GET_VALUE('c1', 'new') will have the value for the column 'c1' - i.e. 'abcde'
LCR.GET_VALUE('c2', 'new') will have the value for the column 'c2' - i.e. 10
Such an LCR will be created and enqueued by a Streams Capture Process on the source system that captures changes on table S.T1
REPLICATE(lcr, true) - will result in a row ('abcde', 10) being inserted into the table T1 on the target system.
Note:
Using this approach will not provide lineage information. If lineage is important, then do not use this function. Use the more direct approach of using an LCR Cast operator bound to the source table and a Table operator bound to the target table and connecting the attributes of these two operators with the same name ('Match by name'). Further information about LCR (Logical Change Record) is available in Oracle Database 10g Documentation.XML transformations provide Warehouse Builder users with functionality to perform transformations on XML objects. These transformations enable Warehouse Builder users to load and transform XML documents and Oracle AQs.
To enable loading of XML sources, Warehouse Builder provides access to the database XML functionality by implementing database XML functions and by defining custom functions.
Following are the XML transformations that are implemented based on database XML functions:
EXISTSNODE
EXTRACT
EXTRACTVALUE
SYS_XMLAGG
SYS_XMLGEN
XMLCONCAT
XMLSEQUENCE
XMLTRANSFORM
See Also:
Oracle Database SQL Language Reference for descriptions for these transformations
Oracle Spatial Developer's Guide for examples on using these transformations
The custom XML transformations are:
WB_XML_LOAD(control_file)
This program unit extracts and loads data from XML documents into database targets. The control_file, an XML document, specifies the source of the XML documents, the targets, and any runtime controls. After the transformation has been defined, a mapping in Warehouse Builder calls the transformation as a pre-map or post-map trigger.
The following example illustrates a script that can be used to implement a Warehouse Builder transformation that extracts data from an XML document stored in the file products.xml and loads it into the target table called books:
begin
wb_xml_load('<OWBXMLRuntime>'
||
'<XMLSource>'
||
' <file>\ora817\GCCAPPS\products.xml</file>'
||
'</XMLSource>'
||
'<targets>'
||
' <target XSLFile="\ora817\XMLstyle\GCC.xsl">books</target>'
||
'</targets>'
||
'</OWBXMLRuntime>'
);
end;
For more information about control files, see the Oracle Warehouse Builder User's Guide.
WB_XML_LOAD_F(control_file)
WB_XML_LOAD_F extracts and loads data from XML documents into database targets. The function returns the number of XML documents read during the load. The control_file, itself an XML document, specifies the source of the XML documents, the targets, and any runtime controls. After the transformation has been defined, a mapping in Warehouse Builder calls the transformation as a pre-map or post-map trigger.
The following example illustrates a script that can be used to implement a Warehouse Builder transformation that extracts data from an XML document stored in the file products.xml and loads it into the target table books:
begin
wb_xml_load_f('<OWBXMLRuntime>'
||
'<XMLSource>'
||
' <file>\ora817\GCCAPPS\products.xml</file>'
||
'</XMLSource>'
||
'<targets>'
||
' <target XSLFile="\ora817\XMLstyle\GCC.xsl">books</target>'
||
'</targets>'
||
'</OWBXMLRuntime>'
);
end;
For more information about the types handled and detailed information about control_files, see the Oracle Warehouse Builder Installation and Administration Guide for Windows and UNIX.
|
 Copyright © 2000, 2010, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|