Differences between Essbase calculation scripts and Business Rules

Calculation scripts

Script is either specific to database or application in Essbase.

Use substitution variables.

Scripts can be executed from Excel Add In / Smart View.

Business Rules

Easier way of implementing business logic either using script or graphical method.

Use Substitution variables.

Use run time prompts to narrow down calculation scope.

Cannot access from Excel Add In / Smart View.

Rule can be defined as global so every application can make use of it.

Happy Learning :)

What is (') Single quote and (") Double quote in Unix

Recently I got one requirement, in which I have to write a Unix shell script which does multiple things such as calling the ESSBASE report script objects and those report scripts should be generated on the fly based on the arguments passed by the user,

Here is the main task where I found very difficulty.

While I am passing the arguments to the script, I tested those arguments by passing them in the single quote to ensure the integrity of the parameters. The later part of the script will call other script using the same arguments and there also I followed the same method by passing the arguments in single quote.

UNIX is very sincere here; it didn’t work because the perception of UNIX for each special character is quite situational.

Here, I came to know how the single and double quote works.

UNIX calls single quote as Strong quotes which shows the arguments integrity at higher level but those characters will not holds good at the lower levels of the script, here you need to use the double quotes which are weak quotes

Single quotes (‘) - Strong quotes.

Double quotes (“) - Weak quotes

Back tact (`) - Command quote.

E.g: abc.cmd ‘xxx yyy ‘yyy zzz’ ‘zzz aaa’ ‘aaa bbb’ (passing argument to a script in single quotes.)

Xyz.cmd which will be called inside abc.cmd with arguments in double quotes. “$1” $2” “$3” “$4”

Happy Learning :)

How to create Dimnesions, Members and its Properties using rule files in ESSBASE

ESSBASE allows us in two ways to load the data.

1) Free form loading (without rule file) 

When you have the data file which is in compatible with the ESSBASE outline , then you can load the data file without using rule file which is called free form loading and it can't create new members in the outline.
2) Rule file loading (with rule file)

Using the rule files you can format the data file according to outline structure and you can build the new dimensions and members which are present in the data file.
But some situations demands me to build the members along with the different consolidation operators(+,-,% etc) and member properties (Dynamic calc, Dynamic calc and store etc) , it looks like difficult and different but it is very easy to achieve using some additional alphabets in the rule file.

here is few alphabets which stands for the property change and their purpose:

in the same way you can have different consolidation operators such as

Now you have symbols for property change but you receive the data from external systems and you shouldnot do any modifications to the data file, then how to achieve this, very simple don't worry!
1) Import the data file as it is into the rule file
2) Generation reference build method is suggested one.
2) Select the column where you want to insert the property.
3) Use the create using text function which is available in the rule file field menu.
4) Write the required Alphabet/Symbol as shown in the above tables.
5) Verify and Save the rule file.

Now do the dimension build you can see the member assigned property for the newly build members and same file can be used to load the data with some little modifications.

Happy Learning :)

Unlock app after specific time in ESSBASE

ESSBASE allows only one user at a time to do any modification in the Outline.
If any active connections are there while starting dimension build then the process will get fail,
during the dimension build if any user tries to access the outline then also dimension build process will get fail.
To avoid this ambiguity before starting the dimension build process just unlock the object from all connection and then start the process.
Here is the simple process how to do the unlock, the code is in maxl statements.
#!/bin/ksh

#########################################################################
# Script designed to make sure there are no more connections to a cube
# after a set waiting period
#
#
# 1. Disables logins
# 2. Wait for # seconds
# 3. Force logout/kill of all sessions to a cube
#
########################################################################## Validate command line arguments
if [ $# != 4 ]
then
echo "Insufficient arguments.."
echo "Usage: waitAndClearSessions.cmd env app_name profile secondsToWait"
exit -1
fi

FRENV=$1
APP=$2
PROFILE=$3
SEC=$4

. /clocal/fme/${FRENV}/forecast/${APP}/init/${PROFILE}.profile

# First login and disable additional connections then kill any connections
# not running a calcessmsh << end_mshcmd1
login $USER_ID $USER_PWD on $SERVER;
alter application '$APP_NAME' disable connects;
alter system logout session on application '$APP_NAME';
quit;
end_mshcmd1

# Now wait a given number of secondssleep $SEC

# Now force a logout of any remaining sessionsessmsh << end_mshcmd2
login $USER_ID $USER_PWD on $SERVER;
alter system logout session on application '$APP_NAME' force;
quit;
end_mshcmd2


Happy Learning

Essbase Read and Write accesses in Security

When I had discussion related to security in Essbase with my friends, to be precise which access between Read and Write takes precedence and when. I had no perfect clue to confess the statement.

I had created one user and took him to the different tests and convinced my self with valid statements, I thought this post will help you as well.

The data looks good and it allows me to do lock and send the data even when I used admin credentials, of course this is known fact! Then

Now I have created user and assigned to one group that has got Read access, Essbase pops me the below box when I tried to lock and send the data using new user credentials.

Group Read access got precedence over user none access.

I gave write access to user still he is part of group that has got Read access now user updated the data.

Now one point is confirmed Write dominates the Read access

User has read access but group has write access

User has write access but group has read access

Now we can add little more confusion to the above points with below statements

When user assigned to two groups one is Read and other is Write access then user access dominates the remaining.

None takes precedence over Read and Write access.

Write takes precedence over Read access.

Thanks for everyone who helped me to write this post and Happy learning :)

One of the report performances tuning technique in Essbase

It is pretty know fact that Essbase allocates 8byets of memory for each data cell and no matter whether the data value is zero (0) or other numeric values.

We had one challenging situation where in most of the member combinations have zero values and it was bugging time during the report pull, we had given a thought on zero’s to improve the performance as a result we had implemented one calculation script that checks for zero’s, for the members which falls in the report scope and replacing with #Missing as below and it is working fine, thanks to google.

Down the line I have got the same result by implementing other logic (a/a)*a = a, now it acts as a supplement for the first one.

Thanks for all who helped me to write this post, Happy learning :)

NetRetry Count Issue in Hyperion Smart View

I think this is one of the best examples for uneven but most impacted issue for end users who are using Hyperion Smart View.

This issue comes into picture due to large Essbase queries and slow internet connections because of that end users can’t retrieve the data.

This issue can be addressed by doing some changes in the registry.

Step 1: Close all Microsoft application and internet browsers as well.

Step 2: Make a note of below entries in the image and save a file with .reg extension instead of .txt

Step 3: Double click on the filename.reg and try once now

Step 4: It is always recommended to restart the system once you do any changes in the registry.

What is the use of Rule files in Essbase?

• To ignore the fields that do not need to be Build and loaded into cube.
• To concatenate two or more fields to one.
• To add dynamic meta data to the outline.
• To remove the unnecessary records during load.
• To change the scaling of decimal numbers.
• To remove the header from the input files.
• To add aliases and update the member storage properties.
• To reject invalid data records.

and many more you can do with rule files.

When do I use IF and FIX in Calculation scripts in ESSBASE

In general you should try and only use

"FIX" statements on Sparse dimensions.

"IF" statements on Dense dimensions.

FIX statement allow you to take slice of the database and only perform an action on that slice.

If you FIX on a sparse dimension, then you are limiting the amount of blocks that you are performing action upon. If you fix on Dense dimension, you are limiting the number of blocks, but rather just a portion within each block.

Using FIX statement on a dense dimension can cause multiple passes through the same block which is very inefficient. On the other hand, the IF statement is well suited to Dense dimensions because it performs IF logic while the block is in memory.

Ex: IF Jan

do.......

ELSE

do......

ENDIF

here the calculations will be done in one pass without the need of multiple passes through the blocks

What is DIRTY and CLEAN block concept in ESSBASE

Executing calculations that only run on blocks that have changed is great feature of ESSBASE, It enables administrators to calculate the database in a fraction of the time and is referred to as calculating "dirty" blocks, or update calc. This is awesome "Why shouldn't I use it all the time?"


This feature in ESSBASE is called as Intelligent calculation.


The calculation engine first calculates the level 0 block of first sparse dimension, it then traverse to higher levels and moves through the dimension from top to bottom until the entire dimension is consolidated.

When a level 0 block is changed( here New York is level 0) , it and all of parents, are tagged as dirty, when a calculation is executed on just dirty blocks, the process is same except that it skips all the "clean" blocks. Once the block is calculated the dirty tag is changed to clean...

Let see what happens when a level zero block becomes dirty:

In the above picture New York is updated. Block 5,6 and 10 are tagged as dirty. The next calculation, if set to calculate only the dirty blocks, would only calculate blocks 5,6 and 10, in that order.


When a calculation has started, it identifies which blocks need calculated (5,6 and 10 in this example) immediately after that, it starts calculating block 5 and it will continue to calculate blocks 6 and 10.


Using the dirty calc feature is great too to have in your arsenal. It can save hours of processing time.
By default it is enables but you can turn off or turn on using SET UPDATECAL ON/OFF, which will turn on/off the Intelligent calculation.

Happy learning and thanks for all who supported me to write this post :)

Few Optimization Techniques in Essbase

With the essential features available in Essbase you can load the huge data to the Essbase cubes, Run the reports and you can perform the complex calculations also,

As you keep on adding the different features to your application the performance will get reduce. As i said Essbase came up with different features along with the different performance tuning techniques which makes the application best optimized.

The optimization can be done at many places such as

Outline Optimization

Data Load Optimization

Report Script Optimization

Calculation Script Optimization

Outline Optimization:

1) Arrange the dimension in "Hour Glass Model"

The Outline should starts with dense dimension with highest stored members and it keep going till the dense dimension with least stored members and then starts with sparse dimension with least stored members and it keep going till the sparse dimension with highest stored members.

2) Use the member storage properties efficiently.

If the dimension is to just host the different types of data such as scenarios, here there is no point in rolling up the lower values to higher level, in this situation you can tag the dimension as "Label Only" and assign the no consolidation operator to the members under it.

Some calculations really not required to stored the results in database at this point of time tag the concern members with "Dynamic Calc" property.

Data Load Optimization:

1) In data file, the fields should starts with sparse dimension members and then dense dimension members and then the data field.

2) If the same field is repeating in all the records in the data file, then try to ignore that field from fetching itself and keep that member in the "Header Definition", why means to save the buffer memory and it will increase data load process.

Report Script Optimization:

1) In the report script first specify the sparse dimensions and then dense dimensions, why means :Sparse dimension creates the data blocks within which the data cells are available, so specifying the dense first does not make sense. So to speed up the process specify the data blocks first(Sparse dimension) and then data cells (Dense dimensions).

2) The dimensions which are not required to display in the report put them in the page.

3) Use the special commands to increase the report performance

SUPMISSINGROWS : To Suppress the data missing rows.

SUPHEADING : To Suppress the headings.

SUPBRACKETS: To Suppress the brackets around the negative values.

SUPEMPTYROWS: To Suppress the empty rows.

Calculation Script Optimization:

1) Use the set commands to increase the calculation performance.

SET MSG SUMMARY : Set the message level to summary.

SET AGGMISSG ON : To avoid the aggregation of #missing values.

SET CACHE HIGH : To increase the bugger size.

SET NOTICE LOW : To set the notices to low.

2) Perform the calculations on only required part of database using FIX command.

Happy learning and thanks for all who supported me to write this post :)

What happens when restructuring the ESSBASE outline

Essbase allows you to perform restructure in three ways,

• Outline Restructure,
• Sparse Restructure,
• Dense Restructure.

The Essbase objects such as page file, index file etc will change according to your selection, the changes first done in temporary files, after completion of restructuring the temporary files will be turned to original files,

Available temporary files in Essbase:
otn/otm/otu : Outline file.

• pan : Page file (each page file size is 2GB, the total number of page file depends on the data)
• inn : Index file (this is only file)
• esn : Essbase kernel file
• tcu : Transaction control file.
• esr : Essbase root file
• ddm/ddn : Partition file

Lets discuss what happen according to the restructuring type:

Outline Restructure : I could say this is only the type of Restructuring in which only outline file will get impact, since this happens when outline has changes in Aliases, Member formula, Attribute members and others.

Sparse Restructure : As sparse dimension members creates the Indexes, so while performing the Sparse restructuring only the Index file will change along with Outline file.

Dense Restructure : I could say this restructure as FULL restructure since it consider all the changes in the outline, here all files Outline, Index and Page file will change.
The remaining files such as Partition file (If available), Essbase Kernel file, Transaction control file and Essbase root file will be common in all the types restructures.

Happy learning and thanks for all who supported to write this post :)

What is the difference between CALC ALL, CALC DIM and AGG in Essbase?

Each meant for the specific purposes:

AGG :
It consolidates the database values ignoring all member formulas.
It works on the sparse dimensions.
It is faster than CALC for less than 6 consolidation levels.

Syntax: AGG (Dimension list);


CALC ALL:
It calculates and aggregates entire outline database based on Outline.
Order of the calculation depends upon the characteristics of the dimensions in the outline.

Syntax: CALC ALL;


CALC DIM:
It calculates and aggregate values for all members in the specified dimensions.
Order of the calculation based on the Sparse and Dense types.
Ex : CALC DIM(dense1,sparse2,dense3,sparse4) then the order of the calculation is dense1,dense3,sparse2,sparse4.
If at all you want to specify your own order use separate function for each dimension.
CALC DIM(dense1);
CALC DIM(sparse2);
CALC DIM(dense3);
CALC DIM(sparse4);


Syntax : CALC DIM(dimension);

Differences between ASO and BSO

A very fundamental and frequent question which appears in all Essbase interviews is what is
the difference between ASO and BSO applications
Here are few differences between ASO and BSO
Essbase system has two distinct storage options Aggregate Storage Option
(ASO) and Block Storage Option (BSO) each one has its own unique significance.


Characteristics of ASO:

• High dimensionality.
• During the retrieval Essbase automatically consolidates the date even then if business requires to perform some calculations then you can use MDX formulae.
• Only one database can be created under one application.
• Mandate to fallow the naming conventions for Application name as Applications names should not be metadata, temp, log, default, replay.
• Dynamic time series and Time balance properties are not available.
• During the restructure Essbase prompts you to choose how to proceed restructure either by keeping the exiting data as it is or by clearing the complete data.
• Only one type of partition techniques available (Transparent)
• There is no concept of Sparse and Dense dimensions.
• No Boolean attribute tag.
• Only store data, never share, label only data storage properties are available.
• Data stored in the .dat files
• Few businesses use ASO as a reporting application that holds Actual and Forecast data.

Characteristics of BSO:

• Less number of dimensions but shows the business model.
• Special functionality for Accounts and Time dimensions like Dynamic time series, Timebalance, Variance reporting.
• 3 types of partitions Replicated, Transparent, Linked.
• Currency conversion is possible.
• There is no restriction of the number of databases under one application but performance costs.
• Complex calculations can be achieved using calc scripts.
• Data store in Page files and addresses being pointed out by Index files.
• It acts as database for the planning applications so apparently you can see it for forecast purpose.