Hyperion Essbase Overview

please find some Q&A, which provide you with essbase overview.

What is Essbase OLAP server?
An OLAP server is a multidimensional database for storing data with an unlimited number of dimensions such as time, region, accounts, channel or products. Essbase server manages analytical data models, data storage, calculations, and data security.

What are the Essbase Application tools?
Applications tools are used for extending Essbase applications
Spreadsheet Add-in
Smart view for Microsoft applications
Currency Conversion
Essbase SQL interface
Essbase Application Programming Interface (API)

What is Partitioning?
Copying a slice of a large database to work with it locally, or link from your database directly to other databases.

What is a Hybrid Analysis?
Hybrid analysis integrates relational databases with Essbase databases to combine the size and scalability of the relational database with conceptual power and analytical capabilities of the multidimensional Database.

What are administrative requests and client requests?
Administrative requests such as
· Logging in and logging out
· Starting and stopping applications and databases,
· Viewing users security information
are handled by the Essbase server agent

Client requests such as
Data loads
Spread sheet reports
Data lock and unlock
are handled by the application server (ESSVR)

What is the use of multidimensional database such as Essbase?
Multidimensional database supports multiple views of data sets for users who need to analyze the relationships between data categories.
For example marketing analyst needs detailed information in different view than the manager.
Multidimensional DB consolidates and calculates data to provide different views. Only Database outline, the structure that defines all elements of the DB, limits the number of views
With the multidimensional DB users can pivot the data to see information from different view point, drill down to find more detailed information or drill up to see an over view.
Relational database have more data and have all transactions information. Whereas the Essbase has limitations on data which it can hold. In Essbase the filters (security) can be given until the data cell value. Whereas in RDBMS the security can be given only until table view. In RDBMS we can see only one view.

What are Standard dimensions?
Standard dimensions are those which represent the core components of the business plan an often relate to the departmental functions
Examples of standard dimensions are as follows
Time
Accounts
Products
Market
Dimension
Dimensions are static in most databases. DB dimensions rarely change over the life of an application.

What is an Outline?
Outline is the structure that defines all elements of the Database. It dictates how data is to be stored into the database. Outline defines the structural relationship between the members in DB. Organizes all data in the DB
Defines consolidations and mathematical relationships between members
It defines type of dimensions. Aliases, member formulas etc.
The order of the outline is that how dimensions are arranged is as follows:
Dense dimensions followed by sparse dimensions followed by Attribute dimensions.

How do you order the Outline?
All the attribute dimensions should be placed at the end of the outline. Dense dimensions should be placed in the first then followed by the sparse dimensions
The order determines
How quickly calculations are run
How long it takes users to retrieve information

The order of the outline for query performance
· Dense
· Most queried Sparse
· Least queried Sparse
· Most queried Attribute
· Least queried Attribute

The order of Outline for Calculation time
· Dense
· Smallest Sparse dimension
· Largest Sparse
· Smallest Attribute
· Largest Attribute


What is the highest level of consolidation in the outline?
Dimension

Is there any limitation on number of members?
No. Essbase does not limit the number of members within a dimension and allows you to add new members as needed.


Parent is a member, which has a branch below it.
Child is a member, which has a parent above it.
Siblings are the child members of same immediate parent, at the same generation.
Descendants are all members in branches below a parent.
Ancestors are all members in above a member.
Root is a top member in a branch.
Leaf member has no children.
Generation number refers to the consolidation levels within a dimension.
Level also refers to a branch within a dimension.

What is a cell/data value?
A data value is defined by the intersection of all standard dimensions in the database.

What is the maximum number of values in the database?
Product of members in each dimension

Why do we need to have classification as dense and sparse dimensions?
As the data is no smoothly and uniformly distributed and data does not exist for the majority of members. Essbase speeds up data retrieval while minimizing the memory and disk requirements.
A sparse dimension is a dimension with a low percentage of available data positions filled.
A dense dimension is a dimension with a high probability that one or more data points are occupied in every combination of dimensions.

What are the two types of internal structures in Essbase?
Data blocks
Index system
The two types of internal structures are to store data and access data.

What is a Data block?
Data block is a cube created for each unique combination of sparse standard dimension members (provided that atleast one data value exists for sparse member combination).

The Data block represents all the dense dimension members for its combination of sparse standard dimensions.

Its size depends on number of dense dimensions.

How does Essbase calculate the data?
Top down order

What is an Index entry?
The index entry provides a pointer to the data block
Essbase creates Index entry for each data block. The index entry represents the combinations of sparse standard dimensions. It contains an entry for each unique combination of sparse standard dimension members for which atleast one data value exists.

What happens if you make all dimensions sparse?
Data blocks are created for each unique combination of sparse standard dimension members. Thus a large number of dense blocks are created, and thus a huge index containing pointers (or addresses) for all those data blocks.

Huge index consumes large amount of memory. The more index entries the longer Essbase searches to find a specific block.

