Friday, August 21, 2015

Essbase Outline And Data Scrambler (Or Essbase JAPI Workshop Part II)

Essbase What?



Imagine you are part of an IT organization, and have to work with all those consultants (like me),  support teams and third party vendors. Sometimes they ask you to share outlines or representative data sets, in order to replicate some issue, optimize your cube, or make enhancements to calculations. Sharing data or metadata usually is not a problem if consultants are on site, have company-issued laptops, and data doesn’t contain sensitive information like salaries, identifiable health information etc.

But what happens in the following situations:
  • Consultants work off site, for example support team or outsourced resources.
  • Consultants are on site, but shouldn’t have access to classified information.
  • IT or business would like to hire an expert for just a few hours, to resolve some specific issue. But they don't want to go through the formal hiring nightmare.

Why would you need representative data set in the first place? Why couldn’t you generate some random data and load into the cube? The problem is, if you try to optimize or design your cube, specific data set and its dynamics may have a big impact on application performance. It will depend on data distribution, volume, and speed of growth. And it is obvious why you need a representative outline structure while designing the application, and not just a random collection of members.
On the other hand, application design or performance usually do not depend on specific data values, or member names.

So what we need is a utility that would completely "scramble" essbase database. If the scrambled data files or outlines get to the wrong hands, it will be completely meaningless. Our consultant however, would have just enough to perform her/his job. In other words, this utility would:
  • Rename all members in the outline to something generic that doesn’t have any business meaning. Something like Member_100037619, where the second part of the member name is just a sequential number.
  • Update all references to member names in formulas.
  • Update all references in calculation scripts, report scripts, maxl scripts or just any textual data.
  • Update data files with new member names, and replace data values with random data.

The output of this encryption could be sent to consultant or third party. Encryption is not really the right term here, since encryption assumes that the information can be read by the authorized party. In our case the authorized party (consultant) cannot decrypt or read the original content, since it has never left client's servers.
On one hand, this output will have no meaningful business information, on the other - the outline structure, data distribution and volume would be exactly the same as in the original application.

My personal reason to build Essbase scrambler was this. As you may know, i was recently working on a system that performs automatic application optimization, testing and monitoring. It’s all in this post.
At Kscope i met with a few clients that wanted to give it a try. But in order to allow me to install the system on their servers i needed to go through a formal contractual process, and that would be a major headache for my clients and myself. So i wanted to give them a utility that would scramble their data and outlines, which they could share with me.
From the optimization point of view i would  get exactly the same results as if i was using the real outlines and data. And if i got promising results, we would continue with configuration on client’s servers. Otherwise - no reason to start the bureaucratic routine.

This post will also serve as a continuation of Essbase CDF Workshop Part I. We will see the use of Java API to query and update member properties.

Now let's jump into technical details and see how it works.

General Workflow

Below is a diagram of the workflow;

First we need to get the arguments passed from the command line. Our command will look like:

java -jar essbasescrambler.jar D:\\AutomationsV2\\Scrambler\\Params.xml outline data

Properties File


As you can see, the first argument is a path to XML file that contains all important properties. Lets take a look at it.

