Essbase Fundamentals

Essbase: Essbase Stands for Extended Spread Sheet Data Base. Generally we use Star Schema Model to design Essbase.

Star Schema Model:

1.       Is a relational database schema for representing multi dimensional data.

2.       It contains one or more dimensions and fact tables.

3.       One fact table is connected to multiple dimensions.

4.       Has few joins contains only one dimension table for each dimension.

Dimension: A dimension represents the highest consolidation level in the database outline. The database outline presents dimensions and members in a tree structure to indicate a hierarchy relationship.

In Essbase there are two types of Dimensions. They are:

1.       Standard Dimension

2.       Attribute Dimension

Standard Dimension:  Standard dimension is a dimension which defines the core components of Business Plan.

Eg: Time, Accounts,Product,Customer, Region etc.,

Standard dimension is further classified in to two types:

1.       Dense Dimension: is a dimension which has high probability of data existence for a particular combination.

2.       Sparse Dimension: is a dimension which has low probability of data existence for a given combination.

Attribute Dimension: Attribute Dimension is a dimension which defines the characteristics of a standard dimension

Eg: Consider a product dimension consisting of iphone’s as its members. Iphones can be categorized in different ways using its characteristics/Specifications.

White & Black colored iphone’s with 16 GB Capacity

White & Black colored iphone’s with 32 GB Capacity

In the above example White,Black colors and 16 & 32 GB Capacity are the characteristics of the iphone which is member of Standard dimension Product

 There are 4 types of Attributes;

1.       Text

2.       Numeric

3.       Boolean

4.       Date

****Using Attribute Dimension we can also analyse the data.

The properties of dimensions define the roles of the dimensions in the design of the multidimensional structure.

Dimension type:

 

When you tag a dimension as a specific type, the dimension can access built-in functionality designed for that type.

For example, if you define a dimension as accounts, you can specify accounting measures for members in that dimension. Essbase calculates the two primary dimension types, time and accounts, before other dimensions in the database. By default, all dimensions are tagged as none.

Dimension Type:

§  None :Specifies no particular dimension type. Default.

§  Time:

Defines the time periods for which you report and update data. You can tag only one dimension as time. The time dimension enables several accounts dimension functions, such as first and last time balances.

§  Accounts,

Contains items that you want to measure, such as profit and inventory, and makes Essbase built-in accounting functionality available. Only one dimension can be defined as accounts.

§  Attribute

Contains members that can be used to classify members of another, associated dimension. See attribute dimension

§  Country Dimension

Contains data about where business activities take place. In a country dimension, you can specify the currency used in each member. For example, Canada has three markets—Vancouver, Toronto, and Montreal, which use the same currency, Canadian dollars.

§  Currency Partition

Separates local currency members from the base currency defined in the application. This dimension type is used only in the main database and is only for currency conversion applications. The base currency for analysis may be U.S. dollars, and the local currency members may contain values that are based on the currency type of their region.

Example

Database:Design

   Year (Type: time)

   Measures (Type: accounts)

   Product

   Market

   Scenario

   Pkg Type (Type: attribute)

   Ounces (Type: attribute)

Data Storage:

·         Essbase stores data in the form of Blocks.

·         A Block is created for each and every unique combination of sparse dimension members.

Block Size: Block size can be calculated using below formula

No. of all dense dimension members X 8 Bytes

No of Blocks: Product of no of all sparse dimension members gives us no of blocks that are created.

Eg: consider Product and Region dimensions with p1,p2,p3 and R1,R2,R3,R4 as their children respectively.

No of blocks is given by 3(no of members in product dim) X4(no of members in Region)=12 Blocks.

**It is recommended that block size must be between 8kb – 100kb for better performance of the cube.

**If we add member to Dense dimension the Block size would increase.

**If we add member to a sparse dimension the no. of blocks would increase.

<script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js?client=ca-pub-8068938535689239"

     crossorigin="anonymous"></script>