Transcript Using Oracle Designer and Discoverer to Build a Data Warehouse

The Big Green Thingy –
A Case Study in Data
Warehousing
Allison Lobato, DBA
Enterprise Data Warehouse
Department of Technology
Services
Denver Public Schools
Denver, Colorado
Agenda
 DPS and the CIF
 Current Environment Overview
 Staffing
 Hardware Architecture
 Software Architecture
 Design, Development and Deployment Architecture
 Installation and Configuration Notes
 The Big Green Thingy Overview
 Conclusion
 Questions
My Bio
Allison Lobato
Database Administrator – 21 years w/ DPS in
IT Applications Development and DBA
Support
Standard Survey
 Who are you?
 DBAs
 Data Warehouse Designers
 Data Warehouse Architects
 Managers
 Experience
 Warehouse Builder (OWB), Discoverer, Designer, Reports
 Oracle 9iAS or Portal
 Data Warehousing
Less than 1 yr?
1-3 yrs?
Over 3 years?
DPS, Data Warehousing and the CIF
DPS has no shortage of data –
inconsistent, disjointed and disparate
DPS knows value and importance of
getting our hands around the data
Using the Corporate Information Factory
(CIF) conceptual architecture
 Developed by Bill Inmon and Claudia Imhoff
DPS District Information Factory
DPS, Data Warehousing and the CIF
Current Environment
CIF is a long term architecture strategy for DPS
(a.k.a. District Information Factory-DIF)
Pilot mission:
Prove the value to get funding!
Deliver current student profile information
Using an Operational Data Store (ODS)
Student profile subject area
Target audience
1 high school
1 middle school
1 elementary school
1 administrative department
DPS, Data Warehousing and the CIF
Current Environment (continued)
Enterprise Data Warehouse is an unfunded project
Using existing IT resources
Hardware – existing servers and disk space
Staff
3 full-time (re-allocated)
Data warehouse architect (Supervisor)
DBA
ETL programmer
 2 part-time (shared)
DBA & iAS administrator
Hardware Architecture
Three-tiered approach
End User Layer (tier 1)
Workstations
Middle Layer (tier 2)
Web and application services
Database Layer (tier 3)
Database and storage services
Hardware Architecture
The Workstations
For developers’ machines: robust PCs are
a must.
They need lots of memory (>512 Mb)
Fast processors
End users machines: they are easier
Browser capable desktop running a supported
browser version
Macs and PCs
Middle Tier
9iAS – Web and Application Services
Production Server
Dell PowerEdge 2650– 4 Gb Memory
2 – 36 Gb Mirrored Hard Drives
1 – 36 Gb Hot Spare Hard Drive
Windows 2000 Advance Server with SP3
Development Server
Dell PowerEdge 2500 – 4 Gb Memory
2 – 18 Gb Mirrored Hard Drives
Windows 2000 Advance Server with SP3
Database Servers and
Storage Devices
Database Servers
HP’s RP8400 class server
Production ODS (64-bit)
HP’s RP7410 class server
Production repositories & development ODS
HP-UX 11.11
Storage Device
EMC Symmetric 8430
Using less than 150GB currently
Estimated need over 400GB
Software Architecture
 End-to-end Oracle solution (Oracle 9iDS , 9iAS &
RDBMS) on all 3 tiers (workstation, web, database)
 RDBMS
 Designer
 Warehouse Builder (OWB)
 Discoverer
 Reports
 Portal
 Single Sign On (SSO)
 Oracle Internet Directory (OID)
 Enterprise Manager (OEM and WebOEM)
 Workflow (Job scheduling and monitoring)
 Version compatibility was key (and constantly changing)
