Transcript Member. - dbmanagement.info

ESSBASE
What is Essbase?

It is a multidimensional database
that enables Business Users to analyze
Business
data
in
multiple
views/prospective and at different
consolidation levels. It stores the data in a
multi dimensional array.
Minute->Day->Week->Month->Qtr->Year
Product Line->Product Family->Product Cat->Product sub Cat
Essbase->Reporting SOL(Hyperion/3rd Party)
Essbase Characteristic
It has the following characteristics.
• Works with multidimensional data and rollup hierarchies in
dimensions.
• Gets its information from other systems.
• Deal with some level of summarised data not transaction
• Can be adapted to many different reporting and analysis
environment.
• Here we are having 200 Calc functions for complex
calculations.
•Without using ETL also we can do the data load using Rules
file (DIM, Essbase Integration Services).
Essbase Components
• Essbase OLAP Server
•
A MDDB for storing data for unlimited number of
dimensions ie. Time, Accounts, Product etc. It manages
analytical data models, data storage, calculation and
data security.
• [Extended spreadsheet Database] Spreadsheet Add-in
(Client)
•
This enables analysis of the data stored in the
Essbase server.
• Essbase Application Tools
•
It is used for extending Essbase applications. It
includes currency conversion, SQL Interface,
Spreadsheet Toolkit and APIs.
• Essbase Partitioning
•
This makes it easy to design and administer
databases that spans Essbase
•
application or servers. You can cope a slice of large
Essbase Architecture
1.Client tier
2.Middle Tier (App tier)
3.Database tier
Architecture
Essbase Terminology
Application:- It is the combination of
databases and the related
files which
to cater a specific requirement.
Database:-It is a MDDB which stores the
data in terms of cubes.
Outline/Cube:- It is the structure of the
database ,where we can add the unlimited
number
of
dimensions, members
,Consolidation operators, formulas, aliases,
storage properties etc…….
Dimension:-It is the view/prospective of the
business data where the business users can
Terminology (contd.):
5. Member.
Subset of the dimension / values of the
dimension.
6. Cell Reference.
Contains one and only one member from
each and every dimension in the outline.
7. Roots and Leaf::
The root is the top member in a branch
Leaf members have no children. They
are also referred to as level 0 members
8. Parent, Children, Siblings.
● A parent is a member that has a branch below .
● A child is a member that has a parent above it.
● Siblings are child members of the same immediate parent
Root
Parent
Children
Leaf
Siblings
8. Generation.
Generation starts from G(1) at
Dimension. Each children of a G(i) member
will be G(i+1). Any member can have only
one Generation.
9. Level.
Level starts from L(0) at a
member without any children. The parent of
a L(i) member will be L(i+1). Any member
can have more than one Levels.
10. Ancestors, Descendents,
Descendants are members in branches below a parent.
e.g Profit, Inventory, and Ratios are descendants of Measures. The children of
Profit, Inventory, and Ratios are also descendants of Measures.
Ancestors are members in branches above a member.
e.g Margin, Profit, and Measures are ancestors of Sales
Outline
Introduction To outline
 An Outline is the tree
structure for a dimension
hierarchies.
 Database outlines define
the structure of a
multidimensional database,
including all the
dimensions, members,
aliases, properties, types,
consolidations, and
mathematical relationships.
 The structure defined in
the outline determines how
data is stored in the
database.
 When a database is created,
