Thoughts, ideas and solutions from a few EPM consultants.

Formatted Member Formulas in DRM

One of the common issuses when using Oracle DRM as the source of metadata for Essbase and Planning is how to store member formulas. DRM isn't really designed for use with multiline properties. It allows for edting via the GUI but when dealing with action scripts or imports things get harder (According to Kate Helmer things may be better in 11.1.2.4.)

In the situation below we care less about how the data looks in DRM and more about how it exports. We wanted well formatted formulas to appear in Essbase even if that means using escape characters in DRM. Unfortunately DRM doesn't support escape characters (at least not out of the box.)

To work around this issue we created two new properties in addition to our field that houses the member formula. The first is a boolean property that enables or disables the second property to format text using the escape sequences.

The second property does all the heavy lifting. First it checks to make sure the first property is enabled. Then if enabled it uses a series of replacements that change \r to carrige return \n to new line and \t to tab. Obviously this functionality could be expanded if needed but this limited set met our needs.

Script:

if (node.PropValue("Custom.EnableFormulaFormat")) {
    var replaceChars={'\\r':'\r', '\\n':'\n', '\\t':'\t'};
    return node.PropValue('Custom.MemberFormula')
        .replace(/\\\\/g, '\\@')
        .replace(/(?:\\[rnt])/g, function(match) { return replaceChars[match]})
        .replace(/\\@/g, '\\');  
} else
    return node.PropValue('Custom.MemberFormula');

The Result

When the flag is disabled the member formula comes across as is. No formatting is done. When the flag is enabled formatting is applied if defined. It might be safe enough to run without the flag and let it be enabled on all formulas but right now we have only begun to test this functionality.

Disabled:

Enabled: