OWB 10g Release 2 Codename: Paris

1
Oracle Warehouse Builder (OWB) 10g R2

• Realize the benefits of true End to End
  Metadata Management

2
Presenter

• Nicholas A. Goodman
• Principal BI Consultant and Founder of bayon
  technologies, inc.
• Certified Business Intelligence Professional
• Oracle Certified Professional
• Bayon Technologies, Inc.
• Consulting, Training, Tools

3
Who are you?

• DBA/DW Architect/Developer
• Who uses OWB 10gR1?
• Impact Analysis?
• User Defined Properties?
• Who uses OWB 10gR2?
• Plan to?
• Wait and see?
• Who uses Informatica/DTS/etc?

4
Overview

• Context
• OWB and Metadata
• BI Soup to Nuts
• Pre-Paris vs Paris Metadata
• User Defined Objects (Classes/Objects)
• Next Steps

5
What is OWB?

• Extract / Transform / Load (ETL)
• OWB is a CODE GENERATOR
• PL/SQL, XPDL, CTL files

6
What is OWB?

7
OWB Metadata

• Metadata is central in OWB architecture
• A working solution requires one to
• Build ETL logically
• Build PL/SQL from design model
• Execute actual ETL (runtime)
• Orphan of data warehousing
• Operational and Contextual Benefits

8
Operational Logic AND Code

• BUSINESS LOGIC CAPTURED

9
Operational Impact Analysis

• Whats the possible impact of a change to the
  CATEGORY DATAFILE in my SALES application?

10
Contextual Company XYZ

• You have a Data Mart that produces Sales
  reports
• BUT WHAT DOES IT MEAN!?!?

11
Contextual Lineage

• Does the SALES cube include data from the OE
  Schema?

12
BI Soup to Nuts

• Source (MVC) and DB / Extract Files
• Data Warehouse (staging/DW)
• Data Marts (star schema)
• Reports (configuration, calculations)
• Custom user and shared reports
• BPM Alerts, Dashboards, Closed-Loop

13
XYZ Soup to Nuts

• An Order travels through all these pieces
• Created, validated, transformed, calculated,
  aggregated, summarized, etc.
• Changing something on the JSP in the CSR
  application could affect the Product Mgr Dashboard

14
Metadata Pre-Paris

• Limited to objects that OWB uses
• User Defined Properties (UDP) a start
• Knows only about middle section
• Tables, Views, Sequences, Functions, etc

15
Metadata Pre-Paris
16
Metadata Paris

• Can extend the OWB Repository to include your own
  Classes and Objects
• User Defined Objects
• Class SoftwareXReport
• Objects WidgetXReport, WidgetYReport

17
Metadata Paris

• Note Possible, not out of the box

18
UDOs Implementation

• First Classes
• Create Iconsets
• Create Classes, Associations, Folders
• Second Objects
• Programmatically (OMBPlus/Java)
• OWB Client

19
UDO Classes Step 1

• Create Iconset

20
UDO Classes Step 2

• Create CLASS DEFINITION
• Done in OMBPlus in Single User Mode
• Requires Knowledge of
• First, Second, Third class objects in OWB
  Repository
• Folder Definitions
• Association Definitions
• Non-trivial to say the least

21
UDO Classes Step 2 (syntax)

• NOT exact syntax
• Oracle should provide reference TCL
• Bayon will publish scripts/information when Paris
  is released (maybe an Expert)

OMBDEFINE MODULE CLASS_DEFINITION
MY_MODULEOMBDEFINE FIRST_CLASS_OBJECT
CLASS_DEFINITION EXCEL ...OMBREDEFINE
CLASS_DEFINITION MY_MODULE ADD CHILD_TYPE
EXCELOMBDEFINE SECOND_CLASS_OBJECT
CLASS_DEFINITION WORKSHEETOMBREDEFINE
CLASS_DEFINITION EXCEL ADD CHILD_TYPE
WORKSHEETOMBDEFINE ASSOCIATION_DEFINITION ...
22
UDO Objects Programmatically
23
UDO Objects OMBPlus

• Swiss Army Knife of OWB
• TCL scripting, easy access to OWB Rep
• Simple pseudo code
• For x in file with values
• create/update JSP objects (values)
• Java for more advanced needs

24
UDO Objects Programmatically
OWBREPOSITORY
25
UDO Objects Manually
26
Other Noteworthy Items

• Exp/Imp/Snapshots/etc work
• Public Metadata Views
• ALL_IV_GENERIC_ASSOCIATIONS
• ALL_IV_GENERIC_FCOS
• ALL_IV_GENERIC_FOLDERS
• ALL_IV_GENERIC_MODULES
• ALL_IV_GENERIC_SCOS
• Use UD_ so OWB can identify USER DEFINED Classes

27
Where to go for more OWB

• http//www.bayontechnologies.com (Our Work)
• EMAIL MEngoodman_at_bayontechnologies.com
• OTN OWB Page
• http//rittman.net
• Expected release CY 2006

28
Open Discussion

• Discoverer Product to keep the two in sync?
• Can we automate the Lineage graphs from OWB to
  HTML help site for reporting app?
• Can we help those developers keep their data in
  order?
• Provide impact analysis as a service to other
  departments?
• Can experts be used to simplify UDO maintenance?