Transcript Slide 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)
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
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
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.
Flow of Data
END USER
(YOU)
WSU ORG
STUDENT
DATA
WAREHOUSE
SRTS
UNAF (addresses)
OARS
Online Phonebook
(email addresses)
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
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
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.
Technical Architecture
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)
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)
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
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
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
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 don’t specify otherwise, you will get ALL (up to
250,000) the records.
• Be careful when combining data from multiple classes
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
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.
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.
How to learn more
• WSU Data Warehouse web page:
http://infotech.wsu.edu/datawarehouse/
• Student Data Warehouse listproc
[email protected]
• Monthly user group meetings
2nd Tuesday of each month (1:30 - 3:30)
• 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
Lab - Exercise 1
• Run the “Geographic Origin” report in
BusinessObjects
• retrieve from Corp Documents
• refresh (for 10th day)
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)
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
Lab - Exercise 4
• Export the data from BusinessObjects and bring
into Excel
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
Lab - Extra Exercise
• Build a query/report chosen by the class