Data transfer between two hyperion Essbase Cubes

Data Transfer from one cube to another.

There are different ways to transfer data from one cube to other.

1. Hyperion Application Link (HAL)

2.Export data using report script and importing data into new cube
3.Jexport
4.XREF

Today we will learn about XREF calc script, which is used by most who want to transfer data between cubes.

please find sample xref calc script below:

In this example I am trasfering payroll,social, bonus and headcount data from my main P&L (profit and loss) application to work force application.

The first step of XREF is to create a location alias of the source application. In this example my location alias is _LocAliasPL.

You can create location alias using EAS in the following way:
open the application
right click database
Click Edit
navigate to location alias.
Click to create location alias and give the details of the source cube.

/*XREF Calc Script Code*/

/*Information */

/*

Script Name : XREF

Created by : Dornakal, Hyperion Consultant, March 27, 2009

Purpose : Copy HR data from main application to work force application

Directions : Check location alias

Versions : Essbase 9.3.1

Assumptions : The Accounts, Time dimensions are Dense and the rest of dimensions are Sparse

*/

/*House Keeping*/

/*Set the calculator cache. */

SET CACHE HIGH ;

/* Display calculation statistics in application log. */

SET MSG Summary;

/* Display calculation completion messages in application log at 20% intervals. */

SET NOTICE LOW;

/*Turn off Intelligent Calculation */

SET UPDATECALC OFF;

/* Enables parallel calculation. */

SET CALCPARALLEL 4;

/* Baseline Fix */
FIX(HSP_INPUTVALUE, Local, USD, FINAL,Actual, FY08, &ActualMnth,&NxtYr,@RELATIVE(Cost_Center,0),"EMPLOYEES")

SET CREATENONMISSINGBLK ON;

"PayRoll" = @XREF(_LocAliasPL, "PRODUCT");
"Social" = @XREF(_LocAliasPL, "PRODUCT");
"Bonus" = @XREF(_LocAliasPL, "PRODUCT");
"Headcount" = @XREF(_LocAliasPL, "PRODUCT");

SET CREATENONMISSINGBLK OFF;

ENDFIX;

/*END MAIN SCRIPT*/

Sample Calculation Script

/*Information */
/*
Script Name : CopyAct2Fcst
Created by : Dornakal, Hyperion Consultant, March 16, 2009
Purpose : Copy Actuals to Current Forecast
Directions : Set substitution variables CurFcst, Actmnth,CY
Versions : Essbase 9.3.1
Assumptions : The Accounts, Time dimensions are Dense and the rest of dimensions are Sparse
Comments : This script copies actual data from actual scenario to forecast scenario; This rule should be run before every forecast.
*/




/*House Keeping*/

/*Set the calculator cache. */
SET CACHE HIGH ;

/* Display calculation statistics in application log. */
SET MSG Summary;

/* Display calculation completion messages in application log at 20% intervals. */
SET NOTICE LOW;

/*Turn off Intelligent Calculation */
SET UPDATECALC OFF;

/* Enables parallel calculation. */
SET CALCPARALLEL 4;


/* Baseline Fix on CurYear, Local Currency, and Level 0 cost center */
FIX(@LEVMBRS("Cost Center",0),Local)



/* Main Rollup */

/* Copies data for all existing employees of all Expense Accounts from Actual scenario , final version to Current Forecast and Working version */

FIX ("Existing_Employees", @IDESCENDANTS("Expense Accounts"), Jan:&Actmnth)
DATACOPY Actual->Final TO &CurFcst->Working;
ENDFIX


/* End of baseline Fix*/
ENDFIX;

What is Intelligent Calculation? Why should we care?

Developing calc scripts Series

What is intelligent calc? Why should I care?

A primary goal in calculation script development is optimization (elimination of extra passes through database index). To optimize calculation, you can use FIX and IF statements to focus calculations, or you can use an option called intelligent calculation.

When you perform a full database calculation, Essbase marks which blocks have been calculated. If you then load a subset of data, you can calculate only the changed data blocks and their ancestors. This selective calculation process is intelligent calculation.

By default, intelligent calculation is turned on. You can change the default setting in the essbase.cfg file or on a script-by-script basis wit the SETUPDATECALC OFF command.

Intelligent calculation is based on data-block marking, when intelligent calculation is active, during the normal processes, within the index file, blocks are marked clean or dirty.

Clean Blocks—Blocks that don’t require calculation
Dirty Blocks --- Blocks that require calculation.

When intelligent calculation is active, during calculation, Essbase looks for only dirty blocks.

