ODI 11g in a Services Oriented Architecture. Part III. Consuming a RESTful web service.

Consuming web services in ODI

In part one of this series (Oracle Data Integrator in a Services Oriented Architecture) I had laid the groundwork and explained the basics of a SOA environment and how ODI fits into it. In part two we then looked at creating and deploying data services with ODI 11g in Weblogic. In part three we will now look at SOA from the other angle. I will show you how we can consume data services using ODI 11g.

Consuming a RESTful web service: OpenStreetMap (OSM) API

We can use ODI to consume public web services for data augmentation purposes, e.g. we can query the OpenStreetMap Geocode web service to augment customer data with longitude and latitude information.

OpenStreetMap provides geographic information such as maps, geocoding etc free of charge. You may wonder why we are not using Google maps? Well, first of all it’s not open source and Google’s license does not allow us to store the resultsets of our web service queries. I recently read the excellent book the Filter Bubble and since then try to avoid Google et al. even more. That’s why I have replaced Google search with Scroogle search.

Anyway, let’s get back to the topic at hand. As per the OpenStreetMap license anyone can use and store their data. They only ask you to mention OSM as the source of the data.

For details on the OSM license have a look at: http://wiki.openstreetmap.org/wiki/OpenStreetMap_License

There is also a page for the OSM usage policy

The OSM geocoding documentation can be found here

In addition there is an FAQ for the geocoding API.

Below is a sample request to the OpenStreet Geocoding API

http://nominatim.openstreetmap.org/search?q=3314+Eastern+Ave,+21224,+Baltimore,+MD,+US&format=xml&polygon=0&addressdetails=1

When we put the above URL in our browser we will get the following XML in return.

We will now look at how we can extract and store this information using ODI. I will show you two ways of extracting longitude and latitude information from the XML. In the first example we use built in functionality of the Oracle database to extract the information. In the second example we will exclusively use ODI functionality to extract the data.

Example 1: Consuming a RESTful web service – ODI piggybacks on the Oracle database

If we know that the target for our RESTful web service request is an Oracle database we can use the utl_http functionality built into the Oracle database to query the OpenStreetMap geocoding web service. This is convenient as we can directly extract the attribute values we are interested in from the XML that the web service returns. We don’t have to first set down the data in an XML file on the file system and we don’t have to use ODI to load the XML file into our target database. This saves us two steps and a bit of development time.

In a first step we need to create an Access Control List (ACL) for user OE. The ACL will give user OE access to the nominatim.openstreetmap.org website, but prevents access to any other websites. ACLs are new in Oracle 11. We need to grant these privileges as user SYS.

We first create the ACL

begin
            dbms_network_acl_admin.create_acl (
                    acl             => 'utl_http.xml',
                    description   => 'Normal Access',
                    principal       => 'OE',
                    is_grant       => TRUE,
                    privilege       => 'connect',
                    start_date    => null,
                    end_date      => null
           );
   end;

Next we assign the domains we want to access to the ACL

begin
        dbms_network_acl_admin.assign_acl (
        acl => 'utl_http.xml',
        host => 'nominatim.openstreetmap.org',
        lower_port => 1,
        upper_port => 10000);
    end;

Finally we grant access to user OE to the utl_http package.

 grant execute on utl_http to OE;

Once we have granted the correct privileges to the OE user we can directly access the OpenStreetMap API via the Oracle database. We can piggyback on this functionality from within an ODI procedure.

We create the ODI procedure PRC_GEOCODE_CUSTOMERS that loads the longitude and latitude information into the OE schema.

In a first step in our procedure we create a table at runtime, which stores the XML that we retrieve from the OpenStreetMap geocode API

CREATE TABLE address_geocode (
address_geocode XMLTYPE,
customer_id NUMBER
)

In the next step we query the OpenStreetMap API. We submit customers with customer_ID 240 and 235 from the OE schema to the OSM geocode API. As we don’t want to swamp the OpenStreetMap servers with too many requests we just retrieve data for two customers.

