Domain-Value Maps and Cross References

>>Requirement -

When an object flows from one system to another system, both using their own entities to represent the same type of object, transformation is required to map the entity from one system to another. For example, when a new customer is created in a SAP application, you might want to create a new entry for the same customer in your Oracle E-Business Suite application named as EBS.

So the functionality is required to map the entities from different domains. Even if in future, another domain gets added, again is the requirement to represent that entity in some common entity format. For example when an object gets created in system A with unique identifier A001, the same object is propagated in system B with identifier B001. The decode functionality is required to map B from A.

>> Solution -
Oracle ESB provides two solutions for this problem -

>>1 Domain-value map –
A domain-value map can be created and populated using Oracle ESB Control. It can than be used with the Oracle JDeveloper Mapper tool while developing XSLT data transformations during design time. Then, at runtime the lookups for application-specific values occur. It uses xml file to store the mapping values.

For example, suppose we want to use a domain-value map to perform a runtime lookup to convert source id to the target id. Using Oracle Service Bus, the source id is passed and the target id returned using lookup function in transformation.

>>1.1 Architecture -
Oracle ESB DVM uses xml to store the mapping values. The response time of the first call of lookup-dvm is high, as internal cache manager loads the xml document into the memory. If the data is frequently changing than the response time increases, because internal cache manager has to reload it again and again. The search algorithm used by lookup-dvm is sequential search. If the value to be found exists in the last row of dvm file, than the response time increases. The DVM functionality of Oracle ESB is memory intensive if it has large set of mapping values.

DVM is best when having small set of mapping values. Internal cache manager increases the performance tremendously in this case, as xml document always resides in runtime memory.

>>2.1.2 How to use –
Creating and populating DVM – There are two ways of creating and populating data value maps in Oracle ESB. Either to manually edit the dvm using Oracle ESB Console or import values using import command provided in Oracle ESB console. DVM does not allow to enter two rows having same set of values. Manual edit functionality should be used, when we don’t have large set of mapping values and which are frequently changing. Importing dvm files is the other options, provided the dvm files have specific format.

Looking up – To lookup the values we have lookup-dvm function. This function can be used in both Oracle ESB and Oracle BPEL through transformation. If the lookup target domain has multiple values for specific source domain value then it returns the first value, as this search is sequential. If lookup fails to find the target domain value, then it returns the default value passed in lookup-dvm function.

>>2.2 Cross References –
A cross reference table consists of following two parts, metadata and the actual data. The metadata is created by using the cross reference command line utilities and is stored in the repository as an XML file. The actual data is stored in the database.

You can use a cross reference table to look up column values at run time. However, before using a cross reference to look up a particular value, you need to populate it at run time. This can de done by using the cross reference XPath functions. The XPath functions enable you to populate a cross reference, perform lookups, and delete a column value. These XPath functions can be used in the Expression builder dialog box to create an expression or in the XSLT Mapper dialog box to create transformations.

>>2.2.1 How it works -
This functionality of finding the cross domain mapping uses both xml and database. XML is used to store the metadata of the xref table and actual data get stored in database. Creation of xref tables is done using xreftool command line utility. Default datasource used by cross reference has jndi name jdbc/xref. Other datasources can be used are jdbc/esb and jdbc/BPELServerDataSource. To start working with cross reference, it is required to create a table in any of the above datasources. Following sql query is used to create XREF_DATA table -


CREATE TABLE XREF_DATA ( XREF_TABLE_NAME VARCHAR2(4000) NOT NULL, XREF_COLUMN_NAME VARCHAR2(4000) NOT NULL, ROW_NUMBER VARCHAR2(48) NOT NULL, VALUE VARCHAR2(4000) NOT NULL, IS_DELETED VARCHAR2(1) NOT NULL, LAST_MODIFIED VARCHAR2(100) NOT NULL, LAST_ACCESSED VARCHAR2(100) NOT NULL );

So here for each lookup, a jdbc call is made to select target domain value. So this type of referencing is not memory intensive. This proved to be best if we have large set of mapping values, and is dynamic. The performance is totally depends on the type and the location of the datasource. It also supports 1:M mapping of domain values.

>>2.2.2 How to use – XPath functions provided can be used in transformation and assign activity of Oracle BPEL. These functions have better exception handling capability. To import and export the cross reference tables, command line utilities such as xrefexport and xrefimport are provided.