Oracle Software Tool Versions
Tool Name
Designer
Client
Version
Repository
Version
Database
Version
Server Type
9.0.2.3
9.0.2.90.10
9.2.0.3
HP RP7410
Warehouse Builder
9.0.4.8.21
9.0.4.0.27
9.2.0.3
HP RP7410
Reports
9.0.2.0.1
n/a
n/a
Dell PE2650
9.0.2.53.09
9.0.2.53.09
9.2.0.3
HP RP7410
Enterprise Manager
n/a
*
9.0.1.3.1
Dell PE2650
Portal
n/a
9.0.2.2.22
9.0.1.3.1
Dell PE2650
SSO
n/a
*
9.0.1.3.1
Dell PE2650
OID
n/a
*
9.0.1.3.1
Dell PE2650
Discoverer
Design Phase Tasks
Create logical and physical data models
using Designer
Staging area for source data
Operational Data Store (ODS)
Create transformation routines using OWB
GUI ETL (Extract,Transform,Load) tool
Diagram inputs, outputs, and transformation
routines for moving data into ODS from source
Generates PL/SQL code
Development Phase Tasks
Create the databases
Using OEM or SQL*Plus
Deploy the data models
Using DDL from Designer
Deploy and test the transformations
Using OWB
Development Phase Tasks
Install and configure 9iAS and the Reports
server
Create the access portal/interface
Using Oracle Portal
Create static, parameter driven reports
Using Oracle Reports
1st cut was generated from Designer
Development Phase Tasks
Create the various portlet providers
Using Oracle Portal
Create the Business Areas (End User
Layer)
Using Discoverer Administration
Create the dynamic business intelligence
interface
Using Discoverer End User Edition
(workstation) or Discoverer Plus (web)
Development Phase Tasks
Create the Discoverer public connections
Within 9iAS environment
Using Web OEM
Deploy Discoverer portlets (worksheet &
workbook)
Using Oracle Portal
Develop additional PL/SQL scripts
For automating the data loads
Develop Workflow Process Flows
Using OWB vs. Workflow Builder
Discoverer Portlets
Management Software
OEM (Oracle Enterprise Manager)
Manage the database – storage, users,
collecting stats, etc.
Web OEM
Manages the 9iAS components
Oracle Workflow
Schedules and monitors the ETL mappings
and load routines
Oracle Tool Repositories
REPOPROD database (meta data)
Oracle Designer
Oracle Warehouse Builder
Oracle Enterprise Manager
ODS database
Oracle Discoverer (EUL)
OWB runtime
OWF runtime
Oracle Tool Repositories
iAS database
Oracle Portal
Web OEM
SSO (Single Sign On)
OID (Oracle Internet Directory)
Repository Schema Locations
Installation and Configuration
Workstation notes
Caution – numerous Oracle homes
Memory, memory and more memory
9iAS (all components on the same server)
Required to do the install 3 times
Infrastructure
Applications
Tools (if using the Portal Developer’s Kit-PDK)
Installation and Configuration
9iAS notes (continued)
Infrastructure installation
9i database (IASDB) automatically built
Version 9.0.1.3.1
Application installation
Reports server configuration will fail 1st time
Series of services started after each install
Documentation is “shaky” for all 9iAS installation
procedures
Be prepared to delete and start over
DPS submitted to IOUG a 9iAS Rel 2 Survival Guide for
Windows for publication in SELECT magazine.
Installation and Configuration
Database notes
Always plan carefully
Check for operating system patches first
Test all components (interfaces, db links, backups,
etc…)
If installing under the same OS user-id
All other Oracle databases must be stopped due
to the shared java components
Get a test server (if you can!)
The Big Green Thingy –
Design Phase Components
The Big Green Thingy Development Phase Components
The Big Green Thingy Deployment Phase Components
Finally –
The Big Green Thingy
Conclusion
Complex Setup
Lot of work, research, trial and error
Limited published documentation
Result
The foundation of our architecture is up and running
Integrated, single vendor solution
Will support our efforts to build our District
Information Factory
Hopefully this information will add some clarity
and make life easier when building the
components of your own data warehouse
Contact Information
Allison Lobato
• [email protected]