Transcript Title of Presentation

Delivering Decision Support
Information to Departmental
Units through a Management
Portal
2004 AIR Forum
4:20-5:00 p.m.
June 1, 2004
Presenter
Ryan Cherland, Ph.D.:
Director of University Management Information and
Associate Director of the Office of Institutional
Research and Planning at the University of Kansas. He
has 19 years of experience in institutional research,
with the last 11 years at the University of KansasLawrence.
2
Synopsis of Presentation
•What is DEMIS?
•Development of DEMIS
•The place of DEMIS in KU’s Information Architecture
•How data is gathered
•Staff resources related to DEMIS
•Future directions and goals
•Live Demo of DEMIS
•Questions?
3
About the University of Kansas
• History - The University of Kansas opened its doors in 1866.
• Academics - The university offers more than 100
undergraduate and graduate majors and programs including
allied health, architecture, business, education, engineering,
fine arts, journalism, law, liberal arts and sciences, nursing,
pharmacy, and social welfare.
• Lawrence Campus Enrollment – 19,651
undergraduates and 6,131 graduate students from every state
in the nation and more than 100 countries around the world.
4
What is DEMIS?
Departmental Executive Management
Information System
• Campus Intranet system for the University of
Kansas
• Developed gradually over the past decade
from a few static web pages to a campus
management ‘portal’
5
What is DEMIS?
• Uses a password protected web-site that
allows customization of the links for a user
• 5 broad areas of information from DEMIS:
General, Academic, Student Administration,
HR/Pay, Financial, and User
6
What is DEMIS?
DEMIS as a decision support system, which
can be defined in many ways. The definition
that I think fits DEMIS the best is:
“A decision support system is an interactive
system that provides the user with easy
access to decision models and data in order
to support semistructured and unstructured
decision-making tasks.”
(Watson, Houdeshel, & Rainer, 1997, p.265)
7
What is DEMIS?
Key parts for a decision support system are:
• It is easy to use
• It contains models or analytical aids used to
analyze the data
• Data are maintained to be used in the
analysis
8
Development of DEMIS
• Phase 1: Initial phase was to provide academic
department trend information and major counts on a
password protected web site – 1995
• Phase 2: Second phase was the development of ad
hoc queries on student credit hours because of a
change in business practices – 1996
• Phase 3: Third phase focused on academic
department management information – 1997 to 1998
• Phase 4: Inclusion of academic/nonacademic
departmental reporting from PeopleSoft systems data
– 1999 to present
9
DEMIS in KU’s Information
Architecture
DEMIS is one part of a larger architecture for
data warehousing / information delivery
proposed by the Office of the Vice Provost for
Information Services
Link to KU’s Technical Infrastructure Task Force
Report:
www.vpinfo.ku.edu/warner/itarchitecture/index.shtml
10
KU Enterprise Data Architecture
FSKU
MCHR
KUMC Fin
SAKU
External Data
IPEDS AAU
AMCAS
Non-operational
Data
Sources
Space Inventory
Data Feed /
Data Mining
/Indexing
Layer
Data Mart
Layer
KUFS
KUFS
Detail
SRIS
Legacy
data
CRINC
FS Detail
Other
Subject
Area
Applications
Central
DW
:
Subject
-Areas
Students
- Financial
- Human
Resources
- Facilities /
Equipment
- Research
SA RDS
Spreadsheets Business
Downloads
Analysis Tools
Feed / Mining / Indexing
Process
SRIS
Core DW
Layer
Integration/Consolidation
Process
HRSA
Data Acquisition -- SAS/ OWB / Decision Stream
Layer Data Staging and Quality
Source
Data
Operational Data Layer Operational
Data Stores
Sources
DEMIS /
Web
Interfaces
KUMCFS
CRINCFS
SCH MART
Others
Presentation /
Desktop
Layer
Access
Metadata Repository
Warehouse
Layer
Management
Layer
Application
Messaging Transport
Layer
Knowledge Management Infrastructure Definition Task Force
Adapted from Ken Orr’s Enterprise Data Architecture, 1998
06/2000
11
DEMIS Menu Architecture
User Id
Authentication via
Web server
User web menu
created based
on role at KU
Menu Links
database
User database
User ID Key
User + Menu
database
Menu Code Key
User ID Key +
Menu Key
12
DEMIS Query Architecture
A DEMIS Query
web form
Filters + Rpt
Breaks
User receives
output in HTML,
RTF, PDF, or
Excel-ready
format
CGI
SAS report
program
Functional
Subject Area
Data mart
13
Advantages of DEMIS Reports
•
Replace hard copy reports with web reporting
options
•HTML, Word, PDF, CSV
•
Allows user access to anytime / anywhere
information
•
Provides semi-flexibility in the user creating
the report they want
14
DEMIS Data Sources
•
Selected PeopleSoft (Oracle) tables and
legacy system flat-file extracts and IR
Analytical data marts
•
Data marts
•

