Oracle Hyperion Interview Questions & Answers

Question 1. What Is Hyperion ?

Answer :

Hyperion is a file that empowers you to access the data quickly. The association was bought by Oracle in 2007 and has continued upgrading the product. The Fancy name of Hyperion is Business Intelligence and its motto is to support better business decision-making.

Question 2. What Is Hyperion Planning ?

Answer :

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.

Question 3. What Is Oracle Hyperion Financial Management (hfm) ?

Answer :

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.

Question 4. What Are The Different Types Of Log Files ?

Answer :

The important LOG files are:

• Application.log
• Configtool.log
• Essbase.log
• ess base server-install.log
• eas_install.log

Question 5. What Are Attributes ?

Answer :

A grouping of a member in a dimension. You can select and group members based on their related attributes. You can in like manner show an attribute when you perform calculations and use functions.

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

Question 6. What Are Tb First And Tb Last ?

Answer :

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.

Question 7. How Does Attribute Dim Impact Report Performance ?

Answer :

Attribute dim highly impact the report performance as the attributes are calculated vigorously when referenced in the report. For very large number of attribute dim displayed in the report, the performance could extremely decrease. While filling the data, you have applied both the selection and the rejection criteria to a same record. The record will be rejected.

Question 8. Explain Why We Use Hyperion ?

Answer :

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.

Question 9. What Is The Difference Between Uda’s And Attribute Dimensions ?

Answer :

Attribute dimensions offers more flexibility than UDA’s.

Attribute calculations dimensions include five members with the default names sum, count, min, max and avg are automatically created for the attribute dimensions and are computed dynamically.

Question 10. Can We Start And Stop An Application Individually And How To Increase Performance Using This ?

Answer :

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.

Question 11. Explain Custom Based Macro ?

Answer :

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.

Question 12. Explain Data File Cache ?

Answer :

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

Question 13. Which Two Functions Can Produce A Report That Includes Only Elimination Entities ?

Answer :

The two functions are:

1. Fixed Name List
2. Dynamic Name List.

Question 14. Explain Dense And Sparse Dimensions ?

Answer :

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.

Question 15. What Are The Three Primary Build Methods For Building Dimensions ?

Answer :

The three primary build methods for building dimensions are:-

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

Question 16. What Impact Do Attribute Dimensions Have On The Performance Of Reports ?

Answer :

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.

Question 17. What Is The Explanation For A Higher Efficiency Of A Bottom-up Calculation Than A Top-down Calculation ?

Answer :

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.

Question 18. What Is The Real Base For Calling A Parallel Or A Serial Method For Making The Calculations ?

Answer :

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.

Question 19. What Is The Meaning Of A Block Locking System ?

Answer :

The Essbase Services 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.

Question 20. What Are The Two Storage Options Available In Essbase And What Are The Differences?

Answer :

ASO (Aggregate Storage Option) Used for :

1. If we have large number of dimensions (generally more than 10)
2. Most of the dimensions are sparse
3. We cannot write back in ASO. I heard recently that ASO will also have the write back capability.

BSO (Block Storage Option):

• Dimensions are less and dense (recommended values for no of dim are between 4 and 8).
• We can write back hence much suited for planning applications.
• Financial applications are generally dense structures and normal analytical cubes are sparse. Here we also need to understand what these dense and sparse members are. When the intersections or cells of two dimensions mostly contain a value it is dense. Say for example we have two dimensions period(Month or quarter) and region and we are calculation the sales amount.
• Generally maximum regions(countries, cities) will have some sales value for a particular month or quarter. Hence we can say that our period dimensions would be dense. But now instead of period, consider another dimension product. Now there are some products which are sold in only some particular regions hence for them there will be no values in other regions or will have very low percentage of data values hence the structure will become sparse.

Question 21. When Do We Generally Use Build Rules Files ?

Answer :

To automate the process of creating dimensions with thousands of members.

Question 22. Why Do Objects Gets Locked And When Does This Happens?

Answer :

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.

Question 23. Can We Create More Than 2 Dimensions Using Only One Build Rule File ?

Answer :

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

Question 24. What Is Uda( User Defined Attributes). How Are They Different Than Aliases.?

Answer :

UDA represents the class of the members. Aliases are just another names of the members. both are different and has different usage.

Question 25. Can We Query A Member For Its Uda In A Calculation Script ?

Answer :

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

Question 26. How Does Uda’s Impact Database Size ?

Answer :

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

