Saturday Dec 28, 2013

GoldenGate 12c - Coordinated Delivery Example

This blog provides an example of setting up a coordinated replicat group using Oracle GoldenGate for MySQL.
[Read More]

Wednesday Dec 18, 2013

ODI 12c - Temporal Data Loading

The temporal validity feature in 12c of the Oracle Database is a great feature for any time based data - see the 12c database OBE here for a demo of the database capability. If you are thinking dimensional data that varies over time.... the temporal validity capabilities of 12c are a great fit, worth checking it out. I wanted to post this to stir discussion in this area, this uses some customized KMs to integrate data into a database table that supports the temporal validity capabilities of the 12c database. This gives a few benefits - one is with respect to querying, you can easily query the current view of the world without adding filters on numerous tables and so on, you can also query back in time - again without having such filters messing up semantics of your logical query.

  1. create table DIM_CUSTOMER (
  9. );
  10. alter table DIM_CUSTOMER add period for CURRENT_IND;  

The data tables for your slowly changing dimension tables probably already have start/end timestamps, the above example shows how temporal information can be added to a table, adding the temporal validity capability is as simple as an 'ALTER TABLE' on the existing table. If we then try and reverse this in ODI, the columns to support temporal validity will not be reversed as they are hidden and the RKM Oracle shipped with ODI uses the ALL_TAB_COLUMNS dictionary view which does not project hidden columns. Creating a new RKM Oracle (Inc Hidden) lets us reverse engineer such tables into ODI. You can download my modified RKM here. Using this lets me reverse the datastore above and see the following definition in ODI (the RKM uses all_tab_cols rather than all_tab_columns etc so I can see hidden columns);

You can see the columns CURRENT_IND_START, CURRENT_IND_END and CURRENT_IND have been defined on the table, this was as a result of adding the temporal validity to the table in the ALTER TABLE statement above. I have defined the SCD tags for starting timestamp, ending timestamp and current record flag on the attributes. Note I can also define how new records are versioned, I define that some columns will simply have their values overwritten and other columns will trigger new rows to be added. I have also defined a surrogate key - this is optional, so you do not need to create such annotations to use this IKM.

The data is then loaded and I can specify the start/end timestamps for the row when it is loaded. The IKM will load the data, updating data marked as overwrite and inserting new rows where trigger data has changed.

The IKM is almost identical to the IKMs for slowly changing dimensions with some minor changes - ODI in the slowly changing KM will manage the current row indicator, with Oracle this is a virtual column so is computed. The population of that column has been removed from the KM plus a few other places it was used. I called it IKM Oracle Temporal Data, you can download it here.

 If I load my data then change the status from Married to Single and rerun, then I will get the following rows loaded, note I am querying the surrogate key also since I supplied that metadata (this is optional);

  1. select key, c_mstat,
  2. to_char(current_ind_start,'dd-mon-yyyy') "Start",
  3. to_char(current_ind_end,'dd-mon-yyyy') "End"
  4. from scott.dim_customer;
  5.        KEY C_MSTAT, Start       End               
  6.         11 Married 18-dec-2013 18-dec-2013
  7.         12 Single 18-dec-2013 01-jan-2400

A new surrogate key is generated for the versioned row. If I define only the current time to be used, then the database will automatically filter my data;

  1. exec dbms_flashback_archive.enable_at_valid_time('CURRENT');

  2. select key, c_mstat,
  3.   to_char(current_ind_start,'dd-mon-yyyy') "Start",
  4.   to_char(current_ind_end,'dd-mon-yyyy') "End"
  5. from scott.dim_customer;
  6.        KEY C_MSTAT Start End     
  7.         12 Single 18-dec-2013 01-jan-2400

I can also query as of a particular time without impacting my query

  1. exec dbms_flashback_archive.enable_at_valid_time('ASOF','18-DEC-13 AM');

  2. select key, c_mstat,
  3.   to_char(current_ind_start,'dd-mon-yyyy') "Start",
  4.   to_char(current_ind_end,'dd-mon-yyyy') "End"
  5. from scott.dim_customer;
  6.        KEY C_MSTAT Start End
  7.         11 Married 18-dec-2013 18-dec-2013

