Storing data as delimited strings in Oracle CPQ Cloud :: Anti-Pattern

Many consulting companies today offer Oracle CPQ Cloud implementation services. Most of them follow the Oracle CPQ Cloud Implementation Guide and additional help resources like the product help section and knowledge on Oracle Support.

Being in the CPQ space for a while we realized that some of these implementation guidelines where not updated to the new technological realities since BigMachines inception in 2000s. Although Oracle bough the product in 2013 and added significant functionality, looking at the recent implementations, we can easily realize that most are implementing like we are in 2003.

One of the major ANTI-PATTERN is the storage of data as delimited strings. 

The documentation claims that a delimited string is a great solution to store a dynamic set of data. This means that you could use it to serialize a dictionary or an array and store that information for future use.

The format of a delimited string is a concatenation as advised by Oracle/BigMachines is:
KEY-DELIMITER-VALUE[-DELIMITER]

As an example the following is a good example for a door configurator delimited string:
doors~2~hasWindows~true~width~17~hight~31

However, the problems with this approach are:

• You need to parse the whole string every-time you want to read or write to the string
• When you have multiple values for a key it's hard to understand the mappings
• If some values contain the key words, it's harder to parse
• If the sequence of the attributes changes it brakes the relying functionality 
• No value for key can cause null-pointer exceptions or error out other keys
• Delimiter present in the string or value would etc

To solve some of these problems many creative CPQ Consultants came up with variation of these to overcome those issues:

• Adding a new delimiters between pairs - door~2|hasWindows~true 
• Create unique delimiters  - door_-_2***|***hasWindows_-_true 
• Check for key presence with delimiter - find(delimitedString, "door_-_")>-1

Although this will solve get the system running the problems would still remain there as technical debt and will be one of the hardest things to debug in production once some values are missing or we happened to have a use input "_-_" as an actual value.

To actually solve the problem CPQ Consultants and CPQ Developers need to adopt the new data format that was actually initially published around the same time BigMachines lunched CPQ, JSON. 

JSON is a flexible data format that was published by Douglas Crockford (read more on JSON). It works perfectly for Oracle CPQ Cloud because it's simple and easy to read.

In JSON the same delimited string we presented before can be represented as:

{

    "doors": 2,

    "hasWindows": true,

    "width": 17,

    "hight": 31

}

This is a more human friendly format and can be understood by every consultant and the business. Combined withe the new JSON functions that Oracle CPQ Cloud released as of 2016 R1, you can:

• check for existence of the key,
• check for empty or null values
• store complex datatypes like nested objets and arrays
• cross language data as you can use the same for your frontend JavaScript 
• cross platform as you could pass it to other systems 
• can be stored as a string too

We encourage all CPQ Consultants and CPQ Developers to start using JSON as the default data format and move away from Delimited Stings.

Save yourself from headaches and help your company save time and money.