Dimension N Member
In above figure we are having 5 members for Year(i.e
Dimension).
Those are 1.Jan 2.Feb 3.Mar 4.Qtr1 5.Year
Parent :- A parent is a member that has a
branch below it. For example, Margin is a
parent member for Sales and Cost of
Goods Sold.
Child:- A child is a member that has a
parent above it. For example , Sales and
Cost of Goods Sold are children of the
parent Margin.
Siblings:-Siblings are child members of the
same immediate parent, at the same
generation. For example, Sales and Cost of
Goods Sold are siblings (they both have
Descendants:- Descendants are members
in branches below a parent. For
example, Profit, Inventory, and Ratios
are descendants of Measures. The
children of Profit, Inventory, and Ratios
are also descendants of Measures.
Ancestors:-Ancestors are members in
branches above a member. For example,
Margin, Profit, and Measures are
ancestors of Sales.
Leaf Node:- Leaf members have no
children. They are also referred to as
level 0 members. For example, Opening
Inventory, Additions, and Ending
Inventory are leaf members.
Generation:- Generation refers to a
consolidation level within a dimension. A root
branch of the tree is generation 1.
Generation numbers increase as you count
from the root toward the leaf member.
Level:- Level also refers to a branch within a
dimension; levels reverse the numerical
ordering used for generations. Levels count
up from the leaf member toward the root.
The root level number varies depending on
the depth of the branch.
Member Properties
Member Properties
You can specify a broad variety of settings for each member that define the
member’s storage characteristics and other rollup and reporting behaviors.
You can define the following important properties for members:
* Aliases
* Consolidation operators
* Data storage
* User-defined attributes (UDAs)
* Attribute dimensions
Dense and Sparse
Data Blocks Created for Sparse Members
An Ideal Configuration with
Combination of Dense and
Sparse Dimensions
Block Size = 20
D, 5
Block Count = 12
D, 4
240
S, 2
S, 6
240
Block for P1->N1
Block for P1->N2
We have 12 such blocks of size 20 each.
Subsequent blocks will be for:
(P1, N1) (P1, N2) (P1, S1) (P1, S2) (P1, N) (P1, S)
(P2, N1) (P2, N2) (P2, S1) (P2, S2) (P2, N) (P2, S)
Assigning Dimension Types
Time
Dimension:
•
There can only be at most 1 Time
Dimension in a Cube.
•
Features are “Dynamic Time Series”
like:
Q-T-D, Y-T-D etc.
•
For present month FEB, Q-T-D will
give us JAN+FEB.
•
The Names Q-T-D (Quarter To Date)
etc has no significance. Whatever
functionality, we attach to it, it will
function accordingly.
Expense Reporting
Actual
Budget
VAR
Sales
100
90
10
Payroll
100
90
10
Actual
Budget
VAR
Sales
100
90
10
Payroll
100
90
-10
Expense Reporting -$
Time Balance
Jan
Feb
Mar
QTR1
10
10
10
30
35
10
10
35
Jan
Feb
Mar
QTR1
Sales
10
10
10
30
Inventory
10
10
15
15
Sales
Inventory
TB Last / TB
First / TB Avg /
TB None
TB First
TB Last
Skippin
g
Jan
Feb
Mar
QTR1
Sales
10
10
10
30
Inventory
10
10
Jan
Feb
Mar
QTR1
Sales
10
10
10
30
Inventory
10
10
#Missing
10
Skip Missing or 0 /
Skip Missing / Skip 0 /
Skip None
#Missing #Missing
Expense Reporting
Time Balance
Skipping
Currency Conversion Properties
• Currency conversion properties define categories of
currency exchange rates
• These properties are used only in currency databases on
members of accounts dimensions
DTS
Calculation:
QTD = G3
•Calculate From present month.
•Calculate in upwards direction.
•Add only L0.
•Calculate till you reach G3.
 When siblings have different operators, Analytic
Services calculates the data in top-down order.
 Parent1
 Member1 (+) 10
 Member2 (+) 20
 Member3 (-) 25
 Member4 (*) 40
 Member5 (%) 50
 Member6 (/) 60
 Member7 (~) 70
• (((Member1 + Member2) + (-1)Member3) *
Member4) = X
• (((10 + 20) + (-25)) * 40) = 200
• If the result of this calculation is X, Member5
consolidates as follows:
• (X/Member5) * 100 = Y
• (200/50) * 100 = 400
• If the result of the Member1 through Member4
calculation is Y, Member6 consolidates as follows:
• Y/Member6 = Z
• 400/60 = 66.67
Types of Dimensions
Analytic
Services has two types of
dimensions.
1.standard dimensions
2. attribute dimensions.
Most data sets of multidimensional databases
have two characteristics:
● Data is not smoothly and uniformly
distributed.
● Data does not exist for the majority of
member combinations. For example, all
products may not be sold in all areas of the
 Sparse:- A sparse dimension is a
dimension with a low percentage of
available data positions filled.
Ex:-Product , Market etc….
Dense:- A dense dimension is a dimension
with a high probability that one or more
cells is occupied in every combination of
 dimensions.
Ex:-Time ,Accounts etc…..
Storage Mechanism
Essbase stores the data in terms of
blocks and cells.
Block:-Analytic Services creates data
blocks for combinations of members in
the
sparse
standard
dimensions
(providing at least one data value exists
for the member combination).
Cell:-It is a part in block. It is the
combination of all dense dimension
stored member combination ,where the
value exactly resides.
Potential No. of data blocks:-That is max
no. of data blocks we can have
multiplication of no. of members from
each sparse dimension).
No.of cells:-Multiplication of no. of
stored members of each dense dimension.
Block size:-no.of cells*8 bytes
Cube size:-No.of blocks*block size
Block Density:-(no.of data existed
cells/Total no.of cells)*100%.
Determining the Number
of Data Blocks in a Database
Entity
(Sparse)
Entity
Corp
Scenario (Sparse)
Year (Sparse)
Version (Sparse)
Scenario
Budget
Year
2007
Version
1st Draft
Final
Account (Dense) Period (Dense)
Account
Account1
Account2
Account3
Period
Jan to Dec
Each block contains 36 cells
= (3 Accounts * 12 Time Periods)
Attributes
•Attribute:-Attribute dimensions are a special type of dimension
and are associated with standard sparse dimensions. Essbase does
not store the data for attribute dimensions, Essbase dynamically
calculates the data when a user retrieves it.
•These should be placed below the std dimensions.
Extension of Index file is .ind
It is just like ess0000n.ind
This n is 1,2,3…….etc
Page File:-It holds the compressed blocks.
Extension of Page file is .pag
It is just like ess0000n.pag
This n is 1,2,3…….etc
Member Storage Properties
You can specify data storage properties for
members;data storage properties
define
where and when consolidations are stored.
In Essbase we are having 6 Storage
properties.
1. Store
2. Dynamic Calc
3. Dynamic Calc and Store
4. Shared member
Store:-Store the data value with the
member.This is the Default setting.
Dynamic Calc:- Not calculate the data
value until a user requests it, and then
discard the data value.
Dynamic Calc and Store:- Not calculate
the data value until a user requests it, and
then store the data value.
 Shared member:- The data associated with
