Home » Oracle » hyperion » Must Read Top 100 Hyperion Essbase Interview Questions

Must Read Top 100 Hyperion Essbase Interview Questions

We are bringing here a compilation from Must Read Top 100 Hyperion Essbase Interview Questions.

1) What is Essbase?


Essbase stands for Extended Spreadsheet Database. Essbase is a multidimensional database Management System(MDBMS) that provides a multidimensional database platform upon which to build Analytical Applications. Essbase products provide companies with the ability to deliver critical business information to the right people when they need it. Essbase products can be deployed on the web with minimum infrastructure requirements. The Essbase Server client needs only to retrieve and view data that resides on a server.

Essbase maintains the Hierarchical data structure. The server acts as a shared resource, handling all data storage, caching, calculations, and data security.

2)  What is Dimension?

Answer A dimension represents the highest consolidation level in the database outline. The database outline presents dimensions and members in a tree structure to indicate a consolidation relationship

3) What are Members?

Answer Members are the individual components of a dimension.

Each member has a unique name. Essbase can store the data associated with a member.

4)  what are standard dimensions?


They represent the core components of a business plan and often relate to departmental functions. Typical standard dimensions are Time, Accounts, Market, and Division. Storage space is allocated to standard dimensions and Essbase maximizes performance by dividing the standard dimensions of an application into two Types: dense and sparse dimensions.

5) What are dense dimensions?


It is a dimension with a high probability that data in one or more cells are occupied in every combination of dimensions. Like Period and Account.

6) What are Sparse dimensions?

Answer It is a dimension with a low probability that data in one or more cells are occupied in some combination of dimensions. Like product because

All the products may not be sold in all areas of the country

7)  What are Attribute dimensions?


They are a special type of dimension that is associated with the standard dimension. The standard dimension is the base dimension for that attribute dimension.

Attributes describe characteristics of data such as the size and color of products. Through attributes, you can group and analyze members of dimensions based on their characteristics.

8)  What are the types of Partitions available in Essbase?

Three types of partitions are there.

Transparent partitionA form of shared partition that provides the ability to access and manipulate remote data transparently as though it is part of your local database. The remote data is retrieved from the data source each time you request it. Any updates made to the data are written back to the data source and become immediately accessible to both local data-target users and transparent data-source users
Replicated Partition:A form of partition where data is copied from a remote source to a local database
Linked Partition: 

9)  What is the difference between standard and attribute dimensions?


Standard DimensionAttribute dimensions
Essbase Allocate storage for     Standard dimensions’ members.Essbase does not Allocate Storage for Attribute dimensions’ members. Instead, it dynamically calculates the members when the user requests data associated with them.
UDA members are allowedNot allowed
Shared members are allowedNot allowed
 It must be associated with Base dimensions

9) What are the two storage options available in Essbase and what are the differences?

Hyperion Essbase  Interview Questions

10) How is data stored in the Essbase database?


Essbase is a file-based database where the data is stored in PAG files of 2 GB each and grows sequentially.

11) How does UDA’s impact database size?

Absolutely no impact as UDA’s does not require additional storage space.

12) What is the difference between UDA’s and Attribute dimensions?

Attribute dimensions provide more flexibility than UDA’s. Attribute calculation dimensions which include five members with the default names sum, count, min, max, and avg are automatically created for the attribute dimensions and are calculated dynamically.

13) What are rules files?


It defines operations that Essbase performs on data values or on dimensions and members when it processes a data source. We use rules to map data values to an Essbase database or to map dimensions and members to an Essbase outline.

14)  Is it possible to have multiple databases in one single application?

Yes. However, only one database per application is recommended.

15) Is it possible to have one ASO database and one BSO database in one single application? If yes, how and If No, why?

No. Because we define the ASO or BSO option while creating the application and not the database. Hence if the application is ASO, the databases it contains will be that type only.

16) What are the file extensions for an outline, rule file, and calculation script.

Hyperion Essbase  Interview Questions

17) What is the role of analytics provider services?

Analytics Provider Services is used to communicate between Essbase and Microsoft Office tools (Smart View)

18) How do Attribute dimensions and UDAs impact batch calculation performance?

UDA’s- No Impact as they do not perform any inherent calculations.
Attribute dim- No Impact as they perform only dynamic calculations.

19) How can we display UDAs in reports? How do they impact report performance?

UDA’s values are never displayed in the reports and hence do not impact report performance.

20) How does Attribute dim impact report performance?

They highly impact the report performance as the attributes are calculated dynamically when referenced in the report. For a very large number of att dim displayed in the report, the performance could drastically reduce.

