Hyperion DRM Interview Questions and Answers

1. What is Hyperion? Explain?

Ans:

• Well, Hyperion is a database that allows the user to access data very quickly compared to the rest of the databases. This company was actually bought by Oracle in the year 2007 and then has made significant improvements to the product.  For Hyperion, we have a fancy name as well which is nothing but Business Intelligence, usually called as BI. The primary use of this is to support and provide value-add services for businesses while taking decisions.
• So most of the time the BI system is actually classified as a Decision Support System (DSS). So this aids the organization level process improvement and efficiencies. All the data is gathered and based on the suggestions the organization will be in a good position to make decisions.

2. What is Hyperion Planning?

Ans:

• The Hyperion planning is nothing but a budgeting and forecasting platform where it was previously owned by Hyperion Solutions Corporation. But all of this is executed under Oracle as they have bought this company in the year 2007. So Hyperion Planning actually uses an Essbase ( Essbase is nothing but a multidimensional database product sold by Hyperion) and calculation engine, repository for harvesting business logic. Further to access all of these services, they have provided a web-based user interface.
• With the help of this software, the user will be able to add planning and forecast related data to the actuals. With the use of Web forms, the users will be able to play around with the data by using Excel SmartView Tool.  All the reporting tools that are provided by Hyperion are really great for planning and have a wide usage.

3. What is HFM, explain?

Ans:

• The HFM stands for Oracle Hyperion Financial Management. As per the name itself states that it is a financial management related tool. It is a web-based tool which helps the users to deliver financial consolidation at the global level and on top of it provides analysis and reporting abilities as well. It is a highly scalable tool which is provided by Oracle.
• This tool can be easily and quickly installed without another hassle and it is integrated directly into the Oracle and non-Oracle based transaction systems. They provide top-notch reporting needs where the organization top tier can enjoy the tailored information easily.

4. List out all the different types of Log files?

Ans:

Within Essbase, they are many log files that are available. But the below-listed log files are considered to be important compared to the others:

• Application log
• Essbase.log
• Configtool.log
• Eas_install.log 
• Essbaseserver-install.log

5. Define what is an attribute?

Ans:

• The attribute is nothing but a group of members that are based on the associated attributes. Using these attributes you will be able to perform calculations and also use calculation functions. A dimension is nothing but a classification of the member.
• For example:  Let’s consider a sample database where the product dimensions are nothing but attributes where they have information like size, package type, flavor etc. So adding up these attributes together to the dimensions will help them to retrieve the information something like this below:
• Diet Coke with 10 Oz with bottles. This type of information is used for generating reports.

6. Explain how Attribute dim impacts the report performances?

Ans:

The report performance is impacted by Attribute dim, as follows:

• The report performance is at high risk because all the attributes are calculated dynamically as per their reference. If we have a very large number of attributes then the report performance could go down drastically.
• The report performance will be compromised if the data is loaded in this fashion. While loading the data if the selection criteria and rejection criteria are applied for the same record. During this scenario, the file will be rejected.

7. What are the main features in Hyperion Planning?

Ans:

As per the recent releases, Oracle Hyperion Planning is offering a wide range of services.

They are as follows:

• Hyperion Planning offers a bunch of new features for users and administrators.
• It provides ASo support with planning
• With the help of flexible import and export process metadata is simplified.
• Provides wide variety of options in reports visualizations

8. Explain how the data is stored in the Essbase database?

Ans:

The Essbase is nothing but a file-based database. The data is stored in the form of PAG files of 2 GB each and they can grow in sequence.

9. Why is Oracle’s Hyperion Financial Management tool useful for financial managers?

Ans:

The Oracle Hyperion Financial Management tool is very useful for the financial managers are as follows:

• It is a boon for financial managers where they can easily consolidate the financial results based on the reports they can execute
• This tool helps them to match up with the global regulatory requirements
• The overall compliance cost is reduced
• The confidence in numbers is supported by great visualized reports

10. What are the key features of Hyperion Financial Management tool?

Ans:

The key features of this tool are:

• It has consolidation feature at global level
• Its web architecture can be expanded
• Dimensional wise it is unlimited
• It can support complete end to end audit trails
• It offers greatest and powerful reporting and analysis tools
• It has robust data integration

11. Within the Hyperion Essa database, can we use the ASO and BSO databases within a single application? If yes please explain.

Ans:

Within the Hyperion Essa database, we cannot use both ASO and BSO databases for a single application. While creating an application the user has to select either ASO database or BSO database. So based on the selection only the users will be able to create the database. Also, the classification is at the application level and not at the database level.

12. What is DRM?

