| Oracle® Warehouse Builder User's Guide 10g Release 1 (10.1) Part Number B12146-02 |
|
|
View PDF |
| Oracle® Warehouse Builder User's Guide 10g Release 1 (10.1) Part Number B12146-02 |
|
|
View PDF |
After you design and configure the logical definitions of your target system, you can deploy and create the physical instance of your target. You can then execute deployed mapping and process flow scripts to load or update your data. If you are working with a previously deployed system, you can view deployment history and plan an upgrade. All of these processes are all managed by a component in Warehouse Builder called the Runtime Platform Service.
This chapter contains the following topics:
Deployment is the process of creating your target system from the logical design or model. The process includes generating scripts such as DDL that create data objects such as tables, views, and dimensions. The process also includes generating PL/SQL and SQL*Loader scripts that load data into data objects. The Runtime Repository stores detailed information about every deployment. This information determines default deployment actions for future deployments. For example, when you deploy a set of objects that already exists in the target system, upgrade is the default action. You can also access deployment data reports using the Runtime Audit Browser. For more information, see Chapter 14, "Auditing Deployment and Execution".
When you deploy or upgrade a target system using Warehouse Builder, you can either use the Deployment Manager or you can deploy objects directly from the navigation tree. The Deployment Manager offers a comprehensive deployment console that enables you to view and manage all aspects of deployment including configuration and validation. The Deployment Manager also enables you to view the deployment history of an object to determine how you want to deploy the object. These options are not available if you deploy objects from the navigation tree.
Prior to deployment, you must ensure the following:
You have assigned locations for all deployable modules.
You have installed the Runtime Repository and the repository is referenced by a Runtime Repository Connection in the navigation tree.
You have defined connectors where needed.
You can define runtime repository connections, locations, and connectors at anytime prior to deployment. These objects are necessary for deployment as they define the connection information for data sources and target as well as the connection between these locations. After these are defined, you can proceed to deploying to your target system.
Runtime Repository Connections describe a connection to the Runtime Repository that represents the collection of systems and tools that comprise the target system you design. The Runtime Repository also helps to manage deployments to this collection and collects all of the audit data. These connections represent connections to the Runtime Repositories that were installed using the Runtime Assistant. For more information about the Runtime Repository, see the Oracle Warehouse Builder Installation and Configuration Guide.
When you deploy objects, you must select a Runtime Repository Connection. The physical details of the logical locations are registered within a Runtime Repository. The Runtime Repository dispatches the generated scripts to the appropriate physical location during the deployment operation. Audit data created during the deployment operation is stored in the Runtime Repository and can be displayed by using the Runtime Audit Browser. Before you can use deployment, you must install Runtime Repositories, and create Runtime Repository Connections in the navigation tree.
To create a new runtime repository connection:
Select a project and expand the navigation tree.
Select the Runtime Repository Connections node.
From the Object menu select Create Runtime Repository Connection or right-click Runtime Repository Connections and select Create Runtime Repository Connection.
The Runtime Repository Connection Wizard Welcome page displays.
Click Next.
The Name Page displays. Use this page to define the following information for the runtime repository connection:
Name: Provide a name for the runtime repository connection. The name must not exceed 30 characters.
Optional Description: Provide an optional description for the runtime repository connection. The description must not exceed 400 characters.
There are no pre-assigned default values assigned to a new runtime repository connection.
Click Next to continue.
The Details Page displays. Use this page to define the following information for the runtime repository:
Host Name: Type the name of the host machine.
Port Number: Specify the Oracle Listener port number. The default value is 1521.
Service Name: Type the service name.
Connect As User: Type a user name that has been granted access to this runtime repository.
Runtime Repository Owner: Type the name of the runtime repository for which you are creating the connection. This represents the schema name that was entered using the Runtime Assistant.
Click Next to continue.
The Finish Page displays. Use this page to verify the definition of the new runtime repository. This page lists the name, host name, port number, service name, user name, and runtime repository name.
Click Finish to create the runtime repository as defined.
The runtime repository connection is created and added under the Runtime Repository Connections node.
To edit a runtime repository connection, right-click the runtime repository connection from the navigation tree and select Properties. Warehouse Builder displays the properties window as shown in Figure 13-1.
This page displays the properties of the selected runtime repository connection. Use the following two tabs to view and edit the properties. Click OK to save changes or Cancel to close the window.
Name: Displays the runtime repository connection name. The name must not exceed 30 characters.
Description: Displays an optional description for the runtime repository connection. The description must not exceed 400 characters.
Host Name: Type the name of the host machine.
Port Number: Specify the Oracle Listener port number.
Service Name: Type the service name.
Connect As User: Type a user name that has been granted access to this runtime repository.
Runtime Repository Owner: Type the name of the runtime repository for which you are creating the connection. This represents the schema name that was entered using the Runtime Assistant.
When you are ready to deploy objects, you can either use the Deployment Manager or you can select and deploy objects from the navigation tree. Deploying objects from the navigation tree is a very simple, quick process of deployment. This method is beneficial if you want Warehouse Builder to use the default deployment actions and you do not need to view the script prior to deployment. The default deployment action is determined by changes to the object design since the object was last deployed. For example, if you deploy an altered object, the default action will be upgrade.
The Deployment Manager offers a comprehensive deployment console and allows for more flexibility in deployment options. For more information, see "Using the Deployment Manager".
To deploy objects from the navigation tree:
Select a deployable object from the navigation tree.
From the Object menu, select Deploy or right-click the object and select Deploy.
The Select Runtime Repository Connection dialog displays.
Select the Runtime Repository Connection and click OK.
Warehouse Builder generates the scripts for the selected objects and the Pre-Deployment Generation Results window displays.
Click Deploy to continue, or Cancel to cancel.
Warehouse Builder deploys the object using the default deployment settings. The Runtime Repository you select stores the data about the deployment.
The Deployment Manager offers the most flexible way to deploy objects using Warehouse Builder. When you open the Deployment Manager, you can access the design objects that exist in your current project. After the Deployment Manager is open, you can select objects from the tree and set them for deployment. Special icons appear on the tree next to these objects. After you have selected the objects and set their deployment action you can then use the Deploy button to validate the objects and generate the scripts based on their current design. These results display in the generation preview screen. You can confirm the objects you want to deploy as well as catch any errors. Complete the deployment by deploying the scripts to the target locations using the current Runtime Repository Connection.
To open the Deployment Manager:
From the Project menu, select Deployment Manager.
A dialog displays asking for you to select a Runtime Repository Connection.
Select the Runtime Repository Connection from the drop-down list and click OK.
The Connection Information dialog for the Runtime Repository you have selected displays.
Enter the password for the Runtime Repository you have selected.
The first time you connect to a specific Runtime Repository, you must verify the password. The password is then stored and you can access the repository during the Warehouse Builder session without entering it again.
The Deployment Manager opens with the current project displayed in the left window, as shown in Figure 13-2.
The Deployment Manager has the following components:
When you open the Deployment Manager, the deployment tree is on the left side. The tree is initially collapsed into a list of Locations and Collections which can be expanded to display their contents, as shown in Figure 13-3. You can use this tree to register locations, select objects for deployment, and view deployment history. Modules that do not have an associated location do not display in the Deployment Manager.
Note:
Only Oracle target warehouse locations expand to show the deployable objects contained within them. All source locations and Flat File locations display the location for registration purposes only.When you select an object in the tree, the deployable objects within that object are displayed on the right. You can select multiple objects from the deployment tree by using the Control or Shift keys.
Select a view from the drop-down list above the deployment tree to limit the objects displayed. Table 13-1 describes the columns displayed in the view selector.
Table 13-1 Views
| View Name | Objects Displayed |
|---|---|
|
All Objects |
Clears any previously selected filter and displays all objects in the deployment tree. |
|
Changed Objects |
Objects that have been changed in the Warehouse Builder Design Repository since the last deployment of the object. The deployment actions for these objects default to upgrade or replace. |
|
Deployed Objects |
Objects that have been previously deployed using the runtime repository that is currently selected. |
|
Projected Deployment Objects |
All Objects currently selected for deployment. |
The Details Tab, located on the right side of the Deployment Manager, displays a summary of information relevant to the object or set of objects selected in the deployment tree. As the status of an object changes, the Details tab reflects those changes. You can also use the buttons on the bottom of the tab to alter the Deploy Action status. The Default Action button changes the Deploy Action, and the Reset button resets the Deploy Action columns back to None if an changes have been made. Figure 13-4 shows the Deployment Window with the Details Tab displayed.
Table 13-2 describes the columns displayed in the Details tab.
Table 13-2 Detail Tab Columns
| Column | Description |
|---|---|
|
Object |
Displays the physical name of the object selected. |
|
Design Status |
Displays status information about the object stored in the design repository. Available statuses are: New: Object exists in the design repository, but has not yet been deployed. No Change: No change in status since last deployment. Deleted: Object is in the runtime but not in the design repository. Updated: Object was deployed and has been updated in the design repository since the last deployment. |
|
Deploy Action |
Action to be performed when the object is deployed. Actions include Create, Upgrade, Drop and Replace. |
|
Deployed |
The timestamp of when the object was last deployed. |
|
Deploy Status |
The current deployment status of the object in the target. |
|
Message |
Lists out any messages. |
The Default Action button automatically updates the action for the objects you select. The default action is determined by the data stored in the design and runtime repositories. The details for the objects are shown in the Details tab. The following rules are used to determine the default action:
Design objects not deployed have their action set to Create.
Previously deployed objects that have been modified since the last deployment will have their action set to Upgrade or Replace depending on how the target system is configured.
If there is no need to deploy an object, the action is set to None.
Note:
Due to the way External Tables are processed, there are no default actions available for these objects. You must manually set the Deploy Action for External Tables.The History tab displays the deployment history for the selected objects, as shown in Table 13-2.
Table 13-3 describes the columns displayed in the History tab.
Table 13-3 History Tab Columns
| Column | Description |
|---|---|
|
Object |
Displays the physical name of the object selected. |
|
Deploy Action |
Action performed when the object was deployed. Actions include Create, Upgrade, Drop and Replace. |
|
Deployed |
The timestamp of when the object was deployed. |
|
Object Status |
Displays whether the object that was deployed is valid or invalid. |
|
Deploy Status |
The current deployment status of the object in the target. |
The toolbar, as shown in Table 13-3, is located in the upper left side of the Deployment Manager contains shortcuts to a few tasks.
Table 13-4 describes these tools.
Table 13-4 Deployment Toolbar
| Icon | Action | Description |
|---|---|---|
 |
