Student Data Warehouse

1
Some day, on the corporate balance sheet, there
will be an entry which reads "Information" for
in most cases, the information is more valuable
than the hardware which processes it. - Grace
Murray Hopper
Student Data Warehouse Training (last updated
12/15/2010)
2
Training Schedule

• Part 1 - Lecture demo
• Data Warehouse Background Overview
• Query Tools Architecture
• Terms and Definitions
• Student Data Overview
• Using Corporate Documents
• Building a Query and Report in BusinessObjects
• Part 2 - Hands-on in lab
• work through 5 or 6 exercises in the lab

3
Background

• What is it?
• Central warehouse for student, course, enrolment
  data
• Does not replace existing systems
• Unlimited report building capabilities
• How did we get here?
• Student data warehouse completed in 1998
• Started capturing data in Fall 1997
• Entire/natural connection downloading

4
Data Warehouse Overview

• Data is organized for ad-hoc, reporting access
  not transaction processing
• Snapshots at various points in time
• Easy access with query tools
• Retention of additional historical data
• Students enrolled from Fall 1997 onward
• All of the WSU transcripts for them (even prior
  to 1997)
• Does not replace existing systems, but provides
  another way to access the data from them.

5
Flow of Data
END USER (YOU)
WSU ORG
STUDENT DATA WAREHOUSE
SRTS
UNAF (addresses)
OARS
Online Phonebook (email addresses)
6
Sources of Student Data

• OARS - Student Records System
• Student academic, demographic data
• Traditional enrollments and DDP (EUS)
• Course/Section data
• Other supporting data
• SRTS - Transcript System
• Student Transcripts - gpas, credits
• Student Transcript Details - courses, grades

7
Other Sources of Data

• Online Phonebook
• student email addresses (email forwarding)
• UNAF - Universal Name and Address File
• mailing, home, parents and workplace
• WSU ORG
• supervising org units and colleges for majors

8
Query Tool Options

• BusinessObjects
• Complete query and reporting tool
• InfoBurst
• Scheduler for pre-defined reports
• May only be used with BusinessObjects
• BusinessQuery
• Add-in for Microsoft Excel
• (being phased out)
• InfoView
• Allows pre-built reports to be refreshed via the
  Web, but cannot build new reports.

9
Technical Architecture
10
Terms and Definitions

• Universe
• a collection of information (e.g. a warehouse) A
  semantic layer between you and the database that
  adds descriptive information, security and
  enables smart queries.
• Class
• a set of related objects. Classes can have
  sub-classes to further group objects together.
  (icon is a folder)

11
Terms and Definitions

• Object - a data item or formula
• dimension - a data object (icon is a blue cube)
• measure - an object that is numeric and can be
  used in a calculation or is the result of a
  calculation (icon is a pink sphere)
• detail - a qualification of another object,
  provides more detail on another object (icon is a
  green pyramid)
• Pre-defined Condition - a special kind of object
  that helps to limit or filter the amount of data
  returned (icon is a yellow funnel)

12
Terms and Definitions

• Document
• a BusinessObjects file that acts as a container
  for reports (.rep extension)
• BusinessObjects documents are composed of 3
  items
• Data Provider - the query that retrieves data for
  reports.
• Report - the formatted results of the query
• Data - the raw data that was returned from the
  data provider (database) and displayed on the
  report

13
Student Data Overview

• Build sample query
• A brief walk- through of each class of data
  (folder) in BusinessObjects
• Describe each class
• Highlight key objects and pre-defined conditions
  (filters)
• Things to watch for in each class

14
Using Corporate Documents

• Example of using pre-built BusinessObjects report
• retrieve Geographic Origin from Corporate
  Documents (run for 10th day)
• Talk about the rest of the Corp Documents

15
Building your own Report

• Select result objects - these are what columns of
  data will be returned from the database and
  displayed in your report.
• Build conditions - these put limitations on the
  number of records that are returned from the
  database.
• Use pre-defined or your own conditions as much as
  possible.
• If you dont specify otherwise, you will get ALL
  (up to 250,000) the records.
• Be careful when combining data from multiple
  classes

16
Formatting the Report

• Set as Master creates a master/detail report
• Can also add breaks, totals, sorts, etc.
• How to Slice and Dice the data

17
Tips Techniques

• If using more than one snapshot class, be sure
  to specify the snapshot generation on EVERY class
• Use Student \ Latest Transcript Summary for most
  queries rather than Student Transcript
• ONLY use Supporting Data when a list of codes is
  required.

18
More Tips Techniques

• Build the query to get the data you want first,
  then format the report.
• Start with a small set of data, then add to it.
• Look at each object to make sure you have put
  appropriate conditions on each class used.
• Conditions are what limit the amount of data
  returned use them as much as possible.
• Notice the UPPER CASE objects they are the
  unique identifiers for each class.
• Watch out for duplicate rows aggregation in
  BusinessObjects.

19
How to learn more

• WSU Data Warehouse web page
  http//infotech.wsu.edu/datawarehouse/
• Student Data Warehouse listproc
• data_warehouse_at_listproc.wsu.edu
• Monthly user group meetings
• 2nd Tuesday of each month (130 - 330)
• Online guides and tutorials (installed with
  software)
• BusinessObjects Getting Started
• BusinessObjects Quick Tour
• BusinessObjects User Guide
• BusinessObjects Computer Based Training CD
• Can be purchased directly from InfoSol
• See Links section on data warehouse web page

20
Lab - Exercise 1

• Run the Geographic Origin report in
  BusinessObjects
• retrieve from Corp Documents
• refresh (for 10th day)

21
Lab - Exercise 2

• Build a Student List report
• select students for a particular college/dept
• include name and major
• Modify to also include cumulative gpa
• (save for Exercise 5)

22
Lab - Exercise 3

• Return student name, and email address for a list
  of WSU numbers numbers in Excel
• Build 2 queries in BusinessObjects
• 1st against spreadsheet
• 2nd against warehouse using results of 1st

23
Lab - Exercise 4

• Export the data from BusinessObjects and bring
  into Excel

24
Lab - Exercise 5

• Reformat report Student List (created in Exercise
  2)
• add class standing code
• make a master-detail report with class standing
  code as master
• create a total count and gpa for each class code
• break on major within each class code
• clean-up title, page breaks, etc.
• insert Special Fields feature

25
Lab - Extra Exercise

• Build a query/report chosen by the class