Kim Kardashian can get my Essbase server updates

I had the great pleasure of presenting at Kscope17 on the power of Essbase CDFs.  At the end of my CDF presentation this year, I gave a live demonstration of a little CDF that is designed to spark the imagination.

In 2009, Matt Milella presented on CDFs at Kaleidoscope and talked about the top 5 CDFs that his team had created.  At the end, he showed a very cool demonstration of how his Essbase server could send out a tweet using a CDF. This was an amazing display and really inspired me to figure out how to create CDFs.

So, as an homage to Matt’s blog post about how Ashton Kutcher can get his Essbase server updates, I have created an updated version of the Twitter CDF. As Matt states, he used JTwitter back in 2009.  Unfortunately for me, Twitter has long since changed their authentication to use OAuth for security which means that JTwitter doesn’t work anymore.

I did some searching and found Twitter4J, an unofficial Java library for the Twitter API. This library handles the OAuth authentication as well as allows submitting of new status updates, sending direct messages, searching tweets, etc. Between Matt’s original Twitter code, the Twitter4J sample code, and some trial and error, I was able to get the library setup and created a Java class that could send my tweets.

1. The first step was to download the Twitter4J library.  I added the twitter4j-core-4.0.3.jar file into my lib folder in JDeveloper and added it to my classpath.
2. Next, I had to setup a new Twitter account (EssbaseServer2).
3. Then, I went to http://twitter.com/oauth_clients/new and setup my application to get the OAuth keys needed for my code to authenticate.
   
4. Once I gathered the keys, I put them into a .properties file called “EssbasTweet.properties”.  This file will be placed onto my Essbase server into the %EOH%/products/Essbase/EssbaseServer/java/udf directory.  Placing the file into the …/java/udf directory puts it into Essbase’s Java classpath and Essbase will be able to access the file when its needed.
   
5. Next, I wrote my code (based heavily on Twitter4J’s sample code), compiled it, deployed the code to a JAR and placed the JAR on the Essbase server.
   
6. I registered the CDF manually in EAS.
   
7. I was able to pretty much reuse Matt’s original calc script as he had it back in 2009 with the exception of using an @CalcMgr function instead of one of the older data functions.

Does it work? Well, go and check out the @EssbaseServer2 account for yourself.

While publicly tweeting your data might not be the best idea, hopefully this serves as a spark to ignite your imagination of the power of CDFs. Anything you can do in Java can be implemented in an Essbase calculation. Some attendees of my presentation were pretty excited about the possibilities of communicating with their users by submitting messages using Slack or updating a status on a SharePoint site. The possibilities are limited only by your imagination.

Thanks again to Matt for presenting on CDFs eight years ago. It definitely inspired me to learn more and hopefully this will inspire others to do the same.

There has been some uncertainty about the fate of CDFs with OAC and the Essbase cloud service, but never fear, CDFs are supported but they are limited to local CDFs. More on that in the future.

Calc Manager 11.1.2.4.010 Issue

I applied the Calc Manager 11.1.2.4.010 patch to a sandbox VM in anticipation of my upcoming Kscope presentation “Essbase CDFs Demystifyied.” As I was working on my CDF demos for this presentation, I found that the @CalcMgrMDXExport CDF was having an issue as my Essbase application started up:

[Thu Jun 15 12:45:49 2017]Local/Samp2///8632/Warning(1200490)
Wrong java method specification [com.hyperion.calcmgr.common.cdf.MDXExport.exportData(String,String,String,String,String,String,String,String,String,String,String,String,String,String,String)] (function [@CalcMgrMDXExport]): [specified method not found:com.hyperion.calcmgr.common.cdf.MDXExport::exportData(java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String)]

This error is saying that the exportData method of the MDXExport class with 15 String input parameters is not valid. I peeked at the code and found that in 11.1.2.4.010, the exportData method is now looking for 19 String variables. It sounds like this was not planned, so we can look forward to a new @CalcMgrMDXExport-like CDF in the near future.

If you have already applied the Calc Manager 11.1.2.4.010 patch, you can apply a quick fix by changing the CDF registration and editing your calculation scripts to include four additional null strings at the end of your @CalcMgrMDXExport calls.

To fix the issue, you can run the following MaxL statement to register the CDF with the appropriate number of parameters:

create or replace function ‘@CalcMgrMDXExport’ as ‘com.hyperion.calcmgr.common.cdf.MDXExport.exportData(String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String,String)’ spec ‘@CalcMgrMDXExport(key,usr,pwd,file,app,db,svr,colAxs,rowAxs,sep,msg,Unique,Alias,supZero,rowHdrs,whereMDX,srcMap,tgtMap,supColH)’ with property runtime

I had to resort to shorthand on the “spec” field because Essbase only allows 128 bytes in that field if you register the CDFs through MaxL or EAS. I believe there may be more leeway for longer fields if you use the Java API to register CDFs.