Ans:

• DRM offers a component based approach to resource allocation management.
• It allows you to create resource plans, which specify resource allocation to various consumer groups. You can group users based on the resource requirement.
• DRM offers an easy to use and flexible system by defining distinct independent components.

13. What are the components or elements of DRM?

Ans:

The three main components or elements of DRM are given as follows:

• Resource consumer groups
• Resource plans
• Resource plan directives

14. What is a resource allocation method?

Ans:

• This parameter is used to define the method used to allocate database resources.
• There can be many different resource allocation methods, such as CPU method, idle time, and active session pool.
• These are also called parameters to resource plan guidelines.

15. List some of the benefits of DRM?

Ans:

Following are the benefits of the DRM:

• Enables you to limit the length of time a user session can stay idle and automatically terminates long running SQL statements and user sessions.
• Sets the initial login priorities for various consumer groups
• Uses the concept of active session pool to specify the maximum number of concurrent active sessions for a consumer group.
• The database resource manager will automatically queue all the subsequent requests until the currently running sessions complete.

16. What is a sub plan?

Ans:

A resource sub plan is a plan, which gets the resource to allocate from a higher level plan. It is created in a similar manner as a plan. However, a top level plan gets to allocate 100% of the resource while a sub plan can allocate only the resources allocated to it by the top level plan.

17. What is an execution time limit?

Ans:

• Execution time limit is a type of resource allocation method. It can be set using the MAX_EST_EXEC_TIME parameter.
• If this parameter is specified, Oracle estimates the execution time of the operation before starting the operation.
• If the estimated time exceeds the parameter value, the operation is not started.
• The default value for this parameter is null which indicates no estimated limit is imposed.

18. What is a resource plan?

Ans:

• A resource plan is used to define resources distribution among different consumer groups or other resource plans.
• Resource plans prioritize the resource allocation through the use of 1 to 8 levels, in order of priority highest to lowest.

19. What is a resource plan directive?

Ans:

Resource plan directive is the key element in creating complex resource plans. It assigns consumer groups to resource plans and defines the resource allocation for each.It can also allocate resources to sub plans.

20. What is an undo pool allocation method?

Ans:

• It is a resource plan directive parameter, which controls the total amount of undo (in KB) that can be generated by a consumer group or sub plan.
• It can be set using the UNDO_POOL parameter.
• The default value for this parameter is null which indicates that no limit is imposed.

21. What is automatic consumer group switching?

Ans:

• It is a type of resource allocation method used to automatically switch the consumer group if the switch criterion is met.
• The switch group is specified using the SWITCH_GROUP parameter.
• Switch criteria can be set using the SWITCH_TIME and SWITCH_ESTIMATE parameters.

22. What is Oracle Hyperion Financial Management (HFM)?

Ans:

• Oracle Hyperion Financial Management (HFM) 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, 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.

23. What are the different types of LOG Files?

Ans:

So many log files are there in Essbase, but the important log files are:

• Application.log
• Essbase.log
• Configtool.log
• eas_install.log
• ess base server-install.log
  Suppose we have assigned Generation 2 and Generation 4 as of now and think of adding generation 3 later some time.  Now can we build the dimension? No. If gen 2 and gen 4 exists, we must assign gen 3.

24. What are attributes?

Ans:

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

25. What is TB First and TB Last?

Ans:

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

26. How does Attribute dim impact report performance?

Ans:

• They highly impact the report performance as the attributes are calculated dynamically when referenced in the report. For the very large number of attributes 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. The record will be rejected.

27. Mention the main Features in Hyperion Planning v11.1.2.3?

Ans:

• The latest release of Oracle Hyperion Planning, v11.1.2.3, offers new key features for users and administrators, including ASO support within Planning, more flexibility to import and export metadata, and task list report visualizations. MijaDeering, Principal Solutions Manager at Edgewater Ranzal, provides an overview of these and other features of the newest Planning release.
• Her presentation also includes a live demo of key features and an opportunity for viewers to ask questions and better understand the product’s enhancements.Oracle Hyperion Planning is a centralized, Microsoft Office and Web-based planning, budgeting and forecasting solution that integrates financial and operational planning processes and improves business predictability.  It allows organizations to reduce planning, budgeting, and forecasting cycles by weeks or months and improve accuracy of plans and forecasts.
• Microsoft Office products are used as an interface into Oracle Hyperion Planning, so the learning curve for users is minimized, and Planning speeds up the time it takes to update plans and refresh reports.  The cost of ownership is reduced through superior application deployment, management tools, and packaged data integration.

28. How is data stored in the Essbase database?

Ans:

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