Exceptions:
Even when the intelligent calculation is enabled, for CALC DIM statements that do not include all dimensions, Essbase does not use intelligent calculation process. Rather, Essbase calculates all relevant data blocks, regardless of clean or dirty status, and all data blocks retain their status, dirty or clean.

SET CLEARUPDATESTATUS AFTER is a calculation command that engages intelligent calc for any calc script, regardless of construction. Typically, you use this command where you cannot meet the conditions for a calc dim on all dimensions.

Example:

SET CLEARUPDATESTATUS AFTER
FIX(@IDESCENDANTS(“Q1”))
CALC DIM (Accounts);
ENDFIX

When you execute a calculation script that includes the SET UPDATESTATUS AFTER command, data blocks that are marked clean are not calculated and data blocks that are marked dirty are calculated and marked clean.

How do you force block marking without calculating?

SET CLEARUPDATESTATUS ONLY command instructs Essbase to mark as clean all data blocks that were previously marked dirty.


How do blocks become dirty?
In the following cases the data blocks are marked as dirty.
Block creation during data input
Data modification (Lock and send)
Creation or modification of descendant blocks
Database Restructure (both dense and sparse)

What are False negative and False positive?
Occasionally, clean data blocks are marked dirty (False negative). In such cases calculation efficiency suffers. A more serious problem, however, is a false positive condition, in which dirty blocks are marked as clean. In such case of false positives, data integrity can suffer.

When does False Positives arise?

Calculation only a subset of a data block:
Essbase marks at block level not at the cell level, so a calculation that I executed on a subset of cells can cause a false positive condition. Only a few cells are calculated but the block is marked clean, although uncalculated cells remain.

Using a FIX statement:

Ancestors of a dirty block are not marked as dirty until the descendant dirty block is calculated. A false positive can result if the descendant dirty block is calculated within a FIX statement that does not include the dirty ancestor block. After calculation, essbase marks the descendant block clean, and the ancestor block remains marked clean, although it should be marked dirty.

Following SET CLEARUPDATESTATUS ONLY with unrelated calculations :
You should follow SET CLEARUPDATESTATUS ONLY with repetition of the section of the script for which you want to force data block marking , A false positive can occur if SET CLEARUPDATESTATUS ONLY touches blocks that are otherwise dirty and that are not calculated.


The Intelligent calc can provide significant performance benefits kin certain situations but require vigilant maintenance of the clean and dirty status of data blocks to avoid skipping the wrong blocks on calculation.

The intelligent calc function most productively used in interactive or iterative situations in which small, incremental changes are made to a database and in which it is not necessary to recalculate the entire database. For example you can use intelligent calc in following situations:

During quarter close periods, allocation rates and adjusting entries may be update multiple times. You use intelligent calc to view update results without recalculating the entire database.
In budgeting or forecasting application, typically, many users update units and drivers on a regular basis. In such applications, for users to analyze the impact of their updates, a short calculation time is imperative.

Implementation Process for Essbase Database

Hi Guys,
good morning.
please find implementation process for Essbase database.


Essbase Database implementation include many steps. The process if iterative. Analysis of the results of one cycle may rise new questions, prompting for new define business requirements, which in turn may lead to changes in design.

Analysis and Planning:

1. Identify business results
2. Examine data sources
3. Analyze sample reports
4. Design Essbase Analytics outlines.

Database Creation:

1. Create Essbase outlines
2. Create Load rules
3. Create Calculation scripts

Deployment and Support:

1. Maintain Essbase outlines
2. Manage data flow
3. Analyze data
4. Provide management and user support.

hope this helps.

Automate HAL Load

Hi All,

good morning.

today we will see how we can autoamte HAL job (Flow diagram)

If you are loading a large number of members, HAL chokes if you don't automate it (.exe etc).

Here are the steps one should follow to autoamate a HAL job.

Step:1

Complete the flow diagram as shown below

Step:2
Drag and drop Window Executible from Palleate.

Step:3
Open the window executible. Go to Runtime Target tab.

give the location of the flow diagram.

Browse to the location, where you want to store the executible file.

Step:4
Check

• Use file name as runtime target name
• Automatically Build when OK pressed.

Step:5
don't check anything in Flow Diagrams tab as shown below:

Step:6

Don't check anything in Management tab as shown below:

Step:7
In Logging tab give the location of your log file.

Step:8
Don't check anything in Profile tab as shown below:

Step:9
Check

• Copy needed Vignette Business Integration Studio DLLs
• Run as Console application
• If you want status bar check that box

Step:10
Hit OK

Step :11

you will see your execution file in the list as shown below:

Step:12

you can give the location of executible file in your batch file and run it using scheduler like window scheduler.