Hyperion Essbase

What we will cover

 Introduction To Essbase
 Hyperion Essbase Demonstration
 Designing & Explanation of Analytics Service Database
 Loading Data
 Understanding Analytics Services Structure
 Consolidating and Calculating Data
 Spreadsheet Addin

2
 Corporate Information Pyramid

Information
 Mainframes and OLTP Data Exploration /
 RDBMS
 SQL Reporting Applications SQL/
 Text Files
 Spreadsheet Applications Relational/

Cleansing/

Data
Transaction

3
Multidimensionality
 Multidimensionality converts two dimensional row and column oriented data into a
multidimensional cube. The faces of the cube represents dimensions.
 Structures data around natural business concepts.
 Provides foundation for efficient, sophisticated business analysis.

East
West
January February
Actual Budget Actual Budget
TV
Sales
VCR
TV
Margin
VCR

4
Data Cube
 Analysts prefer to view multidimensional data in the form of a data cube rather than in
relational tables.
 Cube is a metaphor for multidimensional data storage.
 N-D cube can be visualized as series of (n-1)-D cubes.

Relational table Data cube

Product Time Sales
January February
Diet Cola January 166
Diet Cola 166 182
Diet Cola February 182
Root Beer 131 149
Root Beer January 131
… … …
Root Beer February 149
… … …

5
3-D Cube
 Dimensions
 Time
s
 Products
k et Y
ar N
 Markets M C
T
A
 Members C
166 182 143
 Jan, Feb, Mar (Time) Diet Cola

Products
 Diet Cola, Root Beer, Cream Soda, Root Beer 131 149 120

Fruit Soda (Products) Cream Soda 123 150 115

 CA, CT, NY (Markets) Fruit Soda 267 196 231
 Measures Jan Feb Mar
 Sales
Time

267 units of fruit soda sold in CA in Jan

6
The OLAP Solution-Hyperion Essbase
 Online Analytical processing System (OLAP) is a category of software that lets
analysis ,managers and executables gain insight into data thru fast ,consistent,
interactive access to a wide variety of possible data views.

 OLAP transforms raw data into transformation reflecting the real dimensionality of the
enterprise as understood by the user.

 OLAP solution as Essbase XTD Analytics Services facilitates intensive analysis and
let managers invent their analysis interactively.

 Provides multiple-user access to the analytic information.

 Provides a graphical user interface.

 Conforms to the client-server architecture.

7
Hyperion Essbase Demonstration

8
Hyperion Essbase Demonstration
 Starting The Essbase Analytics Server
 Starting Administration Server
 Start Administration Server Console
 Defining The database outline
 Defining the automatic rules for developing complex outlines
 Creating Calculation Script
 Creating Load Rules for loading data into database
 Providing log reports for trouble shooting.

9
Hyperion Essbase –Product Architecture
Client Tier Middle Tier Database Tier

•Hyperion Analyzer
TCP/IP
•Hyperion Reports

Spreadsheet HTTP Spreadsheet

Add-in Services

HTTP TCP/IP
Administration Essbase
Services

HTTP
Administration Administration
Console Services

10
Essbase
 Multi-threaded OLAP database software
 Based upon Web-deployable, thin-client architecture
 Components:
 Essbase Server
 Essbase Client
– Essbase Spreadsheet Add-in
– ESSCMD
– MaxL data definition language (MaxL DDL)
– Data manipulation language (MDX)
– Optional. Data Mining

11
Administration Services
 Database and system administrators' interface to Essbase
 Components:
 Administration Server
 Administration Console

12
Starting and Stopping the Essbase Server
 To start the Essbase Server, launch the Essbase Server Agent executable:

 To stop the Essbase Server, enter EXIT or QUIT in the Agent Console.

Essbase Server
Agent

Essbase Server Essbase Server

Agent Console

13
 Starting and Stopping the Administration Server
 To start the Administration Server, launch the Administration Server Agent
executable:

 To stop the Administration Server, launch the following executable:

%EASPATH%\eas\server\bin\stopadminsvr.exe

Administration Server
Agent

Administration Server Administration Server

Agent Console

14
Starting the Administration Console
 To start the Administration Console:
1. Launch the console executable:

%EASPATH%\eas\server\console\admincon.exe

2. Log on to the console.

15
Enterprise View
Enterprise View displays your Essbase environment in a hierarchy.

Shortcut Menu Custom View

16
Life Cycle of Essbase Database Design
Build MDB

Report
and Load Data
Verify

Calculate

17
Creating an Essbase Application

18
Essbase Databases
 Data repository that contains multidimensional analytic data
 Database objects:
 Database outline
 Data sources
 Rules files
 Calculation scripts
 Report scripts
 Security definitions
 Linked reporting objects
 Partition definitions

