With this series of posts, I would like to share my knowledge in Hyperion, lessons learned and few practices.
The very first questions that needed exact explanation when I began understanding Hyperion Essbase were:
What exactly is Essbase?
Why is it necessary or is important?
What is the concept behind these Dimensions(Dense , Sparse)?
What are Blocks and how do we calculate the number of Blocks and block size?
Essbase which stands for "Extended Spread Sheet dataBASE" was originally developed by ARBOR software in early 1990s.
Arbor software noticed that companies were beginning to use spreadsheets not only for presentation of information but also as a place to Store Data and Business Logic.
They decided to build a database for spreadsheets and thus Essbase evolved.
Later in 1998, Arbor merged with Hyperion Software.
In 2007, Oracle acquired Hyperion Solutions corporation.
And Essbase as technically defined in the books is A multi-threaded OLAP database software that takes advantage of symmetric multi processing hardware platforms and is based upon Web-deployable, thin-client architecture.
The basic aim to build Essbase was:
To load data in to and retrieve data from database as quickly as possible.
To store the data in as small a space as possible.
Essbase has been readily seen as financial tool. 1
By Pre-saving summarized information, Essbase allows analysis to happen from the top-down with no decrease in performance.
Slicing and Dicing of the data etc.
The concept of Dimensions form the base of understanding Essbase.
A dimension technically represents the highest consolidation level in the database outline.
Simply put, those which we put at the top in rows and columns to get data.
Standard dimensions represent the core components of a business and often relate to departmental functions like Account , Time, Product, Market etc.
As defined in the essbase dbag, A sparse dimension is a dimension with a low percentage of available data positions filled.
A dense dimension is a dimension with a high probability that one or more cells is occupied in every combination of dimensions.
Just as an example: All products need not be sold in all the regions, so that makes it sparse.
But account is existant in every region and for every product, so that makes it dense.
The data block represents all the dense dimension members for its combination of sparse dimension members.
So, A block is created for a unique combination of sparse dimensions.
Number of cells in each block is given by the number of dense dimensions combination.
And the block size is given by number of cells * 8 bytes
Example: A database has five dimensions, Account, Time, Scenario, Product, Market where Account(5), Time(17), Scenario(3) are Dense and Product(5) and Market(5) are Sparse. The numbers in brackets represent the number of members in those dimensions.
Number of data blocks = 5*5 = 25
Number of cells in each block = 5*17*3 = 255
Block size = 255*8 = 2040 bytes
