Corporate PPT Template

1
(No Transcript)
2
Carey ProbstTechnical Director Technology
Business Unit - OLAP Oracle Corporation
3
3 Days Raw Data to OLAPSession 40206 A
Practical Approach for Rapidly Delivering
Successful OLAP Solutions
4
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

5
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

6
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
7
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

8
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

9
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

10
OLAP Design
11
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..

12
OLAP Design - Dimensions
13
Metadata Design - Cubes
14
OLAP Deployment
15
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

16
OLAP Deployment
Oracle Database
OLAP catalog metadata
17
OLAP Deployment - Bridge
18
OLAP Deployment Bridge
Collection Name - Collection to export OWB
Translated Language - MLS Language Deploy to
AW - Do you want to create an AW definition AW
Name - Name for the AW Generate View
Definitions - Do you want to generate views for
this AW Generated View Prefix - Prefix for the
views Access Type - OLAPI, DISCO (currently
ignored) Generated View Directory - Directory on
server for generated view script Deploy PLSQL in
Database - Do you want to deploy the PLSQL in
the db? Username - Password - Hostname -
Port - SID - PLSQL Output
File - Resultant PLSQL generated Log
Level - Information / Trace / Error
19
OLAP Metadata - OEM
20
OLAP Data Loading
21
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

22
OLAP Data Loading
Oracle Database
OLAP catalog metadata
Registered
Relational Views
Cubes, Dimensions, Tables
Analytic Workspace
23
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

24
Ongoing Maintenance

• Add new measures
• Add new dimensions or hierarchies
• Modify existing hierarchies
• Add self calculating measures (formulas)

25
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

26
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

27
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

28
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

29
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)

30
BI Beans - Crosstab
31
Relational Table - jtable
32
Ad Hoc query tool
33
Beanie Oracle Consulting
34
Excel
35
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

36
Next Steps.

• Interested in leveraging Oracle OLAP
• Joseph Rayman joseph.rayman_at_oracle.com
• 443-253-2440
• Carey Probst carey.probst_at_oracle.com
• 518-584-4388
• Larry Anderson larry.anderson_at_oracle.com
• 973-895-6113

37
A
38
Reminder please complete the OracleWorld
online session surveyThank you.
39
(No Transcript)