X

Welcome to All Things Warehouse Builder

Recent Posts

Press

OWB to ODI Migration Utility released.

The much awaited OWB to ODI Migration utility has been released. The migration utility is a command-line tool that enables you to migrate design-time metadata from OWB 11.2.0.4 to ODI 12c. The migration utility is provided in patch number 17547241 for your OWB 11gR2 (11.2.0.4) installation. Migration is supported on Linux 64-bit x86 systems only. Before migrating, ensure that the following requirements are met: OWB 11.2.0.4 installed (OWB 11.2.0.4 plus patch number 17547241) ODI 12.1.2.0.0 installed (ODI 12.1.2.0.0 plus patch number 17053768) ODI is Oracle's strategic product for heterogeneous data integration. Because many Oracle Database customers have significant investment in OWB, Oracle supports a phased migration from OWB 11gR2 (11.2.0.4) to ODI 12c (12.1.2). ODI 12c supports an easier mapping between OWB 11gR2 concepts and objects and their ODI 12c counterparts. A migration utility is provided that automatically translates many OWB objects and mappings into their ODI equivalents. More information about the migration utility is here http://docs.oracle.com/middleware/1212/odi/ODIMG/index.html In addition to enabling the migration, the ODI 12c patch also comes with new  components such as Pivot, Unpivot, Subquery filter  and Table Functions. They're documented in the following appendix: http://docs.oracle.com/middleware/1212/odi/ODIDG/app_12c_patch.htm

The much awaited OWB to ODI Migration utility has been released. The migration utility is a command-line tool that enables you to migrate design-time metadata from OWB 11.2.0.4 to ODI 12c. The migration...

Events

OWB - Making the Move to ODI

Yesterday's session on Making the Move to Oracle Data Integrator at OOW13 after the America's Cup finale was a great way to round off the day (the garlic at the Stinking Rose was even better but we will stick with the technology here), the session was hosted by Julien Testut from product management and was under the Oracle safe harbor statement below. There was an overview by Jyotin Gautam the ODI development director and then some demonstrations from Oracle partners Stewart Bryson and Holger Friedrich. The session lay down the options for OWB customers moving forward;  execute and monitor OWB jobs within ODI migrate all or parts of OWB mappings with the migration utility develop new ETL in ODI ...or a mixture of all of these. Stewart demonstrated the execution of OWB jobs from within ODI and highlighted the immediate value-add of doing this. ODI load plans provide a restartability capability that is a key part of an ETL infrastructure (this had to be manually constructed in OWB). Its great hearing the genuine excitement and enthusiasm Stewart presents with and using words like 'beautiful' are icing on the cake (I can still hear his comments a few years ago of 'all I want is a mapper' ringing in my ears). Holger demonstrated the migration utility that migrates the OWB design metadata into ODI, migrating a complex mapping with many operators and multiple targets into a ODI 12c mapping and like many was surprised with the simplicity of the solution and the maintainability of the new design in ODI. There was a bunch of tweeting going on in the session and Mark Rittman quick succinctly summarized the session below; It was a well attended session with a mix of OWB and ODI customers and customers looking to move to ODI in general. The guys in development were happy to see this finally getting their. Sure and steadfast ;-) The session was under Oracle's safe harbor statement...'The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.'

