Hybrid + MDXDataCopy, It’s black magic

I’m going to consider this to be pure coincidence that I started working on this draft (which is a year old), and I got a call from the same client where I implemented this solution to work on a new project 🙂

I talked about this during my Kscope17 groovy presentation, and I did tell that I’ll write about it. Apologies for not doing it early. I was at a client converting a Planning application to a Hybrid cube and as you all now know that cross dimming to parent in Hybrid breaks Hybrid. I’ve also seen a different scenario which we’ll talk in this post.

There are different ways to handle this.

1. Make the cross dim parent and it’s hierarchy as stored and aggregate the dimension, this is fast Hybrid does it’s magic here.
2. Create a dummy member and add the parent as a child of this member, aggregate the dimension and use this member in formulas.
3. This blog post method 😉

Cameron (Big brother from an entirely different set of parents) and I had a long conversation on this topic about all the approaches, and even though Approach 1 works, I didn’t like that, it removes the Hybridness ;). If you didn’t see Pete’s and Cameron’s award-winning presentation, go ahead and watch the recording or download the presentation, they did cover a lot of Hybrid Essbase and different techniques.

Let’s say there is a formula which uses top level of all dimensions in a formula. It pretty much becomes a regular BSO cube.

Approach 2

This method works (I knew you might be thinking if this works why an Approach 3) and here is how you can do this. I changed Sample Basic slightly to add zip codes to Market dimension and here is my updated Sample Basic.

Parent members are added to a calc only member. Now to use Calc_Only_East member I need to aggregate Market dimension. No, you cannot just ping Calc_Only_East member, there is no block for a dynamic member, and because of that reason, you’ll not see the values of East in Calc_Only_East member only an agg can help you here.

On my updated Sample Basic database this script took 6.353 seconds. In my real-world application, a similar script was taking 900+ secs.

Approach 3

In this approach, we are going to add dummy members, however no child members!!!! How are we going to get the results, are you crazy?

What I’m going to do is use @CalcMgrMDXDataCopy/RUNJAVA com.hyperion.calcmgr.common.cdf.MDXDataCopy to copy the data from Parent member to the dummy members 🙂

I’m going to use Groovy to run the calc and will explain why I’m taking this approach.

I created a property file as given below

// Environments section
essbaseEnv {
 dev {
    maxlKey="1036072223,1105601687"
    maxlUser="980700984040509848903004677700"
    maxlPass="2954990220132113241093195557202967062540"
 }
 test { 
    maxlKey="1036072223,1105601687"
    maxlUser="980700984040509848903004677700"
    maxlPass="2954990220132113241093195557202967062540"
 } 
 prod { 
    maxlKey="1036072223,1105601687"
    maxlUser="980700984040509848903004677700"
    maxlPass="2954990220132113241093195557202967062540"
    calcMgrKey="fhe34i5hsdudoi3fhkjhds"
    calcMgrUser="QgEKVtKPyiOjk46kuKw4XuKiXMUxSBlLYFYiJVVeUJ0KInIKMTGycUYuvmVDWYDT"
    calcMgrPass="qZE1ykPXroMHCcfWIFv2g0IlYv4ZjX3ov/EKxLMDfw+S8YJ4BsbtQxwRHhECbyvU"
    esbApp="SampleH1"
    esbDB="Basic"
 } 
}

Now using Groovy’s ConfigSluper, I can read the keys as opposed to writing them in the calc itself.

// Read java and maxl configuration script for the prod environment.
Thread.currentThread().setContextClassLoader( getClass().getClassLoader() )

def keyConfig = new ConfigSlurper().parse(new File('C:/groovy_scripts/Properties/JavaMaxl.txt').toURL())

// read only prod values, to read test use keyConfig.essbaseEnv.test.esbApp
appname=keyConfig.essbaseEnv.prod.esbApp
dbname=keyConfig.essbaseEnv.prod.esbDB

    essmsh.runScript(keyConfig.essbaseEnv.prod.maxlKey,$/
    login $$key ${keyConfig.essbaseEnv.prod.maxlUser} $$key ${keyConfig.essbaseEnv.prod.maxlPass} on localhost;
    execute calculation '
        RUNJAVA com.hyperion.calcmgr.common.cdf.MDXDataCopy
        ${keyConfig.essbaseEnv.prod.calcMgrKey} /* key */
        ${keyConfig.essbaseEnv.prod.calcMgrUser} /*user*/
        ${keyConfig.essbaseEnv.prod.calcMgrPass} /* password */
        "$appname" /* from application */
        "$dbname" /* from database */
        "$appname" /* to application */
        "$dbname" /* to database */
        "[PERIOD].Levels(0).Members"  /* MDX that defines the column members */
        "NON EMPTY Crossjoin(Crossjoin({([FY16],[Sales],[Actual])}, [Product].Levels(0).Members),{[East],[West],[South],[Central]})" /* MDX that defines the row members */
        "East,West,South,Central" /* source member mappings, can be empty */
        "Calc_Only_East,Calc_Only_West,Calc_Only_South,Calc_Only_Central" /* target member mappings, can be empty */
        "" /* Target POV columns, members from dimensions that do not exist on the source*/
         "5000" /* rows per page */
        "C:/groovy_scripts/Logs/AggSal.log"; /* log file , can be empty */
    '
    on $appname.$dbname;
    logout;
    exit;
    /$)

Think about changing a lot of scripts for a user name or password vs change one prop file 🙂

Essbase calc script

</pre>
RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "clear";
RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "level" "FINE";

RUNJAVA com.hyperion.calcmgr.common.groovy.cdf.GroovyCDF
"run" /* compile, run */
"-file"
"C:\groovy_scripts\AggSal.groovy"
"" /* variables in Groovy Script */
""
;

This script took 3.474 secs to complete. My real world calc takes 65 secs, now that is a big difference 900 secs to 65 secs. Again as usual with Essbase you need to test, test, test, and test to see whether this approach is faster or not.

Hybrid is magic and combine with Groovy+MDXDataCopy it is black magic.