Managing Data Using ColdFusion

1. Managing Data Using ColdFusion Calvin Lai Nov. 29, 2006

2. It all started with CHASE • In 2002, VCH contracted CFE to evaluate the impact of four new facilities: HCC, LSC, DCHC, PCHC setup by VCH as part of the Vancouver Agreement among three levels of government. • CHASE (Community Health And Safety Evaluation) was established and led by Dr. Mark Tyndall. • Three areas of primary interest: • Communicable diseases incidence and prevalence rates including HIV, Hepatitis A, B & C, tuberculosis, syphilis, and influenza. • Primary health care including the health status of DTES residents and the proportion of residents receiving primary health care. • Access and movement within the health care system with a focus on primary health care, hospital care, alcohol and drug counselling, sobering centres, detox, and long-term drug treatment.

3. DATA Components • Two main data components to this evaluation: • DTES Community Statistics such as VPD crime stats, Unemployment rates, Unemployment insurance, vital statistics. • Enroll 3-4 thousands DTES residents via a simple survey; ask for consent to link to other external health linkages such as PARIS, BCCDC, DTP and SPH ER. • One major advantage of data from linkages is these are hard data not self-reported ones. • Another advantage is getting prospective data with less $$ by skipping followups.

4. Challenges • Small budget, needs to control cost very effectively. • 4 full time personnel: admin manager, database manager and two research assistants. • Admin manager and two RA worked in remote office at PCHC (supported by VCH) and database manager at SPH. • Admin manager in charge of recruiting CHASE cohort, setup peer group who helped recruitment, writing reports and data entry. • Peer support is an important piece as it involved the community to help out in the project. • Database manager responsible for setting database, running stats analyses, setup website, provide systems and network support in the remote office and writing reports. http://chase.hivnet.ubc.ca • Need to pay VPD, BC Stats, Vital Stats for statistics requested for three year span 2002-2004 • Need to pay $10 incentive for each participant.

5. Peer Group

6. How to keep cost down? • Hired minimum personnel – everyone had to multi-task. • Used community peers. • Monitored duplicates & triplicates closely since we paid $10 incentive: • Required participants to show ID (photo ID, if possible). • For checking, needed an updated recruited list at least daily • I came up with the CF solution: • Needed NO extra HW (hence no extra $$) on my PC • PC already running W2K-PRO which came with IIS Internet Information Server (windows web server). • I just needed to install a free CF version the company distributed 6 years ago to get users hooked. • Once setup, the staff in remote office can get the recruited list real time.

7. Security issues • Security is a huge issue as it involved confidential info. • Employed three layers of security: • ID/PW protection on the website • IP-restriction • Special web port assignment eg. http://chase.hivnet.ubc.ca:7778

8. CHASE and after • This technology is so cost effective that it is deployed in subsequent cohorts: SEOSI, ARYS, MAKA, ACCESS and VIDUS2 : Cohort Period N Full Cohort Name • CHASE 2002- 3500 Community Health And Safety Evaluation • SEOSI 2003- 1000 Scientific Evaluation of Supervised Injecting • MAKA 2004- 250 Not an acronym, First Nations' languages for all women and/or healing women • ARYS 2005- 500 At Risk Youth Study • ACCESS 2005- 500 AIDS Care Cohort to Evaluate Exposure to Survival Services • VIDUS2 2005- 1000 Vancouver Injection Drug User Study

9. What is ColdFusion? • Popular web application server that generates dynamic database driven web pages. • Middleware that communicates with the database, processes data and then display the results on the web. • Based on an scripting language called ColdFusion Markup Language which compares to JSP or PHP and resembles HTML in syntax • CF web pages always has file extension .cfm simliar to .html in HTML web pages.

10. How ColdFusion works?

11. What so good about ColdFusion? • Based on HTML codes and extended to include SQL for handling databases. • Communicates to all popular databases such as Oracle, MYSQL, Windows SQL, MSAccess and even Excel via ODBC driver or specialized drivers. • Coding in CF is very efficient – compared to say windows ASP. • Rapid Development. • Fast Speed for web display. • Drill down feature. • Copy/Paste data on web into Excel directly.

12. Some CF examples: • ARYS TRACKING • One click on variable link sort the data by that variable, eg. Sorting by FU1 date will indicate who are due for FU2. • Drill-down feature: if you click on one specific ID, it will automatically print out all the detailed info about this particular – useful for staff to track down participants for followups. • Retrieve data into excel by simple copy/paste.

15. History of CF: • 1995 Allaire started ColdFusion • 2001 Macromedia bought out CF • 2005 Adobe bought out Macromedia including CF • 2005 10th Anniversary • Some CF websites: • http://www.medicine.ubc.ca/index.cfm?content=divisions/divisions_directory.cfm • http://www.sauder.ubc.ca/realestate/index.cfm • http://www.students.ubc.ca/calender/index.cfm?page=deadlines

16. CF version and $$ • CF Enterprise Version: US$5,999 • CF Standard US$1,299 • CF Developer Version – FREE everything runs on one PC http://www.adobe.com/products/coldfusion/productinfo/product_editions/ • Free and fully functional version of ColdFusion for local development of applications that will be deployed on either Standard or Enterprise servers. • In addition to localhost, access to applications is now possible from two client machines, making team development even easier.