mobileNavlogo
headerlogo

LOADING DATA INTO ESSBASE DATABASE in Hyperion

Blog Author

Tekslate

Published Date

21st September, 2018

Ratings

Views

778

  • Share this blog :

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”.
About Author
Authorlogo
Name

TekSlate

Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills .

Related Blogs

Write For Us