the member comes from
another
member with the same name.
Label only:- Although a label only member
has no data associated with it, it can still
display a value.
The label only tag groups members and eases
navigation and reporting. Typically, label
only members are not calculated.
Dimension Types
A dimension type is a property that
Analytic Services provides that adds
special functionality to a dimension.
Those are:1.Time
2.Accounts
3.Currency
4.Country
5.Attribute
6.None
Alias
Consolidation
Label Only
Attribute
Time:-Defines the time periods for which
you report and update data. You can tag only
one dimension as time. The time dimension
enables DTS(Dynamic Time Series),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 Analytic Services built-in accounting
functionality available. Only one dimension
Time Balance Properties
By default, a parent in the
time
dimension is calculated based on the
consolidation and formulas of
its
children.
Some times it should not be like this.
So,we are telling the Essbase to calculate
the parent member of Time tagged
Dimension in a different manner using
TB Properties.
So, we are tagging the TB Properties to
the Account tagged dimension.
TB Properties are:-
TB None:-No special property is assigned.
TB First:Set the TB First when you want the parent
value to represent the value of the first
member in the branch.
TB Last:Set the TB Last when you want the parent
value to represent the value of the last
member in the branch.
Time Balance
Jan
Feb
Mar
QTR1
10
10
10
30
35
10
10
35
Jan
Feb
Mar
QTR1
Sales
10
10
10
30
Inventory
10
10
15
15
Sales
Inventory
TB Last / TB
First / TB Avg /
TB None
TB First
TB Last
Skip Properties
If you set the time balance as first, last, or
average, set the skip property to tell Analytic
Services what to do when it encounters
missing values or values of 0.
Skip None:-Does not skip data when
calculating the parent value.
Skip Missing:-Skips #MISSING data when
calculating the parent value.
Skip Zeros:-Skips data that equals zero when
calculating the parent value.
Skippin
g
Jan
Feb
Mar
QTR1
Sales
10
10
10
30
Inventory
10
10
Jan
Feb
Sales
10
10
10
30
Inventory
10
10
#Missing
10(TB
First)
Skip Missing or 0 /
Skip Missing / Skip 0 /
Skip None
#Missing #Missing
(TB
Last)
Mar
QTR1
Variance Reporting
Variance reporting properties determine
how Analytic Services calculates the
difference between actual and budget data
in a member
with
the @VAR or
@VARPER
function in its member
formula.
For Non-Expense members:
Variance=Actual-Budget;
For Expense members:
Variance=Budget-Actual;
Essbase does n’t know which member is
Non-Expense Reporting
Actual
Budget
VAR
Sales
100
90
10
Payroll
100
90
10
Actual
Budget
VAR
Sales
100
90
10
Payroll
100
90
-10
Expense Reporting -$
Two-Pass calculation
Two-pass, this default label indicates that
some member formulas need to be
calculated twice to produce the desired
value.
The two-pass property works only on
members of the dimension tagged as
accounts
and on
members tagged as
Dynamic Calc and Dynamic Calc and
Store.
2 Pass Calculation:
Jan
Feb Mar Qtr1
Sales 1000 1000 1000 3000
Profit 100
100
100
300
Profit
10
10
10
30
Mark Profit%
as 2Pass. After the full calculation is over,
%
it comes back and again makes it 10.
Jan
Sales 1000
Profit 100
Profit
10
%
Feb
1000
100
10
Mar
1000
100
10
Qtr1
3000
300
10
Currency:-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 US dollars, and the local
currency members may contain values that
are based on the currency type of their
region.
Attributes
Attribute:-Contains members that can be
used to classify members of another,
associated dimension.
These define the characteristics of the std
dimensions.
These are for the additional analysis.
By default Dynamic Calc(No Storage).
Steps to create Attributes:1.Create the Attribute dimension.
Rules to create Attribues
1.One attribute dimension can be
associated to only one std Sparse
Dimension.
2.One Std Sparse Dimension can have
multiple attribute dimensions.
3.Only level 0 members of attribute
dimension can be associated to the
members of std sparse members.
4. Std Sparse Dimension member can
have multiple attribute members from
DTS(Dynamic Time Series)
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.
Analytic Services provides eight predefined
Dynamic Time Series members:
● H-T-D (History-to-date)
● Y-T-D (Year-to-date)
● S-T-D (Season-to-date)
● P-T-D (Period-to-date)
● Q-T-D (Quarter-to-date)
These eight members provide up to eight
levels of period-to-date reporting. How
many members you use and which members
you use depends on the data and the
database outline.
For example, if the database contains hourly,
daily, weekly, monthly, quarterly, and yearly
data, you can report day-to date (D-T-D),
week-to-date(W-T-D), month-to-date(M-T-
DTS(Dynamic Time Series)

 For present month
FEB,
 Q-T-D(FEB)
will
give us JAN+FEB.
 The Names Q-T-D
(Quarter To Date)
etc
has
no
significance.
Whatever
functionality,
we
User Defined Attributes (UDA)
●Works similar to the Attribute
dimensions.
 ●Can be used across the dimensions and
across the levels.
●Can be used on dense or sparse.
●Used to group members, these will be
helpful in calc scripts.
● You cannot create a UDA on shared
members.
Aliases
Aliases are names that can be used in
place of the main member name. Aliases
are commonly used for storing descriptions
and for providing
alternative naming
conventions.
The Member name and Alias name should
be unique throughout the Outline.
There can be multiple Alias to a member.
To implement this, we need
to have
LRO(Linked Reporting Object)
An LRO is an artifact associated with a
specific data cell in an Analytic Services
database.
LROs provide
on a cell.
additional
information
Users create linked objects through
Spreadsheet Add-in by selecting a data
Types Of LRO’S
1.Cell note:- A text annotation
2.File:- An external file, such as a
Microsoft Word document, an Excel
spreadsheet, a scanned image, an audio
clip, or an HTML file (for example,
mypage.htm).
3.URL:- An acronym for Uniform
Resource Locator. A string
that
Life Cycle Of Essbase
1.Creating the Database
2.Dimensional Building
3.Data Loading
4.Performing the Calculations
5.Generating the Reports
Dimensional Building
Two Methods to build Dimensions
1.Manually
2.Dynamically(Using Rules file)
 a. Generation Reference Method
 b. Level Reference Method
 c. Parent-Child Method
 d. Add as child of the specified parent
 e. Add as sibling at the lowest level
 f. Add as sibling to a member with a
matching string
Generation
Reference:-For Top-down
data.
 Each
record specifies the parent’s
name, the child’s name, the children of
that child , and so forth.
Level Reference Method:-For Bottomup data.
Each record specifies the name of the
member , the name of its parent, the
name of its parent’s parent, and so
forth.
Loading Outlines
Half
H1
H1
H1
H1
H1
H1
H2
H2
H2
H2
H2
H2
Quarter
Qtr1
Qtr1
Qtr1
Qtr2
Qtr2
Qtr2
Qtr3
Qtr3
Qtr3
Qtr4
Qtr4
Qtr4
Month
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Time.txt
H1,Qtr1,Jan
H1,Qtr1,Feb
H1,Qtr1,Mar
H1,Qtr2,Apr
H1,Qtr2,May
H1,Qtr2,Jun
H2,Qtr3,Jul
H2,Qtr3,Aug
Steps for Dimensional Building
 1.Create a rules file.
 2.Open the data source file
 3.Set the file delimiters for the data
source
 4.Define the fields
 5.Set the build method
 6.Validate the Rules file
 7.Save the Rules file and close the
Rules file
Loading Outlines (contd.):
Loading Outlines (contd.):
Loading Outlines (contd.):
Allow Property Change:
Loading Outlines (contd.):
Loading Outlines (contd.):
from the DLR:
options in Excel):
Profit
Profit
Profit
Profit
Profit
Profit
Assets
Assets
Ratios
Ratios
+
+
+
+
+
+
~X
~X
~X
~X
Income
Income
Expenditure
Expenditure
Expenditure
Tax
Inventory
Inventory
Margin %
Closing %
+
+
~
~
~
~
Sales
COGS
Marketing
Payroll
Misc
+
+
+
+
+
Opening Inventory
Colsing Inventory ~
Parent/Child Reference:
Measures
Profit
Income
Income
Profit
Expenditure
Expenditure
Expenditure
Profit
Measures
Assets
Inventory
Inventory
Measures
Ratios
Ratios
Profit
Income
Sales
COGS
Expenditure
Marketing
Payroll
Misc
Tax
Assets
Inventory
Opening Inventory
Colsing Inventory
Ratios
Margin %
Closing %
+
+
+
+
+
+
~X
~
+
~
~X
~
~
(contd.):
Data Loading
Types of data loading are 3.



