The world of CLEARBLOCK NONINPUT, MaxL Set Statements and CalcMgrMDXExport 1

This blog post started as an introduction to a new feature that was added to @CalcMgrMDXExport function (well not really the function, to the RUNJAVA command). It turned out to be something more than that.

I’m at a client who does heavy data validation after every scheduled data load/calc execution and I changed that cube to a Hybrid cube and the results are fantablous!!!

Now with the Hybrid engine, you loose the ability to write report scripts (and there were report scripts all over the place). Now my task was to convert those report scripts to MDX and perform the data validation.

As you all know a neat MDX was always a man’s(woman’s) dream till @CalcMgrMDXExport came in, later we got the new set commands in MaxL (all thanks to Gary, you can read about it from his blog).

At my current client, I had a mix of @CalcMgrMDXExport (even though I’m referring to this function, all I did was to use the RUNJAVA version of it)and MaxL set commands. Now why the two you might ask and here is why.

All this was done before 11.1.2.4.010(Essbase version) and there was the only @CalcMgrMDXExport function, once we moved to 11.1.2.4.010 then there were scenarios where report scripts are suppressing pages. If you’ve worked with @CalcMgrMDXExport or RUNJAVA com.hyperion.calcmgr.common.cdf.MDXExport, you’ll notice that there is no option to specify a where clause (and this is how you can suppress those members) and suppress column headers.

So let’s look at an example to make this more clear.

	RUNJAVA com.hyperion.calcmgr.common.cdf.MDXExport
	"fhe34i5hsdudoi3fhkjhds" /* key */ 
	"IB2G5gOiq+/2uTSnY82iKtzE9wUpaTWhHrFMxoV9evOC1aE904b4zrlLkuJiQztp" /*user*/ 
	"lS8QdUjodPMJsOZOb4vgAfH4OW7ewRjyk2ce6cTfjwY1V2Xtay4EKhuOQbBTqfBE" /* password */ 
	"C:/Temp/SampleBasic.txt" /* export file */
	"Sample" /* from application */ 
	"Basic" /* from database */ 
	"localhost" /* server name */
	"NON EMPTY {[Jan]}" /* column axis */
	"NON EMPTY crossjoin(crossjoin(crossjoin(crossjoin({[Sales]},{[100-10]}),{[Actual],[Budget]}),{[New York]}),{[&CurYr]})" /* MDX that defines the row members */ 
	"	" /* seperator */
	"0" /* #Missing replacement */
	"false" /* use unique name, if you have duplicate names enabled*/
	"false" /* use alias name */
	"true" /* suppress zeros */
	"false"; /* row headers, similar to DataExportDimHeader ON + DataExportColHeader */

This will export a file as shown below.

Now what if I want to get a file like the one given below

There is where I had to use the new MaxL set commands

set column_width 10000;
set message level fatal;
set column_separator "	";
set column_header off;
set echo_mode off;
alter session set dml_output alias off;
alter session set dml_output cell_status off;
// alter session set dml_output numerical_display fixed_decimal;
// alter session set dml_output precision 2;

spool on to 'C:/Temp/SampleBasicNew.txt'; /* export file */

	select
	NON EMPTY {[Jan]} on columns, /* column axis */
	NON EMPTY crossjoin(crossjoin({[Actual],[Budget]},{[New York]}),{[&CurYr]})
	on rows/* MDX that defines the row members */ 
	from Sample.Basic
	where ([Sales],[100-10]);
	
spool off;
logout;
exit;

As you can see it produces a neat output!!!

Now things that you have to notice in the SET commands are

• alter session set dml_output cell_status off;
• alter session set dml_output numerical_display fixed_decimal;

Let’s look at it one by one and see what happens if you switch the cell_status on;

I picked Profit % to explain cell_status and you’ll see why.

Wow, what is the DC, RO doing here? Well here lies the answer. That is truly the status of the cell. It is telling you that Profit % is a dynamically calculated member and it is read only.

There we go a better way to parse MDX, wait it is not just for MDX, you can use that for display MaxL too.

Now that I can see the cell status and I thought of finding out whether the status CM

“CM: Calculated Member. If any of the members defining the cell is a calculated member, this status is on”.

is used for CLEARBLOCK NONINPUT.

I created a quick and dirty script and calculated Budget. (Scenario was changed to Sparse, only Measures and Period is dense in my modified Sample Basic cube)

FIX(@ILSIBLINGS("Florida")
			"Jan"
			"100-10"
			&CurYr
			"Sales")
			"Budget"(
					1.5 * "Actual";
			)
ENDFIX

Made sure that I just have two blocks in the system.

Nope CM is not populated, I had a curiosity to check CLEARBLOCK NONINPUT command. A quick script showed me that it is working fine.

FIX(@ILSIBLINGS("Florida")
			"Jan"
			"100-10"
			"Budget"
			&CurYr
			"Sales")

			CLEARBLOCK NONINPUT;				

ENDFIX

Now that is settled (or is it? 😉 CLEARBLOCK NONINPUT is relying on blocks, not cells) let’s discuss what numerical_display fixed_decimal is doing. This setting is forcing decimals display in MaxL output (vs scientific notation).

Since I had to use two different options(which I didn’t like), I was in touch with the Calc Manager team to see whether we can improve @CalcMgrMDXExport function.

From 11.1.2.4.010 Calc Manger version (released 5 hours ago 😉 ), the RUNJAVA component of MDXExport is improved.

• It supports where clauses
• It also features source and target mappings
• It allows suppressing column headers

You will now ask why I need the source and target mappings in MDXExport, well you’ll find this feature really useful when you are converting an Essbase cube to Hybrid which uses report scripts.

Consider a report script which uses RENAME command J, now you can convert this using the WITH MDX clause however WITH clause is not currently supported in Hybrid. Bummer isn’t it?

I had to use groovy in calc scripts to do the replace and I found that it’ll be more convenient to have this feature in the function itself.

Here is how the script will look from 11.1.2.4.010 onwards

Here is the output

Happy hybridizing.