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.

Migration of DataForms in Hyperion Planning

The goal of this post is to explain how to migrate data forms from one environment to another in planning applications. The automation of this process helps to reduce time and leaves no scope for human error.

FormDefUtil.cmd utility can be used to move data form definitions from one Planning application to another. Data form definitions can be exported or imported from an XML file. This utility is extremely useful when Data forms need to be moved from a development environment to a production environment.This utility uses a command line interface and is installed in the bin directory (D:Hyperion\Planning\bin). This utility runs only on Windows-based systems (even if application servers are on UNIX). Only administrators can run it. The utility is located on Planning server as shown below.



Steps to export Data Forms :

1. Launch the FormDefUtil.cmd utility from the bin directory using this syntax:

formdefutil export Formname/-all server name user name password application



The utility creates an XML file in D:Hyperion/Planning/bin and logs errors in FormDefUtil.log in the directory from which you run the utility (by default bin). You can copy the utility to any directory and launch it from there to save files to another directory.




Steps to Import Data Forms :

1. Launch the FormDefUtil.cmd utility from the bin directory using this syntax:

formdefutil import Location of Formnames server name user name password application




This completes migration of data forms from one environment to another in planning applications.

Automation of Dimension extraction

Hi Guys,
good morning.
today we will learn how to automate dimension extraction from essbase.
There is a very good utility called outline extractor in

http://www.appliedolap.com

Step # 1
As a first step you should install Outline extractor.





Step # 2

Build a batch file providing details of your application and database and dimension you want to extract as follows:

cd C:\Program Files\olapunderground\Essbase Outline Extractor

win C:\Program Files\olapunderground\Essbase Outline Extractor\exportdim.exe ServerName/AdminName/Password/ApplicationName/DatabaseName/DimensionName/Path where extracted file to be stored/!/ Doc/11111111111111111111/Text




You can schedule the above batch file to run at fixed time using various schedulers like Window scheduler etc.

Hope this helps.
Have a BLESSED day.

Sample Report Script

Import Security in Hyperion Planning applications

Hi Guys,
good morning.
lot of times there will be a huge number of requests to grant or remove security access for users.
for one or two changes, it is simple task, click on member and then assign access and grant permission. It becomes tiresome when we need to make changes for large number of members.
it involves considerable amount of time.
Automating the security changes saves considerable amount of time. The ImportSecurity utility in planning loads access permissions for users or groups from a text file into Planning. Importing access permissions using Import security utility overwrites existing access assignments only for imported members, users, or groups. All other existing access permissions remain intact. The SL_CLEARALL parameter clears all existing access permissions.

ImportSecurity utility is located at D:\Hyperion\Planning\bin on Planning server as shown in the following picture:



Steps for importing access permissions:

Step 1: Create a text file and name it as Secfile.txt and save it in bin directory (D:\Hyperion\Planning\bin). Example of Secfile.txt is shown in picture below where Planning_Security_Group is group name, MemberName is member name, Write is Access permission, and MEMBER is relationship.




Step 2: Locate the ImportSecurity utility by navigating to the bin directory.


Step 3: From the Command Prompt, enter this case-sensitive command, one space, and the parameters. Separate each parameter with a comma, and enclose parameters with double quotation marks:

ImportSecurity.cmd “appname,username,password[delimiter],[RUN_SILENT],[SL_CLEARALL]”

Where:
appname : Name of the Planning application importing access permissions.

username : Planning administrator user name.

password :Planning administrator password.

delimiter (Optional) : SL_TAB, SL_COMMA, SL_PIPE, SL_SPACE, SL_COLON, SL_SEMI-COLON. If no delimiter is specified, comma is the default.

RUN_SILENT ( Optional) : Execute the utility silently (the default) or with progress messages. Specify 0 for messages, or 1 for no messages.

[SL_CLEARALL] (Optional): Clear existing access permissions when importing new access permissions. Must be in uppercase.


You can check the results in log file in bin folder.

Load dimension members into Planning Application using HAL from Relational Database

Hi Guys,

Good afternoon.

please find the process of loading metadata into planning application using HAL (Hyperion Application Link).

In the example below, I am loading employee dimension using HAL from SQL server table.

You can also load it from flat file (text file or .csv file)

I am using following adapters to complete loading:

1. Variable


2. Planning Adapter and


3. ODBC adapter.

The complete loading flow diagram is shown below:

The first adapter is Variable adapter.

we use this adapter to give log in information and application details.

The second adapter is Planning adapter. We use this adapter as a connector to Planning application. This adapter is fed from variable adapter. Make sure that you change setting to connector in this adapter.

In the General tab you can give custom defined name to adapter like Planning Connector etc.

In the Methods tab you should specify which dimension you are loading in the drop down menu. In this example we changed the dimension to employee as we are loading meta data related to employee.

