cubeSavvy – Essbase web grids

Many people have been interested in my Google Sheets add-on experiment. Invariably, however, they have commented that they just can’t get their IT department to buy into the Google Sheets requirement to open their Provider Services URL to the web at large. Not one to beat my head against an immovable object needlessly, I’ve started working on the original cubeSavvy, which operates completely within the corporate firewall.

cubeSavvy was originally released a couple of years ago. I halted development on it soon after, however, because I wasn’t happy with the state of web tables at the time. Besides Google Sheets there was nothing remotely usable for displaying Essbase data for editing. In the interim though, great strides have been made, so I’m going to give it another shot.

This updated version of cubeSavvy relies on a tried and true Essbase technology to specify how to render the web-based grid: report scripts.  Report scripts provide extensive features for selecting members, formatting data, etc. Since all Essbase professionals have experience with creating report scripts, using them seemed to be a logical choice. Grids are not currently stored in a separate database or file system. Rather grids created from the report scripts of the Essbase applications/cubes specified in the configuration file are presented.

Report Script

Here is a sample report script used to create a cubeSavvy grid:

//ESS_LOCALE English_UnitedStates.Latin1@Binary
//calc=subs
{ DECIMAL 2 }
{ COMMAS }
<PAGE ("Measures")
<IDESC "Profit"
<COLUMN ("Scenario", "Year")
"Actual" "Budget"
<CHILD "Qtr1"
<ROW("Product", "Market")
<IDESC "100"
<IDESC "200"
"Product"
<IDESC "South"
"Market"
!

Report script notes:

1. The first thing to note is that this a a completely valid report script. You can run this directly from EAS, MaxL, or anywhere else you’d normally execute a report script.
2. Let me draw your attention to the second line: //calc=subs. This connects the “subs” calc script to the cubeSavvy grid, meaning that it will be run when data on the grid is updated and saved. It has to be commented out in order for the report script to validate in EAS.
3. All sections (Page, Column, and Row) are specified. This is a best practice anyway, but is required for the grid to render properly.
4. All member names are surrounded by double quotes (“). This is also a best practice, since  member names with spaces require double quotes. cubeSavvy identifies member names in the report script as text within double quotes.

Calc Script

Below is the “subs” calc script specified to run in the above report script:

//ESS_LOCALE English_UnitedStates.Latin1@Binary
SET UPDATECALC OFF;
SET AGGMISSG ON;
SET EMPTYMEMBERSETS ON;

SET RUNTIMESUBVARS {
Measures = "Misc"; 
Year = "Qtr2";
Market = "South";
Product = "300","200";
Scenario = "Actual";
};

/*FIX on the Page member passed from grid*/
FIX(@LIST(&Measures))
  /*FIX on Column members from report script*/
  FIX(@LIST(&Scenario),@CHILD(&Year))
    /*FIX on the first Row dimension */
    FIX(@ILDESC(@LIST(&Product)),@ILANC(@LIST(&Product)));
      @ILDESCENDANTS(@LIST(&Market));
      @LANCESTORS(@LIST(&Market));
    ENDFIX
    /*FIX on the second Row dimension */
    FIX(@ILDESC(@LIST(&Market)),@LANC(@LIST(&Market)))
      @LDESCENDANTS(@LIST(&Product)); 
      @ILANCESTORS(@LIST(&Product)); 
    ENDFIX
  ENDFIX
ENDFIX

Calc script notes:

• First, and most obvious, is the use of RUNTIMESUBVARS. This feature was added as of 11.1.2.3, released in April of 2013. If you are using an older release, this functionality will not work. In this case, I really hope you are at least in the planning stages of an upgrade. You can still, however, attach a regular calc script (without RUNTIMESUBVARS) to the grid to be run on save.
• The variables within the SET RTSV are named exactly the same as the dimension names. This is a requirement. Any member selections for the dimension (<IDESC “100” and <IDESC “200” for Product, for example) will be passed to the RTSV variable of the same name (“100” and “200” for &Product).
• Notice the use of @LIST. This is necessary because more than one member can be specified in the report script member selection (see Product, as mentioned above). @CHILD(&Year) is used below without @LIST because <CHILD “Qtr1” is specified in the report script, so only one member, “Qtr1”, is passed to this calc script. This is fine as long as it’s known in advance that “Qtr2”, for example, will never be added to the member selection.
• Since @LIST is used, the normal @DESCENDANTS and @ANCESTORS will not work. Luckily, the “@L” variants (@LDESCENDANTS, @LANCESTORS) were created for just such a requirement.
• Row, Column, AND Page members are passed to the calc script! You can do things like super-focused aggregation (as in the calc above) in Essbase that were previously only available in other tools. You’re not limited to aggregations either. You can do anything with the members passed from the grid that you’d normally do in a calc script. Let your imagination run wild!

Installation and Running

Follow these steps to install and run cubeSavvy:

1. Download cubeSavvy.jar.
2. Just double-click on cubeSavvy.jar to launch the installer, which will prompt you for the install location and Essbase details.
3. After installing, go to the directory you selected for the installation. You’ll see a cubeSavvy folder with two files: start.sh and start.bat.
4. For Windows, double-click on the start.bat file. For Mac/Linux/UNIX, run ./start.sh
5. If you are running the install on a local machine, you can point your web browser to http://localhost:9001/cubeSavvy. If you installed on a server, you’ll need to substitute “localhost” for the server’s IP or DNS name.