19
Creating an Essbase Database

2
3

20
Designing An Analytics Services Database

21
Analytics Services Terminology
 Outline : The presentation of the relationships among
the members in the database.

 Dimensions : A perspective on the data being Analyzed

eg Time Units i.e Year ,quarter,months,weeks etc.
There are two types of dimensions as
 Standard
 Attribute

 Members : Discrete component making up dimensions

 Generation/ Levels : Describe position within the

hierarchy of the dimension.

 Sibling: A child member at the same branch level as

another child member with the same parent.

 Shared Member : The data values associated with a

shared member come from another member with the
same name. The shared member stores a pointer to
data contained in the other member and the data is only
stored once.

 User Defined Attribute (UDA): Used to describe a

member.

22
Standard Dimensions
Essbase maximizes the performance by dividing the standard dimensions into two types :

 Dense Dimension : A dimension with a high probability that one or more data points are
occupied in every combination of dimension. Hence Accounts , Year & Scenario are
considered Dense Dimensions.
 Sparse Dimension : A dimension with a low percentage of data position filled.

23
Essbase File Structure
Essbase Server creates a directory for each application and database.
%HYPERION_HOME%

%ARBORPATH% Outline
Directory for all Transaction control
applications Free fragment file
Database file
Database backup
Kernel
Data source file

Rules files
TBC application
directory
Index file
Sales database directory Data file
Calculation script

24
Database Outline
 Tree structure for dimension hierarchies
 Consolidations and mathematical relationships between members
 Outline Editor

25
Outline Terms: Generations
Generation defines a member’s location within the outline hierarchy from
the top of the dimension.

Gen 1 Scenario Dimension

Gen 2 Actual

Gen 2 Budget Leaves

Gen 2 Forecast
Gen 2 Variances Branch

Gen 3 Act vs Bud

Gen 3 Act vs Fcst Leaves

Gen 3 Bud vs Fcst

26
Outline Terms: Levels
Level defines a member’s location within the outline hierarchy from
the bottom of the dimension.

Scenario Level 1 Level 2

Actual Level 0

Budget Level 0

Forecast Level 0

Variances Level 1

Act vs Bud Level 0

Act vs Fcst Level 0

Bud vs Fcst Level 0

27
Outline Terms: Genealogy
Genealogy names relationships between members in the outline.

Scenario

Actual Ancestors of
• Act vs Bud
Budget • Act vs Fcst
• Bud vs Fcst
Forecast
Descendants Variances Parent
of Scenario
Act vs Bud Child

Act vs Fcst Child

Bud vs Fcst Child

28
Time Dimension
Time dimension defines the time period of the analytic data.

Separate Dimension Model Fiscal Year Crossover Model

Total Year Year

Qtr1 FY03

Qtr2 FY04

Qtr3
Qtr1FY04
Qtr4
Qtr2FY04

Year Qtr3FY04

Qtr4FY04
2003

2004

29
Outline Editor
Outline Editor enables you to design the database outline.

Toolbar

Tabs

Working pane

Control
buttons

30
Adding Dimensions and Members
You can add dimensions and members to the outline manually or
dynamically.

Adding a child Adding a sibling

31
Setting the Dimension Type
Dimension Type Buttons

Assigned
Dimension
Type

32
Verifying and Saving the Database Outline
Essbase enables you to:
 Check the database outline for errors
 Save the outline to the Essbase server or the client file system

Outline errors

33
Enhancing Computational Capabilities
The database outline contains the following metadata that enhances
Essbase computational capabilities:
 Consolidation operators
 Member formulas
 Variance reporting calculation
 Time balance

34
Consolidation Property and Operators
 Consolidation property defines how the member rolls up to its parent.
 Valid consolidation operators are:
 Addition (+)
 Subtraction (-)
 Multiplication (*)
 Division (/)
 Percent (%)
 Ignore (~)

Excluded from
consolidation
Margin

Sales (+)
Margin = Sales – COGS
COGS (-)

35
Defining Member Calculation
 Member formulas define complex computations in the outline.

"Ending Inventory" = "Opening Inventory" - Sales + Additions;

Markup = (Retail - Cost) % Retail;

 Essbase operators and functions return sets of member or data values.

36
Creating Member Formulas
Create the member formula in
Formula Editor.

Formula Editing pane

37
Variance Reporting Calculation
 Members that represent expense must have the Expense Reporting tag.
 Essbase provides two variance reporting functions:
 @VAR(mbrName1, mbrName2)
 @VARPER(mbrName1, mbrName2)
 Example:
