the big green thingy a case study in data warehousing
Download
Skip this Video
Download Presentation
The Big Green Thingy – A Case Study in Data Warehousing

Loading in 2 Seconds...

play fullscreen
1 / 35

The Big Green Thingy – A Case Study in Data Warehousing - PowerPoint PPT Presentation


  • 92 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' The Big Green Thingy – A Case Study in Data Warehousing' - chiara


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
the big green thingy a case study in data warehousing

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
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
My Bio
  • Allison Lobato
    • Database Administrator – 21 years w/ DPS in IT Applications Development and DBA Support
standard survey
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, 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 data warehousing and the cif1
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 cif2
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
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
the workstations
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
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 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
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)
design phase tasks
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
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 tasks1
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 tasks2
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 tasks3
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
management software
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
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 repositories1
Oracle Tool Repositories
  • iAS database
    • Oracle Portal
    • Web OEM
    • SSO (Single Sign On)
    • OID (Oracle Internet Directory)
installation and configuration
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 configuration1
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 configuration2
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!)
conclusion
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
ad