1.Free form data loading
2.Using Rules file
3.Excel lock &send
Free form data Loading:If the source file format is 100% matching
with the outline format ,then only we will
go for Free form data loading(No use of
Rules file).
Excel lock &send:Through Excel we will enter the data
and submit the data.
Using Rules file:1.Create a rules file.
2.Open the data source file
3.Set the file delimiters for the data
source
4.Define the fields
5.Check the data field for the last
field(if it is having single data field)
6.Validate the Rules file
7.Save the Rules file and close the
Loading Data (contd.):
Loading Data (contd.):
formats):
Order of Computation:
Within Accounts:
1. Accounts
2. Time
3. Dense
4. Sparse
5. Two – Pass
9
5
3
1
2
4
8
6
7
Member Formula Vs Calc
Script:
Member
Formula
CALC Script
Mention only
RHS
For a Member
Mention LHS
and RHS
For the Whole
Database
Stored
Externally
Stored in the
Outline
Intelligent Calculation:
•After any change in data, all the relevant blocks are marked
Dirty.
•On running the Default Calc, with Intelligent Calc ON, it
optimizes calculation by only calculating the Dirty blocks.
•It can be turned ON or OFF by the command:
SET UPDATECALC ON/OFF;
Backups
There are two methods of backing up a
database:
● File system backup
● Data export in a text format
Backing up Files During Run-time
If any Essbase databases must be
running at the time of the backup,
follow these steps:

1. Placing a Database in ReadOnly Mode

2. Performing a File Backup

3. Returning Database to ReadWrite Mode
Placing a Database in Read-Only Mode:Placing the database in read-only (or
“archive”) mode protects the database
from updates
during
the backup
process. After you perform the backup
using the third-party backup utility of
your choice, return the database to
read-write mode.
To place a database
mode, use a tool:
in
read-only
Data export
The amount of data to export:
1.All data
2. Level 0 blocks only (blocks containing
only level 0 sparse member combinations.
Note that these blocks may contain data for
upper level dense dimension members.)
3. Data from input blocks only (blocks
containing data from a previous data load
or spreadsheet Lock & Send)
We can export data in a columnar or
non-columnar format.
In each row, the columnar format
displays a member name from every
dimension. Names can be repeated from
row to row.
The columnar format provides a
structure to the exported data, so that it
can be used for further data processing
by applications other than Analytic
Services tools; for example,relational
Automation(ESSCMD): For Scheduling the loading process. Following steps
need to be followed.

1. Open notepad and write the following code as
shown below, and save it as .scr extension file. (Ex:
Sample5.scr)

 2. Open notepad and write a batch file as shown below
and save it as .bat extension file. (Ex: Sample5.bat)

 3. Go to control panel and click on scheduled tasks and