What happens if you make all dimensions dense?
Only one huge sparse block is created with only one index entry. This block is very sparse. This configuration requires thousands of times more storage than other configurations. Essbase need to load the entire data block into memory when it searches for a data value, which requires enormous amounts of memory.

What is the Design Process?
Analyze business needs and plan the database
Define the database outline
Check the system requirements
Load the test data into the database
Define calculations
Define reports
Verify with the users
Repeat the process

What are the different types of data sources?
Flat files (column formatted)
Spreadsheet files
Any RDBMS files

What is a shared member?
The shared member concept lets two members with the same name share data. The shared member stores only pointer to the data contained in the other member, so the Essbase only stores the data once. Shared members should be in the same dimension.

What dimension can be given time balance properties?
Only accounts dimensions can be given time balance, expense reporting, and country and currency properties.

What is the use of variance reporting?
Variance reporting properties defines how Essbase calculates the difference between actual and budget data in members with @VAR, @VARPER functions in their member formula.
· Expense reporting (Budget-Actual)
· Non-Expense reporting (Actual-Budget)
What is a Function?
It is a predefined routine to carry on specific task or calculation.

What is a formula?
Formulas are used to calculate relationships between members in the DB Outline.

What is a dynamic Calc?
When you tag a member as dynamic calc, Essbase calculates the combinations of the member only when user retrieves data, instead of pre calculating member combinations during the regular DB.
Dynamic calc shorten the regular calc time, but may increase the retrieval time for dynamically calculated data values.


What are the advantages of Dynamic calc?
Low disk space
Reduced database restructure time
Reduced back up time
Optimum regular calculation time

What are the members for which Two-pass calculations can be given?
Accounts
Dynamic calc
Dynamic calc and store

How does a user can view data?
Spread sheet
Printed reports
Reports published on web
web analysis
smart view


Can you load data or calculate data on client machine?
No.
Applications and Databases created on client machine are used only to store database objects, such as outlines and calc scripts. You cannot load or calculate data on a client machine.

What are Database Objects?
Files that are related to databases are called Objects. Common types of objects are:
Database Outlines (.OTL)
Report Scripts (.REP)
Calculation Scripts (.CSC)
Data Load rules and Dimension build rules (.RUL)
Data sources
Security definitions
LRO’s (Linked Reporting Objects)
Partition definitions

What is a rule file?
Data load rules are set of operations that Essbase performs on the data from external data source file as it is loaded, or copied into the Essbase database.
Specifying the data load rules is the most common way to load data into the Database.
Dimension build rule files create or modify an outline dynamically based on the data in the external source file.

What is a calculation script?
Calc script is a text file with set of instructions telling Essbase how to calculate data in the database. It has “. csc” extension.
What is a Report script?
Report script is a text file with a set of instructions on data retrieval, formatting and output to create a report from the database.
Report script has .REP extension.

What is a Linked Reporting Object (LRO)?
A LRO is an object associated with a specific data cell in the Essbase database. A LRO can any of the following:
A paragraph of descriptive text (” cell note”).
A separate file that contain text, audio, video or graphics.
An URL (Uniform Resource Locator).
A link to data in another Essbase database.

What are “.EQD” files?
Within spreads sheet add-in, users can create queries using query designer (EQD). Users can save the reports in the form of queries (.EQD files)

What are “.sel” files?
With the spreadsheet add-in, users can define member retrievals with the member select feature. If users want to save member select specification, they can do so with a “.sel” file.

How can you create a database?
Application manager file>new>database
Essbase administrative services console
ESSCMD (“CREATE DB”)
Maxl (“create database”)

Application and database names should be less than 8 characters.

create application------maxl
CREATEAPP------------ESSCMD

What is annotating database?
It is a database note that can provide useful information in the situations where you need to broadcast messages to users about status of the database deadlines for updates and so on

Select database>set note


What are substitution variables?
Substitution variables act as global placeholders for the information that changes regularly.
Each variable has a value assigned to it. The value can be changed at any time by the DB designer, thus manual changes are reduced. Ex :- currmnth.

You cannot use the substitution variables in formulae that are applied to the DB outline. The value of the substitution variable cannot be more than 256 characters.

Substitution variables can be used only in
Calculation scripts
Report scripts
Spread sheet add-in
SQL interface

Server>substitution variable
Maxl (for creating/deleting/updating)
Alter system
Alter application
Alter db
ESSCMD
CREATE VARIABLE
DELETE VARIABLE
UPDATE VARIABLE

What is a location alias?
A location alias maps an alias name for a DB to the location of that DB.
You can use location aliases only with the @XREF function.
With @XREF function you can retrieve data value from another database to include in calculation in the current database. In this case, the location alias points to the database from which the value to be retrieved.

Database>location aliases

Create
Maxl ----------------------create location alias
ESSCMD----------------CREATE LOCATION

Edit /Delete
Maxl display location alias
drop location alias

ESSCMD LIST LOCATIONS
DELETE LOCATION
What happens if you open outlines in two instances?
If you open same outline with two instances of application manager using same login id, each save will overwrite the changes of the other instance.