21) What are the three primary building methods for building dimensions?

  1. Generation references
    2. level references
    3. Parent-Child references.

22) Can we build dimensions directly from data sources without using rule files?


23) What is a block locking system?


Analytic services (or Essbase Services) lock the block and all other blocks that contain the Childs of that block while calculating this block is the block locking system.

24) What is a data file cache?

A buffer in memory that holds compressed data (.PAG) files.

25) What is a custom-defined function?
Answer Essbase calculation functions that you develop in the Java programming language and then add to the standard Essbase calculation scripting language by means of MaxL.

26)  Can we start and stop an application individually? How can this be used to increase performance?

Yes. We can manage our server resources by starting only the applications that receive heavy user traffic. When an application is started, all associated databases are brought to the memory.

27) What are LRO’s( Linked Reporting Objects)?

They are specific objects like files, cell notes, or URLs associated with specific data cells of the Essbase database. You can link multiple objects to a single data cell. These linked objects are stored in the server. These LROs can be exported or imported into the database for backup and migration activities.

28) What is UDA (user-defined attributes)? How are they different from Aliases?
Answer UDA represents the class of the members. Aliases are just another name for the members. both are different and have different uses.

See also  What is FND_OAM_CONTEXT_FILES used for? All about it

29) Can we query a member for its UDA in a calculation script?

Yes. You can query a member for its UDA in a calculation script.

30) While loading the data, you have applied both the selection criteria as well as rejection criteria to the same record. What will be the outcome?

The record will be rejected.

31) What is hybrid analysis?

Lower-level members and associated data remain in the relational database whereas upper-level members and associated data reside in the Essbase database.

32) How to create an Essbase application?


a) Open Essbase Administrative Services 11.1.12.

b) Enter the username and password to Logon into the Administration Server.

c) Inside Essbase select Application and then select Create application,

d) Either Block or Aggregate and name it.

e) Select the application and choose the option to create the DATABASE And name it.

f) Select “outline à Edit”

g) Using the left click select “Add Child”. Similarly, add required child and sibling as required and configure dimension and member property.

h) database and using left click “Create à Rule File”.

e) Select that database and using the left click “load data à Select data file and rule file”

33) What are the different types of attributes?

Essbase supports two different types of attributes.
1. User-Defined attributes
2. Simple attributes
User-defined attributes: The attributes that are defined by the user.
Simple attributes: Essbase supports some attributes, which are: Boolean, date, number, and string.

34) What are filters?

A method of controlling access to database cells in Essbase. A filter is the most detailed level of security, allowing you to define varying access levels different users can have to individual database values.

35) Why is top-down calculation less efficient than bottom-up calculation? Being less efficient, why do we use them?


In the process, it calculates more blocks than is necessary. Sometimes it is necessary to perform top-down calculations to get the correct calculation results.

36) What does never consolidate operator (^) do?

It prevents members from being consolidated across any dimension.

37) What are the different types of LOG Files?

The important log files are
1. Application log
2. Essbase.log
3. Configtool.log
4. eas_install.log
5. essbaseserver-install.log

6) opmn.log

7) Essbase_odl.log
38) What is TB First and TB Last?

TB First: in the Sample Basic database, the accounts member Opening Inventory is tagged as TB First. Opening Inventory consolidates the value of the first month in each quarter and uses that value for that month’s parent. For example, the value for Qtr1 is the same as the value for Jan.
TB Last: in the Sample Basic database, the accounts member Ending Inventory is tagged as TB Last. Ending Inventory consolidates the value for the last month in each quarter and uses that value for that month’s parent. For example, the value for Qtr1 is the same as the value for Mar.

39) How do you optimize the outline?

Usually, the outline is optimized using the hourglass design for dimension ordering i.e.,
· Dimension with Accounts tag
· Dimension with Time tag
· Largest Dense dimension
· Smallest dense dimension
· Smallest Sparse dimension
· Largest Sparse dimension

40) What are the ways to improve performance during data loads?

There are several ways to optimize the load

  1. Grouping of Sparse member combinations
    2. Making the data source as small as possible
    3. Making source fields as small as possible
    4. Positioning the data in the same order as the outline
    5. Loading from Essbase Server
    6. Managing parallel data load processing

41) What are the design considerations for calculation optimization?


You can configure a database to optimize calculation performance. The best configuration for the site depends on the nature and size of the database.
· Block Size (8Kb to 100Kb) and Block Density
· Order of Sparse Dimensions
· Incremental Data Loading
· Database Outlines with Two or More Flat Dimensions
· Formulas and Calculation Scripts