<ESSBASEUSERID><![CDATA[admin]]></ESSBASEUSERID>
<ESSBASEPASSWORD><![CDATA[password]]></ESSBASEPASSWORD>
<ESSSERVER><![CDATA[192.168.72.100]]></ESSSERVER>
<ESSSERVERJAPI><![CDATA[http://192.168.72.100:9000/aps/JAPI]]></ESSSERVERJAPI>
<JAVALOG><![CDATA[D:\\AutomationsV2\\Scrambler\\LOGS\\essbase_tuner_java_log]]></JAVALOG>
<SCRAMBLESOURCEAPP><![CDATA[SFDCRev]]></SCRAMBLESOURCEAPP>
<SCRAMBLESOURCECUBE><![CDATA[FinStmt]]></SCRAMBLESOURCECUBE>
<SCRAMBLETARGETCUBE><![CDATA[FinScr]]></SCRAMBLETARGETCUBE>
<SCRAMBLEDATALOCATION><![CDATA[D:\\AutomationsV2\\Scrambler\\Data]]></SCRAMBLEDATALOCATION>
<DELIM><![CDATA[ ]]></DELIM>
<EXCLUDEDIMLIST><![CDATA[Attribute Calculations,TimePeriod]]></EXCLUDEDIMLIST>
<EXCLUDEMEMBERLIST><![CDATA[Account,Currency,BusinessUnit,Entity,PCC,CostCenter,Scenario,Version,Year,HSP_Rates]]></EXCLUDEMEMBERLIST>
<ALIASESLIST><![CDATA[default]]></ALIASESLIST>
<OBJECTSLOCATION><![CDATA[D:\\AutomationsV2\\Scrambler\\Objects]]></OBJECTSLOCATION>
<OUTPUTLOCATION><![CDATA[D:\\AutomationsV2\\Scrambler\\Output\\]]></OUTPUTLOCATION>

  • ESSBASEUSERID, ESSBASEPASSWORD,ESSSERVER - these don’t require explanation
  • ESSSERVERJAPI - provider link
  • JAVALOG - a mask of the log file name. This mask will be appended with a timestamp. It also defines the path where the logs are written.
  • SCRAMBLESOURCEAPP - source application
  • SCRAMBLESOURCECUBE - source cube. It will be copied inside source application, and its copy will be scrambled.
  • SCRAMBLETARGETCUBE - the name of the copied cube. All update operations will run on this cube.
  • SCRAMBLEDATALOCATION - location with data files. All files in this directory will be scrambled and written into output directory, if an argument “data” is passed.
  • DELIM - delimiter of the data file.
  • EXCLUDEDIMLIST - list of dimensions excluded from scrambling. At the minimum it should include “Attribute Calculations” dimension if attribute dimensions are present. This is a built-in dimension, not visible in the outline, and cannot be renamed. You should consider keeping TimePeriod dimension as well, since it is used in the load rules.
  • EXCLUDEMEMBERLIST - list of members excluded from scrambling. Should contain dimension root members (top nodes). Otherwise it will be difficult to talk to the application owner. How would you explain which dimensions should be reordered, if you don’t know dimension names?
  • ALIASESLIST - list of aliases tables. All member aliases specified by these tables will be deleted during scrambling. If some alias table exists in the outline, but not specified here, those aliases will not be deleted, and will be exposed.
  • OBJECTSLOCATION - all objects in textual format in this location will be scrambled.
  • OUTPUTLOCATION - scrambled data file and objects will be written into this location.

List Outline Members, Generate HashMaps


The purpose of this step is to:
  • Get all members from the outline
  • Generate new member names
  • Populate LinkedHashMap with old and new members
  • If member contains formula, add it to another LinkedHashMap
  • When all members where read from the outline, and HashMaps populated, update formulae with new member names.

First we need to get the list of all members from the outline. The basic code to extract the members comes with Essbase Java API sample files. You can find lots of examples in Middleware/EPMSystem11R1/common/EssbaseJavaAPI/11.1.2.0/samples/japi. The code we are interested in - is in ViewOutlineTree.java file, specifically two functions:
  • listOutlineMembers(): gets the list of all dimension root members (top nodes), and passes each one of them into listOutlineMembers_helper function.
  • listOutlineMembers_helper(): a recursive function that:
    • Gets properties of current member
    • Calls itself for each child of the current member.

We need to add a few modifications to those two methods. Please see the comments in the code block for explanations:

   static void listOutlineMembers_helper(IEssMember mbr, int cntTabs)
           throws EssException {
       
    String memberName=mbr.getName();
       boolean fetchAllProps = false;
       mbrCounter++;
       /**
        * New member name is generated here.
        * If current member is not in the exclude list,
        * we add old member name and a newly generated
        * member name into memberNameMap LinkedHashMap.
        * We also get a formula, and if it is not null,
        * add it to memberFormulaMap LinkedHashMap together
        * with the old member name.
        */
    newMemberName=newMemberNamePrefix+mbrCounter;
    if(!excludeMemberlist.contains(memberName)){
        memberNameMap.put(memberName, newMemberName);
        String memberFormula=mbr.getFormula();
        if(memberFormula!=null){
        memberFormulaMap.put(memberName, memberFormula);
        }
    }

       /**
        * Below is a recursive section. We call listOutlineMembers_helper
        * itself for every child of the current member.
        */
    System.out.println("actual name: "+mbr.getName()+". scrambled name: "+newMemberName);
   
       IEssIterator mbrs = mbr.getChildMembers(fetchAllProps);
       ++cntTabs;
       for (int i = 0; i < mbrs.getCount(); i++)
           listOutlineMembers_helper((IEssMember)mbrs.getAt(i), cntTabs);
   }

Once we have two hashmaps from listOutlineMembers_helper, we need to update references in formulas with new member names. This is done in listOutlineMembers, immediately after Essabe disconnect:

            /**
            * Here we replace all member references in formulas with
            * new member names. There are 2 inner loops that replace first
            * the members enclosed with double quotes, and then members not
            * enclosed with double quotes.
            */
           for(Map.Entry<String, String> entry : memberFormulaMap.entrySet()){
        currentMemberFormula=entry.getValue();
        for(Map.Entry<String, String> entry2 : memberNameMap.entrySet()){
        currentMemberFormula=currentMemberFormula
        .replace("\""+entry2.getKey()+"\""
        , "\""+entry2.getValue()+"\"");
        }
        for(Map.Entry<String, String> entry2 : memberNameMap.entrySet()){
        currentMemberFormula=currentMemberFormula
        .replace(entry2.getKey(), entry2.getValue());
        }
        memberFormulaMap.put(entry.getKey(), currentMemberFormula);
       }
      

Rename members, update formulas


The next step is to update the member names and formulae in replica of the database. Below is the entire method that does that.

 static void UpdateMemberNames() {


       String baseCubeName = params.get("SCRAMBLETARGETCUBE");
       String appName = params.get("SCRAMBLESOURCEAPP");
       String cubeName = params.get("SCRAMBLESOURCECUBE");
       String s_userName=params.get("ESSBASEUSERID");
       String s_password=params.get("ESSBASEPASSWORD");
     
String s_provider=params.get("ESSSERVERJAPI");
String s_olapSvrName=params.get("ESSSERVER");
IEssbase ess = null;
       IEssOlapServer olapSvr = null;
       IEssOlapApplication app = null;
       IEssCube cube = null;
       IEssCube targetCube = null;
       IEssCubeOutline otl = null;

       
       
       String targetCubeName="";
       IEssMember mbr1=null;
       date = new Date();
System.out.println("starting outline update:"
+dateFormat.format(date));
     

       try {

           // Create JAPI instance.
           ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);

           // Sign On to the Provider
           IEssDomain dom
               = ess.signOn(s_userName, s_password,
                false, null, s_provider);

           // Open connection with olap server.
           olapSvr = (IEssOlapServer)dom.getOlapServer(s_olapSvrName);
           olapSvr.connect();


           targetCubeName=baseCubeName;
           app = olapSvr.getApplication(appName);
           cube = app.getCube(cubeName);
           /**
            * If target cube exists, delete if first.
            * Loop through all members in the memberNameMap
            * hashmap. Members not in the hashmap remain unchanged.
            *
            */
           CheckDeleteExistingCube(app, targetCubeName);
           cube.copy(targetCubeName, app);
           targetCube=app.getCube(targetCubeName);
           otl = targetCube.openOutline(false, true, false);
for(Map.Entry<String, String> entry :
memberNameMap.entrySet()){
mbr1 = otl.findMember(entry.getKey());
           mbr1.rename(entry.getValue());
           /**
            * For each alias table specified in the properties file
            * delete alias, if it exists.
            */
           String[] aliasesList=params.get("ALIASESLIST").split(",");
           for (int i = 0; i < aliasesList.length;i++){
            if(mbr1.getAlias(aliasesList[i])!=null)
            mbr1.deleteAlias(aliasesList[i]);
           }
           
           /**
            * If formula exists for current member,
            * Update it with a new formula.
            */
           if(memberFormulaMap.get(entry.getKey())!=null){
            mbr1.setFormula(memberFormulaMap.get(entry.getKey()));
           }
}
otl.save(IEssCube.EEssRestructureOption
.KEEP_ONLY_LEVEL0_DATA);
           
               } catch (EssException x) {
                   System.err.println("ERROR: " + x.getMessage());
               }
                   finally {
                       try {
                        otl.save(IEssCube.EEssRestructureOption.DISCARD_ALL_DATA);
otl.close();
cube=null;
                       app=null;
                       targetCube=null;
                       olapSvr.disconnect();
                       date = new Date();
            System.out.println("end outline update:"
            +dateFormat.format(date));
} catch (EssException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
               }
       }


Scramble Data And Object


Data and object scrambling works similarly to the methods we described. Once we have hashmaps for old and new member names and formulas, it’s a standard file manipulations in Java. For data scrambling you need to take into consideration format of your files. Standard essbase level 0 extract encloses member names with double quotes, and all members/data fields are space delimited.

Steps To Configure And Run EssbaseScrambler


  • First you need executable jar file. Unless you want to rewrite this program yourself (which is not a big deal really, considering most of the code is shared in this post), drop me a line, and i’ll send you the JAR file. Non-commercial use of this utility is free.
  • Define parameters in Params.xml
  • Extract level 0 data and put data files in Data folder
  • Prepare source application and database. It would make sense to make a copy of the application, and clear data (since you already extracted level 0 data).
  • Put objects (calc scripts, report scripts etc. in Objects folder)
Below is a screenshot of the folder structure.

  • Run the utility. Assuming essbasescrambler.jar is located in D:\AutomationsV2\Scrambler, you need to navigate to that folder (cd /d D:\AutomationsV2\Scrambler), and execute the following command:

java -jar essbasescrambler.jar D:\\AutomationsV2\\Scrambler\\Params.xml outline data

  • You can pass the following parameters:
    • Location of the XML file with all the parameters: mandatory.
    • outline - if outline needs to be scrambled.
    • objects - if objects need to be updated with new member names. If only “objects” parameter is supplied, it will also scramble the outline.
    • data - outline - if data needs to be scrambled.
  • Verify data and objects were scrambled in Output folder, and open outline in EAS to verify outline was scrambled.
  • In case of error check the latest execution log in the Logs folder.

Assumptions And Prerequisites



First let's make a few assumptions:
  • Calculation scripts or other objects do not reference members by their aliases.
  • Members in data files are enclosed with double quotes.
  • If you don't have Java path in path environment variable you can call the jar file with full path to java.exe. Something like this (notice double quotes around the path - due to space in the path):


"C:\Program Files\Java\jre1.8.0_45\bin\java" -jar essbasescrambler.jar D:\\AutomationsV2\\Scrambler\\Params.xml outline data


  • You have sufficient timeout interval for you Provider Services. Those are the same settings you define for your long SmartView queries.
If you have a huge outline (or a slow laptop from which you run this utility), and it takes an hour to update all the members, then make sure the following registry values are set to at least 1 hour [HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\InternetSettings]\ :
    • "ReceiveTimeout"=dword:3600000
    • "KeepAliveTimeout"=dword:3600000
    • "ServerInfoTimeout"=dword:3600000


Known Issues

  • If you are on 11.1.2.1 you may get an error: “Concurrent requests on the same session are not allowed. Wait till the previous request completes processing or kill the request”. This is a known Provider Services bug, documented here.
Resolution:
Install 11.1.2.1.102 Patch Set Update (PSU).


Have fun scrambling!

3 comments:

  1. Hello Dmitry, good morning.
    The Scrambler is not scrambling the data just really the metadata.
    Can you please advise on how to make it work for data as well?

    Regards,

    Jorge

    ReplyDelete
    Replies
    1. Hi Jorginho,

      Please shoot me an email with any error you get to hookmax@gmail.com and let's see what that is about. Also you can check if you followed the steps here:
      http://hookmax.blogspot.com/2015/09/scramblerug.html

      Delete
    2. Hi Jorginho,

      Please shoot me an email with any error you get to hookmax@gmail.com and let's see what that is about. Also you can check if you followed the steps here:
      http://hookmax.blogspot.com/2015/09/scramblerug.html

      Delete