25136135 Essbase Intro

download 25136135 Essbase Intro

of 35

Transcript of 25136135 Essbase Intro

  • 8/6/2019 25136135 Essbase Intro

    1/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    TrainingOn

    Oracle Hyperion Products Suite&

    Oracle Business IntelligenceEnterprise Edition

    Amit Sharma

    learnhyperion.wordpress.com

  • 8/6/2019 25136135 Essbase Intro

    2/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    Hyperion Product Suite

    Hyperion

    Hyperion BI+Reporting

    Hyperion BI+Application

    Hyperion BI+ DataManagement

    HFM (HyperionFinancial Management)

    HSF (HyperionStrategic Financial)

    Hyperion

    Planning

    HPM (Hyperion

    Performance

    Management)

    MDM (Maser

    DataManagement)

    FDQM (FinancialQuery Data

    Management)

    HAL (HyperionApplication Link)

    DIM (DataIntegrated

    Management)

    Hyperion

    Essbase

    AnalyzerReports

    Interacting

    Reports

    ProductionReporting

  • 8/6/2019 25136135 Essbase Intro

    3/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    What is Essbase?

    It is a multidimensional database that enables Business Users to

    analyze Business data in multiple views/prospective and atdifferent 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

  • 8/6/2019 25136135 Essbase Intro

    4/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    Typical Data Warehouse Architecture

    Operational

    Systems/Data

    Select

    Extract

    Transform

    Integrate

    Maintain

    Data

    Preparation

    Data

    Marts

    Data

    Warehouse

    (OLAP

    Server or

    RDBMS

    Data

    Repository)

    Metadata

    ODS

    Metadata

    Select

    Extract

    Transform

    Load

    Data

    Preparation

    MultiMulti--tiered Data Warehouse with ODStiered Data Warehouse with ODS

    Data StageData Stage

  • 8/6/2019 25136135 Essbase Intro

    5/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    Life Cycle Of Essbase

    1.Creating the Database

    2.Dimensional Building

    3.Data Loading

    4.Performing the Calculations

    5.Generating the Reports

  • 8/6/2019 25136135 Essbase Intro

    6/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    Oravision Oracle Online Training/Consultancy Solution [email protected]

    Essbase Multi Dimension Data Modeling (Complete Life Cycle)

    Physical Data ModelPhysical Tables from ODS Environment

    Logical Multi Dimensional Model

    Multi Dimensional View

    Presentation Layer Reporting

  • 8/6/2019 25136135 Essbase Intro

    7/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    HYPERION Essbase Components

    1) Essbase Analytic Server (Essbase Server)

    2) Essbase Administration Server (User Interface)

    3) Essbase Integration Services (RDBMSEssbase)

    4) Essbase Spread Sheet Services

    5) Essbase Provider Services.

    6) Essbase Smart-view

    7) Essbase Studio (New Feature)

  • 8/6/2019 25136135 Essbase Intro

    8/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    1.Client tier

    2.Middle Tier (App tier)

    3.Database tier

    Essbase Architecture

  • 8/6/2019 25136135 Essbase Intro

    9/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    Architecture

  • 8/6/2019 25136135 Essbase Intro

    10/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    Contents

    Overview (OLAP)

    Multidimensional Analysis

    * Multidimensional Analysis Introduction

    * Operations In multidimensional Analysis

    * Multidimensional Data Model

    * Multi-Dimensional vs. Relational

    Overview of system 9.x/11.x

    *Hyperion System 9 Smart view

    *Hyperion System 9 BI+ Interactive reporting

    *Hyperion System 9 BI+ Analytic services

    *Hyperion system 9 shared services

    *Hyperion system 9 White Board

    Introduction to Essbase

  • 8/6/2019 25136135 Essbase Intro

    11/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    Multidimensional Viewing and Analysis

    Sales Slice of the Database

  • 8/6/2019 25136135 Essbase Intro

    12/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    Online Analysis Processing(OLAP)

    It enables analysts, managers and executives to gain insight into datathrough fast, consistent, interactive access to a wide variety of possibleviews of information that has been transformed from raw data to reflectthe real dimensionality of the enterprise as understood by the user.

    Data

    Warehouse

    Time

    Product

  • 8/6/2019 25136135 Essbase Intro

    13/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    Overview of OLAP

    OLAP can be defined as a technology which allows the users to view the aggregatedata across measurements (like Maturity Amount, Interest Rate etc.) along with a set ofrelated parameters called dimensions (like Product, Organization, Customer, etc.)

    Relational OLAP (ROLAP)

    Relational and Specialized Relational DBMS to store andmanage warehouse data

    OLAP middleware to support missing pieces Optimize for each DBMS backend

    Aggregation Navigation Logic

    Additional tools and services

    Example: Micro strategy, MetaCube (Informix)

    Multidimensional OLAP (MOLAP) Array-based storage structures

    Direct access to array data structures

    Example: Essbase (Arbor), Accumate (Kenan)

    Domain-specific enrichment

  • 8/6/2019 25136135 Essbase Intro

    14/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    OLAP

    HOLAPMOLAPROLAP

    Relational

    OLAPMultidimensional

    OLAP

    Hybrid

    OLAP

    Implementation Techniques

    MOLAP - Multidimensional

    OLAP

    Multidimensional

    Databases for database

    ROLAP - Relational

    OLAP

    Access Data stored

    in relational Data

    Warehouse for

    OLAP Analysis

    HOLAP - Hybrid OLAP

    OLAP Server routes

    queries first to MDDB,

    then to RDBMS and result

    processed on-the-fly in

    Server

  • 8/6/2019 25136135 Essbase Intro

    15/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    Key Features of OLAP applications

    Multidimensional views of data

    Calculation-intensive capabilities

    Time intelligence

    **Key to OLAP systems are multidimensional databases.

    Multidimensional databases not only consolidate and calculate data; theyalso provide retrieval and calculation of a variety of data subsets.

    A multidimensional database supports multiple views of data sets for userswho need to analyze the relationships between data categories

    Ex: Did this product sell better in particular regions? Are there regionaltrends?

    Did customers return Product A last year? Were the returns due to productdefects?

  • 8/6/2019 25136135 Essbase Intro

    16/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    What is Multidimensional Analysis

  • 8/6/2019 25136135 Essbase Intro

    17/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    A multidimensional database supports multiple views of data sets for users who need

    to analyze the relationships between data categories. For example, a marketing

    analyst might want answers to the following questions:

    How did Product A sell last month? How does this figure compare to

    sales in the same month over the last five years? How did the productsell by branch, region, and territory?

    Did this product sell better in particular regions? Are there regional trends?

    Multidimensional databases consolidate and calculate data to provide

    different views. Only the database outline, the structure that defines all elements ofthe database, limits the number of views. With a multidimensional database, users can

    pivotthe data to see information from a different viewpoint, drill down to find more

    detailed information, or drill up to see an overview.

    Multidimensional Analysis

  • 8/6/2019 25136135 Essbase Intro

    18/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    Multidimensional Analysis

    Analysis of data from multiple

    perspectives.

    Sales Report By Month

    All Products Customer Product

    Month Jan Feb Mar

    Gross Sales 2,358,610 2,345,890 58,860

    Discount 116,616 138,856 20,567

    Net Sales 2,477,428 2,566,526 89,196

    Jan Gross Sales For all the products and all

    customers in the current year. This will give the

    details that which customer bought the most sales

    and which product sold least in a month and year

    Product Report By Month

    Gross Sales Customer Product

    Month Jan Feb Mar

    Performance 1,597,560 1,697,890 775,600

    Values 116,616 138,856 20,567

    All Products 2,358,6 10 2,566,526 89,196

    Variance Report By Channel

    All Products Gross Sales Jan

    Gross Sales Current Year Budget Act Vs Bud

    Performance 775,600 1,697,890 224,160

    Values 116,616 1,651,006 20,567

    All Products 2,358,6 10 2,566,526 89,196

  • 8/6/2019 25136135 Essbase Intro

    19/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    OLAP Operations

    Drill Down

    Time

    Product

    Category e.g Electrical Appliance

    Sub Category e.g Kitchen

    Product e.g Toaster

  • 8/6/2019 25136135 Essbase Intro

    20/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    OLAP Operations

    Drill Up

    Time

    Product

    Category e.g Electrical Appliance

    Sub Category e.g Kitchen

    Product e.g Toaster

  • 8/6/2019 25136135 Essbase Intro

    21/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    OLAP Operations

    Slice and Dice

    Time

    ProductProduct=Toaster

    Time

  • 8/6/2019 25136135 Essbase Intro

    22/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    OLAP Operations

    Pivot

    Time

    Product

    Region

    Product

  • 8/6/2019 25136135 Essbase Intro

    23/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    Operations In multidimensional Analysis

    Aggregation (roll-up)

    dimension reduction: e.g., total sales by city

    summarization over aggregate hierarchy: e.g., total sales by city and year -> total sales by region and by year

    Selection (slice) defines a sub cube

    e.g., sales where city = Palo Alto and date = 1/15/96

    Navigation to detailed data (drill-down)

    e.g., (sales - expense) by city, top 3% of cities by average income

    Visualization Operations (e.g., Pivot)

  • 8/6/2019 25136135 Essbase Intro

    24/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    Database is a set offacts (points) in a multidimensional space

    A fact has a measure dimension

    quantity that is analyzed, e.g., sale, budget, Operating Exp,

    A set ofdimensions on which data is analyzed

    e.g. , store, product, date associated with a sale amount

    Dimensions form a sparsely populated coordinate system

    Each dimension has a set ofattributes

    e.g., owner city and county of store

    Attributes of a dimension may be related by partial order

    Hierarchy: e.g., street > county >city

    Lattice: e.g., date> month>year, date>week>year

    Multidimensional Data Model

  • 8/6/2019 25136135 Essbase Intro

    25/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    Uses a cube metaphor to describe data storage.

    An Essbase database is considered a cube, with

    each cube axis representing a different dimension,

    or slice of the data (accounts, time, products, etc.)

    All possible data intersections are available to the

    user at a click of the mouse.

  • 8/6/2019 25136135 Essbase Intro

    26/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    Multidimensional Data

    10

    47

    30

    12

    Juice

    Cola

    Milk

    Cream

    Sales Volume

    as a function

    of time, city

    and product

    3/1 3/2 3/3 3/4

    Date

  • 8/6/2019 25136135 Essbase Intro

    27/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    A Visual Operation: Pivot (Rotate)

    1010

    4747

    3030

    1212

    Juice

    Cola

    MilkCream

    3/1 3/2 3/3 3/4

    DateDate

    Product

  • 8/6/2019 25136135 Essbase Intro

    28/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    Multidimensional Viewing and Analysis

    Consider the three dimensions in a databases as Accounts, Time, andScenario where Accounts has 4 members, Time has 4 members and

    Scenario has two members.

    Three-Dimensional Database

  • 8/6/2019 25136135 Essbase Intro

    29/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    The shaded cells is called a slice illustrate that, when you refer to Sales,

    you are referring to the portion of the database containing eight Sales

    values.

    MultidimensionalViewing and Analysis

    Sales Slice of the Database

  • 8/6/2019 25136135 Essbase Intro

    30/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    MultidimensionalViewing and Analysis

    Actual, Sales Slice of the Database

    When you refer to Actual Sales, you are referring to the four Sales values

    where Actual and Sales intersect as shown by the shaded area.

  • 8/6/2019 25136135 Essbase Intro

    31/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    Multidimensional Viewing and Analysis

    Data value is stored in a single cell in the database. To refer to a specific data

    value in a multidimensional database, you specify its member on eachdimension. The cell containing the data value for Sales, Jan, Actual is shaded.

    The data value can also be expressed using the cross-dimensional operator (-

    >) as Sales -> Actual -> Jan.

    Sales -> Jan -> ActualSlice of the Database

  • 8/6/2019 25136135 Essbase Intro

    32/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    Multidimensional Viewing and Analysis

    Data for January

    Data for February

    Data for Profit Margin

    Data from Different Perspective

  • 8/6/2019 25136135 Essbase Intro

    33/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    Multi-dimensional database are usually queried top-down the user starts at the top and drills intodimensions of interest.

    - Can perform poorly for transactional queries

    Relational databases are usually queried bottom-up the user selects the desired low level data andaggregates.

    - Harder to visualize data; can perform poorly forhigh-level queries

    Multi-Dimensional vs. Relational

    Total Products

    P01 P02 P03

    P01 P02 P03

    Total Products

  • 8/6/2019 25136135 Essbase Intro

    34/35

    Learnh erion.word ress.com aloo a2 ahoo.com

    OLAPVs RDBMS

    In RDBMS, we have:

    DB -> Table -> Columns -> Rows

    In OLAP, we have:

    CUBES

  • 8/6/2019 25136135 Essbase Intro

    35/35

    Learnh erion word ress com aloo a2 ahoo com

    Questions??????