The Oracle database allows us to make direct http requests through the little known HTTPURITYPE function, which is built on top of the utl_http package. Have a look at the Oracle Books site where more information and examples are given.

INSERT INTO address_geocode
SELECT
XMLTYPE(HTTPURITYPE('http://nominatim.openstreetmap.org/search?q='||street_address||','||postal_code||','||address_city||','||state_province||','||country_id||'&'||'format=xml'||'&'||'polygon=0'||'&'||'addressdetails=1').getclob()),
   customer_id
FROM (
  SELECT
     REPLACE(c.cust_address.street_address,' ','+') street_address,
     REPLACE(c.cust_address.postal_code,' ','+') postal_code,
     REPLACE(c.cust_address.city,' ','+') address_city,
     REPLACE(c.cust_address.state_province,' ','+') state_province,
     REPLACE(c.cust_address.country_id,' ','+') country_id,
     customer_id
  FROM
     customers c
  WHERE
     customer_id IN (240,235)
  )

If we execute the procedure we will find two XML records in our address_geocode table.

We are now ready to extract longitude and latitude information from the XML and store in table customer_geocodes together with the customer_id.

We add another step to our procedure in which we create table customer_geocodes at runtime.

CREATE TABLE customer_geocodes
   (customer_id NUMBER, longitude   NUMBER, latitude NUMBER)

We then extract longitude and latitude information from the XML and store it in table customer_geocodes. We use the Oracle XMLTABLE functionality to extract these attributes from the XML. This function allows us to convert an XML file to a relational table. More details on XMLTABLE can be found in the documentation.

INSERT INTO customer_geocodes
SELECT
   customer_id,
   xt.longitude,
   xt.latitude
     FROM address_geocode yt,
          XMLTable('searchresults/place'
                   PASSING yt.address_geocode
                   COLUMNS
                   latitude NUMBER PATH '@lat',
                   longitude NUMBER PATH '@lon'
                   ) xt

We then execute the procedure and verify that the extracted information was loaded into our customer_geocodes table:

q.e.d.

Example 2: Consuming a RESTful web service using native ODI functionality

If we don’t have the luxury of an Oracle database we can still query the OpenStreetMap API using ODI native functionality. We can make use of the Jython scripting language that ships with ODI to access the OSM API.

We create another procedure PRC_GEOCODE_CUSTOMERS_JYTHON. We add one step to the procedure and set the Technology to Jython. In the code we connect to the OSM API, retrieve the geocode XML, and store this on the C drive on the file system.

import java.net.URL as URL
import java.io.BufferedReader as BR
import java.io.InputStreamReader as SR
import java.net.HttpURLConnection as con
import base64
import string

URLFormat="http://nominatim.openstreetmap.org/search?q=3314+Eastern+Ave,21224,Baltimore,MD,US&format=xml&polygon=0&addressdetails=1"

XMLFile="C:\\OSMGeo_240.xml"

OSMURL=URLFormat.replace(' ','%20')

url = URL(OSMURL)
con =  url.openConnection()

con.setRequestMethod("GET")

con.connect()
responseCode=con.getResponseCode()
responseMessage=con.getResponseMessage()

if responseCode ==  200 and responseMessage == "OK":
   br=BR(SR(con.getInputStream()))
   outRESTXML = ""
   outline = br.readLine()
   while (outline != None):
          outRESTXML += outline + "\n"
          outline = br.readLine()
   XMLfilehandle= open(XMLFile,'w')
   XMLfilehandle.write(outRESTXML)
   XMLfilehandle.close()
else:
   con.disconnect()

con.disconnect()

For the purpose of this walk through I have hard coded the address of the customer with CUSTOMER_ID 240.

Before we can extract the data contained in the XML file into the customer_geocodes table we first need to define the connection to the XML file in ODI Studio Topology.

In a first step we create the data server

Then we add the physical schema

Next we add the logical schema

Then we reverse engineer the model for the XML file