Runtime Repository Synchronize |
Synchronizes objects that display in the Deployment Manager with objects that exist in the Runtime Repository. This function is only available from within the Deployment Manager. |
 |
Design Repository Synchronize |
Synchronizes objects that display with objects that exist in the Warehouse Builder Design Repository. This is useful when there are multiple users. This provides the same function as the synchronize button in the main console. |
 |
Find |
Searches for an object within the Deployment Manager. The Object Find dialog displays and you can type in the name or part of the name of an object you are searching for. Select this icon at anytime. |
 |
Deploy |
Deploys selected objects. |
 |
Execute |
Executes the object selected in the deployment tree. This is only enabled if you have an executable object selected. |
 |
Help |
Displays the Oracle Warehouse Builder User's Guide in the online help navigator. You can select this icon at anytime. |
Before objects can be deployed successfully, all of the location being used must be registered. When you create locations during the design process, you create logical definitions which are limited to only the name, type, and version of the location. This logical information is stored and used to determine which objects can be deployed. All modules must have locations assigned to them in order for them to be used during deployment. This includes the target as well as any sources or files.
When you register locations, you specify the connection information that will be used during deployment to connect to the various data sources and targets. You only have to supply this information the first time you use the location during a deployment. The same connection information is used for future deployments unless you change it.
Database locations can be defined using either Host Name, Port Number and Service Name or by Net Service Name and Service Name. Net Service Name is a name that is defined in your tnsnames.ora file. A Net ServiceName should be defined in the appropriate Oracle home. For example, to deploy to a location identified by Net Service Name the name should be defined in the Oracle home of the Runtime Platform Service.
Database links are generated in the form <service-name>@<connector-name>. If you have global-names set to true, it is important that the service-name you specify for the location matches the global-name of the database you want to link to.
For RAC systems the service-name is typically the cluster-service name. To use RAC it is advisable that you use Net Service Names since this will give you control over some of the RAC features in the tnsnames definition, such as client-side load balancing.
To register locations:
Open the Deployment Manager, and select a Location from the deployment tree.
Note:
You can also register locations during the deployment process. During deployment, a Location Registration window will appear for each location that has not been previously registered.Select File, and then Register. You can also right-click the location and select Register.
The Location Registration window appears. Depending on the type of location, there are different connection information requirements. There are no pre-assigned default values.
Type in the location registration details and then click OK to return to the Deployment Manager. The following sections contain detailed descriptions of the required registration information for each type of location.:
Schema Name: Type the user name to logon to the database. Maximum Length: 30 Characters.
Password: Type the password for the specified user name. Maximum Length: 30 Characters.
Service Name: Type the database service name. Maximum Length: 30 Characters.
Select either Net Service Name or Host Name and provide the following connection details:
Net Service Name: Type the tnsname for the database. The tnsname contains the Service Name in the tnsnames.ora file. Maximum Length: 30 Characters.
Host Name: Type the name of the machine. Maximum Length: 30 Characters.
Port Number: Specify the Oracle Listener port number. Maximum Length: 5 Characters.
Schema: Type the owner of the database objects. Maximum Length: 30 Characters.
Connect as User: Type the user to connect to the database. Maximum Length: 30 Characters.
Password: Type the password for the specified user name. Maximum Length: 30 Characters.
Service Name: Type the database service name for non-Oracle database. Maximum Length: 30 Characters.
Select either Net Service Name or Host Name and provide the following connection details:
Net Service Name: Type the tnsname for the database. The tnsname contains the Service Name in the tnsnames.ora file. Maximum Length: 30 Characters.
Host Name: Type the name of the machine that runs the non-Oracle database. Maximum Length: 30 Characters.
Port Number: Specify the Oracle Listener port number. Maximum Length: 5 Characters.
User Name: Type the user name to connect to the operating system. This user name is used for example in case of an FTP process. Maximum Length: 30 Characters.
Password: Type the password for the specified user name. Maximum Length: 30 Characters.
Host Name: Type the name of the machine that contains the flat file or files. Maximum Length: 30 Characters.
Root Path: Specify the path to the directory in which the file or files are located. The path you specify must end in a trailing slash. For example: c:/temp/.
User Name: Type the name of an Oracle Management Server user that has job registration privileges. Typically a super-user in Oracle Enterprise Manager has the right privileges. The default super-user is SYSMAN. Maximum Length: 30 Characters.
Password: Type the password for the specified user name. Maximum Length: 30 Characters.
OMS Domain: Type the name of the node that runs the Oracle Management Server. Maximum Length: 30 Characters.
Target Agent Name: Type the name of the node that will execute the code. Maximum Length: 30 Characters.
Schema: Type the user name of the Oracle Workflow server. Maximum Length: 30 Characters.
Password: Type the password for the specified user name. Maximum Length: 30 Characters.
Service Name: Type the database service name for the Workflow server. Maximum Length: 30 Characters.
Select either Net Service Name or Host Name and provide the following connection details:
Net Service Name: Type the tnsname for the database. The tnsname contains the Service Name in the tnsnames.ora file. Maximum Length: 30 Characters.
Host Name: Type the name of the machine that runs the Workflow server. Maximum Length: 30 Characters.
Port Number: Specify the Oracle Listener port number. Maximum Length: 5 Characters.
Schema: Type the name of the user that has access to the SAP data objects. Maximum Length: 30 Characters.
Password: Type the password for the specified user name. Maximum Length: 30 Characters.
Service Name: Type the database service name for the SAP instance. Maximum Length: 30 Characters.
Select either Net Service Name or Host Name and provide the following connection details:
Net Service Name: Type the tnsname for the database. The tnsname contains the Service Name in the tnsnames.ora file. Maximum Length: 30 Characters.
Host Name: Type the name of the machine that runs SAP. Maximum Length: 30 Characters.
Port Number: Specify the Oracle Listener port number. Maximum Length: 5 Characters.
To select objects for deployment:
Open the Deployment Manager.
The Deployment Manager displays a collapsed view of the project tree in the left pane of the window.
Expand the project to display the contents. You can choose from one of the following filters to view a partial list of the objects:
Changed: Displays all objects that have changes since the last deployment.
Deployed: Displays all objects that exist in the Runtime Repository.
Projected Deployment: Displays all objects that have been deployed and that are currently selected for deployment.
Select an object or set of objects you want to deploy. The details about the object or objects display in the Details tab on the right pane of the window.
To select an object for initial deployment, click the Deploy Action column and choose Create from the drop-down list.
If this is not you initial deployment you can select another action. Actions include Create, Upgrade, Drop and Replace.
Alternatively, you can click Default Action. This will change the Deploy Action for each object to a default value. If the object has not been previously deployed, the default action is Create.
Continue this process for all objects you want to deploy. Special icons display on the deployment tree next to the objects selected for deployment.
Note:
All deploy actions are not available for all objects even though they appear on the drop-down list. For example, you cannot select Upgrade as the deploy action for mappings. If you want to replace old mappings you must use the Replace action.The Deployment Manager enables you to view the deployment history of objects within the project. This can be useful when making decisions about upgrades.
To view deployment history:
Open the Deployment Manager.
Select the History Tab.
From here you can select items on the tree to display the deployment history.
After you have selected the objects you want to deploy, there are a few steps to complete the deployment.
To complete deployment:
From the File menu, select Deploy or click the Deploy icon in the toolbar.
If this is the first time you are deploying objects to a Location, you will be asked for physical details of the Location.
Specify the physical connection details for the locations and press OK.
The Pre Deployment Generation Results page displays.
You can view the generated script by selecting the script in the lower half of the dialog and clicking View Code.
Click Deploy to confirm the deployment.
The deployment is completed and the Deployment Results display.
Review the Deployment Results and click OK.
The deployment is now finalized. The deployment action and status for the objects you deployed are updated in the Deployment Manager.
In some cases you may find it helpful to save all deployment scripts for a given deployment. You can do that using the Deployment Manager.
To save deployment scripts:
When you are deploying objects, a Deployment Preview page displays prior to deployment completion. From this page, you can save the individual file or the entire deployment specification.
You can execute two types of objects after deployment: mappings and process flows. The most direct way to execute mappings and process flows designed and deployed using Warehouse Builder is to use the Deployment Manager. After you deploy process flows or mappings to your target system, they are available for execution. You can select a mapping or process flow from the project tree within the Deployment Manager and then execute it.
To select an object for execution:
Open the Deployment Manager and expand the project containing the object you want to execute.
The Deployment Manager displays an expanded view of the project tree in the left pane of the window. All objects are listed under their assigned Locations. Expand the locations node to view objects.
Using the deployment tree filter, select Deployed Objects.
This limits the objects displayed on the deployment tree to only objects that have been deployed.
Select a mapping or process flow from the deployment tree.
The selected object displays in Detail tab on the right side of the Deployment Manager.
Note:
Only one mapping or process flow can be executed at a time.From the File menu select Execute, or click the Execute button on the toolbar.
The Execution Dialog displays.
Specify the Run Name and Parameters for the execution.
Click OK.
The execution is performed and the results display.
Review the results and click OK.
This completes the execution and you can proceed to execute other mappings or process flows.
With Warehouse Builder, you have two main options for executing process flows: you can execute them from within Warehouse Builder using the Deployment Manager as described earlier, or you can execute them from Oracle Workflow. In addition, you can use Warehouse Builder to integrate with Oracle Enterprise Manager to schedule these process flow executions.
For information about Oracle Workflow, see the Oracle Workflow Guide. For information about Oracle Enterprise Manager, see the Oracle Enterprise Manager Administrator's Guide.
You can also execute process flows from SQL*Plus.
In addition executing deployed objects using the Deployment Manager, you can also use SQL*Plus. In order to do this, you can use a script provided withWarehouse Builder called sqlplus_exec_template.sql. This script is located in the following location: <OWB home>\owb\rtp\sql
To execute Warehouse Builder generated scripts in SQL*Plus:
Open a SQL*Plus session and run the sqlplus_exec_template.sql script from the SQL prompt using the following syntax:
@sqlplus_exec_template.sql rt_owner location_name {PLSQL |SQL_LOADER | PROCESS} task_name system_params custom_params
Use the following definitions to determine what value to use for each variable in the script syntax:
rt_owner: The runtime repository owner.
location_name: For PL/SQL mappings or Process Flows, use the name of the location you used to deploy the mappings. For SQL*Loader mappings, you must set this value to PlatformSchema. This is a case-sensitive variable.
{PLSQL | SQL_LOADER | PROCESS}: Choose one of these to define the object type.
task_name: Name of the mapping or process flow.
system_params: Comma separated, enclosed by double quotes; comma and\ can be escaped by \.
custom_params: If you have mapping input parameters, this is where they need to be defined. Use the same format as system_params.
In order to use this script to run multiple mappings, you need to run this same script multiple times.
For example, the following would be used to execute a PL/SQL mapping called MY_MAPPING from the location MY_WAREHOUSE with the runtime owner being MY_RUNTIME and with no system or custom parameters:
@sqlplus_exec_template.sql MY_RUNTIME MY_WAREHOUSE PLSQL MY_MAPPING "," ","The following would be used to execute a SQL*Loader mapping called MY_LOAD with the runtime owner being MY_RUNTIME and with no system or custom parameters. Note that you do not define the location_name as the location you used during deployment. You must use the case-sensitive variable PlatformSchema.@sqlplus_exec_template.sql MY_RUNTIME PlatformSchema SQL_LOADER MY_LOAD "," ","
In addition to running mappings and process flows from Warehouse Builder, you can use Warehouse Builder to create jobs in Oracle Enterprise Manager (OEM) that you can schedule to run at specific times or schedules.
Before you can begin scheduling, the following must be complete:
An Oracle Enterprise Manager (OEM) repository has been created.
Preferred credentials have been set, both on the node and on the database.
You must be logged on as an OEM user that has operating system privileges to run batch jobs.
The OEM Intelligent Agent must be running on the node that has the Runtime Platform Service installed.
The Oracle Management Server (OMS) must have been started.
The mappings and process flows you want to schedule have been deployed.
To schedule a mapping or process flow in OEM:
Open Warehouse Builder and verify the Runtime Repository Connection details. You must be connecting as a runtime repository user, and logging into the runtime repository owner.
Open the Deployment Manager and verify the location, name, and deployment status of the objects you want to schedule.
The deployment status should be Successful.
Start the Enterprise Manager Console and login to the Oracle Management Server as a superuser. Sysman is the default superuser.
From the Job menu, select Create Job or press Control+J.
The Create Job window opens and displays 5 tabs.
Specify the name of the Job on the General tab and then select a target type and then a specific target from the available targets.
Use the Add button to move the selected target to the Selected Targets column.
Select the Tasks tab and select Run SQL*Plus Script from the Available Tasks list. Use the Add button to move it over.
Select the Parameters tab and provide the parameters.
You can either import the script oem_exec_template that is provided as part of the Oracle Warehouse Builder or you can copy and paste the content of the script.
Import a script oem_exec_template that is shipped as part of Oracle Warehouse Builder.
The script is located in: <OWB home>\owb\rtp\sql
Browse to the folder and open the script. The script will appear in the Script Text field. The mandatory parameters for the script are as follows:
Runtime Repository Owner
Target location
Target Type: either one of the PL/SQL, SQL_LOADER, or PROCESS.
Task name (name of the mapping/process flow)
System parameters (comma separated, enclosed by double quotes; comma and \ can be escaped by \)
Custom parameters (see system parameters)
The script has to run under the runtime access user. These can be set as preferred credentials. Make sure you have the right credentials.
Submit the Job directly, add it to the job library to be scheduled in the future, or do both.
