39238967 Hyperion Essbase

8/13/2019 39238967 Hyperion Essbase

http://slidepdf.com/reader/full/39238967-hyperion-essbase 1/79

Hyperion Essbase



2

What we will cover

Introduction To EssbaseHyperion Essbase DemonstrationDesigning & Explanation of Analytics Service DatabaseLoading DataUnderstanding Analytics Services Structure

Consolidating and Calculating DataSpreadsheet Addin



3

Corporate Information Pyramid

Exploration /

SQL/

Relational/

Cleansing/

TransactionData

Information Mainframes and OLTP Data RDBMS SQL Reporting Applications Text Files Spreadsheet Applications





5

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

Product Time Sales

Diet Cola January 166

Diet Cola February 182

Root Beer January 131

Root Beer February 149

… … …

Relational table

January February

Diet Cola 166 182

Root Beer 131 149

… … …

Data cube



6

3-D CubeDimensions

TimeProductsMarkets

MembersJan, Feb, Mar (Time)Diet Cola, Root Beer, Cream Soda,

Fruit Soda (Products)CA, CT, NY (Markets)

MeasuresSales

267 P r o

d u c

t s

TimeJan Feb Mar

267 units of fruit soda sold in CA in Jan

Diet Cola

Root Beer

Cream SodaFruit Soda

123

131

166

196150

149

182

231115

120

143



The OLAP Solution-Hyperion Essbase

Online Analytical processing System (OLAP) is a category of software that letsanalysis ,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 theenterprise as understood by the user.

OLAP solution as Essbase XTD Analytics Services facilitates intensive analysis andlet managers invent their analysis interactively.

Provides multiple-user access to the analytic information.

Provides a graphical user interface.

Conforms to the client-server architecture.



Hyperion Essbase Demonstration



Starting The Essbase Analytics Server

Starting Administration Server Start Administration Server Console

Defining The database outlineDefining the automatic rules for developing complex outlinesCreating Calculation ScriptCreating Load Rules for loading data into databaseProviding log reports for trouble shooting.

Hyperion Essbase Demonstration



Hyperion Essbase – Product ArchitectureDatabase TierClient Tier Middle Tier

Administration

Services

• Hyperion Analyzer

• Hyperion Reports

Administration

Console

TCP/IP

TCP/IP

HTTP

HTTPEssbase

Spreadsheet Add-in

SpreadsheetServices

HTTP

AdministrationServices



EssbaseMulti-threaded OLAP database softwareBased upon Web-deployable, thin-client architectureComponents:

Essbase ServerEssbase Client

– Essbase Spreadsheet Add-in – ESSCMD

– MaxL data definition language (MaxL DDL) – Data manipulation language (MDX) – Optional. Data Mining



Administration ServicesDatabase and system administrators' interface to EssbaseComponents:

Administration Server Administration Console



Starting and Stopping the Essbase ServerTo 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

Essbase Server Agent

Essbase Server Agent Console



Starting and Stopping the Administration ServerTo start the Administration Server, launch the Administration Server Agentexecutable:

To stop the Administration Server, launch the following executable:

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

Administration Server

Administration Server Agent

Administration Server Agent Console



Starting the Administration Console To start the Administration Console:

1. Launch the console executable:

2. Log on to the console.

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



Enterprise ViewEnterprise View displays your Essbase environment in a hierarchy.

Custom ViewShortcut Menu



Life Cycle of Essbase Database Design

Load Data

Calculate

Reportand

Verify

Build MDB



Creating an Essbase Application

2

3

1



Essbase DatabasesData repository that contains multidimensional analytic dataDatabase objects:

Database outlineData sourcesRules filesCalculation scriptsReport scriptsSecurity definitions

Linked reporting objectsPartition definitions



Creating an Essbase Database

2

1

3



Designing An Analytics Services Database



Analytics Services TerminologyOutline : The presentation of the relationshipsamong 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 ofdimensions as

Standard Attribute

Members : Discrete component making updimensions

Generation/ Levels : Describe position within thehierarchy of the dimension.

Sibling: A child member at the same branch levelas another child member with the same parent.

Shared Member : The data values associatedwith a shared member come from anothermember with the same name. The sharedmember stores a pointer to data contained in theother member and the data is only stored once.

User Defined Attribute (UDA): Used to describea member.



Standard DimensionsEssbase maximizes the performance by dividing the standard dimensions into two

types :

Dense Dimension : A dimension with a high probability that one or more datapoints 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.



Essbase File StructureEssbase Server creates a directory for each application and database.

Outline

Calculation script

Database fileFree fragment file

TBC application

directory

Directory for allapplications

%ARBORPATH%

%HYPERION_HOME%

Database backup

Transaction control

Kernel

Sales database directory

Data source file

Rules files

Data fileIndex file



25