Once we have reverse engineered the model we should see the two new data stores Place and Searchresults in the XML_OSM_SRC model.

Before we create the interface to augment our customer data with longitude and latitude information we need to import two Knowledge Modules: LKM SQL to Oracle and IKM SQL Control Append.

The longitude and latitude information is contained in data store PLACE. We will use this data store as the source in our new temporary interface INT_OSM_XML_LOAD. Once we have added the PLACE data store to the interface drag and drop columns LAT and LON to the Target Datastore and manually add column CUSTOMER_ID to target datastore CUST_GEOCODES (right click in the target datastore and select Add Column).

As data type for column CUSTOMER_ID we select NUMBER. Enter 240 for the CUSTOMER_ID.

Note: In a real world scenario you would of course extract the customer_id from the filename of the XML file.

Next we change to the Flow tab and select LKM SQL to Oracle as the Load Knowledge Module.

We also select IKM SQL Control Append as the IKM and modify the following options:

FLOW_CONTROL: false
TRUNCATE: true
CREATE_TARG_TABLE: true

Next we execute the Interface. This should create a new table CUST_GEOCODES in the OE schema and insert longitude and latitude for customer_id 240.

This example has shown that we don’t necessarily need the advanced features of the Oracle database to query a web service using ODI.

ODI 11g in a Services Oriented Architecture. Part II. Deploying and Testing data services in Weblogic

In part I of the Oracle Data Integrator in a SOA environment series we’ve had a look at how ODI can be used conceptually in a SOA environment. In this second part we get our hands dirty and actually generate and deploy a data service in Weblogic.

Pre-requisites for the walkthrough

ODI 11g (11.1.3.5) running on Weblogic 11g (10.3.5), Oracle database 11g with OE sample schema installed. For the purpose of this paper ODI, Weblogic, and the Oracle database are installed with the OE sample schema on the same machine.

Next we create the logical data server

You should also have a good understanding of some of the basic ODI development tasks. This includes creating ODI procedures, ODI data servers, and familiarity with the XML functionality in ODI. I have a couple of ODI video tutorials on these tasks on my blog in case you are a complete novice.

Without further ado let’s get started.

Creating and deploying data services in ODI

Before we can use the data services functionality in ODI we have to make sure that we have added the “Oracle Data Integrator – SDK Web Services” product to our Weblogic domain. If you haven’t installed this already launch the configuration wizard, e.g. from D:\Oracle\Middleware\Oracle_ODI1\common\bin and extend your domain.

Next we will create the web services container.
This is where we will deploy the data services for the OE model. Go to ODI Studio Topology and add a physical data server to the JAX-WS techonology. Give the data server a name and type in the base URL for your ODI domain in Weblogic, e.g. http://localhost:8001. You will also need to supply the directory for auto-deployment of your data service. In your ODI domain in Weblogic there should be a folder autodeploy. In my environment this is at D:\Oracle\Middleware\user_projects\domains\ODI-DOMAIN\autodeploy. This is the folder where ODI deploys the auto generated code for the data services.

Next we insert a physical schema for the data server. Leave all of the default values.

Next insert the Logical Data Server

As we will use the OE sample schema to demonstrate data services we will have to set this up as data server in ODI.

In ODI Studio Topology we create a new data server under the Oracle Technology.

Next we create the logical data server

We then reverse engineer the OE model.
Go to the ODI Studio Designer tab, create a new model and reverse engineer the OE schema.