Actual Budget Variance Variance %
Sales 100 120 -20 -16.67
COGS (Expense Reporting) 100 120 20 16.67

 Variance equals Actual minus Budget.

Variance = @VAR(Actual, Budget);

38
Time Balance Property
 Time balance property defines member consolidation across the Time dimension.
 Skip option determines how Essbase treats zero and missing values.

Total Year
Qtr1

Jan
20 Feb
31 Mar
25 20 20
Inventory
11 10 15 36 Qtr1+Qtr2+
Opening Inventory (TB First) Qtr3+Qtr4
Additions 31 25 30 30 Value of Qtr4

Ending Inventory (TB Last)

39
Label Only Members
 Do not store data
 Group members to ease navigation and reporting
 Take the value of the first child that stores data

Total Year

Qtr1 Qtr2 Qtr3 Qtr4

Scenario Label Only 120 125 150 150 545

Actual 120 125 150 150 545

Budget 110 140 140 170 560

40
Member Aliases
 Aliases improve outline and report readability
 Essbase stores aliases in the alias tables
 You can create up to 10 alias tables

Default

Year Year Total Year Total

Quarter 1
Qtr1 Quarter 1
January
Jan
+ February
January

Feb February
March
Mar March
…

41
Attribute Dimensions
 Attributes describe characteristics
of data such as the size and color
of products.

 Through attributes you can group

and analyze members of
dimensions based on their
characteristics.

42
Attribute Dimensions
Analysis by attribute provides depth and perspective, supporting more informed
decisions .
Base dimension Attribute dimensions

Products Caffeinated Intro Date Promotion

Colas True 01-25-2003 Summer Time Fun

Cola False 05-10-2002 Old Time Favorites

Caffeine Free 09-30-2001 New Millennium

Kool Cola 01-01-2001

43
Associating Attribute Dimensions

3
2
1

Associated Attribute Dimensions 5

44
Setting member storage properties

Storage
When to Use
Property
Store Store the data value with the member.
Dynamic Not calculate the data value until a user requests it, and then store the
Calc and data value.
Store
Dynamic Not calculate the data value until a user requests it, and then discard
Calc the data value.
Never share Not allow members to be shared implicitly.
Members tagged as Never share can only be explicitly shared. To
explicitly share a member, create the shared member with the same
name and tag it as shared.
Label only Create members for navigation only, that is, members that contain no
data values.
Shared Share values between members. For example, in the Sample Basic
member database, the 100-20 member is stored under the 100 parent and shared
under Diet parent.

45
UDA (User Defined Attributes)
 A user-defined attribute (UDA) is a word or phrase about a member.

 Use UDAs in the following places:

 Calculation scripts. After you define a UDA, you can query a member for its UDA in
a calculation script.
 Data loading. You can change the sign of the data as it is loaded into the database
based on its UDA.

46
Loading Data

47
 Loading Data Overview

Error File
Formatted
Data File

Administration
+ Console Essbase
Server
Nonformatted
Data File Rules File

MaxL Shell Database

Data Prep Editor

48
 Free-Form Data Loading
You can load a data source directly if it contains all information required to load its data
values into the database.

Measures Scenario Products Markets

Budget "200-30" Dallas

Jan Feb Mar Apr May Jun Total Year
Sales 150 160 150 140 130 110
COGS 60 60 60 50 50 40 Data
Marketing 20 20 20 20 10 10
Payroll 20 20 20 20 20 20

Scenario Markets Products Measures Total Year Data

Budget Chicago "Kool Cola" Sales January 1669.8

Budget Chicago "Kool Cola" COGS February 484
Budget Chicago "Diet Cola" Marketing March 629.2
Budget Chicago "Diet Cola" Payroll April 629.2

49
Performing Free-Form Data Loads

2
1

50
Structured Data Loading with Rules
 You can format the data source to map its data to the database.
 You can store the rules in a rules file.
 You can apply the rules to many data sources.

Map fields to Markets, Products, and Map the header to Budget

Measures dimensions. in the Scenario dimension.

1st quarter Budget Data

Jan Feb Mar Qtr1
Aspen Diet Cola Sales 150 160 150 460
Denver Kool Cola COGS 60 60 60 180
Manchester C-Free Cola Marketing 20 20 20 60

Replace C-Free Cola with Ignore field

Caffeine-Free Cola. during data load.

51
Creating Data Load Rules Files

1
2

52
Opening Data Sources

53
Loading Data with the Rules File

2
1

54
Understanding Analytics Services Structure

55
Essbase Data Storage Options

Aggregate Storage Block Storage

Large number of base dimensions 

Extremely small batch window, or many 