Copying database
Database > copy
Maxl create database as
ESSCMD COPYDB

What are important points while building an outline?
· All members and alias names should be unique;
· Only one dimension should be tagged as accounts, time, currency type and country;
· Level “0” members cannot be label only;
· Level “0” members cannot be assigned formulae but dynamic calc members of standard dimensions may have formula;
· Dynamic calc should not have more than 100 children;
· Boolean attribute dimensions have only two members.

What are the restructuring options in saving database?

· All data
· Level 0 data (when all data in the outline is at level 0)
· Input data (when you load data into non level 0 members)
· Discard all data (when you expect to reload data or when outline is radically changed that no existing data applies)

How do you set dense and sparse settings?
Settings>data storage
Data dictionary button
· You must set the standard dimensions with which you plan to associate attribute dimension as sparse because attributes can only be associated to sparse standard dimensions.
· Application manager automatically sets attribute dimensions as sparse.

How do you rename members?
· Data dictionary button
· Edit>properties
· Manually

When does a DB restructure?
When you add, delete, or move non-attribute (standard) dimensions or members, Essbase restructure DB and you must recalculate your data.

What is Metadata?
Metadata is data is data about data. Metadata is the data contained in the database outline that describes the values within a DB.
Ex:
East>New York>cola>sales>10000

East>New York>cola>sales> is metadata

What are different types of dimension tags?
· Time
· Accounts
· Country
· Currency
· Attribute

Can you add time members that are not tagged as time?
Yes

When do you give an accounts tag to a dimension?
You can tag a dimension as accounts if it contains items that you want to measure such as profit or inventory.

Time dimension tag says how often you collect and update data. The time dimension enables several accounts dimension functions such as first and last time balances.

What is the significance of time balance properties?
When you set a time balance property on a member in an accounts dimension, it affects how Essbase calculates the parent of that member in the time dimension.
· TB FIRST (The parent value is the value of the first member in the branch)
· TB LAST (The parent value is the value of the last member in the branch)
· TB AVG (The parent value represents the average value of the children)
· TB NONE (default; rolls up parents in the time dimension in the usual way)

Skip Properties
· None
· Missing
· Zeros
· Missing and zeros
Skip properties, expense reporting, Time Balance properties are applicable only to the accounts dimension.


What is a Two-Pass calculation?
By default Essbase calculates outlines from the bottom up first calculating the values for children and then values for parent. Sometimes however the values of children depend may be based on the values of parent or the values of other members in the outline. To obtain correct values for these members, Essbase must first calculate the outline and then recalculate the members that are dependent on the calculated values of the other members. The members that are calculated on the second pass through the outline are called Two-Pass Calculation.

Only accounts, dynamic calc, dynamic calc and store members can be given two pass calculation.

Edit>properties
Data dictionary button

What does the consolidation properties do?
Member consolidation determines how children roll up into their parents. Default (+) operator.

Essbase don’t use consolidation properties for attribute dimensions.
Essbase automatically tags members of the attribute dimensions as dynamic calc. you cannot change this setting.

When do you use label only?
When no data is associated with members we use label only. They are used only to ease navigation and reporting from the spread sheet add-in.
You cannot associate attributes to label only. If you tag label only to the base member, which has, attributes associated with it, Essbase removes attributes and displays a warning message

Fragmentation in Essbase (BSO)

• What is Fragmentation?

Fragmentation is unused disk space.

• When does Fragmentation occur?

Fragmentation is likely to occur with the following:

Read/write databases that users are constantly updating with data

Databases that execute calculations around the clock

Databases that frequently update and recalculate dense members

Data loads that are poorly designed

Databases that contain a significant number of Dynamic Calc and Store members

Databases that use an isolation level of uncommitted access with commit block set to zero

• How can you measure fragmentation?

You can measure fragmentation using the average clustering ratio or average fragmentation Quotient.

Using the average fragmentation quotient

Any quotient above the high end of the range indicates that reducing fragmentation may help

performance, with the following qualifications:

The reported value of the Fragmentation Quotient is more accurate when there are no other write

transactions running on the database.

For databases less than 50 MB using the Direct I/O access mode, the fragmentation quotient tends to be high. A high fragmentation quotient does not necessarily indicate a need to reduce fragmentation, because the free space is created in 8 MB chunks and all of it might not get used right away.

Database Size	Fragmentation Quotient Threshold
Small (up to 200 MB)	60% or higher
Medium (up to 2 GB)	40% or higher
Large (greater than 2 GB)	30% or higher

Using the average clustering ratio:

The average clustering ratio database statistic indicates the fragmentation level of the data (.pag) files. The maximum value, 1, indicates no fragmentation.

• How do you can prevent and remove fragmentation?

You can prevent and remove fragmentation:

To prevent fragmentation, optimize data loads by sorting load records based upon sparse dimension members. For a comprehensive discussion of optimizing data load by grouping sparse members.

To remove fragmentation, perform an export of the database, delete all data in the database with CLEARDATA, and reload the export file.

To remove fragmentation, force a dense restructure of the database.