We are using Oracle’s ODI for a project that involves Salesforce as the source and Oracle (the data warehouse) as the target. In this post I will share an issue we had with null values in our primary key.  The interface is implementing SCD Type 2.

From a data retrieval perspective Salesforce is not that much different than a traditional database. There are tables (objects in SF lingo) and unique keys that join various tables together for look-ups and joined relationships.

Something to get used to is the amount of flexibility that Salesforce offers can lead to missing data where you would expect it. For example, an “opportunity” record may point to a “contract” record. A contract may point to a “transaction fee”. At first glance you might layout your record in the warehouse to include something like this: OP_NUM, OP_NAME, CONTRACT_NUM, TRANS_FEE_NUM. Since the opportunity is the “parent” object OP_NUM looks like a good candidate for the SCD natural key. All is well until you realize that a contract may not have a parent project. A transaction may not have a contract. In fact any of these tables (objects) can be independent of each other if the administrator chooses not to enforce a mandatory relationship between them.

This is where creating a concatenated natural key comes in.  In our case CONTRACT_NUM which is the natural key, may be null in some rare cases but there is always a TRANS_FEE_NUM.  When ODI runs and finds multiple null values for the CONTRACT_NUM it throws the dreaded error:

ORA-01427 single-row subquery returns more than one row

There is a simple fix, define another natural key in the “Slowly Changing Dimensions Behavior” for the desired column, in our case it is TRANS_FEE_NUM.

Essentially this creates a composite key and the SCD behavior will now evaluate inserts and updates based on CONTRACT_NUM+TRANS_FEE_NUM.

A quick google search on ODI and ORA-01427 will turn up tips on this problem.  We hope this helps you on your project.