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

This is continuation from previous post to provide more 'out of the box' solution for data movement from BSO planning to ASO Reporting cube. This solution is only possible if you are in 11.1.2.4. 

  We will have exactly the same process like part 1 of this blog. The only thing that we will be skipping here is writing CDF part. Because Oracle has calc manager CDF that can run a MaxL stored in the server using RUNJAVA com.hyperion.calcmgr.common.cdf.MaxLFunctions. More over from 11.1.2.4 we can have formatted MDX output in a flat file. What else you need ! You already guessed where it is going...

Solution:

1. Level0 export MDX query with 'NONEMPTYBLOCK‘  keyword. 
2. Create MaxL script to spool MDX value to a flat file. Use set column_separator "|" ; to get a formatted output. More information on this available on other very useful blogs.
3. Load the flat file to ASO cube with MaxL. 
4. Call all of these MaxL scripts from BSO calculation script using RUNJAVA com.hyperion.calcmgr.common.cdf.MaxLFunctions. (Check internet blogs)
5. Add this calculation script in your web-form to run it on save.

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 ...