Hyperion ASO cube changed Essbase developer’s life in many way. Users are happy with their report retrieval time. Support folks are happy too, as they do not receive angry call from users during financial close. Recently in our organization, we've replaced a huge BSO cube with an ASO. Since
then, report retrieval time improved radically. Now nightly job which loads
actuals from Oracle ERP, does not require to run all those painfully long calculation
scripts. Well, we’ve saved 3.5 hours there. Users can get into the application very early in the morning which was a challenge in past. All the responses were very
positive. All was well till we figured out (Many must have felt the pain
before) that ASO applications are notoriously slow when you want to extract a
large portion of the cube. For example, in our case, we had to copy “Actual” to “Current
Forecast” and “Budget” scenario for prior months. Not a very extraordinary
requirement. I’m sure other organizations have something similar processes
where one needs to extract and load from/to same ASO cube. We tried to use
report script to extract Actual from our ASO cube but the process was running
more than 2 hours and that was not acceptable.We
were desperately trying find something like DataExport Calc for ASO. Wish
Oracle will have that for ASO in near future. After some research we came up with
a process which now runs in 45 sec. Essbase JAPI was used to accomplish it. I
like to believe this is the fastest way to do the job, I explained above. Please
prove me wrong if you have other idea.
Let me explain the basic logic first. More complex logic can be
implemented by using Essbase substitution variables.
Step1: Copy ASO cube (Say SampleASO) to a new ASO Cube (Say JavaCpy).
Step2: Delete all scenario but the scenario you want to copy. In our
case we will delete everything but Actual from JavaCpy.
Step3: rename source scenario to Target Scenario. In our case we will
rename Actual to current forecast.
Step4: Clear from JavaCpy anything else that you may not want to copy.
Step5: Export Level 0 data from copy application (JavaCpy) to App/AppName/DBName
folder of Target cube. i.e. SampleASO
Step6: Clear Data from target cube (SampleASO) for the intersection
data to be loaded from JavaCpy.
Step7: Import data to the target cube.
Now once you understand the process above, it is up to you how
you would write the code in Java. I have added code sample from my
implementation for each steps. I am assuming here that you are familiar with
Essbase java API’s.
/*****************************************************************************************
Step 1: Copy ASO cube to a new ASO
Cube
******************************************************************************************/
try {
try{
//
Delete if the Copy Application already exist
System.out.println(new Timestamp(System.currentTimeMillis()) + " Deleting "+s_appCopyName
+"...");
IEssOlapApplication newApp = olapSvr.getApplication(s_appCopyName);
newApp.delete();
}
catch(EssException x){
System.out.println("No Need to
Delete") ;
}
System.out.println(new Timestamp(System.currentTimeMillis())
+ "
Copying " +s_appName +" To " +s_appCopyName );
app.copy(s_appCopyName);
System.out.println(new Timestamp(System.currentTimeMillis())
+ "
Finished "
+
"Copying
" +s_appName +" To " +s_appCopyName );
} catch (Exception x) {
System.out.println("Error: " + x.getMessage());
x.printStackTrace();
statusCode = FAILURE_CODE;
}
/***********************************************************************************************
Step2: Delete all scenario but Actual
from JavaCpy application
***********************************************************************************************/
IEssCube cube= olapSvr.getApplication(s_appCopyName).getCube(s_cubeName);
if(!cube.isActive()) cube.setActive();
IEssMemberSelection mbrSel = cube.openMemberSelection(s_dimName + "
descendants");
mbrSel.executeQuery(s_dimName, IEssMemberSelection.QUERY_TYPE_CHILDREN, IEssMemberSelection.QUERY_OPTION_MEMBERSONLY, s_dimName, "", "");
IEssIterator mbrs = mbrSel.getMembers();
System.out.println(new Timestamp(System.currentTimeMillis()) + " Deleting
members from "+ s_dimName) ;
IEssCubeOutline
cubeOutline = cube.openOutline(false,true,true);
for(int iCount = 0; iCount < mbrSel.getCountMembers(); iCount++)
{
IEssMember
member = (IEssMember)mbrs.getAt(iCount);
String
s_memName=member.getName().toString();
if(!s_memName.equalsIgnoreCase(s_sourceMember)) //Checking if it is Actual
{
try{
IEssMember essMem = cubeOutline.findMember(s_memName);
essMem.delete();/* delete member */
// System.out.println("Member - " +
member.getName() + " deleted.");
}catch(EssException noMember){
System.err.println("Error: delete
member failed" + noMember.getMessage());
noMember.printStackTrace();
statusCode = FAILURE_CODE;
}
}
}
cubeOutline.verify();/* verify outline,
if outline does not verify interface will fail*/
cubeOutline.save();/* save outline */
cubeOutline.restructureCube(IEssCube.EEssRestructureOption.KEEP_ALL_DATA);
cubeOutline.close();
/***********************************************************************************************
Step3: Rename Actual Scenario in JavaCpy Application to Current Forecast
***********************************************************************************************/
try{
cubeOutline = cube.openOutline(false,true,true);
IEssMember essMem = cubeOutline.findMember(s_sourceMember);
essMem.rename(s_targetMember);// rename member
cubeOutline.verify();/* verify outline,
if outline does not verify interface will fail*/
cubeOutline.save();/* save outline */
cubeOutline.restructureCube(IEssCube.EEssRestructureOption.KEEP_ALL_DATA);
cubeOutline.close();
System.out.println(new Timestamp(System.currentTimeMillis())
+ "
"+ s_sourceMember + " is renamed to
"+ s_targetMember) ;
}catch(EssException noMember){
System.err.println("Error: Rename failed" + noMember.getMessage());
}
/***********************************************************************************************
Step5: Export Level 0 from copy application JavaCpy. Note use of IEssCube.EEssDataLevel.LEVEL0
***********************************************************************************************/
IEssCube cubeCpy = olapSvr.getApplication(s_appCopyName).getCube(s_cubeName);
if(!cubeCpy.isActive()) cubeCpy.setActive();
System.out.println(new Timestamp(System.currentTimeMillis())
+ "
Started Level 0 Export to file " +s_exportFileWithExtn);//s_exportFileWithExtn value is the text file name Ex. DataFile.txt
cubeCpy.exportData(s_essFullPath + s_exportFileWithExtn,
IEssCube.EEssDataLevel.LEVEL0, false);
//s_essFullPath should have full eessbase server path to app/Appname/DbName folder
System.out.println(new Timestamp(System.currentTimeMillis())
+ "
Export completed");
cubeCpy.clearActive();
/***********************************************************************************************
Step6: Sample of clearing partial data from the ASO cube.
***********************************************************************************************/
IEssCube cube = olapSvr.getApplication(s_appName).getCube(s_cubeName);
if(!cube.isActive()) cube.setActive();
//Clear Intersection before loading
String s_region="{CROSSJOIN({["+s_prevYear+"]},{["+s_targetMember+"]})}"; // Clear previous
Year
cube.clearPartialData(s_region, true);
System.out.println(new Timestamp(System.currentTimeMillis())
+"
Data cleared for region "+s_region + " From " + cube.getApplicationName());
/***********************************************************************************************
Step6: Import the data in target cube.
***********************************************************************************************/
System.out.println(new Timestamp(System.currentTimeMillis())
+ "
Started Level 0 import from file " +s_exportFileName);
cube.asyncLoadData(IEssOlapFileObject.TYPE_TEXT, null, IEssOlapFileObject.TYPE_TEXT,s_exportFileName , true, null,null);
System.out.println(new Timestamp(System.currentTimeMillis())
+ "
import completed");
cube.closeAsyncProcess();
