Calculation Manager Execute MaxL and MDX Data Copy 8

• I bought a house and was busy settling in.
• I picked up a new hobby – gardening and landscaping (which was kind of obvious). I was/is always fascinated with gardening. When I was a kid I used to pick up the plants to see whether roots are coming or not 🙂
• I was busy with preparing for KScope – My first KScope and it was a great experience to meet all the people you know from forums, blogs,….. You should sign up for Kscope15.

I thought about using RUNJAVA – just to see the syntax, however I will advice you all to use RUNJAVA always. There is reason behind it 🙂

Syntax for RUNJAVA is different than the function and here is how it looks like.

FIX("Actual")
Jan
(
@CalcMgrMDXDataCopy( "admin", "p@ssword", false, "Sample", "Basic", "Sample", "BasicT", "SELECT {[100].CHILDREN} on columns, {[Margin].CHILDREN} on rows, {[Qtr1].CHILDREN} on pages, {Budget, Actual} on chapters where ([New York])" , "E:\Oracle\Middleware\user_projects\azgardepmsystem\tmp\MDXDataCopy.log", null, null, null, India, "Azgard");
 )

ENDFIX

What was it doing, it kept exporting/loading the same data for all the members it process. (I didn’t have a clue – Sree informed me about this). Here is the reason why I asked you to stick with RUNJAVA always.

Following are Sree’s words on the syntax

Since this was just a POC, Source/Target mapping does not work like partitioning. It is just source meber to target member (Apr to April). No functions are supported.
Since the mdx can return large data, for performace, maxRowsPerPage could be set to say 1000 which means as soon as the grid has 1000 rows and update to ASO is done.
At the end remaining rows are also pushed. If the maxRowsPerPage is set to “-1”, then all the data is filled in to one grid.
sourceMemberMappings could be something like @List(“Apr”, “Qtr1”) and targetMemberMappings could be @List(“April”, “Quarter1”).

After following his advice this is how the script looks like.

RUNJAVA com.hyperion.calcmgr.common.cdf.MDXDataCopy "admin"  "p@ssword"  false  "Sample"  "Basic"  "Sample"  "BasicT"  "SELECT {[100].CHILDREN} on columns, {[Margin].CHILDREN} on rows, {[Qtr1].CHILDREN} on pages, {Budget, Actual} on chapters where ([New York])"  "E:\Oracle\Middleware\user_projects\azgardepmsystem\tmp\MDXDataCopy.log" "New York" "India" -1 "" "Azgard";

Here is the syntax from the specs @CalcMgrMDXDataCopy(user, password, useToken, sourceApplicaionName, srcDatabaseName, targetApplicationName, targetDatabaseName, mdxQuery, loggingFileName, sourceMemberMappings, targetMemberMappings, maxRowsPerPage,tgtPOVCols,server)

I didn’t use tgtPOVCols here, but if you have different dimensionality and you want to load it to a POV, then you can mention them here. Let’s look at the output now.

Query:SELECT {[100].CHILDREN} on columns, {[Margin].CHILDREN} on rows, {[Qtr1].CHILDREN} on pages, {Budget, Actual} on chapters where ([New York])
User:admin
useToken:No
Source:Sample/Basic
Target:Sample/BasicT
Signed in User:admin
Grid Size: Rows(14) Cols(6)
			India			
			100-10	100-20	100-30	
Budget	Jan	Sales	640.0	#Missing	#Missing	
Budget	Jan	COGS	260.0	#Missing	#Missing	
Budget	Feb	Sales	610.0	#Missing	#Missing	
Budget	Feb	COGS	240.0	#Missing	#Missing	
Budget	Mar	Sales	640.0	#Missing	#Missing	
Budget	Mar	COGS	250.0	#Missing	#Missing	
Actual	Jan	Sales	678.0	#Missing	#Missing	
Actual	Jan	COGS	271.0	#Missing	#Missing	
Actual	Feb	Sales	645.0	#Missing	#Missing	
Actual	Feb	COGS	258.0	#Missing	#Missing	
Actual	Mar	Sales	675.0	#Missing	#Missing	
Actual	Mar	COGS	270.0	#Missing	#Missing

I didn’t do anything here. That is the entire output. Isn’t that really neat?

I had some trouble getting the syntax for CalcMgrMDXDynDataCopy

I think the definitions are little messed up in the specs.

This is from the specs

@CalcMgrMDXDynDataCopy(user, password, useToken, sourceApplicaionName, srcDatabaseName, targetApplicationName, targetDatabaseName, logFileName, sourceMemberMappings, targetMemberMappings, maxRowsPerPage, slicerAxisQuery, axisQuery, povCols, server)

and this is the one which works

RUNJAVA com.hyperion.calcmgr.common.cdf.MDXDynDataCopy "admin"  "p@ssword"  false  "Sample"  "Basic"  "Sample"  "BasicT" "E:\Oracle\Middleware\user_projects\azgardepmsystem\tmp\MDXDynDataCopy.log" "New York" "India" -1 "" "([New York])" "[100].CHILDREN" "[Margin].CHILDREN" "[Qtr1].CHILDREN" "{Budget, Actual}";

So I think this is what the spec should really look like

@CalcMgrMDXDynDataCopy(user, password, useToken, sourceApplicaionName, srcDatabaseName, targetApplicationName, targetDatabaseName, logFileName, sourceMemberMappings, targetMemberMappings, maxRowsPerPage, povCols, slicerAxisQuery, axisQuery);

You don’t need a server for this one.

What will this do, it generates the MDX for you!!!!!

Query:SELECT [100].CHILDREN on 0, [Margin].CHILDREN on 1, [Qtr1].CHILDREN on 2, {Budget, Actual} on 3 FROM Sample.Basic WHERE ([New York])
User:admin
useToken:No
Source:Sample/Basic
Target:Sample/BasicT
Signed in User:admin
Grid Size: Rows(14) Cols(6)

The first line is the MDX generated by the function.