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
Wednesday, July 30, 2008
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.
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!
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!
Wednesday, March 19, 2008
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.
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.
Email #2 to instructors
The following emails were sent on Wednesday March 12th. The first one was sent to instructors who are required to give the survey. The second was sent to instructors who had the option to give or not give the survey. The departments determined who is optional and who is mandatory.
These emails were the second of those sent to instructors, with the first one being forwarded to them through their department representative.
-----------------------
Email sent to mandatory instructors:
The new online Course And Faculty Evaluation System (eCAFE) is open for use. Please log in to eCAFE at http://www.hawaii.edu/ecafe using your UH username and password to set up your surveys.
Setting up your surveys involves adding your own questions to those already selected by your campus and department. If you have set up a survey in the past, you can choose to copy those questions into a current survey and edit as desired.
Your students will start using the system from April 21st. Prior to this date, you will receive an email providing instructions on the eCAFE system which you should forward to your students.
You can also indicate if you want to publish your results at the end of the semester. Publishing your results means that the aggregate results will be displayed through a link on http://www.hawaii.edu/ecafe for all to see. You are not required to publish, and if you decide to publish, you will have the ability to decline showing individual open-ended responses. You can also revoke your decision at any time.
Why would you want to announce that you intend to publish? Because your students will be told of your intent, which gives them an incentive to fill out the survey. This will increase the number of surveys completed, and provide an alternative to the now popular http://www.ratemyprofessors.com, sans hotness ratings.
You have until Friday, April 4th to make your changes. After the 4th, the system will close and you will not be able to make any further changes.
Usage of eCAFE should not be combined with using the paper-based surveys. If you receive the application for a paper survey, please let us know asap by email or phone ().
As this is a pilot test, any and all feedback is greatly appreciated. Please feel free to email us at cafe-support-l@hawaii.edu.
--------------------
Email sent to non-mandatory (optional) instructors:
The new online Course And Faculty Evaluation System (eCAFE) is open for use. Please log in to eCAFE at http://www.hawaii.edu/ecafe using your UH username and password to set up your surveys.
Your department has specified that your participation in the eCAFE pilot test is voluntary. If you don't want to participate, you must log in to http://www.hawaii.edu/ecafe and click the Disable button next to the survey(s). If you choose to Disable, no further notices will be sent to you or your students, and you can ignore the rest of this email. Otherwise...
Setting up your surveys involves adding your own questions to those already selected by your campus and department. If you have set up a survey in the past, you can choose to copy those questions into a current survey and edit as desired.
Your students will start using the system from April 21st. Prior to this date, you will receive an email providing instructions on the eCAFE system which you should forward to your students.
You can also indicate if you want to publish your results at the end of the semester. Publishing your results means that the aggregate results will be displayed through a link on http://www.hawaii.edu/ecafe for all to see. You are not required to publish, and if you decide to publish, you will have the ability to decline showing individual open-ended responses. You can also revoke your decision at any time.
Why would you want to announce that you intend to publish? Because your students will be told of your intent, which gives them an incentive to fill out the survey. This will increase the number of surveys completed, and provide an alternative to the now popular http://www.ratemyprofessors.com, sans hotness ratings.
You have until Friday, April 4th to make your changes. After the 4th, the system will close and you will not be able to make any further changes.
Usage of eCAFE should not be combined with using the paper-based surveys. If you receive the application for a paper survey, please let us know asap by email or phone ().
As this is a pilot test, any and all feedback is greatly appreciated. Please feel free to email us at cafe-support-l@hawaii.edu.
These emails were the second of those sent to instructors, with the first one being forwarded to them through their department representative.
-----------------------
Email sent to mandatory instructors:
The new online Course And Faculty Evaluation System (eCAFE) is open for use. Please log in to eCAFE at http://www.hawaii.edu/ecafe using your UH username and password to set up your surveys.
Setting up your surveys involves adding your own questions to those already selected by your campus and department. If you have set up a survey in the past, you can choose to copy those questions into a current survey and edit as desired.
Your students will start using the system from April 21st. Prior to this date, you will receive an email providing instructions on the eCAFE system which you should forward to your students.
You can also indicate if you want to publish your results at the end of the semester. Publishing your results means that the aggregate results will be displayed through a link on http://www.hawaii.edu/ecafe for all to see. You are not required to publish, and if you decide to publish, you will have the ability to decline showing individual open-ended responses. You can also revoke your decision at any time.
Why would you want to announce that you intend to publish? Because your students will be told of your intent, which gives them an incentive to fill out the survey. This will increase the number of surveys completed, and provide an alternative to the now popular http://www.ratemyprofessors.com, sans hotness ratings.
You have until Friday, April 4th to make your changes. After the 4th, the system will close and you will not be able to make any further changes.
Usage of eCAFE should not be combined with using the paper-based surveys. If you receive the application for a paper survey, please let us know asap by email or phone (
As this is a pilot test, any and all feedback is greatly appreciated. Please feel free to email us at cafe-support-l@hawaii.edu.
--------------------
Email sent to non-mandatory (optional) instructors:
The new online Course And Faculty Evaluation System (eCAFE) is open for use. Please log in to eCAFE at http://www.hawaii.edu/ecafe using your UH username and password to set up your surveys.
Your department has specified that your participation in the eCAFE pilot test is voluntary. If you don't want to participate, you must log in to http://www.hawaii.edu/ecafe and click the Disable button next to the survey(s). If you choose to Disable, no further notices will be sent to you or your students, and you can ignore the rest of this email. Otherwise...
Setting up your surveys involves adding your own questions to those already selected by your campus and department. If you have set up a survey in the past, you can choose to copy those questions into a current survey and edit as desired.
Your students will start using the system from April 21st. Prior to this date, you will receive an email providing instructions on the eCAFE system which you should forward to your students.
You can also indicate if you want to publish your results at the end of the semester. Publishing your results means that the aggregate results will be displayed through a link on http://www.hawaii.edu/ecafe for all to see. You are not required to publish, and if you decide to publish, you will have the ability to decline showing individual open-ended responses. You can also revoke your decision at any time.
Why would you want to announce that you intend to publish? Because your students will be told of your intent, which gives them an incentive to fill out the survey. This will increase the number of surveys completed, and provide an alternative to the now popular http://www.ratemyprofessors.com, sans hotness ratings.
You have until Friday, April 4th to make your changes. After the 4th, the system will close and you will not be able to make any further changes.
Usage of eCAFE should not be combined with using the paper-based surveys. If you receive the application for a paper survey, please let us know asap by email or phone (
As this is a pilot test, any and all feedback is greatly appreciated. Please feel free to email us at cafe-support-l@hawaii.edu.
Friday, March 14, 2008
Want to request a feature?
If you want to request a feature, please answer the following questions:
1.) What type of user is the feature for (Staff, Instructor, Student, or Administrator)?
2.) What should the feature do?
3.) What page would the feature be added to?
4.) What sort of forms/buttons are needed to make it work?
5.) What happens when you submit the form or click the buttons?
6.) How many people would be using this feature? (estimate)
7.) How frequently would this feature be used? (ex: every login, once a semester, once ever, etc)
8.) How important is this feature? (ex: Not very, somewhat, a deal breaker, etc)
9.) How tech-savvy is the typical user of this feature?
10.) What other details would a developer need to know to make the feature work the way you envision?
Please include your contact information so we can reach you to discuss the possibilities. Feel free to email your contact information if you don't want to post it.
1.) What type of user is the feature for (Staff, Instructor, Student, or Administrator)?
2.) What should the feature do?
3.) What page would the feature be added to?
4.) What sort of forms/buttons are needed to make it work?
5.) What happens when you submit the form or click the buttons?
6.) How many people would be using this feature? (estimate)
7.) How frequently would this feature be used? (ex: every login, once a semester, once ever, etc)
8.) How important is this feature? (ex: Not very, somewhat, a deal breaker, etc)
9.) How tech-savvy is the typical user of this feature?
10.) What other details would a developer need to know to make the feature work the way you envision?
Please include your contact information so we can reach you to discuss the possibilities. Feel free to email your contact information if you don't want to post it.
Subscribe to:
Posts (Atom)