Friday, March 27, 2009

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*/

7 comments:

  1. Excellent post Dornakal.

    I have some queries on this topic. Can you please clarify it for me.

    1.Do i need equal number of dimensions on both cubes?

    2.suppose i have equal number of dimensions on both cubes but dimensions are different how will it works?

    I think it is working fine in the 2 situation, but functionally is it right?

    my suggestion is can you please describe how many dimensions are there and what are that. so we get good clarity on the script.

    Many thanks,
    Mahesh.

    ReplyDelete
  2. Hi Dornakal,

    your posts are very interesting.
    Can please post some information about EPMA.
    I have some knowledge on EPMA.
    Can you please tell me how is it working.
    what are the situations we use EPMA.
    and something about Dimension library, Application library, Data Synchronization, Application upgradation and all.

    Thanks,
    Mahesh.

    ReplyDelete
  3. Mahesh, you don't need same number of dimentions for data copy or XREF.

    Dronakal (yes, I've been reading your blogs) here is a question for you.
    You are using "SET CREATENONMISSINGBLK ON;" don't this command create unnecessary blocks hence increasing size of your database?

    Second: What's the advantage of using script vs having @XREF in the outline? I can think of couple but would like to know what you think.

    Third: Are you using something like this in production and if so what is the performance. You know I asked you this before.

    Thanks
    Pranay

    ReplyDelete
  4. Hi Pranay,
    Thanks for your comments.

    You are using "SET CREATENONMISSINGBLK ON;" don't this command create unnecessary blocks hence increasing size of your database?


    You are right in your comment, but it is when you enable database wide in database settings.

    The purpose of SET CREATEONMISSINGBLK ON; is to create to new blocks for the XREF operation only. I also included SET CREATEONMISSINGBLK OFF; at the end of script so that it doesn't create unneccesary blocks.

    Yes we are using this script in production and don't have any noticible size increase because of this.


    What's the advantage of using script vs having @XREF in the outline?
    By using script we can run XREF on need basis, not every time when you do aggregation.

    Are you using something like this in production and if so what is the performance.
    Yes. we are using this script in production. our cube sizes are 4 GB and 6 GB and script takes less than a 5 minutes to complete.

    have a great day Pranay.

    ReplyDelete
  5. This creates a cube in the same application, but if we want to copy data to other application, how can we maupulate it.

    Example: We have two application (app1 & app2) with different dimensions, app1 have 10 dimensions and app2 have 8 dimensions. From app1 we need to extract all the level0 data and copy in the app2.
    Can you suggest me how i can go ahead with the XREF script. If possible can you give one sample script.

    Thanks
    Vivek

    ReplyDelete
  6. Hello Dornakal,

    I have almost the same question as Vivek where there are two applications and different number of dimensions in each. The data from application 1 needs to be transfered to application 2 everytime and this process should be automated. What are the different or best way to do this and how should we proceed? What are the things we need to know before choosing one way and what are the keys things to be kept in mind while implementing it? Please let me know your opinions.

    Thank you.

    ReplyDelete
  7. XREF can handle data transfer between databases with unequal number of dimensions easily.
    You just need to know which dimension member you need to send data into target.

    you can email your specific details at ppasup123@gmail.com

    ReplyDelete