How To Use String CDF Functions in Essbase 11.1.2.x


Below mentioned are the steps to follow-

1. Copy the JAR file and other files extracted from String.zip downloaded from http://www.oracle.com/technetwork/indexes/samplecode/essbase-sample-522117.html to the following directory: (if directory is not present create it)
C:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\java\udf

2 To grant access to the JAR file, add the following statement to the end of the udf.policy file, which
is located in the C:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\java directory:
grant codeBase “file:${essbase.java.home}/../java/udf/ CompareCDF.jar” {permission java.security.AllPermission;};
3. Modify the C:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\java\udf\CDFRegisterAll.cmd as below and then run it:

Ensure RegisterStringFunctions.msh has the correct information in the first 3 lines:
set ServerName = ‘localhost’;
set User = ‘admin’;
set Password = ‘password’;

C:\Oracle\Middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\bin\startMaxL.bat RegisterStringFunctions.msh

pause

Output of above MaxL script:

MAXL> LOGIN admin password ON localhost;

OK/INFO – 1051034 – Logging in user [admin@Native Directory].
OK/INFO – 1241001 – Logged in to Essbase.

MAXL> CREATE OR REPLACE FUNCTION ‘@JconcatStrings’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.ConcatStrings(String[])’
3> SPEC ‘@JConcatStrings(strs)’
4> COMMENT ‘concatenates an array of strings’
5> with property runtime;

OK/INFO – 1056101 – Function @JconcatStrings created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JconcatStringDouble’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.ConcatStringDouble(String,double,boolean,boolean)’
3> SPEC ‘@JConcatStringDouble(str,dbl,strFirst,withDecimal)’
4> COMMENT ‘concatenates a string to a double with control over the order’
5> with property runtime;

OK/INFO – 1056101 – Function @JconcatStringDouble created.

MAXL> CREATE OR REPLACE FUNCTION ‘@Jequals’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.equals(String,String)’
3> SPEC ‘@Jequals(str,str)’
4> COMMENT ‘compares two strings case sensitive’
5> with property runtime;

OK/INFO – 1056101 – Function @Jequals created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JequalsIgnoreCase’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.equalsIgnoreCase(String,String)’
3> SPEC ‘@JequalsIgnoreCase(str,str)’
4> COMMENT ‘compares two strings case insensitive’
5> with property runtime;

OK/INFO – 1056101 – Function @JequalsIgnoreCase created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JcompareStringToDouble’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.bcompareStringToDouble(String,double)’
3> SPEC ‘@JcompareStringToDouble(str,dbl)’
4> COMMENT ‘compares a string to a double’
5> with property runtime;

OK/INFO – 1056101 – Function @JcompareStringToDouble created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JLCase’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.LCase(String)’
3> SPEC ‘@JLCase(str)’
4> COMMENT ‘returns lower case’
5> with property runtime;

OK/INFO – 1056101 – Function @JLCase created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JUCase’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.UCase(String)’
3> SPEC ‘@JUCase(str)’
4> COMMENT ‘returns upper case’
5> with property runtime;

OK/INFO – 1056101 – Function @JUCase created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JgetStringFromDouble’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.getStringFromDouble(double,boolean,boolean)’
3> SPEC ‘@JgetStringFromDouble(dbl,withQuotes,withDecimal)’
4> COMMENT ‘returns a string’
5> with property runtime;

OK/INFO – 1056101 – Function @JgetStringFromDouble created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JgetDoubleFromString’
2> AS ‘java.lang.Double.parseDouble(String)’
3> SPEC ‘@JgetDoubleFromString(str)’
4> COMMENT ‘converts a string to a double’
5> with property runtime;

OK/INFO – 1056101 – Function @JgetDoubleFromString created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JechoBoth’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.echoBoth(String[],double[])’
3> SPEC ‘@JechoBoth(strArray,dblArray)’
4> COMMENT ‘Echoes back all arguments passed to the function. To pass an array of arguments use @List(comma delimited list)’
5> with property runtime;

OK/INFO – 1056101 – Function @JechoBoth created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JechoString’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.echoString(String[])’
3> SPEC ‘@JechoString(strArray)’
4> COMMENT ‘Echoes back all arguments passed to the function. To pass an array of arguments use @List(comma delimited list)’;

OK/INFO – 1056101 – Function @JechoString created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JechoDouble’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.echoDouble(double[])’
3> SPEC ‘@JechoDouble(dblArray)’
4> COMMENT ‘Echoes back all arguments passed to the function. To pass an array of arguments use @List(comma delimited list)’
5> with property runtime;

OK/INFO – 1056101 – Function @JechoDouble created.

MAXL> CREATE OR REPLACE FUNCTION ‘@JgetDoubleQuote’
2> AS ‘com.oracle.essbase.cdf.StringFunctions.getDoubleQuote(String)’
3> SPEC ‘@JgetDoubleQuote(any string)’
4> COMMENT ‘Returns a string enclosed in double quotes’
5> WITH PROPERTY RUNTIME;

OK/INFO – 1056101 – Function @JgetDoubleQuote created.

MAXL> logout;

User admin is logged out

4. Restart Essbase

5. Created calc script under sample.basic as below

Set Updatecalc Off;

Fix (@CHILD(“Product”),”New York”,”Jan”,”Sales”)
“Actual” (
If (@JcompareStringToDouble(@NAME(@CURRMBR(“Product”)),100))
@JechoString(@LIST(“Level 1: Equal “,@NAME(@CURRMBR(“Product”))));
Else
@JechoString(@LIST(“Level 2: Not Equal “,@NAME(@CURRMBR(“Product”))));
EndIf
)

“Actual” (
If (@JequalsIgnoreCase(@ALIAS(@CURRMBR(“Product”)),@SUBSTRING(“goodcolas”,4)))
@JechoString(@LIST(“Equal: “,@ALIAS(@CURRMBR(“Product”)),” | “,@SUBSTRING (“goodcolas”,4)));
Else
@JechoString(@LIST(“Not Equal: “,@ALIAS(@CURRMBR(“Product”))));
EndIf
)

“Actual” (
If (@Jequals(@NAME(@CURRMBR(“Product”)),@JgetStringFromDouble(“100”,@_True,@_False)))
@JechoString(@LIST(“Equal: “,@NAME(@CURRMBR(“Product”)),” | “,@JgetStringFromDouble(“100”,@_True,@_False)));
@JechoString(@JconcatStrings(@LIST(“More”,”-“,”than”,”-“,”two”,”-“,”strings.”)));
Else
@JechoString(@LIST(“Not Equal: “,@NAME(@CURRMBR(“Product”))));
EndIf
)

EndFix

6. Verified and executed – success

EssbaseCluster-1.Sample.Basic.test Verify calculation script September 18, 2014 6:00:23 AM BST Succeeded
EssbaseCluster-1.Sample.Basic.test Execute calculation script September 18, 2014 6:00:35 AM BST Succeeded

Thanks,
~KKT~

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s