Imagine without this, on the query side you will have to have lots of conditional queries based on time for each object that varies by time. With this approach you define the time window and query. Simple. The KMs help you easily load data into such tables too, you can see how flexible ODI is with respect to providing new integration patterns to take advantage of the greatest and latest features. The KMs used in this blog post have been posted on the site to stir ideas and further discussion, I'm interested to hear what you think.

Tuesday Dec 17, 2013

ODI 12c - Slowly Changing Dimensions

Here we see how to setup a slowly changing dimension load in ODI 12c, everything from defining the metadata on the datastore to loading the data. I illustrated how this was done in ODI 11g here, for ODI 12c this has been simplified, the ODI 12c viewlet here provides a very quick look at setting up a datastore for supporting slowly changing dimension data loading. It uses the IKM Slowly Changing Dimension and shows dimension members being versioned when for example a marital status change happens.

The datastore editor has been enhanced to be able to quickly annotate attributes in the datastore with SCD metadata - information such as the surrogate key attribute, or the attribute that triggers history.

This used to be a particular pain to setup, so now you can quickly define this information and also get a convenient display/overview of the attributes and what they mean and are used for. Check out the viewlet above, see how it hangs together. I plan on illustrating thus further, so we have seen how you can load slowly changing dimensions for a data warehouse, we can take this even further and use the temporal validity feature of the Oracle 12c database - how you load temporal data, what does the KM give you etc. 

Monday Dec 16, 2013

ODI 12c - Pivoting Data and Correlated subqueries

Here are more data transformation illustrations using the ODI 12c SDK demonstrating pivoting/unpivoting data and correlated subqueries. The SDK examples are on the shared site and are in addition to the existing examples I posted on the blog entry on Mapping Ins and Outs here.

You can download these ODI_12c_Mappings_SDK Examples here unzip and inspect the groovy and the images of the mappings within. There are examples from the SQL documentation on correlated subqueries and examples previously posted for OWB here on pivot/unpivot.

The mappings I have covered include (click on hyperlink for an image of each);

  1. unpivot data from a table with YEAR,Q1_sales,Q2_sales,Q3_sales and Q4_sales columns to YEAR,QTR,SALES (UNPIVOT component).
  2. pivot data from a table with YEAR,QTR,SALES columns to YEAR,Q1_sales,Q2_sales,Q3_sales and Q4_sales columns (PIVOT component).
  3. perform a subquery to get the employees who are in a department that exists in the subquery (SUBQUERY_FILTER component).
  4. perform a subquery to get the employees who have a salary greater than the average salary for employees in their department (SUBQUERY_FILTER component).

These examples all use the mapping SDK to build data integration flows, the existing ODI interface SDK is still supported for now. You can see the mapping built from the SDK to perform unpivot of data as an illustration. There are now 14 examples of components in the download illustrating each one, they can operate on arbitrary data and have generic KMs as well as specific Oracle implementations.

Plenty more to show including some of the nitty gritty details on the physical deployment specification side to help illustrate how to build flows using the SDK and backup the javadoc. A lot of great transformation components and plenty more to explore and discover!

Wednesday Dec 11, 2013

GoldenGate 12c - What is Coordinated Delivery?

