Making use of ODI Flexfields to meet requirements

Uli Bethke Oracle Data Integrator (ODI)

Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes

What are ODI Flexfields?

In ODI you can create user-defined fields on certain objects. You can think of these fields as additional attributes for certain objects. At design time you populate these attributes with values that are then used at runtime, e.g. by a Knowledge Module. There are various Flexfields defined out of the box for very specific requirements, e.g. there are Flexfields defined on the Datastore object for SAP and HIVE data integration tasks.

Where do you create them?

You create Flexfields in the Security module under the Objects accordion. You can’t create Flexfields for all of the objects. While you can create a Flexfield for an Interface you can’t create a Flexfield for an Interface Target Table.
Once you have created the Flexfield you can then populate it with values in Designer.
An example
You have a requirement to log errors to an error table using a CKM. Based on the severity of the error you want to allow records through to your target table. DQ checks that result in minor errors are logged in the error table and go through to the target. Records with more sever issues only go to the error table and do not end up in the target table.
In a first step we need to define a Numeric type Flexfield on the Condition object. We name this field Passthrough

When you create your DQ Condition in Designer you can then set the value for the Flexfield. The value 1 will allow the record to go through to the target table even if there is a violation of the DQ check.

In a next step we need to write some code in our CKM to make use of the Flexfield and implement the above logic.

We store the value of the Flexfield in a variable passthrough and flag all of the records with the passthrough value.

<? passthrough="<%=odiRef.getFlexFieldValue("" + odiRef.getCK("ID") + "","2500","PASSTHROUGH_COND")%>".replaceAll("'","''''"); ?>

As you can see from the figure below, 2500 is the internal ID of object Condition in the ODI repository.

Teach me Big Data to Advance my Career

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books, holds an Oracle ACE award, and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.