Fort Hays State University

1
Fort Hays State University

• Data Warehouse
• and
• Decision Support

2
Data Warehouse at FHSU

• Why a Data Warehouse?
• Vision
• Approach
• Database Implementation
• Query Tool
• Queries and Reports
• Development Philosophy

3
Why Data Warehousing?

• Legacy Data Difficult to Query
• Often on Tape and are Pictures in Time (causes
  merging of data to compare different periods)
• Current data is not structured for easy query and
  queries impact the production systems

4
Vision

• All transaction processing system data stored in
  the warehouse
• Separate reporting from transaction processing
  systems
• Implement in a Relational Database.

5
FHSU Approach to Warehouse

• Simple Index Method (no Star or Snowflake Schema)
• Nightly Downloads of Current data
• Automated loads to Warehouse in a.m.
• Trigger file starts load on Server

6
FHSU Approach to Warehouse Cont.

• History data loaded when archived
• Periodic table updates of some mainframe data
  (Transcript file, Test Score, Degree Table)

7
FHSU Approach to Warehouse cont.

• De-normalization of data
• Text delimited files using FTP
• Lower casing of descriptions and names
• Table views are used to limit access by user
  group
• Standard query tool provided

8
FHSU Approach to Warehouse cont.

• Compressed data for download. (one file 1.3 Gig
  characters saved from going over network)
• Validate Dates02/30/1975
• Isdate function in transactSQL
• Mainframe date validate routine --Best at data
  location (with leap year check).

9
Database Implementation

• MS/SQL Server
• SASSCAT---IBM Netfinity 5500 M20 Rack Mounted (1
  Gig RAM), (2-550MHZ Processors Expandable to 4
  Processors), (109.2 Gig Hard Drive)
• NT Operating System

10
Database Implementation

• Truncate tables dont delete rows
• Use trigger file to load tables
• Dont worry about what to index - some indexes
  are obvious the rest use query analysis tool.
• Pre-load tables on some data

11
Query Tool

• Cognos Impromptu
• Client, Server and Web based Product
• Uses catalogs---Table Joins, User friendly table
  and column names, predefined calculations and
  filters
• Pushes query to the server for execution
• Native Drivers--Select Only

12
What is loaded to-date?

• Student History 20th Day and Final back to 1978
• Course History Date 20th Day and Final back to
  1978
• Current Student and Course Data
• Transcript Data

13
What is Loaded to-date? Cont.

• Test Score Data
• Budget data back to fiscal year 1984
• Name Change History
• Payroll Detail Including FHSUs contributions
• Course Transfer

14
What is Loaded to-date? Cont.

• Prospective Student Data
• Student Degree Data

15
Query and Reports

• Standard Reports published to the Web
• Standard Prompted, run at will reports on the Web
• Ad-Hoc query and reports on the client
• Data download in different formats (Spreadsheet,
  etc.)

16
Query and Reports Cont.

• Summary Reports with drill through to detail
• Course and Advisor Rosters with student pictures
• Users can use any query tool that generates SQL
  Statements to access their data (we recommend
  Impromptu)

17
Class Roster with Student Pictures
18
Development Philosophy

• Remember your 7 Ps but No Analysis Paralysis
• Just Do It!
• And Do It Again and Again and..We reloaded some
  table many times

19
Summary

• Keep it simpleload data, clean, load again...
• Compress download data
• De-normalize tables but dont worry about
  itadding additional columns is simple
• Develop Indexes based on usage