Download
1 / 37

Challenge - PowerPoint PPT Presentation


  • 242 Views
  • Updated 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 'Challenge' - 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 l.jpg

Carey ProbstTechnical Director

Technology Business Unit - OLAP

Oracle Corporation


Slide3 l.jpg

3 Days: Raw Data to OLAPSession: 40206

A Practical Approach for Rapidly Delivering Successful OLAP Solutions


Challenge l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg

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 l.jpg
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 l.jpg

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 l.jpg
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 l.jpg
Ongoing – Maintenance

  • Add new measures

  • Add new dimensions or hierarchies

  • Modify existing hierarchies

  • Add self calculating measures (formulas)


Add new stored measures l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
Next Steps….


Slide35 l.jpg

Q

&

Q U E S T I O N S

A N S W E R S

A


Slide36 l.jpg

Reminder – please complete the OracleWorld online session surveyThank you.


ad