Thursday, September 17, 2015

Essbase Scrambler User Guide



Summary
Preparations
Download
Extract
Copy Objects And Data
On Windows
On Linux
Updating XML parameters
On Windows
On Linux
Running Utility
On Windows
On Linux
Review Results
Data Comparison
Objects Comparison
Outline Comparison
Assumptions And Prerequisites
Known Issues


Summary

This post is sort of user guide for Essbase Scrambler, which you can download here, and read about here.
Just in a few words Essbase scrambler is a utility that scrambles or encrypts essbase cubes including outlines, objects such as calculation scripts, report scripts or just any textual object that contains member names. It also replaces data values in data files with random numbers.


Below are the screenshots with steps of how to setup scrambler on Windows and Linux.
At the bottom of the page I’ll keep the list of Q&A and known issues.


Preparations

I’ll be using this ArpaBI application as an example. Let’s take a look at it. It has some sample level 0 data, some calc scripts and a set of report scripts.


We will want the outline, data files, calc scripts and report scripts to be encrypted. As a preparation we will need to:
  • Make a full backup of the source application, or make a copy the source into a separate application. If you made a copy, run the scrambler on the copy, and not on the original application (we will call that copy a "scrambler source" further). And of cause, we assume all this exercise is done in Non-Prod environment.
  • Extract level 0 data from our source/source copy database.
  • Once you extracted level 0 data it is better to clear the data from the scrambler source. The first thing the scrambler does - it copies a source cube. If the source cube is 200Gb it’ll take a very long time to copy. Since we scramble data files, we don’t need data in target scrambled application. And again, this assumes you made backups, and you can restore the original aggregated state without any issue.


Download

Now we need to download the scrambler and extract it to some directory.






Extract

Lets say our extract path on Windows is D:\Scrambler.

Or for Linux this is /home/oracle/Scrambler.




Copy Objects And Data

Now we need to copy all the objects (calc scripts and report scripts into Objects folder), and data files into Data folder.

On Windows



On Linux





Updating XML parameters

You can use existing parameter templates ParamsIni.xml or ParamsNixIni.xml for Window and Linux respectively. Below are updated templates for my environment. In bold red are values I had to update. Notice that i have epm001:9000 in my environment. This is because i have foundation and Essbase on the same server, and the use the same port. Usually this value should be yourfoundationserver:19000 or something like this.

On Windows



<?xml version="1.0" encoding="ISO-8859-1"?>
<sequence>
<SET>
<ESSBASEUSERID><![CDATA[admin]]></ESSBASEUSERID>
<ESSBASEPASSWORD><![CDATA[xxxxxxxx]]></ESSBASEPASSWORD>
<ESSSERVER><![CDATA[epm001]]></ESSSERVER>
<ESSSERVERJAPI><![CDATA[http://epm001:9000/aps/JAPI]]></ESSSERVERJAPI>
<JAVALOG><![CDATA[D:\\Scrambler\\LOGS\\essbase_tuner_java_log]]></JAVALOG>
<SCRAMBLESOURCEAPP><![CDATA[ArpaBI]]></SCRAMBLESOURCEAPP>
<SCRAMBLESOURCECUBE><![CDATA[FinStmt]]></SCRAMBLESOURCECUBE>
<SCRAMBLETARGETCUBE><![CDATA[FinScr]]></SCRAMBLETARGETCUBE>
<SCRAMBLEDATALOCATION><![CDATA[D:\\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:\\Scrambler\\Objects]]></OBJECTSLOCATION>
<OUTPUTLOCATION><![CDATA[D:\\Scrambler\\Output\\]]></OUTPUTLOCATION>
</SET>
</sequence>


Save this file as Params.xml in the same directory.

On Linux



<?xml version="1.0" encoding="ISO-8859-1"?>
<sequence>
<SET>
<ESSBASEUSERID><![CDATA[admin]]></ESSBASEUSERID>
<ESSBASEPASSWORD><![CDATA[xxxxxxx]]></ESSBASEPASSWORD>
<ESSSERVER><![CDATA[epm001]]></ESSSERVER>
<ESSSERVERJAPI><![CDATA[http://epm001:9000/aps/JAPI]]></ESSSERVERJAPI>
<JAVALOG><![CDATA[/home/oracle/Scrambler/LOGS/essbase_tuner_java_log]]></JAVALOG>
<SCRAMBLESOURCEAPP><![CDATA[ArpaBI]]></SCRAMBLESOURCEAPP>
<SCRAMBLESOURCECUBE><![CDATA[FinStmt]]></SCRAMBLESOURCECUBE>
<SCRAMBLETARGETCUBE><![CDATA[FinScr]]></SCRAMBLETARGETCUBE>
<SCRAMBLEDATALOCATION><![CDATA[/home/oracle/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[/home/oracle/Scrambler/Objects]]></OBJECTSLOCATION>
<OUTPUTLOCATION><![CDATA[/home/oracle/Scrambler/Output/]]></OUTPUTLOCATION>
</SET>
</sequence>


Save this file as ParamsNix.xml in the same directory.


Running Utility



You can use a sample run command in run.txt.
I’ll need to open a command line and run this.


On Windows

cd /d D:\Scrambler
java -jar essbasescrambler.jar D:\\Scrambler\\Params.xml objects data




On Linux



Review Results

The scrambler creates a new timestamp folder inside an output folder.


All the scrambled objects and data files are written into that folder.




Data Comparison

If you compare original data file with the scrambled one you’ll see something like that:


Objects Comparison

If you compare calc scripts:




Outline Comparison

And finally, if you compare outlines and formulas:


Original:


Scrambled:


Assumptions And Prerequisites



  • 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:\\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).

2 comments:

  1. Looks brilliant! When is the one for ASO cubes going to be available!

    ReplyDelete
    Replies
    1. Thanks! By Kscope16 for sure, maybe sooner. Let me know if you would like to test it out.

      Delete