CDF: JExport and Dynamic Members

One of the most frequently asked questions by people who use the JExport function is “How do I output the dynamically calculated members along with the stored members in a cube?” By default, the Essbase calculator will skip over the dynamically calculated members for performance reasons. It is only until you explicitly make the calculator evaluate a dynamic calculation that it will include dynamic calculations in its output.

There are 2 ways to make this happen. For both solutions, it is required that the formula be based upon a DENSE member. In my examples to follow, the formula is for the member “Jan” which is from the Time dimension in the Sample|Basic cube. The Time dimension is a dense dimension.

The first way to get all dynamic members to be output by the JExport function is to include an evaluation statement in the script that uses a dynamic member in the evaluation. In the example script, the evaluation statement is “IF(“Variance” >=0 OR “Variance” <= 0)”. The member “Variance” is a dynamically calculated member. Note: The IF statement has been purposefully written so that it always evaluates to True.

The second way to get all dynamic members to be output by the JExport function is similar to the first but instead of introducing an evaluation statement into the script you include a dynamic member as an output member in the JExport function. In this example, the “Variance” member is being output along with the “Actual” and “Budget” members.

Remember, extracting dynamic members using JExport will degrade the performance of your script since the calculator has to perform the calculations before it can output the data.

Custom Defined Functions

I am a huge fan of the Essbase Custom Defined Function (CDF). My colleague Toufic Wakim and I have built a library of CDFs over the years to do a variety of things that extend the capabilities of Essbase. Probably the most famous CDF we created is JExport. JExport is a function that uses the powerful Essbase calculator to extract data from Essbase into a file or SQL database. The main benefits of JExport are that it is very fast and allows for conditional extracts.

Anyway, I will be blogging about the various functions within the CDF library for the next few months. By doing so, I am hoping that those new to Essbase will discover this powerful extensibility feature of Essbase and those veteran Essbase designers will get new inspiration for how to use Essbase to solve new business problems.

PAGELESS CUBES

Did you know that it is possible to create a cube that is completely real-time? Where there is NO data stored in the cube or any other cube for that matter. How can this be, you ask yourself? It can’t be through partitioning because there is not any data stored in any cube. It can’t be through the use of an XREF function for the same reason. The answer is through the use of dynamically calculated members and customer defined functions (CDF).

I have always wanted to build my own personal Essbase stock market database that would give me investment information. I wanted it to use freely available market information and to have real-time stock prices. I found my source of this data on Yahoo Finance. You pass in a URL with parameters to Yahoo and it returns comma separated data.

My plan was to create a simple CDF that would generate the Yahoo URL and insert the necessary parameters. This would return the stock price for any stock symbol in real-time. I would use this CDF function in the calculation of a dynamically calculated member. When the dynamically calculated member is queried, it launches the URL and retrieves the data just as if it existed in the cube.

I was able to achieve my dream cube and it works great. This technique of using a CDF within a dynamically calculated member works for other data sources as well. I have CDF functions that retrieve data from a flat file, SQL database and an in-memory variable.

One caveat to this solution: the query performance is not sub-second. But for me, I can live with slower performance to have my dream cube.