Skip redundant pieces
Information Resources
Information Resources  :  Organizational Units  :  Data Integration

Data Warehouse Architecture and Development

The hardware and software that comprise the Data Warehouse (DW) architecture include:

  1. Two servers
    • A Sun SPARC V210 running Solaris 9 (KMServer)
    • A HP Proliant DL380 running Windows Server 2003 (DIPROD)
    • Two HP Proliant servers running SuSe Linux (DW and DWDB)
  2. Test servers for both KMServer and DIPROD
  3. Oracle 10g (the Oracle relational database)
  4. Oracle 10gAS (the Oracle Application Server)
  5. Oracle developer and end-user software
    • Oracle Internet Developer Suite - includes Oracle Warehouse Builder, Oracle Enterprise Manager, JDeveloper, Oracle Internet Directory
    • Oracle Portal
    • Oracle Discoverer

 The responsibilities associated with the creation and maintenance of the DW includes:

  1. Maintenance of the DW physical environment - KMServer, DIPROD, DW, and DWDB
  2. Maintenance of the Oracle database on KMServer
  3. Maintenance of the Oracle Application Server and it's various components on the DIPROD, DW and DWDB servers
  4. Installation and maintenance of software upgrades and applications (at least quarterly)
  5. Creation of data marts for customer's reporting needs
  6. Working with customers to understand and analyze data requirements
    • Identification and creation of links to the source data
    • Physical and logical design of the reporting environment
    • Development and implementation of procedures for data processing, data cleansing, and text file manipulation
    • Creation of the database tables
    • Providing ad hoc query access to power users
    • Production and maintenance of web-enabled reports that answer data-related questions
    • Training customers to use their reports
  7. Maintenance and enhancement of existing data marts
  8. Maintenance of Oracle Internet Directory, creation of individual accounts and groups, and appropriate data access rights and privileges

 A typical Application Development Life Cycle for developing a Data Mart includes the following steps:

  1. Identify user requirements - What information does the user use now?  What information would the user like to have?  How would the user like the information presented?
  2. Identify data, tables, columns, values, etc. from the source system(s)
  3. Create test staging tables
  4. Test to be sure we have the right data
  5. Create end user layer - this prevents the user from having direct access to the tables, but allows the end user to test the data and run some initial queries
  6. Create local business area - this is the first load of real data into the data mart
  7. Create conditions, calculated items and joins to build the queries needed by the user
  8. Test
  9. Create facts and dimensions
  10. Edit formatting properties such as field labels, etc.
  11. Create hierarchies and summaries needed for drill down details
  12. Test
  13. Set up secure, appropriate access
  14. Test
  15. Deploy data in required format(s) - this could include ad hoc query access, spreadsheets, pre-defined reports, etc., most of which is web-based
  16. Train customer to use the appropriate format of the newly deployed data mart

Contact information

Tania Stastny, Director
tstastny@kumc.edu
(913) 588-7183
3010 Eleanor Taylor
Mailstop 3024
3901 Rainbow Boulevard
Kansas City, KS 66160