University of Oregon

Student DWHS Archive of User Group Meetings


February 9th, 1999

I. US Citizenship Flag

There was a good discussion of the US Citizen Flag data following the recent discovery that the data is not reliable. Currently, the value contained in this field is based on whether a record for a given person exists in the BANNER table SPRINTL (via the form SPAINTL). If NO record exists, then the warehouse (incorrectly) assumes the person is a US citizen and stores a "Y" in the appropriate warehouse table. Various ideas were debated, however it was left that the Registrars office will invesitgate the matter, a plan will be proposed, and that proposal will be posted to the sisdwhs list for user feedback.

Until then, note that you can not absolutely rely on the US Citizenship Flag data.

II. 4th Week Freeze

The question was raised whether we need to suspend the refresh (ie "freeze the data") of the warehouse each term or just Fall Term in the 4th week. Some time ago we decided to discontinue the 4th week freeze during the summer terms. Those present felt that Fall Term was the only time when the refresh should be suspended in the 4th week. A notice will be sent to the user list for feedback.


A question was raised about TOFEL scores. Currently, the SDW only lists paper-based scores, though BANNER contains both paper- and computer-based scores. The SDW will be modified to contain both types, and users will be notified of that change when it is in place.


A security concern exists with CASWEB and until the problem is solved, the Registrar's Office will not endorse or encourage the use of CASWEB. It is believed that the CASWEB webmaster is currently working on resolving the problem.

V. Queries

  • A set of SDW queries is now available (both for the PC and Mac) for installation on your desktop computer.
  • A demo of how you can calculate a persons GPA for just classes from their Major was given.

January 19, 1999

I. Refresh

  • Why does the refresh fail and how can I know the details?

Failure of the SDW refresh can occur for many reasons. Typically the refresh does not fail "catastrophically", that is to say, only a portion of the refresh may fail, and even then, the failure may not impact data of immediate interest to you. When a problem in the refresh occurs, the Computing Center staff who manage the SDW will post a message to the list informing you of relevant details such as what data was affected. Beyond this, it is not presently possible to provide any information about the problem.

The most common reason the refresh stumbles is that the connection to the database "times out" or is otherwise disconnected, either temporally or, in more serious instances, permanently. Such disconnections may be the result of intensive database processes (unrelated to the SDW processes) which keep the database engine busy juggling its' resources. Realize that a single Oracle database engine is constantly processing all Banner jobs as well as other processes such as the SDW jobs and nightly backups. In some cases, a network error is responsible for the lost connection to the database. Furthermore, in other cases, the server upon which the database engine is running may experience problems. The net result of these (and other) possibilities is that the SDW refresh may be unsuccessful.

II. Query Questions

A. Labels

Some problems have been encountered on PCs when trying to select Avery style 5160/5260 - GQL will not produce the selected style. At this point in time we have no solution to this problem. It appears to be related what GQL determines will with in the printable area of the page. If the selected label format will not fit, then GQL will default the label selection to a "Custom Label" definition.  

III. Model Changes

A. Person Object - E-mail Addresses

1. The refresh now will include the e-mail addresses for Law School students

2. There was some debate as to whether e-mail address data should be modified in the SDW. Specifically, some experience difficulty with the "piped" e-mail data. Currently, if a student has more than one valid e-mail address, then the multiple addresses are concatenated with a "|" symbol separating each address. Users must some how decompose the data outside of GQL to make use of them in their emailings. Users commented that e-mail addresses are becoming more commonly used in the daily operations. Various options were discussed including: a) add a new E-mail Object (unlikely); b), add additional fields to hold individual addresses (possible); or purge the existing data and populate the existing e-mail field with only one values address (possible). Additional input from the user community is needed before a design change can be made.

B. Forthcoming changes

We have been wanting to include in the distributed Student model a set of queries which have been optimized and paramertized in such as way as to make them useful to a broad group of the SDW user population. At this point, we have the some queries in mind, but if you feel that some are missing, please let us know. We are also planning to reinstitute the intuitive Validation Table descriptions instead of the actual Banner table names which were included in the last distribution of the Student model.

"When will the new model be distributed?" you ask. I would expect that with in the next month or so....


I. User questions

  • How can I identify Post-Baccalaureate students in my queries?

In order to select a population of just Post-Baccalaureate students, qualify the Student Object's Student_Type_Code to be 'P'. You may query the Student Type Validation Table (STVSTYP) for other types of possible Student type codes. You may qualify Student_Level_Code = 'UG' to identify Post-Baccs who are not graduate level.

  • After I execute the query and create a report, the report columns do not show repetitive data, such as the student name. Why is this and how can I see all of the data, regardless of whether it is redundant?

While viewing the report, click on column in which you want to see (or not see) redundant data; this "selects" that column and you should see a solid-lined box outlining the selected column. Then click on the Report menu item and click on the "Suppress Duplicates" list item. This will toggle the current setting for the selected column. If suppression in on, a check mark will be displayed in the Report menu list.

  • How do I identify only transfer students?

If you query the Student Type Code validation table (STVSTYP) you will see that various transfer students are identified with values of 3,4,5,6,7,8, 9 and T. If, in your query, you qualify the Student Object field Student_Type_Code to = or Contains the one or more values of interest, you will limit the retrieved data to transfer students only. Furthermore, you may also qualify on the Student Object TERM (or Admit_Term) field, specifying the current term (at this writing 199801) to identify students who are new at that time.

  • Who can I identify people who have 2-year degrees?

If you query the Degree Code Validation Table (STVDEGC) (sort by Code to make it easy to read) you will see that the codes "AA" and "AABT" relate to Associate degrees. In your query, qualify the Degree Objects Degree Code to match one or both of these codes.

II. Banner GUI and the Student Data Warehouse (SDW) software

If you have installed the SDW software prior to the installation of the Banner 2000 GUI software, you do not need to reinstall the SDW software. This is true for both Macintosh and PC operating systems.

III. SDW User Group Meeting Times

Many users find the current meeting time inconvenient or in conflict with other commitments and consequently can not attend. In the discussion about meeting times, individuals voiced differing opinions about meeting time and duration. All persons present liked the fact the the meetings are more frequent, so we will continue to meet at least once a month. An effort will be made to modify the current meeting schedule to enhance attendance and user participation.

In addition to meeting times, meeting content was also discussed. In general it was agreed that a very functional meeting format would combine a) presentation of new or advanced techniques with b) time devoted to addressing "in the moment" user questions.

Organizers of the meetings are focusing on both these issues, schedule and content, so that the utility of the SDW may be enhanced for the users.

IV. SDW Student Model

Computing Center staff will be focusing some attention in the future to revise the current Student model you use with the GQL software. The timing of when these modification will be applied and made available is not known currently, however, we expect to deliver the changes all at the same time so users need download the new model only once. The current changes include:

  1. Renaming the Validation Tables so that the table name (currently used) is replaced by a brief description
  2. Incorporating some commonly used queries in the model for ease of use and guarantee of design.


In an Access database, create a new table by linking the our_students file generated with GQL. Click on the Tables tab and then select New. On the New Table window select Link Table and click on Ok.Navigate to the location where you saved the GQL file (probably c:\gql\dwhs\users) and enter the file name (our_students.qrd) in the File Name field. You must remember here to tell Access the file type is a Text File! To do this you select Text Files from the list of File Types. If all has been done correctly, then you can click on the Link button and Access will begin the process of linking the new table to the GQL-generated file. Access will display the Link Text Wizard window. The GQL file is a TAB delimited file and some of the file contents will be displayed for you. Click on the Next button you should see that Access has figured out how to separate the data. The first row, however, contains the names of the data columns, so you want to check "First Row Contains Field Names" so that Access will not consider this first row as actual data. Once you've completed this, you can click on Finish and Access will complete the linking.

At this point, you could rerun the GQL query "our_students", perhaps this time for a different department, save the Results to the "our_students" file, and the Access link would reflect this change. Note, however, that you might have to close the view of the table in Access before GQL can successfully write to the file or before the new results are reflected in Access.

From here the demo explored how one could generate different letters to students of different Class Level and also make use to the Access Label generation wizard.

  • The data warehouse Current Term now is 199801 (Current Semester = 199805).
  • Remind everyone that during the 4th week of the term the warehouse refresh process will be suspended from that 4th-week Friday through the following Wednesday. The refresh will resume Wednesday night of the 5th week of the term.
  • Reviewed label generation.
  • Demo of Microsoft Access and GQL. The demo details are too much for here, but basically what was shown is how one can define a common query in GQL, run the query when you need to, and save the results to a specific file. That file and then be linked to Access and various sorts of additional reporting capabilities are then available to you. An example presented included Name, address, and Class Level information for a specific department. This data was saved in GQL (Results -> Save Results) to a file (our_students.qrd). An important thing to remember here is that, in GQL, before saving the data to the file you need to set in the Result Options:
    1. The only Option checked should be Save Column Headings
    2. the Field Separator should be TAB
    3. the Record Separator should be <CR><LF>, and
    4. the End of File should be <None>.

    Also, you will want to remember where you saved the file (this location is typically c:\gql\dwhs\users). Another good idea would be to save the query (Query->Save Query) so that next time you want to refresh the file with up-to-date data, you will be able to do so easily. To facilitate this, it's a good retain the same base name as you used in the file ("our_students"). The next step involves Access.

    The following discussion assumes a working knowledge of the Microsoft product Access (version 7.0) .Note that other versions of Access may differ somewhat in procedure and text, but the linking capability should be available.

February 3rd, 1998

  • Page-breaks in GLQ reports: Pete Bauer mentioned that someone wanted to know how to insert page-breaks into a report so that the data would break nicely at a group level. For example, in a departmental report listing student names in all courses offered for a specific term, it would be nice to have a new page start with a new CRN. It is not clear at this time if this feature is possible.
  • Past Student Object: When using the Past Students Object, it is generally desirable to qualify on TERM CODE in that object since a given student will have repeating records. Just because you have specified a TERM CODE qualifier in some other object in a linked query does not mean that records selected from the Past Students Object will be qualified too.
  • All Students Object: To identify "currently registered students", qualify that the Hours Registered data be greater than 0. Some discussion followed about the difference between how the Registrar counts registered students and how a department might. Contact Pete Bauer if you have questions.
  • Use of the Student Data Warehouse data: Pete reminded users, and asked that they remind other users, that SDW data is confidential and should be guarded carefully. Students assisting Peer Advisors are not to have access to sensitive data. If you integrate SDW data into other applications, such as spreadsheets or databases, you must ensure these data are secure.
  • User queries: If you have a GQL query which you have saved, it is saved in the Dwhs\Users\Queries folder. If some one has given you a GQL query which they saved, you must put that file into this folder to enable it's selection via GQL.

June 24th, 1998

  • Modifications to Web Page: The web page now contains a listing of step-by-step procedures for constructing some commonly used queries. This listing is currently available from the User Notes page.
  • Bad e-mail data: A user mentioned that there are many incorrect e-mail addresses stored in the PERSON object and wondered why. The Computing Center personnel will look into the issue and correct the problem.
  • Refresher Classes: People echoed a common sentiment that a Student Data Warehouse refresher course would be nice. This refresher course would not repeat the basic information obtained during the initial training, but would review the GQL data models and delve deeper into constructing complicated queries and how to best optimize queries. Pete Bauer will look into the possibility of offering such a class.
  • Validation Table names in GQL model: In the last major modification to the GQL data model an misunderstanding led to the renaming of the individual objects in the Validation Tables window. Previously, a brief description of the individual table was used to identify a given object. Now, the BANNER table name is listed and users find it confusing and cryptic. The current model will remain intact until the BANNER 3.0 conversion is complete, at which time the Computing Center staff will be able to refocus on any additional modifications to the GQL model.