Move data "On-Save" from Planning BSO to Reporting ASO (Part 1)

Moving data from BSO planning application to ASO reporting application ? 

Want to have this data movement really fast ? 

Want to move that data as soon as users save web forms ? 

     

      Well, you can do that in many different ways. The time it takes to move the data between 2 cubes depends mostly on how fast you can extract level 0 data from the Planning BSO cube. 

    To do that I have used MDX export from planning BSO cube with 'NONEMPTYBLOCK‘ keyword. I have tested this against 3 different BSO planning cubes and found that MDX with 'NONEMPTYBLOCK' keyword export level 0 data faster than data export calculation script. But I imagine, it won't be the case always. So, test test and test before you select your data export process. 

We have built this solution last year using Essbase Java API and CDF. I agree this is not really out of the box solution. You need to use bit of Essbase Java API to format MDX output to get this to work.  But if you are in Essbase version 11.1.2.4, this can be a solution where you don't have to write any Java code. I will write about that in my next post.

Here is the solution steps:

    

1. Get your level0 export MDX query ready. Don't forget to   add 'NONEMPTYBLOCK‘  keyword. 
2. Now you have to create Java Custom defined function(CDF). With in the CDF use Essbase Java API to parse and load MDX output to ASO database. If you need more details, I have blogged earlier about this here. 
3. Run CDF from a calculation script in the planning cube using RUNJAVA command.
   
4. Add this calculation script in your web-form to run it on save.
   
5. Success of this 'On-Save' load depends on speed. You don't want your user to watch their frozen web-form for 5 min while data is getting transferred. Try to have this data movement more targeted by passing POV information to your CDF from planning web-form via calculation script. By doing that you can minimize amount of data movement and restrict it only to POV of the web-form. 

 Demo time ... 

How to extract fast from BSO Planning to ASO Reporting

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

}

ASO Extract & Load - How fast it can be?

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();