In Oracle GoldenGate 12c, the parallel apply is improved with two features: Integrated Delivery and Coordinated Delivery. Different from the Integrated Delivery, Coordinated Delivery is not limited to Oracle Database.This blog discusses the new Coordinated Delivery feature by answering two questions:

  • Why do I need Coordinated Delivery?

  • What is Coordinated Delivery?
  • [Read More]

    Sunday Dec 08, 2013

    ODI 12c - XML improvements

    ODI has added support to a number of advanced XML Schema constructs in the latest 12.1.2 release: union, list, substitution groups, mixed content, and annotations. XML is a core exchange format in SOA and B2B environments; it is essential for data integration to read and write XML documents in a data load process. ODI supports XML files as sources and targets and allows access to nested XML structures by mapping them to relational tables. This is accomplished through the ODI XML JDBC driver. This entry focuses on the new 12.1.2 features that build on top of XML support from previous ODI releases. 

    Let’s take a look at the improvements in detail with concrete examples for each type.


    The union construct allows a simple type to be based on a union of multiple different simple types, for example a clothing size that is either numeric or based on words. ODI maps a simple type based on union into a VARCHAR column in the resulting relational table, containing the XML string representation of the simple type.

    Example XSD:

    <xs:element name="shirtSize">
          <xs:union memberTypes="sizeNum sizeString" />

    <xs:simpleType name="sizeNum">
    <xs:restriction base="xs:positiveInteger">
          <xs:maxInclusive value="42"/>

    <xs:simpleType name="sizeString">
    <xs:restriction base="xs:string">
          <xs:enumeration value="small"/>
          <xs:enumeration value="medium"/>
          <xs:enumeration value="large"/>

     Example XML fragment:

    <shirtSize >41</shirtSize>
    <shirtSize >small</shirtSize>
    <shirtSize >medium</shirtSize>

    Resulting ODI datastore:


    The list construct defines an XML Schema simple type that consists of a list of values delimited by space. ODI will load a list type into a VARCHAR column regardless of XML type and retain the space-separated value. The user would need to use SQL functions in ODI to extract the appropriate values from the list string.

    Example XSD:

    <xs:element name="availableDates" type="valuelist"/>

    <xs:simpleType name="valuelist">
       <xs:list itemType="xs:integer"/>

    Example XML fragment:

    <availableDates>1 4 5 6 8 11 19 23 30</availableDates>

    Resulting ODI datastore:


    The XML Schema substitution group mechanism helps to extend a complex type into one or more subtypes. Elements of the supertype can be of any of the types in the substitution group, allowing polymorphism. In our example this allows to create a list that contains both shoes or pants types with different structures. ODI treats sequences based on substitution group elements much like it treats xsd:choice based sequences; it generates different tables for each substitution group member and loads the elements into the respective tables. Order and parent FK are maintained.

    Example XSD:

    <xs:element name="clothing" type="ClothingType"/>

    <xs:complexType name="ClothingType">
          <xs:element name="name" type="xs:string"/>

    <xs:element name="shoe" type="ShoeType" substitutionGroup="clothing"/>

    <xs:complexType name="ShoeType">
          <xs:extension base="ClothingType">
                <xs:element name="size" type="xs:string"/>

    <xs:element name="pants" type="PantsType" substitutionGroup="clothing"/>

    <xs:complexType name="PantsType">
          <xs:extension base="ClothingType">
                <xs:element name="inseam" type="xs:integer"/>
                <xs:element name="waist" type="xs:integer"/>

    Example XML Fragment:



    Resulting ODI datastores:

    Mixed Content

    Mixed content allows an element to have arbitrary text between the child elements, much like XHTML. In the example the element email has acomplex type with attribute mixed=”true”. Because of this, ODI will map the element into a single VARCHAR column containing the entire markup with child elements and text. If the user needs to get to individual content inside this element, an external parser needs to be used.

    Example XSD:

    <xs:element name="email">
    <xs:complexType mixed="true">
             <xs:element name="name" type="xs:string"/>
             <xs:element name="order" type="xs:positiveInteger"/>
             <xs:element name="date" type="xs:date"/>

    Example XML Fragment:

    Dear <name>Estella Havisham</name>!
    We have processed your oder number <order>345424356</order>
    The estimated date of delivery will be <date>2013-12-23</date>.

    Resulting ODI datastore:


    XML Schema annotations are used in XML Schema to provide metadata for schema constructs. Annotations have been allowed in ODI XML JDBC driver before, but they have been discarded. Now annotations on elements and attributes are stored in the ODI datastore description. Other annotations, such as those referring to the entire schema or to types, groups, sequences, and others, are still discarded. This information can be viewed and modified in ODI Studio and by the SDK, but modifications are not written back to the XSD file.

    Example XSD:

    <xs:element name="customer">
          <xs:documentation>Formal customer record for retail clothing orders.</xs:documentation>
             <xs:element name="id" type="xs:integer"/>
             <xs:element name="firstName" type="xs:string"/>
             <xs:element name="lastName" type="xs:string"/>
             <xs:element name="birthDate" type="xs:date"/>

    Example XML fragment:


    Annotation as datastore description:

    The following are great resources if you need more information on ODI’s XML support in general: 

    The files for this example can be downloaded here:

    Thursday Dec 05, 2013

    Are You Ready for the Future?

    Have you wondered where you and your organization will be in the future as the information technology advances in an unprecedented way? Have you wondered whether your company, or team, is equipped with the right tools and strategy to be ahead of competition, as everything around us changes? If yes, you are not alone. If not, you should. 

    When it comes to leveraging data and creating opportunities for innovation and growth, data integration is a critical technology that sits in the center of many key initiatives: big data analytics, cloud integration, modernization to name a few. Many industry leaders that excel and continue to innovate are the ones that keep an eye on how they collect and distribute their data.They know the value of turning data into an asset, rather than a cost driver.

    With the new release of Oracle Data Integrator 12c and Oracle GoldenGate 12c, Oracle established itself as the leader in the data integration and replication space.  And how Oracle differentiates its offering is now highlighted in an interactive e-book: The Path to the Future.

    In this e-book we told Oracle Data Integration product family's story through the words of our customers; including Starwood Hotels and Resorts, BT, Herbalife, SK Telecom, MediSwitch, Aria Systems, MegaFon and others.  The e-book reveals how Oracle Data Integration products help you stay ahead of the curve by integrating any data, anywhere, at the speed of thought, with maximum performance and availability,  and full trust in its high quality.  You will also find out how the latest 12c release helps you keep pace with the latest trends while improving developer productivity and performance. 

    Take a look at our new interactive e-book and let us know what you think!

    Tuesday Dec 03, 2013

    ODI 12c - Mapping SDK the ins and outs

    The ODI 12c SDK provides a mechanism to accelerate data integration development using patterns and the APIs in the SDK. With OWB many customers automated mundane tasks - tasks that were repeated and consistent. I've uploaded a bunch of examples covering many characteristics of the 12c mapping SDK, the examples are primarily slanted towards the logical side right now. You can find examples of the components and images for each on the site (download ODI_12c_Mappings_SDK Examples here unzip and inspect the groovy). This is an open community area where you too can contribute.

    The mappings I have covered include (click on hyperlink for an image of each);

    1. basic datastore to datastore mapping with configuration of the LKM and IKM on the deployment spec which options set (datastore component)
    2. simple filter example writing to a target (filter component).
    3. datastores being joined, with a left outer join property set then written to a target (join component).
    4. data being looked up from a reference table using the (lookup component).
    5. data being aggregated, SUM aggregation used and data automatically grouped (aggregate component).
    6. defining expressions which may be reused across many map parts, also useful for explicitly seeing expressions in canvas (expression component).
    7. unifying data using the relational set component to UNION/MINUS data (set component).
    8. if then/else capabilities using the split component to define branches (split component).
    9. defining a dataset to source data from, defines joins and filters (dataset component)
    10. deduplicating data with distinct (distinct component).

    These examples all use the mapping SDK to build data integration flows, the existing ODI interface SDK is still supported for now. I plan on more examples, plenty more to show including some of the nitty gritty details on the physical deployment specification side. I'd also like to show the mapping accelerators, I posted some while back this kind of capability for 11g (see here).

    If you are brand new to the ODI SDK, I tend to break down the labyrinth of classes into the following sections (I blogged about this for 11g here);

    Entry to the Platform

    Object Finder SDK
    odiInstance odiInstance (groovy variable for console) OdiInstance

    Topology Objects

    Object Finder SDK
    Technology IOdiTechnologyFinder OdiTechnology
    Context IOdiContextFinder OdiContext
    Logical Schema IOdiLogicalSchemaFinder OdiLogicalSchema
    Data Server IOdiDataServerFinder OdiDataServer
    Physical Schema IOdiPhysicalSchemaFinder OdiPhysicalSchema
    Logical Schema to Physical Mapping IOdiContextualSchemaMappingFinder OdiContextualSchemaMapping
    Logical Agent IOdiLogicalAgentFinder OdiLogicalAgent
    Physical Agent IOdiPhysicalAgentFinder OdiPhysicalAgent
    Logical Agent to Physical Mapping IOdiContextualAgentMappingFinder OdiContextualAgentMapping
    Master Repository IOdiMasterRepositoryInfoFinder OdiMasterRepositoryInfo
    Work Repository IOdiWorkRepositoryInfoFinder OdiWorkRepositoryInfo

    Project Objects

    Object Finder SDK
    Project IOdiProjectFinder OdiProject
    Folder IOdiFolderFinder OdiFolder
    Mapping IMappingFinder Mapping
    Package IOdiPackageFinder OdiPackage
    Procedure IOdiUserProcedureFinder OdiUserProcedure
    User Function IOdiUserFunctionFinder OdiUserFunction
    Variable IOdiVariableFinder OdiVariable
    Sequence IOdiSequenceFinder OdiSequence
    KM IOdiKMFinder OdiKM

    Load Plans and Scenarios

    Object Finder SDK
    Load Plan IOdiLoadPlanFinder OdiLoadPlan
    Load Plan and Scenario Folder IOdiScenarioFolderFinder OdiScenarioFolder

    Model Objects

    Object Finder SDK
    Model IOdiModelFinder OdiModel
    Sub Model IOdiSubModel OdiSubModel
    DataStore IOdiDataStoreFinder OdiDataStore
    Column IOdiColumnFinder OdiColumn
    Key IOdiKeyFinder OdiKey
    Condition IOdiConditionFinder OdiCondition

    Operator Objects

    Object Finder SDK
    Session Folder IOdiSessionFolderFinder OdiSessionFolder
    Session IOdiSessionFinder OdiSession
    Schedule OdiSchedule

    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;

    txnDef = new DefaultTransactionDefinition();
    tm = odiInstance.getTransactionManager()
    txnStatus = tm.getTransaction(txnDef)

    project = new OdiProject("Project For Demo", "PROJECT_DEMO")

    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;

    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");


    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;

    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)

    Hopefully these examples will get you on your way. 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. The mappings examples are a good start, more to come on those, remember and check out the ODI area in for the examples (here). Have fun, happy coding

    Monday Dec 02, 2013

    Planning the journey from Oracle Warehouse Builder to Oracle Data Integrator

    by Julien Testut and David Allan


    In this post we are going to discuss how Oracle Warehouse Builder (OWB) customers can easily migrate to Oracle Data Integrator 12c (ODI12c) thanks to two key new features introduced recently.

    As mentioned in our statement of direction Oracle Data Integrator is Oracle's strategic product for heterogeneous data integration. recognizing that many Oracle Database customers have a significant investment in OWB, ODI now provides mechanisms to support a phased migration, specifically from OWB 11gR2 ( to ODI 12c (12.1.2).

    The following features are provided to make the transition to ODI easier:

          · ODI 12c supports the execution and administration of OWB 11gR2 jobs directly within ODI Studio, ODI Console and Enterprise Manager. This provides a single orchestration and monitoring solution and allows companies to continue running OWB Mappings and perform a phased migration to ODI.

           · A new migration utility is provided that will automatically translate many OWB objects and mappings into their ODI equivalents. The migration utility is a command-line tool and requires two patches: one for OWB (17547241) and one for ODI (17053768, both can be found on My Oracle Support.

    Phased Migration using OWB and ODI run time Integration

    The run time integration of Oracle Warehouse Builder and Oracle Data Integrator can be used a first step towards aligning with Oracle’s data integration strategy. You can start new developments in ODI 12c while continuing to run side-by-side the two products and start migrating OWB Mappings into ODI Mappings using the migration utility.

    In Oracle Data Integrator 12c it is now possible to define a connection to an Oracle Warehouse Builder workspace in Topology Navigator. Storing the connection and credential details in the ODI repository allows developers to invoke OWB processes in Packages with the OdiStartOwbJob tool (OdiStartOwbJob documentation). It also allows developers and operators to monitor the execution of OWB processes in ODI Studio, ODI Console or Enterprise Manager along with the rest of the ODI jobs.

    The Technical Details

    Invoking OWB processes in ODI 12c is simple. You have to first add the OdiStartOwbJob tool to a Package. Then point the tool to the OWB workspace entry in Topology. This then allows you to browse the content of the OWB workspace and select the specific processes you’d like to invoke as you can see in the following screenshot:

    Once the Package design is complete, you can execute it and monitor its execution in ODI. The OWB and ODI logs have been integrated allowing end users to centrally monitor their ETL processes.

    OWB to ODI Migration Utility

         In addition to the integration of OWB jobs there is also a metadata migration utility to migrate metadata from OWB 11gR2 to ODI 12c. A wide range of metadata can now be migrated with this initial release;(you can find a complete list of objects in the documentation). A large selection of core mapping operators are supported – In addition to the patch containing the utility for the OWB migration, there is also a patch for ODI 12c, and new Knowledge Modules to support some of the OWB mapping capabilities. The user documentation explores these considerations in great detail.

         1. The migration process itself, a good read to be prepared

         2. Details for preparing to migrate

         3. Information on using the migration utility

         4. Reviewing the migration – what to look for in the logs and so on

    Error reference guide and a list of migrated components

    The Technical Details

    Below you can see a project in OWB 11gR2; this has a number of modules including Oracle and flat file modules; the Oracle one has mappings for loading a data warehouse from external tables based on flat files.

    The migration utility is a command line tool that is executed from the OWB home. The utility uses a driver file to specify which modules and objects to migrate. It also has 3 modes of operation, this lets you discover how much of your project will get migrated. The modes are;

          · FAST_CHECK – performs a read only check, which is the fastest way to get an understanding of how much will be migrated.

          · DRY_RUN – a sanity check mode which will try creating the objects in-memory using the ODI 12c SDK but will not commit in the ODI repository.

          · RUN – performs the migration and commit the newly migrated objects in the 12c repository.

    The driver properties file can specify the specific objects to migrate, specify wildcards and lists of objects. The utility produces a report and a log file from its execution. For example, when I migrated the OWB project above, I used the following driver file;



          · ODI_MASTER_URL=jdbc:oracle:thin:@localhost:1521:orcl11204

          · ODI_MASTER_DRIVER=oracle.jdbc.OracleDriver




          · OWB_URL=localhost:1521:orcl11204



    The utility driver parameters are fully document in the user documentation. Firstly I ran the fast check just to see if there were any issues reported. There were none which was good news and bad – I wanted to illustrate an example that wasn’t migrated, so I will create another to show you what happens.

    In the report file when I executed there is a convenient summary of what happened, you can quickly see what was migrated, the number of objects and also a column defining if any were not migrated;

    You can see the migration utility also has migrated external tables; As part of the patch for ODI there is a new component KM to define an external table on top of the flat file definition. This supports the external table capabilities that were in OWB (both bound and unbound external table). Below you can see the mapping loading the sales data that was shown above, it looks identical to what was in OWB, the datastore representing the external table is still the source, the data is split and written to multiple targets.

    The mapping has been created and configured to mimic the OWB behavior. The utility doesn’t configure the multi insert IKMs as this was an optimization within the OWB code generator, in ODI 12c you simply change the assignment of the IKM on each target. So in above my targets were each assigned IKM Oracle Insert, I changed them both to IKM Oracle Multi-Insert after the migration. You can see the physical configuration below;