Teacher Data Warehouse User Guide
For Teacher Data Warehouse v1.0.0 Last update: October 18, 2007
Table of Contents
User Accounts Log-in procedure Contact for login/password problems Creating additional user accounts Submitting Data Submitting data using an Excel document Submitting data using an ASCII document Generating Reports Generating a B2 report Generating a B4 report Generating a “Where are my Teachers?” report as a map Generating a “Where are my Teachers?” report as a datatable Generating an Alumni Certification report Generating a Program Participation report Generating a State-wide TSR (Teacher Service Record) Analysis report Employees and Salary by Experience, Position and ROE Employees by Ethnicity, Role, and ROE Employees and Salary by Fiscal Year, Role and Highest Degree Earned Employees by Ethnicity, Gender, Role and ROE Teachers by Fiscal Year, Ethnicity and Role Employees by Role and Type of Degree Institution page 15 page 17 page 19 page 22 page 26 page 29 page 31 page 32 page 35 page 37 page 40 page 42 page 45 page 4 page 11 page 2 page 3 page 3
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 1
User Accounts
Log-in procedure 1. Click on the Login link at the top of the webpage:
2.
Enter your Login and Password, then click on the Login button:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 2
3.
You will be at the TDW Main Menu:
Contact for login/password problems
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 3
If you experience issues pertaining to your login and/or password (i.e. – lost login/password, nonfunctioning login/password), please contact Jon Laird at (217) 2446733 or jonlaird@uillinois.edu
Creating additional user accounts If you need an account created for a user other than the keyholder or Title II Coordinator, please contact Jon Laird at (217) 244-6733 or jonlaird@uillinois.edu
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 4
Submitting Data
Submitting data using an Excel document 1. Download the Excel data template from the link titled “Excel Worksheet for submitting Institutional Data” on the TDW homepage. Save the file to your computer:
2. 3.
Open the downloaded Excel file which was saved to your computer. The Excel document will open on the Data Specification tab of the worksheet. This tab explains each field on both the Annual Data tab and Program Data tab:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 5
4.
Click on the Annual Data tab to enter annual data for your institution.
Annual Data Field Descriptions FICE Code – 6 characters – Enter your institution’s FICE Code into this field for each entry. SSN – 9 characters – Enter the student’s social security number, without dashes or spaces.
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 6
Dummy SSN – 9 characters – Use only if the student does not have a social security number (i.e. students from other countries) Previous SSN – 9 characters – Use only if the student previously had a different social security number. Last Name – 20 characters (maximum) – Enter the student’s last name, up to 20 characters. First Name – 15 characters (maximum) – Enter the student’s first name, up to 15 characters. Middle Initial – 1 character – Enter the student’s middle initial, if any. Date of Birth – 8 characters – Enter the student’s date of birth, in the format YYYYMMDD (i.e. February 15, 1981 would be 19810215) Gender Code – 1 character – Enter the student’s gender (M=male, F=female) Race Code – 1 character – Enter the student’s race: o 1 – Black o 2 – American Indian/Alaskan Native o 3 – White, Non-Hispanic o 4 – Asian/Pacific Islander o 5 – Hispanic o 6 – Non-Resident Alien o 9 – Not Reported/Unknown/Missing Fiscal Year – 4 characters – Enter the appropriate Fiscal Year for the data you are submitting (i.e. Data for students enrolled from July 1, 2006 through June 30, 2007 are in Fiscal Year 2007). Use a separate file for each Fiscal Year. Do not submit multiple Fiscal Years in the same file. Summer – 1 character – Enter the enrollment status of the student during the summer at the beginning of the Fiscal Year: o 0 – Enrollment period not offered at your institution o 1 – Enrolled full time o 2 – Enrolled part time o 3 – Not enrolled o 9 – Data missing Fall – 1 character – Enter the enrollment status of the student during the fall period of that Fiscal Year: o 0 – Enrollment period not offered at your institution o 1 – Enrolled full time o 2 – Enrolled part time o 3 – Not enrolled o 9 – Data missing Winter – 1 character – Enter the enrollment status of the student during the winter period of that Fiscal Year: o 0 – Enrollment period not offered at your institution o 1 – Enrolled full time o 2 – Enrolled part time o 3 – Not enrolled o 9 – Data missing Spring – 1 character – Enter the enrollment status of the student during the spring period of that Fiscal Year: o 0 – Enrollment period not offered at your institution o 1 – Enrolled full time o 2 – Enrolled part time o 3 – Not enrolled o 9 – Data missing Class Level – 1 character – Enter the student’s class level upon the 10-day census date for the first term the student is enrolled in that Fiscal Year:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 7
o 1 – Freshman o 2 – Sophomore o 3 – Junior o 4 – Senior o 5 – Non-degree student o 6 – Graduate U.S. Citizen – 1 character – Indicate if the student is a U.S. citizen (Y=yes, N=no). Degree Type – 1 character – Highest degree awarded during this Fiscal Year: o - No degree awarded this year o B – Baccalaureate o M – Master’s o C – Certificate of Advanced Study/Specialist o D - Doctorate Admission Zip Code – 5 characters – Enter the ZIP code from which the student submitted their application when admitted to your institution.
Notes on the Annual Data tab: - Each field in the Annual Data tab, except Dummy SSN and Previous SSN, is required. - Each student should have only one entry in the Annual Data tab. 5. Click on the Program Data tab to enter program data for your institution.
Program Data Field Descriptions FICE Code – 6 characters – Enter your institution’s FICE Code into this field for each entry. SSN – 9 characters – Enter the student’s social security number, without dashes or spaces.
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 8
Fiscal Year – 4 characters – Enter the appropriate Fiscal Year for the data you are submitting (i.e. Data for students enrolled from July 1, 2006 through June 30, 2007 are in Fiscal Year 2007). Use a separate file for each Fiscal Year. Do not submit multiple Fiscal Years in the same file. Program Abbr – 6 characters (maximum) – Enter the program abbreviation for the program in which the student is enrolled for that Fiscal Year. A comprehensive list of program abbreviations can be found at https://pbtdw1.ui.uillinois.edu/docs/Certification_codes_final.xls Program Route – 1 character – Enter the applicable program route for the program in which the student is enrolled: o A – Alternative o L – Alternative Administrative o T – Traditional Educational Objective – 1 character – Enter the educational objective for the program in which the student is enrolled: o B – Baccalaureate o M – Master’s o P – Post Master’s o D – Doctorate o T – Teacher Certificate Only Program Type – 1 character – Enter the program type for the program in which the student is enrolled: o I – Initial certificate o T – Advanced program teacher preparation (with certificate) o N – Advanced program not leading to a certificate o O – Advanced program - other Program Completer – 1 character – Indicate if the student has completed this program at the end of that Fiscal Year (Y=yes, N=no). Both completers and non-completers should be included in the data. Completion Date – 8 characters – Enter the date on which the student completed this program, in the format YYYYMMDD (i.e. May 15, 2007 would be 20070515). A completion date should only be provided if the Program Completer field for this record is Y (yes); if the Program Completer field for this record is N (no), then no completion date should be given. Only give completion dates with fall within the range of the indicated Fiscal Year.
Notes on the Program Data tab: - Since students may be concurrently enrolled in more than one program, there may be more than one record in the Program Data tab for each entry in the Annual Data tab. Click the “Click to check all sheets” button to provide basic error-checking of the document prior to uploading. Formatting errors will be highlighted in yellow. Be sure to save all changes to the file. Click the “Upload Files” link on the TDW Main Menu:
6.
7.
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 9
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 10
8.
You will arrive at the Upload page:
9.
Click the “Browse…” button to locate the saved data file. Select the file and click “Open”:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 11
10. The directory location of your file should appear in the “Select the file to upload” field. Click the “Upload” button to send the file to the Teacher Data Warehouse staff. Once the file is uploaded and the Upload page refreshed, the file you just uploaded should appear under Current Contents:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 12
Submitting data using an ASCII document 1. Download and open the Excel ASCII template from the link titled “ASCII file layout for data submission” on the TDW homepage. You will see an Excel spreadsheet outlining the fields which need to be included in the file, the number of character spaces which should be allotted for each field, and the valid characters for each field.
2.
3.
Enter student data into a suitable ASCII program, such as Microsoft Text. One ASCII file should be submitted for Annual Data (all students admitted during that Fiscal Year), and a separate ASCII file should be submitted for Program Data (all students enrolled in each program offered by your teacher education program). Annual Data – Example: John A. Smith is a Hispanic male from Waukegan, IL who is enrolled in the teacher education program at the University of Illinois – Urbana/Champaign for the 2007 Fiscal Year. He was enrolled full-time in the fall of 2006, receiving his Baccalaureate in December, and began work full-time on his Master’s degree at UIUC in the spring of 2007. The correct ASCII-formatted data would be:
Smith John A19810215M5200731014YB60085
4.
001775111991111
5.
It is important to note that all characters must be accounted for when submitting an ASCII file. If you will not use all of the characters allotted for a particular field (i.e. a student’s last name is shorter than 20 character), use spaces for the remaining characters. Program Data – Example: John A. Smith is a Hispanic male from Waukegan, IL who is enrolled in the teacher education program at the University of Illinois – Urbana/Champaign for the 2007 Fiscal Year. He was enrolled full-time in the fall of
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 13
2006, receiving his Baccalaureate in December (an initial certificate in Sociology), and began work full-time on his Master’s degree at UIUC in the spring of 2007 in order to become a Principal. The correct ASCII-formatted data would be:
0017751119911112007SSSA 0017751119911112007GADM TBIY20061215 TMON
Notice there are two entries in the Program Data file for this one student this Fiscal Year: one entry for the completed Baccalaureate degree in Sociology, and one for the inprogress Type 75 certificate. Data should be included whether the student has completed the program or not, and a separate line should be used for each program. 6. Save both text files to your hard drive. Please include your institution’s FICE Code and the relevant Fiscal Year in the file name. Click the “Upload Files” link on the TDW Main Menu:
7.
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 14
8.
You will arrive at the Upload page:
9.
Click the “Browse…” button to locate the saved data file. Select the file and click “Open”:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 15
10. The directory location of your file should appear in the “Select the file to upload” field. Click the “Upload” button to send the file to the Teacher Data Warehouse staff. Once the file is uploaded and the Upload page refreshed, the file you just uploaded should appear under Current Contents:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 16
Generating Reports
Generating a B2 report A B2 report analyzes the gender and race/ethnicity of candidates who have been formally accepted into programs which prepare them to work in P-12 schools. The report is generated by analyzing the Annual Data files submitted by participating institutions. 1. Click on the Reports link at the top of the page:
2.
You will arrive at the Reports page:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 17
3.
Using the drop-down menu under B2 Reports, select the academic year and semester you wish to view. Click the “go” button:
4.
A table will be generated detailing the number of male and female candidates in both graduate and undergraduate programs for each race/ethnicity gathered by the Teacher Data Warehouse. A bar graph representing these data will also be generated.
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 18
Generating a B4 report A B4 report analyzes enrollment by gender, ethnicity and program pursued. The generated table depicts data for the academic year, semester, and enrollment type indicated. The report is generated in real time. 1. Click on the Reports link at the top of the page:
2.
You will arrive at the Reports page:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 19
3.
Using the drop-down menu under B4 Reports, select the academic year you wish to view. Click the “go” button:
4.
A table will be generated detailing the number of male and female candidates in both initial and advanced programs for each race/ethnicity gathered by the Teacher Data Warehouse. A bar graph representing these data will also be generated.
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 20
Generating a “Where are my Teachers?” report as a map The “Where are my Teachers?” report details each graduate’s enrollment history, state certifications, certificate endorsements, region of issue for certificates, and teaching assignments in Illinois public schools. 1. Click on the Reports link at the top of the page:
2.
You will arrive at the Reports page:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 21
3.
Under Where are my Teachers?, click the radio button next to Map:
4.
Using the drop-down menu, select the enrollment year you wish to view:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 22
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 23
5.
Next, use the second drop-down menu to select the teaching year you wish to view:
6.
Check the box below the drop-down menus to show only completers from that enrollment year, or uncheck it for all. When finished, press the “go” button. A map showing employment by county and a table showing county totals will be displayed:
7.
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 24
8.
By checking the empty check-box on this page, you can normalize the map for county population:
Generating a “Where are my Teachers?” map as a datatable
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 25
The “Where are my Teachers?” report details each graduate’s enrollment history, state certifications, certificate endorsements, region of issue for certificates, and teaching assignments in Illinois public schools. 1. Click on the Reports link at the top of the page:
2.
You will arrive at the Reports page:
3.
Under Where are my Teachers?, click the radio button next to Datatable:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 26
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 27
4.
Using the drop-down menu, select the enrollment year you wish to view:
5.
Next, use the second drop-down menu to select the teaching year you wish to view:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 28
6. 7.
Check the box below the drop-down menus to show only completers from that enrollment year, or uncheck it for all. When finished, press the “go” button. A table will be displayed for the data selected, showing the student’s name, their position title, the school code for the school at which they are employed, the school name, the school address, the school city, the school county, and the school zip code:
8.
By clicking on a student’s name, you can obtain more detailed information about the student:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 29
9.
You can also save the datatable as an Excel file. From the generated datatable, click on the link titled “Export this table as an Excel file (.csv)”:
10. Click the “Open” button to open the file in Excel, or click the “Save” button to save the Excel file to your computer.
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 30
11. The generated Excel file while look like this (personal student information has been blacked out for the purposes of this user guide):
Generating an Alumni Certification report The Alumni Certification report shows what Illinois Teaching Certificates your students have earned, by enrollment year at your institution. 1. Click on the Reports link at the top of the page:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 31
2.
You will arrive at the Reports page:
3.
Under Alumni Certification, use the drop-down menu to select the enrollment year you wish to view:
4.
Check the box below the drop-down menus to show only completers from that enrollment year, or uncheck it for all. When finished, press the “go” button
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 32
5.
A table will be displayed for the data selected, showing the number of matching alumni, the number of certificates issued, the teacher’s name, the teacher’s date of birth, the type of certification issued, the certificate level, the issuance date, the region of issuance, and the issuing institution:
6.
You can also save the datatable as an Excel file. From the generated datatable, click on the link titled “Export this table as an Excel file (.csv)”:
7.
Click the “Open” button to open the file in Excel, or click the “Save” button to save the Excel file to your computer.
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 33
8.
The generated Excel file while look like this (personal student information has been blacked out for the purposes of this user guide):
Generating a Program Participation report A Program Participation report shows which ISBE programs your students are pursuing, what your program profile is by class (Freshman – Senior), and how it compares to state-wide averages. 1. Click on the Reports link at the top of the page:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 34
2.
You will arrive at the Reports page:
3.
Under Program Participation, click on the link titled “View Report”:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 35
4.
You will be presented with six tables detailing program participation: two tables detailing program participation for your institution, two tables detailing program participation for public institutions, and two tables detailing program participation for all institutions:
The Program Participation report defaults to Fiscal Years 2001 – 2004. To change the range of years, select the starting year from the Start Year drop-down menu, the ending year from the End Year drop-down menu, and click the “Generate Report” button. Currently, 2000 is the earliest starting Fiscal Year, and 2004 is the most recent ending Fiscal Year. Generating a State-wide TSR (Teacher Service Record) Analysis report 5.
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 36
The TSR Analysis reports provide aggregate analysis of TSR data, which includes information about ethnicity, gender, education level and type, and assignments throughout the state of Illinois. Currently, there are six TSR Summary reports provided by the Teacher Data Warehouse: Employees and Salary by Experience, Position and ROE Employees by Ethnicity, Role, and ROE Employees and Salary by Fiscal Year, Role and Highest Degree Earned Employees by Ethnicity, Gender, Role and ROE Teachers by Fiscal Year, Ethnicity and Role Employees by Role and Type of Degree Institution Generating a Employees and Salary by Experience, Position and ROE report 1. Click on the Reports link at the top of the page:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 37
2.
You will arrive at the Reports page:
3.
Under State-wide TSR Analyses, click on the link titled “View Report”:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 38
4.
You will be at the TSR Summary Reports menu:
5.
Click the link for the year you wish to view under Employees and Salary by Experience, Position and ROE. A table will be displayed detailing positions by regional office, giving counts and salaries based on the number of years of experience:
6.
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 39
Generating a Employees by Ethnicity, Role, and ROE report 1. Click on the Reports link at the top of the page:
2.
You will arrive at the Reports page:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 40
3.
Under State-wide TSR Analyses, click on the link titled “View Report”:
4.
You will be at the TSR Summary Reports menu:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 41
5.
Click the link for the year you wish to view under Employees by Ethnicity, Role, and ROE. A table will be displayed detailing positions by regional office, giving counts and percentages based on self-reported race/ethnicity:
6.
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 42
Generating a Employees and Salary by Fiscal Year, Role and Highest Degree report 1. Click on the Reports link at the top of the page:
2.
You will arrive at the Reports page:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 43
3.
Under State-wide TSR Analyses, click on the link titled “View Report”:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 44
4.
You will be at the TSR Summary Reports menu:
5.
Click the link titled “1999-2006” under Employees and Salary by Fiscal Year, Role and Highest Degree Earned. A table will be displayed detailing highest education level by position, giving counts and salaries based on the Fiscal Year:
6.
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 45
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 46
Generating a Employees by Ethnicity, Role, and ROE report 1. Click on the Reports link at the top of the page:
2.
You will arrive at the Reports page:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 47
3.
Under State-wide TSR Analyses, click on the link titled “View Report”:
4.
You will be at the TSR Summary Reports menu:
5.
Click the link under Employees by Ethnicity, Gender, Role and ROE for the year you wish to view.
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 48
6.
A table will be displayed detailing self-reported race/ethnicity counts, arranged by regional office, then position, then gender.
Generating a Employees and Salary by Fiscal Year, Role and Highest Degree report 1. Click on the Reports link at the top of the page:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 49
2.
You will arrive at the Reports page:
3.
Under State-wide TSR Analyses, click on the link titled “View Report”:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 50
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 51
4.
You will be at the TSR Summary Reports menu:
5.
Click the link titled “1999-2006” under Employees by Fiscal Year, Ethnicity and Position. A table will be displayed detailing race/ethnicity counts for each type of position, sorted by Fiscal Year:
6.
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 52
Generating a Employees by Role and Type of Degree-Granting Institution report 1. Click on the Reports link at the top of the page:
2.
You will arrive at the Reports page:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 53
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 54
3.
Under State-wide TSR Analyses, click on the link titled “View Report”:
4.
You will be at the TSR Summary Reports menu:
5.
Click the link titled “1999-2006” under Employees by Role and Type of DegreeGranting Institution
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 55
6.
A table will be displayed detailing position and in-state education, sorted by Fiscal Year:
Teacher Data Warehouse User Guide
https://tdw.pb.uillinois.edu
Page 56