Essbase Data Load

Ever Essbase cube build to load data. There are several ways to load data in to Essbase database.

1. Manual Data Loading
2. Free form data loading
3. Data load using Rule file
4. Data load with header

1. Manual Data Loading

Essbase has option to load data manually. Right click on data base then select preview data.

 

If data is missing, drill down up to zero level of the data base and insert data. You can’t update data if you are not in zero level. 

 

 

After inserting data in to field click on update button.

You can see all fields those have associated formula gets updated automatically. When you complete data file right click on database and click on "Execute calculation".

 

To execute database calculation click OK.

You will have one calculated value.

  

2. Free form data Loading

If the source file format is 100% matching with the outline format, then only  you can load data from "Free form data loading" method. There is no need to create rule file.  If you have data file which have contents as per outline structure so Essbase can automatically map fields and load data.
First, to make sure you understand, when loading data in Essbase the data must contain a representative element of Metadata for each dimension in the target Essbase database's outline. 

 

Right click database and select load data.

Go to load data and select load only mode for loading data only.

Browse database text file.

No need to do select rule file change other option. 

 

Just click OK….

 

After success full loading of data right click on database and Execute Calculation.

 

  

 3. Data Load Using Rules File

Rule file use for filtering data and then load in to Metadata. Rule files use for mapping text file or source file with outline structure. The particular data will go to a particular field. First let’s clear all available data. Just right click on database and clear all data. 

Data deletion is a risky task so it will ask confirmation. 

Create a rule file for a respective data base file.

This is the data source file. This file is different with the outline structure. You need to build a rule file and specify that what data consist in which field?

 

 Set space as delimiter. Map all fields of data source file.

 

Once mapping has done, validate the rule file.

 

Select data file and rule file by using “Find Data File” and “Find Rule Files” button. Click OK......

 

Right click OK database and execute calculation.

 

Data gets loaded. 

 

4. Data load with Header

a)     The data source file has header also. There is no need to load header information.

 

You need to skip header row while installation.

 

Set mapping properties with data source file.

Validate the build rule.

 

Select both data source file and rule file and click OK.

 

You can see the data load successfully.

 

b)     This data load source file has different scenario. You can set number of rows in header tab. That number of rows will not load while loading data.

 

Map data source file

 

 

 Validate rule file

 

Select both data source file and rule file from “Find Data File” and “Find Rules File” button respectively. Click OK

 

You can check data loaded successfully.

 

Essbase Metadata Load

 Metadata is defined as data about data. Metadata is a record of all information of database. Suppose in relational database you have tables and their properties. Relational database has Columns and every column has their attributes like data type and size. Metadata for multidimensional database is information of database type, number of dimension and dimension properties.

Metadata Load through Rule File

1. Generation Reference
2. Level Reference
3. Parent Child
4. Add alias through rule file
5. Rejecting Metadata
6. Metadata load with Consolidation property
7. Metadata load with member formula
8. Metadata load with select and reject property
9. Metadata load through RDBMS
10. Metadata load with User Define Attribute

Generation Reference Method 

 
You need to build rule file to update outline structure in a particular field. You require a source file and one rule file which

Example:-
How to load new member in “Product” dimension?
Open the rule file.

First you should have one text file; there should be specific outline structure. This is the source file for generation reference.

 

Go to File >> New >> Rule File

Browse data file and open it on rule file.

Set delimiters as it in text file, it may be comma, Tab or All spaces. Through delimiters Essbase server understands difference between two fields.

Go to Field Properties and set field properties.

 

Select the dimension on which you want to update Metadata. Set field type as generation because when Metadata structure is in top to bottom level then use generation reference.

 There is number option where you need check generation level number or set generation in some case.

 “Dimension Build Setting” has meta data load methods.

Select right dimension and then select Use generation references in Build Method. If you select wrong build method you will have error at the time of Metadata load.
 

 

First validate all settings. If it shows warning or error you can correct.

 

Save the rule file, it will use while loading Metadata.

Now you have data text file rule file. To load metadata through rule file Right click on data base select “Load data” option.  Select loading Metadata mode will be Build only, because you are loading Metadata only. 

Browse and Select data file or source file then select respective rule file. Each rule file has one associated data file. 

Checks below information should correct path of data file and rule file.

 Click “OK”

This is final massage of Metadata load. If status is success then your Metadata load success fully . If it shows warning or error massage follow error massage shown in white panel.