Database OutlineTree structure for dimension hierarchiesConsolidations and mathematical relationships between membersOutline Editor



26

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

ScenarioGen 1

Gen 2

Gen 2

Gen 2

Gen 2

Gen 3

Gen 3

Dimension

Variances

Leaves

Branch

Budget

Actual

Forecast

Act vs Fcst

Act vs Bud

Bud vs Fcst

Gen 3

Leaves



27

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

Scenario Level 1

Level 0

Level 0

Level 0

Level 1

Level 0

Level 0

Variances

Budget

Actual

Forecast

Act vs Fcst

Act vs Bud

Bud vs Fcst

Level 0

Level 2



28

Outline Terms: GenealogyGenealogy names relationships between members in the outline.

Scenario

Variances Parent

Budget

Actual

Forecast

Act vs Fcst

Act vs Bud

Bud vs Fcst

Ancestors of• Act vs Bud• Act vs Fcst• Bud vs Fcst

Child

Descendantsof Scenario

Child

Child



29

Time DimensionTime dimension defines the time period of the analytic data.

Year

2004

2003

Separate Dimension Model Fiscal Year Crossover Model

Year

FY04

FY03

Total Year

Qtr4

Qtr2

Qtr1

Qtr3

Qtr2FY04

Qtr1FY04

Qtr3FY04

Qtr4FY04



30

Outline EditorOutline Editor enables you to design the database outline.

Toolbar

Working pane

Tabs

Controlbuttons



31

Adding Dimensions and MembersYou can add dimensions and members to the outline manually ordynamically.

Adding a sibling Adding a child



32

Setting the Dimension TypeDimension Type Buttons

2

AssignedDimensionType

1



33

Verifying and Saving the Database OutlineEssbase enables you to:

Check the database outline for errors

Save the outline to the Essbase server or the client file system

Outline errors



34

Enhancing Computational CapabilitiesThe database outline contains the following metadata that enhancesEssbase computational capabilities:

Consolidation operatorsMember formulasVariance reporting calculationTime balance



35

Consolidation property defines how the member rolls up to its parent.Valid consolidation operators are:

Addition (+)Subtraction (-)Multiplication (*)Division (/)Percent (%)

Ignore (~)

Consolidation Property and Operators

Sales

COGS

(+)

(-)

Margin = Sales – COGS

Margin

Excluded fromconsolidation



36

Defining Member CalculationMember formulas define complex computations in the outline.

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

Markup = (Retail - Cost) % Retail;

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



37

Creating Member FormulasCreate the member formula inFormula Editor.

1

2

Formula Editing pane

3

4

5



38

Variance Reporting CalculationMembers that represent expense must have the Expense Reporting tag.Essbase provides two variance reporting functions:

@VAR(mbrName1, mbrName2)@VARPER(mbrName1, mbrName2)

Example:

Variance equals Actual minus Budget.

Actual Budget Variance Variance %

Sales 100 120 -20 -16.67

COGS (Expense Reporting) 100 120 20 16.67

Variance = @VAR(Actual, Budget);





40

Label Only MembersDo not store dataGroup members to ease navigation and reporting

Take the value of the first child that stores data

120 125 150 150 545

110 140 140 170 560

Scenario

Budget

Actual

Label Only

Qtr4

Total Year

Qtr3Qtr2Qtr1

120 125 150 150 545





42

Attribute Dimensions Attributes describe characteristics

of data such as the size and color ofproducts.

Through attributes you can groupand analyze members of dimensionsbased on their characteristics.



43

Attribute Dimensions Analysis by attribute provides depth and perspective, supporting more informed

decisions .

Products

Colas

Cola

Caffeine Free

Kool Cola

Intro Date

New Millennium

Summer Time FunTrue

Old Time FavoritesFalse

PromotionCaffeinated

05-10-2002

01-25-2003

Attribute dimensionsBase dimension

01-01-2001

09-30-2001



44

Associating Attribute Dimensions

1

4

32

5 Associated Attribute Dimensions



45

Setting member storage properties

StorageProperty

When to Use

Store Store the data value with the member.DynamicCalc andStore

Not calculate the data value until a user requests it, and then store thedata value.

Dynamic

Calc

Not calculate the data value until a user requests it, and then discard

the data value. Never share Not allow members to be shared implicitly.

Members tagged as Never share can only be explicitly shared. Toexplicitly share a member, create the shared member with the samename and tag it as shared.

Label only Create members for navigation only, that is, members that contain nodata values.

Sharedmember

Share values between members. For example, in the Sample Basicdatabase, the 100-20 member is stored under the 100 parent and sharedunder Diet parent.





47

Loading Data





49

Free-Form Data LoadingYou can load a data source directly if it contains all information required to load its datavalues into the database.

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

DataScenario Markets Products Measures Total Year

Budget "200-30" DallasJan Feb Mar Apr May Jun

