Top Oracle Hyperion Interview Questions and Answers

Last updated on Feb 18 2022
Avinash M

Table of Contents

What is Hyperion?

It is a file that permits you to access the data quickly. The association was bought by Oracle in 2007 and has continued upgrading the product. The other name of Hyperion is Business Intelligence, and its motive is to support better business decision making.

Can you name the two functions that can produce a report that includes only elimination entities?

The two functions are:

  • Dynamic name list
  • Fixed name list

When do we use build rules files?

We use to automate the process of creating dimensions with thousands of members

How does attribute dim impact report performance?

They extremely impact the report performance as the attributes are calculated dynamically when referenced in the report. For a vast number of attribute dim displayed in the report, the performance could intensely reduce.

What is a custom-defined function?

Essbase calculation’s function that you develop in java programming language and then add to the standard Essbase calculation scripting language by means of MaxL.

How do you optimize the outline?

Usually, the outline is optimized using the hourglass design for dimensions ordering, i.e.,

  • Largest sparse dimension
  • Smallest sparse dimension
  • Dimension with a time tag
  • Dimension with accounts tag
  • Largest dense dimension
  • Smallest dense dimension

How can you make a Max L script run at a specified time?

Max L scripts can be called from a BAT file which is used for scheduling.

How do you optimize 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

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

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.

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

What are the files used during Restructuring?

When Essbase restructures both the data blocks and the index, it uses the files described essxxxxx.pag Essbase data file essxxxxx.ind Essbase index file dbname.esm Essbase kernel file that contains control information used for db recovery dbname.tct Transaction control table dbname.ind Free fragment file for data and index free fragments dbname.otl Outline file in which is defined all metadata for a database and how data is stored

What is Implicit Restructures?

Essbase initiates an implicit restructure of the database files after an outline is changed using Outline Editor or Dimension Build. The type of restructure that is performed depends on the type of changes made to the outline

What is Sparse restructure?

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 amount of time required depends on the size of the index.

What is 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.

Explain the process of sparse restructure?

  When Essbase does a sparse restructure (restructures just the index), it uses the following files:· essxxxxx.ind· dbname.otl· dbname.esm

What is data compression?

Essbase allows you to choose whether data blocks that are stored on disk are compressed, as well as which compression scheme to use. When data compression is enabled, Essbase compresses data blocks when it writes them out to disk. Essbase fully expands the compressed data blocks, including empty cells, when the blocks are swapped into the data cache. Generally, data compression optimizes storage use. You can check compression efficiency by checking the compression ratio statistic.

When do you use RLE over Bitmap Compression?

Use RLE over Bitmap When, Average block density very low (< 3%). Database has many consecutive repeating Values.

When do you disable compression?

You may want to disable data compression if blocks have very high density (90% or greater) and have few consecutive, repeating data values. Under these conditions, enabling compression consumes resources unnecessarily. Don’t use compression if disc space/memory is not an issue compared to your application. It can become a drain on the processor.

What is transaction control file?

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

What is isolation level and what are the types of isolation levels?

Isolation levels determine how Essbase commits data to disk. Essbase offers two isolation levels for transactions —committed access and uncommitted access (the default).

What is committed access?

When data is committed, it is taken from server memory and written to the database on disk. Essbase automatically commits data to disk. There are no explicit commands that users perform to commit data blocks.

What is Data file cache?

  The data file cache is a buffer in memory that holds compressed data files (.pag files). Essbase allocates memory to the data file cache during data load, calculation, and retrieval operations, as needed. The data file cache is used only when direct I/O is in effect.

What is Data cache?

The data cache is a buffer in memory that holds uncompressed data blocks. Essbase allocates memory to the data cache during data load, calculation, and retrieval operations, as needed.

What is Calculator cache?

  The calculator cache is a buffer in memory that Essbase uses to create and track data blocks during calculation operations.

What is the structure of currency applications?

  In a business application requiring currency conversion, the main database is divided into at least two slices. One slice handles input of the local data, and another slice holds a copy of the input data converted to a common currency. Essbase holds the exchange rates required for currency conversion in a separate currency database. The currency database outline, which is automatically generated by Essbase from the main database after you assign the necessary tags, typically maps a given conversion ratio onto a section of the main database. After the currency database is generated, it can be edited just like any other Essbase database.