Financials datamarts updated twice daily

Payroll and other datamarts updated once daily
Data warehouse

Frequency of updates determined by business
need
15
DEMIS Extraction and Data
Transformation
• Use SAS with Access to Oracle
• Data is “transformed” and manipulated for the
report model.
• Data marts are saved as SAS datasets and
indexed for reporting
• “Power users” use desktop SAS and
SAS/Connect to access the operational daily
stores on the DEMIS Unix Server
16
Staff Resources in Support of
DEMIS
• Total of 6 to 7 FTE
– Department of University Management Information
3 FTE (+1 FTE next fiscal year)
– Office of Institutional Research and Planning – 1
to 1.5 FTE (contributed by multiple analyst) in
support of various analytical systems
– Office of Data Services – 1 FTE in maintenance of
users and interface with production control and
technical support
17
How has DEMIS Impacted KU?
• The Institutional Research Office
– Who am I today???
• The University Overall
18
More IR Offices developing Dual
Personalities??
Institutional Research
•Census extracts of operational
data
•Analytical Skills
•Applies context to data
•Brings data from multiple
systems together
•Creates “value added” data
fields
University Management
Information
•Day-to-day operational data
marts and reports
•Involved in meeting the
business needs of the campus
•Involved in developing a core
group of knowledge workers in
the major functional areas
19
Benefits of the IR Duality
• Depth and knowledge of current and future
operational data systems which is accessed
often in IR “quick-and-clean” analyses
• Invited to meetings which can impact
approaches to the setup of systems
• Acts as a “bridge” between the needs of the
operational systems and the needs of the
decision-makers – we walk and talk in both
worlds
20
DEMIS Benefits to the University
Community
• One version of the truth – with audits
• A one-stop shop for information
• High level of executive support on
campus
– We will use the data from the operational
systems – not from departmental shadow
systems
21
How DEMIS is Guided &
Developed
• Provost
• Directors Group
–
–
–
–
–
Assc Vice Provost
Budget
Comptroller
Human Resources
Institutional Research
• System Administrators
• Student Reporting Team
22
Live DEMIS Demo
23
Coming Systems to DEMIS
• Develop an Academic performance metric
query system – in prototype
• Develop a Grant Overview query – in
prototype
• Deploy a Space Usage collection system – in
prototype
• Develop a “pick-a-peer” comparative analysis
query – in prototype
• Develop a Financial EIS “dashboard”
24
Immediate Technical Goals for DEMIS
/ Decision-making at KU
• Enhance the DEMIS interface with more of a portal
look and feel
• Bring other data server(s) online in support of
administrative decision-making and data collection
needs
• Integration of the ETL and data warehouse meta-data
with the reporting tools and web query systems
• Purchase data-mining/text-mining tools to apply to
university data stores in support of decision-making
and data discovery
25
Contact Information:
Ryan Cherland,
Office of Institutional Research and Planning
University of Kansas
(785) 864-4412
[email protected]
www.ku.edu/~oirp
26
Hardware & Software resources in
support of DEMIS
• Software
– SAS Modules (V8.2)
• SAS/Base; SAS/IntrNet; SAS/MDDB; SAS/Access to
Oracle; SAS/Warehouse Administrator
– Apache Web Server software
• Hardware
– SUN 3500E
– 6 Processors
– SAN Storage (when more is needed it is allocated
within a few days
27
General Subject Area
28
Academic Subject Area
29
Student Admin. Subject Area
30
HR/Payroll Subject Area
31
Financials Subject Area
32
Developer/TS Subject Area
33
My Data Subject Area
34
.
35
DEMIS Query Activity over time
As of 08AUG2003
1,200
1,100
Average Daily Queries
1,000
900
800
700
600
500
400
300
200
100
0
1
2
3
4
5
6
7
2002
8
9
10 11 12
1
2
3
4
2003
5
6
Month
Year
36
DEMIS Average Daily User Activity over time
As of 08AUG2003
110
100
100
90
Average Daily Users
82
80
74
76
74
67
70
60
66
69
69
68
65
79
79
4
5
75
66
64
59
60
50
40
30
20
10
0
1
2
3
4
5
6
7
2002
8
9
10 11 12
1
2
3
2003
6
Month
Year
37
Screen snapshots
• What follows are screen snapshots of
what is anticipated to be shown during
the ‘live’ demo of the presentation for
those who were not able to attend…
38
Academic Program Review Statistical Overview
39
Additional trend Information on various categories
40
Overviews contain links to drill-down
information
41
Detail of Faculty Workload
42
Student Information in the Major
Drill-downs to additional
statistics.
43
General Statistical Analyses on the students in the major
44
A continuation of the statistical analyses the ability to map data
Click on
anchor to map
the
distributions
45
Resulting map
46
Web form to create report selections
Selection of
semesters to
compare
And the
‘census’ point
within
The semester
Filters selections to restrict what’s
included in the query
4 report breaks
available for the query
47
Output of resulting query
48
Classroom management report
Course excluded
from analysis
Because it is crosslisted
Course that
is underenrolled
49
Space Utilization menu
English
department
has 77%
Of the space
it needs
based
On current
resources
50
Drill-downs to Space detail
51
Staffing detail from HRMS PeopleSoft data
52
Opening menu of a user’s DEMIS screen – with General Links
Password protected system allows
customization for user and security
checks and restrictions.
53
Academic / Instructional Links
54
HR/Payroll links
55
Financial Reporting Links
56
Individual User’s area
57
Financial Reports Menu of links
58
Web form for creation and submission of financial report
Date/Time Stamp of
last refresh
Link to an
overview of
the system
Budget year filter can be
entered, otherwise all budget
years are provided for a given
fiscal year
Main campus
analyses has
fiscal years
stored in
separate tables
Organizational Unit drop
down menu
DEPTID values can
also be entered
freehand
Project ID values can be
entered free hand as
well
59
Additional aspects of the financials report form
Fund numbers
can be selected
from a drop
down or
entered directly
The user can
restrict the
analyses to
broad areas of
account types
A beginning
and ending date
for the
financial report
can be entered,
otherwise it is
as-of-today
60
Available report breaks for the DEMIS financial web
reports
Up to 4 nested
report breaks are
allowed, with the
default break at
the budget
category. All 4
break drop-downs
contain the same
options.
61
Financial output using default settings for a given department
62
A report with ‘details’ checked and a beginning date
provided
Organizational
reporting structure
is included in the
report.
Date range
provided is
included in output
Detailed info
include an ID#,
the Source, the
date, and
descriptions of
the transaction
Pending
transactions are
highlighted by
using RED
with the Source
63
An example of using the extract option
Detail on the
transaction is
being
requested
Checkbox for
sending back a
tab-delimited
file instead of
html
User can keep or exclude
subtotals based on their selected
report breaks
Additional
columns of the
organizational
structure can
be requested in
the extract file
64
Results of query as it looks in Excel
65
Gross and Fringe Reporting web form
Date stamp
of last
refresh
(nightly
extract)
Fiscal Year filter
Check box to
summarize across
payroll vouchers
Or select
individual
vouchers to
analyze
Budgetary unit or
area of
responsibility
66
Continuation of Gross & Fringe reporting
options
Filters for sources of
funding
Filters for person
and/or position
and/or job types
67
Report break options
for Gross and Fringe report
Additional columns of salary/fringe
detail can be included as desired
Up to 4
nested
report
breaks
are
available
A tabdelimited
extract
file can
be
requested
instead
of html
output
68
Output of a default Gross and Fringe report
69
A report requesting all the additional columns of detail
All additional salary and fringe columns are selected
‘True’ pay
end date is
the
requested
report
break
70
Report generated from previous selections
‘True’ pay
end dates
are the
grouping
variable
Various salary and fringe columns provided as separate analysis columns
Feedback to
the user on
the number of
total records
involved from
the query
Total time
elapsed to
subset the data
and create the
report
71
Employment Cessation report web form
The user can subset
the data based on
EFFDT ranges or
ACTION_DT
ranges.
Types of staff can be
selected; so that if
someone was just
interested in faculty they
wouldn’t have to look
through all the students,
etc.
72
Output of the Employee Cessation report
Report layout
is as requested
by the HR
department
If no date ranges are
requested then the last
30 days are used.
73
Admissions Reporting
74
Admissions Reporting Form
75
Admissions Sample Output
76