Changes made to eCAFE

Changes to eCAFE:

In general, the changes all were made to correct issues that came up when dealing with the community colleges, and the rest were to simplify in areas that appeared to give users trouble.

Staff:
1.) No more “Override Concurrent Courses.” Not a single department used this through all three pilot tests, and it seemed to cause confusion. In the interests of keeping things simple we removed this feature. We can add it back in should it prove necessary.
2.) No more reordering of questions. This was another infrequently-used feature which seemed to cause more harm than good. Instead, questions appear in the order they are defined in eCAFE. There is some logic to the order as defined by eCAFE, and an argument can be made that results would be more consistent across the board if the questions appeared in a standard order.
3.) We switched from a department focus to handling all “organizations” with organizations being defined as campuses, colleges, divisions, departments, and subjects. While Manoa is primary department oriented, other campuses operate at the college (ex: WCC) or campus level (ex: HCC). The department-only model didn’t even work entirely for UH as some needed it to work at the subject level (ex: the ICS department breaks down into ICS, LIS, and CIS, all of which wanted a different question set). The system now allows for any organizational level to be used.
4.) Staff members see all instructors who are teaching a class for their organization, regardless of who the instructor is “employed” by. So, in the event of a crosslisted class, all organizations involved will see the instructor for that class.
5.) Setting the primary department of crosslisted courses. This is an experimental feature. We need a means of knowing which org has priority over a crosslisted course. For example, in the case where History and Women’s Studies have a crosslisted course between them, but WS is the one paying for the instructor, then WS should be considered as primary so the instructor gets the settings defined by that organization. This feature allows the staff member to indicate which organization takes precedence. Note that this feature is not used when all setup is done at the campus level (like HCC).

Instructors:
1.) No more “personally defined questions.” Another little-used question that was rarely used correctly. We found most instructors that used this were just re-writing the same questions that already appeared in the standard list of eCAFE questions.
2.) No more reordering of questions. Same logic as for the staff.
3.) Instructors will get the settings defined by the organization of the course they are teaching, not necessarily whom they are employed by. The previous model of using the department they were paid by caused many problems. For example, roughly 60 percent of FAMR’s instructors are from other departments, and some departments reported borrowing instructors from other departments as a trial before hiring them on. Since those instructors weren’t on their payroll, they didn’t get the settings for those departments.
4.) When there are multiple instructors for a class, we allow them all to have a survey. Previously, one instructor was designated as “Primary” and would be the only one to get a survey. Now, they all have the option to give a survey to the students if they want to. So, for a course with two instructors, the student will be presented with two individual surveys, one for each instructor.
5.) When a course is crosslisted under multiple departments, the survey will contain all questions set by all departments that course is crosslisted under.
6.) The instructors will get an email listing the names of all of the students in each of their classes. We were having a problem with students occasionally be omitted (late registration, or registration through outreach), or appearing when they shouldn’t (late withdrawal). While we updated our database to handle for those issues, they still occurred. So, we’re asking the instructors to look over the list and let us know if there are any issues.

Students:
No significant changes.

New Usage Dates

We changed some of our dates to better synchronize with the paper version, and because some of the data we need isn't available until later in the semester than we anticipated.

Staff: September 8th -> September 26
Instructors: October 8th -> November 7th
Students: November 24th -> December 12th
Results: December 24th
Publish: January 12, 2009

Fall 2008 Usage Dates

Staff: Sept. 1 - Sept. 26 (4 weeks)
Faculty: Oct. 1 - Oct. 31 (4 weeks)
Students: Nov. 24 - Dec. 12 (3 weeks)
Results: Dec. 24
Publish: Jan. 12, 2009

Migration to MySQL from Oracle

Thus far, the road to move from Oracle to MySQL has been a little rocky. We got access to our new MySQL DB about a week ago, and there's a meeting next week with the all the DBAs to discuss administrative issues like access permissions, backup schedules, etc.

Along with that, the DBAs just set up a dblink for us from our eCAFE and ODS Oracle dbs to our MySQL db. The links are supposed to make it easier for us to move the data from one to the other.

In the case of the Oracle eCAFE db, this one holds all the data from our past semesters, which of course needs to be transferred into the new system. The catch is that the db schema has changed dramatically, so it isn't an issue of just transferring data, but translating it to the new schema.

The ODS db is where we get all our data for the upcoming semester, class schedules, enrollment, teaching assignments, etc. Of course, their views don't match our schema in the slightest, so we need to do more translating of data in this case as well.

Until we got the links, I was getting the data by any of the following methods:
1.) Perl scripts that connected to one db, did the translations, and then inserted into the MySQL db. The problem here is that getting perl DBI installed on Max OS X Leopard was a royal pain, and not one I can ask my fellow developers to go through when their plates are already full.
2.) Doing a query in the appropriate Oracle db, exporting the results, running sed commands on the sql file to make changes as needed, and then importing the modified dump file. Works, but takes time, and is rather error prone.

So we got the dblinks and there was much celebrating... At least until I tried to use them in a real-world example. I guess I needed to do more research, but I had visions of just running queries in my oracle ecafe db (ecafeproduction) like

insert into ecafe.person@mysql-remote (username) select username from ecafeproduction.person

This failed on so many levels.
1.) The syntax only works if both databases involved are Oracle. Since one is MySQL, I got "ORA-02025: all tables in the SQL statement must be at the remote database.
Cause: The user's SQL statement references tables from multiple databases." To fix this, I had to run the query in SQL*Plus and change the query format:
copy
from <username>/<password>@<db>
append <username>@mysql-remote("username")
using select username
from ecafeproduction.person;

2.) Once I changed to sqlplus and the copy command, I discovered that any insert or append required that I provide values for ALL the columns in the destination table. Since the tables don't match up, this is a problem. To top it off, I have auto-generated id fields in the destination table which I definitely could not provide values for. The solution to this is a hack, I create a table with the subset of values that I am retrieving from the original db, and then insert into this intermediary table. Then I go into MySQL, and write another insert to copy from the intermediary to the real destination table. Not fun, but it works.

So, thus far the process has been a bit of a trial, but hopefully we'll work all these issues out. At least the transfer from the original eCAFE db to the new one is a one-time deal. I can focus on automating the ODS process later when the development cycle has calmed down.

Status Update

I haven't posted in a while. With the majority of the decisions made, and the project planned out, we're just doing the actual development. Here's the basic schedule we're working off of (Note that all the staff features are already completed):

July:
1.) Implement Instructor features: enable/disable non-mandatory
surveys, clone past surveys, add/remove questions, preview.
2.) Transfer data from past semesters into new database

August:
1.) Testing staff features.
2.) Testing instructor features.
3.) Implementing all student features.
4.) DB updates for next semester.

Sept:
1.) Implement all view results features.
2.) Testing of Student features.
3.) Go live with new version in Fall 2008 to Staff users.

Oct:
1.) Go live to Instructor users.
2.) Testing student features.
3.) Implement published results.

Nov:
1.) Go live to Student users.
2.) Testing results retrieval features.

Dec:
1.) Results go live
2.) Celebrate!

Database Schema V.6

Changes:
- Removed course.department_id since it's redundant w/ subject.dept_id
- Changed section_statistics to survey_statistics and made section an org so the table hold statistics for all levels.
- Removed target_type_id from org_question_set.
- Removed instructor_id from question_category and question_sub_category.

ASUH

I had a meeting with a representative of ASUH today. The gist of the conversation covered privacy concerns and participation rates, with the focus on the latter.

Ideas and new features:
1.) Allow instructors to know who did surveys so they can offer extra credit or other incentives. As long as we make it clear that the responses can never be tied back to the individual, privacy shouldn't be a concern.
2.) Allow instructors to add their own comments in response to what students write on a survey when publishing. This may encourage instructors to publish knowing that they can state why they decline to show a certain response. It may also have the effect of helping students understand where their instructor is coming from and why they do some things the way they do.
3.) During the time period that eCAFE is open to students, add a link to eCAFE on the main UH site and in the portal.

Action items:
1.) Talk to Accounting dept. head regarding their current practice of giving multiple surveys in a class: one for the instructor, one for the department, and one for the college. Discuss if it's better to put all in one survey, or break the questions for each organization out into a survey answered only once by the students under them. ASUH will establish initial contact.
2.) Talk to Marketing or Statistics departments regarding setting up a focus group with students to gather feedback on ideas to increase participation. ASUH will talk to an instructor in the Marketing department to assess interest.