Question 27. How Does Attribute Dimensions And Uda’s Impact Batch Calculation Performance ?

Answer :

1. UDA’s- No Impact as they do not perform any inherent calculations.

2. Attribute dim- No Impact as they perform only dynamic calculations.

Question 28. How Can We Display Uda’s In Reports? How Do They Impact Report Report Performance.

Answer :

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

Question 29. How Is Data Stored In The Essbase Database?

Answer :

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

Question 30. Can We Have Multiple Databases In One Single Application ?

Answer :

Yes. But only one database per application is recommended.

Question 31. Can We Have One Aso Database And One Bso Database In One Single Application. If Yes, How And If No, Why.?

Answer :

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

Question 32. What Is The Role Of Provider Services ?

Answer :

To communicate between Essbase and Microsoft office tools.

Question 33. What Is An Alternative To Create Meta Outline, Rule File And Load Data ?

Answer :

Integration services and in version 11, we have Essbase studio.

Question 34. Can We Start And Stop An Application Individually. How Can This Be Used To Increase Performance.?

Answer :

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

Question 35. A Customer Wants To Run Two Instances Of An Essbase Server On A Same Machine To Have Both Test Env And Development Env On The Same Server. Can He Do That?

Answer :

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.

Question 36. What Is Hybrid Analysis ?

Answer :

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

Question 37. Why Top-down Calculation Less Efficient Than A Bottom-up Calculation? Being Less Efficient, Why Do We Use Them ?

Answer :

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.

Question 38. On What Basis You Will Decide To Invoke A Serial Or Parallel Calculation Method ?

Answer :

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.

Question 39. What Is Block Locking System ?

Answer :

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.

Question 40. Dynamic Calc Decreases The Retrieval Time And Increases Batch Database Calculation Time. How True Is The Statement ?

Answer :

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

Question 41. What Are Lro’s( Linked Reporting Objects) ?

Answer :

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.

Question 42. Types Of Partitions Available In Essbase?

Answer :

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

3. Linked Partition

Question 43. How Can You Display Uda’s In Reports ?

Answer :

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

Question 44. Can We Have Multiple Databases In One Single Application?

Answer :

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

Question 45. How Is Data Stored In The Essbase Database ?

Answer :

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

Question 46. What Is The Role Of Provider Services.?

Answer :

To communicate between Essbase and Microsoft office tools.

Question 47. 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 ?

Answer :

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.

Question 48. What Is Custom Based Macro ?

Answer :

Essbase macros that we write with Essbase calculator functions and special macro functions. Custom-defined macros use an internal essbase macro language that enables you to combine calculation function and operate on multiple input parameters.

Question 49. What Do You Mean By Dirty Block ?

Answer :

A data block containing cells that have been changed since the last calculation. Upper level blocks are marked as dirty if their child blocks are dirty (that is, have been updated)

Question 50. How Do You Optimize Outline ?

Answer :

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

1. Dimension with Accounts tag

2. Dimension with Time tag

3. Largest Dense dimension

4. Smallest dense dimension

5. Smallest Sparse dimension

6. Largest Sparse dimension

Question 51. What Are The Ways To Improve Performance During Data Loads ?

Answer :

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

Question 52. What Are The Design Considerations For Calculation Optimization ?

Answer :

You can configure a database to optimize calculation performance. The best configuration for the site depends on the nature and size of the database.

1. Block Size(8Kb to 100Kb) and Block Density

2. Order of Sparse Dimensions

3. Incremental Data Loading

4. Database Outlines with Two or More Flat Dimensions

5. Formulas and Calculation Scripts

Question 53. When Does Fragmentation Occur ?

Answer :

Fragmentation is likely to occur with the following:

1. Read/write databases that users are constantly updating with data

2. Databases that execute calculations around the clock

3. Databases that frequently update and recalculate dense members

4. Data loads that are poorly designed

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

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

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

Question 55. How Do You Can Prevent And Remove Fragmentation ?

Answer :

You can prevent and remove fragmentation:

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

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

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

Question 56. Why Is Database Restructuring ?

Answer :

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.

Question 57. What Are The Types Of Database Restructuring ?

Answer :

The two ways by which a database restructure is triggered:

1. Implicit Restructures
2. Dense restructure
3. Sparse restructure
4. Outline-only restructure
5. Explicit Restructures

Question 58. What Are The Conditions Affecting Database Restructuring ?

Answer :

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

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

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

Question 59. What Are The Files Used During Restructuring ?

Answer :

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

