Moving to a new environment

August 10, 2010 Leave a comment

If you’re like us, you plan on using OWB in more than just a production environment – it is an enterprise tool after all. So, after we’ve created a nice set of ETLs in a development environment, how do we get them into a test environment? Below are steps we’ve found to work for exporting and importing an OWB project.

Copy from MY_PROJECT
If you have used or renamed the default OWB project called “MY_PROJECT”, create a new project and copy your modules from the default project to the non-default project. Right-click on the module, select copy, go into the new project, click paste, and wait several minutes. (This is necessary to avoid the import errors such as “API0027: Attempting to modify a read-only object: DEFAULT_DEPLOYMENT!”)

We do not recommended using the default project “MY_PROJECT” that is present the first time the Design Center runs. If the default project is used (even if it is renamed), errors will occur when importing the project.

However, if the module created in a default project is first copy/pasted into a non-default project, then the import to a non-default project succeeds. IMHO, Oracle should state this in any documentation that says in effect that the default project can be used. Because of this import limitation, and because there are no benefits to using the default project, a new project should always be created and MY_PROJECT should be deleted.

Export the project in the source environment
Log in to OWB Design Center as the workspace owner (normal workspace users don’t have permissions to export locations)
Highlight the source project
Go to File > Export > Warehouse Builder Metadata
Select Export all object dependencies
Click Show Details to ensure the project exports without errors

Import the project’s modules in the destination environment
Open Design Center
Create a new project with the same name as the project in your source environment
Expand the new project and click save
Highlight the new project
Right click the new project
Select Import > Warehouse Builder Metadata
Browse to your exported MDL file
Under Object selection, select Import selected objects from file
Click Select Object… and slide everything under the project into selected objects
Select Merge
Select Match by Names
Click OK
Look through the details of the import
It may skip importing some objects, which can be handled with further imports, but make sure there aren’t any errors
After this is complete there are mappings in the destination OWB environment, but they cannot be deployed yet since the imported locations are not registered yet

Register the new locations
Go to the Locations Navigator, and expand Control Centers
Double click the control center listed to open it
Go to Data Locations
Slide the available locations into Selected Locations
Click OK
Now go to Tools > Control Center Manager
Right click on the new location and select Register…
Update the connection details for the new environment
Test the connection
Click OK

Done. Now the imported mappings and objects are now deployable in the new environment!

Advertisements
Categories: Deploying, Importing

Connecting OWB to SQL Server Express

August 10, 2010 2 comments

There are multiple ways to connect to SQL Server or MSSQL through OWB. Depending on licensing, ODBC might be the best option – that capability does not require extra licensing. If your license includes a transparent gateway agent, then you can go that route. This has been documented for 11gR1 in Oracle Warehouse Builder 11g Getting Started by Bob Griesemer.

I haven’t investigated the ODBC approach, or the use of transparent gateway agents, but I do have OWB working with SQL Server Express using JDBC.

How to connect OWB to SQL Server Express

In the SQL Server Configuration Manager:
Expand SQL Server Network Configuration > Protocols
Enable TCP/IP

In SSMS:
Expand the SQL Server instance tree
Expand Security > Logins and create a new login
Expand Databases > your database > Security > Users and add a new login to Users
Now right click on the SQL Server instance > Properties
Select Security
Select SQL Server and Windows Authentication mode
Hit OK
Restart the SQL Server instance so these changes take effect

In a browser:
Download the JDBC driver from Microsoft
Unzip and place sqljdbc.jar in ORACLE_HOME\owb\lib\ext

In OWB:
Add a new SQL Server location
Fill in your connection info and credentials
Go to Advanced
Set the Url to be in this format:
jdbc:sqlserver://[MACHINE]\SQLEXPRESS/Databases/[DBNAME]:1433;
Test the connection – you should immediately get a pop-up saying “Successful!”

That’s it – now the tables and objects in the SQL Server instance can be imported in OWB.

Categories: SQL Server

