Recently in DW Connect Category

DW Connect: Close-Out

| 1 Comment

We convened a Close-Out meeting for DW Connect and Third Party Billing projects. In attendance: Christopher, Rebecca, Sarah, Catalina, Missy and me. DW Connect is the name given to a collection of smaller projects that all query the data warehouse or leverage data from the Shadow database. Born as a four-alarm fire as we scrambled to extract EA data in order to perform essential tasks, DW Connect now, almost a year later, has cooled into a steady flow of data that spills onto reports and forms.

We treated the Third Party Billing Report as an off-shoot of DW Connect. Because:

  • It had a different set of stakeholders in Catalina and Missy.
  • It produced a final product that was external rather than internal.
  • It came with its own set of goals and objectives as declared in the Statement of Work.

What Worked

  • We did it! We completed all the work and met our objectives.
  • We maintained scope. Stakeholders had a good grasp of their needs and were able to articulate them in a way I could comprehend.
  • For not having a clear concept of the finished product or how to duplicate the data warehouse without confusing operations, we developed a good final product.
  • We spread the gospel of Project Management to two more LAC staff.
  • Our communication methods worked well - emails, calls, chats and Apple Remote Desktop.

What We Could Have Done Better

  • Communication frequency could have been improved - at least once Missy had to ask the status of the project.
  • We ought to have included the Institutional Relations team at the start of the Third Party Billing project. While it wouldn't have changed direction or scope, it may have helped set expectations and educate them on what's possible.
  • We continue to struggle with concluding projects in a timely manner. That may be a function of the next failing:
  • We didn't assign a person with "skin in the game" the role of project manager. The very people who had vested interest in seeing it through were outside looking in. That said, we don't have a stable of project managers, and the majority of staff don't have training in project management principles.

Looking Ahead

There are no related projects waiting in the wings. Instead, opportunities for growth lie in training existing staff to use the Shadow Database more effectively. We will continue to add reports, routines and small queries of additional data.

The downside of our success is our product shares data too easily. As more systems and processes hook into the Shadow Database, our challenge will be:

  • make sure other systems honor the privacy of the data and follow proper guidelines.
  • control access so that ability to alter data rests with those who are trained and understand its interdependencies
  • prevent users from aggregating data for historical reports. LAC and ES own the data and business rules around it.

A future enhancement to the Third Party Billing report is to build an institutional relations database to store the business logic that's currently contained in Catalina's brain and an increasingly complex Excel spreadsheet.


DW Connect: 152 hours since March 31, 2010.
Third Party Billing: 40 hours since June 15, 2010.

Favorite Four Letter Word: DONE

| 1 Comment

It might just be that the DW Connect project is done. I'll leave it open for a bit to see what bubbles to the surface. Thanks much to Barbara S. who spent two hours schooling me on queries and the Data Warehouse.

I'll schedule a Close-Out meeting just as soon as the Third Party Billing Report wraps. We're waiting for Christopher and team to install the ODBC plugins on Missy and Catalina's computers. Note to self: check if they have proper fonts.

Third Party Billing Report

The 3PBR is done. Once the ODBC plugin is installed on Catalina's and Missy's computers, I will schedule a training session/Close-Out meeting.

DW Connect

Christopher is in the process of adjusting your access so you can query passport information. It's super secret so not a part of the default access profile. The query is written and waiting in the database. I need about five minutes of Rebecca's time to complete a find and export script to make the process really smooth.

I've been wrestling with the final piece — educational data for UofM students. It's beyond my ken so I have asked Barbara S to come help me. If, after a session with her, it's still too complex, perhaps we could request a custom table that pulls it all together. Rebecca tells me that educational info isn't needed until the Spring so I'm not holding up anything.


Forgive me blogger for it's been six weeks since my last post.

  • I missed a deadline for the Third Party Billing Report. In my defense, I learned of business rules beneath the surface that needed to be understood and applied. It's not simply plucking values off the interface as we assumed in the Statement of Work. I submitted a new draft to Catalina yesterday.

Redeeming Developments

  • Before leaving for vacation, I wrote and circulated a Statement of Work for the Staff Database project.
  • External Funding is complete; just waiting for the Business Process Owners to complete a few things before scheduling a Close-Out meeting.
  • External WIV Programs is done. Next week I train the WIV team.

Completing last the two projects means we can let the Backup Server die. This is big, as that server required constant end-of-life care.

Formula for Absolution

  • Complete the Third Party Billing Project.
  • Resolve support tickets assigned to me.
  • Conclude DW Connect for LAC.
  • Work on two smallish projects for CARLA.
  • Fix the Event Registration work-around for the Dean's Office.
  • Focus my energies on the Staff Database project.

DW Connect: Status Report

FileMaker Peoplesoft Reconciliation

Done. I provided Rebecca with individual databases containing FileMaker records that don't match their Peoplesoft counterpart. Once ES staff works through their list we'll run the comparison again to catch what we missed.

CISI Report

Mandi has entered comments using new codes Sarah set up for Insurance Sent and Insurance Waived. I can query the comment table but the Application Number field is empty. This means we can only associate an insurance comment back to the person, not the application. If a person needs insurance on multiple occasions I we can't tell for which one the insurance was sent or waived. I sent an email to Sarah regarding the issue.

Improve Queries

I got wonderful feedback from Barbara. I need to find time to incorporate her tips and techniques. She's also expressed willingness to come work with us for a few hours - an offer I plan to take her up on.

Third Party Billing Supplemental Report

I incorporated feedback from Catalina and updated the Statement of Work.

DW Connect: New Project

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.

DW Connect: Status Report

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.

DW Connect: Status Report


  • 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.

Action Item: Record comments indicating insurance sent or waived so I can test my query.

Third Party Billing Supplemental Report

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

Action Item: Read and respond with questions or comments by Friday, July 2, 2010.

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.

CISI Report

Due by June 15.

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

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.

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?

Improved Error Trapping

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

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

DW Connect: Status Report

| 1 Comment
  • Rebecca, Arianna, Julie, Jessica, Amber and Rebecca have been trained on the system.
  • Christopher is set to run the queries all week, starting Friday, April 9.