The third adapter is planning adapter again. Make sure that you change the drop down to load as shown below:

You can specify the name of the adapter. For example Planning load adapter etc.

In the methods section you should specify the dimension you are loading to. In this example we are using Employee.

The last adapter is ODBC adapter.

You should create a DSN for the relational database in the location where you are running the HAL job. Then connect to the relational database.

Select the table which has metadata information.

Once you have all the information. Connect to ports as shown below and save and then run to load metadata.

Hope this helps.

Hyperion Planning Certification Help

Hi Guys,
here are some planning questions which could help you get certified in Hyperion Planning Certification. I intentionally didn't post answers for most of them as you can find those answers in planning administrator manual. Hope this helps.

1. What are the required dimensions in planning?
2. What are the steps you need to take before creating planning application?
3. Which process state signifies that a planning unit is being reviewed by someone in the organization.
4. What file acts as bridge between Planning and relational data source?
5. When you tag a currency as a reporting currency, which dimensions will it be part of.
6. What is the maximum number of plan types you can have in planning?
7. Where are the rejected records after loading using HAL are placed in?
8. What are the different user roles in planning security?
9. Planning supports fiscal year calendars. It does not support mixed use of fiscal year and calendar year (True/False)?
10. What are the data sources supported by hyperion financial reporting?
11. Which type of tasks can you define in a task list?
12. What are the requirements for creating a dataform?
13. Row and column layout, business rules, POV definition, display options etc
14. Fiscal start of year and fiscal start month defines the starting fiscal year and start month for application. You cannot change this after you have created the application (True/False)?
15. To which application elements can you assign rights
16. When you enter plan data in the planning web client, planning automatically distributes values from :
    · Summary periods to base periods
17. Do you need to refresh your database once you modify the webforms?
18. How many relational databases are needed for an application, which has 4 plantypes
19. List of places where you can launch business rules from
20. What is the maximum number of dimensions that a planning application can have?
21. Which can be runtime prompts in business rules?
    Member or members
    Dimension
    String integer, real or percent
22. The POV is always set to _______ for each POV dimension
    Single member
    Multiple members
    none
23. What is the reason for having sparse dimension? Data values are not smoothly and randomly distributed throughout the database Data values do not exist for the majority of member combinations in the database
24. How many databases will be created for two plan types.
25. By default account and period are dense dimensions in planning application.
26. When creating planning application, what base time period allows you to select weekly distribution option? (Ans:Monthly)
27. In which dimension your exchange rates are stored?
28. An authentication directory is a centralized store of users and passwords, user groups etc.
29. What authentication directories are supported by hyperion planning?
30. Which type of tasks can you define in a task list?
    Dataform
    Descriptive
    Workflow
    URL
    Business rule
31. Because dimension members can belong to more than one plan type you can specify different aggregation options by plan type.
32. Where does planning store application definition?
33. What happens when you use dynamic calc for base-level members for which users enter data?
34. Which data form tasks can you perform in smart view?
35. Parent members set to dynamic calc are read only in target version.
36. How many user variables can you setup for each dimension?
37. A parent member set to label only displays the value of its first child member.
38. In multiple currency applications, you cannot apply the label only storage option to members of the following dimensions: Entity, Version, Currency and user defined custom dimensions. To store exchange rates, these members should remain Never Share.
39. What is the calculation order in essbase
    Account dimension
    Time dimension
    Other dense dimensions in top down order
    Other sparse dimensions in top down order
    Members that are tagged as two pass calcs
40. The Two pass calculation option is used primarily for members of the account dimension. For other dimension, the two pass calculation option is only valid for Dynamic calc or dynamic calc and store members.
41. What is the maximum number of alias tables you can have for a dimension?
42. What is a planning unit?
43. For all Hyperion system 9 products, all user provisioning and external authentication is handled through:
44. After entering or modifying data in a dataform, you must calculate new totals for parent members in the dataform.
45. When loading files that contain member properties, you must :
    Load parent members before children
    Have a separate load file for each dimension.
    
    
    

How to delete members in planning using HAL

Hi
good morning.
Deleting members in planning is challenging to do manually as it consume lots of time specially if you more than 5 members to delete. we can use Hyperion Application Link (HAL) to delete precisely the members we don't need.

Here are the steps to follow on how to delete members using HAL

Step:1
prepare a text document or .CSV file of the list of members you want to delete in Parent, Child format.

Step:2
Open a new flow diagram and add flat file adapter, Planning adapter and Variable adapter as shown below. When you select a Planning adapter, the names of ports and the number of ports are determined by the dimension to which members are deleted. Most ports reflect the properties and custom attributes of the selected dimension.




Step : 3
Open a Variable adapter and type Delete in port name column and
in the Initial Value column type

