The Big Green Thingy

1
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

2
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

3
My Bio

• Allison Lobato
• Database Administrator 21 years w/ DPS in IT
  Applications Development and DBA Support

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

5
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

6
DPS District Information Factory
7
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

8
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

9
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

10
Hardware Architecture
11
The Workstations

• For developers machines robust PCs are a must.
• They need lots of memory (gt512 Mb)
• Fast processors
• End users machines they are easier
• Browser capable desktop running a supported
  browser version
• Macs and PCs

12
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

13
Database Servers and Storage Devices

• Database Servers
• HPs RP8400 class server
• Production ODS (64-bit)
• HPs 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

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

15
Oracle Software Tool Versions
Tool Name Client Version Repository Version Database Version Server Type
Designer 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
Discoverer 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
16
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

17
Development Phase Tasks

• Create the databases
• Using OEM or SQLPlus
• Deploy the data models
• Using DDL from Designer
• Deploy and test the transformations
• Using OWB

18
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

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

20
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

21
Discoverer Portlets
22
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

23
Oracle Tool Repositories

• REPOPROD database (meta data)
• Oracle Designer
• Oracle Warehouse Builder
• Oracle Enterprise Manager
• ODS database
• Oracle Discoverer (EUL)
• OWB runtime
• OWF runtime

24
Oracle Tool Repositories

• iAS database
• Oracle Portal
• Web OEM
• SSO (Single Sign On)
• OID (Oracle Internet Directory)

25
Repository Schema Locations
26
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 Developers Kit-PDK)

27
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.

28
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!)

29
The Big Green Thingy Design Phase Components
30
The Big Green Thingy -Development Phase Components
31
The Big Green Thingy -Deployment Phase Components
32
Finally The Big Green Thingy
33
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

34
Questions?
35
Contact Information

• Allison Lobato
• Allison_Lobato_at_dpsk12.org