applications to compute
Combinations of large dimensions 

Calculation script-based applications 

Write-back capability 

Interactive planning, allocations, 

sophisticated analytics

56
Block Storage Overview
Block storage databases use dense and sparse dimensions.

Dense Dimensions Sparse Dimensions

a rio ets
c en ar
k
S M

Measures Products

Total Year Customers

57
Setting Dimensions as Dense or Sparse

2
Data-descriptor dimensions

Business-view dimensions 3

Attribute dimensions

58
Aggregate Storage Overview
 A new storage kernel
 Aggregate-intensive cubes
 Larger numbers of dimensions and
members
 Optimized for sparser data sets,
operational analytics
 Significantly reduces calculation
times and disk footprint
 Designed to work seamlessly with
existing interfaces and user skills
 Reduces complexity when creating
Essbase databases

59
Consolidating & Calculating Data

60
Database Calculation
 Data consolidation and calculation is performed upon retrieval.
 Analytic Services offers two ways that you can calculate a database:
 Outline calculation

 Calculation script calculation

 Retrieval performance can be improved by aggregating and storing selected level 0

data.

Level 0 Data Report

Aggregated Data

61
Outline Calculations
 By default, the calculation for a database is a CALC ALL of the database outline.
CALC ALL consolidates all dimensions and members and calculates all formulas in the
outline.
 Used for less complex calculations
 Follow outline relationships
 Fastest way to calculate entire database

Calculated
Database
Input Data

Outline

62
Order of Outline Calculations
Essbase calculates the outline in the following order:
1. Accounts dimension
2. Time dimension
3. Other dense dimensions in top-down order
4. Other sparse dimensions in top-down order
5. Members that are tagged as Two-Pass calculations

1st Pass 2nd Pass

Calculated Data Calculated Data

Level 0 Data
(Before Two-Pass) (After Two-Pass)

63
Calculation Scripts
Calculation scripts enable you to:
 A calculation script contains a series of calculation commands, equations, and formulas.
 You use a calculation script to define calculations other than the calculations that are
defined by the database outline.
 Calculate all or a portion of the database
 Control the order in which dimensions are calculated
 Perform complex calculations

Calculated
Level 0 Data Database

Calculation Script

64
Creating Calculation Scripts

Dimensions and
Members

Opening the Calculation

Script Editor

Functions and
Commands

65
Outline Enhancements That Affect Calculations
 Two-Pass Calculations
 Dynamic Calculations
 Dynamic Time Series

66
Two-Pass Calculations
 Before Two-Pass

 After Two-Pass    

   

67
Dynamic Calculations
 Advantages
 Shorten batch database calculation time
 Reduce disk storage usage
 Reduce database restructure time
 Reduce time to perform a backup

 Disadvantages
 May increase retrieval time
 Change calculation order

Precalculated Data Calculated Report

Database Request Request

68
Dynamic Time Series
January February March
Sales 100 120 130

QTD Sales 100 220 350

Jan Calc Script Stored QTD

Feb

Dynamic Non-stored QTD

Jan Feb Time Series

69
Spreadsheet Addin

70
Essbase Spreadsheet Add-in
 Is a software program that is embedded in a client spreadsheet application
 Enables you to retrieve data, create ad hoc reports, and analyze data
 Provides the interface with Essbase Server

MS Excel with Essbase Server Essbase Database

Essbase Spreadsheet
Add-in

71
Essbase Menu and Toolbar
The Essbase menu and toolbar provide tools to access the database.

Essbase Menu

Essbase Toolbar

72
Hyperion
HyperionEssbase
Essbase
Spreadsheet
SpreadsheetAdd-In
Add-In
provides
providesad-hoc
ad-hocanalysis
analysis
ininExcel.
Excel.

Connect
ConnecttotoHyperion
Hyperion
Essbase Server
Essbase Server

73
Connecting to and Disconnecting From
a Database
Connecting to the Database Disconnecting from the Database

1
1

2 5 3
2
3 4

74
Retrieving Data
 Select Essbase > Retrieve.
 Essbase returns top-level data into an empty spreadsheet.

3
MS Excel Essbase Server Essbase Database

Top-level Data

75
Retrieving Multidimensional Data into a Two-
Dimensional Spreadsheet Report
 All dimensions must be represented in the spreadsheet.
 The spreadsheet layout has four sections:

Header Labels

Column
Labels

Row Labels Data Cells

76
Display Options
 Indentation

Totals
 Replacement Subitems None

77
Display Options (cont.)
 Suppress

 Aliases

Member Names Use Aliases

Use Both Member Names and Aliases

78
Questions

? ? ?
?
?

?
79