29. What is NoSQL?

Ans:

NoSQL encompasses a wide variety of different database technologies that were developed in response to a rise in the volume of data stored about users, objects and products. The frequency in which this data is accessed, and performance and processing needs. Relational databases, on the other hand, were not designed to cope with the scale and agility challenges that face modern applications, nor were they built to take advantage of the cheap storage and processing power available today.

30. What are the features of NoSQL?

Ans:

When compared to relational databases, NoSQL databases are more scalable and provide superior performance, and their data model addresses several issues that the relational model is not designed to address:

• Large volumes of structured, semi-structured, and unstructured data
• Agile sprints, quick iteration, and frequent code pushes
• Object-oriented programming that is easy to use and flexible
• Efficient, scale-out architecture instead of expensive, monolithic architecture

31. Explain “Polyglot Persistence” in NoSQL?

Ans:

• In 2006, Neal Ford coined the term polyglot programming, to express the idea that applications should be written in a mix of languages to take advantage of the fact that different languages are suitable for tackling different problems.
• Complex applications combine different types of problems, so picking the right language for each job may be more productive than trying to fit all aspects into a single language.Similarly, when working on an e-commerce business problem, using a data store for the shopping cart which is highly available and can scale is important, but the same data store cannot help you find products bought by the customers’ friends—which is a totally different question. We use the term polyglot persistence to define this hybrid approach to persistence.

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

Ans:

It stops members from becoming consolidated on all dimensions.

33. Can We Have Multiple Databases In Single Application?

Ans:

Yes, in this one database per application.

34. What Are Filters?

Ans:

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. What Are Dense And Sparse Dimensions?

Ans:

Dense: A dimension which has the high probability that data exists for every combination of dimension members.

Sparse Dimension: A dimension which has low probability that data exists for every combination of dimension members.

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

Ans:

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

37. Explain Attributes?

Ans:

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

38. What is canceling SQL and terminating sessions?

Ans:

You can specify the directives to cancel long running SQL queries or to terminate long running sessions based on the amount of system resources consumed. In other words, you can cancel SQL queries or kill sessions if consumer group meets certain criteria.The criterion is specified using the SWITCH_TIME and SWITCH_ESTIMATE parameters.The option to CANCEL_SQL or KILL_SESSION can be specified by the SWITCH_GROUP parameter.

39. Define resource consumer groups?

Ans:

A resource consumer group is used to classify users into logical groups based on their resource consumption requirements.

40. What is an active session pool?

Ans:

Oracles uses eight levels of CPU allocation namely, CPU_P1 to CPU_P8, to prioritize and allocate CPU usage among the competing user sessions.

41. The HSP_RATES Dimension includes the following members?

Ans:

• HSP_InputValue
• HSP_InputCurrency
• HSP_Rate_XXX (where XXX is a currency, i.e USD)

42. What are the 2 copy calc scripts called?

Ans:

• HspCRtB
• HspCRtT

43. What Hyperion tools can be used to load data?

Ans:

• HAL Essbase adapter
• EAS – Essbase Application Manager (for versions prior to 4.x)

44. The base time period selected is 12 months, how is the default calendar set up?

Ans:

Months rolling into four quarter parents, and quarters rolling into a total year parent

45. What must be done after creating or updating user and / or group security?

Ans:

Refresh the application including the security from the Planning Desktop

46. Can you list out different types of database restructuring?

Ans:

The following is the list of different types of database restructuring.

They are:

• Implicit Restructures
• Dense restructures
• Sparse restructures
• Outline-only restructure
• Explicit restructure

47. How can you prevent and also remove any fragmentation?

Ans:

The following are the steps where it will help the users to prevent the fragmentation happening.

They are:

• By appropriately designing the data loads and making the process smoother
• Sorting the load records based on the dimensions and members, this will ease up the data load process
• By grouping the required sparse members, data loads can be optimized

The following is the list of things that the user has to follow so that they can remove fragmentation :

• Perform a database export option
• Deleting all the data within the database with CLEARDATA and do follow up tasks of reloading the file
• A forced restructure of the database

48. How can you measure the fragmentation?

Ans:

The following is the list of methods where the fragmentation can be measured:

• By using average clustering ratio
• Average fragmentation quotient

49. Explain when fragmentation occurs?

Ans:

The following is the list of factors where the fragmentation occurs:

• Within read/write databases, if the user is constantly updating the data
• If the databases are continuously executing calculations 24/7

50. What are the types of partitioning options available in Essbase?

Ans:

They are three types of partitioning options available in Essbase, they are:

• Replicated partition
• Transparent partition
• Linked partition

