Facilitating Access to Campus Technology Data
Copyright November, 2004 Miguel Guhlin Web Page URL: http://www.mguhlin.net/artifacts/writings/2004/access.htm
"If we don't change direction soon," wrote Professor Irwin Corey, "we'll end up where we're going." Surprisingly, in bringing technology into schools, do we ask where are we going to end up with all this? I had the occasion to reflect on the constant pace of change a few months ago. But, before I share that story with you--which is the beginning of this tale--I'm going to go straight to the end. You need to know about it if you're a technologist in public schools and lack lots of money. It's appropriate that we begin with the end in mind.
THE END OF THE STORY In early September, several of us--directors and associate superintendent--were planning our annual campus asset tracking (CAT) survey, a.k.a. Campus Access Survey (since we did not want to offend Fixed Assets, we changed the name). Several key challenges we faced included the following:
  1. The District needed to know more about equipment, software, and its use than Fixed Assets Department was collecting. Existing fixed asset systems were too limited and there was no centralized processing of equipment that comes into the District. Let me say that differently. New equipment coming into the District goes straight to the campus--bypassing the District--and is then tagged at that location by secretary assigned the duty. This system made it possible for the campuses to buy equipment with no oversight from the District. >
  2. Computer assets were moved from room to room and there was no uniform room numbering scheme. As such, the most we could know was that equipment was at the campus but not where it was. The campus staff knew what the room number was, but not the District. In other words, the room numbering scheme made sense only to campus level staff yet they had no way to see what they had in one place. >
  3. How could we move our Campus Enhancement Program--all about matching campus purchases of equipment with an equal amount from the state technology allotment (the only game in town now that TIF/TIE funding is gone)--to a web-based process? >
  4. Establishing a dialogue in the form of information exchanges was important. As assets moved, were added to or removed (obsolescence), campuses needed a way to keep the District in the loop for long-term technology planning. >
  5. Data was being collected but being stored in separate locations, unrelated to each other. In other words, what impact did the number of assets have on the LOTI level? How could anyone know if they did not know what they had? >
  6. Superintendents, principals were asking, "How many computers do we actually have? How does that number match up to the number of teachers in the District?
