Corporate PPT Template

Document Sample
Corporate PPT Template Powered By Docstoc
					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

 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?
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

                         Oracle Database
                                                            OLAP API

        Oracle Call Interface                                JDBC

 Relational Technology           Object Technology            OLAP Technology
     SQL Engine                   Table Functions

                    Relational                      Multidimensional
                      Cubes                              Cubes
    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

 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

 Oracle Warehouse Builder streamlines many
  complex tasks of building OLAP solution

   –   Design & Metadata population
   –   Deployment of OLAP Cubes
   –   Loading of Data
OLAP Design
  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 Design - Dimensions
Metadata Design - Cubes
OLAP Deployment
          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 Deployment

Generate                                Oracle Database

      OLAP       Bridge
                                     OLAP catalog metadata
      PL/SQL                                         Relational Views

                          Tables,                              Publish
       DDL                Dimensions,
                          PLSQL etc..              Analytic Workspace
OLAP Deployment - Bridge
OLAP Data Loading
          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
          OLAP Data Loading

                                        Oracle Database

                                   OLAP catalog metadata

                                                        Relational Views

                          Dimensions,                     Analytic Workspace
          Insert/Update   Tables         Load/Refresh
        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
    –   Analytic Workspaces can be queried through SQL

 OLAP Worksheet in OWB
    –   Provides visualization during design & build iterations
      Ongoing – Maintenance

   Add new measures
   Add new dimensions or hierarchies
   Modify existing hierarchies
   Add self calculating measures (formulas)
  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
   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

 Make changes to parents, levels, etc. as
 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
 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

   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)
BI Beans - Crosstab
Relational Table - jtable
Ad Hoc query tool
Beanie – Oracle Consulting

 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
              Next Steps….

 Interested in leveraging Oracle OLAP
   –   Joseph Rayman –
         443-253-2440
   –   Carey Probst –
         518-584-4388
   –   Larry Anderson –
         973-895-6113
Reminder –
please complete the OracleWorld
online session survey

Thank you.