What are the dimensions that should be present in currency database of currency application?

   A currency database always consists of the following three dimensions, with an optional fourth dimension:

  • A dimension tagged as time, which is typically the same as the dimension tagged as time in the main database.
  • A dimension tagged as country, which contains the names of currencies relevant to the markets (or countries) defined in the main database.
  • A dimension tagged as accounts, which enables the application of various rates to members of the dimension tagged as accounts in the main database.
  • A currency database, which typically includes an optional currency type dimension, which enables different scenarios for currency conversion.

What is the process to build a currency conversion application and perform conversions?

To build a currency conversion application and perform conversions, use the following process:

  1. Create or open the main database outline. 2. Prepare the main database outline for currency conversion. 3. Generate the currency database outline. 4. Link the main and currency databases. 5. Convert currency values. 6. Track currency conversions. 7. If necessary, troubleshoot currency conversion.

Can we convert the converted currency back into its local currency?

 You can convert the data values back to the original, local currencies by using the CCONV TOLOCALRATE command.

When you convert currencies using the CCONV command, are the resulting data blocks are marked as dirty or clean?

 When you convert currencies using the CCONV command, the resulting data blocks are marked as dirty for the purposes of Intelligent Calculation. Thus, Essbase recalculates all converted blocks when you recalculate the database.

How can you turn off CCTRACK?

 You can turn off CCTRACK in three ways: · Use the SET CCTRACKCALC ONOFF command in a calculation script to turn off CCTRACK temporarily · Use the CLEARCCTRACK calculation command to clear the internal exchange rate tables created by CCTRACK. Set CCTRACK to FALSE in the essbase.cfg file.

What is LRO (Linked reporting objects)?

  An LRO is an artifact associated with a specific data cell in an Essbase database. LROs can enhance data analysis capabilities by providing additional information on a cell. An LRO can be any of the following:

  • A paragraph of descriptive text (a “cell note”)
  • A separate file that contains text, audio, video, or graphics
  • A URL for a Web site
  • A link to data in another Essbase database

How does Essbase locate and retrieve linked objects?

Essbase uses the database index to locate and retrieve linked objects. If you clear all data values from a database, the index is deleted and so are the links to linked objects. If you restructure a database, the index is preserved and so are the links to linked objects.

 Do shared members share LRO’s?

Shared members share data values but do not share LROs. This is because LROs are linked to specific member combinations and shared members do not have identical member combinations. To link a given object to shared members, link it to each shared member individually.

Why do we need to limit the LRO file sizes for storage conversion?

Because Essbase stores linked files in a repository on the server and, by default, the size is unlimited. Limiting the file size prevents users from taking up too much of the server resources by storing extremely large objects. You can set the maximum linked file size for each application. If a user attempts to link a file that is larger than the limit, an error message displays. The maximum file size setting applies only to linked files and does not affect cell notes or URLs. The lengths of the cell note, URL string, and LRO descriptions are fixed.

 What is partitioning?

A partition is the piece of a database that is shared with another database. An Essbase partitioned application can span multiple servers, processors, or computers.

 What is Essbase Partitioning?

Essbase Partitioning is a collection of features that makes it easy to design and administer databases that span Essbase applications or servers. Partitioning is licensed separately from Essbase.

What are the parts of partition?

Partitions contain the following parts,

  • Type of partition: A flag indicating whether the partition is replicated, transparent, or linke
  • Data source information: The server, application, and database name of the data source.
  • Data target information: The server, application, and database name of the

data target.

  • Login and password: The login and password information for the data source and the data target.
  • Shared areas: A definition of one or more areas, or sub cubes, shared between the data source and the data target.
  • Member mapping information: A description of how the members in the data source map to members in the data target.
  • State of the partition: Information about whether the partition is up-to-date and when the partition was last updated.

Can you define different types of partitions between the same two databases?

No

 Can a single database serve as the data source or data target for multiple partitions?

Yes

 What is overlapping partition?

An overlapping partition occurs when similar data from two or more databases serve as the data source for a single data target in a partition.

What is the limitation on version and mode during partition?

Both ends of a transparent, replicated, or linked partition must be on the same release level of Essbase Server. For example, if the source of a linked partition is on a Release 7.1.2 server, the target must also be on a Release 7.1.2 server. In addition, for transparent and replicated (but not linked) partitions, the application mode of both ends of the partitions must be the same–either Unicode mode or non-Unicode mode.

 What are the major difference between ASO & BSO?

If we have more dimensions (generally more than 10) then we will go for ASO that simply rollup If we have less dimensions then we will go for BSO We cannot write back in ASO we can write back in BSO Most of the dimensions are sparse in ASO Most of the dimensions are dense in BSO

 What is “Enterprise Analytics”?

 ASO in System 9 is called Enterprise Analytics.

