slide1
Download
Skip this Video
Download Presentation
Carey Probst Technical Director Technology Business Unit - OLAP Oracle Corporation

Loading in 2 Seconds...

play fullscreen
1 / 37

Carey Probst Technical Director Technology Business Unit - OLAP Oracle Corporation - PowerPoint PPT Presentation


  • 246 Views
  • Uploaded on

Carey Probst Technical Director Technology Business Unit - OLAP Oracle Corporation. 3 Days: Raw Data to OLAP Session: 40206 A Practical Approach for Rapidly Delivering Successful OLAP Solutions. Challenge. Deliver fully-functional OLAP solution in 3 days Keys to Success:

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Carey Probst Technical Director Technology Business Unit - OLAP Oracle Corporation' - Jimmy


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide2
Carey ProbstTechnical Director

Technology Business Unit - OLAP

Oracle Corporation

slide3
3 Days: Raw Data to OLAPSession: 40206

A Practical Approach for Rapidly Delivering Successful OLAP Solutions

challenge
Challenge
  • Deliver fully-functional OLAP solution in 3 days
  • Keys to Success:
    • Existing, populated data source
    • Well-defined scope of data destined for OLAP
    • A little knowledge of Oracle Warehouse Builder
    • A preference for a reporting interface
how is this possible
How Is This Possible?

Key points:

  • Oracle moved the OLAP engine into the database
    • No need to extract & reload data
    • Leverage existing designs for OLAP
  • Automation of complex OLAP design tasks with Oracle Warehouse Builder
    • Build, Deploy, Load & Query data
  • Provide several methods of access
    • SQL access to OLAP
    • Allowing mix-and-match of OLAP and Relational
    • Support for multiple access tools & technologies
olap architecture terminology
OLAP Architecture & Terminology

Oracle Database

OLAP API

Oracle Call Interface

JDBC

Relational Technology

Object Technology

OLAP Technology

SQL Engine

Table Functions

Multidimensional Engine

Relational

Cubes

Multidimensional Cubes

step 1 populated data store
Step 1: Populated Data Store
  • Constructing OLAP solutions from warehouses is simple if
    • Dimensions have been identified
    • Hierarchies have been identified
    • Measures are known
    • Mapping to star schema is simplified
    • Mapping to OLAP structures streamlined
    • Many traditionally difficult queries can be easily solved by OLAP
step 2 defined scope of data
Step 2: Defined Scope of Data
  • Know what data should logically be summarized for queries
  • Know types of queries users will want to ask
  • Known level of aggregation
  • Known data transformations required
step 3 oracle warehouse builder
Step 3: Oracle Warehouse Builder
  • Oracle Warehouse Builder streamlines many complex tasks of building OLAP solution
    • Design & Metadata population
    • Deployment of OLAP Cubes
    • Loading of Data
olap design best practices
OLAP Design – Best Practices
  • Best Practice Guidelines
  • Long and Short Descriptions Dimension Attributes:
    • Level Attribute mapped to column with name suffixed by ‘_LONG_NAME’ or ‘_SHORT_NAME’
  • Time Dimension Descriptors:
    • Table name suffixed with ‘_TIME’
    • Level Attribute with column suffix ‘_END_DATE’
    • Level Attribute with column suffix ‘_TIME_SPAN’
    • Level names suffixed with _DAY, _MONTH etc..
olap deployment15
OLAP Deployment
  • Deploy scripts using the Deployment Manager
  • Deploy OLAP metadata via OLAP bridge:
    • Creates all skeleton objects (empty)
    • Registered the objects in the OLAP catalog
    • Binds the OLAP objects to the relational objects
    • BI Beans enabled environment
  • Creates a ROLAP environment
olap deployment16
OWB

Generate

Scripts

OLAP

metadata

DDL

PL/SQL

Bridge

Register

Register

Relational Views

Tables,

Dimensions,

PLSQL etc..

Publish

Deploy

Analytic Workspace

Create

OLAP Deployment

Oracle Database

OLAP catalog metadata

olap data loading19
OLAP Data Loading
  • Load relational objects via a normal mapping
  • Load the OLAP Analytic Workspace
    • Methods:
      • Mapping – post mapping process
      • Process Flow activity
    • Refresh or Insert into Dimensions
    • Refresh or Insert into Cubes
  • Using an OWB wrapper procedure on top of the RDBMS PL/SQL
olap data loading20
Sources

Publish

Insert/Update

Load/Refresh

OLAP Data Loading

Oracle Database

OLAP catalog metadata

Registered

Relational Views

Cubes,

Dimensions,

Tables

Analytic Workspace

step 4 reporting choices
Step 4: Reporting Choices

Currently OLAP access is provided through:

  • BI Beans
    • The Java query components to enable OLAP
    • Enables custom application development with several deployment options
  • Discoverer
    • The Ad-Hoc query tool now utilizing OLAP
    • OWB capable of generating Business Areas for Discoverer
  • SQL
    • Analytic Workspaces can be queried through SQL
  • OLAP Worksheet in OWB
    • Provides visualization during design & build iterations
ongoing maintenance
Ongoing – Maintenance
  • Add new measures
  • Add new dimensions or hierarchies
  • Modify existing hierarchies
  • Add self calculating measures (formulas)
add new stored measures
Add new stored measures
  • Use Analytic Workspace Manager to define the stored measure
  • Run add_stored_measure utility to add to an existing Standard Format (SF) cube
  • Modify SQL views if using SQL queries
  • Re-run AW enablement for BI Beans if using CWM2 metadata
add new dimensions or hierarchies
Add new dimensions or hierarchies
  • Create new hierarchy
  • Add descriptions of hierarcy
  • Populate parent relationship.
  • Run groupingid to set new hierarchy details
  • Run hierheight to set new level details
  • Re-run AW enablement for BI Beans if using CWM2 metadata
modify existing hierarchies
Modify existing hierarchies
  • Make changes to parents, levels, etc. as required.
  • Run groupingid to set new hierarchy details
  • Run hierheight to set new level details
  • Re-run AW enablement for BI Beans if using CWM2 metadata
add self calculating measures formulas
Add self calculating measures (formulas)
  • Use Analytic Workspace Manager to define the formula
  • Run add_cube utility to add to create a new Standard Format (SF) cube
  • Run set_measure_formula_properties utility to add to the new cube
  • Modify SQL views if using SQL queries
  • Re-run AW enablement for BI Beans if using CWM2 metadata
viewing data samples
Viewing Data - Samples
  • BI Beans - Crosstab
  • Drill to Relational Detail (adhoc jtable)
  • Ad Hoc query tool
  • Beanie – Drill to Relational coming
  • Excel
  • Discoverer
  • SQL views – any query tool (olap_table)
summary
Summary
  • Existing Star schema not required but knowledge of data is
  • Basic understanding of OWB necessary
  • OLAP design understanding is critical
  • Knowledge of OLAP structures and functionality mandatory
  • Can use Oracle Workflow to automate updates
next steps
Next Steps….
slide35
Q

&

Q U E S T I O N S

A N S W E R S

A

ad