The big green thingy a case study in data warehousing
Download
1 / 35

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


  • 91 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!)


The big green thingy design phase components
The Big Green Thingy – Design Phase Components




Finally the big green thingy
Finally –The Big Green Thingy


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



Contact information
Contact Information

Allison Lobato

  • [email protected]


ad