25136135 essbase intro
Post on 07-Apr-2018
227 Views
Preview:
TRANSCRIPT
-
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 aloo_a2@yahoo.com
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??????
top related