After running the MaxL to register the CDF and restarting my application, it looks like all is well with the world:

[Thu Jun 15 15:47:08 2017]Local/Samp2///1960/Info(1200445)
External [GLOBAL] function [@CalcMgrMDXExport] registered OK

The additional fields needed for the @CalcMgrMDXExport method in this version of Calc Manager are as follows (in order):

• String wheremdx
• String srcMappings
• String targetMappings
• String supressColHeaders

The wheremdx field, if used, allows me to filter my results coming back from the source application. This field is optional and can be left null.

The srcMappings and targetMappings fields, if used, allow mapping members from source to target. This would allow me to map account 1234 to 4567 on the export by providing “1234” for the srcMappings field and “4567” in the targetMappings field. This field is also optional and can be left null.

The supressColHeaders accepts a string “true” or “yes” to suppress the column headers. Any other value (including null) will result in the output file containing the headers.

I have submitted an SR and expect a bug to be filed in the next few days. I’ll submit a new post once we have an updated Calc Manager patch that fixes this issue and includes a new @CalcMgr* CDF.

FDMEE and Essbase ASO clears

Last month we covered FDMEE and Essbase (BSO) calculations. This month, let’s take a look at FDMEE integration with ASO. With BSO, we set up calculation scripts to do a clear before the data load and an aggregation after the load. With ASO, there are no calculation scripts so we can’t use the same functionality.

Partial clears in ASO can be done using MaxL, the Essbase scripting language. Those of you familiar with Essbase already know, an aggregation is not needed after the a data load to ASO as those cubes dynamically aggregate.

There are several ways to accomplish these clears, most of them revolve around using Event Scripts in FDMEE. Event Scripts are written in Jython or Visual Basic. Jython is an implementation of the Python programming language, designed to run on the Java platform. Jython is a more powerful language than VBA and it’s fairly easy to learn and work with, so that is what I use when writing Event Scripts.

We have many intervals in the FDMEE integration process where we can insert custom code into an Event Script. Each FDMEE process has a before and after script that can be customized. Since we want data to remain in our Essbase ASO application for as long as possible, we will use the BefLoad script to run our clear process.

It’s possible to call a batch file that will execute your MaxL script to run the clear, but I like to call MaxL directly from Jython. This method requires that the Essbase Client is installed on your FDMEE server so that the startMaxl.cmd script is available. Of course, we should be using encrypted MaxL scripts so that our passwords are not visible in either the Jython BefLoad.py script or in our MaxL script.

In this hypothetical situation, our Club Electronics stores in Delaware have submitted their ATM Sales. Lets say that Club Electronics submits a new file each day to update our ASOSamp application with the month-to-date sales numbers. To make sure that we are loading the correct data each time, we need to clear the existing Delaware ATM sales for Club Electronics for the current month and current year.

This scenario could be accomplished by hard coding values in for Delaware in MaxL, but we have other states that submit similar files using different locations in FDMEE. In order to make our clear script usable by multiple stores and entities, we can pass variables using Jython to MaxL to dynamically clear portions of our ASO cube based on the location (or data load rule, or integration option set in FDMEE, or many other variables).

So, let’s begin in FDMEE with our file integration using ASOSamp as our Target Application. I have already set up ASOSamp as a Target Application, created our Import Format for a comma delimited file, created our Location for Club Electronics ATM Sales (CE_ATM_Sales), and created the Data Load Rule to load this data.

MaxL Script

Our MaxL script accepts three parameters: Geography, Month, and Stores. We have our MaxL encrypted so that no passwords are stored in plain text. The trick to getting this to work, I have found, is using double quotes around the MDX expression in the MaxL statement. This allows MaxL to properly evaluate the variables. You can hard code some or all of the MDX tuple, I did a little of both here.

Jython BefLoad.py Script

In the BefLoad script, we need to test and make sure that which FDMEE Location is being loaded to ensure we are running the proper code. This can test can also be done at the Load Rule level, if you have multiple rules in the same location. Next, the script calls startMaxl.cmd which is installed as part of the Essbase Client installation and passes the variables to the MaxL script.

Passing Variables

The trick to getting all of this to work is the ability to pass variables; either dynamic variables that come from FDMEE (Location name, POV month, etc.) or static variables that we have coded into the Jython script. In the example above, I show how to pass a variable with a space from Jython to MaxL. By escaping the double quote (“) with a backslash (\), we are able to pass the variable from Jython to the Windows Command prompt surrounded in double quotes (“).  Without the escape character, the variable will not get passed correctly.

Logs

In our FDMEE process logs, we can see that the code is running properly thanks to the fdmAPI.logInfo lines we added to the BefLoad script:

2016-07-22 17:53:11,871 INFO [AIF]: Executing the following script: C:\FDMEE/data/scripts/event/BefLoad.py
2016-07-22 17:53:11,923 INFO [AIF]: ======================================================================
2016-07-22 17:53:11,923 INFO [AIF]: BefLoad Script: Begin
2016-07-22 17:53:11,923 INFO [AIF]: ======================================================================
2016-07-22 17:53:11,923 INFO [AIF]: ======================================================================
2016-07-22 17:53:11,923 INFO [AIF]: Submitting MaxL to selectively clear ASOSamp
2016-07-22 17:53:11,923 INFO [AIF]: ======================================================================
2016-07-22 17:53:13,141 INFO [AIF]: ======================================================================
2016-07-22 17:53:13,141 INFO [AIF]: MaxL commands to ASOSamp were successful
2016-07-22 17:53:13,141 INFO [AIF]: ======================================================================
2016-07-22 17:53:13,141 INFO [AIF]: ======================================================================
2016-07-22 17:53:13,141 INFO [AIF]: BefLoad Script: END
2016-07-22 17:53:13,141 INFO [AIF]: ======================================================================
2016-07-22 17:53:14,825 INFO [AIF]: EssbaseService.loadData - START

In Essbase, we can also verify that the MaxL code is executing properly by checking the ASOSamp application log:

[Fri Jul 22 17:53:12 2016]Local/ASOsamp///6600/Info(1013210)
User [admin@Native Directory] set active on database [Sample]

[Fri Jul 22 17:53:12 2016]Local/ASOsamp///6544/Info(1042059)
Connected from [::1]

[Fri Jul 22 17:53:12 2016]Local/ASOsamp/Sample/admin@Native Directory/6544/Info(1013091)
Received Command [AsoClearRegion] from user [admin@Native Directory]

[Fri Jul 22 17:53:13 2016]Local/ASOsamp/Sample/admin@Native Directory/6544/Info(1270602)
Removed [25] cells from input view. Partial Data Clear Elapsed Time [0.258334] sec

[Fri Jul 22 17:53:13 2016]Local/ASOsamp/Sample/admin@Native Directory/6544/Info(1013273)
Database ASOSamp.Sample altered

[Fri Jul 22 17:53:13 2016]Local/ASOsamp///7080/Info(1013214)
Clear Active on User [admin@Native Directory] Instance [1]

With the ability to call MaxL directly as part of FDMEE scripts, your integration is only limited by your imagination. To take this post another step further, you might decide to update substitution variables in Essbase based on the FDMEE POV that is being loaded or maybe even build aggregate views using MaxL in the AftLoad.py script without much additional effort.

CDF Series Part 3: Creating CDFs

If the prebuilt CDFs from Oracle that we discussed last time don’t meet your needs, it is also possible to create your own Custom Defined Function. Using a Java integrated development environment (IDE) is the simplest method for beginners, although it is possible to create a JAR using nothing more than a text editor and command line interface with Java.  There are several IDEs available for Java; however, I prefer to use Oracle’s JDeveloper as it makes doing this work very easy.

JDeveloper

JDeveloper makes Java development and deployment to JARs very easy. I am currently using JDeveloper 11.1.1.9 because it is compatible with both Java 1.6 and 1.7.  The EPM System 11.1.2.4 is shipped with Java 1.6, so using some version of Java 6 will prevent any errors due to incompatible code.

The software can be downloaded from the Oracle Technology Network. For just programming Essbase CDFs, select the Oracle JDeveloper Java Edition download. Unzip the downloaded files to the C:\ drive for simplicity, and it will create a C:\jdeveloper folder. At the first startup, point JDeveloper to a JDK installation.  The Oracle EPM JDK can be used at C:\Oracle\Middleware\jdk160_35 to ensure compatibility.  Any Java 6 JDK could be used and later versions are recommended due to security vulnerabilities in the lower Java 6 releases.

Next we need to create a generic Java project and add a new Java Class.  Here we will enter our code.  When writing a Java CDF that may be called using the RUNJAVA method, we need to add in an additional parameter to the “main” method.  Typically in Java programming the main method only accepts a String array (String[]) called “args”.  With Custom Defined Functions, we must accept a calculator context parameter as well as our String[] args. The main method description will appear similar to:

public static void main(com.hyperion.essbase.calculator.Context ctx, String[] args) {
   doSomething();
}

By adding the calculator context to our Java Class, JDeveloper will give errors as it is unable to find the definition for the context object. In order to properly compile our Java Class into a JAR, we must first make JDeveloper aware of the context object that is part of the Essbase server in a file called Essbase.jar.  To add Essbase.jar into the Classpath for our code, right-click the project in JDeveloper and select Project Properties… as shown below:

Next, navigate to the Libraries and Classpath option of the Project Properties and click the Add JAR Directory button.  Browse to the Essbase.jar file and click Select.  If done properly, your screen will be updated showing the Essbase.jar file as a Classpath entry as shown here:

