Oracle’s Hyperion Essbase allows users to model, analyze and interpret the most complex business scenarios. Essbase is an object oriented database that provides users with multidimensional analysis capabilities.
Essbase Databases are often called “Cubes” and are defined by dimensions, which themselves are hierarchical groups of members Data is organized into cross sectional groups that can be accessed by users depending on what sections of the hierarchal dimensions they wish to see. The Dimensions are hierarchical representations of descriptors that business users are familiar with, such as a Product Hierarchy.
By simply choosing any point in the various dimension hierarchies users are instantly presented with the data values. Users can drill up or down, or users can pivot different dimensions to form new cross sections and better analyze the information.
Essbase is optimized to support On-Line Analytical Processing (OLAP) as opposed to the more traditional transaction processing (OLTP) found in relational databases. This enables rapid response times for large volumes of users and large volumes of information.
Essbase gets its name from Extended Spreadsheet Database and is commonly accessed via a spreadsheet add-in that provides users the capability to analyze information in user friendly environments such as Microsoft Excel. Essbase can accept data input from end users which makes it a very capable budgeting tool in addition to its analytic capabilities. Essbase also contains a very powerful calculation engine.
Essbase has two distinct storage options Aggregate Storage Option (ASO) and Block Storage Option (BSO) each one has its own unique significance.
The outline is a tool that gives you a visual reference to how the data is stored in database and how different elements relate to each other. The Essbase outline is the framework or base platform, upon which entire database is built.
The outline is comprised of dimensions and members, dimensions in the Cubes broadly explained as Standard (Which represents the business model) and Attribute (Classified based on character such as Size, Color etc) dimensions and members are children of the dimensions.
The standard dimensions are either Dense or Sparse, the classification is done based on the probability for the availability of the data, Example: Coke is a product which may or may not sell in the Chennai location but the measure sales and Day in time dimension should be available, so the product, location dimensions are sparse and measure, time are dense dimension
Storage space, Retrieval performance and calculation performances factors decide to use the required member storage properties such as stored data or dynamic calc etc available in Essbase.
Extra functionalities such as Time balance, Variance reporting, Attribute calculations can be achieved by tagging the dimension with the dimension type.
The dimensions and members names can be localized using the alias table and default table is the default alias table and number of tables depends on the versions of Essbase.
UDA is User Defined Attributes which represents a class of members and it is not level specific and it will not affect the database size and retrieval performance and these can’t be used in reports, UDA’s are some what similar to Attribute Dimension but Attribute dimensions have extra feature that is Attribute Dimension Calculations dimension which holds some default calculations like sum, avg, min, max, count and it shows effect on the retrieval performance since default member storage property for Attribute dimension members is dynamic calc and we can’t change the property.
Till the outline is restructured there is no point of cells and blocks, once restructure completed index file will be created and after data load the page files will be created with size of 2GB each and there are no page and index terminologies in ASO applications.
While restructuring Essbase creates temporary files such as .otn for outline file, .pan files for page file etc, during Essbase database crashes the data can be recovered using the .esm file which is Essbase kernel file.
The outline building and data loading can be automated using Rule files if the data file is not well formatted otherwise the data file can be loaded directly called free form loading but here new members will not be build.
Baby Steps to learn Essbase:
Step 1) Creating Outline and Loading Data:
1) How to create an Outline in Essbase.
2) How to create the dimension member properties through rule files.
3) How to load the data using rule files.
4) About restructuring in Essbase.
Step 2) What happens behind the screen of Essbase:
1) Dirty and Clean blocks.
2) Files and Extensions in Essbase.
Step 3) How to use the elements available in Essbase:
1) When do I use If and FIX statement.
2) How to get slice of data from other database.
3) Variance Reporting Essbase.
4) Time Balance Property.
5) Difference between ASO and BSO.
6) Optimization Techniques in Essbase.