Explain in detail about the features of ASO?

ASO databases are created specifically to deal with the requirements of very large sparse data sets with a high no of dimensions and potentially millions of members. · ASO do not have indexes or data blocks. · ASO do not use calculation scripts. Bcoz calculations are not complex. · ASO uses a new kind of storage mechanism that allows improved calculation times from 10 to100 times faster than BSO. · ASO can store up to 252 dimensional combinations. · The front end tools usually do not care if the database is ASO or BSO. Even Maxl sees minor differences. · We can have attribute dimensions in ASO. · In ASO there is no concept as dense and sparse dimensions. · We do not have two pass logic and built in time balance functionality.( time balance functionality is present from 9.3 version onwards). · Member formulas are not supported in stored hierarchies. · Only non consolidation (~) and addition (+) operators are supported in shared hierarchies. · We cannot create more than 1 database in ASO. · ASO does not utilize procedural calculation scripts. · ASO formulas are written in MDX syntax. · ASO has Accounts dimension but it is completely different from the account dimension of BSO. · ASO is read-only. You cannot write to ASO databases, but there is a workaround using transparent partitions and pointing to an attached BSO database for those duties. · You can load data to level zero members only. · The database must restructure after any members in the standard dimensions are added ,deleted or moved. In fact most actions on an ASO outline will either cause a loss of data or restructure.

How do you create an ASO database using ASO Outline Conversion Wizard ?

 You can also create an ASO database using ASO Outline Conversion Wizard. This wizard uses the existing BSO database to convert to an ASO database. This is advantageous because we do not need to create an ASO database from the Scratch. However we need perform reengineering of dimensions and hierarchies.

What are the different types of LOG Files?

So many log files are there in essbase, but the important log files are

  1. Application log
  2. Essbase.log
  3. Configtool.log
  4. eas_install.log
  5. essbaseserver-install.log

Why do objects gets locked and when does this happens?

Objects gets locked to prevent users to make simultaneous and conflicting changes to Essbase database objects. By default whenever an object is accessed through Aministrative services console or Excel spreadsheet add-in, it gets locked.

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

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

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.

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.

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 very large number of attribute dim displayed in the report, the performance could drastically reduce.

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

The record will be rejected.

How is data stored in the Essbase database?

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

What is hybrid analysis?

Lower level members and associated data remains in relational database where as upper level members and associated data resides in Essbase database.

Why top-down calculation less efficient than a 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 calculation to get the correct calculation results.

On what basis you will decide to invoke a serial or parallel calculation method.

If we have a single processor, we will use serial calculation but if we have multiple processors we can break the task into threads and make them run on different processors.

What are the specified roles other than Administrator to view sessions, disconnect sessions or kill users requests for a particular application?

You should have the role of Application manager for the specified application.

What is block locking system?

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

What are the three options specified in Username and Password management under security tab in Essbase server properties.

  1. Login attempts allowed before username is disabled. 2. Number of inactive days before username is disabled. 3. Number of days before user must change password.

Can we have multiple databases in one single application?

Yes. But only one database per application is recommended. Depend on which database that you are going to create. For Example: If you are creating ASO then we can’t create more that 1 db per application. If you are creating BSO then you can create more than 1 db per application

Dynamic calc decreases the retrieval time and increases batch database calculation time. How true is the statement?

The statement should be just opposite. As dynamic calc members are calculated when requested, the retrieval time should increase.

A customer wants to run two instances of an Essbase server on a same machine to have both test environment and Development environment 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.

Define Hyperion?

Hyperion is Business Intelligence (BI) and Business Performance Management (BPM ) tool. It is the market leader in Operational , Financial and Strategic Planning. It contains the applications for reporting, Planning , dashboards, Analysis, score carding, consolidation, Workspace, Master Data Management and Foundation.

Can we have multiple databases in single application ?

Yes, in this one database per application.

What does ‘DOU’ mean ?

  • In reference to RPG, DOU means (Do Until).This will execute a loop matched with an End or End do
  • For instance
  • Eval X=1
  • DOU X=4
  • Eval X=X=1
  • End Do
  • This example will go through the loop 4 times with the result of X being 4.
  • ‘DOU’ is similar to Do While(‘DOW’) where the difference lies in that the DOU will always perform 1 pass through the loop and DOW will perform the comparison and only continue into the loop if the condition meets else the program continues after the End (End Do).
  • For example
  • Eval X=1
  • DOW X=4
  • X=X+1
  • End do

