Recently we have implemented a new BSO
planning cube (for input purpose) and corresponding ASO cube for reporting. We have
a process that Extract from BSO cube and load data to ASO cube. We are running this
process every 15 min interval to sync our reporting cube with planning cube.
Well, I will not discuss here the merit of using
BSO planning with ASO Reporting. But here in this blog, I would like to discuss
certain way by which you can extract large chunk of data fairly quickly from
BSO. Then you can load that data to ASO cube or do nothing and be happy that you
did it and did it fast!
This process is much quicker
than dataexport calc script extract (as per testing done in our
environment). The process here is based on MDX extract to flat file from BSO
with Java API. The same flat file can be loaded to ASO/BSO cube if needed. Key
to your MDX query success? Keyword 'NONEMPTYBLOCK' in the query. It is complex to
parse MDX output because of its weird format. But once you do that ‘Good thing
will happen’...
Here is the code sample that we have used in our job that runs every
15 min to extract from BSO to ASO cube.
//Note1: Provide full path to your app/DB folder of target database as the value of filePath below. In case you are loading the extracted data back to ASO/BSO cube.
// Note2: We will extract data from BSO in flat file fileName
//Note3: Extracted data will be written in a format that do not require any load rule to load back into ASO or BSO. That is each row will have one dimension member from each dimension and a measure value. We will not load 0's or #Missing.
//Sample:
pln= IEssbase.Home.create(IEssbase.JAPI_VERSION);
// Sign On to the Provider
IEssDomain plnprovider = pln.signOn(s_userName, s_password, false, null, s_provider);
try {
cv = plnprovider.openCubeView("Mdx Query Extract", s_PlanningSrvName,s_plnAppName, s_plnCubeName);
System.out.println(new Timestamp(System.currentTimeMillis())
+ "
Connected to "+ s_PlanningSrvName +" "+
s_plnAppName +" "+ s_plnCubeName);
mdxExtract(cv); // This subroutine
will extract data from planning cube to flat file
} catch (Exception x) {
System.out.println("Error: Extract
to flatfile failed " + x.getMessage());
x.printStackTrace();
statusCode = FAILURE_CODE;
} finally {
// Close cube view.
try {
if (cv != null)
cv.close();
} catch (EssException x) {
System.err.println("Error: " + x.getMessage());
x.printStackTrace();
statusCode = FAILURE_CODE;
}
}
//========================================================
// Subroutine mdxExtract
//========================================================
private static void
mdxExtract(IEssCubeView cv) throws Exception {
boolean bDataLess = false;
boolean bNeedCellAttributes = false;
boolean bHideData = true;
//
String lastData = null;
String mdxquery = null;
file=new FileWriter (filePath+fileName);
filePrint=new PrintWriter(file);
System.out.println("Beginning MDX Extarct and Load...");
mdxquery ="SELECT " +
"
{MemberRange([Period].Jan,[Period].Dec)}" +" ON COLUMNS," +
" NONEMPTYBLOCK Crossjoin"+
" (Crossjoin
([Account].Levels(0).Members, [Operating Unit].Levels(0).Members),"+
" (CrossJoin
"+
" (Crossjoin
([Scenario].Levels(0).Members,[Entity].Levels(0).Members),"+
" (CrossJoin
"+
" (CrossJoin
({USD,CAD},[Version].Levels(0).Members),"+
" (CrossJoin "+
" ( CrossJoin([Product].Levels(0).Members,
[Intercompany].Levels(0).Members), "+
" CrossJoin([Source].Levels(0).Members,
[Year].Levels(0).Members) )
))))))"+
" ON ROWS "+
" FROM
[APP].[DB]";
System.out.println(mdxquery);
IEssOpMdxQuery op = cv.createIEssOpMdxQuery();
op.setQuery(bDataLess, bHideData, mdxquery, bNeedCellAttributes,IEssOpMdxQuery.EEssMemberIdentifierType.NAME);
op.setXMLAMode(false);
op.setNeedFormattedCellValue(true);
op.setNeedFormatString(false);
op.setNeedMeaninglessCells(false);
cv.performOperation(op);
System.out.println("MDX Extarct complete...Writing in file ");
IEssMdDataSet mddata = cv.getMdDataSet();
IEssMdAxis[] axis = mddata.getAllAxes();
String s_pad="Amount"; // We have one more dimension in ASO cube than BSO. We are padding a value here for the member name where data needs to get loaded.
int totalRec=0;
int k=0;
for(int j=0;j<axis[1].getTupleCount();j++)
for(int i=0;i<axis[0].getTupleCount();i++){
data=null;
IEssMdMember[] row = axis[1].getAllTupleMembers(j);
IEssMdMember[] datacol = axis[0].getAllTupleMembers(i);
data="\""+row[0].getName()+"\"
\""+row[1].getName()+"\" \""+row[2].getName()+"\" \""+row[3].getName()+"\" \"";
data=data+row[4].getName()+"\"
\""+row[5].getName()+"\" \""+row[6].getName()+"\"
\""+s_pad+"\" \"";
data=data+row[7].getName()+"\"
\""+row[8].getName()+"\" \""+row[9].getName()+"\"
\""+datacol[0].getName();
loadCellValue(mddata,k);
k++;
if(data!=null){
filePrint.println(data);
totalRec++;
}
}//end
of for loop
filePrint.close();
System.out.println(new Timestamp(System.currentTimeMillis())
+"
Data Export file is ready... ");
System.out.println("Total number of Data Extracted:"+k);
System.out.println("Total number of Data Loaded:"+totalRec);
System.out.println("------------------ -------------------------
----------------");
}
private static void
loadCellValue(IEssMdDataSet mddata ,int k)
throws Exception {
if (mddata.isMissingCell(k)) {
data=null;
} else {
String fmtdCellTxtVal = mddata.getFormattedValue(k);
if (fmtdCellTxtVal != null && fmtdCellTxtVal.length() > 0) {
data=null;
} else {
double val = mddata.getCellValue(k);
data=data+"\" "+val;
}
}
k++;
}
//========================================================
// Load ASO
//========================================================
// Easy
String[][] error=cube.loadData(IEssOlapFileObject.TYPE_TEXT, null, IEssOlapFileObject.TYPE_TEXT,s_exportedFileName , true, null,null);
if (error!=null){
System.out.println("There is
kickout ....Check for it");
// print it in File if needed
}
