Training
On
Oracle Hyperion/OBIEE
Products
Suite
On
Oracle Hyperion/OBIEE
Products
Suite
Basics of Hyperion Essbase
Hyperion Essbase
I. Essbase Versions/ New features/ Differences:
1. What are new features in 7.x version?• Aggregate Storage option
• Hyperion Hub
• Triggers
• Hyperion Hub
• Triggers
2. What is MOLAP? What is MDDB? What are features available with Multidimensional Data base?
• Essbase OLAP Server contains multidimensional databases that support analysis and management reporting applications that are described as online analytical processing (OLAP) applications.
• A multidimensional database supports multiple views of data sets for users who need to analyze the relationships between data categories.
• Multidimensional databases consolidate and calculate data to provide different views. Only the database outline, the structure that defines all elements of the database, limits the number of views.
• With a multidimensional database, users can pivot the data to see information from a different viewpoint, drill down to find more detailed information, or drill up to see an overview.
3. What Essbase Administration Services will do In Essbase?
Ans: Essbase Administration Services is for managing and maintaining Essbase. Essbase Administration Services consists of a client console and a middle tier server that communicate directly with Essbase OLAP Servers.
4. What are differences between Application Manager and Essbase Administration services?
Ans: With Admin services,
• We can run calc scripts directly from editor.
• Migrate application from one server to other server.
• You can restructure database from admin console.
• You can start and stop OLAP server.
• We can run calc scripts directly from editor.
• Migrate application from one server to other server.
• You can restructure database from admin console.
• You can start and stop OLAP server.
II. Essbase Client/Server Architecture
1. What server agent will do?
• The server runs a Server Agent (ESSBASE) process that acts as a traffic coordinator for all user requests to Essbase applications.
• Administrative requests, such as logging in and logging out, starting and stopping applications and databases, and viewing user security information, are handled by the Essbase Server Agent (ESSBASE).
• Administrative requests, such as logging in and logging out, starting and stopping applications and databases, and viewing user security information, are handled by the Essbase Server Agent (ESSBASE).
2. What ESSSVR process will do?
Application server (ESSSVR) handles client requests for data, such as data loads, calculations, spreadsheet reports, and data lock and unlock..
III. Application/database Creation and settings
1. What files will be created once you created application?
.app, .apb, and application log file.
.app, .apb, and application log file.
2. I have 4 databases in my application then how many database log files can be created in Application?
Only one application log
(Note: Only one app log is created for one application)
Only one application log
(Note: Only one app log is created for one application)
3. What files will be created once you created database?
.db, .dbb, .esm, .tct, and .ind
.db, .dbb, .esm, .tct, and .ind
IV. Outline creation and settings
1. What is outline and what is it contains?
Database outlines define the structure of a multidimensional database, including all the dimensions, members, aliases, tags, types, consolidations, and mathematical relationships. The structure defined in the outline determines how data is stored in the database.
When a database is created, Essbase creates an outline for that database automatically. The outline has the same name as the database (dbname.OTL).
2. What is difference between non-attribute/standard and attribute dimensions?
Standard dimensions represent the core components of a business plan and often relate to departmental functions.
Attribute dimensions are a special type of dimension and are associated with standard dimensions. Through attribute dimensions, you group and analyze members of your standard dimensions. It doesn’t associate any data.
Attribute dimensions are a special type of dimension and are associated with standard dimensions. Through attribute dimensions, you group and analyze members of your standard dimensions. It doesn’t associate any data.
3. Why they classified standard dimensions as dense and sparse? What is difference between Dense and Sparse dimensions?
Essbase maximizes performance by dividing the standard dimensions of an application into two types: dense dimensions and sparse dimensions.
Most multidimensional databases are inherently sparse: they lack data values for the majority of member combinations. A sparse dimension is a dimension with a low percentage of available data positions filled.
Most multidimensional databases also contain dense dimensions. A dense dimension is a dimension with a high probability that one or more data points is occupied in every combination of dimensions.
Most multidimensional databases are inherently sparse: they lack data values for the majority of member combinations. A sparse dimension is a dimension with a low percentage of available data positions filled.
Most multidimensional databases also contain dense dimensions. A dense dimension is a dimension with a high probability that one or more data points is occupied in every combination of dimensions.
4. What is difference between generation and level?
Generation: Any member can have only one generation and Top to Bottom
Level: Any member can have more than one level and Bottom to Top
Generation: Any member can have only one generation and Top to Bottom
Level: Any member can have more than one level and Bottom to Top
5. What are storage properties available in Essbase?
Store Data:
Computed, when the default calculation script is run.
Dynamic Calc Member (do not store data).
Computed at Reporting time. Not stored in HDD.
Dynamic Calc and Store Member.
Same as above, but after first computation, starts behaving as Stored Member.
Shared Member (do not store data).
Explicit sharing for implementing Alternate Hierarchy.
Never share data.
Explicitly mention that there should be no implicit sharing.
Label Only.
Not stored. Not computed. Used just for grouping purpose.
Computed, when the default calculation script is run.
Dynamic Calc Member (do not store data).
Computed at Reporting time. Not stored in HDD.
Dynamic Calc and Store Member.
Same as above, but after first computation, starts behaving as Stored Member.
Shared Member (do not store data).
Explicit sharing for implementing Alternate Hierarchy.
Never share data.
Explicitly mention that there should be no implicit sharing.
Label Only.
Not stored. Not computed. Used just for grouping purpose.
6. What is difference between Dynamic calc and Dynamic calc and store?
Dynamic calc:
The data associated with the member is not calculated until requested by a user. The calculated data is not stored; it is discarded after the request is completed.
The data associated with the member is not calculated until requested by a user. The calculated data is not stored; it is discarded after the request is completed.
Dynamic calc and store:
The data associated with the member is not calculated until it is requested by a user. The calculated data is then stored.
7. What is shared member? What are uses of shared member? What are features and limitations of shared member?
• The data associated with the member comes from base member with the same name. The shared member stores a pointer to data contained in the other member and the data is only stored once.
• Shared members are typically used to calculate the same member across multiple parents.
• Using shared members lets you use members repeatedly throughout a dimension. Essbase stores the data value only once, but it displays in multiple locations. This offers considerable space saving as well as processing efficiency.
Features:
- You can have an unlimited number of shared members with the same name.
- You can assign aliases to shared members.
Limitations:
- The shared members must be in the same dimension.
- You should not create an outline where shared members are located before
8. What is implicit sharing?
Some members are shared even if you don’t explicitly set them as shared.
9. When does it happen/ eg of implicit sharing.
1 Single PC-C
2 Parent with all children as ~
3 Parent with only one child with a consolidation, all other with ~
1 Single PC-C
2 Parent with all children as ~
3 Parent with only one child with a consolidation, all other with ~
10. What is never share member?
Not allow members to be shared implicitly.
Not allow members to be shared implicitly.
11. When you want to use it?
• If you do not want a member to be shared implicitly, mark the parent as Never Share so that the data is duplicated, and is not shared.
• If you do not want a member to be shared implicitly, mark the parent as Never Share so that the data is duplicated, and is not shared.
12. What is Label only member? What are uses of label only members? What are limitations on Label only member?
• Label only members have no data associated with them.
• Use them to group members or to ease navigation and reporting from the Spreadsheet Add-in. Typically, you should give label only members the no consolidation property.
• You cannot associate attributes with label only members. If you tag as label only a base dimension member that has attributes associated with it, Essbase removes the attribute associations and displays a warning message.
• Use them to group members or to ease navigation and reporting from the Spreadsheet Add-in. Typically, you should give label only members the no consolidation property.
• You cannot associate attributes with label only members. If you tag as label only a base dimension member that has attributes associated with it, Essbase removes the attribute associations and displays a warning message.
13. What is significance of ‘~’?
When a member has the ~ operator, Essbase does not use it in the consolidation to its parent.
14. (What are the types of dimensions and what are the features of each one of them?
A dimension type is a property that Essbase provides that adds special functionality to a dimension.
o Time, Accounts, Country, Currency and Attribute.
14. (What are the types of dimensions and what are the features of each one of them?
A dimension type is a property that Essbase provides that adds special functionality to a dimension.
o Time, Accounts, Country, Currency and Attribute.
15. What is the use of two pass calculation?
• Your combination of data and calculation needs may require the use a calculation script to calculate a formula twice, instead of two-pass tagging to preserve accuracy.
• You can use a two-pass calculation on member formulas that need to be calculated twice to produce the correct value.
• Whenever possible, Essbase calculates two-pass formulas at the data block level, calculating the two-pass formulas at the same time as the main calculation. Thus, Essbase does not need to do an extra calculation pass through the database. How Essbase calculates the two-pass formulas depends on whether there is a dimension tagged as time as well as a dimension tagged as accounts.
• You can use a two-pass calculation on member formulas that need to be calculated twice to produce the correct value.
• Whenever possible, Essbase calculates two-pass formulas at the data block level, calculating the two-pass formulas at the same time as the main calculation. Thus, Essbase does not need to do an extra calculation pass through the database. How Essbase calculates the two-pass formulas depends on whether there is a dimension tagged as time as well as a dimension tagged as accounts.
16. What is the use of Time balance calculations? If we want to work on TB what we required?
• TB provides instructions to Essbase about how to calculate the data in a dimension tagged as accounts.
• To use these tags, you must have a dimension tagged as accounts and a dimension tagged as time.
• The first, last, average, and expense tags are available exclusively for use with accounts dimension members.
• Time Balance Last The value for the last child member is carried to the parent.
• Time Balance First The value for the first child is carried to the parent.
• To use these tags, you must have a dimension tagged as accounts and a dimension tagged as time.
• The first, last, average, and expense tags are available exclusively for use with accounts dimension members.
• Time Balance Last The value for the last child member is carried to the parent.
• Time Balance First The value for the first child is carried to the parent.
17. What DTS will do in Essbase?
• In order to calculate period-to-date values dynamically, you need to use a Dynamic Time Series member for a period on the dimension tagged as time.
• Use built in Dynamic Time series calculations for period – to – date calculations such as YTD, QTD, & MTD accumulations. DTS calculations are on the fly and require time related dimension to be tagged as Time.
• You do not create the Dynamic Time Series member directly in the database outline. Instead, you enable a predefined Dynamic Time Series member and associate it with an appropriate generation number.
• Dynamic Time Series members are not displayed as members in the database outline. Instead, Essbase lists the currently active Dynamic Time Series members in a comment on the time dimension.
V. Dimension Building and Loading data
• Use built in Dynamic Time series calculations for period – to – date calculations such as YTD, QTD, & MTD accumulations. DTS calculations are on the fly and require time related dimension to be tagged as Time.
• You do not create the Dynamic Time Series member directly in the database outline. Instead, you enable a predefined Dynamic Time Series member and associate it with an appropriate generation number.
• Dynamic Time Series members are not displayed as members in the database outline. Instead, Essbase lists the currently active Dynamic Time Series members in a comment on the time dimension.
18. What is the importance of variance reporting? What are various reporting properties? To do expense reporting what we required?
• The variance reporting calculation requires that any item that represents an expense to the company must have an expense reporting tag.
• Essbase provides two variance reporting properties: expense and non-expense. The default is non-expense.
• Variance reporting properties define how Essbase calculates the difference between actual and budget data in members with the @VAR or @VARPER function in their member formulas.
• We required account dimension tag.
• Essbase provides two variance reporting properties: expense and non-expense. The default is non-expense.
• Variance reporting properties define how Essbase calculates the difference between actual and budget data in members with the @VAR or @VARPER function in their member formulas.
• We required account dimension tag.
V. Dimension Building and Loading data
1. What we can do with Rule file?
• Rule file automate loading and maintenance of dimensions in an outline.
• With rule file,
-Add new dimensions and members to the database.
-Loading data.
• Rule file automate loading and maintenance of dimensions in an outline.
• With rule file,
-Add new dimensions and members to the database.
-Loading data.
2. In how many ways you can build outline using rule file?
- Parent/Child reference
- Generation Reference
- Level Reference
- Generation Reference
- Level Reference
3. In how many ways you can load data into Database?
- Essxxxxx.pag and Essxxxx.Ind
VI. Calculation Part
VI. Calculation Part
1. In how many ways we can calculate database?
- Consolidation operators
- Member formula
- Calculation Scripts
- Consolidation operators
- Member formula
- Calculation Scripts
2. What is intelligent calculation? What is the use of intelligent calculation?
A calculation method that tracks which data blocks have been updated since the last calculation.
When Intelligent Calculation is enabled, Essbase calculates only dirty blocks and their dependent parents.
A calculation method that tracks which data blocks have been updated since the last calculation.
When Intelligent Calculation is enabled, Essbase calculates only dirty blocks and their dependent parents.
Note: Change in Member Formula or Addition of members does not mark the block Dirty. So it’s better to turn intelligent calc OFF.
3. What is dirty block?
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).
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).
4. What is difference between formula and calc script?
5. What is the use of Fix command? What are benefits we will get once you use FIX command on Dense/Sparse dimension(s)?
The FIX command is particularly useful to calculate a carefully defined subset of the values in a database.
When you use the FIX command only on a dense dimension, Essbase retrieves the entire block that contains the required value or values for the member or members that you specify. Thus, I/O is not affected, and the calculation performance time is improved.
When you use the FIX command on a sparse dimension, Essbase retrieves the block for the specified sparse dimension member or members. Thus, I/O may be greatly reduced.
6. What is use of @XREF function?
A data value from another database to be used for calculation of a value from the current database
A data value from another database to be used for calculation of a value from the current database
7. What is substitution variable? What is use of substitution variable? What is the limitation? At which level we can set substitution variable?
• Substitution variables act as placeholders for information that changes regularly;
• You can use substitution variables in formulas that you include in a calculation script.
• You cannot use substitution variables in formulas that you apply to the database outline.
• You can set substitution variables at the server, application, and database levels.
• You can use substitution variables in formulas that you include in a calculation script.
• You cannot use substitution variables in formulas that you apply to the database outline.
• You can set substitution variables at the server, application, and database levels.
8. What is optimal block size and block density to improve calculation performance?
Upto > 100 kb in Windows
Upto > 200 kb in Unix
Upto > 200 kb in Unix
9. How many types of restructure methods available in Essbase?
– Full Restructure
– Sparse Restructure
– Outline only Restructure
VII. Reporting part
– Full Restructure
– Sparse Restructure
– Outline only Restructure
VII. Reporting part
1. How can the data be extracted from essbase Cubes?
1. Report script
2. Excel spread sheet add-in
3. VBA
2. Excel spread sheet add-in
3. VBA
VIII. Optimization
1. How the dimensions should be arranged in an outline. (What should be the order of dimension in the outline)
a. Large members Dense dimension
b. Small members Dense dimension
c. Small members Sparse dimension
d. Large members Sparse dimension
e. Attribute dimensions
a. Dimension tagged accounts if it is dense.
b. Dense dimensions in outline or CALC DIM statement order.
c. Dimensions tagged as Accounts if it is sparse.
d. Sparse dimensions in outline order or CALC DIM statement order.
e. Two-pass calculations on members in the Accounts tagged dimension.
b. Small members Dense dimension
c. Small members Sparse dimension
d. Large members Sparse dimension
e. Attribute dimensions
2. Calculation order of the dimensions
a. Dimension tagged accounts if it is dense.
b. Dense dimensions in outline or CALC DIM statement order.
c. Dimensions tagged as Accounts if it is sparse.
d. Sparse dimensions in outline order or CALC DIM statement order.
e. Two-pass calculations on members in the Accounts tagged dimension.
3. What are optimization techniques you used in Essbase?
For data loading:
• Grouping Sparse Member Combinations
• Positioning Data in the Same Order As the Outline
• Loading from the Essbase OLAP Server
• Making the Data Source As Small As Possible
• Making Source Fields As Small As Possible
• Managing Parallel Data Load Processing
For data loading:
• Grouping Sparse Member Combinations
• Positioning Data in the Same Order As the Outline
• Loading from the Essbase OLAP Server
• Making the Data Source As Small As Possible
• Making Source Fields As Small As Possible
• Managing Parallel Data Load Processing
For Calculation:
• Using Parallel Calculation
• Using Formulas
• Using Parallel Calculation
• Using Formulas
• Managing Caches to Improve Performance
• Using Two-Pass Calculation
• Aggregating #MISSING Values
• Removing #MISSSING Blocks
$ARBORPATH\Bin directory.
• Using Two-Pass Calculation
• Aggregating #MISSING Values
• Removing #MISSSING Blocks
IX. Security and Administration
1. What information contains in ESSBASE.SEC file? Where is Essbase.sec file stored?
All information about users, groups, passwords, privileges, filters, applications, databases, and their corresponding directories is stored in the ESSBASE.SEC file
Location: All information about users, groups, passwords, privileges, filters, applications, databases, and their corresponding directories is stored in the ESSBASE.SEC file
$ARBORPATH\Bin directory.
X. Partitioning.
1. What is partitioning? What are uses of partitioning? How many types of partition available in Essbase?
1. What is partitioning? What are uses of partitioning? How many types of partition available in Essbase?
A partition is the piece of a database that is shared with another database.
Essbase Partitioning is a collection of features that makes it easy to design and administer databases that span Hyperion Essbase applications or servers.
Uses:
Uses:
Synchronize the data in multiple partitioned databases. Essbase tracks changes made to data values in a partition and provides tools for updating the data values in related partitions.
● Synchronize the outlines of multiple partitioned databases. Essbase tracks changes made to the outlines of partitioned databases and provides tools for updating related outlines.
Types:
A replicated partition is a copy of a portion of the data source that is stored in the data target.
A replicated partition is a copy of a portion of the data source that is stored in the data target.
A transparent partition allows users to access data from the data source as though it were stored in the data target. The data is, however, stored at the data source, which can be in another application, in another Essbase database, or on another OLAP Server
A linked partition sends users from a cell in one database to a cell in another database. This gives users a different perspective on the data.
XI. Essbase Log files
How many types of log files are in Essbase?
XII. Backup/Recovery
You should regularly back up the server, application, and database files.
It is important to back up all .ind and .pag files related to a database because a single database can have multiple .ind and .pag files.
Note: Remember, the Agent should be shut down before the essbase.sec file is backed up.
Note: Remember, the Agent should be shut down before the essbase.sec file is backed up.
XIII. Others
You can verify an outline automatically when you save it or you can verify the outline manually at any time. When verifying an outline, Analytic Services checks the following items:
●All member and alias names are valid. Members and aliases cannot have the same name as other members, aliases, generations, or levels.
●Only one dimension is tagged as accounts, time, currency type, or country.
●Shared members are valid
●Level 0 members are not tagged as label only.
●Label-only members have not been assigned formulas.
●The currency category and currency name are valid for the currency outline.
●Dynamic Calc members in sparse dimensions do not have more than 100 children.
●If a parent member has one child and if that child is a Dynamic Calc member, the parent member must also be Dynamic Calc.
●If a parent member has one child and if that child is a Dynamic Calc, Two-Pass member, the parent member must also be Dynamic Calc, Two-Pass.
●Only one dimension is tagged as accounts, time, currency type, or country.
●Shared members are valid
●Level 0 members are not tagged as label only.
●Label-only members have not been assigned formulas.
●The currency category and currency name are valid for the currency outline.
●Dynamic Calc members in sparse dimensions do not have more than 100 children.
●If a parent member has one child and if that child is a Dynamic Calc member, the parent member must also be Dynamic Calc.
●If a parent member has one child and if that child is a Dynamic Calc, Two-Pass member, the parent member must also be Dynamic Calc, Two-Pass.
●The two names of members of Boolean attribute dimensions are the same as the two Boolean attribute dimension member names defined for the outline.
●The level 0 member name of a date attribute dimension must match the date format name setting (mm-dd-yyyy or dd-mm-yyyy). If the dimension has no members, because the dimension name is the level 0 member, the dimension name must match the setting.
●The level 0 member name of a numeric attribute dimension is a numeric value. If the dimension has no members, because the dimension name is the level 0 member, the dimension name must be a numeric value.
●Attribute dimensions are located at the end of the outline, following all standard dimensions.
●Level 0 Dynamic Calc members of standard dimensions have a formula.
●Formulas for members are valid.
●In a Hybrid Analysis outline, only the level 0 members of a dimension can be Hybrid Analysis-enabled.
During outline verify, Analytic Services also performs the following conversions to appropriate numeric attribute dimension member names and displays them in the outline:
●It moves minus signs in member names from the front to the end of the name; for example, -1 becomes 1-.
●It strips out leading or trailing zeroes in member names; for example, 1.0 becomes 1, and 00.1 becomes 0.1.
●Attribute dimensions are located at the end of the outline, following all standard dimensions.
●Level 0 Dynamic Calc members of standard dimensions have a formula.
●Formulas for members are valid.
●In a Hybrid Analysis outline, only the level 0 members of a dimension can be Hybrid Analysis-enabled.
During outline verify, Analytic Services also performs the following conversions to appropriate numeric attribute dimension member names and displays them in the outline:
●It moves minus signs in member names from the front to the end of the name; for example, -1 becomes 1-.
●It strips out leading or trailing zeroes in member names; for example, 1.0 becomes 1, and 00.1 becomes 0.1.
Understanding the Essbase Kernel
The Essbase Kernel provides the foundation for a variety of functions of the Essbase server. These
functions include data loading, calculations, spreadsheet lock&send, partitioning, and restructuring.
The Essbase Kernel reads, caches, and writes data; manages transactions; and enforces transaction semantics to ensure data consistency and data integrity.
functions include data loading, calculations, spreadsheet lock&send, partitioning, and restructuring.
The Essbase Kernel reads, caches, and writes data; manages transactions; and enforces transaction semantics to ensure data consistency and data integrity.
The Essbase Kernel has the following functions:
• Handles disk storage and caching of Essbase files
• Handles data retrieval
• Handles data updates
• Controls input-output functions related to Essbase
• Consolidates free space for re-use
• Manages concurrent operations
• Recovers databases after a server crash
• Issues locks
• Handles disk storage and caching of Essbase files
• Handles data retrieval
• Handles data updates
• Controls input-output functions related to Essbase
• Consolidates free space for re-use
• Manages concurrent operations
• Recovers databases after a server crash
• Issues locks
• Manages transactions
Essbase Kernel Components
The Essbase Kernel contains components that control all aspects of retrieving and storing data:
The Index Manager finds and tracks the location of requested data.
The Allocation Manager is part of the Index Manager, allocates space and manages some file operations.
The Data Block Manager retrieves the data pointed to by the index and stores the data.
The LRO Manager handles retrieval and storage of linked reporting objects (LROs).
The Lock Manager handles the locking of data blocks to regulate concurrent data access.
The Transaction Manager tracks transactions and handles internal commit and abort operations.
Essbase.cfg usage
You can define storage settings for all databases on the Hyperion Essbase server by changing values in the configuration file essbase.cfg.
The Essbase Kernel contains components that control all aspects of retrieving and storing data:
The Index Manager finds and tracks the location of requested data.
The Allocation Manager is part of the Index Manager, allocates space and manages some file operations.
The Data Block Manager retrieves the data pointed to by the index and stores the data.
The LRO Manager handles retrieval and storage of linked reporting objects (LROs).
The Lock Manager handles the locking of data blocks to regulate concurrent data access.
The Transaction Manager tracks transactions and handles internal commit and abort operations.
Essbase.cfg usage
You can define storage settings for all databases on the Hyperion Essbase server by changing values in the configuration file essbase.cfg.