Add scheduled task as shown below Click on next
following window appears
 4. When u click next the following window appears then
click browse

 5. select the .bat (Ex: Sample5.bat) as shown below and
select when the task need to be performed

 6. Select the time an the no of days in the following
window

 7. Enter the Username and password and click next the
following window appears
 8. After entering the system user name and password
click next following window appears then click finish.
Windows will perform the task for the scheduled day and
time.
Report Scripts
•Select the application and the database, and click the Report
Scripts
• Click the New button to open the Report Editor
// This is a simple report script example
// Define the dimensions to list on the current page, as below
<PAGE (Market, Measures)
// Define the dimensions to list across the page, as below
<COLUMN (Year, Scenario)
// Define the dimensions to list down the page, as below
<ROW (Product) // Select the members to include in the report Sales
<ICHILDREN Market Qtr1 Qtr2 Actual Budget Variance
<ICHILDREN Product
// Finish with a bang
• Choose File > Save, and type Myrept1 for the report
script object name, and save it on the server (the
default).
• Choose Report > Run.
Essbase Partitioning
Partition is the piece of a database that is shared with another
database.
Partitions contain the following parts :
• Data source information
• Data target information
• Login and password
• Type of partition
• Shared areas
• Member mapping information
• State of the partition
Types of Partitions
1.Replicated Partition
2.Transparent Partition
3.Linked partition
Replicated
Partition:- A replicated
partition is a copy of a portion of the
data source that is stored in the data
target.
Some users can then access the data in
the data source while others access it
in the data target.
Transparent
Partition:-A transparent
partition allows users to manipulate data
that is stored remotely as if it were
part of the local database.
 The remote data is retrieved from the
data source each time that users at the
data target request it. Users do not need
to know where the data is stored, because
they see it as part of their local database.
When they update the data, their updates
are written back to the data source. This
process means that other users at both the
data source and the data target have
immediate access to those updates.
Linked Partition:- A linked partition
connects two different databases with a
data cell. When the end user clicks the
linked cell in the data target, you drill
Linked Partition