DOAG BI 2013, 17.04...1 DOAG BI 2013, 17.04.2013 Stefan Vogel, Senior BI-Consultant High Performance...

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


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


• 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


• 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


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


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)


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


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


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

– ?


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


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


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


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


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


SQL Query of OLAP Cubes

BI Application

Cube Materialized

Views

SQL

Automatic Query

Rewrite

BI Application

Cube Views

SQL

Oracle Cube


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


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


Cube-Organized Materialized Views Automatic Query Rewrite


BI Application


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


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.


Cube-Organized Materialized Views Fast, Incremental MV Refresh


BI Application


• 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


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


Oracle Cube

Relational (SQL)

Dimensional (MDX)

Oracle OLAP 11g One Cube, Any Tool and Consistent Results


Calculations defined in the Oracle cube make BI applications more valuable to business users

Oracle OLAP 11g OLAP Calculations in SQL-Based BI Tools


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)




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 …


All data, measures and summaries, calculated in Oracle cube and presented in Excel

Oracle OLAP 11g Dimensional Presentation of Oracle Cube in Excel


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


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


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


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


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 [email protected] Internet: www.frt.at

DOAG BI 2013, 17.04...1 DOAG BI 2013, 17.04.2013 Stefan Vogel, Senior BI-Consultant High Performance...

Documents

Transcript of DOAG BI 2013, 17.04...1 DOAG BI 2013, 17.04.2013 Stefan Vogel, Senior BI-Consultant High Performance...