June 2010 Archives

FileMaker Peoplesoft Reconciliation

This is a new, mini project under the DW Connect umbrella. The goal is to update historical records in Peoplesoft with what's stored in FileMaker. The trick is zeroing in on just those records that changed, not auditing every last one. I will work with Rebecca to generate some kind of report or script or something. Deadline to complete the project is Sept 1, 2010. My deadline to deliver something to Rebecca is July 15, 2010.

Improved Letters

Done. Just waiting for Arianna to supply text for the email. We stumbled upon a bug where there's a black background instead of readable text in the Mail message generated by FileMaker. This is a bug in Safari 5 and nothing we can do about it until it's fixed in an update. This support article from Apple offers a work-around. It will be a pain but perhaps it's still better than stamping signatures on letters and mailing them.

Miscellaneous

• Rebranded the EA_Data (a.k.a. Applications) file to "Shadow Database." Bwah ah ah.
• Restored Track Term Start and End dates. I had deleted them in error from the Shadow Database. They're back in the database and restored to the various visa letters that call for them.

CISI Report

• Programmed Enrollment Code to calculate based on insurance start and end dates.
• Added Alliance for Global Ed (00009) to the "Find CISI Records" script as a sponsor to omit from the search.
• Changed logic on the email address. If the STUDENT_TYPE_DESCR = "UMN Student," use the UofM address, otherwise use the other email address, unless that's empty, then use the UofM address.
• Began the process of filtering out those whose insurance has been sent or waived.
• Programmed a second, internal report to run on the heels of the CISI report. This report is optimized for student workers recording comments in Peoplesoft indicating insurance has been sent.

Third Party Billing Supplemental Report

• Wrote and delivered the Statement of Work to Missy and Catalina on June 23rd.

Institutional Relations Report

Done. Now, both PDF and Excel versions of the report include Track.

Controlling the Data Set

Solved. We keep only "current" applications in the Shadow Database. "Current" means just those records required to do the work Peoplesoft doesn't do, like printing letters and labels, preparing for orientations, kicking lists out to providers, etc. On a cycle which is currently weekly, we dump all data from the Shadow Database and import fresh. The Shadow Database will not store data for historical reporting. The Shadow Database will not maintain data on its own - it only knows what it imports from Peoplesoft.

Improve Queries

I sent my queries off to Barbara on June 28. We'll have to see when and how she can help us.

We held a close-out meeting for LAC's Liberal Ed Database project. In attendance were Christopher, Emily, Kim and Sarah. Let us first congratulate ourselves for completing a project that languished at the bottom of the priority pile for two years.

The Statement of Work, dated Aug 2008, sounds quaint as it worries over learning PHP, configuring servers, and following project management protocols. That these practices are second nature to us serves to demonstrate how far we've come in a short span.

Remarkable is how little the scope changed. Sure, we switched up how to accommodate old and new requirements, but the core of the project remained the same. This is a testament to the business process owners who thoroughly understood their needs and the system required to meet them.

As we scored ourselves against the Statement of Work, a few fails jumped out:

• A significant piece of scope was dropped surreptitiously and without documentation. That the project didn't suffer without it is beside the point. I should have brought it to the attention of the stakeholders and given them an opportunity to make an informed decision.
• A new stakeholder joined the project mid-stride. I could have done a better job bringing her up to speed, providing her a copy of the Statement of Work, and prepped her on the project management process.
• We omitted an important constituency — advisers — from the testing and training phase. When the system went live they sent waves of feedback. We should have addressed their concerns earlier in the process and leveraged their buy-in for the launch.

The Timeline

I'm not sure what to learn from the timeline. Here's what we know:

1. Changes to to Lib Eds hadn't completely settled out when we started the project. We actually benefitted from delays. In the end we built a system that serves the new requirements, not the old, and not some blending of the two.
2. Other projects were given priority over this one and rightly so.
3. Both LAC and IT underwent significant staff changes. Terry left. I jumped from contract to contract to perm. Both Christopher and I moved from LAC to OIP. Sarah was gobbled up by Peoplesoft. Emily stepped up. Kim was dropped in medias res.

Communication

The blog took getting used to but worked well for communication and documentation. And how slick was that? To simply send a link to another institution interested in replicating our work.

Hours

I spent 100 hours from Feb 5, 2009 to June 23, 2010.

