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>