The result is that the loop will not execute and the value of X will be 1.

Differentiate between ASO & BSO?

  1. We cannot write back in ASO although we can write back in BSO.
  2. Most dimensions in ASO are sparse whereas in BSO most of them are dense.
  3. We cannot create more than 1 database in ASO but we can create more than 1 db per application. If we have more than 10 dimensions then we should opt for ASO.

Explain attributes?

 Classification of a member in a dimension is known as an attribute. We can do selection of group members based on their associated attributes. We can also specify an attribute while performing calculations and along with we can use calculation functions. As example, the db in Sample Basic with product dimension has some attributes like package type, size, and flavor. We can add attributes to the dimensions and can retrieve data. For example, to retrieve ‘coke with 8 Oz with bottles’.

What is Oracle Hyperion Financial Management (HFM)?

Oracle Hyperion Financial Management (HFM) is a complete Web-based application that provides global financial alliance, reporting and examination in a single, highly scalable software solution.  Oracle Hyperion Financial Management, part of the Oracle EPM Suite, supports the collection, consolidation and reporting of financial results in multiple GAAPs as well as IFRS, and the reconciliation of differences among the various standards. Hyperion Financial Management can be deployed quickly to integrate data from multiple Oracle and non-Oracle transactional systems, provide top-side reporting that conforms to IFRS requirements, and help organizations make the transition to IFRS.

Can we start and stop an application individually and how to increase performance using this?

We can manage our server resources by starting only applications which get heavy user traffic. Right when an application is started, memory is joined by all related data bases.

Explain custom based macro?

Custom defined macros makes use of an internal Ess base macro language that enables to combine calculation functions and also helpful to operate on multiple input parameters.

Which two functions can produce a report that includes only Elimination entities?

The two functions are:

  • Fixed Name List
  • Dynamic Name List.

Explain Dense and Sparse Dimensions?

Dense dimension: It is a dimension in which most data exists for every combination of dimension members.

Sparse dimension: It is a dimension which has less probability that data will exist for every combination of dimension members.

What impact do Attribute dimensions have on the performance of reports?

The impact of the Attribute dimensions on the performance of the reports is very high due to the dynamic calculation made referencing the reports. If the number of attribute dimensions shown in the reports is high, this will have a negative impact on performance.

What are LRO’s(Linked Reporting Objects)?

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

Explain OLAP and mention if it is related to HYPERION FINANCIAL MANAGEMENT ?

  • Hyperion HFM/ Hyperion Planning both are Essbase based. They are front-end tech and Ess-base is the back end. Ess-base is a M-OLAP. There are three types of OLAP technology present in the market. These are ROLAP, MOLAP and HOLAP.
  • An example of ROLAP is BO. Here we deal with table and they acts as a virtual cube. But if we think about Oracle Express Hyperion Essbase and Cognos then they are real cubes. Thus MOLAP. ROLAP+MOLAP=HOLAP.
  • In case of BO, we need to join to attain the OLAP effect virtually but behind is a joined table. But in Case of MOLAP there is no concept of table it is cube only.
  • For example a page is a table then a Book is a Cube.
  • If the data is large then it is advisable to take up ROLAP not MOLAP. because the performance of the cubes degrades. If the size of data is around 100 GB to 150 GB then it is fine to go with MOLAP and if it is more than that then we should choose BO.

Name some different types of LOG files?

The different types of LOG files are:

  • log
  • Ess base server-install.log
  • log
  • log
  • log

What is the meaning of a block locking system?

The Essbase services are also known analytic will make a lock to all the blocks that capture the Childs of the initial block; this process is block locking system.

Name the three primary build methods for building dimensions?

The three primary build methods for building dimensions are:

  • Level references
  • Parent-child references
  • Generation references

What is the role of provider services?

The role of providing services is to communicate between Essbase and Microsoft office tools.

What is a hybrid analysis?

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

What are the Filters?

A method of controlling access to database cell 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.

How does Essbase recover from a database crash?

After a database crash, Essbase automatically recovers a database after rolling back the transactions that were active at that time.

What are the several ways to improve performance during data loads?

Several ways to advance load are:

  • Loading from Essbase server
  • Making the data source as small as possible
  • Managing parallel data load processing
  • Making source fields as small as possible
  • Positioning the data in the same order as the outline
  • Grouping of sparse member combinations

What are the ways to improve performance during data loads?

There are several ways to optimize 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

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 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.