To deploy our Class to a JAR, we must first create a deployment profile in JDeveloper.  To create a deployment profile, right-click the project, select Deploy >> New Deployment Profile… as shown below:

In the New Gallery dialog box, select Deployment Profiles then select JAR File in the right pane like this:

In the Create Deployment Profile – JAR File dialog box, enter a name for the Deployment Profile.  This will become the name of your JAR file.

In the JAR Options dialog box, select the checkbox to Include Manifest File (META-INF/MANIFEST.MF) and browse to select the main class.

Next, to deploy our Class to a JAR file, simply right-click on the project again and navigate to Deploy >> ProfileName, then click the Finish button to deploy the Class to a JAR file.

Now that we have our JAR file, the next steps are to copy the JAR to our Essbase server and register the function in EAS or by using MAXL.

After restarting Essbase, we should then be able to use our new code in an Essbase calculation script:

SET UPDATECALC OFF;
"Budget" = @CREATEBLOCK("New York"->@RELATIVE("Product",0));
FIX ("New York", "Sales", "Budget",@RELATIVE("Product",0))
"Jan"(
  @INT(@Random()*100)+1;
)
ENDFIX
AGG (Product, Market);

 

CDF Tips for Java Developers

Custom Defined Functions should be programmed using static methods in Java.  These classes typically are not be overridden and are used as utility classes, so using static methods is the best practice.

CDF methods can accept any number of parameters. These parameters may use any of the following data types:

• boolean
• byte
• char
• hyperion.essbase.calculator.CalcBoolean
• float, double
• lang.String
• short, int, long
• arrays of any of these types

The CalcBoolean object is defined in the Essbase.jar file and is equivalent to the Essbase calculator’s Boolean function and has values of TRUE, FALSE, or #Missing.

Any return values to Essbase can be void, or any of the above data types. Returned data is mapped to an Essbase-specific data type.  String return values are mapped to a String. Boolean return values are mapped to the CalcBoolean data type.  Any other return value is mapped to a double data type.

CDFs must start with the @ symbol, the rest of the name can contain letters, numbers, or the following symbols: @, #, $, and _.  CDF names must not include spaces.

If multiple users can run the same CDF at a time, the variables can be shared and overwritten.  For thread safety, I have used the synchronized attribute on methods in Java with success.

Any passwords or other properties that may need changed can be stored in a *.properties file in the %EPM_ORACLE_HOME%\products\Essbase\EssbaseServer\java\udf directory for global or server-wide functions or they can be placed in the %EPM_ORACLE_INSTANCE%\EssbaseServer\essbaseserver1\app\ApplicationName directory.  Your methods can then use the *.properties file as a resource and read the values at runtime.  This is a better method than saving passwords directly in your Java code.

Custom Defined Functions open Essbase calculations up to a world of possibilities. There are several prebuilt options from Oracle that may meet any needs outside the traditional Essbase calculator engine. If the existing prebuilt code does not solve your specific need, custom code can easily be implemented to solve almost any calculation need in Essbase.

CDF Series Part 2: Prebuilt CDFs

Oracle has given Essbase administrators several different prebuilt CDFs that can be used anytime they are needed. There are three main locations for prebuilt CDFs: Oracle’s Sample Code site, Calculation Manager, and Hyperion Planning.

Oracle Sample Code