Sales 150 160 150 140 130 110COGS 60 60 60 50 50 40Marketing 20 20 20 20 10 10Payroll 20 20 20 20 20 20

Data

Scenario MarketsProductsMeasures

Total Year



50

Performing Free-Form Data Loads

3

2

1

4



51

Structured Data Loading with RulesYou 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.

1st quarter Budget DataJan Feb Mar Qtr1

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

Map fields to Markets, Products, andMeasures dimensions.

Replace C-Free Cola withCaffeine-Free Cola.

Ignore fieldduring data load.

Map the header to Budgetin the Scenario dimension.



52

Creating Data Load Rules Files

12

3



53

Opening Data Sources

1

3

2



54

Loading Data with the Rules File

3

4

2

5

6

1



55

Understanding Analytics Services Structure

b S O i



56

Essbase Data Storage Options

Aggregate Storage Block Storage

Large number of base dimensions

Extremely small batch window, ormany applications to compute

Combinations of large dimensions

Calculation script-based applications

Write-back capability

Interactive planning, allocations,

sophisticated analytics

Bl k S O i



57

Block Storage OverviewBlock storage databases use dense and sparse dimensions.

Measures

Total Year

Dense Dimensions

Products

Customers

Sparse Dimensions

S i Di i D S



58

Setting Dimensions as Dense or Sparse

Data-descriptor dimensions

Business-view dimensions

Attribute dimensions

2

3

1

A S O i



59

Aggregate Storage Overview A new storage kernel Aggregate -intensive cubes

Larger numbers of dimensions andmembersOptimized for sparser data sets,operational analyticsSignificantly reduces calculationtimes and disk footprint

Designed to work seamlessly withexisting interfaces and user skillsReduces complexity when creatingEssbase databases



60

Consolidating & Calculating Data

D t b C l l ti



61

Database CalculationData 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 0data.

Aggregated Data

ReportLevel 0 Data

O tli C l l ti



62

Outline CalculationsBy 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 calculationsFollow outline relationshipsFastest way to calculate entire database

Outline

Input Data

CalculatedDatabase

O d f O tli C l l ti



63

Order of Outline Calculations

Essbase calculates the outline in the following order:1. Accounts dimension2. Time dimension3. Other dense dimensions in top-down order4. Other sparse dimensions in top-down order5. Members that are tagged as Two-Pass calculations

Calculated Data(After Two-Pass)

1st Pass 2nd Pass

Calculated Data(Before Two-Pass)

Level 0 Data

Calc lation Scripts



64

Calculation ScriptsCalculation 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 aredefined by the database outline.Calculate all or a portion of the databaseControl the order in which dimensions are calculatedPerform complex calculations

Level 0 DataCalculatedDatabase

Calculation Script

Creating Calculation Scripts



65

Creating Calculation Scripts

Functions andCommands

Dimensions andMembers

Opening the CalculationScript Editor

Outline Enhancements That Affect Calculations



66

Outline Enhancements That Affect Calculations

Two-Pass CalculationsDynamic Calculations

Dynamic Time Series

Two Pass Calculations



67

Before Two-Pass

After Two-Pass

Two-Pass Calculations

Dynamic Calculations



68

Dynamic Calculations Advantages

Shorten batch database calculation time

Reduce disk storage usageReduce database restructure timeReduce time to perform a backup

DisadvantagesMay increase retrieval timeChange calculation order

PrecalculatedDatabase

DataRequest

CalculatedRequest

Report

Dynamic Time Series



69

Dynamic Time Series January February March

Sales 100 120 130

QTDSales

100 220 350

Jan Feb Calc Script

Jan FebDynamic

Time Series

Stored QTD

Non-stored QTD



70

Spreadsheet Addin



Essbase Menu and Toolbar



72

Essbase Menu and ToolbarThe Essbase menu and toolbar provide tools to access the database.

Essbase Toolbar

Essbase Menu



73

Hyperion Essbase

Spreadsheet Add-Inprovides ad-hoc analysisin Excel.

Connect to HyperionEssbase Server

Connecting to and Disconnecting From



74

Connecting to and Disconnecting Froma Database

1

3

4

2 5

1

3

42

Disconnecting from the Database Connecting to the Database



Retrieving Multidimensional Data into a Two-



76

Retrieving Multidimensional Data into a TwoDimensional Spreadsheet Report

All dimensions must be represented in the spreadsheet.

The spreadsheet layout has four sections:

Data Cells

ColumnLabels

Row Labels

Header Labels

Display Options



77

Display OptionsIndentation

ReplacementTotals Subitems None

Display Options (cont )



78

Display Options (cont.)Suppress

Aliases

Member Names Use Aliases

Use Both Member Names and Aliases

Questions



Questions

? ??

??

?

Documents

39238967 Hyperion Essbase