Why is database restructuring?

  As your business changes, 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.

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.

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:

  • If you change a dimension frequently, make it sparse. · Use incremental restructuring to control when Essbase performs a required database restructuring. · Select options when you save a modified outline that reduce the amount of restructuring required.

Which restructure operations are faster?

These types of restructure operations are listed from fastest to slowest:

  • Outline only (no index or data files)· Sparse (only index files) · Dense (index files and data files) as a result of adding, deleting, or moving members and other operations · Dense (index and data files) as a result of changing a dense dimension to sparse or changing a sparse dimension to dense

What is Explicit Restructures?

  When you manually initiate a database restructure, you perform an explicit restructure. An explicit restructure forces a full restructure of the database. A full restructure comprises a dense restructure plus removal of empty blocks.

What is Dense restructure?

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 need to recalculate the database.

Explain the process of dense restructure?

To perform a dense restructure, Essbase does the following:

  1. Creates temporary files that are copies of the .ind, .pag, .otl, .esm, and .tct files. Each temporary file substitutes either N or U for the last character of the file extension, so the temporary file names are .inn, essxxxxx.inn, essxxxxx.pan, dbname.otn, dbname.esn, and dbname.tcu. 2. Reads the blocks from the database files copied in step 1, restructures the blocks in memory, and then stores them in the new temporary files. This step takes the most time. 3. Removes the database files copied in step 1, including .ind, .pag, .otl, .esm, and .tct files. 4. Renames the temporary files to the correct file names: .ind, .pag, .otl, .esm, and .tct.

What are types of data compression?

Essbase provides several options for data compression:

1. Bitmap compression, the default. Essbase stores only non-missing values
uses a bitmapping scheme. A bitmap uses one bit for each cell in the data block, whether the cell value is missing or non-missing. When a data block is not compressed, Essbase uses 8 bytes to store every non-missing cell. In most cases, bitmap compression conserves disk space more efficiently. However, much depends on the configuration of the data.

2. Run-length encoding (RLE). Essbase compresses repetitive, consecutive values –any value that repeats three or more times consecutively, including zeros and #MISSING values. Each data value that is repeated three or more times uses 8 bytes plus a 16 byte repetition factor.

3. zlib compression. Essbase builds a data dictionary based on the actual data being compressed. This method is used in packages like PNG, Zip, and gzip. Generally, the more dense or heterogeneous the data is, the better zlib will compress it in comparison to bitmap or RLE compression.

4. Index Value Pair compression. Essbase applies this compression if the block density is less than 3%.Index Value Pair addresses compression on databases with larger block sizes, where the blocks are highly sparse. zlib does not use this.

5. No compression. Essbase does not compress data blocks when they are written to disk

What are data locks?

Essbase issues write (exclusive) locks for blocks that are created, updated, or deleted, and issues read (shared) locks for blocks that should be accessed but not modified. By issuing the appropriate locks, Essbase ensures that data changed by one operation cannot be corrupted by a concurrent update.

What is a transaction?

  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.

Talk about committed and uncommitted access?

 Committed:  Committed at the end of a transaction. Data retained till then. All blocks in question locked. Pre-Image Access: If enabled, Read only access allowed Wait Times: Indefinite Immediate Access or no Wait No. of Seconds Specified Uncommitted:  Committed only at synchronization points. Block by Block Locks. Commit Row: No of rows of data loaded when Sync point occurs. Commit Block: No. of Blocks Modified when Sync Point occurs. For Rollback, Commit Row=0 and Commit Block=0

What are the advantages and disadvantages of using committed access?

  You can optimize data integrity by using committed access. Setting the isolation level to committed access may increase memory and time requirements for database restructure.

Which transaction is always in committed mode?

  The Spreadsheet Add-in lock and send and the Grid API are always in Committed Access Mode

What are the memory caches used by Essbase to coordinate memory usage?

 Essbase uses five memory caches to coordinate memory usage 1. Index Cache 2. Data File Cache 3. Data Cache 4.

Calculator Cache 5. Dynamic Calculator Cache

What is Index cache?

The index cache is a buffer in memory that holds index pages. How many index pages are in memory at one time depends upon the amount of memory allocated to the cache.

What is Dynamic calculator cache?

ssblocks needed for a calculation of a Dynamic Calc member in a dense dimension (for example, for a query).

