A couple of enhancements have been made in the 5.2 version now available for download:

1. Now more than one calc script can be run on save. All COLUMN, ROW, and PAGE members are passed to each calc script, allowing for super-focused calculations/aggregations.
2. Grids can now display a Description, visible to all users. Html tags are supported, so it’s possible to link to other applications, files, and folders. Or you can just provide additional information, such as status, deadlines, or instructions.

A bug was also fixed that reset the Shared Services Groups selected in the Edit Grid screen below.

What functionality would you like to see next in cubeSavvy? Send me an email or leave a comment.

cubeSavvy – data update view for admins

This point version update includes bug fixes for displaying MDX grids. Previously there was an issue with MDX statements that didn’t contain a WITH statement.

For example, here’s some MDX that contains a WITH statement:

WITH
set [secScenario] as '{[Actual],[Budget]}'
set [secMeasures] as '{Descendants([Margin]),Descendants([Total Expenses])}'
set [secMarket] as '{Descendants([Central]),[Texas],[California],Descendants([East])}'
set [secProduct] as '{Descendants([100])}'set [secYear] as '{Descendants([Qtr1]),Descendants([Qtr2])}'

SELECT crossjoin([secProduct],[secYear]) PROPERTIES MEMBER_ALIAS ON COLUMNS,
crossjoin([secMeasures],[secMarket]) PROPERTIES [RussianNames] ON ROWS,
[secScenario] ON PAGES
FROM Sample_U.Basic

What can I say? I always use them, so it didn’t even occur to me that people would just want to create all their sets in the ROW or COLUMN axis. But now you can also write your MDX queries like so:

select crossjoin(Descendants([100]),{Descendants([Qtr1]),Descendants([Qtr2])}) PROPERTIES MEMBER_ALIAS ON COLUMNS,
crossjoin({Descendants([Margin]),Descendants([Total Expenses])},{Descendants([Central]),[Texas],[California],Descendants([East])}) PROPERTIES [RussianNames] ON ROWS,
{[Actual],[Budget]} ON PAGES
FROM Sample_U.Basic

I’d like to say, however, that typing out the query above was even more painful than trying to read it later. Ok, I’ll get off my MDX soapbox.

The one new, but very useful, feature is a User Data Updates view available to admin users:

cubeSavvy has long had the ability to pass ROW, COLUMN, and/or PAGE member selections from grids to a calc script using RUNTIMESUBVARS. Now admins can even see the exact member intersection that was updated, along with the old value and the new one. The user who performed the update and the date/timestamp are also provided. All fields are searchable and sortable. You can also select multiple rows and ctrl-c copy them to other programs, such as Excel.

Download, enjoy, and let me know what you think.

-Harry

Soon after releasing cubeSavvy, the very first comment asked why MDX queries weren’t available. Well, now they are. They are first-class citizens, along with report scripts. The reason they were not added at the outset was because, unlike report scripts, MDX queries are not stored on the Essbase server. Until grid storage was added in version 3.0, there was literally no place to put them.

I, personally, always choose MDX over report scripts. It’s the main reason I added the ability to export data from complex MDX queries to cubeSavvy Utilities. MDX is just more logical than report scripts, given my background with SQL before even learning Essbase.

So download cubeSavvy and get started writing MDX grids! To get you going, the following example MDX grid is included as a sample in cubeSavvy.

Sample MDX query used to create a cubeSavvy grid:

WITH
set [secProduct] as '{Descendants([100], Levels([Product],0))}'
set [secMarket] as '{Descendants([South])}'
set [secMeasures] as '{Descendants([Margin]),Descendants([Total Expenses])}'
set [Scen1] as '{[Actual]}'
set [Scen2] as '{[Budget]}'
set [secScenario] as '{[Scen1],[Scen2]}'
set [secYear] as '{Children([Qtr1])}'

SELECT non empty crossjoin([secScenario],[secYear]) properties [Long Names] ON COLUMNS,
nonemptyblock (crossjoin([secProduct],[secMarket])) PROPERTIES MEMBER_ALIAS ON ROWS,
[secMeasures] ON PAGES
FROM Sample.Basic

MDX Query notes:

1. Again, as with report scripts, the first thing to note is that this a completely valid mdx query. You can run this directly from EAS, MaxL, or anywhere else you’d normally execute an mdx query.
2. Dimension Properties. You can specify exactly what you want displayed on the grid. As  you can see above, selecting a specific alias table (Long Names) is straightforward – or you can just use MEMBER_ALIAS to get the default.
3. You can call your SETs whatever you want in the WITH statement. cubeSavvy will determine the dimensions they refer to and pass them to the calc script. The WITH statement is not a requirement, but I do think it makes MDX more readable.
4. Note the optimization keywords – non empty and nonemptyblock. These are more powerful than the limited commands (SUPMISSINGROWS and SPARSE) available in report scripts.

I’m introducing some major changes in this, the 3.0, version. Grids are no longer read directly from the Essbase application/database. This was too restrictive, both in terms of naming grids and grouping them by task, user, etc. Along the same lines, security is also needed to make sure only users who are authorized can access individual grids.

Adding a new grid or folder is as easy as clicking on an existing folder/grid. The applications and databases are read from the Essbase server, as are the calc scripts and Shared Services Groups.

As hinted at by the above screenshot, security to a grid can be added based on Shared Services groups. Only those groups that have been provisioned to the selected Essbase application/database combination are displayed. The other way to control grid access is through user provisioning.

Admin users will see the User administration screen below on the Home tab. Only users who have Essbase server access will be displayed in the dropdown, which is searchable. Just type in the first few characters of the user who you’d like to add. There are only two User Types: admin and user. Only admins can edit grids and users. All of the columns are searchable and sortable.

Clicking the Edit User Access button for a selected user presents the following screen:

The left panel displays the available grids to which the user doesn’t have access. The right panel shows the grids the selected user can access. Moving groups back and forth between the panels is as simple as clicking on them.

Ok, now that the administrative details have been covered, let’s finally take a look at some grids. Six sample grids are included for instructional purposes. As can be seen clearly in the second screenshot above, you are no longer restricted by the 8-character limitation for non-Unicode Essbase report scripts. You can use up to 50 characters, with spaces, special characters, etc., in your names. Go wild!

The “calculated columns” sample grid is presented below:

I haven’t been doing a lot of Planning development lately, but I’m pretty sure it can’t produce this type of butterfly layout. Please let me know in the comments if I’m wrong. Don’t forget that cubeSavvy is using Essbase report scripts as the metadata/data presentation language. And report scripts can do some pretty cool things, like adding calculated columns, calculated rows, and rollups that are not in the underlying cube. First, let me show you the report script behind the above grid (by the way, it’s taken almost verbatim from the techref report script samples; I just added a Page dropdown):

The above report script demonstrates how to add 3 calculated columns (Actual~Qtr1, Budget~Q1, and Var~Q1). Why is this interesting? Because in the Year dimension of Demo/Basic there are no dynamically-calculated members. So Qtr1 is being updated solely through the report script (you can see that no calc script is attached to run on save – although this is still of course possible).

You can also use native report script functionality to create groupings that do not exist in the outline, as shown below in another sample grid:

Not only are Northern Cities and Southern Cities grouped, but the totals for each are also displayed at the bottom. These are done on-the-fly when the Save Changes button is clicked. Again, this is taken from the techref samples, 15-B. To quote chapter and verse: “This sample report is a simple summary of information in a North/South grouping, which is not part of the database outline. When relationships that you need for reporting are missing in the database outline, often the best solution is to use calculated rows (or columns).”

You can download the latest version in the usual spot. Any feedback would be greatly appreciated.

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.