essxxxxx.pag

Essbase data fileessxxxxx.ind

Essbase index filedbname.esm

Essbase kernel file that contains control information used for db recoverydbname.tct

Transaction control tabledbname.ind

Free fragment file for data and index free fragmentsdbname.otl

Outline file in which is defined all metadata for a database and how data is stored

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

Question 61. Why Do Objects Gets Locked And When Does This Happens ?

Answer :

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

Question 62. How Can We Backup An Aggregate Storage Database?

Answer :

To back up an aggregate storage database, copy the application directory ARBORPATH/app/appname.

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

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

Question 65. What Is The Meaning Of The Two Options From Essbase Regarding Storage And What Separates Them ?

Answer :

The Aggregate Storage Option or ASO:

1. Used for more than ten dimensions
2. The majority of the dimensions will be sparse
3. It is impossible to write back in ASO.

In the next versions we should be able to write back.

The Block Storage Option or BSO:

1. Less dimensions and more dense dimensions

Question 66. With The Aid Of A Single Build Rule File Can We Make At Least 2 Dimensions ?

Answer :

yes, but a different rule file should be used for every dimension.

Question 67. What Is The Meaning Of User Defined Attributes Or Uda And What Is The Difference Between Them And Aliases ?

Answer :

The user defined attributes are referring to the members classes while the aliases are only different ways to name memebres, they are totally separate and have totally separate usages.

Question 68. Is It Possible To Query A Member For Uda In Calculating Scripts ?

Answer :

Yes, we can query members for UDA in calculating scripts.

Question 69. What Impact Has Uda On The Size Of A Database ?

Answer :

Because UDA’s are not needing any space in addition, they don’t have any impact on the size of a database.

Question 70. What Separates Attribute Dimensions From Uda’s ?

Answer :

Attribute dimensions are different from UDA’s because of their improved flexibility. The calculation with the attribute calculations dimensions with five members named: sum, min, max, count and avg is made automatically and dunamicaly.

Question 71. What Impact Have Uda’s And Attribute Dimensions On The Performance Of Batch Calculations ?

Answer :

The UDA’s don’t have any impact because they don’t make inherent calculations and the Attribute dimensions the same have no impact because they just make dynamic calculations.

Question 72. In What Way Can We Show Uda’s In The Reports And What Impact Have They On The Performance Of The Reports?

Answer :

We can’t show UDA’s in the reports so there is no performance change.

Question 73. In What Way Is Data Storage Made In The Database Of Essbase ?

Answer :

In Essbase (a database based on files)the data storage is made in 2GB PAG files that have a sequential way of growing.

Question 74. What File Extensions Are Used For Rule File, Calc Script Or Outline ?

Answer :

The extensions are: .RUL, .CSC and .OTL.

Question 75. What Purpose Do Provider Services Have ?

Answer :

They have the purpose of communication of the Essbase with the office tools of Microsoft.

Question 76. How Can We Create Rule File, Load Data Or Metaoutline In A Different Way ?

Answer :

We can make it using Integration services and the ESSbase studio from the version 11.

Question 77. Is It Possible To Begin And End An Application At The Individual Level And Can This Enhance The Performance ?

Answer :

Yes, it is. We can begin only those applications that have high traffic, in this way we can save server resources, the memory will be filled with databases at the beginning of the application.

Question 78. In What Way Can Migrating Of An Application From The Test Environment Be Made If We Want To Move It In The Production Environment, Also Can We Do This From Windows To Unix Or If The Test Environment Is Different From The Production Environment ?

Answer :

There is  a tool given to us by the Hyperion Administrative services  but this only helps with the migration of the application and the objects database while the data is not migrating. In the other two cases the answer is yes.

Question 79. Can We Run On The Same Machine Two Instances Of The Same Essbase Server And Each One Will Have The Test Environment And The Development Environment?

Answer :

We will respond with yes, because we can have two instances of Essbase with various sets of services on the same machine.

Question 80. What Is The Role Of The Never Consolidate Operator(^)?

Answer :

It stops member from becoming consolidate on all dimensions

Question 81. What Is The True Meaning Of A Hybrid Analysis ?

Answer :

The members with a higher level along with the data associated will stay in the Essbase database while the members of a smaller level and their associated data will live in the relational database.

Question 82. What Is The Real Base For Calling A Parallel Or A Serial Method For Making The Calculations?

Answer :

We will use the serial calculation only when we have one processor and when we have more than one the operation can be split into threads that will work on the various available processors.