51. When do we generally use build rules files?

Ans:

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

52. What are the three primary build methods for building dimensions?

Ans:

• Generation references
• level references
• Parent-Child references.

53. In what case we can define generation 1 to a field in generation build method.

Ans:

We cannot define that as Generation 1 is not valid.

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

Ans:

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

55. Can we create more than 2 dimensions using only one build rule file.

Ans:

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

56. What is UDA( user defined attributes). How are they different from Aliases?

Ans:

UDA represents the class of the members. Aliases are just another name for the members. Both are different and have different usage.

57. How does UDA’s database size impact?

Ans:

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

58. What is the difference between UDA and Attribute dimensions?

Ans:

Attribute dimensions provide 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 calculated dynamically.

59. How does Attribute dimensions and UDA’s impact batch calculation performance?

Ans:

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

Attribute dim: No Impact as they perform only dynamic calculations.

60. Name some different types of LOG files?

Ans:

The different types of LOG files are:

• Eas_install.log
• Ess base server-install.log
• Essbase.log
• Application.log
• Configtool.log

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

Ans:

The two functions are:

• Dynamic name list
• Fixed name list

62. What is the meaning of a block locking system?

Ans:

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 a block locking system. 

63. Name the three primary build methods for building dimensions?

Ans:

The three primary build methods for building dimensions are:

• Level references
• Parent-child references
• Generation references

64. When do we use build rules files?

Ans:

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

65. What is the parallel degree limit?

Ans:

Parallel degree limit is an allocation method for degree of parallelism. It specifies the maximum degree of parallelism for an operation. It can be set using the PARALLEL_DEGREE_LIMIT_P1 parameter. The default value of this parameter is null which indicates no limit is imposed.

66. What is the role of provider services?

Ans:

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

67. What is a hybrid analysis?

Ans:

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

68. What are the Filters?

Ans:

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.

69. How does Essbase recover from a database crash?

Ans:

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

70. How do you optimize outline? 

Ans:

Usually the outline is optimized using the hourglass design for dimension ordering :

• Dimension with Accounts tag
• Dimension with Time tag
• Largest Dense dimension
• Smallest dense dimension
• Smallest Sparse dimension
• Largest Sparse dimension

71. What are the ways to improve performance during data loads? 

Ans:

There are several ways to optimize load:

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

72. What are the design considerations for calculation optimization? 

Ans:

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

73. When does Fragmentation occur? 

Ans:

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

74. How can you measure fragmentation? 

Ans:

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

75. How can you prevent and remove fragmentation? 

Ans:

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.

76. Why is database restructuring? 

Ans:

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.

77. What are the types of database restructuring? 

Ans:

The two ways by which a database restructure is triggered:

• Implicit Restructures
• Dense restructure
• Sparse restructure
• Outline-only restructure
• Explicit Restructures

78. What are the conditions affecting Database restructuring? 

Ans:

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.

79. What are the actions that improve performance for restructuring? 

Ans:

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 reduces the amount of restructuring required.

80. Which restructure operations are faster? 

Ans:

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

81. What is PBCS?

Ans:

PBCS is a web-based planning tool on track to have identical features to the on-premise solution. The product has several Cloud specific features such as EPM Automate to push data from your system to the cloud. It includes two Planning instances (Dev/Test and Production), Life Cycle Management (LCM) support for migrations and automated nightly backups of your entire environment.

82. What Oracle EPM products are available today?

Ans:

Hyperion Planning and Essbase (if built through a planning application) are available today through PBCS. Hyperion Financial Management (HFM) is not yet available, but seems to be next in line for the move to the cloud, while no plans seem to have surfaced for the hierarchy management tools Data Relationship Management (DRM) and Enterprise Performance Management Architect (EPMA).

83. Which is right for us, on-premise or cloud?

Ans:

The decision really comes down to your priorities, but here are some pros and cons:

On premise advantages:

• Application sits behind your own firewall
• Sophisticated automation data and hierarchy builds
• Stronger integration with other systems
• Drill back to on premise EBS

Cloud advantages:

• No capital costs, low monthly subscription cost
• Lower TCO – data center, hardware, DR, IT team
• Less/no reliance on IT
• Scalability – no new hardware required for expansion

84. Why Are Filters Used?

Ans:

If you want to grant access to all dimensions then you wouldn’t use a filter. Just grant read access for the database to the user and/or group. The only reason you would use a filter is if you wanted to restrict access.

85. What Is Uda (user Defined Attributes). How Are They Different Than Aliases?

Ans:

UDA represents the class of the members. Aliases are just other names of the members. Both are different and have different usage.