In part one of this series, I shared some of the scenarios for recognizing a data warehouse. In part two, please allow me to share a sample proposal. The proposal follows the model outlined in this blog entry, Solving Wicked Problems: When Details Matter (Part 3). Before seeking a data warehouse, it’s important that a school district take stock and organize itself.
Challenges to Overcome
Before starting down the road, district staff need to have a realistic understanding of how data flows. Administrators looking for a procedure document may find one here. That flow varies from district to district. Below, you will find the diagram that captured the flow of data in my district. Before I created it, few had a clear idea of how data moved from one place to another. In fact, it might as well have happened via magical means. This process needs to be mapped for each data file that will be tapped as a source for student data in the data warehouse.
Process for Automated Upload of Student/Staff Data to an External Vendor System
- Given a need, a technology-based intervention or assessment tool is desired that requires student/staff data imports on a regular basis
- Project contact and technology department contact meet with vendor to identify the data file elements needed and complete the Data Request Form
- Once the Data Request form is complete, it is submitted to the Student Information System (SIS) contact
- When data files are received and tested successfully, the technology department contact will ensure the file gets to the vendor nightly via an automated, secure file transfer process
- The process is complete when data is flowing nightly into the external vendor system
Example of Process in Action
- Need for providing an instructional intervention for students in grades 3-8 to facilitate differentiation
- Curriculum director contacts technology director and a joint meeting takes place. After speaking with the vendor, a comma-delimited file needs to be created nightly that contains students’ firstname, lastname, campus, student ID#, TSDS#, district username, and password. A tentative start date is set for when this is needed.
- A Data Request form is prepared mapping SIS data element names to what the vendor needs an item to be called, then submittted to the student information system (SIS) partner
- Once the SIS vendor partner has finished creating the student data file, they save it to a secure FTP server. Technology staff sets up an automated process to transfer the file to the vendor so it can be processed nightly (e.g. 2:05am)
- Curriculum director and end users (e.g. teachers in grades 3-8) verify that information in the vendor system is up to date.
This data file creation process is offered as an example. The intent of sharing this is to help you review your own processes for data file creation.
The Proposal
The purpose of this proposal is to 1) facilitate easy, safe, and appropriate access to data that campus and district staff need to accomplish essential job functions, and 2) eliminate time staff currently waste in data hunting and gathering, sifting, and organizing that consumes more time than the data analysis component requires. The end result is a more informed leadership and better decision-making in the service of students and staff.
Achievement of the tenets of this proposal enable staff to have tiered levels of access to generate reports derived from data critical to their job functions via a web-based interface to a data warehouse of content.
- A data warehouse – This component serves as a district-controlled repository of all data–a local copy regardless of the source–on students and employees from a variety of data sources including, but not limited to, student assessments, student and parent lists, employee data from internal (e.g. Eduphoria, special education, Scholastic programs) and external sources of data (e.g. STAAR, iStation, iTCCS, TxGradebook, eSPED).
- A web dataportal – This component provides as a browser-based, easy to use interface to the data warehouse that enables campus and district staff to have point-n-click query-creation abilities to generate a variety of reports that juxtapose data from a variety of internal and external data sources.
Need
The district has a clear and urgent need to institute a data warehouse that is easily accessible by district/campus level staff. Some examples:
- Assessment Office: The district lacks a locally-controlled, central repository of all–Eduphoria Aware only provides some access–student/staff data, making it a time-consuming (e.g. hours to weeks) chore to correlate data from multiple sources (e.g. SMI/SRI and STAAR).
- Bilingual/ESL: TPRI/TejasLee staff and student data exports have to be obtained via a single district person, and this can involve wait time.
- Curriculum Department: Lack of a customizable data aggregation and reporting functions that are automated and easy to produce force reliance on a single person to generate reports for various locations.
- Campus Level: Staff spend hours per day, even weeks, tracking and sifting through data to create end of course (EOC) student lists that could be generated with a custom query that had access to all data sources. Furthermore, staff must keep track of alternative measures using Excel spreadsheets, which they assert is time-consuming and error-prone.
- iTCCS Access: The district currently has to provide ALL or nothing access via Open DataBase Compliant (ODBC) connection to staff, making it difficult to grant general users access to a subset of sensitive, confidential data they need to work.
- Special Education: Both district and campus staff have to correlate data between the student information system (SIS) and vendor systems–whose disparate systems are unable to “talk to each other”–to get the results needed to serve special populations.
- Technology Operations: The technology department must manually manage and try to create student/staff listing for an increasing variety of vendors. This involves manual updates that must be done daily, requiring a full-time, dedicated staff member simply to ensure account management is handled.
Note: Videos of stakeholders are available to provide specific details.
Process
A diagram (presented in part one of this blog entry) of how this would work appears in this section. It is modeled on how other districts’ data warehouses operate. Beginning at the top, end-users, whether at the campus or district level, would be able to interface via their web browser to a local repository of data. End users would be able to generate a variety of custom reports that juxtapose data they need to provide student level interventions (e.g. EOC, dropout, alternative measures, SMI/SRI to STAAR correlations, projections based on existing data to state/national standards). End users would not require an ODBC connection to the student information system (SIS) or administrative level access, instead running reports that give them access to data customized for their job function (e.g. principal vs. teacher) and location (e.g. campus staff can only see their data).
This data is composed of information gleaned programmatically and updated nightly via automated connections. Data flows from the SIS and other external data sources into the data warehouse. These automated connections–operating as many as two times per day–synchronize the data available in the Data Warehouse.
Cost
The approximate cost of establishing a web dataportal and data warehouse will require the following:
- $85K – $95K: DataBase Administrator (DBA) – Job description attached.
Description: This is an individual who will create the following:- A local repository of databases and tables (e.g. MySQL/MariaDB, MS-SQL) that mirror internal and external data sources that is updated two times per 24-hour period.
- A secure (HTTPS) web data portal that enables end users to have tiered-level access to data via queries that interface with the data warehouse and generate reports in HTML, PDF, and XLS formats for easy viewing and manipulation.
- $30K: Servers and Software to Create Data Warehouse and Web Data Portal
Description: Specialized software and servers will be needed to create the data warehouse and web data portal. This cost is not anticipated to impact until year 2 after the DBA is hired. - $30K: Identity Automation and Account Management
Description: The identity automation component–which can be independent of a data warehouse–enables automation of 95% of current account creation/login management, elimination of work, and systems integration. The $30K is an initial cost that will drop to approximately 10% after year 1 launch.
Timeline
Pre-Development
- Hire database administrator
- Purchase identity automation system
Phase 1
- Construct data warehouse
- Develop data join queries that merge data from multiple sources of data.
- Map out data queries to obtain desired reports (e.g. EOC tracking, dropout reports, correlations, and projections).
Phase 2
- Create tiered access matching staff member’s right to data access.
- Create district web portal that presents desired reports.
- Enable access for district and campus staff.
Phase 3
- Enable drill-down data mining for teachers to student level.
- Enable teacher/parent/student web portal views and reports.
Assessing Success
Success is defined by achieving all phases defined above.
Closing Thoughts
As you begin the process of putting in place a data warehouse, you may be tempted to seek out a vendor partner who can help you bridge the gap from where you are to where you need to be to see student data in a usable way. Be careful that before you involve a vendor, you are prepared to change how you work. Perhaps even more important, make sure that you have top level support to change the workflows for others in your organization. In part three of this series, we will take a look at finding a vendor partner.