Located at (http://www.oracle.com/technetwork/indexes/samplecode/essbase-sample-522117.html), Oracle’s Sample Code site offers eight different downloads containing several CDFs.  These downloads contain precompiled JAR files, the necessary MaxL statements to register the CDFs, and usually a sample calculation to show how to use the CDFs.  The eight downloads are:

• Financial Functions: Calculates loan maturity, principal and interest payments, and interest rates
• Export Functions: The main function used in this CDF was replaced by the DATAEXPORT Essbase calculator function
• Email Functions: Sends emails during calculations to alert administrators or users
• Date Functions: Includes various functions to work with dates, some are epoch date format and some are YYYYMMDD format
• Data Functions: Allows retrieval of external data from files or the Internet
• Counter Functions: Counts the number of passes through an outline during a calculation for tuning purposes
• Logging Functions: Logs messages to a specified file, replaced by @CalcMgrLog and @CalcMgrLogText functions
• String Functions: Manipulates text fields, many of these functions are also available in @CalcMgr* text functions

Calculation Manager CDFs

Beginning with Calculation Manager 11.1.2.3.502 and up, many prebuilt Custom Defined Functions are automatically deployed to the Essbase server and ready for use. Version 11.1.2.4 of Calculation Manager includes 67 prebuilt functions.  Many of these functions are for text and date manipulation.  The date functions are primarily for use in Capital Expenditure and Workforce Planning applications using the YYYYMMDD date format, but can be used in any application that stores dates using that format.

In addition to the text and date functions, there are six miscellaneous functions that can be useful in a variety of situations.  The six miscellaneous functions are:

• @CalcMgrExecuteEncryptMaxLFile – allows the execution of an encrypted MaxL file.
• @CalcMgrExecuteMaxLScript – streams MaxL code to the Essbase kernel for immediate execution
• @CalcMgrLog – writes messages to a specified text file, can include data from Essbase for validations
• @CalcMgrLogText – writes text-only messages to a specified text file
• @CalcMgrMDXDataCopy – using MDX queries, this function can select a range of data blocks in one application and copy the data to a target application
• @CalcMgrMDXExport – using MDX queries, this function exports data to a file quickly without the ugly formatting of a traditional MDX export

Some of these functions are not properly registered after applying the patch.  The errors can be fixed by editing the essfunc.xml file in the %EPM_ORACLE_HOME%\EssbaseServer\essbaseserver1\java directory. The following edits need to be made to have all of the CDFs registered properly:

• @CalcMgrGetCurrentDate
  • Change getCurrentDate(int) to getCurrentDate()
• @CalcMgrIndexOf
  • Change indexOf(String,int) to indexOf(String,String,int)
  • Change @CalcMgrIndexOf(text, beginIndex) to @CalcMgrIndexOf(text, searchString, beginIndex)
• @CalcMgrLastIndexOf
  • Change lastIndexOf(String,int) to indexOf(String,String,int)
  • Change @CalcMgrLastIndexOf(text, beginIndex) to @CalcMgrLastIndexOf(text, searchString, beginIndex)
• @CalcMgrSubstring
  • Change substring2(String,int,int) to substring(String,int,int)
  • Change @CalcMgrSubstring2(text, startIndex, endIndex) to @CalcMgrSubstring(text, startIndex, endIndex)
• @CalcMgrMDXExport
  • Change hyperion.calcmgr.common.cdf to com.hyperion.calcmgr.common.cdf.MDXExport

Hyperion Planning CDFs

Hyperion Planning also comes with several prebuilt CDFs.  In version 11.1.2.4, Planning contains 23 CDFs available for use in both Calculation Manager rules and in Essbase calculation scripts. When attempting to use these rules in Calculation Manager, the parameter definitions are not descriptive, as shown in the picture below:

To get a better feel for the parameters for each @Hsp* function, view their definitions in Essbase Administration services as shown below:

Next time we will cover creating our own CDFs from scratch.

Introduction to CDFs

I attended a Matt Milella presentation several years ago about CDFs and always wanted to use one.  A couple of years ago, I was in a bind at a client who needed very specific to an Essbase extract for integration with SAP, and I actually had a chance to write one from scratch.  It’s not very often that you are faced with such a problem, but it’s great to know that Oracle has given us the tools to handle almost any situation.

Introduction to Custom Defined Functions

The Essbase calculator engine is use in Block Storage Option (BSO) and hybrid aggregation applications. There are 160 built-in calculator functions. BSO databases are known for their superior calculation ability due to these functions; however, occasionally there is a need for additional functionality not covered by the calculator engine or built-in functions. In these instances, Custom Defined Functions can be used to extend the calculator engine.

Custom Defined Functions (CDFs) are Java classes written to interact with the Essbase calculator. CDFs allow data, metadata, or both to be passed from the calculator engine into Java.  Data or metadata can then be returned to the calculator engine. CDFs harness the power of Java, so they are able to call any outside process.

CDFs open a world of possibilities through the use of Java.  They can be used to: submit MaxL commands dynamically from within a calculation, interact with the server operating system, do complex calculations not available with the existing calculator functions, or even fetch data from the Internet. These functions may be deployed at the server-level and available for use by all applications, or at the individual application level.

Java code is compiled into a Class file. Classes must then be packaged into a Java Archive file, also known as a JAR. The JAR file is then placed on the Essbase server in the %EPM_ORACLE_HOME%\products\Essbase\EssbaseServer\java\udf directory for global or server-wide functions or they can be placed in the %EPM_ORACLE_INSTANCE%\EssbaseServer\essbaseserver1\app\ApplicationName directory for an application-level CDF.  When using global CDFs, the udf.policy in the %EPM_ORACLE_HOME%\products\Essbase\EssbaseServer\java directory must be updated to allow Essbase access to the custom code.  A line can be added to the end of the udf.policy file as follows:

grant codeBase "file:${essbase.java.home}/../java/udf/yourCDF.jar" {   
   permission java.security.AllPermission;
};

Next, the CDF must be registered with Essbase.  This can be done in Essbase Administration Services (EAS) by selecting Edit >> Functions on your Essbase server, or within MaxL. To register the @JsendMail function, the MaxL code would look like:

create or replace function '@JechoString' as 
'com.oracle.essbase.cdf.StringFunctions.echoString(String[])' 
spec '@JechoString(strArray)' 
comment 'Echoes back all arguments passed to the function.  To pass an array of arguments use @List(comma delimited list)'

Essbase picks up new Custom Defined Functions on startup.  When creating new global CDFs, the Essbase service must be stopped and restarted to recognize the new function.  When creating application specific CDFs, restarting the application is sufficient. When developing new CDFs, it is recommended to begin with application level functions during the initial testing as it is easier to stop and restart at the application level than restart the Essbase service.

As the application starts, messages are written to the application’s Essbase log about the functions. When creating new functions, check the Essbase application log to verify that the function is registered successfully. Any messages other than a successful registration indicate changes are needed to the CDFs registration in EAS. When a function is registered properly the following message is displayed in the log:

[Wed May 20 10:14:34 2015]Local/Sample///10636/Info(1200445) 
External [GLOBAL] function [@JechoString] registered OK

Once defined properly and registered with Essbase, Custom Defined Functions may typically be run in two ways.  The first method of running a CDF is similar to a typical Essbase calculator function.  When running a CDF using this method, it must be placed inside a calculation member block.  This option calls the Java methods directly from inside the Class file. If the CDF definition was setup “with property runtime” in MaxL, or with the “Runtime” box checked in EAS, the CDF will execute for all members of the calculation.  The amount of blocks impacted by the CDF can be limited with a FIX statement. A typical calculation script using the first option for running a CDF is:

FIX ("New York", "Sales", "Budget",@RELATIVE("Product",0))
"Jan"(
    @INT(@Random()*100)+1;
)
ENDFIX

The second option to run a CDF is using the RUNJAVA command.  This method of running a CDF executes only once and calls the “main” method of a Java class.  The Custom Defined Function must be written properly to accept this method of calling it. Using the RUNJAVA command does not require that a CDF be registered in EAS or by MaxL. A typical RUNJAVA command is:

RUNJAVA com.hyperion.calcmgr.common.cdf.MaxLFunctions
  true -D C:\\Scripts\\BatchCalc.mxl
  678870187,2425911017
  050927985109683855112314385651
  7592008210957166529169707423014313885401;

That’s all for this week, next time we will talk about prebuilt CDFs from Oracle that we can use right away. Later, we will cover how to write your own CDF (assuming some level of Java coding experience)/

Running Essbase 11.1.2.x in the foreground

For many years, the conventional wisdom was that if your Essbase server was failing to start, you should run it in the foreground to see any messages that might be displayed to the console, but not logged into the Essbase.log file. With the pre-11.1.2.x versions, this was usually quite simple. In fact, if you had your environment variables set right, all you needed to do is type in ‘essbase’ at a command prompt regardless of what directory and your Essbase server would start before your eyes.

According to the Essbase Database Administrator’s Guide (dbag), it states that the ability to run Essbase in the foreground is no longer supported as of version 11.1.2.2.100. With the 11.1.2.x versions and their more complicated directory structure (some static files stored in …Oracle/Middleware/EPMSystem11R1/products…. and some files stored in …Oracle/Middleware/user_projects/epmsystem1/…); starting Essbase in the foreground can be challenging if you don’t use this simple hack.

If we open a command prompt and navigate to the …/Oracle/Middleware/EPMSystem11R1/products/Essbase/EssbaseServer/bin directory and type in ‘essbase’, you will likely see a message about an improper ESSBASEPATH, or maybe a message about the Locale.

In later versions, Essbase needs to reference files from different folders.  We have two options, we can either set up the proper environment variables or we can do what Oracle has done with their ESSCMD and ESSMSH executables.  I like to hijack the “startEsscmd.cmd” script that is installed with the Essbase Client in …/Oracle/Middleware/EPMSystem11R1/products/Essbase/EssbaseClient/bin directory.

Simply save a copy of the “startEsscmd.cmd” script as “startEssbase.cmd”.  Edit the new “startEssbase.cmd” script and change the “%ARBORPATH%\bin\ESSCMD.exe %*” line to “%ARBORPATH%\bin\ESSBASE.exe %*”. Then, save the startEssbase.cmd file and when you double-click it in Windows, Essbase will run in that window. Now you can use those old school Essbase commands in the foreground and rebuild the Essbase.sec file if absolutely necessary.

Of course, this is unsupported and with the advances in Essbase, it’s probably not even necessary since Essbase runs much better now than it did in the 9.3.x and early 11.1.1.x days.  The only time I have had to do this is when the Essbase.sec is corrupted beyond repair, but starting in version 11.1.2.2, I believe, Essbase tries to create a new Essbase.sec file automatically if it’s missing. It’s not very often that these steps are needed, but I wanted to document them just in case anyone is running on an old version out there. If you are on EPM version 11.1.2.2 or older, please consider an upgrade. I know a guy that can help with that.

Essbase fragmentation head-scratcher

With the release of Essbase 11.1.2.3.500, there was an interesting entry in the readme that caused some chatter on Network54.  The readme states:

The INPLACEDATAWRITE setting in essbase.cfg enables or disables in-place data writing.

Prior to this release, each time a data block was updated, it was written to a new disk location. With this release, for Exalytics, Essbase enables in-place data writing.

In-place data writing means that when updates occur, the data block can be written to the same location, as long as the compressed size of the data block fits in its original location on the disk.

In-place data writing can help reduce data fragmentation and lower the need for frequent restructuring of database. It also reduces the need for frequent index updates, resulting in improved performance.

Merely updating a single value of a cell in a single block causes that block to be written to the end of the page file? This made long-time developers scratch their heads because many of us believed that updating values in a block did not cause fragmentation. If submitting data causes fragmentation, imagine how bloated our Planning databases must be. No wonder we hear recommendations to restructure nightly during a planning cycle.

Glenn Schwartzberg said that yes, that’s how he understood it to work.  Edward Roske said there was no way submitting data cause fragmentation.  Cameron Lackpour didn’t believe it either until he did some testing and found that the ESSCMD GETDBSTATS command reported fragmentation after updating a single cell of Sample.Basic. Dan Pressman commented that he had seen the same behavior with compression turned off – BSO writes to the end of the .pag file when blocks are updated.

Test 1:

I tested this out on an 11.1.2.4.000, non-Exalytics sandbox. In Excel, I retrieved a single block of one of our sample cubes (not Sample.Basic, this was a larger demo cube). The page dimensions of my worksheet listed a single level-0 member from each Sparse dimension of the cube. Accounts were on my rows and January was my column dimension (both were level-0 Dense members).

With that sheet, I wrote a macro to do the following:

• Update each data cell of the grid to a randomly generated number
• Submit the data for the block back to Essbase
• Call a MaxL script to stop and restart the Essbase application to flush the cache
• Loop back to the top, iterating 250 times

While I ran the macro, I (im)patiently watched the database directory to see if the database’s essn.ind or essn.pag files would grow.  As we all know, if there is fragmentation that happens each time the block is submitted back to Essbase, the .pag file should see some growth – especially over 250 iterations. For complete transparency, this database is using the bitmap compression type that is the default for BSO applications.

After 250 iterations of changing the same block and stopping/restarting the application there was no essn.pag file growth. The GETDBSTATS results show the following:

• Average Clustering Ratio: 0.4431846
• Average Fragmentation Quotient: 0.8091218

That leads me to believe there is a little bit of fragmentation, so I cleared the cube and loaded in a level-0 extract of the data and ran a calculation.  After that, I ran GETDBSTATS again and found the following:

• Average Clustering Ratio: 0.5117247
• Average Fragmentation Quotient: 0.6745301

Test 2:

My next test was to do the same loop except I added in a quick aggregation step after the data was submitted. This time, as the macro ran, I could plainly see the essn.pag file growing every 5-6 loops of the code. The Average Clustering Ratio and Average Fragmentation Quotient also grew as the calculations ran.

What does all of this mean?

Conclusion:

My conclusion is that the documentation on INPLACEDATAWRITE is a little misleading. Essbase without the INPLACEDATAWRITE does NOT write a block to the end of the .pag file if a block is updated. Blocks may be updated all day long by submitting data to them without expanding your .pag files. Fragmentation is caused when a data block is brought into the calculator engine. This is where INPLACEDATAWRITE does its magic on Exalytics machines and allows those blocks to be compressed and returned to their original location in the .pag file, thus preventing fragmentation.

What about the Fragmentation Quotient? Well, in the DBAG, it mentions that the Fragmentation quotient can be high and not indicate a need to defragment since free space in the .pag is created in 8MB chunks. I observed this to be true during Test 2 where excess free space was added to the .pag file and the file would only grow after 5-6 loops of the submit data/calc/stop/start routine. My thought is that an Average Fragmentation Quotient less than 1 (<1%) is essentially no fragmentation at all. After a single calculation script I began to see that Average Fragmentation Quotient above 1 (1.884844). By nature Essbase has some degree of fragmentation all of the time as it’s .pag file is always a little bit bigger than the actual size of the data contained therein. As long as the .pag file isn’t growing and the Average Fragmentation Quotient is less than 1%, I’m counting that as no fragmentation by submitting data to a block.

EPM Compact Deployment issue in 11.1.2.4

A compact deployment in EPM is when there are multiple Java Web Applications deployed to a single WebLogic Managed Server called EPMServer0.  A compact deployment is part of Oracle’s standard deployment documentation for EPM; however, I do not recommend it for production systems.  The compact deployment is an option for development or sandbox environments as it reduces the memory (RAM) requirements for the EPM system.  That means that you can size a development environment smaller than your production environment if you use the compact deployment.  There are some trade-offs in going this route, so do your homework to make an educated decision before using compact deployment.

This is a fairly minor issue with a quick fix.  I found on my laptop installation of EPM that my icons were not displaying in Shared Services when attempting to assign filter access to a user.  My colleague, Kenneth Staudt, pointed me to a Knowledge Base article on Oracle’s support site that described the issue exactly.  The instructions on KB article 1612768.1 are for 11.1.2.3.003 and later; however, the folders are a little different in 11.1.2.4.

If you run into this issue, copy the “ui_themes” folder from …\Oracle\Middleware\user_projects\domains\EPMSystem\servers\EPMServer0\tmp\servers\EPMServer0\tmp\_WL_user\SHAREDSERVICES_11.1.2.0\nth7gv\war to …\Oracle\Middleware\user_projects\domains\EPMSystem\servers\EPMServer0\tmp\servers\EPMServer0\tmp\_WL_user\WORKSPACE_11.1.2.0\rj8acj\war.  After that, restart your EPMServer0 Managed Server and you should now have the icons in the Access Control screen.

Little known ESSCMDQ feature – FILLDB

Occasionally, we have a need to load up a database with some fake data. This could be a copy of a customer application or perhaps a sample application that we really want to load up with some random data for calculation testing.

As most people know, Oracle has a special version of ESSCMD known as ESSCMDQ downloadable on their site here. Oracle even has an 11.1.2.4 version posted for those of us playing with the latest version. As of right now, the only documentation available on ESSCMDQ relates to the ASO compaction process blogged about by several of my colleagues in the EPM space.

Edward Roske mentioned this command to me a couple of years ago when we were doing some load testing of a customer’s application, but the only thing I could find about it on Google was a single Network54 post from 2004. Thank you Ray Dhiilon, wherever you are for recording the only existing documentation on FILLDB until now. I recently saw that Jason Jones created a utility called Cube Data that fills random data into Essbase and it jogged my memory on this command. While it was fresh in my mind, I figured that I would share it.

Setting up ESSCMDQ

I downloaded the zip file for 64-bit ESSCMDQ version 11.1.2.4 from Oracle’s site. I copied the ESSCMDQ.exe file into my Essbase Server directory at C:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\bin. In the C:\Oracle\Middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\bin directory, I copied the startEsscmd.bat file to startEsscmdq.bat and edited the script to call ESSCMDQ.exe instead of ESSCMD.exe.

I launched ESSCMDQ by double-clicking on my .bat file. I am not a huge fan of ESSCMD, so I don’t know the login steps very well. I figured there are probably a lot of people like me that have invested time into learning MaxL, so don’t really use ESSCMD very frequently or ever.  The commands are pretty easy to make this work and I show how to run this in my command line output below.

FILLDB loads random data into level0 data blocks. There are three parameters, if no parameters are specified, ESSCMDQ will fill all cells of the database.

FILLDB [size density distance]

• size – the desired combined size of the essn.pag files in Megabytes (MB)
• density – how full your blocks will be as a percent
• distance – distance between blocks that get filled (determines the sparsity of the database)

FILLDB 1024 5 3 = Fill the database up to 1GB, 5% block density, fill every third block.

FILLDB = Fill the entire database, 100% block density, fill every block.

The distance parameter is interesting. The way it appears to work is that it takes the level0 list of your first sparse dimension and will fill the top level0 member, then skip the desired number of level0 members and fill in the next one.

I logged into my 11.1.2.4 sandbox environment and selected a Hybrid Aggregation database just to see if it would work. I am pleased to report that it does. That’s not terribly surprising since Hybrid Aggregation databases still use blocks and index and page files.  Combine that with the fact that FILLDB works on level0 cells and it’s really a no-brainer; nevertheless, I wanted to make sure.

Just to prove that it actually works, this cube was blank when I started and here are the statistics:

  

Obviously, this is a BSO command, as we are talking about filling blocks.  You can run it against an ASO application, but the prompt just goes away while ESSCMDQ tries to figure it out.  I let it run for a few minutes and then it came back with a 1042017 Essbase error.  So, BSO only for this command.

Also, while I’m blogging, I just wanted to put in a quick plug for the OAUG Hyperion SIG meeting this Sunday, April 12.  If you are going to be at COLLABORATE, please join me at the SIG meeting.  We have some great speakers lined up to cover what’s new in 11.1.2.4 as well as talk about the road map beyond 11.1.2.4.

 

EDIT: It’s probably worth mentioning that this is undocumented and unsupported, so don’t call Oracle Support if you are having issues. You are on your own, and of course this may be removed at any time as well. So use it if you can, if not, take a look at Jason Jones’s utilities.