What are the memory caches used by Essbase to coordinate memory usage?

  Essbase uses five memory caches to coordinate memory usage Index Cache: Min -1024 KB (1048576 bytes) Default – Buffered I/O : 1024 KB (1048576 bytes);Direct I/O : 10240 KB (10485760 bytes) Opt -Combined size of all essn.ind files, if possible; as large as possible otherwise.Do not set this cache size higher than the total index size, as no performance improvement results. Data File Cache: Min – Direct I/O: 10240 KB(10485760 bytes) Default -Direct I/O: 32768 KB(33554432 bytes)Opt -Combined size of all essn.pag files, if possible; otherwise as large as possible.This cache setting not used if Essbase is set to use buffered I/O. Data Cache:Min – 3072 KB (3145728 bytes) Default – 3072 KB (3145728 bytes) Opt -0.125 * the value of data file cache size. Calculator Cache:Min – 4 bytes Max: 200,000,000 bytes Default – 200,000 bytes Opt -The best size for the calculator cache depends on the number and density of the sparse dimensions in your outline. The optimum size of the calculator cache depends on the amount of memory the system has available.

What are the three dimension that should be present in main database of currency application?

  The main database outline can contain from 3 to dimensions. At a minimum, the main database must contain the following dimensions:

  • A dimension tagged as time.
  • A dimension tagged as accounts.
  • A market-related dimension tagged as country.

What are the conversion methods supported by Essbase for currency applications?

Different currency applications have different conversion requirements. Essbase supports two conversion methods:

  • Overwriting local values with converted values.
  • Keeping local and converted values.

Either of these two methods may require a currency conversion to be applied at report time. Report time conversion enables analysis of various exchange rate scenarios without actually storing data in the database.

What is CCONV?

After you create a currency conversion application, you convert data values from a local currency to a common, converted currency by using the CCONV command in calculation scripts Ex: CCONV USD;CALC ALL;

What is CCTRACK?

You can use the CCTRACK setting in the essbase.cfg file to control whether Essbase tracks the currency partitions that have been converted and the exchange rates that have been used for the conversions. By default CCTRACK is turned on.

What are the reasons to turn off CCTRACK?

 For increased efficiency when converting currency data between currency partitions, you may want to turn off CCTRACK. For example, you load data for the current month into the local partition, use the DATACOPY command to copy the entire currency partition that contains the updated data, and then run the conversion on the currency partition.

How do you create LRO’s?

Users create linked objects through Essbase Spreadsheet Add-in for Excel by selecting a data cell and choosing a menu item. There is no limit to the number of objects you can link to a cell. The objects are stored on the Essbase Server where they are available to any user with the appropriate access permissions. Users retrieve and edit the objects through the Essbase Spreadsheet Add-in for Excel Linked Objects Browser feature, enabling them to view objects linked to the selected cell.

Does adding or removing links to a cell does not affect the cell contents?

No.LROs are linked to data cells –not to the data contained in the cells. The link is based on a specific member combination in the database. Q.Give a few examples of LRO’s?  Ex1: A sales manager may attach cell notes to recently updated budget items. Ex2: A finance manager might link a spreadsheet containing supporting data for this quarter’s results. Ex3: A product manager might link bitmap images of new products. Ex4: A sales manager may link the URL of a company’s Web site to quickly access the info on the Web

Can you change the member combination associated with any linked object?

You cannot change the member combination associated with any linked object. To move an object to another member combination, first delete it, then use Essbase Spreadsheet Addin for Excel to re-link the object to the desired member combination.

What are the types of Partitions available in Essbase?

Three types of partitions are there.

1. Transparent partition: 

A 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

2. Replicated Partition:

A portion of a database, defined through Partition Manager, used to propagate an update to data mastered at one site to a copy of data stored at another site. Users can access the data as though it were part of their local database.

3. Linked Partition: 

A shared partition that enables you to use a data cell to link two databases. When a user clicks a linked cell in a worksheet, Essbase opens a new sheet displaying the dimensions in the linked database. The user can then drill down those dimensions.

What is the process for designing a partitioned database?

  Here is the suggested process for designing a partitioned database.

  1. Learn about partitions.
  2. Determine whether the database can benefit from partitioning.
  3. Identify the data to partition.
  4. Decide on the type of partition.
  5. Understand the security issues related to partitions.

What are benefits of partitioning?

  Partitioning applications can provide the following benefits:

  • Improved scalability, reliability, availability, and performance of databases
  • Reduced database sizes
  • More efficient use of resources
  • Data synchronization across multiple databases.
  • Outline synchronization across multiple databases.
  • Ability for user navigation between databases with differing dimensionality.

Is overlapping partition valid in all the partitions?

An overlapping partition is allowed in linked partitions, but is invalid in replicated and transparent partitions and generates an error message during validation.

