Planning Calc Manager Rules - performance monitor

Sometimes I get comments from users that "Planning performance seems slow". The first thing I do in such instances is run the SQL code below to determine if there's an issue with Calc Manager rules running too long. Both the Oracle and SQL Server versions below calculate the "average time in seconds" (avg_time_seconds) and the "maximum time in seconds" (max_time_seconds) for each Calc Manager rule, producing a table similar to the following:

job_name	num_times_run	avg_time_seconds	max_time_seconds
AllocateBudget	45	25	50
AllocateForecast	15	28	32

Oracle:

select job_name, count(job_name) as num_times_run, round(avg(abs(extract(second from elapsed) + extract(minute from elapsed)*60 + extract(hour from elapsed)*60*60 + extract(day from elapsed)*24*60*60)),0) as avg_time_seconds, round(max(abs(extract(second from elapsed) + extract(minute from elapsed)*60 + extract(hour from elapsed)*60*60 + extract(day from elapsed)*24*60*60)),0) as max_time_seconds
from (
select user_id, job_name, start_time, end_time, (end_time - start_time) elapsed;
from hsp_job_status
where parent_job_id is null
and end_time is not null
and start_time > to_char(sysdate-7)
)
group by job_name
order by num_times_run desc

SQL Server:

select job_name, count(job_name) as num_times_run, round(avg(abs(DATEPART(second, elapsed) + datepart(minute, elapsed) + datepart(hour, elapsed) + datepart(day, elapsed))),0) as avg_time_seconds, round(max(abs(DATEPART(second, elapsed) + DATEPART(minute, elapsed) + DATEPART(hour, elapsed) + DATEPART(DAY, elapsed))),0) as max_time_seconds
from (
select user_id, job_name, start_time, end_time, datediff(ss,start_time,END_TIME) as elapsed 
from [dbo].[HSP_JOB_STATUS]
where parent_job_id is null
and end_time is not null
and start_time > GETDATE()-7
) as ps
group by job_name
order by num_times_run desc

Essbase runtime subvars in 11.1.2.3

The Oracle blog has a good explanation of the new runtime subvars functionality available starting in 11.1.2.3. At the end of that post they vaguely hint that it's possible to access this feature from the APIs. So I did a little research into adding the ability to pass runtime subvars from a grid in cubeSavvyLite to a calc script.

First, let's start with a simple calc script in Sample.Basic (named calcSubs):

SET UPDATECALC off;
SET RUNTIMESUBVARS { 
Currmonth = "Jun";
Currlocation = "Florida";
Currproduct="100";
};

FIX(&Currmonth, &Currlocation)
&Currproduct;
ENDFIX

I'd like to point out here that the new runtimesubvars command is very finicky. "SET RUNTIMESUBVARS" (in all capital letters, as above) works, but "SET runtimesubvars" will not even validate.

The Scala code (cubeSavvyLite is written in Scala, not Java) to call the "calcSubs" calc script and pass in the runtime subvars is:

val ess: IEssbase = IEssbase.Home.create(IEssbase.JAPI_VERSION)
val dom: IEssDomain = ess.signOn("admin", "password", false, null, "http://apsServer:portNumber/aps/JAPI")
val olapSvr: IEssOlapServer = dom.getOlapServer("serverName")
olapSvr.connect()
val cube: IEssCube = olapSvr.getApplication("Sample").getCube("Basic")
cube.calcFileWithRunTimeSubVars(
     false, "calcSubs", "Currmonth=\"Feb\";Currlocation=\"Texas\";Currproduct=\"200\";"
)
olapSvr.disconnect()

Looking at the Sample.Basic log, we see the following entry:
Calculating [ Product(200)] with fixed members [Year(Feb); Market(Texas)]

Runtime subvars will be coming soon to cubeSavvyLite. And they will allow you to pass in Page, Row, and Column members from the grid. The grid, in turn, is created from your report script definition.

I'm also looking at making the runtime subvars available in pre-11.1.2.3 versions, as they're really just simple find-and-replaces in the calc script. Please let me know if that's something you'd be interested in having available.