View
7
Download
0
Category
Preview:
Citation preview
1
DOAG BI 2013, 17.04.2013
Stefan Vogel, Senior BI-Consultant
High Performance Datawarehouse Analyse – Die Oracle OLAOP Option 11g im Vergleich
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 2
Agenda
Vorstellung FRT Consulting GmbH
Einleitung DWH Architekturen
Vorstellung OLAP Option
Cube-Organized Materialized Views
Oracle OLAP Daten abfragen
Abgrenzung Oracle Essbase
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 3
IT Beratung und Umsetzung Prozessanalyse, Architekturberatung, Konzepte, Entwicklung, Schulung, Support
Geschäftsanwendungen -- Rapid Development mit APEX -- Sicherheitskonzepte -- Datenbankdesign -- Prozessanalyse & Architektur
Oracle, OBIEE, Essbase, BO, Penthao, OWB/ODI
BI / DWH / EPM -- BI Frontend (Analytics, Reports, Dashboards) -- BI/EPM Applikationen -- DWH Architektur und Umsetzung -- ETL Konzepte und Umsetzung
Foku
sber
eich
e Te
chno
logi
e S
ervi
ces
Fundus-
Verwaltung ProFundus
Oracle, APEX, SQL, PL/SQL, MySQL, Java, PHP
FRT Consulting - Leistungsspektrum
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 4
• DWH • Architektur
• Analyse, DWH Design, dimensionale Modelle, Kennzahlen • ETL-Prozesse
• Konzepte, Implementierung ETL-Prozesse • Automatisierung
• Qualitätssicherung • Check Datenqualität und Konsistenz • Check Richtigkeit
• Frontend Entwicklung • Aufbau Business Layer • Frontend implementierung (Dashboard, Reports…..) • Migration, Betriebsunterstützung, Performanceoptimierung
• Toolmigration (Discoverer Oracle BI) • Schulung, Betriebsunterstützung, Migration
• Einziger Oracle BI Foundation specialized Partner in AT
BI / DWH - Leistungsspektrum
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 5
• Query tools access star schema stored in an Oracle data warehouse
• Most queries at a summary level
• Summary queries against detail-level data can be expensive to process
• Aggregation • Calculations
Typacilly star schema
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 6
Category ITEM_ID ITEM_NAME CATEGORY_NAME TOTAL_CATEGORY
Field_Org ORG_ID ORG_DESC PARENT
Time DAY_ID MONTH_NAME QUARTER_NAME YEAR_NAME ALL_YEARS …
Expense_Fraud_View DAY_ID ORG_ID ITEM_ID EXPENSE_AMOUNT PREDICTION PROBABILITY PREDICTED_FRAUD_COST
1 *
1
*
1
* Year
Quarter
Month
Day
Time
Total Category
Category
Item
Category Organization
Parent/Child All Years
Benefits: • Business rules are shared across all client
applications • Simplifies end user analysis
• Query specification • Calculation definition
Hierarchical Dimensions Codify relationships in the data
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 7
Calculate and Store in Tables
Store summaries and analytic measures in tables
• Advantages – Single source of truth – Use with many different SQL-based
reporting tools • Disadvantages
– Expensive and time consuming to develop analytics in ETL process and to maintain summary tables
– Difficult to pre-compute many types of measures
– May require pre-calculating large volumes of data
– Does not support many dimensional reporting tools (e.g., Excel)
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 8
Calculate in BI Server
Store summaries in tables and calculate analytic measures in BI server
• Advantages – Dynamic calculation of KPIs; reduced ETL
process • Disadvantages
– Locked into tools that work with BI Server
– Managing summary data in DWH can be challenging
– Query performance might be a challenge – Difficult to pre-compute many types of
measures – Does not support dimensional reporting
tools
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 9
Use a Stand-alone OLAP Server
Use an stand-alone OLAP server for summaries and calculations
• Advantages – OLAP servers are optimized for analytics
and summary data; reduced ETL process for tables
– Supports dimensional query tools • Disadvantages
– Multiple copies of data; no single version of truth
– Requires additional users, security policies, etc.
– Requires additional servers, DBAs, training, etc.
– Does not support relational (SQL-based) query tools
– Lacks mature high availability and disaster recovery features
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 10
An Embedded OLAP Solution
Oracle OLAP, embedded in Oracle Database
• Advantages – OLAP optimized for analytics and fast
query – Rapid BI application development: easy to
add analytics, reduces ETL requirements for tables.
– Supports dimensional and relational BI tools
– Single version of the truth; one copy of data and business rules / calculations
– Eliminates need for separate servers, DBAs, reporting tools, DBAs, etc.
– All Oracle security, high availability, GRID, etc. features work with Oracle OLAP cubes
– Runs on Oracle Exadata • Disadvantages
– ?
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 11
Embedded in Oracle Database 11g
Runs within Oracle instance on same server
OLAP cubes are stored in Oracle data files
Object and data security for OLAP cubes Managed using Oracle database
Fully compatible with: Real Application Clusters and Grid Computing
Cubes are easily queried using SQL
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 12
Easy Analytics Analytic Functions
Time-series calculations
Calculated Members
Financial Models Depreciation Schedules Payment/Interest
Schedules IRR NPV Growth rates
Forecasting Basic Expert system
Allocations
Regressions
Number of leaf nodes
Dimensional/Hierarchy Functions Parent/Child/Ancestors/Descendants of
Statistical Categorization Standard Deviation Correlation Distribution Methods
Top X percent
Custom functions
…and many more
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 13
Aggregation
Highlights Aggregation methods can vary
by dimension.
Pre-calculation options for performance acceleration.
Non-numeric data types are supported by certain aggregation operations.
Aggregation Operators Sum
Maximum
Minimum
Hierarchical Weighted Average
Hierarchical Average
Scaled Sum
Weighted Average
… and many more.
13
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 14
OLAP Option
One cube can be used as A summary management solution to SQL-based business intelligence applications as cube-organized materialized views A analytically rich data source to SQL-based business intelligence applications as SQL cube-views A full-featured multidimensional cube, servicing dimensionally oriented business intelligence applications
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 15
One Cube, Dimensional or SQL Tools Single version of the truth
SQL Query
OLAP Query
Metadata Data
Business Rules
Extract, Load & Transform (ELT)
Centrally managed data, meta data and business rules
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 16
SQL Query of OLAP Cubes
BI Application
Cube Materialized
Views
SQL
Automatic Query
Rewrite
BI Application
Cube Views
SQL
Oracle Cube
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 17
Cube Organized Materialized Views
Transparently enhance the query performance of BI applications Data is managed in an Oracle cube
Fast query Fast refresh Manage a single cube instead of 10’s, 100’s or 1,000’s of table-based materialized views
Applications query base / detail relational tables Oracle automatically rewrites SQL queries to OLAP cubes Access to summary data in the cube is fully transparent
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 18
Summary Data: Collections of Materialized Views
Materialized Views Typical MV Architecture Today
Users expect excellent query response for all summary queries Might require 10’s, 100’s or even 1,000’s of
materialized views Difficult to manage Longer build and update times
Fact Table: Sales by Day, Item, Customer and Channel
BI Application
SELECT SUM(sales) GROUP BY quarter, brand, region, channel
Automatic Query
Rewrite
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 19
Cube-Organized Materialized Views Automatic Query Rewrite
Fact Table: Sales by Day, Item, Customer and Channel
BI Application
SELECT SUM(sales) GROUP BY quarter, brand, region, channel
Automatic Query Rewrite
• A single cube manages summaries for all groupings in the model
• A cube can be represented as a cube-organized materialized view
• Oracle automatically rewrites summary queries to the cube
• A singe cube can replace 10’s, 100’s or 1,000’s of materialized views
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 20
Query Rewrite
Typical query issued by Oracle Business Intelligence Enterprise Edition.
Query is automatically rewritten by Oracle to access summary data in the cube-organized materialized view.
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 21
Cube-Organized Materialized Views Fast, Incremental MV Refresh
Fact Table: Sales by Day, Item, Customer and Channel
BI Application
SELECT SUM(sales) GROUP BY quarter, brand, region, channel
• A single cube is refreshed using MV refresh system • Fast, incremental
update from MV logs. • Fast, incremental
aggregation within the cube.
• Efficient management of sparse data sets.
• Replaces 10’s, 100’s or even 1,000’s of table-based MVs
MV Refresh
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 22
Cube Organized Materialized Views
An excellent summary management solution for business intelligence tools such as BI EE, MicroStrategy, Cognos and Business Objects
Cube organized materialized views are similar to materialized views on pre-built tables Cube organized materialized views are meta data only – they do not store data; data comes from the cube
A common implementation will be to leave detail data in tables and create the cube at aggregate levels E.g. tables with day, customer and cube with month, zip code
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 23
Oracle Cube
Relational (SQL)
Dimensional (MDX)
Oracle OLAP 11g One Cube, Any Tool and Consistent Results
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 24
Calculations defined in the Oracle cube make BI applications more valuable to business users
Oracle OLAP 11g OLAP Calculations in SQL-Based BI Tools
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 25
Easy to define analytic measures in Oracle Cube SUM(SALES_CUBE.SALES) OVER HIERARCHY (TIME.CALENDAR BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER WITHIN ANCESTOR AT LEVEL TIME.CALENDAR_YEAR)
Oracle OLAP 11g OLAP Calculations in SQL-Based BI Tools
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 26
Oracle OLAP 11g OLAP Calculations in SQL-Based BI Tools
Easy to query analytic measures using simple and efficient SQL SELECT t.long_description, p.long_description, s.sales, s.sales_ytd, s.sales_ytd_yr_ago, s.sales_ytd_pctchg_yr_ago, s.sales_3_period_moving_avg, s.sales_target, s.actual_pct_of_target FROM time view t, product_view p, sales_cube_view s JOINS …
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 27
All data, measures and summaries, calculated in Oracle cube and presented in Excel
Oracle OLAP 11g Dimensional Presentation of Oracle Cube in Excel
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 28
Middle Tier BI Tools (e.g., OBIEE)
SQL
Star / Snowflake Tables Day
Week
Month
Quarter
Year
State Customer Country Region
Detail Tables
OLAP in the Data Warehouse
Cubes with Embedded
Summaries & Rich Analytics
MDX
Cubes are typically used with tables as part of an overall BI solution
SQL
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 29
An Alternative to In-Database OLAP : Oracle Essbase
• Oracle’s in-database OLAP has lots of advantages • Single application to manage, leverage SQL knowledge
and Oracle scalability • A very good way of boosting the performance and
capability of your Oracle DW • That is not the only use for OLAP • Some users required full multi-dimensional access to OLAP
data • MDX, XML/A, OLAP-aware query, planning and
forecasting tools • SQL is not appropriate to these sorts of uses
• Some users have not centralized on Oracle Database as their DW engine
• Finance departments • Heterogeneous environments
• For these customers, Oracle Essbase is an interesting alternative
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 30
Essbase Overview
• Standalone OLAP server now owned by Oracle • Acquired as part of the Hyperion Acquisition
• Adds a fully-featured OLAP server, separate to the database, as part of the Fusion Middleware family of products
• End-User focused, very popular with business users
• Used to power many of the Hyperion performance management applications
• Similar capabilities to Oracle OLAP
• Supports MDX and XML/A rather than SQL access
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 31
Oracle OLAP vs. Oracle Essbase
Feature Oracle OLAP 11g Oracle Essbase
Storage Method Multidimensional Arrays Multidimensional Arrays
Query language SQL, via query rewrite or SQL views MDX via Simba Plugin
MDX and XML/A
Process Type In-database, embedded process
Stand-alone server
Product dependency Oracle Database Enterprise Edition
Any relational database
Primary use Enhancing SQL-based Data Warehouses
Supporting OLAP analytical applications
Wir machen aus Daten Wissen. © 2013 FRT Consulting GmbH 32
Vielen Dank für die Aufmerksamkeit!
Kontaktadresse: Stefan Vogel FRT Consulting GmbH Liebenauer Hauptstraße 2-6 A-8041 Graz Österreich
Telefon +43 (0) 316-71 12 12 Fax: +43 (0) 316-71 12 12 - 99 E-Mail stefan.vogel@frt.at Internet: www.frt.at
Recommended