Bob the Warehouse Builder meets Don the Data Miner From Creative Bookkeeping to Fortune Telling Luca

1
Bob the Warehouse Builder meets Don the Data
Miner From Creative Bookkeeping to Fortune
Telling Lucas Jellema, AMIS Services
BVOTDUG 2003
2
Agenda
BI Beans
Discoverer
Reports
Oracle 9i Release 2
OLAP API

SPLExecutor
Oracle Enterprise Manager
SQL Engine
OLAP DML
CubeViewer
DBMS_AW
SummaryAdvisor
Query rewrite
OLAP Catalog
OLAP_TABLE
OLAP Engine
OLAP Worksheet
AW Manager
Wizards for Cubes Dimensions
Materialized Views
Analytic (multi-dimensional) Workspaces
Relational Tables
Data Mining
DM4J
3
Strategic Scope

• Enterprise Information Architecture
• Operational Data Store (ODS)
• DataMarts cs. Data Warehouse
• Enterprise Data Model
• External Sources of Data
• Functional Scope/Business Objectives
• Business Sponsors
• Budget and Planning
• Technical Architecture
• Technology Stack Hardware Software

4
Example Scope ODTUGIn Oracle Users and
Conferences

• Functional Scope
• Member registration and administration
• Conference organization
• Business Objectives
• Number of Members and their overall satisfaction
• Profitability of conferences number of
  attendees, added value for members, evaluation
  scores
• Technical Architecture
• Oracle technology stack
• Existing Administrative Systems

5
ODTUG - Data Model
6
Business Intelligence State of Affairs

• Technology
• Oracle 9iR2 with integrated support for OLAP and
  Data Mining, Analytical SQL
• Emerging Industry Standards for OLAP, Data
  Warehousing and Data Mining
• Web architecture
• Oracle tools Warehouse Builder, Discoverer, BI
  Beans and Data Mining Beans
• Post Millennium Bug issues
• Market Movements past the hype

7
From Operational Data to Business Answers
?
8
From Operational Data to Business Answers -
Sources

• Sources
• Operational data in
• (Oracle) Database
• ERP/CRM applications
• WebServices
• Message Queues
• Flat Files

?
Considerations Available/accessible Reliable Inte
rpretation Semantics
9
From Operational Data to Business Answers
Consolidation (ETL)
Consolidation Extract Transform Load ( Enrich,
Refine, Filter, Aggregate)
?
Considerations Frequency Volume/Time-window Mappi
ng/Merging Technology
10
From Operational Data to Business Answers Data
Warehouse
Data Warehouse

• Warehouse Database Objects
• Consolidated data
• OLAP Meta-Data
• pre-calculated data (materialized views)
• Multi-dimensional workspace (AW)

Multidimensional Cubes
Relational Cubes
Considerations Expiration Volume
(growth) Latency Performance!
11
Set-up a Data Warehouse

• Capture Sources
• Files, Applications (SAP R/3), (External) Tables,
  Advanced Queues, WebServices
• (Capture,) Design and Deploy Targets
• Files, Tables, Advanced Queues, Dimensions,
  Cubes, Materialized Views
• Design, Generate, Deploy and Execute Mappings
  (ETL process)
• SQLLoader, SQL/PLSQL, Workflow Process
• Transfer OLAP/BI Meta-data
• Discoverer EUL, OMG Common Warehouse Model,
  Oracle 9i OLAP Server

12
Dimensions

• Data is placed in Dimensional context
• Dimensions make up multidimensional world
• Dimensions determine rollup/drilldown paths
• Dimensions describe data (like look-ups)
• Dimensions are typically hierarchically
  structured from detailed to more aggregated
• Dimensions help set up the slicendice context
• Examples
• Region World Continents Countries Cities
• Time Decade Year Quarter Month Day

13
Dimensions
14
Cubes and Measures

• The facts or measures to be analyzed are related
  to Dimensions at the lowest level
• The measures are in a Cube that is set up by the
  Dimensions
• A Cube can contain multiple measures, for example
  Cost, Price, Discount, Duration of salespitch
• Every set of measures (every fact) lives in a
  single cell in the multidimensional space

15
ODTUG Conference Attendance Cube
16
Cube and Measures (for Conference Attendance)
Geography

• Attendance
• - duration of attendance ( days)
• - overall conference evaluation
• - travel time to conference venue
• - birth date of attendee
• duration of ODTUG membership- years Oracle
  experience

Person
Time
17
Data Warehouse database instance
Tables
18
Analytic Workspace multi-dimensional OLAP Engine

• Used for
• What-if analysis
• Resolve models
• Forecasting (90 methods)
• Distribution (planning, allocation)
• Similar to engine in Express Server
• Integrated in RDBMS
• PL/SQL API, Java API, OLAP Worksheet and Analytic
  Workspace Manager (9.2.0.4)