Yesterday's session on Making the Move to Oracle Data Integrator at OOW13 after the America's Cup finale was a great way to round off the day (the garlic at the Stinking Rose was even better but we...

ETL

OWB - 11.2.0.4 standalone client released

The 11.2.0.4 release of OWB containing the 32 bit and 64 bit clients is released today. Big thanks to Anil for spearheading that, another milestone on the Data Integration roadmap. Below are the patch numbers; 17389934 - OWB 11.2.0.4 STANDALONE CLIENT FOR LINUX X86 64 BIT 17389949 - OWB 11.2.0.4 STANDALONE CLIENT FOR LINUX X86 32 BIT The windows releases will come in due course. This is the terminal release of OWB and customer bugs will be resolved on top of this release. Sure and Stedfast has been a steady motto through my life, it came from way back in my old Boys Brigade days back in Scotland. Working in Oracle I have always reflected back on that over the years, can still hear 'Will your anchor hold in the storms of life' ringing in my ear. The ride through different development organizations from Oracle Tools, through Oracle Database and Oracle Middleware groups, from buildings 200, to 400 to 100, 7th floor, 9th floor, 5th floor, countless acquisitions and integrations. Constant change in some aspects, but zeroes and ones remain zeroes and ones, for our group the direction and goals were well understood. Whilst its been quiet on the OWB blogging front, the data integration development organization has been busy, very busy releasing versions of OWB and ODI over the past few years and building the 12c release. So to 12c... our data integration product roadmap has been a strong focal point in our development over the past few years and that's what we have been using to focus our energy and and our direction. Like personal life we need a goal, a vision and a roadmap for getting there. There have been plenty of challenges along the way; technical, political and personal - its been a tough and challenging few years on all of those fronts, its when you are faced with momentous personal challenges that the technical ones look trivial. The most gratifying aspect is when you see light at the end of the tunnel. It's that light at the end of the tunnel that gives you added strength to finish the job at hand. Onwards and upwards!

The 11.2.0.4 release of OWB containing the 32 bit and 64 bit clients is released today. Big thanks to Anil for spearheading that, another milestone on the Data Integration roadmap. Below are the patch...

ETL

How to Configure The SSL Certificate For Oracle Warehouse Builder Repository Browser

  The Repository Browser is a browser-based tool that generates reports from data stored in Oracle Warehouse Builder (OWB) repositories. It use OC4j as the web server. Users need to use HTTPS to access the web interface. ( HTTP on top of the SSL/TLS protocol) If the Repository Browser Listener is running on a computer named owb_server, then typing the following address will start the Repository Browser:    https://owb_server:8999/owbb/RABLogin.uix?mode=design    or    https://owb_server:8999/owbb/RABLogin.uix?mode=runtime On the server side, the SSL certificate for the browser is required. Users can create it by themselves. First, uses can user the JRE's util "keytool" to generate a keystore, name it keystore.jks. For example: keytool -genkey -keyalg RSA -alias mykey -keystore keystore.jks -validity 2000 -storepass  welcome1 Please pay attention to the password of the store, it need to be the same as the credentials of keystoreadmin in the file called "system-jazn-data.xml". If the password is not the same, the error message like "Keystore was tampered with, or password was incorrect" will be generated. In order to change the credentials, there are two files you can edit. http-web-site.xml: It is in the path of %OWB_HOME%/j2ee/config. The password is stored as clear text in the http-web-site.xml, Users can change it to fit the password they use to generate the keysotre. For the security reason, if users don't want to store clear text, they can use the point (->keystoreadmin) to point another file named system-jazn-data.xml. system-jazn-data.xml: User can find "system-jazn-data.xml" in the %OWB_HOME%/j2ee/config. There is a entry in it called "keystoreadmin".  Password store in this file is encrypted password. The pointer mentioned above is pointing to this place. In order to change the password, you can edit "system-jazn-data.xml",  change the value "<credentials>" of the entry "keystoreadmin". Please added "!" in front of your password. For example, if you want to change the password to welcome,change it to <credentials>!welcome</credentials> The next time OC4J reads "system-jazn-data.xml", it will rewrite the file with all passwords obfuscated and unreadable.(So  your clear text like "!welcome" will become encrypted password, something like '{903}dnHlnv/Mp892K8ySQan+zGTlvUDeFYyW'

  The Repository Browser is a browser-based tool that generates reports from data stored in Oracle Warehouse Builder (OWB) repositories. It use OC4j as the web server. Users need to use HTTPS to...

ETL

OWB - Compressing Files in Parallel using Java Activity

Yesterday I posted a user function for compressing/decompressing files using parallel processes in ODI. The same code you can pick up and use from an OWB process flow. Invoking the java function from within a Java activity from within the flow. The JAR used in the example below can be downloaded here, from the process flow OWB invokes the main method within the ZipFile class for example - passing the parameters to the function for the input, output directories and also the number of threads. The parameters are passed as a string in OWB, each parameter is wrapped in ?, so we have a string like ?param1?param2?param3? and so on. In the example I pass the input directory d:\inputlogs as the first parameter and d:\outputzips as the second, the number of processes used is 4 - I have escaped my backslash in order to get this to work on Windows.  The classpath has the JAR file with the class compiled in it and the classpath value can be specified specified on the activity, carefully escaping the path if on windows. Then you can define the actual class to use; That's it, pretty easy. The return value from the method will use the exit code from your java method - normally 0 is failure and other values are error (so if you exit the java using a specific error code value you can return this code into a variable in OWB or perform a complex transition condition). Any standard output/error is also capture from within the OWB activity log in the UI, for example below you can see an exception that was thrown and also messages output to the standard output/error;  That's a quick insight to the java activity in OWB.

Yesterday I posted a user function for compressing/decompressing files using parallel processes in ODI. The same code you can pick up and use from an OWB process flow. Invoking the java function from...

How to ...

OWB Repository Install on RAC using OMBPlus

There are few documents on the Oracle Support site http://support.oracle.com  to check if OWB is installed correctly on RAC and Exadata (Doc ID 455999.1) and How to Install a Warehouse Builder Repository on a RAC (Doc ID 459961.1).  This blog will just show you how to install the OWB repository on RAC using OMBPlus. The steps are: Database preparation Repository installation on the first Node Copy of the rtrepos.properties File on all Nodes Registration of all the other Nodes Check the installation Step 1: Database preparation UNIQUE Service NameMake sure that EACH Node in the RAC has a UNIQUE service_name. If this is not the case, then add a unique service_name with the following command: srvctl add service -d dbname -s instn -r instnThe resulting service name is instn.clusterdomainname. For example, if the instance name is racsrvc1,then the service name could be racsrvc1.us.oracle.com."srvctl" can be used to manage the RAC services:srvctl [status|stop|start] service -d <db> -s <service-name>Details are described in the OWB Installation Guide:Paragraph "Ensuring the Availability of Service Names for Oracle RAC Nodes"LISTENER ConfigurationMake sure that EACH Node has the LISTENER configured correctly. The listener on each Node should be able to manage connections to the unique database service of each Node. Step 2: Repository installation on the first Node We assume that RAC has 2 Nodes: NODE1 and NODE2, the database instance is setup and the OWB software has been installed on all Nodes of the RAC.  Start the OMBPlus shell on the primary node say Node 1 from <OWB_HOME>/owb/bin/unix/OMBPlus.sh Execute the following command OMB+> OMBSEED DATA_TABLESPACE'USERS' INDEX_TABLESPACE 'INDX' TEMPORARY_TABLESPACE 'TEMP'SNAPSHOT_TABLESPACE 'USERS' USING CREDENTIALOWBSYS/PASSWORD@hostname:1521:servicename OWB repository seeding completed. OMB+> exit  Step 3: Copy of the rtrepos.properties File on all Nodes  During the Repository seeding, a file rtrepos.properties is created/updated on Node 1 at location  <OWB_HOME>\ owb\bin\admin directory. This file should be copied to all RAC Nodes to the same location. In this case to Node 2 at  <OWB_HOME>\ owb\bin\admin. Step 4: Registration of all the other Nodes After the Repository installation, all RAC Nodes should be registered. This to enable the OWB Runtime Service to fail over to one of the other Nodes when required (e.g. because of a node crash). This registration process consists of an update in tables OWBRTPS and WB_RT_SERVICE_NODES. These tables will be updated with Node specific details like the Oracle_Home where the OWB software has been installed on the Node and and host, port, service connection details for the instance running on the Node.   OMB+>OMBINSTALL OWB_RAC USINGCREDENTIAL OWBSYS/OWBSYS@localhost:1521:service_name RAC instance has beebregistered. Step 5: Check the installation Check the owb home values in the following tables are correct. Select * from owbsys.owbrtps Select * from owbsys.wb_rt_service_nodes. Connect as the OWBSYS to the unique service net_service on each node and execute the script located in the <OWB_HOME>\ owb\rtp\sql directory SQL>@show_service.sql Available PL/SQL procedure successfully completed.  If the service is not available start the service using the following script SQL>@start_service.sql Available Your installation of the OWB repository is now complete. You can also use the following OMBplus commands to create a OWB WORKSPACE and workspace owner. In SQL*Plus as sysdba create user WORKSPACE_OWNER identified by PASSWORD; grant resource, connect to WORKSPACE_OWNER; grant OWB_USER to WORKSPACE_OWNER; grant create session to WORKSPACE_OWNER; In OMBPlus OMB+> OMBINSTALL WORKSPACE 'WORKSPACE_WH' USING CREDENTIAL WORKSPACE_OWNER/PASSWORD@hostname:1521:service_name Workspace has been created. OMB+> exit

There are few documents on the Oracle Support site http://support.oracle.com  to check if OWB is installed correctly on RAC and Exadata (DocID 455999.1) and How to Install a Warehouse Builder...

ETL

OWB - Securing your data with Transparent Data Encryption

Oracle provides a secure and convenient functionality for securing data in your datawarehouse, tables can be designed in OWB utilizing the Transparent Data Encryption capability. This is done by configuring specific columns in a table to use encryption. When users insert data, the Oracle database transparently encrypts it and stores it in the column.  Similarly, when users select the column, the database automatically decrypts it.  Since all this is done transparently without any change the application code, the feature has an appropriate name: Transparent Data Encryption.  Encryption requires users to apply an encryption algorithm and an encryption key to the clear-text input data. And to successfully decrypt an encrypted value, users must know the value of the same algorithm and key. In Oracle database, users can specify an entire tablespace to be encrypted, or selected columns of a table. From OWB we support column encryption that can be applied to tables and external tables. We secure the capture of the password for encryption in an OWB location, just like other credentials. This is then used later in the configuration of the table. We can configure a table and for columns define any encryption, including the encryption algorithm, integrity algorithm and the password.  Then when the table is deployed from OWB, the TDE information is incorporated into the DDL for the table; When data is written to this column it is encrypted on disk. Read more about this area in the Oracle Advanced Security white paper on Transparent Data Encryption Best Practices here.

Oracle provides a secure and convenient functionality for securing data in your datawarehouse, tables can be designed in OWB utilizing the Transparent Data Encryption capability. This is done...

ETL

External table and preprocessor for loading LOBs

I was using the COLUMN TRANSFORMS syntax to load LOBs into Oracle using the Oracle external which is a handy way of doing several stuff - from loading LOBs from the filesystem to having constants as fields. In OWB you can use unbound external tables to define an external table using your own arbitrary access parameters - I blogged a while back on this for doing preprocessing before it was added into OWB 11gR2. For loading LOBs using the COLUMN TRANSFORMS syntax have a read through this post on loading CLOB, BLOB or any LOB, the files to load can be specified as a field that is a filename field, the content of this file will be the LOB data. So using the example from the linked post, you can define the columns; Then define the access parameters - if you go the unbound external table route you can can put whatever you want in here (your external table get out of jail free card); This will let you read the LOB files fromn the filesystem and use the external table in a mapping. Pushing the envelope a little further I then thought about marrying together the preprocessor with the COLUMN TRANSFORMS, this would have let me have a shell script for example as the preprocessor which listed the contents of a directory and let me read the files as LOBs via an external table. Unfortunately that doesn't quote work - there is now a bug/enhancement logged, so one day maybe. So I'm afraid my blog title was a little bit of a teaser....

I was using the COLUMN TRANSFORMS syntax to load LOBs into Oracle using the Oracle external which is a handy way of doing several stuff - from loading LOBs from the filesystem to having constants as...

ETL

OWB – Configuration Templates, Default Values

The 11gR2 release of OWB introduced ways of overriding the default values for properties – users may wish to change the seeded default values for properties (for all objects of a type. You can do this using the enterprise feature supplied in Configuration Templates.These configuration templates are defined on the global tree, once you create a configuration template it is used in a configuration – then any objects created will inherit these default values. You can create a new template and provide a name and description; This then brings up the editor for the configuration template, the properties are in the tree, and the columns such as PRODUCTION_DEFAULTS is where you can change the property value. So for example if you wanted to change the property value for Generation Mode – so rather than generation All Operating Modes which is the default, you can just generate Set Based, you would find this property;Then change the value to Set Based for your configuration template;Lots of property defaults are here, see there is also one for Default Operating Mode, if you were to change the default code gen to just be Set Based, it makes sense to also change the default operating mode to Set Based.Remember these are defaults so you are not setting specific values on an object – these are the defaults of o overriden value is specified. There are many other interesting properties from tablespace info for tables to all sorts of properties for mappings.The final piece of the jigsaw is to use this configuration template in a configuration – otherwise it will never be used.

The 11gR2 release of OWB introduced ways of overriding the default values for properties – users may wish to change the seeded default values for properties (for all objects of a type. You can do this...

ETL

OWB 11gR2 – Parallelization using DBMS_PARALLEL_EXECUTE

As well as all of the parallel query and DML capabilities exposed in OWB, the 11gR2 release of OWB includes out of the box support for the DBMS_PARALLEL_EXECUTE package in 11gR2 of the Oracle database, there are various articles on how to do the grunt work manually coding it on the web (see here for example). The use cases for this range from Oracle techie stuff like managing roll back segments for large table updates to parallelizing your PLSQL mappings…whatever they may be. Here we will see how with a couple of configuration settings in OWB you can self parallelize mappings – divide and conquer the olde fashioned way.The use case that is generally mentioned for using this is for doing large table updates – basically its dividing a BIG problem into smaller pieces, the package also exposes a resume capability for reprocessing failed chunks which is also exposed from OWB (when you execute a chunked mapping you can resume or start a mapping). The approach can also be utilized for parallelizing arbitrary PLSQL (support for set based, row based also the table being chunked can be the source or the target or an arbitrary SQL statement). The 11.2.0.3 patch is best for this.Chunk by SQL StatementThe example below updates employees salaries, driving the parallel worker threads by the departments table. As you see below we the start and end department id are the same, we are doing a distinct, so all employees in a department will be updated in each chunk, so we can increase the salary within the EMPLOYEES table for each department in a chunk. We are using the department id to drive the chunking – each department if processed in its own thread. We will see how we can control the thread pool of workers. Note above there are other options such as chunk by ROWID and an arbitrary SQL statement). The mapping now using olde fashioned divide and conquer has new runtime properties which relate back to the DBMS_PARALLEL_EXECUTE package and include the resume functionality for processing failed chunks and the parallel level, so you can change at runtime whether 1,2 or n threads are used to process the chunks. We are saying that we want 2 child processes processing the chunks, this is a runtime parameter. Its wise to think about the number of chunks and the number of child processes to ensure optimal execution plan. The image below depicts the code that gets generated with chunk parallel level 2, and parallel level 4 – essentially the package ‘main’ procedure uses DBMS_PARALLEL_EXECUTE to process the heart of the mapping in the child processes. There is much more to the package than meets the eye the resume capability for example provides a way of reprocessing failed chunks, rather than reprocessing everything again. This is also exposed as a runtime parameter for the chunked OWB mapping so you can resume and reprocess only the failed chunks. Chunk by ROWIDThis is the classic Large Table Update example that typical divide and conquer is used for. This example updates employees salaries, driving the parallel worker threads by rows in the target employees table itself. With this example when we configure the mapping we pick chunk method as ROWID, the chunk table EMPLOYEES, chunk type BY_ROWS and size is 1000 for example. The EMPLOYEES table is set to perform and update, I define the target filter for update for the ON clause in the merge/update – so its the ‘how do I identify the rows to update within this chunk’ clause. The other thing I had to do was define a chunking expression – now in this case its kind of redundant since the chunk is being done in the ON clause of the update – so we can trick OWB by just saying ‘start=start and end=end’ using the variables. If you don’t specify this, OWB will complain that the chunking expression is invalid. So the MERGE statement within the chunking code will increase the SALARY for the chunk, you will see the expression to increase salary by, the dummy chunking expression used in selecting from the source (DUAL) and which rows to match – the rows in this chunk. This let’s us perform large table updates in chunks and drive the parallelized mapping using mapping input parameters.The parallelization for PLSQL (or row based mappings) is an interesting case - for example for the likes of match merge which has an inherent divide and conquer strategy (in binning), with match merge out of the box the processing of the bins is serialized by default. Combining the chunk parallelization with the match merge binning lets you boost the performance of such mappings. So if you pair the chunking criteria with the binning you can substantially increase the performance of such mappings.

As well as all of the parallel query and DML capabilities exposed in OWB, the 11gR2 release of OWB includes out of the box support for the DBMS_PARALLEL_EXECUTE package in 11gR2 of the Oracle...

ETL

ODI 11g – Scripting Repository Creation

Here’s a quick post on how to create both master and work repositories in one simple dialog, its using the groovy capabilities in ODI 11g and the groovy swing builder components. So if you want more/less take the groovy script and change, its easy stuff. The groovy script odi_create_repos.groovy is here, just open it in ODI before connecting and you will be able to create both master and work repositories with ease – or check the groovy out and script your own automation – you can construct the master, work and runtime repositories, so if you are embedding ODI as your DI engine this may be very useful. When you click ‘Create Repository’ you will see the following in the log as the master repository starts to be created;======================================================Repository Creation Started....======================================================Master Repository Creation Started.... Then the completion message followed by the work repository creation and final completion message.Master Repository Creation Completed.Work Repository Creation Started.Work Repository Creation Completed.======================================================Repository Creation Completed Successfully======================================================Script exited. If any error is hit, the script just exits and prints any error to the log. For example if I enter no passwords, I will get this error;======================================================Repository Creation Started....======================================================Master Repository Creation Started....======================================================Repository Creation Complete in Error======================================================oracle.odi.setup.RepositorySetupException: oracle.odi.core.security.PasswordPolicyNotMatchedException: ODI-10189: Password policy MinPasswordLength is not matched.======================================================Script exited. This is another example of using the ODI 11g SDK showing how to automate the construction of your data integration environment. The main interfaces and classes used here are IMasterRepositorySetup / MasterRepositorySetupImpl and IWorkRepositorySetup / WorkRepositorySetupImpl.

Here’s a quick post on how to create both master and work repositories in one simple dialog, its using the groovy capabilities in ODI 11g and the groovy swing builder components. So if you want...

Application Connectors

ODI 11g – Faster Files

Deep in the trenches of ODI development I raised my head above the parapet to read a few odds and ends and then think why don’t they know this? Such as this article here – in the past customers (see forum) were told to use a staging route which has a big overhead for large files. This KM is an example of the great extensibility capabilities of ODI, its quite simple, just a new KM that;improves the out of the box experience – just build the mapping and the appropriate KM is usedimproves out of the box performance for file to file data movement.This improvement for out of the box handling for File to File data integration cases (from the 11.1.1.5.2 companion CD and on) dramatically speeds up the file integration handling. In the past I had seem some consultants write perl versions of the file to file integration case, now Oracle ships this KM to fill the gap. You can find the documentation for the IKM here. The KM uses pure java to perform the integration, using java.io classes to read and write the file in a pipe – it uses java threading in order to super-charge the file processing, and can process several source files at once when the datastore's resource name contains a wildcard. This is a big step for regular file processing on the way to super-charging big data files using Hadoop – the KM works with the lightweight agent and regular filesystems.So in my design below transforming a bunch of files, by default the IKM File to File (Java) knowledge module was assigned. I pointed the KM at my JDK (since the KM generates and compiles java), and I also increased the thread count to 2, to take advantage of my 2 processors. For my illustration I transformed (can also filter if desired) and moved about 1.3Gb with 2 threads in 140 seconds (with a single thread it took 220 seconds) - by no means was this on any super computer by the way. The great thing here is that it worked well out of the box from the design to the execution without any funky configuration, plus, and a big plus it was much faster than before,So if you are doing any file to file transformations, check it out!

Deep in the trenches of ODI development I raised my head above the parapet to read a few odds and ends and then think why don’t they know this? Such as this article here – in the past customers (see f...

ETL

ODI 11g – How to override SQL at runtime?

Following on from the posting some time back entitled ‘ODI 11g – Simple, Powerful, Flexible’ here we push the envelope even further. Rather than just having the SQL we override defined statically in the interface design we will have it configurable via a variable….at runtime.Imagine you have a well defined interface shape that you want to be fulfilled and that shape can be satisfied from a number of different sources that is what this allows - or the ability for one interface to consume data from many different places using variables. The cool thing about ODI’s reference API and this is that it can be fantastically flexible and useful. When I use the variable as the option value, and I execute the top level scenario that uses this temporary interface I get prompted (or can get prompted to be correct) for the value of the variable. Note I am using the <@=odiRef.getObjectName("L","EMP", "SCOTT","D")@> notation for the table reference, since this is done at runtime, then the context will resolve to the correct table name etc.Each time I execute, I could use a different source provider (obviously some dependencies on KMs/technologies here). For example, the following groovy snippet first executes and the query uses SCOTT model with EMP, the next time it is from BOB model and the datastore OTHERS.m=new Properties();m.put("DEMO.SQLSTR", "select empno, deptno from <@=odiRef.getObjectName("L","EMP", "SCOTT","D")@>");s=new StartupParams(m);runtimeAgent.startScenario("TOP", null, s, null, "GLOBAL", 5, null, true);m2=new Properties();m2.put("DEMO.SQLSTR", "select empno, deptno from <@=odiRef.getObjectName("L","OTHERS", "BOB","D")@>");s2=new StartupParams(m);runtimeAgent.startScenario("TOP", null, s2, null, "GLOBAL", 5, null, true);You’ll need a patch to 11.1.1.6 for this type of capability, thanks to my ole buddy Ron Gonzalez from the Enterprise Management group for help pushing the envelope!

Following on from the posting some time back entitled ‘ODI 11g – Simple, Powerful, Flexible’ here we push the envelope even further. Rather than just having the SQL we override defined statically in...

ETL

ODI 11g – Oracle Multi Table Insert

With the IKM Oracle Multi Table Insert you can generate Oracle specific DML for inserting into multiple target tables from a single query result – without reprocessing the query or staging its result.When designing this to exploit the IKM you must split the problem into the reusable parts – the select part goes in one interface (I named SELECT_PART), then each target goes in a separate interface (INSERT_SPECIAL and INSERT_REGULAR).So for my statement below…/*INSERT_SPECIAL interface */ insert  all when 1=1 And (INCOME_LEVEL > 250000) theninto SCOTT.CUSTOMERS_NEW(ID, NAME, GENDER, BIRTH_DATE, MARITAL_STATUS, INCOME_LEVEL, CREDIT_LIMIT, EMAIL, USER_CREATED, DATE_CREATED, USER_MODIFIED, DATE_MODIFIED)values(ID, NAME, GENDER, BIRTH_DATE, MARITAL_STATUS, INCOME_LEVEL, CREDIT_LIMIT, EMAIL, USER_CREATED, DATE_CREATED, USER_MODIFIED, DATE_MODIFIED) /* INSERT_REGULAR interface */ when 1=1  theninto SCOTT.CUSTOMERS_SPECIAL(ID, NAME, GENDER, BIRTH_DATE, MARITAL_STATUS, INCOME_LEVEL, CREDIT_LIMIT, EMAIL, USER_CREATED, DATE_CREATED, USER_MODIFIED, DATE_MODIFIED)values(ID, NAME, GENDER, BIRTH_DATE, MARITAL_STATUS, INCOME_LEVEL, CREDIT_LIMIT, EMAIL, USER_CREATED, DATE_CREATED, USER_MODIFIED, DATE_MODIFIED)/*SELECT*PART interface */ select        CUSTOMERS.EMAIL EMAIL,    CUSTOMERS.CREDIT_LIMIT CREDIT_LIMIT,    UPPER(CUSTOMERS.NAME) NAME,    CUSTOMERS.USER_MODIFIED USER_MODIFIED,    CUSTOMERS.DATE_MODIFIED DATE_MODIFIED,    CUSTOMERS.BIRTH_DATE BIRTH_DATE,    CUSTOMERS.MARITAL_STATUS MARITAL_STATUS,    CUSTOMERS.ID ID,    CUSTOMERS.USER_CREATED USER_CREATED,    CUSTOMERS.GENDER GENDER,    CUSTOMERS.DATE_CREATED DATE_CREATED,    CUSTOMERS.INCOME_LEVEL INCOME_LEVELfrom    SCOTT.CUSTOMERS   CUSTOMERSwhere    (1=1) Firstly I create a SELECT_PART temporary interface for the query to be reused and in the IKM assignment I state that it is defining the query, it is not a target and it should not be executed. Then in my INSERT_SPECIAL interface loading a target with a filter, I set define query to false, then set true for the target table and execute to false. This interface uses the SELECT_PART query definition interface as a source. Finally in my final interface loading another target I set define query to false again, set target table to true and execute to true – this is the go run it indicator! To coordinate the statement construction you will need to create a package with the select and insert statements. With 11g you can now execute the package in simulation mode and preview the generated code including the SQL statements. Hopefully this helps shed some light on how you can leverage the Oracle MTI statement. A similar IKM exists for Teradata. The ODI IKM Teradata Multi Statement supports this multi statement request in 11g, here is an extract from the paper at www.teradata.com/white-papers/born-to-be-parallel-eb3053/Teradata Database offers an SQL extension called a Multi-Statement Request that allows several distinct SQL statements to be bundled together and sent to the optimizer as if they were one. Teradata Database will attempt to execute these SQL statements in parallel. When this feature is used, any sub-expressions that the different SQL statements have in common will be executed once, and the results shared among them.It works in the same way as the ODI MTI IKM, multiple interfaces orchestrated in a package, each interface contributes some SQL, the last interface in the chain executes the multi statement.

With the IKM Oracle Multi Table Insert you can generate Oracle specific DML for inserting into multiple target tables from a single query result – without reprocessing the query or staging its result. W...

ETL

ODI 11g – How to Load Using Partition Exchange

Here we will look at how to load large volumes of data efficiently into the Oracle database using a mixture of CTAS and partition exchange loading. The example we will leverage was posted by Mark Rittman a couple of years back on Interval Partitioning, you can find that posting here. The best thing about ODI is that you can encapsulate all those ‘how to’ blog posts and scripts into templates that can be reused – the templates are of course Knowledge Modules.The interface design to mimic Mark's posting is shown below; The IKM I have constructed performs a simple series of steps to perform a CTAS to create the stage table to use in the exchange, then lock the partition (to ensure it exists, it will be created if it doesn’t) then exchange the partition in the target table. You can find the IKM Oracle PEL.xml file here.The IKM performs the follows steps and is meant to illustrate what can be done; So when you use the IKM in an interface you configure the options for hints (for parallelism levels etc), initial extent size, next extent size and the partition variable;  The KM has an option where the name of the partition can be passed in, so if you know the name of the partition then set the variable to the name, if you have interval partitioning you probably don’t know the name, so you can use the FOR clause. In my example I set the variable to use the date value of the source dataFOR (TO_DATE(''01-FEB-2010'',''dd-MON-yyyy'')) Using a variable lets me invoke the scenario many times loading different partitions of the same target table.Below you can see where this is defined within ODI, I had to double single-quote the strings since this is placed inside the execute immediate tasks in the KM; Note also this example interface uses the LKM Oracle to Oracle (datapump), so this illustration uses a lot of the high performing Oracle database capabilities – it uses Data Pump to unload, then a CreateTableAsSelect (CTAS) is executed on the external table based on top of the Data Pump export. This table is then exchanged in the target. The IKM and illustrations above are using ODI 11.1.1.6 which was needed to get around some bugs in earlier releases with how the variable is handled...as far as I remember.

Here we will look at how to load large volumes of data efficiently into the Oracle database using a mixture of CTAS and partition exchange loading. The example we will leverage was posted by Mark...

SDK

ODI 11g – Insight to the SDK

This post is a useful index into the ODI SDK that cross references the type names from the user interface with the SDK class and also the finder for how to get a handle on the object or objects. The volume of content in the SDK might seem a little ominous, there is a lot there, but there is a general pattern to the SDK that I will describe here.Also I will illustrate some basic CRUD operations so you can see how the SDK usage pattern works. The examples are written in groovy, you can simply run from the groovy console in ODI 11.1.1.6. Entry to the PlatformObjectFinder SDKodiInstanceodiInstance (groovy variable for console)OdiInstanceTopology ObjectsObjectFinderSDKTechnologyIOdiTechnologyFinderOdiTechnologyContextIOdiContextFinderOdiContextLogical SchemaIOdiLogicalSchemaFinderOdiLogicalSchemaData ServerIOdiDataServerFinderOdiDataServerPhysical SchemaIOdiPhysicalSchemaFinderOdiPhysicalSchemaLogical Schema to Physical MappingIOdiContextualSchemaMappingFinderOdiContextualSchemaMappingLogical AgentIOdiLogicalAgentFinderOdiLogicalAgentPhysical AgentIOdiPhysicalAgentFinderOdiPhysicalAgentLogical Agent to Physical MappingIOdiContextualAgentMappingFinderOdiContextualAgentMappingMaster RepositoryIOdiMasterRepositoryInfoFinderOdiMasterRepositoryInfoWork RepositoryIOdiWorkRepositoryInfoFinderOdiWorkRepositoryInfoProject Objects ObjectFinder SDKProjectIOdiProjectFinderOdiProjectFolderIOdiFolderFinderOdiFolderInterfaceIOdiInterfaceFinderOdiInterfacePackageIOdiPackageFinderOdiPackageProcedureIOdiUserProcedureFinderOdiUserProcedureUser FunctionIOdiUserFunctionFinderOdiUserFunctionVariableIOdiVariableFinderOdiVariableSequenceIOdiSequenceFinderOdiSequenceKMIOdiKMFinderOdiKMLoad Plans and ScenariosObjectFinder SDKLoad PlanIOdiLoadPlanFinderOdiLoadPlanLoad Plan and Scenario FolderIOdiScenarioFolderFinderOdiScenarioFolderModel Objects ObjectFinder SDKModelIOdiModelFinderOdiModelSub ModelIOdiSubModelOdiSubModelDataStoreIOdiDataStoreFinderOdiDataStoreColumnIOdiColumnFinderOdiColumnKeyIOdiKeyFinderOdiKeyConditionIOdiConditionFinderOdiConditionOperator ObjectsObjectFinder SDKSession FolderIOdiSessionFolderFinderOdiSessionFolderSessionIOdiSessionFinderOdiSessionScheduleOdiSchedule How to Create an Object?Here is a simple example to create a project, it uses IOdiEntityManager.persist to persist the object.import oracle.odi.domain.project.OdiProject;import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition; txnDef = new DefaultTransactionDefinition(); tm = odiInstance.getTransactionManager() txnStatus = tm.getTransaction(txnDef) project = new OdiProject("Project For Demo", "PROJECT_DEMO")odiInstance.getTransactionalEntityManager().persist(project) tm.commit(txnStatus) How to Update an Object?This update example uses the methods on the OdiProject object to change the project’s name that was created above, it is then persisted.import oracle.odi.domain.project.OdiProject;import oracle.odi.domain.project.finder.IOdiProjectFinder;import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition; txnDef = new DefaultTransactionDefinition(); tm = odiInstance.getTransactionManager() txnStatus = tm.getTransaction(txnDef) prjFinder = (IOdiProjectFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiProject.class); project = prjFinder.findByCode("PROJECT_DEMO"); project.setName("A Demo Project"); odiInstance.getTransactionalEntityManager().persist(project) tm.commit(txnStatus) How to Delete an Object?Here is a simple example to delete all of the sessions, it uses IOdiEntityManager.remove to delete the object.import oracle.odi.domain.runtime.session.finder.IOdiSessionFinder;import oracle.odi.domain.runtime.session.OdiSession;import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition; txnDef = new DefaultTransactionDefinition(); tm = odiInstance.getTransactionManager() txnStatus = tm.getTransaction(txnDef) sessFinder = (IOdiSessionFinder)odiInstance.getTransactionalEntityManager().getFinder(OdiSession.class); sessc = sessFinder.findAll();sessItr = sessc.iterator()while (sessItr.hasNext()) {  sess = (OdiSession) sessItr.next()  odiInstance.getTransactionalEntityManager().remove(sess) }tm.commit(txnStatus) This isn't an all encompassing summary of the SDK, but covers a lot of the content to give you a good handle on the objects and how they work. For details of how specific complex objects are created via the SDK, its best to look at postings such as the interface builder posting here. Have fun, happy coding!

This post is a useful index into the ODI SDK that cross references the type names from the user interface with the SDK class and also the finder for how to get a handle on the object or objects. The...

ETL

ODI 11g – Expert Accelerator for Model Creation

Following on from my post earlier this morning on scripting model and topology creation tonight I thought I’d add a little UI to make those groovy functions a little more palatable. In OWB we have experts for capturing user input, with the groovy console we open up opportunities to build UI around the scripts in a very easy way – even I can do it;-)After a little googling around I found some useful posts on SwingBuilder, the most useful one that I used for the dialog below was this one here. This dialog captures user input for the technology and context for the model and logical schema etc to be created. You can see there are a variety of interesting controls, and its really easy to do. The dialog captures the users input, then when OK is pressed I call the functions from the earlier post to create the logical schema (plus all the other objects) and model. The image below shows what was created, you can see the model (with typo in name), the model is Oracle technology and references the logical schema ORACLE_SCOTT (that I named in dialog above), the logical schema is mapped via the GLOBAL context to the data server ORACLE_SCOTT_DEV (that I named in dialog above), and the physical schema used was just the user name that I connected with – so if you wanted a different user the schema name could be added to the dialog. In a nutshell, one dialog that encapsulates a simpler mechanism for creating a model. You can create your own scripts that use dialogs like this, capture input and process.You can find the groovy script for this is here odi_create_model.groovy, again I wrapped the user capture code in a groovy function and return the result in a variable and then simply call the createLogicalSchema and createModel functions from the previous posting. The script I supplied above has everything you will need. To execute use Tools->Groovy->Open Script and then execute the green play button on the toolbar.Have fun.

Following on from my post earlier this morning on scripting model and topology creation tonight I thought I’d add a little UI to make those groovy functions a little more palatable. In OWB we have...

ETL

ODI 11g - Scripting the Model and Topology

Scripting is the ideal mechanism to automate start up and teardown for repeated tasks and those that you just want to automate. Here are a couple of more illustrations of how to easily construct a model in ODI, the script will also create all of the topology objects. The script uses two methods; createLogicalSchema and createModel. The createLogicalSchema creates the logical schema, data server, physical schema and logical schema to physical schema mapping via a context all from one function call.The signature of these methods looks like this;createLogicalSchemacontextCode – the ODI code for the context used to map the logical schema to the physicaltechnologyCode – the ODI code for the technologynameForLogicalSchema – the name for the logical schema to createNameForDataserver – the name for the data server to createuserNameForAuthentication – the username for the connection to the data serverpasswordForAuthentication – the password for the connection to the data serverurlForAuthentication – the URL for the connection to the data serverdriverForAuthentication – the JDBC driver for the connection to the data serverschemaForAuthentication – the schema to use for the ODI physical schemacreateModellogicalSchemaObject – the ODI logical schema object (instance of ODILogicalSchema)contextCode – the ODI context code for reverse engineeringnameForModel – the name for the model to createcodeForModel – the code for the model to createSo with these two methods or variations of them you can easily construct your topology objects and models. For example the call below creates a new model named ORACLE_MODEL and all of the topology objects that will allow me to go straight to reverse engineering when the script has been run.lschema = createLogicalSchema("GLOBAL", "ORACLE", "ORACLE_EBS", "ORACLE_HQLINUX_DEV", "SCOTT",     ObfuscatedString.obfuscate("<password>"), "jdbc:oracle:thin:@localhost:1521:orcl", "oracle.jdbc.OracleDriver", "SCOTT") createModel(lschema, "GLOBAL", "ORACLE_MODEL", "ORACLE_MODEL")Here is the source code for the script import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition; import oracle.odi.domain.util.ObfuscatedString;import oracle.odi.domain.model.OdiModel;import oracle.odi.domain.topology.OdiLogicalSchema;import oracle.odi.domain.topology.OdiPhysicalSchema;import oracle.odi.domain.topology.OdiDataServer;import oracle.odi.domain.topology.OdiContext;import oracle.odi.domain.topology.OdiTechnology;import oracle.odi.domain.topology.OdiContextualSchemaMapping;import oracle.odi.domain.topology.AbstractOdiDataServer;import oracle.odi.domain.topology.finder.IOdiContextFinder;import oracle.odi.domain.topology.finder.IOdiTechnologyFinder; def createLogicalSchema(contextCode, techCode, schName, dataserverName, userName, password, url, driver, schema) {   txnDef = new DefaultTransactionDefinition();   tm = odiInstance.getTransactionManager()   txnStatus = tm.getTransaction(txnDef)   contextFinder = (IOdiContextFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiContext.class);  context = contextFinder.findByCode(contextCode);   techFinder = (IOdiTechnologyFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiTechnology.class);  tech = techFinder.findByCode(techCode);   lschema = new OdiLogicalSchema(tech, schName)  dserver = new OdiDataServer(tech, dataserverName)  con = new AbstractOdiDataServer.JdbcSettings(url, driver)  dserver.setConnectionSettings(con)  dserver.setUsername(userName)  dserver.setPassword(password)  pschema = new OdiPhysicalSchema(dserver)  pschema.setSchemaName(schema)  pschema.setWorkSchemaName(schema)  cschema = new OdiContextualSchemaMapping(context, lschema, pschema)   odiInstance.getTransactionalEntityManager().persist(lschema)  odiInstance.getTransactionalEntityManager().persist(dserver)  tm.commit(txnStatus)   return lschema} def createModel(lschema, contextCode, modName, modCode) {   txnDef = new DefaultTransactionDefinition();   tm = odiInstance.getTransactionManager()   txnStatus = tm.getTransaction(txnDef)   contextFinder = (IOdiContextFinder) odiInstance.getTransactionalEntityManager().getFinder(OdiContext.class);  context = contextFinder.findByCode(contextCode);   mod = new OdiModel(lschema, modName, modCode)  mod.setReverseContext(context)  odiInstance.getTransactionalEntityManager().persist(mod)  tm.commit(txnStatus)   return mod} lschema = createLogicalSchema("GLOBAL", "ORACLE", "ORACLE_EBS", "ORACLE_HQLINUX_DEV", "SCOTT", ObfuscatedString.obfuscate("<password>"), "jdbc:oracle:thin:@localhost:1521:orcl", "oracle.jdbc.OracleDriver", "SCOTT") createModel(lschema, "GLOBAL", "ORACLE_MODEL", "ORACLE_MODEL")Have fun scripting!

Scripting is the ideal mechanism to automate start up and teardown for repeated tasks and those that you just want to automate. Here are a couple of more illustrations of how to easily construct a...

Application Connectors

ODI 11g - Getting Scripting with Groovy

The addition of the groovy interpreter to the ODI designer now let’s you easily script any tasks that you repeatedly perform. The documentation has illustrations here, so using the ODI 11g SDK you can encapsulate common tasks in simple groovy functions.Groovy can be executed by executing a script, you can create a new one or open an existing groovy script; You will then see a new groovy window appear in the IDE plus the execute green button is enabled on the toolbar. I have taken the script defined here and shown below in its more minimal groovy form and parameterized the script in a groovy function ‘createProject’. I can then call createProject with whatever values for the project and folder I wish to create.import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition;import oracle.odi.domain.project.OdiProject;import oracle.odi.domain.project.OdiFolder; def createProject(projectName, projectCode, folderName) {  txnDef = new DefaultTransactionDefinition();  tm = odiInstance.getTransactionManager()  txnStatus = tm.getTransaction(txnDef)  project = new OdiProject(projectName, projectCode)  folder = new OdiFolder(project, folderName)  odiInstance.getTransactionalEntityManager().persist(project)  tm.commit(txnStatus)} createProject("EDW Staging", "EDW", "Initialization")So in the UI if I execute as follows; After executing the script I refresh the Designer tree and see my new project.

The addition of the groovy interpreter to the ODI designer now let’s you easily script any tasks that you repeatedly perform. The documentation has illustrations here, so using the ODI 11g SDK you...

Application Connectors

ODI 11g – Interface Builder

In the previous blogs such as the one here I illustrated how to use the SDK to perform interface creation using various auto mapping options for generating 1:1 interfaces either using positional based matching, like names ignoring case and so on. Here we will see another example (download OdiInterfaceBuilder.java) showing a different aspect using a control file which describes the interface in simple primitives which drives the creation. The example uses a tab delimited text file to control the interface creation, but it could be easily taken and changed to drive from Excel, XML or whatever you wanted to capture the design of the interface.The interface can be as complete or incomplete as you’d like, so could just contain the objects or could be concise and semantically complete. The control file is VERY simple and just like ODI requests the minimal amount of information required. The basic format is as follows;DirectiveColumn 2Column 3Column 4Column 5source<model><datastore>     can add many    target<model><datastore>  mapping<column><expression>     can add many    join<expression>      can add many    filter<expression>       can repeat many    lookup<model><datastore><alias><expression>   can add many    So for example the control file below can define the sources, target, joins, mapping expressions etc;source    SCOTT    EMP source    SCOTT    DEPT target    STG_MODEL_CASE    TGTEMP mapping    ENAME    UPPER(EMP.ENAME) mapping    DNAME    UPPER(DEPT.DNAME) mapping    DEPTNO    ABS(EMP.EMPNO) join    EMP.DEPTNO = DEPT.DEPTNO lookup    SCOTT    BONUS    BONUS    BONUS.ENAME = EMP.ENAME filter    EMP.SAL > 1 mapping    COMM    ABS(BONUS.COMM)When executed, this generates the interface below with the join, filter, lookup and target expressions from the file. You should be able to join the dots between the control file sample and the interface design above. So just like the initial post you will compile and execute the code, but use the different classname OdiInterfaceBuilder;java –classpath <cp> OdinterfaceBuilder jdbc:oracle:thin:@localhost:1521:ora112 oracle.jdbc.OracleDriver ODI_MASTER mypwd WORKREP1 SUPERVISOR myodipwd STARTERS SDK DEMO1 <myinterfacecontrolfile.tabThe interface to be created is passed from the command line. You can intersperse other documentation lines between the control lines so long as the control keywords in first column don’t clash. Anyway some useful snippets of code for those learning the SDK, or for those wanting to capture the design outside and generate ODI Interfaces. Have fun!

In the previous blogs such as the one here I illustrated how to use the SDK to perform interface creation using various auto mapping options for generating 1:1 interfaces either using positional...

ETL

ODI 11g – More accelerator options

A few more options added into the interface accelerator that I blogged about earlier here in initial post and a later one here. Added options for doing position based and case sensitive/insensitive options. These were simple changes added into the auto map class. You can now find the latest updates below;OdiInterfaceAccelerator.javaOdiAutoMapper.javaSo just like the initial post you will compile and execute the code, but use the different classname OdiInterfaceAccelerator;java –classpath <cp> OdinterfaceAccelerator jdbc:oracle:thin:@localhost:1521:ora112 oracle.jdbc.OracleDriver ODI_MASTER mypwd WORKREP1 SUPERVISOR myodipwd STARTERS SDK <icontrol.csvIn the automapper I created a couple of options that can drive the accelerator, it supports;positional based match (match columns by position from source to target)exact match case sensitive  (match EMPNO with EMPNO, but not empno with EMPNO)exact match case insensitive (match EMPNO with empno)src/target ends with sensitive/insensitive (match PFX_empno with empno/EMPNO)src/target starts with sensitive/insensitive (match empno_col with empno/EMPNO)Note, you can also use the “diagrams” in the models to greatly accelerate development if source and targets have the same structure – if not then you have to go through the SDK route above if you want to accelerate.

A few more options added into the interface accelerator that I blogged about earlier here in initial post and a later one here. Added options for doing position based and case sensitive/insensitive...

ETL

OWB – SQLLoader, big data files and logging

OWB’s flat file loading support is rich and there are a lot of configuration properties for mappings in general and the properties exposed for SQLLoader mappings are expansive. One of the things that OWB does is load the resultant logs from SQLLoader into the runtime audit tables through the runtime service and also scrapes audit information (number of rows etc) from the file.The thing to be wary of is whether a verbose output is used for feedback – combined with the rows per commit property (default is 200) the SQLLoader mapping will write a feedback message every 200 rows committed. Imagine…big data files and filling a log file with this kind of message every n rows can be quite large! With this setting your log will have a lot of ‘Commit point reached’ messages in the log.  The ‘Supress’ properties (a typo I know) can be used to hide this information and make the logs compact, so switch on the Supress Feedback property as follows and the log shrinks, with no verbose output;   This equates to the SQLLoader SILENT option, if we look at the control file generated by OWB, we see the SILENT=(FEEDBACK) option is now added;  The tooltip for the misspelled ‘Supress Feedback’ is ‘Suppresses the “commit point reach” messages that normally appear on the screen’. Others have come across this on the forum also.

OWB’s flat file loading support is rich and there are a lot of configuration properties for mappings in general and the properties exposed for SQLLoader mappings are expansive. One of the things that...

OWB 11gR2 - Windows and Linux 64-bit clients available

In addition to the integrated release of OWB in the 11.2.0.3 Oracle database distribution, the following 64-bit standalone clients are now available for download from Oracle Support. OWB 11.2.0.3 Standalone client for Windows 64-bit - 13365470 OWB 11.2.0.3 Standalone client for Linux X86 64-bit - 13366327 This is in addition to the previously released 32-bit client on Windows. OWB 11.2.0.3 Standalone client for Windows 32-bit - 13365457 The support document Major OWB 11.2.0.3 New Features Summary has details for OWB 11.2.0.3 which include the following.Exadata v2 and oracle Database 11gR2 support capabilities;Support for Oracle Database 11gR2 and Exadata compression types Even more partitioning: Range-Range, Composite Hash/List, System, Reference Transparent Data Encryption support Data Guard support/certification Compiled PL/SQL code generation Capabilities to support data warehouse ETL best practices;Read and write Oracle Data Pump files with external tables External table preprocessor Partition specific DML Bulk data movement code templates: Oracle, IBM DB2, Microsoft SQL Server to Oracle Integration with Fusion Middleware capabilities;Support OWB's Control Center Agent on WLS Lots of interesting capabilities in 11.2.0.3 and the availability of the 64-bit client I'm sure is welcome news for many!

In addition to the integrated release of OWB in the 11.2.0.3 Oracle database distribution, the following 64-bit standalone clients are now available for download from Oracle Support. OWB 11.2.0.3...

SAP

Debugging OWB generated SAP ABAP code executed through RFC

Within OWB if you need to execute ABAP code using RFC you will have to use the SAP Function Module RFC_ABAP_INSTALL_AND_RUN. This function module is specified during the creation of the SAP source location. Usually in a Production environment a copy of this function module is used due to security restrictions.When you execute the mapping by using this Function Module you can’t see the actual ABAP code that is passed on to the SAP system. In case you want to take a look at the code that will be executed on the SAP system you need to use a custom Function Module in SAP. The easiest way to do this is to make a copy of the Function Module RFC_ABAP_INSTALL_AND_RUN and call it say Z_TEST_FM. Then edit the code of the Function Module in SAP as belowFUNCTION Z_TEST_FM . DATA: BEGIN OF listobj OCCURS 20. INCLUDE STRUCTURE abaplist. DATA: END OF listobj. DATA: begin_of_line(72). DATA: line_end_char(1). DATA: line_length type I. DATA: lin(72). loop at program. append program-line to WRITES. endloop. ENDFUNCTION.Within OWB edit the SAP Location and use Z_TEST_FM as the “Execution Function Module” instead of  RFC_ABAP_INSTALL_AND_RUN. Then register this location. The Mapping you want to debug will have to be deployed. After deployment you can right click the mapping and click on “Start”. After clicking start the “Input Parameters” screen will be displayed. You can make changes here if you need to. Check that the parameter BACKGROUND is set to “TRUE”.After Clicking “OK” the log for the execution will be displayed. The execution of Mappings will always fail when you use the above function module. Clicking on the icon “I” (information) the ABAP code will be displayed.  The ABAP code displayed is the code that is passed through the Function Module. You can also find the code by going through the log files on the server which hosts the OWB repository. The logs will be located under <OWB_HOME>/owb/log.Patch #12951045 is recommended while using the SAP Connector with OWB 11.2.0.2. For recommended patches for other releases please check with Oracle Support at http://support.oracle.com

Within OWB if you need to execute ABAP code using RFC you will have to use the SAP Function Module RFC_ABAP_INSTALL_AND_RUN. This function module is specified during the creation of the SAP...

ETL

Parallel Processing with DBMS_PARALLEL_EXECUTE

Here is another illustration of some of the powerful capabilities of the DBMS_PARALLEL_EXECUTE package in the Oracle database, carrying on from the earlier post here. One of the comments from that post was on how to insert into a different named table within each chunk and that insert can perform parallel DML also. This kind of scenario could be interesting for very high end processing, it could be end point target tables or tables that are prepared and then you perform partition exchanges with them or something.The image below shows a variation on the original post where rather than inserting into a specific partition, you write into a specific table. Driving the whole process can be your own chunking criteria, the question was how to drive this process from a table using SQL such as ‘select distinct level_key, level_key from chunk_table’ where chunk_table has the level_key and the target table name. For example it could contain the following data;level_keytable_name1sales_level12sales_level23sales_level34sales_level4… So the first chunk with level_key 1 will write the results to table sales_level1 etc.You can use the DBMS_PARALLEL_PACKAGE as follows to create this functionality. The start/end values have to be of data type NUMBER, so you will have to lookup the (target) table name inside your PLSQL block within the statement provided in the run_task call.This block has the query to determine the chunks .....begin   begin     DBMS_PARALLEL_EXECUTE.DROP_TASK(task_name => 'TASK_NAME');   exception when others then null;   end;   DBMS_PARALLEL_EXECUTE.CREATE_TASK(task_name => 'TASK_NAME');   DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL(task_name => 'TASK_NAME',   sql_stmt =>'select distinct level_key, level_key from chunk_table', by_rowid => false);end;Then the next block will construct the and process the tasks......begin   DBMS_PARALLEL_EXECUTE.RUN_TASK (task_name => 'TASK_NAME',     sql_stmt =>'declare       s varchar2(16000); vstart_id number := :start_id; vend_id number:= :end_id;       table_name varchar2(30);       begin         select table_name into table_name from chunk_table where level_key=vstart_id;         s:=''insert into ''||table_name||'' select /*+ PARALLEL(STG, 8) */ colx from STAGING_TABLE STG           where level_key =:vstart_id'';         execute immediate s using vstart_id;         commit;     end;',     language_flag => DBMS_SQL.NATIVE, parallel_level => 2 );end;The anonymous PLSQL block can be any arbitrary code, you can see the table name of the target is retrieved, the example does a parallel INSERT using the hint PARALLEL(STG,8). Anyway, good to share.

Here is another illustration of some of the powerful capabilities of the DBMS_PARALLEL_EXECUTE package in the Oracle database, carrying on from the earlier post here. One of the comments from that...

ETL

Generating XML with Experts

The leveraging XDB post (here) from a few years ago is one of the most actively read posts, since that was done there have been a few more updates on the expert posted within it. One of the updated areas was in the generation of XML using the Oracle Database using the expert., the areas include supporting generated a single document vs multiple documents and the ability to include/exclude attributes from the content, plus whether to create the attributes as XML properties or XML elements.A recent query was regarding how the ‘Create XML from objects’ menu option gets created. This is added just by enabling the expert on the ‘Tables’ node in the tree, here we see the sequence of actions to do this in OWB 11gR2, you must first import the expert’s MDL, then add the expert to the tree as follows.First right click on Tables node and select ‘Maintain Creation Experts Here' (you can add any of your own custom experts to parts of the tree also);Then in the XML_ETL folder within public experts, enable the CREATE_XML_FROM_OBJECTS expert; That’s it! Now you can run the expert from the tree. For example now click on the Tables node, you will see the ‘Create XML from objects’ option. This then runs the expert, the dialog was enhanced to include a ‘Generate Root’ option – this was added so that all generated XML fragments are wrapped in a single element rather than created as XML documents. Using this lets you generate one document like;<AllDepartments><Department name=’ACCOUNTING’/><Department name=’RESEARCH’/></AllDepartments>rather than multiple documents like (where Department is the route node);<Department name=’ACCOUNTING’/><Department name=’RESEARCH’/>So let’s select ‘Generate Root’ and see how it works…. As before we get to enter the name of the pluggable map that gets generated. We then choose the tables for or document, and order the master to the detail, we will have departments and the employees nested inside the department;We then can define the element name for the root (because we selected generate root), and the dept and emp tables. For each table we can then define the XML element/attribute names for the columns also, we can also define whether to exclude attributes, or define an element name for the attribute rather than a property name. For the EMP XML element details we will exclude the foreign key column DEPTNO, and provide nice business names for the properties.After this, the pluggable mapping is generated. We can use the table function from the earlier post and the pluggable mapping to write the XML to file, for example we generate the following from the SCOTT schema. Fairly simple example of leveraging the database along with experts to generate based on some basic inputs from the guided expert.

The leveraging XDB post (here) from a few years ago is one of the most actively read posts, since that was done there have been a few more updates on the expert posted within it. One of the updated...

ETL

OWB 11gR2 - Creating Interval Partitions

Designing partitioned tables in OWB is done in the table editor in the partitioned tab, the partitions tab let’s you design and deploy complex partitioning strategies. Here we will see how to define an interval partition (see an example in the Oracle Database VLDB and Partitioning documentation here), we will partition the SALES fact table using a date column (TIMES) in the table below. On the partitioning tab there is a table with a tree control inside, essentially there are 4 steps for this example; defining the partition type, define the key columns, define the interval expression and the initial partition details. The buttons Add/Add Subpartition/Add Hash Count/Delete get enabled when you select rows, so you can modify the definition. Generating the code we can see the DDL for the Oracle partitioning clause has been included. To create the table definition in OMB you can do something like the following – note there is some double quoting in the expressions.OMBCREATE TABLE 'SALES_TAB' ADD COLUMN 'TIMES' SET PROPERTIES (DATATYPE) VALUES ('DATE') # Plus the rest of your table definition....OMBALTER TABLE 'SALES_TAB' ADD PARTITION_KEY 'TIMES' SET PROPERTIES (TYPE,INTERVAL) VALUES ('RANGE','NUMTOYMINTERVAL(1,''MONTH'')') OMBALTER TABLE 'INTERVAL_TAB' ADD PARTITION 'PART_01' SET PROPERTIES (VALUES_LESS_THAN) VALUES ('TO_DATE(''01-NOV-2007'',''DD-MON-YYYY'')')That’s it!

Designing partitioned tables in OWB is done in the table editor in the partitioned tab, the partitions tab let’s you design and deploy complex partitioning strategies. Here we will see how to define...

Application Connectors

OWB – Calling Java from Process Flows

Although the doc shows how to call java from process flows it still may not be clear enough..or even correct, here’s a quick illustration to plug the gap. The section on the JAVA activity is here just in case you didn’t know. Below is the simple example java class I fabricated…public class javatest { public static void main(String args[]) {  System.out.println("In main of my java, with " + args.length + " arguments.");  for (int i = 0; i < args.length; i++) {    System.out.println("  arg  " + i + " : " + args[i]);  }  if (args.length == 2) {    int rslt = new Integer(args[0]) + new Integer(args[1]);    System.exit(rslt);  } }}The java I will call is the static main method which takes an array of arguments and adds the first and second argument and uses that as the status. A silly example I know…but at least you see how parameters are passed into and out of the java call. There is also some debug to print the parameters etc so you can see where it goes in OWB. I compiled this class with JDK 1.5 (it must be 1.5 or less than) and jar’d the class file into a jar file.  The classpath, class_name and result_code parameters to the JAVA activity are pretty much standard as you would setup any activity in process flow. The parameter passing into the java activity is done via the PARAMETER_LIST activity parameter, see the value ,$(PARAM1),$(PARAM2) this uses the custom parameters PARAM1 and PARAM2 that I added to the JAVA activity in my flow.In the above screenshot I have tried to show all of the important parts for configuring this example, the JAVA activity input parameters are bound to the process flow parameters PROC_ARG1 and PROC_ARG2 respectively.So when I execute the flow, the arguments used will be passed to the java class. So if I pass 0 and 0 for the arguments the result of the java will be 0+0, 0 is a success code for the java activity. I can see what happened in the audit, I can see the process parameters and the activity parameters, I can also see the std output from the java, see the ‘Execution Details’ dialog and the Log panel for that output… If I execute the process flow passing the values 1 and 2, then the result of the java is 1+2 which is an error code in java. See below for when the values 1 and 2 are passed …. There is another parameter that has not been used and that was the RUN_DIRECTORY parameter, this is simply the working directory to where the java command is invoked from. So this is course grained integration of java using process flows, in 11gR2 its also possible to invoke EJBs from a process flow, and there are other integration techniques from java stored procedures or tables functions to java in 11gR2 Code Templates.

Although the doc shows how to call java from process flows it still may not be clear enough..or even correct, here’s a quick illustration to plug the gap. The section on the JAVA activity is here just...

ETL

OWB 11gR2 – DML Error Logging

A common technique for set based error handling with the Oracle database is using DML Error Logging which has been in the database since 10gR2, I posted an entry back in 2007 illustrating how to use this with OWB 10gR2. With OWB 11gR2 there is a change to how this error table is incorporated, it is now controlled much more in the mapping, so the DML error table name is defined within the table operator in the mapping (not within the table data object as in OWB 10gR2).So if you look at a table operator in mapping, you will see the ‘DML Error table name’ property in the ‘Error table’ group and by default it has no value. By entering a name such as ‘PROD_ERRORS’ you will enable DML error logging on that table, the table will be created if it does not exist when the mapping is deployed. If you now generate the intermediate code generation and inspect you will now see the ‘LOG ERRORS INTO’ clause also generated. Looking into the code for the mapping you will see where it attempts to create the DML Error logging table if it does not exist using the DBMS_ERRLOG.CREATE_ERROR_LOG procedure. You should ensure that you are using 11.2.0.2 at least for this since there was a bug impacting the DML error logging behavior on subsequent executions.

A common technique for set based error handling with the Oracle database is using DML Error Logging which has been in the database since 10gR2, I posted an entry back in 2007 illustrating how to use...

ETL

OWB 11gR2 - SAP ins and outs

In 11gR2 there are a few SAP mapping runtime parameters that are not explained very well and the documentation describes data retrieval techniques without telling you what exact properties to use for each of the approaches. The properties which are not described are the INSTALL ONLY and the Include FTP properties, the table below illustrates for the modes in the documentation what the property values need to be set to.Mode \ PropertiesINSTALL ONLYInclude FTPAutomatedFALSETRUESemi-automatedTRUE/FALSE for DEV, then send ABAP to SAP admin for PRODTRUEManualTRUE/FALSE for DEV, then send ABAP to SAP admin for PRODFALSEThe INSTALL ONLY property controls the deployment of the ABAP into the SAP system, general the SAP administrator will not let you deploy directly into the production SAP system, so OWB supports the following modes to deploy the mapping into;OWB runtime schema and the ABAP into SAP, or OWB runtime schema only, the ABAP report must be manually configured These modes above are what dictate the system configurations in the doc along with the 'Include ftp' option which impacts how the runtime operator works; The Automated System mentioned in the OWB documentation relates to deploying into SAP (point 1 above) and transferring the file and loading into Oracle (deployment of ABAP to SAP, execution, transfer and loading done by OWB).The Semi-automated System mention in the documentation relates to using point 2 above and transferring the file and loading into Oracle (execution, transfer and loading done by OWB, deployment of ABAP in SAP done by administrator).The Manual System mention in the documentation relates to using point 2 above (execution done by OWB, transfer and load by user's process flow or other mechanism of choice, deployment of ABAP into SAP done by administrator).Option 2 is used for production environments where the SAP administrator does not let you deploy directly into the SAP system (see this for background). For semi-automated and manual system configurations if the report name is changed by the SAP administrator you can change the mapping's configuration for production in OWB and deploy such that the names match. When the INSTALL ONLY configuration property in the mapping is set to true the ABAP is taken an manually transferred by the SAP administrator. This relates back to the technical note (552621.1) on deploying and running on production environments - being able to control the ABAP report name, and the execution function module. When the mapping is executed the file system that SAP writes to can be either a shared folder setup or ftp/https is used to transfer from SAP to the OWB system. If you plan to transfer data using a shared directory (shared between the SAP system and the OWB runtime service) you need to validate that both systems can access the directory. If you are using FTP you need to validate that both ends can transfer into the directory. One thing that is a pain today is that the load is only done when ‘Include FTP’ option is true, soooo if you are using a shared folder..there is no need to ftp right, but can only load when ftp is used.The 'Include ftp' configuration parameter on the mapping that controls whether when the mapping is executed by OWB will include the transfer of the file by one of the transfer types (ftp/http/https) defined in the SAP location. Above you see I set 'Include ftp' to false, in doing so when the mapping is executed the ABAP code writes a file into /tmp and I will manually transfer the file in a process flow for example (or it may be written to a shared file system). One of the restrictions is that there is no sftp in this list as you notice. So what customers have done mostly is perform the sftp in a process flow after the mapping has been executed. That way the development/test/prod designs are identical. The ABAP generated by OWB when executed generates a SQL*Loader control file that has both the header and the data, so an activity to load the data using the SQL*Loader control file must be constructed (see here for details). If I change the staging file directory configuration parameter for the mapping... then the generated ABAP has this value; …so in reverse if the administrator changes the ABAP report when moving into production you can align the production OWB mapping with the values he used and everything will be in sync (if using OWB's built in ftp capability), or change your custom process flow to have the values in a process flow parameter for example. My location for the above used FILE transport type. If I had enabled 'Include FTP' above in the mapping configuration then I would have to have set the transport type to FTP/HTTP/HTTPS and supplied credentials for the transport properties. These are just protocols to retrieve the staged file (so they are doing 'ftp get' 'http get' 'https get'), the get is initiated from the OWB runtime connecting to the ftp service. The ftp service could be on the SAP system or on another system that the administrator setup to decouple from the SAP system. Hopefully that helps bridge the gap between what is mentioned in the automated/semi-automated/manual system configurations in the doc and how you configure the mappings.

In 11gR2 there are a few SAP mapping runtime parameters that are not explained very well and the documentation describes data retrieval techniques without telling you what exact properties to use for...

ETL

OWB - Mapping Scripting Accelerator

Carrying on from the ODI posts on building lots of simple interfaces rapidly using the SDK, we can see here how to use the OMB scripting commands in OWB to build maps in an identical manner as the post here. The script takes as input the same control file used in the post on the ODI SDK which specifies the mapping name, source module and table and the target module and table name. The script is a simple tcl loop over the file which invokes OMBCREATE MAPPING to create the mapping in OWB.set f [open /temp/icontrol.csv] while {1} {   set line [gets $f]   if {[eof $f]} {       close $f       break   }   set toks [split $line ","]   set g_map_name [lindex $toks 0]   set g_src_obj_name [lindex $toks 2]   set g_tgt_obj_name [lindex $toks 4]   set G_SRC_MODULE [lindex $toks 1]   set G_TGT_MODULE [lindex $toks 3]   set G_SRC_OBJ_TYPE TABLE   set G_TGT_OBJ_TYPE TABLE   set g_from_group INOUTGRP1   # a prefix so that we can differentiate from possibly adding the same name as source and target   set spfx "S_"   set tpfx "T_"   OMBCREATE MAPPING '$g_map_name'\   ADD $G_SRC_OBJ_TYPE OPERATOR '$spfx$g_src_obj_name' BOUND TO $G_SRC_OBJ_TYPE '$OMB_CURRENT_PROJECT/$G_SRC_MODULE/$g_src_obj_name'\   ADD $G_TGT_OBJ_TYPE OPERATOR '$tpfx$g_tgt_obj_name' BOUND TO $G_TGT_OBJ_TYPE '$OMB_CURRENT_PROJECT/$G_TGT_MODULE/$g_tgt_obj_name'\   ADD CONNECTION FROM GROUP '$g_from_group' OF OPERATOR '$spfx$g_src_obj_name'\   TO GROUP 'INOUTGRP1' OF OPERATOR '$tpfx$g_tgt_obj_name' BY NAME }One of the nice things in the MAPPING related commands is the group level mapping – see the ‘CONNECTION FROM GROUP  …  TO GROUP… BY NAME‘ as used above, there are also options (see the groupToGroupConnectType clause) from scripting as in the UI for ignoring suffixes/prefixes and being case insensitive. So for example to ignore target column prefixes of STG_ I can augment the BY NAME portion of the command above to be;..... BY NAME IGNORE TARGET_PREFIX 'STG_'Which in the UI would be defined as.... This will let me match a column name of EMPNO on source with a target name of STG_EMPNO on the target. Anyway that's a quick run through of something that popped up recently.

Carrying on from the ODI posts on building lots of simple interfaces rapidly using the SDK, we can see here how to use the OMB scripting commands in OWB to build maps in an identical manner as the...

Application Connectors

ODI 11g - Getting Groovy with ODI

Here we will see how you can use the groovy interpreter to automate, automate, automate (with no compile,compile,compile). When you get into large enterprise configurations of software, automation is the name of the game. The ODI 11g SDK as I blogged here lets you interact with pretty much the entire ODI set of metadata. The example I posted was a interfaceAccelerator.java (or for post 11.1.1.3 here) is a java class that needs compiled and then executed. Rather than compiling and executing (and needing to understand much of the build side of java), let’s look at an alternative….In OWB, there is an interpreter/interactive shell called OMBPlus and language that lets you automate the construction of artifacts and get smart with the tool. With ODI you can leverage the groovy interpreter for example, which lets you interact with java objects. You’ll need to download groovy and install. When groovy is installed you can then either execute the groovy process and process a groovy script as input or use the groovysh shell/interpreter. The groovy scripts can be scripts which use the ODI 11g SDK and interact with ODI.For example the command below, takes the groovy script interfaceAccelerator.groovy or post 11.1.1.3 here (which is a groovy version of the java class file previously used) as input, and the tabs.txt is piped in the standard input stream.groovy interfaceAccelerator.groovy < tabs.txtThe groovy script is a mixture of groovy commands and ODI SDK calls to generate the interfaces in ODI.The CLASSPATH including all the ODI jars is one thing that needs set up prior to executing the groovy script above, I just included all jars in the oracledi.sdk\lib directory.There was a small change in the transaction handling APIs after production 11g ODI, hence the different versions of the groovy and java scripts.Lots of possibilities as you can imagine! There are a few cool things we can extend the actual interfaceAceelerator to do...regardless of groovy or java, but just some more useful capabilities, more to come.

Here we will see how you can use the groovy interpreter to automate, automate, automate (with no compile,compile,compile). When you get into large enterprise configurations of software, automation is...

ETL

OWB 11gR2 – Table Functions

Here is a follow-up on an old post on table functions to illustrate how they work in 11gR2. There were some changes in 11gR2 to support table functions as a design object rather than a best practice, below is the OMB for the examples from the older post here. 11gR2 Scalar Table Function Example The code for the scalar example is pretty much the same apart for unbound table functions the default operator has a different output group name and no input group (really useful..not). # =============================================== # Source: Scalar TF Map: # =============================================== OMBCREATE MAPPING 'TF_SCALAR_PARAM' ADD TABLE_FUNCTION OPERATOR 'GET_EMPS' SET PROPERTIES (TABLE_FUNCTION_NAME) VALUES ('SCOTT.SCALARF') OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD INPUT_PARAMETER OPERATOR 'INPUTS' OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD ATTRIBUTE 'FILTERREC' OF GROUP 'OUTGRP1' OF OPERATOR 'INPUTS' SET PROPERTIES (DATATYPE,DEFAULT_VALUE) VALUES ('NUMBER', '0') OMBALTER MAPPING 'TF_SCALAR_PARAM'  ADD INPUT GROUP 'INGRP1' OF OPERATOR 'GET_EMPS' OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD ATTRIBUTE 'FILTER_EMP' OF GROUP 'INGRP1' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER') OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD ATTRIBUTE 'EMPNO' OF GROUP 'RETURN' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER') OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD ATTRIBUTE 'ENAME' OF GROUP 'RETURN' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE, LENGTH) VALUES ('VARCHAR2', '256') OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD ATTRIBUTE 'LVL' OF GROUP 'RETURN' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER') OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD CONNECTION FROM ATTRIBUTE 'FILTERREC' OF GROUP 'OUTGRP1' OF OPERATOR 'INPUTS' TO ATTRIBUTE 'FILTER_EMP' OF GROUP 'INGRP1' OF OPERATOR 'GET_EMPS' OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD TABLE OPERATOR 'EMPLOYEES' OMBALTER MAPPING 'TF_SCALAR_PARAM' ADD CONNECTION FROM GROUP 'RETURN' OF OPERATOR 'GET_EMPS' TO GROUP 'INOUTGRP1' OF OPERATOR 'EMPLOYEES' This produces the following mappings and generates the code illustrated in the Generation Results panel;   11gR2 Ref Cursor Table Function Example The ref cursor example is now a little different but when you reflect on what the actual table function definition is (look at the parameters, its a ref cursor) then it makes some sense. A ref cursor is build using the CONTRUCT_OBJECT operator, that might not have been obvious. # =============================================== # Source: Ref Cursor TF Map: # =============================================== OMBCREATE MAPPING 'TF_REFCURSOR_PARAM' ADD TABLE_FUNCTION OPERATOR 'GET_EMPS' SET PROPERTIES (TABLE_FUNCTION_NAME) VALUES ('SCOTT.REFCURSORF') OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD TABLE OPERATOR 'SRC' OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'C1' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC' SET PROPERTIES (DATATYPE) VALUES ('NUMBER') OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'C2' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC' SET PROPERTIES (DATATYPE, LENGTH) VALUES ('VARCHAR2', '256') OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'C3' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC' SET PROPERTIES (DATATYPE) VALUES ('NUMBER') OMBALTER MAPPING 'TF_REFCURSOR_PARAM'  ADD INPUT GROUP 'INGRP1' OF OPERATOR 'GET_EMPS' OMBALTER MAPPING 'TF_REFCURSOR_PARAM'  ADD CONSTRUCT_OBJECT OPERATOR 'SYS_REFCURSOR' OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD CONNECTION FROM ATTRIBUTE 'C1' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC' TO GROUP 'INGRP1' OF OPERATOR 'SYS_REFCURSOR' OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD CONNECTION FROM ATTRIBUTE 'C2' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC' TO GROUP 'INGRP1' OF OPERATOR 'SYS_REFCURSOR' OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD CONNECTION FROM ATTRIBUTE 'C3' OF GROUP 'INOUTGRP1' OF OPERATOR 'SRC' TO GROUP 'INGRP1' OF OPERATOR 'SYS_REFCURSOR' OMBALTER MAPPING 'TF_REFCURSOR_PARAM' MODIFY ATTRIBUTE 'OUTPUT1' OF GROUP 'OUTGRP1' OF OPERATOR 'SYS_REFCURSOR' SET PROPERTIES (DATATYPE) VALUES ('SYS_REFCURSOR') OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD CONNECTION FROM ATTRIBUTE 'OUTPUT1' OF GROUP 'OUTGRP1' OF OPERATOR 'SYS_REFCURSOR' TO GROUP 'INGRP1' OF OPERATOR 'GET_EMPS' OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'EMPNO' OF GROUP 'RETURN' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER') OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'ENAME' OF GROUP 'RETURN' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE, LENGTH) VALUES ('VARCHAR2', '256') OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD ATTRIBUTE 'LVL' OF GROUP 'RETURN' OF OPERATOR 'GET_EMPS' SET PROPERTIES (DATATYPE) VALUES ('NUMBER') OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD TABLE OPERATOR 'EMPLOYEES_SAL' OMBALTER MAPPING 'TF_REFCURSOR_PARAM' ADD CONNECTION FROM GROUP 'RETURN' OF OPERATOR 'GET_EMPS' TO GROUP 'INOUTGRP1' OF OPERATOR 'EMPLOYEES_SAL' When you execute this it builds the following mapping and generates the following code; 11gR2 Table Function Import The table function operator can operate on bound and unbound table functions, the table functions can be imported from the data dictionary, so the database import has been extended to include them and can be captured into the OWB design repository. So can now import the examples from the earlier post rather than defining via OMB; The import will import any dependent objects such as types and nested tables; Note I had success with this example but if there are PLSQL types and collections then this didn’t work (such as the ones from JP’s blog post on MapReduce with table functions). If you import the table functions then you can just drop the table function into the mapping canvas and build as normal; When the table function is dropped on to the canvas you will see the input is defined with type SYS_REFCURSOR, so you must provide a ref cursor as I described above using the CONSTTRUCT_OBJECT operator. Anyway that’s a very quick run through that hopefully will help illustrate how this hangs together.

Here is a follow-up on an old post on table functions to illustrate how they work in 11gR2. There were some changes in 11gR2 to support table functions as a design object rather than a best practice,...

ETL

ODI 11g – Interface Builder Accelerator

Often using ANY tool there are scenarios where there is a lot of grunt work, imagine Microsoft tools like Excel without VB and macros to accelerate and customize those boring repetitive tasks. Data integration and ETL design is exactly the same, the tool needs to expose an SDK to a base platform that you can use to make your life easier. Something to automate the grunt work that is common and very repetitive. The ODI 11g SDK let’s you script these kind of repetitive tasks. As an aside the ODI common format designer (see this post here) has a way for migrating like named objects, however using the SDK let’s you control much much more. To illustrate I have created a simple interface construction accelerator that you can download (interfaceAccelerator.java or 11.1.1.3 and after here), the accelerator generates ODI interfaces from a control file that defines the interface name, the source and the target – simple and a nice example for demo purposes. If you look at the java code, it is very basic (no pun intended).  It literally is a dozen lines of code. The image below illustrates the java program interfaceAccelerator using the ODI 11g SDK to take as inputs the configuration of the connection details and a control file specify the source to target datastore mappings. The code when called has a bunch of command line parameters shown below and the standard input stream is the interface control file, so the command line looks like; java –classpath <cp> interfaceAccelerator <url> <driver> <schema> <pwd> <workrep> <odiuser> <odiuserpwd> <project> <folder> < <control_file> the control file provided in the standard input stream needs to be a comma delimited file with the following structure interface_name,source_model,source_table,target_model,target_table … for example a sample command line using an Oracle repository could be java –classpath <cp> interfaceAccelerator jdbc:oracle:thin:@localhost:1521:ora112 oracle.jdbc.OracleDriver ODI_MASTER mypwd WORKREP1 SUPERVISOR myodipwd STARTERS SDK < icontrol.csv the interfaces will be created in the folder SDK and the project code is STARTERS. The icontrol.csv file used above was (remember the format is interface_name,source_model,source_table,target_model,target_table, this is just what I happened to use in this simple demo program); INTFC1,SCOTT,EMP,STG_MODEL,STG_EMP INTFC2,SCOTT,DEPT,STG_MODEL,STG_DEPT INTFC3,SCOTT,BONUS,STG_MODEL,STG_BONUS You can created as many interfaces from this driver control file as you desire, the interface generated will map from the source table to the target table and use ODI’s auto mapping to perform column level mapping of the source to target table, it will also create default source sets and use the default KM assignment. So you get a pretty useful set of stuff as a basis here. The interfaces generated whilst executing this accelerator look like the following, the table to table map with all of the like-named columns mapped, the physical flow configured with defaults KMs! You can take this code and customize to make it fit your needs or send in comments on how to do things. In summary if you are finding you desire ways of tuning your work to make using ODI even more productive, then you should look into the ODI 11g SDK and see if you can automate, automate, automate.

Often using ANY tool there are scenarios where there is a lot of grunt work, imagine Microsoft tools like Excel without VB and macros to accelerate and customize those boring repetitive tasks. Data...

ETL

OWB 11gR2 – XML

An XML post I did a while back was on Leveraging XDB, which illustrated how to leverage the XML SQL capabilities of the Oracle database. A couple years on, this post could as well have been titled Leveraging ODI, since here I’ll show how with a new XML platform defined, you can leverage the ODI XML JDBC driver and build code template mappings to extract and integrate XML in the same manner as ODI. First up copy the snpsxmlo.jar file from ODI 10g into the OWB owb/lib/ext directory and also on the server if you are running distributed. You’ll need to restart any processes (OWB, control center agent etc.)  to pick up the jar. Next download and execute this XML platform script here to define the XML platform in the OWB repository – the platform script defines datatypes and some properties of the platform (default URL, driver class etc.). You can execute this from OMBPlus or from the OMBPlus panel in the designer. This will create an XML node under the Databases node in the Projects panel and Locations panel. You build XML modules in the same way as you build any other module. Create the module and define the location using the URL for the XML JDBC driver (see doc here) – just like in ODI we can stage the data in a different database rather than what the default driver does, below we are just using the default in-memory mode; You can hit Test Connection and validate your input. If you get class not found ensure you put the driver JAR file in the correct directory. There is a bit of typing going on as you can see, so double check your work. After that thought its easy street. Now you can import just like regular modules. Select the table type, the import for XML will use the JDBC database metadata to return ‘tables’ corresponding to the elements in the XML. I just selected all the elements… After the import is complete, just like in ODI we get the columns for our elements and a table for each element, You can then view the data in the elements; Note there are some extra columns from our XML element definition; <!ELEMENT city (client*)> <!ATTLIST city     CITY_ID ID #REQUIRED     CITY_NAME CDATA #IMPLIED     POPULATION CDATA #IMPLIED > The columns CITYORDER, CITYPK and REGIONFK are the additional columns the XML JDBC driver has added to ‘relationalize’ the nested XML element. You can then go on and build code template mappings manipulating the XML, below I join region, city and client and load the result into an Oracle target table, I could do all sorts along the way. The execution unit view has the XML elements being joined in one execution unit and the Oracle target in another, this was the default execution plan generated by OWB. I used the SQL to Oracle load code template shipped with OWB and the Oracle target code template for the integration execution unit. Deploying and executing the mapping we can then view the resultant data in the target table, pretty straightforward. That’s a whirlwind pass on extracting information from XML in a very straight forward manner. The platform definition provided has been used here for demonstration to illustration how simple this can be. Writing XML is another interesting capability also possible with such an approach using this driver, but that’s another story.

An XML post I did a while back was on Leveraging XDB, which illustrated how to leverage the XML SQL capabilities of the Oracle database. A couple years on, this post could as well have been titled...

ETL

OWB 11gR2 – Getting to grips with configurations

The 11gR2 release has a useful feature for altering the shipped configuration property values – you can set default property values for all sorts of information, from code generation modes to analyze table settings and many more. The default values are captured in a template – a template for a configuration. So you can have different default settings per configuration, see the OBE here. Objects will use these default settings rather than those shipped with the product. In the past users have done this kind of thing by setting properties via script after they have been created. The ‘Globals’ panel has the configuration templates node, new templates can be created here; When the template editor is launched you will see all of the object types that OWB let’s you set default configuration properties for – you will see the object types listed in a tree with properties as children in the tree and the OWB seeded property values listed, a column with the new template name is available to change the values. Below I have changed the generation mode property to ‘Set based’ in my MY_CONFIG_TEMPLATE, I could have changed the analyze table statements property to true for example also or any other. This is a sparse matrix, so if no value is specified for your template the seeded default is used. You can look for yourself, but there are lots and lots of properties that you can tweak the defaults for. After I have finished with defining my template, how do I use it? It is a configuration template, so I can set the template to use in my configuration. below I am changing my default configuration to use the newly created template (if you leave the value to Unspecified the OWB seeded value is used). Then when I create new mappings, by default the default properties are the ones defined in the template, see below my mapping AMAP has the generation mode set to ‘Set based’. I have shown just mappings above, but the configuration model is exposed such that you can change the defaults for all objects. So when you find yourself changing en-masse configuration property values for your objects and wishing the defaults were different, now you know what to do.

The 11gR2 release has a useful feature for altering the shipped configuration property values – you can set default property values for all sorts of information, from code generation modes to...

ETL

OWB – Reuse Mapping for Different Data Sources

A common query over the years is how to do more dynamic changes at runtime, with the core OWB mappings, a common technique which has been used through the years is to use Oracle database synonyms (see forum entries such as this). This is useful for when you are doing cookie cutter style data movement from many replicated systems. The synonym always points to a common relational shape that is processed by a mapping, its just that in one execution it may point to schema X table T, and in another point to schema Y table T or even schema Z table T2 over a database link. For example we may have an ORDERS table in schemas XWEEK and XWEEK_ANO, if we define a synonym ORDERS which points to the XWEEK.ORDERS table we will see how it is used in OWB. In the database metadata import wizard you should check the ‘Use a synonym to look up objects’ option, this will show private synonyms from USER_SYNONYMS; The synonyms are differentiated by icon in the tree, synonyms are shown with the image and  regular tables with the   image . When a synonym is imported we get the table metadata imported for the object the synonym points to. So we get all the columns and keys etc. When the object is used in a mapping then the synonym name is used in the local schema, so for example we get SELECT … FROM ORDERS which is really using whatever the synonyms is pointing to. At runtime we can change the synonym definition and point to any schema so long as the shape is the same and the mapping will execute and process the data. Below I have changed the synonym to point to XWEEK_ANO.ORDERS Executing the mapping again will now consume the data from XWEEK_ANO.ORDERS. Check out the forum post for how to incorporate this into a mapping.

A common query over the years is how to do more dynamic changes at runtime, with the core OWB mappings, a common technique which has been used through the years is to use Oracle database synonyms (see...

OWB 11.2.0.2: Managing Use of Optional OWB Features

Most OWB users know that parts of Warehouse Builder are covered with the database license and others require additional options (such as the Oracle Data Integrator Enterprise Edition license). Warehouse Builder 11.2.0.2 adds the ability to disable optional feature groups. This lets you avoid the inadvertent use of most licensed features at the repository level.  This capability is accessed through the 11.2.0.2 Repository Assistant. We’ll look at the basics here. There’s also a new whitepaper that details which features are in the different feature groups associated with licenses. Read on to find out more. In Repository Assistant in 11.2.0.2, in Step 2 (“Choose Operation”), you will see a new task, “Manage Optional Features.” This is where you choose which features to enable or disable. After you click Next, and provide OWBSYS credentials, will see the following new screen:   By default, all options are enabled. If there are feature groups you want to disable, uncheck them. Click Next.   Click Finish to confirm your selections, and in a moment you will see this confirmation: You must perform this step on each host with an OWB repository on it, to control which features are enabled on that host.. From this point, the use of features requiring extra license will be blocked, in the OWB Design Center, from scripting, on MDL import and at deployment/execution time. For example, if you disable the Data Integrator Enterprise Edition features, and you try to use an ODI-EE feature at design time, you will get an error such as: Similar errors will be returned when deploying or executing objects, importing MDLs containing objects that use controlled features, and so on. For a closer look at which features are controlled by which settings, see the new OTN page on Warehouse Builder licensing. The definitive word on licensing is still the Fusion Middleware Licensing Information documentation, but the new whitepaper on OWB feature groups, licensing and the optional feature manager will help you parse the details in the licensing and map them back to your everyday interactions with Design Center.

Most OWB users know that parts of Warehouse Builder are covered with the database license and others require additional options (such as the Oracle Data Integrator Enterprise Edition license). Warehouse...

How to ...

OWB 11gR2 – OMB and File Editing

Here we will see how we can use the IDE for editing OMB scripts. The 11gR2 release is based on the common Oracle platform IDE used also by JDeveloper. It comes with a bunch of standard behavior for editing and rendering code. One of the lesser known things is that if you drop a text file into OWB you can edit it. So you can drop your tcl scripts right into OWB and edit in-place, and don’t need another IDE like Eclipse just for this task. Cool, so you have the file here. There may be no line numbers, you can toggle line numbers on by right clicking in the gutter. If we edit the file within the OWB IDE, the save is a little different from normal. OWB doesn’t normally manipulate files so things like ctrl-s to save, saves the OWB objects, but if you edit a file the closing of the file will ask if you want to save it – check it out. Now we enter the realm of ‘he who dares’…. Note the IDE doesn’t know about tcl files out of the box, so you see above there is no syntax highlighting. The code is identified by the extension… .java is java, .html is HTML etc. With OWB, the OMB scripts are tcl, we usually have .tcl extension on these files. One of the things we can do to trick up the syntax highlighting is to simply rename the file to have a .java suffix, then all of a sudden we get syntax highlighting, see the illustration here where side by side we see a the file with a .java extension and a .tcl extension. Not ideal pretending to be .java but gets us a way to having something more useful than notepad. We can then change the syntax highlighting such that we get Eclipse like highlighting within the IDE from the Tools Preferences option; You then get the Eclipse like rendering albeit using a little tweak on the file names… Might be useful if you are doing any kind of heavy duty OMB script development and just want a single IDE. The OMBPlus panel is then at hand for executing and testing it out.

Here we will see how we can use the IDE for editing OMB scripts. The 11gR2 release is based on the common Oracle platform IDE used also by JDeveloper. It comes with a bunch of standard behavior...

ETL

OWB 11gR2 – JDBC Helper Utility

One of the common queries when importing the tables via JDBC with 11gR2 is determining why the import wizard doesn’t display the tables that you think it should. I often just use the script below to dump out the schemas, tables and columns that the JDBC driver is returning. This is useful in a few areas; to figure out what the schema name is returned to double check with the schema name you have used in the location (this is used in the DatabaseMetaData.getTables API call within the basic JDBC metadata import. to figure out the data types returned from the JDBC driver when you see columns skipped because of no datatype supported messages. also…I can do it via scripting and don’t need to recompile classes and stuff :-) Edit the tcl script and set the JDBC driver, the connection URL and the username and password (they are at the bottom of the script), the script then calls a basic tcl procedure which writes to standard out the schemas, tables and columns with various properties. For example I executed it using the XML JDBC driver from ODI over a simple customers XML file and it writes the following metadata; You can add more details as you need and execute from the OMBPlus panel within OWB. Download the sample tcl jdbc script here There is a bunch of really useful stuff on OTN documenting this area (start with the white paper here) that is worth checking out all related to the OWB SDK covering everything from platform definitions, custom metadata importers, application adapters, code templates etc. You can find a bunch of goodies on the OWB SDK here.

One of the common queries when importing the tables via JDBC with 11gR2 is determining why the import wizard doesn’t display the tables that you think it should. I often just use the script below to...

ETL

OWB 11gR2 – Heap, JDBC and Mappings

When using the generic JDBC connectivity with code template mappings you need to be wary of how the driver you are using works. So, if you are using code template mappings and the load templates like SQL to SQL or SQL to Oracle its worth digging into the details of the driver and how you connect and use it. Generally users put in the most basic authentication information into the URL to get up and running. So if you get this kind of an error its worth reading on; Job 22 error: com.microsoft.sqlserver.jdbc.SQLServerException: The system is out of memory. Use server side cursors for large resultsets:Java heap space. Result set size:.. JVM total memory size .. You have to be wary of how the JDBC driver you are using operates. To illustrate let’s take Microsoft SQLServer as an example. There is a property that can be defined when connecting to SQLServer named selectMethod that by default has value direct which means 'all result set rows are retained in client memory'. Yep, you read it right….all result set rows in client memory, the client here being the OWB control center agent. With SQLServer, using the value cursor (rather than direct) will mean only a limited number of rows will be retained. So…….if you are dealing with large volumes of rows that will not fit in memory its best to think about whether you will need to set this property definition to cursor otherwise you’ll be hitting out of memory errors. So an example if you define the selectMethod=cursor as follows jdbc:sqlserver://someserver:someport;databaseName=somedatabase;selectMethod=cursor; You will use the cursor approach rather than the direct ‘all result set rows in memory’ approach. The SQLServer driver has other properties such as adaptive response buffering for processing large result sets, and each vendor driver has different configuration properties so ensure you make maximum use of the connectivity by understanding how best to use it. By making these changes in the connection URL the JDBC driver will behave quite differently from the default and be able to process larger volumes of data. I found these configuration properties from the SQLServer documentation for JDBC connectivity which is here. So, when you hit such errors don’t go straight to increasing the heap - increasing the CCA heap may get you further but changing the URL to configure these kinds of properties would make it operate on much larger volumes.

When using the generic JDBC connectivity with code template mappings you need to be wary of how the driver you are using works. So, if you are using code template mappings and the load templates like...

ETL

ODI and OBIEE 11g Integration

Here we will see some of the connectivity options to OBIEE 11g using the JDBC driver. You’ll see based upon some connection properties how the physical or presentation layers can be utilized. In the integrators guide for OBIEE 11g you will find a brief statement indicating that there actually is a JDBC driver for OBIEE. In OBIEE 11g its now possible to connect directly to the physical layer, Venkat has an informative post here on this topic. In ODI 11g the Oracle BI technology is shipped with the product along with KMs for reverse engineering, and using OBIEE models for a data source. When you install OBIEE in 11g a light weight demonstration application is preinstalled in the server, when you open this in the BI Administration tool we see the regular 3 panel view within the administration tool. To interrogate this system via JDBC (just like ODI does using the KMs) need a couple of things; the JDBC driver from OBIEE 11g, a java client program and the credentials. In my java client program I want to connect to the OBIEE system, when I connect I can interrogate what the JDBC driver presents for the metadata. The metadata projected via the JDBC connection’s DatabaseMetadata changes depending on whether the property NQ_SESSION.SELECTPHYSICAL is set when the java client connects. Let’s use the sample app to illustrate. I have a java client program here that will print out the tables in the DatabaseMetadata, it will also output the catalog and schema. For example if I execute without any special JDBC properties as follows; java -classpath .;%BIHOMEDIR%\clients\bijdbc.jar meta_jdbc oracle.bi.jdbc.AnaJdbcDriver jdbc:oraclebi://localhost:9703/ weblogic mypass Then I get the following returned representing the presentation layer, the sample I used is XML, and has no schema; Catalog Schema Table Sample Sales Lite null Base Facts Sample Sales Lite null Calculated Facts …     Sample Targets Lite null Base Facts …     Now if I execute with the only difference being the JDBC property NQ_SESSION.SELECTPHYSICAL with the value Yes, then I see a different set of values representing the physical layer in OBIEE; java -classpath .;%BIHOMEDIR%\clients\bijdbc.jar meta_jdbc oracle.bi.jdbc.AnaJdbcDriver jdbc:oraclebi://localhost:9703/ weblogic mypass NQ_SESSION.SELECTPHYSICAL=Yes The following is returned; Catalog Schema Table Sample App Lite Data null D01 Time Day Grain Sample App Lite Data null F10 Revenue Facts (Order grain) …     System DB (Update me)     …     If this was a database system such as Oracle, the catalog value would be the OBIEE database name and the schema would be the Oracle database schema. Other systems which have real catalog structure such as SQLServer would use its catalog value. Its this ‘Catalog’ and ‘Schema’ value that is important when integration OBIEE with ODI. For the demonstration application in OBIEE 11g, the following illustration shows how the information from OBIEE is related via the JDBC driver through to ODI. In the XML example above, within ODI’s physical schema definition on the right, we leave the schema blank since the XML data source has no schema. When I did this at first, I left the default value that ODI places in the Schema field since which was ‘<Undefined>’ (like image below) but this string is actually used in the RKM so ended up not finding any tables in this schema! Entering an empty string resolved this. Below we see a regular Oracle database example that has the database, schema, physical table structure, and how this is defined in ODI.   Remember back to the physical versus presentation layer usage when we passed the special property, well to do this in ODI, the data server has a panel for properties where you can define key/value pairs. So if you want to select physical objects from the OBIEE server, then you must set this property. An additional changed in ODI 11g is the OBIEE connection pool support, this has been implemented via a ‘Connection Pool’ flex field for the Oracle BI data server. So here you set the connection pool name from the OBIEE system that you specifically want to use and this is used by the Oracle BI to Oracle (DBLINK) LKM, so if you are using this you must set this flex field. Hopefully a useful insight into some of the mechanics of how this hangs together.

Here we will see some of the connectivity options to OBIEE 11g using the JDBC driver. You’ll see based upon some connection properties how the physical or presentation layers can be utilized. In the...

ETL

OWB 11gR2 - Find and Search Metadata in Designer

Here are some tools and techniques for finding objects, specifically in the design repository. There are ways of navigating and collating objects that are useful for day to day development and build-time usage - this includes features out of the box and utilities constructed on top. There are a variety of techniques to navigate and find objects in the repository, the first 3 are out of the box, the 4th is an expert utility. Navigating by the tree, grouping by project and module - ok if you are aware of the exact module/folder that objects reside in. The structure panel is a useful way of finding parts of an object, especially when large rather than using the canvas. In large scale projects it helps to have accelerators (either find or collections below). Advanced find to search by name - 11gR2 included a find capability specifically for large scale projects. There were improvements in both the tree search and the object editors (including highlighting in mapping for example). So you can now do regular expression based search and quickly navigate to objects within a repository. Collections - logically organize your objects into virtual folders by shortcutting the actual objects. This is useful for a range of things since all the OWB services operate on collections too (export/import, validation, deployment). See the post here for new collection functionality in 11gR2. Reports for searching by type, updated on, updated by etc. Useful for activities such as periodic incremental actions (deploy all mappings changed in the past week). The report style view is useful since I can quickly see who changed what and when. You can see all the audit details for objects within each objects property inspector, but its useful to just get all objects changed today or example, all objects changed since my last build etc. This utility combines both UI extensions via experts and the public views on the repository. In the figure to the right you see the contextual option 'Object Search' which invokes the utility, you can see I have quite a number of modules within my project. Figure out all the potential objects which have been changed is not simple. The utility is an expert which provides this kind of search capability. The utility provides a report of the objects in the design repository which satisfy some filter criteria. The type of criteria includes; objects updated in the last n days optionally filter the objects updated by user filter the user by project and by type (table/mappings etc.) The search dialog appears with these options, you can multi-select the object types, so for example you can select TABLE and MAPPING. Its also possible to search across projects if need be. If you have multiple users using the repository you can define the OWB user name in the 'Updated by' property to restrict the report to just that user also. Finally there is a search name that will be used for some of the options such as building a collection - this name is used for the collection to be built. In the example I have done, I've just searched my project for all process flows and mappings that users have updated in the last 7 days. The results of the query are returned in a table containing the object names, types, full path and audit details. The columns are sort-able, you can sort the results by name, type, path etc. One of the cool things here, is that you can then perform operations on these objects - such as edit them, export single selection or entire results to MDL, create a collection from the results (now you have a saved set of references in the repository, you could do deploy/export etc.), create a deployment script from the results...or even add in your own ideas! You see from this that you can do bulk operations on sets of objects based on search results. So for example selecting the 'Build Collection' option creates a collection with all of the objects from my search, you can subsequently deploy/generate/maintain this collection of objects. Under the hood of the expert if just basic OMB commands from the product and the use of the public views on the design repository. You can see how easy it is to build up macro-like capabilities that will help you do day-to-day as well as build like tasks on sets of objects.

Here are some tools and techniques for finding objects, specifically in the design repository. There are ways of navigating and collating objects that are useful for day to day development and...

Recommended OWB Patch before Repository Upgrade or Migration: 11.2.0.2 + 10185523 or 11.2.0.1 + 10270220 (replaces 9802120)

Revised 6 December 2010 - Customers upgrading existing repositories from previous releases to OWB 11gR2 should now upgrade to OWB 11.2.0.2 on platforms where it is available and apply patch 10185523 before performing the migration. Customers who want to use OWB 11.2.0.2 but who are using the Windows client (where the 11.2.0.2 client is not yet available) should contact support to obtain a pre-release OWB 11.2.0.2/Win32 client that will work with the currently released 11.2.0.2 server. More information on how to handle this upgrade is in the OWB 11.2.0.2 release notes. Customers who are unable, for whatever reason, to move to OWB 11.2.0.2 should move to OWB 11.2.0.1 and then download and apply the patch 10270220. It is a cumulative patch that combines fixes for nearly all known issues with repository upgrades and migrations. Download OWB 11gR2 Upgrade and Migration Paths White Paper here! You should also consult the Migration and Upgrade Paths whitepaper attached to this posting. Strategies for upgrading are described in this paper, including upgrading from pre-10.2 releases. The information in the paper is correct apart from needing a refresh to recommend the 11.2.0.2 upgrade as a better option than patch 9802120. The paper will be updated shortly to reflect the latest patch levels.

Revised 6 December 2010 - Customers upgrading existing repositories from previous releases to OWB 11gR2 should now upgrade to OWB 11.2.0.2 on platforms where it is available and apply patch 10185523...

ETL

Parallel Direct Path Inserts into Partitioned Tables

Combining the DBMS_PARALLEL_EXECUTE package in 11gR2 of the Oracle database with direct path inserts into partitioned tables is a useful pairing. Another flexibility facet I used here is the use of interval partitioning - so with the pattern, not only is the ETL loading into the partitions in an efficient, flexible implementation but the database is managing the partitions in the table too. Here I'll show the raw first step of how it's done then go about generalizing it into the tools. The figure below shows chunks of source data being processed via a pool of jobs writing into a partitioned target table in direct path. The parallel execute package has some tables which define the tasks and chunks to be processed by the parallel job pool, you can query these via the data dictionary views (*_PARALLEL_EXECUTE_TASKS and *_PARALLEL_EXECUTE_CHUNKS). The package also supports resuming a task to reprocess failed chunks which is useful. There is an introductory article on DBMS_PARALLEL_EXECUTE worth checking out in the May/June Oracle magazine from Steven Feuerstein. In the Oracle SQL grammar the partition key value of the partition extension clause in the INSERT DML provides critical information that will enable us to make a pattern for providing parallel direct path loads into partitioned tables. So if we make the chunking column from DBMS_PARALLEL_EXECUTE useful for identifying the partition key value above then we have a winner. The parallel execute chunking identifier is a numeric value - in the example below the SALES table is partitioned by month, so we can imagine the chunking identifier using YYYYMM (ie. 200812 for December 2008) to represent a month in numeric form and this being converted to a date for the partition key value in the INSERT SQL clause using something like TO_DATE(200812, 'YYYYMM'). The illustration here will load a partitioned SALES table that uses interval partitioning so we get a table that the database will manage the addition of partitions. CREATE TABLE sales     ( prod_id        NUMBER(6)     , cust_id        NUMBER     , time_id        DATE     , channel_id     CHAR(1)     , promo_id       NUMBER(6)     , quantity_sold  NUMBER(3)     , amount_sold    NUMBER(10,2)     )   PARTITION BY RANGE (time_id)   INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))     ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY'))   ); The source table I'm using mirrors the target table, without the partitions, also added some basic data here for a demo - each batch of rows I have added will be processed in a chunk (imagine it was a lot of data in that partition) CREATE TABLE src_sales     ( prod_id        NUMBER(6)     , cust_id        NUMBER     , time_id        DATE     , channel_id     CHAR(1)     , promo_id       NUMBER(6)     , quantity_sold  NUMBER(3)     , amount_sold    NUMBER(10,2)     ); begin   for c in 1..1000000 loop     insert into src_sales (prod_id,cust_id,time_id) values (1,1,'01-FEB-10');     insert into src_sales (prod_id,cust_id,time_id) values (1,1,'01-MAR-10');     insert into src_sales (prod_id,cust_id,time_id) values (1,1,'01-APR-10');     insert into src_sales (prod_id,cust_id,time_id) values (1,1,'01-MAY-10');     insert into src_sales (prod_id,cust_id,time_id) values (1,1,'01-JUN-10');     insert into src_sales (prod_id,cust_id,time_id) values (1,1,'01-JUL-10');     commit;   end loop; end; / To create the task and chunks for the execution we can use the DBMS_PARALLEL_EXECUTE APIs, in the call below we define the task with a name and in this case a SQL statement to identify the chunks (demo example, should be careful on performance here, commonly an indexed numeric field is used); begin   begin     DBMS_PARALLEL_EXECUTE.DROP_TASK(task_name => 'TASK_NAME');     exception when others then null;   end;   DBMS_PARALLEL_EXECUTE.CREATE_TASK(task_name => 'TASK_NAME');   DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL(task_name => 'TASK_NAME',     sql_stmt =>'select distinct to_number(to_char(time_id,''YYYYMM'')) startid, to_number(to_char(time_id,''YYYYMM'')) endid from src_sales', by_rowid => false); end; Then we have to define the meat of the task and the number of jobs to process the chunks, note I am using dynamic SQL since the partition key value cannot be a bind variable - and its value will change in each child task that the parallel execute engine executes (it itself will pass start_id and end_id as bind variables to this block). begin   DBMS_PARALLEL_EXECUTE.RUN_TASK (task_name => 'TASK_NAME',     sql_stmt =>'declare       s varchar2(16000); vstart_id number := :start_id; vend_id number:= :end_id;       begin         s:=''insert into SALES /*+ APPEND*/            partition for (to_date(''||vstart_id||'', ''''YYYYMM''''))            select PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD            from SRC_SALES            where time_id between to_date(:vstart_id, ''''YYYYMM'''') and last_day(to_date(:vstart_id, ''''YYYYMM'''')) '';         execute immediate s using vstart_id, vend_id;         commit;       end;',     language_flag => DBMS_SQL.NATIVE, parallel_level => 2 ); end; / Whilst the above is running the parallel execution package spawns 2 jobs (since I indicated parallel level of 2). If I quickly look at the USER_PARALLEL_EXECUTE_CHUNKS view I see 6 chunks, since I had 6 distinct months of data. I can see below the first two chunks are in ASSIGNED status and are being processed. Checking the view again I see 2 have are now in PROCESSED status, and 2 are ASSIGNED - note the start_id and end_id columns here, these are the bind variables passed to my PLSQL block in the RUN_TASK routine above, it is these I use for the PARTITION FOR key value. Finally all chunks are processed and I have processed the data in parallel writing direct path into the partitioned target tables. I mentioned earlier the resume capability, this is also very useful. There is another state PROCESSED_WITH_ERROR that will be flagged when the chunk being processed has failed for whatever reason. The RESUME_TASK procedures allow you to retry the task and reprocess just those chunks that failed, Interesting stuff, combining some capabilities in 11gR2 of the database to boost processing and leverage those CPUs! Hopefully this sparks some other ideas out there. Next up I'll take this into the data integration tools and illustrate how it can be commoditized and avoid the programming.

Combining the DBMS_PARALLEL_EXECUTE package in 11gR2 of the Oracle database with direct path inserts into partitioned tables is a useful pairing. Another flexibility facet I used here is the use of...

ETL

ODI 11g – Simple, Flexible, Powerful

SQL as a source, inline SQL, SQL override...want to know more ....? A couple of specific enhancements in the code generation capabilities of ODI 11g, opens the door to an area of code generation which provides great benefit from the ability to have SQL as a source to the construction of even better performing code to better interface designs and accelerators. So what are they? The two key changes are; support for sub-select to chain, when possible, multiple interfaces into one. With ODI 11g the code generation capabilities of ODI were extended in order to build larger more complex interface designs without the necessity of staging via temporary interfaces. There have already been blog posts on this subject, for example see Uli Bethke's post here. a property in the KM definition to define current command for sub-select. A new property defined on a command in a KM to indicate to the ODI code generator that this command should be used for generating the SQL statement for a sub-select. This one is the key change in addition to the support for sub-select that opens the door. Is the penny dropping yet? These two together open the door to building operator(or transformation)-like KMs within the bounds of the information that can be defined by a temporary interface and the options on the KM. Let's see... Simple Example - The SQL as Source IKM Let's build a very simple example that is similar to Informatica's SQL override capability or inline views - SQL as source. So to the new breed of code template an IKM with the SQL code generator to be used for sub-query. We define an IKM and have the target technology just now set to Oracle; Now comes the interesting part, we can define a command to fulfill the SQL generation. This can be as simple as actually just being the SQL code (SQL as source) or something a little more complex such as fulfilling a transformation such as a pivot or table function. Let's check out the command definition, its very simple - the SQL representing the subquery will be an option on the IKM. Don't worry you won't have to build this KM its on the code samples already, you can download the KM here. (I built it with Oracle as a target technology at first then changed for all using undefined technology). Also it will become blatantly obvious how its used when you see a simple example. From the above you see 2 things; 'Use current command for Derived-Table sub-select statement' is checked, a bit of a mouthful, basically ODI will use THIS command in the IKM for generating the SQL when a temporary interface is used in an interface and the sub-select check box is selected. The IKM has an option VIEWQUERY. When the IKM is used in an interface design this is where you will define the SQL as a source. That's a peek inside the IKM, we can now build temporary interfaces that are essentially inline view designs that will define the view columns (the target datastore columns in the temporary interface) and the SQL query which is to be used to realize the interface. With ODI we can go the extra mile and use the ODI reference APIs to ensure the SQL is portable also across systems so that we don't hard-wire access, schemas etc. Building the inline view definition Like any view (in database terms) we define a signature - the columns to be projected are defined on the temporary target datastore on an ODI temporary interface, below you see I have defined a comment (--) for the mapping expression and the indicator is for the target; The execution unit is assigned the SQL_as_Source IKM, where the SQL query is then defined on the VIEWQUERY option. Above I have hard-wired the query, as mentioned we can use ODI reference APIs to get the physical name from the datastore in a flexible manner. So the query could be defined using the odiRef.getObjectName which makes the statement much more flexible and provides flexibility via the context to use different physical schemas for example. That is the SQL as a source or inline view defined. We can reuse this definition many times in different interfaces. Let's see how it is used. Using the inline view in an interface The temporary interface (our inline view definition) can be dropped into other interfaces and used like any other interface in ODI, now in 11g as we have seen from other posts we can include the interface as a sub-select. The INLINE_VIEW can be joined  with other tables etc and used like any other interface artifact. Above I am using the INLINE_VIEW temporary interface and have selected the Use Temporary Interface as Derived Table (Sub-Select), since the tables used by the view are from the same server as the BONUS table. So what code gets generated? Simulating the code in ODI 11g we see the SQL statement we defined in our temporary interface nested inside the overall code (this was using the had-wired SQL rather than the odiRef'd version). This KM lets you then use a temporary interface as your SQL Query or inline view and provides a great mechanism to balance fully fledged design using interfaces and integrating custom arbitrary SQL. You can download the SQL as Source KM from the code samples here on java.net. The example illustrates a capability that we can exploit for other cases - more to come!

SQL as a source, inline SQL, SQL override...want to know more ....? A couple of specific enhancements in the code generation capabilities of ODI 11g, opens the door to an area of code generation which...

New OWB 11gR2 feature: Dynamic Lookups in Row-Based Mappings

Some of the changes in OWB 11gR2, like ODI knowledge module support, are pretty obvious; others are harder to spot. One new feature that has lurked in the tall grasses until now is the new support for dynamic lookups (as our friends at Informatica called them) in the revamped lookup operator.  This was alluded to in the OWB 11gR2 New Features list and covered in the documentation but deserves more attention than it got. When you use dynamic lookup, if a lookup table is changed during the execution of a mapping, the lookups from that point on pick up any changes. To accomplish this, we implemented a couple of subtle changes in the language support of the lookup operator. In 11gR2 the key lookup operator supports both SQL and PL/SQL. In the past, only SQL was supported. In the doc there is a table defining for the operators the languages that each supports (11gR2 is here, 11gR1 is here - check the lookup operator in table...subtle, maybe too subtle.) When used in row-based PL/SQL, the lookup operator can now be moved out of the SQL cursor boundary. This becomes clearer when you look at the (somewhat contrived) exampled below. In the above figure, REF_DATA is the hidden lookup table within the REF_LOOKUP operator. Each row extracted from SRC_DATA will go through the REF_LOOKUP, SPLITTER, then get inserted into REF_DATA (when missed in lookup) and TGT_DATA. This mapping will behave differently depending on the code generation mode you pick. In set-based mode, the behavior is as before-- any changes to REF_DATA are not picked up during mapping execution. If you configure the code generation mode and runtime default operating mode for the mapping to be row based, the behavior changes: In row-based mode, each row extracted from SRC_DATA will go through the REF_LOOKUP, SPLITTER, then get inserted into REF_DATA (when missed in lookup) and TGT_DATA. All these are done before the next row from SRC_DATA is fetched. So the next row will be able to access any new lookup data previous rows have inserted into REF_DATA.  Conversely a row from SRC_DATA will NOT be able to access any new lookup data to be generated by future rows. Note that  the ordering of rows coming out of SRC_DATA becomes very important-- carefully consider the extraction order from SRC_DATA and add a sort operator right after SRC_DATA to ensure the desired behavior. You will see the code generated with the cursor prior to the lookup; You get another variation if you configure the operating mode of the mapping as row-based target only: Here  the cursor is extended to include everything except the target tables, as shown below. In this case, the REF_LOOKUP operator will not be able to access any new data inserted in REF_DATA table because REF_DATA is only read when the cursor is opened. You can see this in the generated code:   So that's a quick dump of what’s behind the dynamic lookup in 11gR2. OWB lets you pick the right tradeoff between maximum performance (in set-based mode) and more flexible behavior (in row-based mode).

Some of the changes in OWB 11gR2, like ODI knowledge module support, are pretty obvious; others are harder to spot. One new feature that has lurked in the tall grasses until now is the new support for...

OWB 11gR2 Tip: Downloading and Installing Workflow Monitor 2.6.4

Oracle Workflow 2.6.4 is still used as an integral part of Warehouse Builder 11gR2—specifically, to support the use of OWB process flows. However, Workflow Monitor is not part of the OWB 11gR2 installation of Workflow. As a result, many users of OWB 11gR2 want to know where to download Workflow Monitor, and how to install Workflow Monitor to manage the workflow jobs OWB creates. Workflow Monitor usable with OWB 11gR2 is available from the Oracle Database 10gR2 Companion CD, which you can download from OTN. A detailed installation walkthrough on Windows 32-bit follows. Other platforms should be similar. After you download and unzip the Companion CD, run Oracle Universal Installer from the CD. Install the 10gR2 Companion Products.   Install these products into a separate Oracle Home. Do not install them into the database home. From the components, select Oracle Workflow Middle Tier 2.6.4.  Run through the Prerequisite checks. For the Middle Tier Configuration, enter the host, port and SID fo your database, and the workflow schema OWF_MGR. Enter the password for OWF_MGR when prompted. Review the summary, which should look like the one below. Click “Install” to start the installation process running: When the installation completes, the Configuration Assistants will run. Wait until they complete. When the “End of Installation” dialog appears, Workflow Monitor is installed. You can check whether the HTTP server is running by visiting http://your-host:7777/ – you should see the Oracle HTTP Server Home Page: You can then go to the Workflow Monitor home page by visiting http://your-host:7777/pls/wf/wfa_html.home – you should see the login screen:   Log in as OWF_MGR/password. Et voilà!

Oracle Workflow 2.6.4 is still used as an integral part of Warehouse Builder 11gR2—specifically, to support the use of OWB process flows. However, Workflow Monitor is not part of the OWB 11gR2...

ETL

OWB Synchronization in Code Samples

The OWB synchronization expert to update sets of dependencies in a simple manner has been uploaded into the code sharing area here. The expert lets you update dependent objects in a quicker manner than going one by one in the designer. After importing the MDL you can apply the experts (one to list dependencies, the other to synchronize dependencies) to object types. For example you can apply to tables, pluggable mappings, then if you change the table and want to synchronize all usages, you can right click on the object and select 'Synchronize to Dependents. This will then display all dependent usages of the object, where you can decide which ones to synchronize and how. Select the objects and OK, this will for example here ensure the mappings where this table is used are in synch with the table definition itself. You will then get a conformation dialog indicating it has finished. This useful utility is a simple sample expert built on the OMB primitives and is in the code sample area, so you can take, enhance and change...and upload if you want to donate in the sample area for others. There is another expert in the MDL file that simply lists the dependencies, not all object types are supported but many of the common ones are (relational object definitions and mappings/pluggable mappings).

The OWB synchronization expert to update sets of dependencies in a simple manner has been uploaded into the code sharing area here. The expert lets you update dependent objects in a quicker manner...

ETL

OWB SQL Reports in Code Samples

The SQL reports on OWB for SQLDeveloper have been uploaded into the code sharing here. There are a number of reports you can take, change, enhance or whatever and also share your reports or changes by putting back into the code sharing site for others to benefit from. There are other useful odds and ends in the code samples such as KMs and other experts and best of all.... we all (including you) can access and update. If you just want to take the SQL, you can open the OWBReports.xml file and search for the various queries. There are useful execution reports plus some deployment ones (see what mappings are deployed, their parameters, when they were deployed, their state etc.). For example the mappings deployment report you can see the parameters with their default values, etc. Plus I can see the deployments, when, type of object and status. To see the SQL for these right click on report, select 'Edit'; You then get an editor where you can change, copy or view the SQL for the master and detailed parts of the report. The child reports use the standard mechanism within SQLDeveloper to relate a detailed report to the master (use :BIND variables). There are other reports such as seeing the registered locations which are handy with details of all the properties plus see execution reports for mappings which are master detail reports which drill to throughput, audit information, errors, parameters and timing analysis. There are also some rudimentary ones for looking at process flows that let you see the execution audit for process flow activities.  These were created so I could quickly analyze what was going on just by going right at the public views rather than using the OWB designer or browser. I'm sure many of you have better ones that it would be useful to share and make a richer set. If you are using 11gR1 of OWB you will have to set the workspace by calling the owbsys.wb_workspace_management.set_workspace from the SQL worksheet for the connection, likewise if you want a different workspace from the default...if you have many workspaces. So check out these reports on the Oracle code samples site, change, fix, add and share!

The SQL reports on OWB for SQLDeveloper have been uploaded into the code sharing here. There are a number of reports you can take, change, enhance or whatever and also share yourreports or changes by...

OWB in the News: Department of Energy Success Story

Warehouse Builder was one of a great many Oracle products leveraged in a success story for Oracle at the Department of Energy that shows off the breadth, depth and integration of Oracle’s offerings. See the whole press release Sep 28, 2010 08:00 ET U.S. Department of Energy Leverages Oracle Solutions to Consolidate and Streamline Department-Wide Systems and Business Processes Reporting System Integrates Financial, Budgetary, Procurement, Personnel, Program and Performance Information REDWOOD SHORES, CA--(Marketwire - September 28, 2010) - Oracle (NASDAQ: ORCL) News Facts U.S. Department of Energy (DOE) has deployed Oracle Business Intelligence, Oracle Financials, Oracle's PeopleSoft Enterprise Human Resources, Oracle Portal, Oracle WebCenter Suite, Oracle Beehive and Oracle's Primavera Enterprise Project Portfolio Management applications to build a reporting system to manage agency-wide systems and data. After it received American Recovery and Reinvestment Act (ARRA) funding that doubled its annual budget from $26 billion to almost $44 billion, DOE needed a comprehensive system to help manage the additional funding as well as provide accurate, timely reports on funding allocation and usage to both the Office of Management and Budget (OMB) and the public in near real-time, through recovery.gov and the DOE's own recovery funds portal. To meet OMB's requirement for near real-time access to how DOE is spending its ARRA funding, the agency integrated its financial, budgetary, procurement, personnel, program and performance information into a reporting system. Oracle Warehouse Builder supports the system at the core, linking common data elements from each of the department's business systems and supporting both external and internal reporting. With the initial goal of securing 800 users on Oracle Financials, DOE has grown its user community to more than 1,700 users in less than one year, and is experiencing a growth of about 100 new users per month. DOE uses its core systems to provide the basic information for ARRA reports and can use Oracle Business Intelligence to easily make minor modifications to address new reporting requirements. With Oracle, DOE can now deliver financial reports to OMB on a daily, instead of weekly, basis. The Oracle software also provides automatic e-mail alerts to DOE staff when financial figures are not falling within expected ranges, so the agency can address budget allocation issues more proactively. The portal also enables users to create their own financial and project reports to meet their specific needs. In addition, DOE's budget and procurement offices can now use Oracle Application Express (APEX) to work more easily with various program offices by connecting an easy-to-use Web form or spreadsheet with Oracle Database to pull required data directly from corporate systems. Supporting Resources Oracle Public Sector Oracle Public Sector on Facebook Oracle's Solution for the American Recovery and Reinvestment Act Oracle White Paper: Oracle Solutions for Implementing the American Recovery and Reinvestment Act

Warehouse Builder was one of a great many Oracle products leveraged in a success story for Oracle at the Department of Energy that shows off the breadth, depth and integration of Oracle’s offerings. See...

ETL

OWB 11gR2 – Errors and Line Numbers

How do you find errors in mapping designs when you can the PLS-0021 style errors deploying code? Here's some hints and tips that make life easier. One of the most common errors I've seen is finding the needle in the haystack when there is a typo in an expression that is entered and hasn't been validated/verified. This appends when for example you forget a quote, type a wrong name for a function, forget a brace .... all the usual stuff we forget to do when we design ... anything in fact. The typical kind of error you will see is something like this when you use a function that doesn't exist. This example uses a mapping input parameter accessor and the wrong name is used. Now there are a few clues here, one is the PACKAGE BODY, next the line number and finally the identifier GET_FILENAMES. Let's see what we can investigate... Just above the errors are the generated scripts, if you view the generated package by double-clicking, you can find the line number in question. The code opens in the new 11gR2 code viewer, by default it opens on Source, but note there is also a Spec and Body tab. Remember PACKAGE BODY was where the error was, so click on Body. Then we can use the all to familiar control-G style operation to go-to the line number in question, line 548 in problem above, this takes us exactly to the line in error. This let's us see the expression in the context of the generated code. I was sure the parameter was named FILENAMES, so let me search for at least get_filena. I find that the accessor is named GET_FILENAME, I made a mistake and used GET_FILENAMES. Changing the expression in my map and redeploying resolves my issue. The changes in 11gR2 make this kind of cycle much simpler, the fact that the Spec and Body are split and viewable with line numbers and have easy to use search capabilities, makes life simpler than using the data dictionary views for checking the source code which could be done like this; select text from user_source where name='DYNAMIC_FILE_TGT' and type = 'PACKAGE BODY' and line > 545 and line < 552; TEXT --------------------------------------------------------------------         "FFFF_Fh" := UTL_FILE.FOPEN(           'SCEN_FILES_LOCATION1',           ''||get_filenames||'',           'w',           32767 So accessing the body and searching is a nice capability that makes it easier for finding those little issues.

How do you find errors in mapping designs when you can the PLS-0021 style errors deploying code? Here's some hints and tips that make life easier. One of the most common errors I've seen is finding...

Events

Oracle OpenWorld 2010 Data Integration Track Session Schedule: Thursday 23 September

Here are the Oracle OpenWorld 2010 Data Integration Track events for Thursday 23 September.S317395:The Emerging Data Integration Platform: Real-Time CDC and Continuous E-LT Kulvinder Hari; Miranda Nash; Julien Testut Moscone West L3--Rm 3020 23 September, 9:00-10:00 In this session, learn about the emerging style of data integration that enables real-time business intelligence, optimal source system performance, and continuous warehouse availability while leveraging your existing investments. Hear about Oracle's investments in a new, powerful combination of Oracle Data Integrator and Oracle GoldenGate. See a live demo of the newly available knowledge module integrating Oracle GoldenGate and Oracle Data Integrator and a preview of more to come. Discover how other organizations are using this emerging style of data integration to transform their business in innovative ways.  S317265: Going Beyond ETL with Oracle Data Integrator Denis Gray; Jeff Pollock Moscone West L3--Rm 3020 23 September, 10:30-11:30 Data integration is more than simply about extract, transform, load (ETL); it’s also about optimized data movement and data transformation for the real-time enterprise. This session shows enterprise architects, data architects, and data stewards how they can use Oracle Data Integrator Enterprise Edition to improve efficiencies and lower the costs of their data warehousing and BI solutions. The session addresses key elements of the latest product release, highlighting Oracle Data Integrator’s flexibility; scalability; and innovative extract, then load and transform (E-LT) architecture. In addition, it recommends best practices for key architectural data integration patterns, including data services, consolidation, and modernization. S317460: Real-Time Operational Reporting for Oracle Applications with Oracle GoldenGate Alok Pareek; Doug Reid Moscone West L3--Rm 3020 23 September, 12:00-13:00 This session provides an overview of using Oracle GoldenGate to offload reporting activity from applications such as Oracle E-Business Suite and Oracle's Siebel, PeopleSoft Enterprise, and JD Edwards systems in mission-critical application environments without compromising data freshness or performance. It includes real-world case studies and a demo.S317441:Zero-Downtime Database Migration and Upgrade at RIM-BlackBerry Gordon Leach; Lego Haryanto; Irem Radzik Moscone West L3--Rm 3020 23 September, 13:30-14:30 Systems migrations and upgrades are a fact of life in today's dynamically changing IT landscape. However, a major obstacle in moving to modern and cost-effective systems is downtime. Attend this session to learn how you can eliminate downtime with Oracle GoldenGate's heterogeneous real-time data replication technology. The session features Research In Motion (RIM), designer and manufacturer of the BlackBerry smartphone, and its case study for migrating legacy systems to Oracle Database with minimal downtime. S314919: Oracle Data Integrator: ETL Loads on Oracle Essbase and Oracle Hyperion Planning Venkatakrishnan Janakiraman Moscone West L3--Rm 3020 23 September, 15:00-16:00 This presentation discusses a new feature introduced in the 10.1.3.5 release of Oracle Data Integrator: integration with Oracle Essbase and Oracle Hyperion Planning. It covers four main areas in the existing integration: 1. Using Java API (JAPI) in Oracle Essbase 2. Using MaxL scripts and the SQL interface of Oracle Essbase 3. Using calculation scripts 4. Using report scripts and MDX scripts The presentation illustrates these with a practical customer deployment scenario. For more details see: Oracle OpenWorld 2010 Agenda.

Here are the Oracle OpenWorld 2010 Data Integration Track events for Thursday 23 September. S317395:The Emerging Data Integration Platform: Real-Time CDC and Continuous E-LTKulvinder Hari; Miranda...

Events

Oracle OpenWorld 2010 Data Integration Track Session Schedule: Wednesday 22 September

Here are the Oracle OpenWorld 2010 Data Integration Track events for 22 September.S317489: Lifetouch Improves the Quality of Its Customer Data with Oracle Data Quality Colleen McDonald; Brian Olson; Ancil McBarnett Moscone West L3--Rm 3020 22 September, 10:00-11:00 Data quality is increasingly becoming an important factor that helps improve business performance, shorten product and distribution cycles, and improve customer satisfaction. With Oracle Data Quality for Data Integrator and Oracle Data Profiling, companies can improve their data governance programs to establish cleansing rules as part of their integration processes. In this session, hear how Lifetouch Portrait Studios improved its customer care programs by architecting a single consistent view of its complete customer data, using Oracle’s data integration and data quality solutions. Hear best practices of Lifetouch's name and address cleansing, which leveraged a combination of matching, merging, and data profiling. S317418: Achieving Continuous Availability: Retail Decisions Case Study Christopher Uriarte; Andras Belokosztolszki; Alok Pareek Moscone West L3--Rm 3020 22 September, 11:30-12:30 Oracle GoldenGate offers bidirectional and heterogeneous data replication technology and enables continuous availability for mission-critical environments. This session presents an overview of the Oracle GoldenGate product and focuses on Retail Decisions’ use of Oracle GoldenGate in implementing multimaster (active-active) database configurations for continuous data availability. In addition, the session discusses the key benefits Retail Decisions gained from its active-active database implementation for a geographically distributed environment.S317269:Oracle Data Integrator Technical Deep Dive Christophe Dupupet; Jyotin Gautam; FX Nicolas Hotel Nikko--Nikko Ballroom III 22 September, 11:30-12:30 This new generation of Oracle Data Integrator sets the standard for high-power data integration while maintaining exceptional ease of use. This architecture-based session will supply detailed examples of how Oracle Data Integrator should be deployed in highly available, high-security enterprise environments. We'll also take a hands-on look at the new developer-driven features that make Oracle Data Integrator the most productive data integration tool in the market. Finally, we'll examine how Oracle Data Integrator has been improved to enable a simple, powerful, and lightweight framework for embedding ETL services inside applications, middleware, and database technology, with examples of how Oracle is doing exactly that.S317389: Customer Panel: Innovations in Data Integration Architecture Dan Jackson; Kiran Vankamamidi; Dain Hansen Moscone West L3--Rm 3020 22 September, 13:00-14:00 Hear from industry experts how they have revolutionized their business through innovations in real-time business intelligence/data warehousing with a comprehensive approach to data integration. The customer panel includes enterprise architects and other data integration professionals from leading companies. Learn about some of the pressing drivers organizations face for keeping data accessible, up to date, and clean. This session interactively discusses key business benefits of real-time data integration, best practices for building a data integration center of excellence, and considerations for selecting the right set of technologies within data integration. S317367: Turkcell Transforms Its Business with Oracle Data Integrator and Oracle Exadata Faith Feran; Gurcan Orhan; Ugo Pollio Moscone West L3--Rm 3020 22 September, 16:45-17:45 Turkcell is the leading telecommunication operator in Turkey and is ranked third in Europe. It standardized on Oracle Data Integrator Enterprise Edition to help consolidate operational systems of different vendors, databases, and operating systems to maintain, analyze, and track behavior for its comprehensive network infrastructure. Turkcell also leveraged Oracle data warehousing solutions and Oracle Exadata technology for its operational data stores and data mart queries as well as data mining. In this session, you’ll learn about some of the best practices Turkcell leveraged for rapidly transforming its network operations data into accelerating business value.S317407: Oracle GoldenGate Architecture Deep Dive Scott Corbin; Jagdev Dhillon Hotel Nikko--Peninsula 22 September, 16:45-17:45 Oracle GoldenGate log-based replication solutions deliver high volumes of transactional data between heterogeneous systems in subsecond latency with negligible overhead on the infrastructure. The technology enables real-time data integration for operational BI as well as high-availability solutions for mission-critical databases. Learn about the Oracle GoldenGate product architecture from the lead architect. This session will cover the Oracle GoldenGate internal product architecture and its advantages as well as general replication topics. Discussion topics will include transaction integrity, checkpointing for guaranteed data delivery, replication topologies and strategies, conflict detection and resolution, and more.For more details see: Oracle OpenWorld 2010 Agenda.

Here are the Oracle OpenWorld 2010 Data Integration Track events for 22 September. S317489: Lifetouch Improves the Quality of Its Customer Data with Oracle Data QualityColleen McDonald; Brian Olson;...

Events

Oracle OpenWorld 2010 Data Integration Track Session Schedule: Tuesday 21 September

Here are the Oracle OpenWorld 2010 Data Integration Track events for 21 September.S319114: Extend ETL with Oracle Warehouse Builder 11g Release 2 Suraj Bang, Consultant Moscone West L3--Rm 3020 21 September, 11:00-12:00 Beyond basic Oracle-to-Oracle extract, transform, and load (ETL), data warehousing customers need to integrate data from multiple datasources spanning multiple database vendors, file formats (CSV, XML, HTML), and unstructured datasources such as PDFs and log files. This session describes experiences with extending Oracle Warehouse Builder 11g Release 2 to extract data from Postgres, SQL Server, MySQL, Sybase, PDF documents, and Sharepoint (yes! Sharepoint!) for a major banking client's data warehousing project supporting IT operations.Learn how to perform metadata extraction, build custom knowledge-module-based ETL, and replace ad hoc Perl and Java extraction code with a manageable ETL solution built on Oracle Warehouse Builder's extensible plaform.S319115: Oracle GoldenGate: Making Your Best Practices Golden Steven Phillips Moscone West L3--Rm 3016 21 September, 11:00-12:00 Oracle GoldenGate is rapidly becoming the tool of choice for replicating Oracle data for reporting, zero-downtime database upgrades, and data distribution. In this session, you will learn the best practices for running Oracle GoldenGate replication. HP engineers will share their best practices and tips and tricks based on a wide variety of experiences with running Oracle GoldenGate in real-world scenarios. Topics include best practices for planning, gathering requirements, installation, network considerations, replication configuration, and management.S315221:Morrisons Supermarkets: DW Best Practices with Oracle Data Integrator Dan Jackson; Jonathan Walsh; Duncan Alastra Moscone West L3--Rm 3020 21 September, 12:30-13:30 Hear how Wm. Morrisons Supermarkets implemented a near-real-time operational data store (ODS) to integrate its mission-critical sales data with its ERP applications. This session describes the business problem Wm. Morrisons faced in integrating a new electronic point of sale (EPOS) system for 450 stores generating more than 50 million transactions per day within the backdrop of a major systems replatform program. It reviews the physical and application architecture as well as best practices for high availability using Oracle Data Integrator and Oracle Database to load high-volume data with parallelism, load balancing, and Oracle Real Application Clusters (Oracle RAC). S317416: Dell Success Story: Harnessing the Power of Oracle GoldenGate/Oracle Streams Shivani Agarwal; Raveendra Avutu ; Nick Wagner Moscone West L3--Rm 3020 21 September, 14:00-15:00 Dell Inc. has implemented Oracle GoldenGate’s real-time data replication technology in various solutions: from zero-downtime database upgrades and distribution of real-time data for reporting to migrating to a new operational MDM hub. Dell has also used Oracle Streams and combined it with Oracle GoldenGate for optimized solutions in its environment. Attend this session to learn how Dell implemented Oracle GoldenGate and Oracle Streams to modernize and advance its IT infrastructure. In the session, Oracle GoldenGate product management also looks at the main differences between the two products and discusses the product strategy for Oracle’s data replication technology.S315953:Oracle GoldenGate in Depth: What's All the Fuss About Borkur Steingrimsson, Rittman Mead Consulting Moscone West L3--Rm 3020 21 September, 15:30-16:30 This session takes a good look at the exciting features made available in the latest release of Oracle GoldenGate. It presents a comprehensive architecture review, installation steps, and configurations. A demonstration of Oracle GoldenGate in practice, followed by a discussion, aims to give the attendees a strong understanding of what all the fuss is about.S315426: Consolidating Data on the Oracle Database Machine with Oracle GoldenGate Ken Gottry Moscone West L3--Rm 3020 21 September, 17:00-18:00 The Oracle Database Machine with the Oracle Exadata Storage Server is powerful and effective for storing and presenting lots of data. One way to amass data on the Oracle Database Machine is to use Oracle GoldenGate to consolidate it from many disparate systems. This session tells how Oracle GoldenGate performs change data capture from remote database systems; transmits changes to the Oracle Database Machine; and uses Oracle Database File System, a new Oracle Database 11g Release 2 feature, to access the Oracle Exadata Storage Server change data, transforming it as needed to match a standardized, central format. The power of the Oracle Database Machine is used to present centralized data for consumption by querying applications.For more details see: Oracle OpenWorld 2010 Agenda.

Here are the Oracle OpenWorld 2010 Data Integration Track events for 21 September. S319114: Extend ETL with Oracle Warehouse Builder 11g Release 2 Suraj Bang, ConsultantMoscone West L3--Rm 3020 21...

Events

Oracle OpenWorld 2010 Data Integration Track Session Schedule: Monday 20 September

Here's the list of OpenWorld Data Integration track events for Monday 20 September 2010. S316943--Oracle Data Integration Strategy: Accelerate Your BusinessSpeakers: Dain Hansen; Miranda Nash; Sandro TomaLocation: Moscone West L3, Rm 3020 Time: 11:00-12:00 Oracle is the leader in data integration, delivering the fastest and most scalable real-time data integration solutions for its customers. By combining Oracle GoldenGate with Oracle Data Integrator and Oracle Data Quality solutions, Oracle is able to unify real-time data movement, bulk data movement, and data quality into a comprehensive, integrated solution. This session examines Oracle's strategy for data integration, including how its world-class capabilities for availability, scalability, low latency, and help separate it in the industry. It also looks at how customers have adopted Oracle Data Integration to make them more agile and more profitable. S316943--Using Oracle GoldenGate with Oracle ExadataSpeakers: Steven George; Alok PareekLocation: Hotel Nikko, Nikko Ballroom III Time: 11:30-12:30 This session will discuss how Oracle GoldenGate can be used with Oracle Exadata. Topics covered will include real-time data integration into Oracle Exadata from heterogeneous data sources and upgrading/migrating to Oracle Exadata with near-zero downtime from prior Oracle Database versions and non-Oracle databases. The session will cover detailed solution architecture, technical configuration details, best practices, and case studies.S316943--Oracle Data Integration Strategy: Accelerate Your Business Speakers: Dain Hansen; Miranda Nash; Sandro TomaLocation: Moscone West L3, Rm 3020 Time: 20 September, 11:00-12:00 Oracle is the leader in data integration, delivering the fastest and most scalable real-time data integration solutions for its customers. By combining Oracle GoldenGate with Oracle Data Integrator and Oracle Data Quality solutions, Oracle is able to unify real-time data movement, bulk data movement, and data quality into a comprehensive, integrated solution. This session examines Oracle's strategy for data integration, including how its world-class capabilities for availability, scalability, low latency, and help separate it in the industry. It also looks at how customers have adopted Oracle Data Integration to make them more agile and more profitable. S317311--Using Oracle GoldenGate with Oracle Exadata Speakers: Steven George; Alok Pareek Location: Hotel Nikko, Nikko Ballroom III Time: 20 September, 11:30-12:30 This session will discuss how Oracle GoldenGate can be used with Oracle Exadata. Topics covered will include real-time data integration into Oracle Exadata from heterogeneous data sources and upgrading/migrating to Oracle Exadata with near-zero downtime from prior Oracle Database versions and non-Oracle databases. The session will cover detailed solution architecture, technical configuration details, best practices, and case studies.S317449: Sprint Improves Customer Experience with Real-Time Data Integration David Harris, Chris Lawless, Senior Principal Product Manager, Oracle Corporation Steve Wilkes, Senior Director, Development, Oracle Corporation Moscone West L3--Rm 3020 20 September, 12:30-13:30 Sprint strives to improve customer service in today's competitive telecommunications market. The company has relied on Oracle GoldenGate's real-time data integration technology to achieve a single view of customers and to provide innovative solutions to grow its customer base. Attend this session to learn how Sprint transformed its customer service operations by using Oracle GoldenGate's real-time data integration product for real-time data in its enterprise data warehouse for improved decision-making.S317415: Real-Time Data Integration for Business Intelligence: Cbeyond Success Story Duncan Meredith; Randy Pugh; Irem Radzik Moscone West L3--Rm 3020 20 September, 15:30-16:30 Today's savvy consumers force firms to transform their infrastructures to offer high-quality service while minimizing costs. Oracle GoldenGate feeds real-time data to analytical environments and enables decision-making with up-to-date data. CBeyond, a leading provider of IT and communications services for small businesses, is a great example of innovative companies that use advanced technologies to differentiate their business. It uses Oracle GoldenGate to feed real-time data to an operational data store for BI systems from Oracle's Siebel Customer Relationship Management without affecting performance. This session covers Oracle's real-time data integration offering and how CBeyond uses the product to deliver a superior customer experience.S317202: Accelerate Your Service-Oriented Architecture with Oracle Data Integration Suite Doug Dittrich; Dain Hansen Hotel Nikko--Mendocino I / II 20 September, 16:00-17:00 Organizations are turning more and more to data integration to accelerate their service-oriented architecture (SOA). A comprehensive approach to data integration can help lower costs through consolidation, reduce risk of bad data polluting applications, and shorten the time to deliver new service offerings. In this session you'll learn how to innovate across your applications swiftly and at a lower cost using Oracle Data Integrator Enterprise Edition, Oracle GoldenGate, and Oracle Data Quality for Data Integrator. We'll address best practices for building data services that are high performing and scalable across the enterprise and how innovative approaches in consolidation and federation can maximize returns for SOA.S317414: Oracle GoldenGate Product Strategy and Roadmap Glenn Goodrich; Chris McAllister Moscone West L3--Rm 3020 20 September, 17:00-18:00 In this session, the Oracle GoldenGate product management team presents the key new features from the latest Oracle GoldenGate release. Along with Oracle Database-specific features, the presentation covers new functionality for other supported databases such as HP NonStop, DB2, SQL Server, Sybase, and Teradata. It also provides a glimpse into the future product roadmap for Oracle GoldenGate and add-on products such as Oracle GoldenGate Veridata and Oracle Management Pack for Oracle GoldenGate. Attend the session to learn what's new in Oracle GoldenGate and why customers currently on older versions should upgrade. You will also learn the recommended path for upgrading to the latest release. For more details on Monday's events see the Oracle Open World 2010 Agenda.

Here's the list of OpenWorld Data Integration track events for Monday 20 September 2010. S316943--Oracle Data Integration Strategy: Accelerate Your Business Speakers: Dain Hansen; Miranda Nash;...

How to ...

OWB 11gR2 – Metadata change management

OWB when generating objects to the Oracle database can generate create, drop/replace and upgrade/alter scripts for the primary objects in the database. This has been in the product for a long time now, there is metadata reconciliation services both into and out of the tool. This can be thought of as part 1 since we will look at managing change from OWB to the database. To upgrade a table you must use the Control Center Manager and select the upgrade action; There are certain privileges that this operation needs that by default are disabled, so if you try and upgrade a table without them you will get the following error; Now what? Well there is some setup that should have been done in addition to the install of the repository. The detail is actually in the deployment job audit which you can find in the control center manager and I think is mentioned in the documentation (note to go dig it out). If you look into the detail of the error (see image above) it states that you must run the script owb/rtp/grant_upgrade_privileges.sql this needs to be run as a privileged user and the script has  parameter which is the schema you wish to grant the privilege to. Below I am granting privilege to the SALES schema; After you enable these privileges now you are ready to go, when you upgrade there is an additional tab 'Impact Report' where you can see detailed information about the upgrade. Its important to know about this because the world of upgrade has many different scenarios, this will give you lots of information that will help understand what is going on. You can also see the script with the ALTER statements, in the example I ran through for example one change was to add a new column CHANNEL_DESCRIPTION; So...hopefully if you hit the RTC-5270 error you know what to do now.

OWB when generating objects to the Oracle database can generate create, drop/replace and upgrade/alter scripts for the primary objects in the database. This has been in the product for a long time...

ETL

OWB 11gR2 – Lookup operator

The lookup operator in OWB 11gR2 has been supercharged letting you utilize it in many more scenarios and generate more efficient SQL and in general be more flexible than before. The enhancements were requested from large use case scenarios and are summarized below (and expand on the doc here); Lookup operator supports multiple lookup objects - more efficient use of screen real estate. Lookup operator supports complex lookup conditions including a range of condition types such as =, <, >, or BETWEEN...AND (any boolean expression basically). Preferences for which row to return in case of multiple rows in lookup result (ANY, FIRST,LAST,nTH). Below we can see that there are multiple output groups in the lookup operator. We can lookup all the surrogate keys for example of one sales tables in a single lookup. For each output group you can specify the table which you will be looking up information in, below we see that OUTGRP3 is looking up information in CHANNELS_TAB, note we could rename OUTGRP3 to have a meaningful name. The lookup condition as before by default is a simple column equals column style condition for each output group and input pair. Note now in the panel there is a 'Freestyle Editing' option.   If you select the 'Freestyle Editing' you will change into a more traditional OWB expression editor with the available inputs in the tree and now the ability to do different types of lookups including complex expressions. Last but definitely not least, we have the ability to control for each output group how the lookup row is to returned, do we want a single row and let it be; any row (uses rownum = 1) first row - uses MIN(column) KEEP (DENSE_RANK FIRST ORDER BY column_4_order ASC) last row - uses MAX(column) KEEP (DENSE_RANK FIRST ORDER BY column_4_order ASC) nTH row We can also decide to make the mapping error if there are multiple matching lookup entries. How does it error? Well the generated code would cause the map to error with the SQL error; ORA-01427: single-row subquery returns more than one row Or we can decide to return all the matching rows. For the case where no row matches you can still specify the default values to return. These defaults are then used in the generated code. Many more options than before as you can see. You'll also see from the generated code that a scalar subquery is generated for the single-row style lookups. Advice on Using The Wizard? How do I use the lookup? Well the quickest way of building one up when you add in a lookup operator is to exit the lookup wizard at Step 4 when defining the Input Attributes. Its much easier just to use the mapper to draw the inputs rather than type them in. So after I exit from the lookup wizard I map the input attributes then edit the lookup operator and define the actual lookups etc. Some interesting changes there that were included in the lookup operator for the OWB 11gR2 release.

The lookup operator in OWB 11gR2 has been supercharged letting you utilize it in many more scenarios and generate more efficient SQL and in general be more flexible than before. The enhancements were...

ETL

OWB 11gR2 – Debugging

The mapping debugger is a useful tool when testing business logic in your mappings. It allows you to step through row by row, or operator by operator, or with breakpoints and see how the data is transformed by each operator. It does this in the context of the objects you have used to design the mapping, these are logical ETL objects used for describing the transformations. So each operator has a specific detail panel that lets you see before, during and after views of the data. The example below illustrates the filter operator where we can see the data before and after  the operator execution, also the property inspector is open so we see the actual filter expression itself that is applied to the source rows. Pretty much like any debugger there are common functions like step, skip, step into, setting breakpoints and watching data. Some debugging illustrations below, I like this view of the operators with real data since you can actually visualize what the operator is doing (so these are actually useful to look at even to get a quick idea of some of the operators); Union (set operation example) Aggregator Join Expression Pivot Unpivot Deduplicator Pluggable mapping (user-defined, reusable components) Splitter Filter So for those that like using debuggers the above is all good! What about the other type of developer? Commonly when I am problem solving I will use the intermediate code generator within mapping. This lets me take snippets of the SQL and execute manually to figure out a problem. For each operator in the graph you can get the SQL before, after and for data operators get loading (insert/update/merge etc.) or incoming (select) SQL. The generated SQL has the operator name in comments to allow you to pinpoint the responsible operator for that part of the SQL. This is handy for the SQL-savvy users that want to run the SQL manually and do their own debugging. Note in the above panel there are a few other buttons; the explain plan provides a graphical view of the estimated Oracle explain plan, statistics details provides actual information from executing the SQL including the plan an statistics and the SQL Tuning advisor uses DBMS_SQLTUNE (a comprehensive tuning task is started). There we have it, a quick peek at some of the operators in debug mode and tuning capabilities in OWB. I think this could be continued to explain some other mechanisms for debugging the package and database views for seeing lines numbers etc.

The mapping debugger is a useful tool when testing business logic in your mappings. It allows you to step through row by row, or operator by operator, or with breakpoints and see how the data is...

How to Use USER_DEFINED Activity in OWB Process Flow (part II)

We have discussed how to use USER_DEFINED activity in OWB Process Flow in the past. In this topic, we will talk about another option for this property - “SCHEDULER”. With this setting, we can run the external job as a configured OS user. Enable execution of User Defined activity Presume we are using the UNIX platform for this simple demo. First, we need to modify the Runtime.properties as below. SCHEDULER uses a DBMS Scheduler external job submitted by the Control Center repository owner which is executed by the default operating system user configured by the DBA property.RuntimePlatform.0.NativeExecution.Shell.security_constraint= SCHEDULER To make this change take effect, we need to restart the Control Center Service. Then we can take advantage of the OS user configure settings in <ORACLE_HOME>/rdbms/admin/externaljob.ora (which is configured to run the jobs as a dedicated batch user). By default, a low privileged user & group (nobody) is defined in this file, you need to change this to the expected OS user for the execution (In my environment, I have set it to owb user). Note: externaljob.ora is owned by root, so you need to root privilege to make the changes. In OWB design center, let's still use the simple process flow, which contains a Start activity, a User Defined activity and an End Success activity. Leave all parameters of activity USER_DEFINED unchanged except that we enter /tmp/test.sh into the Value column of the COMMAND parameter. Then let's create the shell script test.sh in /tmp directory. Here is the content of /tmp/test.sh (this article is demonstrating a scenario in Linux system, and /tmp/test.sh is a Bash shell script): #!/bin/sh echo `id` > /tmp/test.txt Note: don't forget to grant the execution privilege on /tmp/test.sh to your dedicated OS user. For simplicity, we just use the following command. chmod a+x /tmp/test.sh After the change is ready, let’s deploy and execute the Process Flow. We will see that the Process Flow completes successfully. The execution of /tmp/test.sh successfully generated a file /tmp/test.txt, which contains the owb user information as below.   Platform differences UNIX platform and ORACLE 11.2 are used in above example; there are different settings with different environments though. With UNIX platform, if using release 10.2.0.2 or prior releases (where there is no rdbms/admin/externaljob.ora) we need to change the owner of the extjob executable (who should be nobody by default); With Windows platform, instead of modify externaljob.ora, we need to change the user that the external jobs Windows service runs as ("OracleJobSchedulerSID" service).   Diagnostic information OWB uses DBMS_SCHEDULER (create_job, enable etc) package to execute external processes, so when we have issues with the execution, we can try executing using the package manually to narrow down the issue. An example is as below: BEGIN     DBMS_SCHEDULER.CREATE_JOB(     job_name             => 'TESTJOB',     job_type             => 'EXECUTABLE',     job_action  => '/tmp/test.sh');     DBMS_SCHEDULER.ENABLE('TESTJOB');     END;     / This block should be run under the context of the Control Center Service Owner, In OWB 11.2 release, the owner should be OWBSYS. The view “all_scheduler_job_run_details” can be queried for more details with the job run information.   We can also pass the parameters to the command or leverage the return value with “SCHEDULER”, they are the same as discussed in previous post, and will not be discussed here.

We have discussed how to use USER_DEFINED activity in OWB Process Flow in the past. In this topic, we will talk about another option for this property - “SCHEDULER”. With this setting, we can run the...

Data Warehousing Best Practices: Oracle’s Data Warehouse Reference Architecture and OWB

Sometimes we talk about the details of Warehouse Builder functionality so much that it’s easy to forget to raise our heads from time to time, and look at the broader questions of data warehousing and business intelligence. But recently I was asked by a few different customers, who are already using Oracle and OWB, “Wait… are we going about this the right way?” (No suggestion is intended that these customers aren’t generating smart solutions to really tough problems… but the problems can be really tough, and the solutions quite complex, and it’s always good to ask for help, especially when coming up to a technology or organizational inflection point that creates an opportunity for a re-think or a re-design.) In connection with this, I introduced them to Oracle’s Data Warehouse Reference Architecture whitepaper, most recently revised in February 2010, which they and I found to be a great resource. While actually mostly vendor neutral, even shying away from talking about specifics of the Oracle database, the paper if properly understood made the value proposition for data warehousing on Oracle (and Exadata) and for using OWB as the data warehousing tool quite compelling. So I thought I’d share it with you… Perhaps going forward an interesting blog post or paper could be built around the question, “What makes OWB 11gR2 the ideal tool for Oracle data warehousing?” with reference to this document. One more thing on the to-do list, I guess. Until I get to it, go do the reading (and enjoy it!) Perhaps there will even be a quiz!

Sometimes we talk about the details of Warehouse Builder functionality so much that it’s easy to forget to raise our heads from time to time, and look at the broader questions of data warehousing and...

Oracle

Integrated Cloud Applications & Platform Services