42) What is SmartView?


It is a Web-deployed thin client that is embedded in a client spreadsheet application and Enables you to retrieve data and create ad hoc reports and it Provides integration with Microsoft Office products. This provides a single Excel interface to analyze data

43) What is an outline?


The outline is a Tree structure for dimension hierarchies and it also contains Consolidations and mathematical relationships between members

44) What are the applications and databases in Essbase?





Contains databases and shared scripts
Reside on the server where the Essbase Server is installed
Run application server processes



Are repositories for multidimensional analytic data
Contains database objects and security definitions

45) We have created an application in Unicode mode. Can we change it later to non-Unicode mode?
Answer No.

46) What are the specified roles other than Administrator to view sessions, disconnect sessions, or kill users’ requests for a particular application?
Answer You should have the role of Application manager for the specified application

47) What are the different types of Essbase cache?


Hyperion Essbase Interview questions and Answers

48)  I have write access to one dimension, for other dimensions I have read access, is it possible to load data into dimensions?

49) what is the difference between shift and relative functions and where you use them?

50) what is a relative function?

51) In what case we can define generation 1 to a field in the generation build method?

We cannot define that as Generation 1 is not valid.

52) Suppose we have assigned Generation 2 and Generation 4 as of now and think of adding Generation 3 later sometime. Can we build the dimension?

No. If Gen 2 and Gen 4 exist, we must assign Gen 3.

53) Can we create more than 2 dimensions using only one build rule file?

Yes, we can do that but it is recommended to use separate rule files for each dimension.

54) Can we have multiple meta outlines based on one OLAP model in Integration services?

55) Can we have a meta outline based on two different OLAP models?

56)  What is the data block and index file in the BSO database?

Answer The data block is the structure that is comprised of the dimensions tagged as dense in the Essbase outline (.otl file). The block is defined by the intersections of all dense dimension member intersections. When data blocks and index entries are persisted to disk, the data blocks are compressed and stored within ESS.PAG files and index entries are stored within ESS.IND files.

57) Can we create UDAs and apply them to Dense as well as Sparse dimensions?
Answer Yes

58) What is a block locking system?

Analytic services (or Essbase Services) lock the block and all other blocks that contain the Childs of that block while calculating this block is the block locking system.

See also  Running Opatch throws up org.xml.sax.SAXParseException: : XML-20108: (Fatal Error) Start of root element expected

59) What are attributes?
Answer A classification of a member in a dimension. You can select and group members based on their associated attributes. You can also specify an attribute when you perform calculations and use calculation functions. Eg: The database in Sample Basic which has product dimensions has some attributes like size, package type, and flavour. We can add these attributes to the dimensions where we can retrieve the data like for example to retrieve “coke with 8 Oz with bottles”, this is useful for generating reports.

60)  Why do objects get locked and when does this happen?
Answer Objects get locked to prevent users from making simultaneous and conflicting changes to Essbase database objects. By default, whenever an object is accessed through the Administrative services console or Excel spreadsheet add-in, it gets locked.

61) When does Fragmentation occur?

Answer 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 a commit block set to zero

62) How can you measure fragmentation?

Answer 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

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.

63) 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 on 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.

64) What is database restructuring?


When you change the Essbase database outline to capture new product lines, provide information on new scenarios, reflect new time periods, etc. Some changes to a database outline affect the data storage arrangement, forcing Essbase to restructure the database.

65) What are the types of database restructuring?


The two ways by which a database restructure is triggered:

Implicit Restructures

Dense restructure

Sparse restructure

Outline-only restructure

Explicit Restructures

66) What are the conditions affecting Database restructuring?

Intelligent Calculation, name changes, and formula changes affect database restructuring:

  • If you use Intelligent Calculation in the database, all restructured blocks are marked as dirty whenever data blocks are restructured. Marking the blocks as dirty forces the next default Intelligent Calculation to be a full calculation.
  • If you change a name or a formula, Essbase does not mark the affected blocks as dirty. Therefore, you must use a method other than full calculation to recalculate the member or the database.

67) What are the files used during Restructuring?


When Essbase restructures both the data blocks and the index, it uses the files described

Hyperion Essbase Interview questions pdf

68) What are the actions that improve performance for restructuring?


There are a number of things you can do to improve performance related to database restructuring:

a) If you change a dimension frequently, make it sparse. ·

b) Use incremental restructuring to control when Essbase performs a required database restructuring. ·

c) Select options when you save a modified outline that reduces the amount of restructuring required.

