The Big Green Thingy – A Case Study in Data Warehousing

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 (>512 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 • 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

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

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 SQL*Plus • 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 Developer’s 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@dpsk12.org