Before we can generate the data service for the customer table we first have to create a JDBC data source to the OE schema in Weblogic.
Log on to the Weblogic console (the default URL for the Weblogic console is http://localhost:7001/console) and navigate to Services > Data Sources.
We create a new Generic Data Source and name it JDBC OE. AS JNDI name we submit jdbc/oe.

On the next screen we select the appropriate Oracle driver. In our case this is the Oracle driver (Thin) for Instance connections Version 9.0.1 and later

On the next screen we leave all of the given default values.

On the next screen we type in the connection details to our OE schema.

On the next screen we will test our connection.

On the final screen we will select both the Admin Server (in my implementation AdminServer) and the ODI Server (in my implementation odi_server1) servers as target.

We now have created our JDBC data source in Weblogic and are now in a position to create our ODI data service.

Data services in ODI are generated using a Services Knowledge Module (SKM). We first need to import this into our project.

Next we open the ORCL_OE_SRC model and navigate to the Services finger tab.

In the application server dropdown we select the WEBLOGIC_WS_CONTAINER we created earlier on. Then we submit an appropriate namespace, e.g. http://www.business-intelligence-quotient.com/ws/oe/ and package name, e.g. com.biq.ws.oe. As the Name of Data Source we supply the previously created jdbc/OE data source. We give the data service a name, e.g. WSOE. Finally we select the SKM Oracle.ODI WS from the Knowledge Module dropdown.

Next we navigate to the Deployed Datastores tab and select the Customers table.

We save our selection and click the Generate and deploy… button. We leave the default values in the popup and click OK.

If we browse to our autodeploy folder we will find the .war file in it.

Congratulations. You have deployed your first ODI data service.

Let’s verify that the data service has been deployed as an application. In the Weblogic Console navigate to Deployment and you should see _appsdir_ORCL_OE_SRC_war (autodeployed). If the application has not yet been started you need to start it manually now.

Testing our data service

We have various options when it comes to testing our data service. I will show you how to test it using the Weblogic Console and the ODI web service client OdiInvokeWebService. If you need more advanced options I recommend the Open Source web services client soapUI.

Weblogic

We log into your Weblogic console, e.g. http://localhost:7001/console , go to Deployments and expand _appsdir_ORCL_OE_SRC_war (autodeployed).

Then we click on WSCustomers and then go to the Testing tab.

We can view the WSDL by clicking the ?WSDL link.

http://localhost:7001/ORCL_OE_SRC/WSCustomers?WSDL

This will show the WSDL in your web browser

We can test the web services by clicking on Test client. This will open a new browser window that lists all of the available web services.

To retrieve the attributes for a particular customer we will invoke the getCustomers method, e.g. for CUSTOMER_ID 105

After clicking the getCustomers button this returns the Customer attributes for ID 105.

OdiIinvokeWebservice

Another way of testing our data service is to use the web service client built into ODI.

We create a new package WSCustomers and add an OdiInvokeWebService tool.

To launch the ODI web service client we click the Advanced button.

We type in the URL to the WSDL http://localhost:7001/ORCL_OE_SRC/WSCustomers?WSDL
And click the Connect to WSDL button. This will bring back a list of the available web service methods in the Operation pane.

From the list of available web services we will invoke the deleteCustomers method to delete the customer with ID 105.

In the Editor pane we set the CUSTOMER_ID to 105 and click on the Invoke Web Service button

In the Response pane in the NBAffected field you will see that one record was deleted.

ODI 11g in a Services Oriented Architecture? Exposing data services and consuming web services with ODI 11g. Part I.

ODI in a SOA environment

This will be a four part series/tutorial on using ODI in a SOA environment.

One of the most overlooked features in ODI in general and in ODI 11g in particular is the ability to fully integrate ODI into a SOA environment. Typically technical architects associate ODI with high data volume ELT type data loads. However, ODI is very versatile when it comes down to meeting data integration requirements. Apart from the usual high volume, high performance ELT loads it can also be used in a web services centric environment.

In this series I will outline how ODI 11g can be deployed in a SOA environment to make data stores available to clients as data services. I will also show you how you can use ODI to consume data and web services. In the article I will give a step by step guide on how to create, deploy, and test a data service through ODI on Weblogic (without writing a single line of code). For the example I will use the customer table in the OE sample schema (http://download.oracle.com/docs/cd/B28359_01/server.111/b28328.pdf). On top of this, ODI can act as a consumer of web services. It can access both SOAP and RESTful web services. I will walk you through consuming both SOAP and RESTful web services using ODI. For the SOAP example we will connect to the OBIEE 11g web services API. For the RESTful web services we will consume the OpenStreet Map API to augment data in the OE schema customer table by longitude and latitude information.

Before we dive into the step by step guides, however, I first want to give a brief overview on the world of SOA, data services, SOAP and RESTful web services and how they are related to ODI.

What is SOA?

SOA is not a technology. It is rather a set of design principles for enterprise application architecture. In a SOA environment software units are loosely tied together as reusable services. They are platform independent and can be consumed by applications over standard network protocols. In a SOA environment the various web and data services are orchestrated in a workflow to implement a complex business process. The main benefit of SOA is faster application development. Already existing services can be orchestrated to quickly implement a given business process.

What are data services?

Data services expose enterprise data. Typically a data service makes data in one or more database tables available to consumers in the form of a web service. Client applications and end users can retrieve this data across physical and logical boundaries. On the web this could be across different geographies. For example this happens when you query the OpenStreet Map API. Irrespective of your location the data service returns the exact same data set. In the enterprise world this could be across business units within an enterprise. For example both the marketing and sales department can consume centrally held customer data. It could also be across enterprise boundaries, e.g. company 1 consumes product data from company 2.

What obstacles do we encounter in the implementation of data services?

Exposing data as a service without a supporting Master Data Management (MDM) program is like data warehousing without MDM. Difficult at best. Ideally the data that data services expose should be integrated, ought to be cleansed of errors, should be complete, needs to be up to date, and most not violate referential integrity. How would you feel when the longitude and latitude information changes each time you query the Open Street Map API? Good data quality is key for an environment that relies on data services. Any data services program should be accompanied by a data quality and master data management initiative. The so called golden record is a pre-requisite for a successful implementation of data services.

How are data services consumed?

Data services are typically consumed as web services. In the enterprise world you will mainly find SOAP based implementations. In the Web 2.0 world a lot of data services are now implemented as RESTful web services.

Data services and web services in ODI 11g

With ODI you can both create data services and consume them via web services. Once you have integrated, cleansed, and created the golden record of your data using ODI you can also expose this master data via data services. In turn you can consume data services via the ODI web services functionality.
When implementing data services you are not limited to read only access. You can also perform other operations on your ODI data services such as: deleting, updating, filtering.
Data service functionality has been before the latest release of ODI. However, in ODI 10g the data services had to be deployed in an Axis web services stack. This was a tedious and time consuming process. In ODI 11g it is a lot easier. ODI now supports the Java API for XML Web Services (JAX-WS) and you can deploy your data services directly to your Weblogic application server. This all happens automagically with a single click of a button. You don’t have to write a single line of code.
This means that you can use ODI to do the heavy lifting in a data warehouse environment but also in a master data management program to expose your golden record to client applications via data services.
As of ODI version 11.1.1.5.0 you can now also use web service container based authentication. It is now also possible to use fully formed SOAP messages as an input to a web service call.

In part of using Oracle Data Integrator in a Services Oriented Architecture we will create and deploy some ODI data services in Weblogic.

Oracle Warehouse Builder to go into retirement. What to do?

What I had predicted in my post OWB vs ODI: What to do, what to do? a couple of years ago, is now official. Oracle Warehouse Builder will be no more. At the time I had recommended not to embark on any new OWB implementations going forward. I hope you have stuck to this advice.

Anyway, as per Director of Software Development for Oracle Data Integrator, Robert Costin Velisar, OWB 11gR2 will be the final release of Oracle Warehouse Builder. There will be support for the last OWB release throughout the Oracle 12c life cycle. However, Dani Weber from Trivadis raises some serious questions on the way forward for OWB customers.

We are again at crossroads. Once more the question: what to do?

There is no easy way out. Personally I see two options

- Hope that Oracle will provide an official migration path over the next couple of months/years. A migration from OWB to ODI while technically difficult is not impossible. With the new ODI 11g SDK this should actually be quite doable. I personally know of a few shops that have migrated from other ETL tools to ODI using the SDK. Painful and expensive but doable.
- Be a bit more proactive and investigate a ‘roll your own migration’ path. It is certainly possible with the new ODI 11g SDK and there are third party tools that can assist.

No matter which route you pursue you should start to prepare your staff for the transition and provide training in the new technology asap.

The good news is that the ODI family will get bigger now. Not such bad news for the best ETL tool in the world.

Are Oracle’s whitepapers really for the bin?

According to the MOS search they are. whitepaper = wastepaper ??

Oracle data integration book

The guys at Packt publishing have asked me to review a brand new book on Oracle data integration and consolidation: Oracle Information, Integration, Migration and Consolidation. I have written this as a guest blog for the blog best oracle books.

Still waiting on this ODI book. Hope it is out soon. Some interesting stuff in it.

Skills an ETL developer on ODI 11g (Oracle Data Integrator) requires.

This post is inspired by a recent post by Mark Rittman on What Skills Does an Oracle BI Developer Need in 2011?.

ETL skills in general

As with everything in life you need to be passionate about what you are doing. This certainly also holds true for your job as an ODI developer. When we hire someone we first try to figure out how much passion the guy or gal has for data warehousing and business intelligence.

Independent of the tool, any ETL developer needs to have very good SQL knowledge. Good is not good enough. SQL is the most important skill. This holds definitely true for ETL tools that push the ETL code down to the database, but also for tools that execute the code on their application server such as Informatica. An ODI developer must know how to write advanced analytic functions. Analytic functions allow any procedural logic to be replaced by set based logic. Forget about PL/SQL. Or any such procedural language.

Apart from good SQL knowledge any ETL developer also must have a sound understanding of database technology, e.g. being able to read and interpret an explain plan is a distinct advantage.

Throw some data modelling experience into the mix and you have a solid skillbase as an ETL developer.

Specific ODI (11g) skills

What about the specific knowledge you need to work with ODI then?

Apart from SQL, you need to know some basic Java and have experience with scripting languages (ideally Jython, Java Bean Shell, and Groovy). This is useful to write ODI Knowledge Modules and do some fancy stuff with the substitution API using Java Bean Shell.

As of ODI 11g it is useful to have very good Java/Groovy skills. This will allow you to automate a lot of ODI tasks and exploit the new SDK to its full potential.

Last but not least it is useful to have some knowledge around application servers, in particular Weblogic. The ODI agent, the ODI console (ODIC), and the Enterprise Manager plugin all run on Weblogic. Knowledge around Weblogic is useful for troubleshooting stuff. I personally hate it, but that is probably because I don’t know enough about it myself.

A look in the crystal ball

The world of data warehousing, enterprise application integration, master data management, and SOA are moving closer together. You will see this reflected in the various tools, e.g. in ODI 11g there is a strong (somewhat overlooked) emphasis on web services and data services. In my opinion this stuff will become more and more important. If you want to learn one new skill then this should be around web services, SOA, queue and message based systems etc.

Last Words

You see the actual tool knowledge itself is not that relevant (you know a fool …). None of the ETL dudes who work alongside me have actually had any prior knowledge of ODI. We have a mix of guys with experience in Informatica Powercentre, Oracle Warehouse Builder, and the Microsoft stuff (what was the name of that again?). With proper mentoring it takes them on average two to three weeks to pick up the specifics of ODI.

ODI performance tuning, and optimisation

I get a lot of questions around ODI performance tuning and performance optimisation. There is a very good note on the Oracle support site that covers this.

Note 423726.1 covers ODI performance best practices from any angle. It is a must read for any ODI developer.

What’s the best location for the ODI agent?

I am just back from a holiday in County Wexford, Ireland’s sunny south east. We had a great time even though I was not allowed to bring the laptop (there was no reception anyway).

I got some good inspirations for a couple of new posts. Some on ODI and some others on Enterprise data architecture esp. around the relationship of MDM/SOA and the Enterprise Data Warehouse.

Today, however, we will have a look at the best location for your ODI agent(s).

You want to have your agent on the target

- You only have one target destination for your data. This is typically the case in a data warehousing environment.
- When you have a lot of data integration tasks that flow through the agent, e.g. when you use JDBC drivers, parse XML, use the Sunopsis Memory Engine etc. For all these tasks the data needs to flow through the agent’s memory and as a result you have increased network traffic, i.e. the data first needs to flow from your source to the server where you have your agent installed and then from there to your target. If you have the agent installed on the target. It will flow from source directly to target avoiding the extra roundtrip.
- You don’t have a separate server to install your agent on.

You should have your agent on a separate server

- Multiple targets. If you have multiple targets you could install the agent on one of the targets. However, you then tie this agent to the maintenance cycle of this server. When this server shuts down your agent shuts down with it.
- If you run your agent as a JEE agent on Weblogic. Weblogic takes up a lot of server resources and typically is installed on a separate server. However, you may install a standalone agent on the target in addition to the JEE agent
- When your target databse sits on Linux and you have MS Excel or MS Access or any other source technology that requires ODBC drivers. You may purchase third party ODBC drivers in this case though.
- There is not enough memory on the target to host the agent. Typically the memory that the agent requires is quite low. However, depending on your circumstances and your data volume this may not the case for you
- The DBAs don’t allow you to install the agent on the target
- If the OS of the database server is not supported for your agent, e.g. the case for Windows 2008 for ODI 10.

So you see it’s not always a good idea to have the agent on the target as you can read on some of the forums and blogs.

Exporting & Importing Oracle Data Miner (11gR2) Workflows

As with all development environments there will be need to move your code from one schema to another or from one database to another.

With Oracle Data Miner 11gR2, we have the same requirement. In our case it is not just individual procedures or packages, we have a workflow consisting of a number of nodes. With each node we may have a number of steps or functions that are applied to the data.

Exporting an ODM (11gR2) Workflow

In the Data Miner navigator, right-click the name of the workflow that you want to export.

The Save dialog opens. Specify a location on you computer where the workflow is saved as an XML file.

The default name for the file is workflow_name.xml, where workflow_name is the name of the workflow. You can change the name and location of the file.

Importing an ODM (11gR2) Workflow

Before you import your ODM workflow, you need to make sure that you have access the the same data that is specified in the workflow.

All tables/views are prefixed with the schema where the table/view resides.

You may want to import the data into the new schema or ensure that the new schema has the necessary grants.

Open the connection in ODM.

Select the project under with you want to import the workflow, or create a new project.

Right click the Project and select Import Workflow.

Search for the XML export file of the workflow.

Preserve the objects during the import.

When you have all the data and the ODM workflow imported, you will need to run the entire workflow to ensure that you have everything setup correctly.

It will also create the models in the new schema.

Data encoding in Workflow

All of the tables and views used as data sources in the exported workflow must reside in the new account

The account from which the workflow was exported is encoded in the exported workflow e.g. the exported workflow was exported from the account DMUSER and contains the data source node with data MINING_DATA_BUILD. If you import the schema into a different account (that is, an account that is not DMUSER) and try to run the workflow, the data source node fails because the workflow is looking for USER.MINING_DATA_BUILD_V.

To solve this problem, right-click the data node (MINING_DATA_BUILD_V in this example) and select Define Data Wizard. A message appears indicating that DMUSER.MINING_DATA_BUILD_V does not exist in the available tables/views. Click OK and then select MINING_DATA_BUILD_V in the current account.

Video

I have created a video of this blog. It illustrates how you can Export a workflow and Import the workflow into a new schema.

Exporting and Importing Oracle Data Miner (11gR2) Workflows

Make sure to check out my other Oracle Data Miner (11gR2) videos.

http://www.youtube.com/user/btierney70

Brendan Tierney