Reflecting on the Statement of Work for this project, we maintained scope, succeeded on deliverables and met goals and objectives. Not bad for our first project together. Still, we learned a few things that may help us in future projects.

Re-doing Work

After I had formatted a complicated page, CARLA re-organized it. I spent considerable time re-doing my work. I might have assumed the page needed modification before I committed it to HTML. Make sure the stakeholder is aware of the work I'm doing and give them a final chance at editing.

Timeline Slippage

This project was not complete by its original due date for several reasons:

• it was set aside so I could work on a more urgent project
• the iterative process yields a good final product but takes more time
• in general, things always take longer than we think
• final tasks were turned over to CARLA for completion and their timeframe wasn't urgent

The longer timeline was not a problem for CARLA but it can be for IT. Inability to close out projects as scheduled impairs our ability to deliver other projects on time. The lesson here may be: don't coast just because the stakeholder is not applying pressure.

Communication

Overall, communication was frequent enough and effective. However, I learned that CARLA did not read blog entries because they were not aware of them and they prefer to read content in email. Let's try three things next time:

1. Make sure the blog subscription service is working and train recipients how to handle subscription emails.
2. Help stakeholders resolve their "blog block." Show how they only see content relevant to their project. Set up an RSS feed if that's more compatible with their work style.
3. Having declared in the Statement of Work that project updates will be delivered via blog post, make sure stakeholders understand it's our expectation they read it.

Hours

I recorded 105.5 hours between November 16, 2009 and June 22, 2010.

If the web pages come up with a 403 Access Forbidden message, this likely means that the Live Volume that houses the webserver data has been hijacked by Contribute.

To get a terminal it is also possible to ssh in, at the command line type: ssh -l oipadmin -C xxx.xx.xxx.36

Enter in the password and you will be dropped to a command line and can proceed as normal!

1. Login to the webserver:
2. Open terminal:
3. cd /Volumes
4. sudo mv Live Live5.blah
5. sudo shutdown -r now

That will move the contribute files to a new folder incase there is data we need in there. After that the restart, with the shutdown -r now, should remount /Volumes/Live with the correct name.

Before Go Live

 Send list of users to Stacey to audit.
 Conduct testing sessions with APDs. I have two scheduled for Thursday.
 Disable Documents tab until we solve security issues.
 Prepare launch e-mail that addresses technical issues and send to Stacey.
 Audit all error messages to make sure they're clear, appropriate and provide the user a path to resolution.
 Add Stacey's phone number to error messages that currently have her e-mail address.
 Edit logout page to contain a single link to log back in.
 Remove my test case.
 Enable participants list to show actual names.

After Go Live

 Investigate SuperContainer as a secure method to upload and share documents.

CISI Report

Due by June 15.
Stakeholder=Mandi

Third Party Billing Supplemental Report

Similar to the Institutional Relations report but showing the breakdown of charges to students' account. Meeting set for June 15
Stakeholder=Missy

Institutional Relations Report

The Ins Rel team would like to request that TRACK be added to the Ins Rel report that is pulled from PeopleSoft (now added to the PeopleSoft Applications Filemaker database). PROGRAM alone is not descriptive enough anymore for reporting to our affiliates.
Stakeholder=Rebecca

Improved Letters

With the "Venezuela with Proof" letter in the FileMaker solution for PeopleSoft, would there be any way to add the UM header/footer and Holly's signature? I'll be emailing letters to student individually. As it stands, I'll enter track start/end dates for each student, print the letters on letterhead, stamp Holly's signature on each, then scan/email each individually to myself, finally email each student their letter. Any idea to make this less cumbersome?
Stakeholder=Arianna

Improved Error Trapping

Error messages aren't descriptive enough so the user doesn't understand why query failed and how to correct it.
Stakeholder=Me

Build More Queries

Return to the original list provided by ES and make sure I am able to query all those values — from emergency contact information to passport number.
Stakeholder=ES Team

Improve Queries

Work with OIT to improve the efficiency of the queries. Currently we're running the same query multiple (30+) times which is not sustainable. How can I rewrite them to get the same info in a single pass? Does OIT need to create additional tables?
Stakeholder=ES Team

Controlling the Data Set

How are we going to maintain an up-to-date set of records that covers everyone's needs?
Stakeholder=All LAC