69) Why do objects get locked and when does this happen?
Answer Objects get locked to prevent users from making simultaneous and conflicting changes to Essbase database objects. By default, whenever an object is accessed through the Administrative services console or Excel spreadsheet add-in, it gets locked.

70) I have created an application with one database in Essbase at the test environment and now need to copy the entire application with all security permissions to the production server which is not connected to the test server. How can I do that?
Answer: You can achieve this using a migration wizard to migrate an application to a text file which you can take physically to the production server.

71) When I migrated the application to the production server I did not see any data in the Essbase database. What has gone wrong and how to correct this?
Answer Nothing has gone wrong. Migration utility does not migrate the actual data. If you want to copy the exact data from the test machine, get it exported using export utility and import it on a production machine.

72) How can we backup an aggregate storage database?

a) shut down the application

b) copy the application directory ARBORPATH/app/appname.c) startup the application

73) How does Essbase recover from a database crash?
Answer After a database crash or server interruption, Essbase automatically recovers a database after rolling back the transactions that were active at that time.

74) How can you make a Max L script run at a specified time?
Answer MaxL scripts can be called from a BAT file which is used for scheduling.

75)  A customer wants to run two instances of an Essbase server on the same machine to have both test env and Development env on the same server. Can he do that?

Yes. We can have multiple instances of an Essbase server on a single machine and there will be different sets of Windows services for all these instances

76)  How to make the Essbase application read-write and read-only in case backup fails in that stage


Archive mode (Essbase application in read-only mode) By using of below command we can achieve that

alter database dbname begin archive

Essbase application read-write mode we can achieve that by using of below command

alter database dbname end archive

77) How can we back up a block storage database?

a) put the application in read-only mode

b) Take an archive of the whole application which contains all the files.

c) put the application back in read-write mode

78)  What is dense restructuring?


If a member of a dense dimension is moved, deleted, or added, Essbase restructures the blocks in the data files and creates new data files. When Essbase restructures the data blocks, it regenerates the index automatically so that index entries point to the new data blocks. Empty blocks are not removed. Essbase marks all restructured blocks as dirty, so, after a dense restructure you must recalculate the database. Dense restructuring, the most time-consuming of the restructures, can take a long time to complete for large databases.

See also  Service Group changes in R12.2

79) What is sparse restructuring?


If a member of a sparse dimension is moved, deleted, or added, Essbase restructures the index and creates new index files. Restructuring the index is relatively fast; the time required depends on the index size.

80)  What is an Outline-only restructure


If a change affects only the database outline, Essbase does not restructure the index or data files. Member name changes, creation of aliases, and dynamic calculation formula changes are examples of changes that affect only the database outline.

81) How does Essbase perform Sparse restructuring?


When Essbase does a sparse restructure (restructures only the index), it uses the following files:




To perform a sparse restructure, Essbase does the following:

1 Renames the dbname.esm file to dbname.esr.

2 Renames the essxxxxx.ind files to essxxxxx.inm.

82) What is intelligent calc? Why should I care?


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, the intelligent calculation is turned on. You can change the default setting in the essbase.cfg file or on a script-by-script basis with the SETUPDATECALC OFF command.

The intelligent calculation is based on data-block marking, when the 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 the calculation

Dirty Blocks — Blocks that require calculation.

When the intelligent calculation is active, during calculation, Essbase looks for only dirty blocks.

83)  How to optimize the data loads in the BSO cube?


BSO load process is quite a complex process. Following action items can speed the data load in BSO

a) Positioning Data in the Same Order as the Outline

b) Loading from Essbase Server

c) Managing Parallel Data Load Processing

d) Grouping Sparse Member Combinations

e) Making the Data Source as Small as Possible

84) Explain the working of each of the tablespace in the ASO cube.


Hyperion Essbase Interview questions

85)  What is the use of OPMN in Essbase?


Oracle Process Manager and Notification Server (Oracle Process Manager and Notification

Server) enables you to monitor and control the Essbase Agent process. You add Essbase Agent information to the opmn.xml file to enable OPMN to start, stop, and restart the agent using the OPMN command-line interface. OPMN can automatically restart the Essbase Agent when it becomes unresponsive, terminates unexpectedly, or becomes unreachable as determined by ping and notification operations

86)  What is active-active clustering?


Active-active Essbase clusters are the cluster of Essbase.it supports high availability and load balancing. So, all the Essbase nodes are serving the request. An active-active Essbase cluster supports read-only operations on the databases and should be used only for reporting. Because active-active Essbase clusters do not support data write-back or outline modification, and they do not manage database replication tasks such as synchronizing the changes in one database across all databases in the cluster, they do not support Planning.

