INFORMATION FOR...
navigation
Showcase of EDW Banner Applications
- Catalog and Schedule: XML Tie-In
- Catalog and Schedule: Section/Instructor List
- Finance: Operating Ledger Transaction and Summary Report
- HR: Departments and Executive Officers
- Student: UI Online inquiry to registration comparison
- Student: Course Enrollment Query
- Student: graduate student application and student web interfaces
- Student: Tuition Assessment, Waiver, and Appts
Catalog and Schedule XML Tie-In
Data Area
Catalog and Schedule
Tools Used
XML; XSLT
Contact
UIUC Office of Public Affairs
URLs
- Sample: http://www.ed.uiuc.edu/hre/courseapp
- Documentation: http://www.opa.uiuc.edu/webservices/xml/
- Documentation (See references to course catalog): http://www.ed.uiuc.edu/cio/web/edwebs/programming/final.html
- Training: http://opa.uiuc.edu/webservices/
Description
Catalog and Schedule data in xml format is delivered via a url from UIUC Office of Web Services which extracts it from Banner\EDW. Data is parsed and our xslt applied. This route gives us the skin of our College website, the familiar look/feel of the Course Catalog, and up-to-date data with minimal effort on our part
Lessons Learned
A tremendous amount of time was saved by buying into another unit's application and development. Our servers aren't taxed, maintenance and extraction process is supported elsewhere, and our responsibilities are minimized
Banner/EDW Tip
- Get a head start on sql queries by snagging the sql from a report in Business Objects...
Section/Instructor List
Data Area
Catalog and Schedule
Tools Used
ASP; SAS; SQL Server
Contact
UIUC Division of Management Information
URLs
- Sample: https://www-s.dmi.uiuc.edu/sil
Description
Daily downloads of registration snapshot and catalog/schedule data are loaded into our SQL Server. Enrollments and credit hours are summarized across crosslistings. The section and instructor data are then served from a web site which also allows some interactive data entry by authorized users.
EDW Operating Ledger Transaction and Summary Report
Data Area
Finance
Tools Used
MS Access; ASP; VBScript
Contact
UIUC ECE
URLs
- Sample: http://adnetweb.ece.uiuc.edu/edw/transactions/
- Sample: http://adnetweb.ece.uiuc.edu/edw/olsummary/
- Code (Right-click on the following link to download the source for the Transaction report)
http://adnetweb.ece.uiuc.edu/edw/transactions/source.asp
http://adnetweb.ece.uiuc.edu/edw/olsummary/source.txt
Description
Using ODBC to link the EDW to an Access database, principal data tables are downloaded each month into Access. Custom reports are delivered on the web using ASP code and VBScript. Note that the database file locations and passwords are changed in the source for security reasons. There are two main functions in the source. The first pre-processes the data by writing all the transactions into a temporary Access table. The second writes the report using the data from the temporary table. This method of pre-processing the data was adopted because it enables separating the data-retrieval issues from the report-output issues. Also, note that there are custom functions for DLookup and DSum that are available in Access but not in VbScript. It was desireable to keep the code syntax in the ASP version as close as possible to the original Access code in order to make it easy to convert future reports.
Departments and Executive Officers
Data Area
HR
Tools Used
ASP; SAS; SQL Server
Contact
UIUC Division of Management Information
URLs
- Sample: http://www.dmi.uiuc.edu/ddd/
Description
Staff directory information is extracted from the EDW and linked to executive officer information and dept URLs maintained locally. The results are published by department from this web site.
UI Online inquiry to registration comparison
Data Area
Student
Tools Used
ASP; SQL Server; DTS
Contact
UI Online
Description
We needed to know the names of the people who registered for online courses in all 3 campuses and compare them with the inquiries that we receive everyday for the U of I Online, in order to determine our Marketing strategy.
I accessed the EDW Census data through the Oracle ODBC driver. For our specific problem I used SQL DTS to import the tables I needed, and then performed the queries.
I went to the workshop and the DS people helped me construct the query with Business Objects. Finally I used a different query.
This is the query used and it takes about a minute and a half to run:SELECT DISTINCT pers_lname, pers_mname, pers_fname FROM V_RS_PERS_DIR P JOIN t_rs_student_crs_info C on P.edw_pers_id=C.edw_pers_id JOIN t_rs_sect_base S on C.crn=S.crn and C.vpdi_cd=S.vpdi_cd WHERE P.reg_snapshot_desc='Census' AND sched_type_desc='Online'
Lessons Learned
We were happy with the results; it showed us that an increased number of inquirers, who were referred to us by an outside service, enrolled in the online programs.
Banner/EDW Tip
- Make sure you have the right permissions. At one point I had full access to the students' info, including the SS numbers which I didn't really need, but only for the UIUC campus.
- If you are using MS Access, remember that the limit is 1.99 GB. So if you try to import huge tables, it will choke and give you an error message.
GARDS and GRINS: graduate student application and student web interfaces
Data Area
Student
Tools Used
Cold Fusion ; SQL Server ; DTS
Contact
UIUC Grad College - Sally Mikel (smikel@uiuc.edu)
Description
The Graduate College created GARDS and is prototyping another system, GRINS. These are web interfaces to graduate student data for application and registration data. The data is automatically extracted every morning from the EDW to SQL Server using SQL Server's Data Transformation Service. We used Coldfusion to create a simple interface for departmental contacts to access lists of students/applicants. The user can click on a record in the list of students/applicants to see more information about that student. We use bluestem and a seperate user database for authentication.
Lessons Learned
Users appreciate a simple interface. Coldfusion allowed us to build a solution fairly quickly. We plan to continue using this approach with other projects (SQL Server backend and Coldfusion for development of the web interface).
Banner/EDW Tip
- I used Eddie (Webi and 5i) to work with the data prior to extracting the data directly with SQL Queries. It helps with understanding how to join tables (ex. current info indicators).
- If you are using MS Access, remember that the limit is 1.99 GB. So if you try to link huge tables, it will choke and give you an error message.
Tuition Assessment, Waiver, and Appts
Data Area
Student Financial Aid
Tools Used
ASP; SAS; SQL Server
Contact
UIUC Division of Management Information
URLs
- Sample: https://www-s.dmi.uiuc.edu/tw
Description
This web site pulls data from the AR, Financial Aid, HR, and student registration EDW modules and loads a SQL server database with information by student on enrollment, assessment, tuition waivers, and appointments. Individual student data is restricted to authorized users, but summary data is available
Course Enrollment Query Direct against the EDW
Data Area
Student
Tools Used
MS Access; ASP; VBScript
Contact
UIUC ECE
URLs
To see the demo program, right-click on the link to download the file:
http://adnetweb.ece.uiuc.edu/edw/oracle/enroll_demo.txt
After downloading, you need to change the file extension to .asp if you want to run it.
Description
Accessing the EDW from a web page using ASP code
In some instances, it seems desirable for the user to be able to query the EDW directly from a web program. For example, counseling personnel may like an up-to-date list of course enrollments for the fall semester. Wouldn't it be nice if they could just go to a web page and run a program that gets the latest totals for the EDW on the fly? The demo link below shows how to produce an enrollment report.
This technique works best for simple reports where none of the data needs to be stored in tables. These coding techniques are also used when linkedk to the EDW and also to an Access database. Then I can write data from the EDW into Access tables and do further processing before printing the final results. This is sometimes necessary because we can't write data to the EDW so we need to use a second database for storage and manipulation.