MDXDataCopy: Smart Push for On-Premise

If you attended my recent presentation at Kscope18, I covered this topic and provided a live demonstration of MDXDataCopy.  MDXDataCopy provides an excellent method for creating functionality similar to that of Smart Push in PBCS.  While my presentation has all of the code that you need to get started, not everyone likes getting things like this out of a PowerPoint and the PowerPoint doesn’t provide 100% of the context that delivering the presentation provides.

Smart Push

In case you have no idea what I’m talking about, Smart Push provides the ability to push data from one cube to another upon form save.  This means that I can do a push from BSO to an ASO reporting cube AND map the data at the same time.  You can find more information here provided in the Oracle PBCS docs.  This is one of the features we’ve been waiting for in On-Premise for a long time.  I’ve been fortunate enough to implement this functionality at a couple of client that can’t go to the cloud yet.  Let’s see how this is done.

MDXDataCopy

MDXDataCopy is one of the many, many functions included with Calculation Manager.  These are essentially CDF’s that are registered with Essbase.  As the name implies, it simply uses MDX queries pull data from the source cube and then map it into the target cube.  The cool part about this is that it works with ASO perfectly.  But, as with many things Oracle, especially on-premise, the documentation isn’t very good.  Before we can use MDXDataCopy, we first have some setup to do:

• Generate a CalcMgr encyrption key
• Encrypt your username using that key
• Encrypt your password using that key

Please note that the encryption process we are going through is similar to what we do in MaxL, yet completely different and separate.  Why would we want all of our encryption to be consistent anyway?  Let’s get started with our encrypting.

Generate Encryption Key

As I mentioned earlier, this is not the same process that we use to encrypt usernames and passwords with MaxL, so go ahead and set your encrypted MaxL processes and ideas to the side before we get started.  Next, log into the server where Calculation Manager is installed.  For most of us, this will be where Foundation Services happens to also be installed.  First we’ll make sure that the Java bin folder is in the path, then we’ll change to our lib directory that contains calcmgrCmdLine.jar, and finally we’ll generate our key:

path e:\Oracle\Middleware\jdk160_35\bin
cd Oracle\Middleware\EPMSystem11R1\common\calcmgr\11.1.2.0\lib
java -jar calcmgrCmdLine.jar –gk

This should generate a key:

We’ll copy and paste that key so that we have a copy.  We’ll also need it for our next two commands.

Encrypt Your Username and Password

Now that we have our key, we should be ready to encrypt our username and then our password.  Here’s the command to encrypt using the key we just generated (obviously your key will be different):

java -jar calcmgrCmdLine.jar -encrypt -key HQMvim5GrSYox7S9bR8jSx admin
java -jar calcmgrCmdLine.jar -encrypt -key HQMvim5GrSYox7S9bR8jSx GetYourOwnPassword

This will produce two keys for us to again copy and paste somewhere so that we can reference them in our calculation script or business rule:

Now that we have everything we need from our calculation manager server, we can log out and continue on.

Vision

While not as popular as Sample Basic, the demo application that Hyperion Planning (and PBCS) comes with is great.  The application is named Vision and it comes with three BSO Plan Types ready to go.  What it doesn’t come with is an ASO Plan Type.  I won’t go through the steps here, but I basically created a new ASO Plan Type and added enough members to make my demonstration work.  Here are the important parts that we care about (the source and target cubes):

Now we need a form so that we have something to attach to.  I created two forms, one for the source data entry and one to test and verify that the data successfully copied to the target cube.  Our source BSO cube form looks like this:

Could it get more basic?  I think not.  And then for good measure, we have a matching form for the ASO target cube:

Still basic…exactly the same as our BSO form.  That’s it for changes to our Planning application for now.

Calculation Script

Now that we have our application ready, we can start by building a basic (I’m big on basic today) calculation script to get MDXDataCopy working.  Before we get to building the script, let’s take a look at the parameters for our function:

• Key that we just generated
• Username that we just encrypted
• Password that we just encrypted
• Source Essbase Application
• Source Essbase Database
• Target Essbase Application
• Target Essbase Database
• MDX column definition
• MDX row definition
• Source mapping
• Target mapping
• POV for any dimensions in the target, but not the source
• Number of rows to commit
• Log file path