• Calculation functionality and results can be
  published through OLAP_TABLE table function, e.g.
  in Views

19
Query Methods for accessing Analytic Workspaces
20
Materialized Views Management

• Pre-calculated aggregations
• Automatically managed
• Transparently used (Query Rewrite)
• Along Dimensions
• Summary Advisor in Enterprise Manager

21
Query Rewrite using MV

• Automatic Redirection
• Transparent to end users
• Faster response time
• Use Materialized Views

Select Store, Period, Product, Sum(video_sales)
From video_details Group by Store, Period, Product
Select Store, Period, Product, Sales From
video_summary
Detail Rows
Summary in MaterializedView (select
sum(video_sales.)
22
From Operational Data to Business Answers -
Reporting

• Reporting/OLAP Analysis
• (static) Reports
• (dynamic) Slicendice, drilling rolling
• Integration operational application

?
Considerations Audience Technical skills
end-user Flexibility (desired) Output-format Frequ
ency Performance/Scalability Integration in
processes
23
From Operational Data to Business Answers -
Reporting
Tables/Views/ PLSQL Table Functions
Data Mining
Oracle Forms
HTML Web App
BI Beans
JavaClient
Discoverer
Reports
AW
OLAP API (Java)
Portal
24
Definitions
25
Business Questions some analysis

• Who is asking the questions and therefore needs
  access to answers and to the analysis-application?
  
• Roles, functions, privileges
• What freedom/flexibility need end-users for
  performing analysis?
• How should the analysis fit into existing
  processes?
• Part of the workflow
• How should the analysis of the business questions
  be incorporated into the existing applications?
• Invoke from existing applications?
• Retrieve results into existing applications?

26
Applications choices

• Stand-alone BI-analysis tool, flexibility for the
  end-user as well as some skill-requirements
• Embedded in existing applications or Portal
• Look feel
• Integration with applications
• Part of workflow (results BI fed back into OLTP
  application)
• BI Analysis/report in Batch

27
Applications for OLAP/BI

• Oracle Reports
• Oracle Discoverer
• BI Beans (in JDeveloper)
• HTML, JSP/Servlet
• Java Client
• Embed in Forms or Reports
• OLAP API (Java)
• Oracle DataMining

Metadata
Portal
Query Reporting
BI Components
Real-time Personalization
28
Oracle Discoverer
EUL
Workbook
Data Warehouse
Relational Tables
Workbook
Materialized Views
Workbook
Multidimensional Cubes
Relational Cubes
Workbook
SemanticLayer
End Users
Business Areas
29
Oracle Discoverer

• Business Areas define
• Mappings Table/View gt Folder, Column gt Item
• Derived structures Summaries, Calculations and
  Hierarchies
• Building a BI application using Discoverer
• 80 Business Areas vs 20 End User Reports
  (Workbooks)
• Oracle Discoverer
• Does not use OLAP Catalog or Analytical
  Workspaces (unless through relational views)
  (will do in planned release within 1 year)
• Runs Client/Server and on the Web
• Output to Excel, Oracle Reports, HTML, CSV file
• Does hardly integrate with your own applications
• Only by providing Portlets
• For BI functionality incorporated in your
  applications, use BI Beans (even embeddable in
  Oracle Forms through PJC)

30
BI Beans

• Reusable Components and Wizards for productivity
  for Application Developers
• Integrated with JDeveloper and leveraging the
  OLAP API
• Presentation Beans
• Table, crosstab, graph
• Query Beans
• Publish OLAP queries and manipulations, such as
  Forecasting, Allocation, What-if analyses
• Embed in
• HTML applications based on JSP/Servlet
• Java Client applications
• Oracle Forms applications

31
BI Beans
32
Data Mining

• Valuable information hidden in lots of data
• Need scalable data mining to find all the hidden
  information

33
Enable Business Intelligence Applications

• Retain customers and avoid churn have ODTUG
  members come and return to Conferences
• Profile ODTUG-members and understand behavior
• Maintain and improve satisfaction with ODTUG and
  Conferences
• Reduce attendee acquisition costs focus on most
  likely prospects
• Compile the best-liked program of topics and
  presenters

34
Data Mining Concepts
Corporate Data
Model
Attributes or Fields
FunctionalRelationship
Buy a Buick? 1 Yes, 0 No
Name
Income
Age Has dog?. . .
Records
Y
Jones
30,000
30
1
Y F(X1, X2, , Xm)
N
Smith
55,000
67
1
Y
Lee
23
25,000
0
Predictions
Confidence
N
Rogers
50,000
44
0
1 0 1 0
.85 .74 .93 .65
X
X
X
......
Y
m
1
2
Independent Variables
Dependent Variable
(target value (Y))
35
Oracle Data Mining

• Embedded in Oracle 9i RDBMS
• Note data mining is separate from OLAP mining
  does not use Dimensions an Cubes
• Accessible through Java API
• Also Oracle DataMining Beans in JDeveloper
• Data Mining results can be stored in Relational
  Tables and used from SQL
• Predictions, clustering etc. based on Data Mining
  can easily be embedded in existing applications

36
Data Mining for ODTUG business objectives

• Try to find out who is likely to attend next
  years conference
• Based on data from people who have attended over
  the years
• As well as people who did not attend
• Attributes may include properties such as
• Last Year attendant, Last Year Presenter?, Award
  Winner?, Age, Country, Number of Children,
  Overall evaluation for last years conference?,
  Industry, Years in IT, Active on ODTUG
  Mailinglist?, Job Role
• Data Mining engine will see from historical data
  which attributes apparently influence the
  decision of coming or not and in what way
• This pattern will be applied to current data to
  predict chances for individuals of being likely
  candidates to attend next years conference

37
Example Data Mining Beans in JDeveloper
38
Oracle Financials Analyzer

• OLAP application for Oracle Financials
• Based on Oracle Express Server
• Separate, multi-dimensional database

39
Oracle Enterprise Planning and Budgeting (1.0beta
end of 2003)

• Replaces Oracle Financials Analyzer and Oracle
  Sales Analyzer
• Based on Oracle 9iR2 technology (no more Oracle
  Express)
• Pre-defined Business Report library
• (Traditional) Financial statement budgeting
• Planning driven from key performance indicators
• Planning, budgeting analysis
• Forecasting, What-if scenarios, Model resolution
• Business Process Flow
• Workflow voor planning analysis approvals,
  explanations, alerts

40
Oracle licenses/packaging
Oracle9iDB
Oracle9iDS
Relational
Wrehouse Builder
OLAP
Forms Reports Builder
Data Mining
BI Beans JDeveloper
Transformation
Discoverer Administrator
Oracle Designer
9iR2 (Enterprise Edition) Analytical SQL, OLAP
Server, Data Mining, ETL, External Tables,
Streams, AQ, Summary Wizard Materialized Views,
Workflow Engine
9iASR2 Enterprise Edition Forms Server, Reports
Server, Portal, Discoverer (Web), BI Beans
JDeveloper
9iDS Warehouse Builder, BI Beans JDeveloper,
Forms Builder, Reports Builder, Designer, SCM,
Discoverer Admin Desktop
41
Oracle BI Architecture
BI Beans
Discoverer
Reports
Oracle 9i Release 2
OLAP API

SPLExecutor
Oracle Enterprise Manager
SQL Engine
OLAP DML
CubeViewer
DBMS_AW
SummaryAdvisor
Query rewrite
OLAP Catalog
OLAP_TABLE
OLAP Engine
OLAP Worksheet
AW Manager
Wizards for Cubes Dimensions
Materialized Views
Analytic (multi-dimensional) Workspaces
Relational Tables
Data Mining
DM4J
42
Summary

• Warehouse Builder can be used to
• Design
• Validate
• Generate/Deploy
• Maintain
• a Data Warehouse and the ETL processes to
  populate it
• An existing Data Warehouse can be design
  captured
• Warehouse Builder also provides a lot of useful
  meta-data for actually accessing the Data
  Warehouse
• OLAP Server
• Analytic Workspace
• Discoverer End User Layer
• Etc.

43
Summary (2)

• Warehouse Builder is cornerstone in Oracle
  Warehouse/OLAP/BI toolstack
• Many recent changes
• Getting better fast however, not very stable,
  many small bugs and big changes between 9.0.3 and
  9.0.4
• Integration with OLAP Server not yet complete
  need to-be- released database patch (9.2.0.2B and
  9.2.0.3A or 9.0.2.4)
• Once all bridges are functional, the meta-data
  created once in Warehouse Builder has impact all
  over the place
• ETL design and code generation is quite good
• Database Design is rather crude (compared for
  example to Oracle Designer)

44
Summary (3)

• Warehouse Builder generates very efficient code
  for handling large data volumes in ETL
• Benefits from many very recent Oracle 9i Database
  features for ETL
• Code generation is fast, fully based on graphical
  process design
• For very simple mappings, having to go through
  the entire design is somewhat tedious
  especially with larger number of columns
• For complex mappings, the time saving with
  Warehouse Builder is substantial, especially for
  maintaining the ETL process

45
The End

• For questions send an email to jellema_at_amis.nl
• For more information, scripts and examples go to
  www.amis.nl/technology