So, you can see, this last question is what really started the ball rolling on this massive data collection effort. As Curriculum & Instruction, the Instructional Technology Office brought more web-centric (e.g. web-based gradebook, digital video and more) and handheld assessment applications into the District, it became necessary to know exactly who had a computer and who did not. This became fundamental need in preparing reports for the superintendent. After all, how could we justify the statement, "Our teachers lack the equipment necessary to achieve LOTI Level 1 or 2, much less the target technology level of the STaR Chart and LOTI--level 4."
external image ctrdatactr.jpg
Click on picture to enlarge
The end of the story is really the beginning. In our effort to collect massive amounts of campus asset tracking data, campus contact information for our campus technology representatives and moving administrative staff, campus technology enhancement program, digital video distribution system passwords, bring STaR Chart data together, LOTI data, we ended up with what is now called the Campus Technology Representatives (CTR) Data Center.
CTRs are volunteers from each campus that shuttle messages back and forth from monthly meetings (http://itls.saisd.net/ctrs); the District lacks the funds to pay for full-time Campus Instructional Technologists (CIT). Utilizing free database tools (e.g. PHP/MySQL) and Macromedia Dreamweaver, I built the CTR Data Center to centralize a wide variety of data. Relating all this data was actually easier with PHP/MySQL than it was with Filemaker Pro, the tool we had been using. And remember, I do not know how to program or code HTML. Relating various data to a campus was important and the key to success.
THE MIDDLE: IT'S ALL ABOUT RELATIONSHIPS When I arrived in my district in 2002, the first campus asset tracking (CAT) survey was created using Filemaker Pro. We had to have the data because the only other option was to send people to campuses--people that should be spending their time working on instructional applications of technology rather than counting the technology. Although Filemaker Pro was fantastic, it wasn't long before we had almost 40 separate databases being accessed via the Web. We were working with data in--what my supervisor called--"silos." Of course, he was referring to Human Resources having one set of data and maintaining it, DataWarehouse having another, and Finance having another. The problem with this approach is obvious now--we end up with separate sets of data that do not relate to each other and grow inaccurate with time.
I had databases to collect CAT Survey data, the Campus STaR Chart data (which at the time did not allow districts to see what had been submitted unless they knew the campus password), frequently asked questions, and much more. Many other functions were still done with paper, and again, when asked what was our teacher to computer ratio, the best we could offer was a guesstimate. Yet, Filemaker Pro was all I knew could be used as easily.
As you may know, Filemaker Pro is a powerful database program, much easier and forgiving than MS Access. One of the key features that endured through versions 4-6 was its ability to share data on the web through custom web pages one could design using a special language, CDML. This was a key feature because it meant I--as well as many of the folks I taught how to do this at TCEA State Conferences--did not have to learn how to program PERL, PHP, whatever other arcane language interacts with web databases.
When version 7 of Filemaker Pro came out, this feature was no longer available. I would have to learn XML and this was too much like programming. Then, someone suggested, perhaps MySQL would work? As I pondered this monumental shift--has your cheese been moved lately? You'll know how I felt--in early September, my supervisor asked, "So, what's our computer to teacher ratio?" Worse, with so many new technologies in the District, the Filemaker based version of the Campus Asset Tracking survey was inadequate. It failed to capture the data needed. I had every incentive to start over using a new tool.
With the help of some of my team--notably, Greg Rodriguez and Jim Baldoni--I learned the basics of using Dreamweaver's PHP connections to MySQL databases to create web-enabled databases without programming. The process was not unlike that of web-enabling MS Access databases. The Campus Asset Tracking Survey is six sections long; campuses can complete and save one section at a time. While this is standard for the web these days, remember, Instructional Technology is not about programming and does not have $80K programmers on staff. And, if we did, they probably would not be used to collect asset tracking data needed for instructional decisions, STaR Chart, LOTI, Campus Enhancement Program, etc. What also made this successful is that our District Initiatives and Special Projects Director and staff were also able to query data using SQLYog (read sidebar), export the data, and build elaborate reports in MS Excel, their tool of choice.
What a powerful experience this has been. It only took me two-three days, finding or being shown the right resources like the ones in the sidebar, to get everything going...but it also helped me appreciate starting over. I resisted the desire to quit and not learn. I resisted the desire to tell myself, "Miguel, you're getting too old to start over." And, I'm better for the experience.
So, even though I didn't like the fact that Filemaker stopped working the way it once did, I'm grateful for the fact that it did. Otherwise, I would have kept going the way I was going...and maybe that would have landed me somewhere I didn't want to be--one of those old fuddy-duddies who can't do anything, waiting to retire because they don't think they can, or want to, start over, even if that was retirement is--starting over.
About the Author
Miguel Guhlin currently serves as the Director of Instructional Technology Services (http://itls.saisd.net) for a large San Antonio school district. With a small team, he's concerned about doing more with less when managing large projects--and he believes every project is a large project. You can reach him via email at mguhlin@yahoo.com or peruse his other writings at http://www.mguhlin.net
SideBar: Getting Started with PHP/MySQL
Windows Software
  1. WAMP Server: Easy setup of Apache server, PHP and MySQL on Windows. > Web: http://www.en.wampserver.com/ > Cost: Free
  2. SQLYOG : Allows you to interact with the database using Structured Query Language (SQL), as well as make backups of your SQL tables. > Web: http://www.webyog.com > Cost: Free
  3. Macromedia Dreamweaver MX: Allows you to build web forms that interact with your MySQL databases and tables. The 2004 version of Macromedia Dreamweaver features better PHP interface. > Web: http://www.macromedia.com > Cost: approximately $150
Macintosh Software
  1. MAMP Server: Easy setup of Apache server, PHP and MySQL on on Macintosh OS X
  2. CocoaMySQL: Allows you to interact with the database using Structured Query Language (SQL) statements. > Cost: Free
  3. Macromedia Dreamweaver MX: Allows you to build web forms that interact with your MySQL databases and tables. > Cost: approximately $150
MySQL Tools Available for Both Windows and Macintosh
While you can never have enough books, I recommend having these handy. Think of them as a $60 tranquilizer when you're ready to pull your hair out (a normal feeling when web-enabling databases no matter what database you use).

SQL Pocket Guide
Gennick, Jonathan

SQL Queries for Mere Mortals
Hernandez, Michael J. & Viescas, John L.
9 780201 433364
Workshops Interested in attending a workshop on this? You can find one at the upcoming TCEA 2005 State Conference in Austin, Tx. The workshop--materials available online--is being given by Jamey Osborne. He describes it as follows:

Wednesday, February 9, 2005. It's a six-hour workshop. I intend to cover installation and configuration of MySQL on Mac OS X, enabling PHP (and upgrading it to later versions will also be touched on). I will discuss how to set up a testing server on your OS X machine then, using Dreamweaver MX 2004, take you step-by-step through the development of a PHP-driven website. Email: josborne@elp.rr.com

If Windows is your focus, Miguel Guhlin is available to deliver a 6-12 hour workshop entitled:
Free Database-Backed Web Site Development for Non-Programmers
I. Introduction A. Setting Up a Testing Server Using WAMP (a.k.a. Windows Apache Mysql Php). Of course, for a Mac, you'd use MAMP. B. Tools to Interact with your MySQL server: 1) MySQL Control Center 2) Toad for Windows (once again, CocoaMySQL on a Mac)
II. Database Design A. Creation of a database with multiple tables, security and contacts, including primary and foreign keys. B. Discussion of FieldTypes, as well as recommending use of DATE, TIMESTAMP, VarChar, LongText, Int field types in particular C. Creating table and databases, adding initial data through MySQL Control Center D. Simple administration topics such as setting up a user for each dbase rather than using the admin user (root) for everything, and backing up data.
III. Introduction to SQL A. Querying existing data including sums and counts. Model use of statements such as SELECT, FROM, WHERE, JOIN, USING, AS and SUM, COUNT, GROUP BY, ORDER BY ASC/DESC B. Creating Web-based Forms to 1) Search Data and display results 2) Add New Data using Dreamweaver MX 3) Edit Data Entered 4) Delete Data Entererd
IV. Miscellaneous A. Setting up a Login page to ensure appropriate access by web users B. Other Resources Available: handy books to have- Hernandez, Michael J. & Viescas, John L. (2000). SQL queries for mere mortals: A hands-on guide to data manipulation in SQL. You can more info online at http://www.awprofessional.com and the ISBN is 9 780201 433364 ($50). C. Need a consultant? Send Miguel Guhlin an email at mguhlin@yahoo.com