Somewhere buried in that many parameters you might be able to find the meaning of life.  Let’s put this to practical use in our calculation script:

RUNJAVA com.hyperion.calcmgr.common.cdf.MDXDataCopy  
"HQMvim5GrSYox7S9bR8jSx"
"PnfoEFzjH4P37KrZiNCgd0TMRGSxWoFhbGFJLaP0K72mSoZMCz2ajF9TePp751Dv"
"D44Yplx+Mlj6P2XhGfwvIw4GWHQ5tWOytksR5bToq126xNoPYxWGe3KGlPd56oZ8"
"VisionM"
"Plan1"
"VMASO"
"VMASO"
"{[Jul]}"
"CrossJoin({[No Account]},CrossJoin({[FY16]},CrossJoin({[Forecast]},CrossJoin({[Working]},CrossJoin({[No Entity]},{[No Product]})))))"
""
""
""
"-1"
"e:\\mdxdatacopy.log";

Let’s run down the values used for our parameters:

• HQMvim5GrSYox7S9bR8jSx (Key that we just generated)
• PnfoEFzjH4P37KrZiNCgd0TMRGSxWoFhbGFJLaP0K72mSoZMCz2ajF9TePp751Dv (Username that we just encrypted)
• D44Yplx+Mlj6P2XhGfwvIw4GWHQ5tWOytksR5bToq126xNoPYxWGe3KGlPd56oZ8 (Password that we just encrypted)
• VisionM (Source Essbase Application)
• Plan1 (Source Essbase Database)
• VMASO (Target Essbase Application)
• VMASO (Target Essbase Database)
• {[Jul]} (MDX column definition, in this case just the single member from our form)
• CrossJoin({[No Account]},CrossJoin({[FY16]},CrossJoin({[Forecast]},CrossJoin({[Working]},CrossJoin({[No Entity]},{[No Product]}))))) (MDX row definition, in this case it requires a series of nested crossjoin functions to ensure that all dimensions are represented in either the rows or the columns)
• Blank (Source mapping which is left blank as the two cubes are exactly the same)
• Also Blank (Target mapping which is left blank as the two cubes are exactly the same)
• Also Blank (POV for any dimensions in the target, but not the source which is left blank as the two cubes are exactly the same)
• -1 (Number of rows to commit which is this case is essentially set to commit everything all at once)
• e:\\mdxdatacopy.log (Log file path where we will verify that the data copy actually executed)

The log file is of particular importance as the script will execute with success regardless of the actual result of the script.  This means that especially for testing purposes we need to check the file to verify that the copy actually occurred.  We’ll have to log into our Essbase server and open the file that we specified.  If everything went according to plan, it should look like this:

This gives us quite a bit of information:

• The query that was generated based on our row and column specifications
• The user that was used to execute the query
• The source and target applications and databases
• The rows to commit
• The query and copy execution times
• And the actual data that was copied

If you have an error, it will show up in this file as well.  We can see that our copy was successful.  For my demo at Kscope18, I just attached this calculation script to the form.  This works and shows us the data movement using the two forms.  Let’s go back to Vision and give it a go.

Back to Vision

The last step to making this fully functional is to attach our newly created calculation script to our form.  Notice that we’ve added the calculation script and set it to run on save:

Now we can test it out.  Let’s change our data:

Once we save the data, we should see it execute the script:

Now we can open our ASO form and we should see the same data:

The numbers match!  Let’s check the log file just to be safe:

The copy looks good here, as expected.  Our numbers did match after all.

Conclusion

Obviously this is a proof of concept.  To make this production ready, you would likely want to use a business rule so that you can get context from the form for your data copy.  There are however some limitations compared to PBCS.  For instance, I can get context for anything that is a variable or a form selection in the page, but I can’t get context from the grid itself.  So I need to know what my rows and columns are and hard-code that.  You could use some variables for some of this, but at the end of the day, you may just need a script or rule for each form that you with to enable Smart Push on.  Not exactly the most elegant solution, but not terrible either.  After all, how often do your forms really change?