Hi Folks,

My new OTN article was published today. The article describes the process of running focused calculations with Oracle Data Integrator (ODI) and pushing changed data sets into ASO/BSO cubes across the environment.

Again we are trying to optimize calculations, and limit them only to relevant data sets. This time not just in the context of one application, but in the context of entire Hyperion/Essbase ecosystem.

A small change in one database may require recalculation in many downstream application. And it is even more challenging to draw connections between affected data sets.

How can we run focused calculations automatically on multiple ASO/BSO applications with different dimensionality in particular sequence? Well, let me show you how deep the rabbit hole goes ;-)

You will also find a few cool features like:

Triggering ODI jobs from essbase calc script/Business rule
Passing Run-Time-Prompts from Business Rule to ODI
Automatic notification of the users when their Business Rule completes
Automatic queueing of Business Rules when concurrency is not possible
Monitoring of the queue, and custom job console

Please follow this link to read the entire article.

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"?>

<SET>

</SET>

</sequence>

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

On Linux

<?xml version="1.0" encoding="ISO-8859-1"?>

<SET>

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

Tuesday, December 1, 2015

OTN Article: Focused ODI Jobs on the Entire Ecosystem

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

Dmitry Kryuk