Delete Level 0 .........................(If you want to delete the member if it has no children)
Delete Idescendants ............(If you want to delete the member and its descendants)
Delete Descendants ............. (If you want to delete the descendants but not the member)




Step.4:
Upload the flat file with members to delete as shown below:



After the execution of HAL, you should refresh planning application to push the changes to essbase.

Automation of DataLoad

This document is intended to explain how to automate data load.

Step.1:
Create a data load rule in Essbase

Step.2 :

Create a Maxl file to execute the load rule

Step .3 :

Create a batch file to execute the MaxL file. We can schedule the Batch file using windows scheduler.


Example of MaxL code.
1.This MaxL code clears the existing data
2.Loads data
3.Aggregates the data.


Remember : You need clear the data before you load. We clear the data because of our requirements. You can just use step 2 and 3 load data and aggregate data.




/*Script Start */
/*Login */
LOGIN 'UserName' 'Password' ON 'Server Name';

/*Clears data */
execute calculation '
Fix (@idescendants("Account"),@idescendants(Entity),&Current Year,Version)
CLEARDATA Scenario;
Endfix;'
on ApplicationName.Database Name;

/* Imports Data */

import database 'ApplicationName'.'DatabaseName' data connect as 'UserName' identified by 'Password' using server rules_file 'LoadRuleName' on error
write to 'e:\\Logs\Errorfilename.err';

/*Aggregates Data*/
execute calculation'
SET AGGMISSG ON;
SET UPDATECALC OFF;
Fix (Scenario,Year,Version)
CALC DIM(Product,Entity);
Endfix;'
on ApplicationName.DatabaseName;

LOGOUT;
EXIT;
/*End MaxL Script*/


Batch File to call the MaxL Script and send email about status of the

Rem ******************************************************************************
Rem Object Type: Batch File
Rem Object Name: BatchFileName.bat
Rem Script Date: 01-27-2009
Rem Created by: Dornakal
Rem Purpose: This script loads the following data into Cube
Rem Changed By:
Rem Change Date:
Rem Description:
Rem ******************************************************************************
Rem This starts the log file
echo "Start of log" > E:\Logs\Dataload.log

Rem This calls MaxL script to load data


essmsh E:\Scripts\MaxL \DataLoad.mxl >> E:\Logs\Dataload.log

Rem Send mail about the status of the job


sendmail -b E:\Logs\Dataload.log -s "Subject of the mail (data load status)." -f Sendersemail -r Recievers email -r Receiver’s email -X HQSMTP.yourcompany.net

Certification Questions

Hi All,
please find some sample questions, which can help you to get certified in Essbase.

1. Development tool used to build application which can add or delete dimensions.
2. essbase quick start when is it used
3. multiple fix – dense or sparse
4. multiple if – dense or sparse
5. Member selection wizard in excel. Placement
6. When are hash tables used
7. Number of passes through database (two pass calc) – 2questions
8. Query designer – what cannot be assigned to filter access
9. Sequence of cache files to be optimized based on their priority
10. What causes fragmentation?
11. Which compression type to use based on the statistics information of the database
    is incremental restructuring deferred when u add, move or delete a member of a sparse dimension
12. What command do you use to optimize data export
13. Max .pag file size
14. Given a load file identify what field or load method should be used.
15. Attribute dimensions
16. What cannot be linked to a cell with an LRO
17. When would you select Shared Member functionality, as opposed to Attribute dimension functionality?
18. Proper use of substitution variables in the cal scripts
19. Variance calculation – when is it used. Result
20. Given an exhibit identify the improper use of label only tag
21. What are the minimum security settings for LRO’s
22. Accept and reject records based on rules
23. Data source 3 partitions, data target updated. How many .CHG files are updated
24. Given settings, what is the optimal outline
25. Which partition uses less synchronization
26. Given 2 exhibits from excel, which exhibit will be retrieved
27. hashtbl theory
28. Set commands – 3 questions
29. Dynamic calc and dynamic cal and store properties interchanged what kind of restructuring happens
30. Attribute calc dimensions
31. Use of attribute dimensions in partitions
32. Identify how many blocks are created given database statistics
33. How many index files will be restructures
34. Validate command
35. Which access a filter can have
36. Fix command and number of passes
37. Fix with datacopy
38. Fix with cross-dimensional operator
39. frmbottom up command
40. Given an outline which dimension is tagged as label only
41. Can u load data into attribute dimension
42. Datacopy syntax
43. beginarchive and endarchive commands
44. net delay command
45. query designer filter access issues
46. Which file holds uncompressed data blocks
47. Fragmentation, causes prevention and resolution
48. Essbase member selection placement in query designer of spread sheet
    

Introduction to Hyperion Reports (Boot Camp)