When do you use substitution variables in partitions?

Using substitution variables in partition definitions enables you to base the partition definition on different members at different times.

Can we use attribute values to partition a database?

Yes,You can use attribute functions for partitioning on attribute values. But you cannot partition an attribute dimension.

Can we partition an attribute dimension?

 No, we cannot partition an attribute dimension.

How do you differentiate ASO applications?

You can easily differentiate the ASO database in the Administrative Services Console by the red star beside the application name.

How do you create an ASO application?

ASO has two types of hierarchies: stored and dynamic. The dimension can contain both types of hierarchies (if you enable multiple hierarchies).Other properties that need to be set for dimensions and members include · Dimension Type · Data Storage(store, never share, label only) · Member solve order

  • Alias

You can add dimensions using the visual editor or the rules files. Unlike in block storage ASO does not allow you to preview the outline changes. If you are unsure of the build file, make a backup of your outline before running the new build rule. For ASO databases after the data values are loaded into the level 0 cells of an outline, the database requires no separate calculation step. For retrieving from the ASO database, retrieve and analyze just as in BSO database.

How do you create ASO in the Automated Way?

 The final way of creating an ASO application is by using “Create Application” , “Create Database” ,”Create Outline ” commands using MaxL. Typically this method is used when you are running the MaxL command as a part of the batch job. **Unicode is supported for BSO databases only. **Data Mining is not supported by ASO databases. **MDX is the only mechanism for defining member calculations in databases. Unicode applications use UTF-8 encoding form to interpret and store character text, providing support for multiple character sets. To set up a Unicode application

1.Setup a computer for Unicode support by doing one of

  • Install the for that supports UTF-8 encoding
  • Install a Unicode editor

2. Set the Essbase server to Unicode Mode via Administrative Services or MaxL.

3. Check the Unicode box when creating a new Unicode -mode application.

4. You can also migrate from non-Unicode applications to Unicode applications (but not the other way round).

Report Scripts are outdated but still can be helpful when extracting subsets of data from Essbase for online backups or feeding into other systems. The Wizards Tab of Administrative Services Console menu has the following components

  1. Migration
  2. Aggregate Storage Outline Conversion
  3. Aggregate Storage Partition
  4. User Setup
  5. Data Mining Wizard

Suppose we have assigned Generation 2 and Generation 4 as of now and think of adding generation 3 later some time. Can we build the dimension.

No. If gen 2 and gen 4 exists, we must assign gen

What are attributes?

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 dimension has some attributes like size, package type, and flavor. 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.

How does Attribute dimensions and UDA’s 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.

What are 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, they are: Boolean, date, number, and string.

How can we display UDA’s in reports? How do they impact report performance?

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

Can we have multiple metaoutlines based on one OLAP model in Integration services?

Yes

What are the three primary build methods for building dimensions?

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

How does UDA’s impact database size?

There will be no impact on the database as the UDA’s doesn’t store data in the database.

Can we have an metaoutline based on two different OLAP models.

No.

Can we create UDA’s and apply it to Dense as well as Sparse dimensions?

Yes

How can you display UDA’s in reports?

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

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

The record will be rejected.

We have created an application as unicode mode. Can we change it later to non-unicode mode.

No

What are the types of partitioning options available in Essbase?

  1. Replicated partition. 2. Transparent partition 3. Linked partition.

What is Hyperion Financial Management?

Oracle Hyperion Financial Management is a comprehensive, Web-based application that delivers global financial consolidation, reporting and analysis in a single, highly scalable software solution. Oracle Hyperion Financial Management utilizes today’s most advanced technology, yet is built to be owned and maintained by the enterprise’s finance team. What are the benefits of the Hyperion Financial Management?

  • BENEFITS
  • Accelerate reporting cycles – Reduce closing cycles by days, deliver more timely results to internal and external stakeholders.
  • Improve transparency and compliance – Helps reduce the cost of compliance (as stipulated by the Sarbanes-Oxley Act, electronic filing, and other regulatory requirements) and support disclosure requirements, such as sustainability reporting
  • Perform strategic analysis – Spend less time on processing and more time on value-added analysis
  • Deliver a single truth – Provide a single version of the truth to support financial management and statutory reporting
  • Easily integrate – Integrate not only with Hyperion products but also with your existing infrastructure

Which property helps us to consider using ACE?

A very little population of intercompany data forces us to consider using ACE.

What are the three primary build methods for building dimensions?

The following are the three primary build methods for building dimensions :-

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

What is Hyperion Planning?