OMB Runtime Repository

July 30, 2010 2 comments

The scripting tool OMB*Plus can in theory be used to do anything that you can do with OWB, but in a scripted fashion. I am having an issue deploying a table under certain conditions (I’ll write a future post about that). I investigated using OMB as a work-around. Though initially I had trouble using OMB with 11g R2 for this task, but I eventually got it to work.

In researching OMB on the web, I found references to the command “OMBCREATE RUNTIME_REPOSITORY_CONNECTION”. Attempting to use this command did not work for me, but every web page seemed to say it was needed. However, I finally found this bit of 10g R2 documentation: “OMBCREATE CONTROL_CENTER – This command provides methods for connecting to a control center. This command replaces OMBCREATE RUNTIME_REPOSITORY.”

The errors I was getting seemed to be contradictory. We have to connect to OWB workspace to navigate the project, but then OMB said it wasn’t connected when I gave the command to deploy the table. As it turns out, simply connecting to the workspace is not enough. You must also connect to the control center, though you don’t need to specify any credentials to connect to it. Interesting.

These are the errors that came up:
OMB05608: Connection to a Control Center has not been made.
OMB00009: OMBCONNECT is not allowed when running from UI client.

So here’s the complete syntax for getting 11g R2 OMB*Plus to deploy a table using the command prompt started outside the Design Center:

OMB+> OMBCONNECT user/password@host:1521:service USE WORKSPACE 'workspace_owner.workspace_name'
Connected.
OMB+> OMBLIST PROJECTS
PROJECT_1 PROJECT_2
OMB+> OMBCC '/PROJECT_1/MODULE_1/'
Context changed.
OMB+> OMBCONNECT CONTROL_CENTER
Control Center connected.
OMB+> OMBCREATE TRANSIENT DEPLOYMENT_ACTION_PLAN \
'DEPLOY_PLAN' ADD ACTION 'TABLE_DEPLOY' \
SET PROPERTIES (OPERATION) \
VALUES ('CREATE') SET REFERENCE TABLE \
'/PROJECT_1/MODULE_1/TEST_TABLE'
Action plan DEPLOY_PLAN created.
OMB+> OMBDEPLOY DEPLOYMENT_ACTION_PLAN 'DEPLOY_PLAN'
Deploying TEST_TABLE
Deploying TEST_TABLE_ERR

Deployment completed.
OMB+>

This is a very user-friendly document about 10g OMB. I assume most of it will apply to 11g R2 OMB:
http://www.oracle.com/technology/products/warehouse/sdk/scripting%20pages/Scripting_home.htm

However, I can find no 11g version of that doc. Instead, what is officially available seems bare bones in comparison:
http://download.oracle.com/docs/cd/E14072_01/owb.112/e14406/toc.htm

Categories: Deploying, OMB

(Re) Importing Tables

July 22, 2010 Leave a comment

OWB stores representations of tables and other objects. The ETLs are created using these representations. Tables and objects are either created in OWB, or they can be imported from a source or target schema.

When re-importing a table, OWB by default does not drop columns or indexes that are no longer on the table in the schema. Instead it unions the object’s properties between what was in OWB and what is in the schema. One scenario where this can cause issues is if a foreign key was “renamed”, since OWB then creates a duplicate foreign key. The table then cannot be deployed (perhaps to set up error handling) since duplicate foreign keys are not valid in Oracle.

To change this behavior, click on the Advanced Import Options button on the final screen of the import wizard and deselect the “Preserve workspace added” options.

Update: I need to point out that the “Preserve workspace added” text in the Advanced Import Options of the Import Metadata Wizard is ambiguous. Leaving them checked preserves all existing indexes, constraints, etc. on the table(s), whether or not they were added through the use of OWB. (I guess you could argue they were “added” when the table was first “imported”). Perhaps a clearer label would state “Preserve existing metadata”.

OWB Advanced Import

OWB Advanced Import

Categories: Importing