LOADING DATA INTO ESSBASE DATABASE in Hyperion

LOADING DATA INTO ESSBASE DATABASE:-

To load data into the database, we must build all dimensions the data will be loaded into a page file and indexes also we get created. When file page reaches 2 GB, it creates another file and so on. Same with the index file. The files are created in the following path.

ARBORPATH/APP/app-name/db-name/ESS000013 AG

ARBORPATH/APP/app-name/db-name/ESS000013 IN

Loading data can be done using any or one following methods.

1. Loading data using rules file.
2. Free from data loading.
3. through excel lock and send (or through analyzer)

It’s recommended to load data an level 0 member combination in BSO (block storage) where as in ASO, we can load data in level 0 member combination only.

1. LOADING DATA USING RULES FILES:-

This data the in prepared assuming that there are 4 dimensions in the outline. For example, products, time, measures, geography. At least one member of each dimension must exit in the source file to load data into Essbase database.

A sample source file is shown below.

JAN 2007 AP COCKSALE   1000

JAN 2007 AP PEPSISALE   1200

JAN 2007 AP COCKSALE1000

JAN 2007 AP COCKSALE1200

• Open data prep editor (right click on rules files and click create rules files)
• Click file->open data file
• Make sure data load fields button is selected
• Select field properties
• Select data load properties tab
• For this example select time for the first column (by double-clicking it)
• Click NEXT select geography
• Click NEXT select products
• Click NEXT select measures
• Click NEXT all dimensions are already selected . just select the DATA FIELD CHECK BOX. Don’t select any dimensions
• Click validates and saves the rule files.

After creating the rules file, right click on the database (on the database that is created)

• Click load data. Another screen appears.
• Click find data file and locate the source file
• Click find rules file and locate the rule file
• Select overwrite check box
• Click OK.

2.FREE FROM DATA LOADING:-

Loading data without using any rules file is called free from data loading. The source file must follow the below rules to free from data loading

• The member name must be enclosed in double quotes if the member has extra spaces in between or some special characters in member name.
• Tab delimiter must separate the fields.
• The member from all dimensions must be present in the source file. If any of the members from any dimension are missing, then it is not possible to load the data.

EXAMPLE SOURCE FILE:-

“JAN 2007” AP COCKSALE   1000

“JAN 2007” AP PEPSISALE   1200

“JAN 2007 “ AP COCKSALE1000

“JAN 2007” AP COCKSALE1200

RIGHT click on the database (on the database that is created )and

• Click load data another screen appears.
• Click find data file and locate the source file
• Select overwrite checkbox
• Click OK.

NOTE:-it is not possible to do free from data loading. If the source is SQL DB or Excel files

3.HANDLING DUPLICATE RECORDS IN THE SOURCE FILE WHILE LOADING DATA:-

If the source file contains duplicate records. There are options to take of are in this rules file. Essbase creates a cell for each unique member combination and stores the data value in the page file. If the same combination repeats, then it has to either overwrite or it has to add existing values to subtract from existing values. We can do this in the rules file

JAN 2007 AP COCKSALE   1000

JAN 2007 AP COCKSALE   1200

In this example, the same combination is repeated. If we selected add to existing values.

Then the values will be 1200. If we selected overwrite existing values, then the final value will be 1200

• Open data prep editor
• Click options ->data load settings
• Select options” overwrite to existing values” or add to existing values” or “subtract from existing values”.