Hyperion Planning is an accounting and forecasting application from Oracle formerly controlled by Hyperion Solutions Corporation. It uses Essbase as a database and calculation engine, a relational repository for business method of reasoning, and fuses a web-based user interface.  The Software allows you to add planning or forecast data to actuals.

What are TB First and TB Last?

TB First: The accounts member Opening Inventory in the Sample.Basic database is tagged as TB First. Opening Inventory combines the value of the first month in every quarter and uses that value for that month’s parent.

E.g. the value for Qtr1 is the same as the value for Jan.

TB Last: The accounts member Ending Inventory in the Sample.Basic database is tagged as TB Last. Ending Inventory combines the value for the last month in every quarter and uses that value for that month’s parent.

E.g. the value for Qtr1 is the same as the value for Mar.

Explain why we use Hyperion?

We have IIS for HFM although there is no IIS for OLAP’s. If there is no IIS then it is a time taking process to recover financial data.

Explain data file cache?

It signifies buffer in memory which contains compressed data files (.PAG ).

What is the explanation for a higher efficiency of a bottom-up calculation than a top-down calculation?

The clarification behind this is figuring a higher measure of blocks than required and for the right results to show up there is a prerequisite for making a top-down calculation.

What is the real base for calling a parallel or a serial method for making the calculations?

We will use the serial estimation exactly when we have one processor and when we have more than one the operation can be part into strings that will wear down the diverse available processors.

 

So, this brings us to the end of the Oracle Hyperion Interview Questions blog.This Tecklearn ‘Top Oracle Hyperion Interview Questions and Answers’ helps you with commonly asked questions if you are looking out for a job in Oracle Hyperion or Business Intelligence Domain. If you wish to learn Oracle Hyperion and build a career in Business Intelligence domain, then check out our interactive, Oracle Hyperion Training, that comes with 24*7 support to guide you throughout your learning period.

https://www.tecklearn.com/course/oracle-hyperion-training/

Oracle Hyperion Training

About the course

Oracle Hyperion training provides comprehensive knowledge on how to manage metadata, plan applications, set up dimensions, load data, define forms, business rules, etc., through practical executions. Various subjects included in this course are Oracle EPM, Hyperion interactive core services and various reporting tools. The course is designed to help individuals master all the advanced level techniques of Oracle Hyperion, and successfully clear the Oracle Hyperion Interactive Reporting Certification exam and advance their career.

Why Should you take Oracle Hyperion Training?

  • The average salary of a Hyperion Developer is $115,942 per annum -ZipRecruiter.com.
  • Hyperion has a market share of around 6% globally.
  • Oracle Hyperion help the mid and top-level management to gain insight into the relevant tasks like budgeting, forecasting, financial consolidation, profitability analysis, etc. Hyperion applications helps to build complex and automated business logic that allows to perform tasks with ease.

Curriculum

Introduction to Oracle Hyperion

  • Introduction to Oracle EPM
  • Hyperion system 11 Components
  • BI+ 3 Tier Architecture
  • Client/Server Reporting Architecture
  • BI+ Interfaces
  • Hyperion IR Core Services
  • DAS & LCM Utility
  • Report Process Flow

Interactive Reporting Studio Overview

  • Introduction to IR
  • Relational Vs Multidimensional Reporting Tools
  • ODBC and DSN
  • OCE Optimization

SQL Queries and Important Operations and Functions

  • Building a Query
  • Filters
  • Building Subqueries
  • Processing Queries
  • Estimating Query Size, Appending Queries
  • Other Query Operations

Pivot, Result and Table Section

  • Adding and Building Pivot Section
  • Adding table Section & it’s significance
  • Result Section and its significance

Using JavaScript in Hyperion

  • Introduction to Java Scripting
  • Extracting report’s metadata using Java Scripting
  • Various Operations (Loading, Tools Bar Hiding, Filter etc.) using JavaScript
  • Other JavaScript Utilities

Extracting Reports

  • Working with Report section (Creating Report Groups)
  • Extracting report’s metadata using Java Scripting
  • Advance Reporting
  • Other Reporting Functions

Workspace and Admin Demonstrations

  • Demonstration of Workspace
  • Demonstration of Administration Activities
  • Demonstration of Shared Services

Creating Dashboards using IR Dashboard Section

  • Describe Dashboards and their benefits

IR Dashboard Benefits

Got a question for us? Please mention it in the comments section and we will get back to you.

 

0 responses on "Top Oracle Hyperion Interview Questions and Answers"

Leave a Message

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