87) what is active-passive clustering?


An active-passive Essbase cluster consists of two Essbase instances, one on each node that shares common storage for configuration and data. Storage is shared across two computers which removes the need for the administrator to synchronize storage, as well as the constraint of read-only support (as is the case with Essbase active-active clusters in Provider Services). Essbase uses database tables to ensure that only one agent and its associated servers are active to avoid data corruption on writes. During installation and configuration, a table is created to hold information on configuration and application data existing in the cluster.

Active-passive Essbase clusters do not use any file-system locking (that is, essbase.lck files).

Some of the features

  1. a) Active-passive Essbase clusters support failover with write-back to databases.
  2. b) Active-passive Essbase clusters do not support load-balancing.
  3. c) An active-passive Essbase cluster can contain only two Essbase servers.

88)  What is a split-brain problem in Essbase clusters?


A split-brain situation can occur when both instances in an active-passive clustered environment think they are “active” and are unaware of each other, which violates the basic premise of active-passive clustering (One node being active). Situations where split-brain can occur include network outages or partitions. Such a situation can result in corruption

89) How Essbase resolve the split-brain problem?


Essbase failover uses a leasing mechanism to solve the problem of split-brain

90) How Leasing mechanism works in Essbase?


An Essbase process acquires a lease on a specified shared resource or set of resources upon startup; it renews the lease periodically and surrenders the lease upon termination.

This lease establishes the process as the primary service provider and grants the right to update the specified set of shared resources, service client requests, and spawn processes.

Essbase servers can acquire leases if and only if its authorizing agent has a current lease.

91) How Leases are Implemented internally in Essbase?


Essbase failover implements a leasing mechanism using a centralized relational database to store, update, and retrieve information about the following:

  1. a) Shared resources
  2. b) Lease ownership
  3. c) Lease validity

92)  How to stop/start Essbase server?


We start/stop the Essbase server using OPMN (Oracle Process Manager).  Upon Essbase installation, it registers Essbase Server for OPMN. OPMN manages the Essbase Agent, which manages the Essbase Server. Essbase Server start and stop scripts redirect to OPMN

93) What are these executable Essbase.exe and esssvr.exe in Windows?


94) What does the Essbase security file Contains? Where is, it located?


Essbase.sec is an important file for Essbase server functioning.

Essbase is EPM System security mode (Shared services)

Calculation script access

Users and Groups details

Application and Database access type

Application and database properties, including substitution variables and DISKVOLUMES settings

Filters access

Locked objects

Substitution Variables

Passwords are not stored in Security files as they are stored in shared services.

Essbase is in native security mode

Stores all information about users, groups, passwords, permissions, filters, applications, databases, and corresponding directories

Essbase security file is located in Arborpath/bin directory


95) How to restore the Essbase security file after corruption?

  1. a) Stop the Essbase services
  2. b) Copy the essbase_timestamped.bak file from the same arborpath/bin folder and rename it to essbase.sec
  3. c) Restart the essbase services

Note: Each time the Essbase server restarts, a backup copy of the security file is created and named essbase.bak file

96)  What is maxl?


Maxl is a multidimensional database access language that is part of Essbase – Servers provide a flexible way to automate Essbase administration and maintenance tasks

97) What is ARBORPATH?


ARBORPATH is an environment variable that defines the home directory for the Essbase – Server. It contains two main directories

appThe app directory location where Essbase application files (as they are created) and

 sample applications and databases (provided with Essbase) are stored.

BinThe bin directory location where Essbase configuration setting (essbase.cfg) and security (essbase.sec and essbase_timestamp.bak) files (as they are created) are


98) What is Essbase exception logs (.xcp)?


If an Essbase Server, an application, or a database shuts down abnormally and cannot restart, Essbase Server generates an exception log to help troubleshoot the problem. The location of the exception log depends on which component shut down abnormally and the amount of information that the Essbase Server had available at the time

99) What is a transaction in Essbase?


When a database is in read/write mode, Essbase considers every update request to the server (such as a data load, a calculation, or a statement in a calculation script) as a transaction. Essbase tracks information about transactions in a transaction control file (dbname. tct). The transaction control file contains an entry for each transaction and tracks the current state of each transaction (Active, Committed, or Aborted).

100) How to restore the Essbase application?


We need to replace the files on disk with the corresponding files from backup to restore a database, The application should be stopped unless you are restoring from an export file. In that case, ensure the application is not accepting client connections.

Also Reads
Windows grep equivalent
Senior Oracle DBA interview Questions

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top