KDE DATA CLEAN-UP GUIDE V.1 4/28/08
Table of Contents
I. Households………………………………………………………………………….. 2
II. Data Consistency………………………………………………………………….. 3
III. Results………………………………………………………………………………… 6
IV. SQL Queries …………………………………………………………………………. 8
V. Staff/Guardians……………………………………………………………………… 8
VI. Data Mapping……………………………………………………………………….. 9
VII. Trial 1 & Trial 2 Data Review ………………………………………………………. 9
VIII. STI Clean-Up Report………………………………………………………………….9
I. Households
Infinite Campus creates households from district wide data. In the legacy system, student information was available through school level data on a student level basis. In Infinite Campus, the student level information is available but is viewed through a district wide household. When building households, KDE looks for matching home phone numbers between the student and guardian(s). Students and their guardians should have consistent first and last name and the same, correct phone number across all schools within your district. For example, a family (guardian one and guardian two) lives in a school district and has a child in the elementary school and in the middle school. The first step is to make certain that the same phone number exists for primary phone number for the student at Lincoln Elementary and both guardians at Lincoln Elementary. Be sure your data is consistent within the school first. The next step is to make certain the student at Lincoln Middle and both guardians at Lincoln Middle have the same phone number as the child and guardians in Lincoln Elementary. The final step would be to ensure that the guardians are entered the same in Lincoln Elementary as in Lincoln Middle.
In the case of a two parent family with two students enrolled at the elementary and middle school, for the household to build correctly, both STI databases at the elementary and middle school must contain the exact same data for the Guardian1 and Guardian 2. Both STI databases at the elementary and middle school must contain the same phone number for each student.
The physical addresses are used as a secondary matching when the phone number does not exist. In order to build households correctly, you should focus on cleaning up phone numbers and the first names and last names of guardians and students. However, you will still want to clean your addresses; if you do not you will have multiple addresses in the same household.
If parents are divorced and do not live in the same household, then the phone number for the custodial guardian should match the student to place them in the correct household.
II. Data Consistency
When dealing with multiple school databases, it is important to have uniform data across each school database. Decisions must be made on a district wide basis on correct phone numbers for the households, correct first and last names for the guardians living with the student and correct addresses. These need to be communicated to each school in the district as necessary.
The most important factor in creating households is to make certain phone numbers match. However, data consistency across the rest of the records is important as well.
PHONE NUMBERS
ü Phone numbers must be seven or ten digits long. Anything after the tenth digit may cause your households not to build correctly. Examples include placing two phone numbers in the phone number field for the guardians and/or indicating the type of phone it is with an (h) or (c).
Figure 1--The h should be removed from the home phone number. The second phone number should be removed.
ü If a phone number is without an area code, please fill in the correct area code. If not, KDE will program in the area code for your district; however, this can cause issues when the area code is different due to cell phones or recent area code conversions.
ü If a phone number does not exist, be sure to leave it blank and not to put in all zeroes. If you leave zeroes in the phone number field, this will create one giant household containing everyone in your district who has zeroes entered for their phone number.
NAMES
The first and last name of guardians must match if they are living in the same household as the student. They must be entered the same across each school where they have a child. One of the most common problems occurs when you have the same person but their names don’t match across school database:
Example One: Nicknames:
1. Charles Taylor
2. Chuck Taylor
3. Charlie Taylor
Make a district decision about which name should be in the database.
Example Two: Middle Names Combined with First Name
1. Mary Wilson
2. Mary Ann Wilson
3. MaryAnn Wilson
Does the person go by both of their names? If they go by both names, determine if you should put the middle name in the middle name field (as #2) or put both names in the first name field (as #3) and correct it in each school in the district where the name is.
Example Three: Changed Names
1. Valerie L. Wilson (Maiden Name)
2. Valerie Wilson Smith (Married Name with Maiden Last Name as Middle Name)
3. Valerie L. Smith (Married Name with Maiden Middle Name as Middle Name)
You may need to contact the guardian to determine the correct legal name and standardize across each school.
ADDRESSES:
If your student and their guardians do not have a phone number, addresses will be used instead. However, even if phone numbers are present, you still need to clean your addresses. If you do not, a household may be built correctly, but you will end up with multiple primary addresses in Infinite Campus.
Example One: Federal or State Routes are indicated on one address but not the other.
v 400 US Hgwy 27
v 400 Highway 27
v 400 Highway 27N
Example Two: The street goes by multiple names.
v 821 Main Street
v 821 Highway 62
v 821 Highway 27
v 821 Highway 62/27
v 821 Highway 27/62
Example Three: Rural Routes
v Rural Route 23
v RR 23
v Rural Rt 23
v Route 23
In each of these examples, one address must be selected and entered the same across all schools where it occurs.
If you have street abbreviations, these will be converted to Infinite Campus. The KDE conversion will send the exact entry from the mailing and physical address fields from STI over to Infinite Campus for converting into the IC site. Infinite Campus then runs the address information through their process and makes the following updates to the data. If the addresses in your district / schools do not match the abbreviations listed on the right, then the data will be converted as is.
Street tags that are found in the data (listed on the right of the arrow) will get converted to a consistent street tag (listed on the left of the arrow). Capitalization does not matter (case-insensitive), but exact spelling does matter for matching.
· Ave <-- Av Ave Aven Avenu Avenue Avn Avnue
· Blvd <-- Bl Bld Blv Blvd Boulevard Boul Boulv
· Cir <-- Cir Circ Circl Crcl Crcle Cr Circle
· Ct <-- Ct Court Crt cour
· Cres <-- Cre Cres Crescent Crecent Cresent Crsent Crscnt Crsnt
· Dr <-- Dr Drive Drv driv dri
· Ln <-- La Ln Lane Lanes lan
· Park <-- Park pk Prk
· Pkwy <-- Parkway Parkwy Pkway Pkwy pky
· Pl <-- place pl
· Rd <-- road rd roa ro r
· Rte <-- rte route
· St <-- St Str Street Strt stree stre
· Ter <-- Ter Terr Terrace
· Trl <-- Tr Trail Trails Trl Trls trai tl
· Trce <-- Trace Traces Trce trc
· Tpke <-- Tpk Tpke Trnpk Turnpike Turnpk
· Pike <-- Pike
· Way <-- way wy
For po boxes, anything that starts with (case-insensitive):
· PO<space>
· P<space>O<space>
· P.O
· P.<space>O
and has the last word in the address as all numeric digits, gets converted to a PO Box. The last part becomes the box number, if it is a number.
Example:
Input PO box number
------------------------- -------------
PO Box 123 123
P O 456 456
p.o. box 789 789
P. O. 1234 1234
PO Box 123, Apt 9 9
PO Box 123, 456 Main Str
The last one doesn’t get converted to a PO Box-style address, because the last part isn’t a number.
III. Results
The following are case studies in building households.
Example One
In this example, the guardian (Charles) has three children (Nancy, Mark and Wayne) and each child is enrolled at a different school in the district. The guardian has different names at different schools across the same district.
1. Lincoln Elementary School-Guardian Record
Charles Taylor, 500 Richwood Boulevard, Frankfort, KY 40601, (502) 555-1000.
Lincoln Elementary School-Child
Nancy Taylor, 500 Richwood Boulevard, Frankfort, KY 40601, (502) 555-1000.
2. Lincoln Middle School-Guardian Record
Chuck Taylor, 500 Richwood Boulevard, Frankfort, KY 40601, (502) 555-1000.
Lincoln Middle School-Child
Mark Taylor, 500 Richwood Boulevard, Frankfort, KY 40601, (502) 555-1000.
3. Lincoln High School—Guardian Record
Charlie Taylor, 500 Richwood Boulevard, Frankfort, KY 40601, (502) 555-1000.
Lincoln High School-Child
Wayne Taylor, 500 Richwood Boulevard, Frankfort, KY 40601, (502) 555-1000.
The result in the conversion will be as follows: A household will be built based upon the home phone number (502) 555-1000. So one household will be built with six members although there are only four members in the household. The district must make a decision on what name they want the guardian to be and make it uniform across the three school databases. For example, if the district selects Charles as the correct name, then Lincoln Middle School and Lincoln High School will need to change the guardian name in their databases. If the data is corrected at Lincoln Middle and Lincoln High, one household will be built with four members with Charles as the guardian.
Example Two
In the following example, the guardian (Charles) has three children (Nancy, Mark and Wayne) and each child is enrolled at a different school in the district. The guardian has a different phone number at the elementary school than at the middle and high schools.
1. Lincoln Elementary School-Guardian Record
Charles Taylor, 500 Richwood Boulevard, Frankfort, KY 40601, (502) 555-1009.
Lincoln Elementary School-Child
Nancy Taylor, 500 Richwood Boulevard, Frankfort, KY 40601, (502) 555-1000.
2. Lincoln Middle School-Guardian Record
Charles Taylor, 500 Richwood Boulevard, Frankfort, KY 40601, (502) 555-1000.
Lincoln Middle School-Child
Mark Taylor, 500 Richwood Boulevard, Frankfort, KY 40601, (502) 555-1000.
3. Lincoln High School—Guardian Record
CharlesTaylor, 500 Richwood Boulevard, Frankfort, KY 40601, (502) 555-1000.
Lincoln High School-Child
Wayne Taylor, 500 Richwood Boulevard, Frankfort, KY 40601, (502) 555-1000.
The result will create two households. The first household will have Charles Taylor alone because the phone number at Lincoln Elementary School does not match any other number in the database. The second household will have Charles, Nancy, Mark and Wayne because the phone numbers are the same.
Example Three
In the following example, the guardian (Charles) has three children (Nancy, Mark and Wayne) and each child is enrolled at a different school in the district. The child at the middle school has a different address due to a typo entered into the legacy system.
1. Lincoln Elementary School-Guardian Record
Charles Taylor, 500 Richwood Boulevard, Frankfort, KY 40601, (502) 555-1000.
Lincoln Elementary School-Child
Nancy Taylor, 500 Richwood Boulevard, Frankfort, KY 40601, (502) 555-1000.
2. Lincoln Middle School-Guardian Record
Charles Taylor, 500 Richwood Boulevard, Frankfort, KY 40601, (502) 555-1000.
Lincoln Middle School-Child
Mark Taylor, 509 Richwood Boulevard, Frankfort, KY 40601, (502) 555-1000.
3. Lincoln High School—Guardian Record
CharlesTaylor, 500 Richwood Boulevard, Frankfort, KY 40601, (502) 555-1000.
Lincoln High School-Child
Wayne Taylor, 500 Richwood Boulevard, Frankfort, KY 40601, (502) 555-1000.
The result will be one household because all the phone numbers match for all the students across the district and the guardian has the same first and last name across the district. However, the household will have two addresses.
IV. SQL Queries
KDE has developed SQL queries to run against your district STI back-up. These SQL queries will help you target which data you may need to clean. It is highly recommended you run these queries. The queries will help you identify mismatched telephone numbers between guardians and students, name and address inconsistencies and schedule gaps. To request these queries, please contact Paige Patterson Grant via e-mail at . The results can be imported to an Excel spreadsheet for review. Please note: the results do not necessarily indicate there is a problem with the data—it will need to be reviewed by the district to determine whether it is a problem and truly needs to be changed.
After you have run your SQL Queries, you may also want to run the STI Census report and sort by address in order to find address discrepancies. You can also sort by names and phone numbers; however it is recommended by KDE to concentrate on the results of the SQL queries first for your phone number and name inconsistencies.
V. Staff/Guardians
If you have a guardian who is also a staff member in your district, this may create duplicate households. Please note the steps below and follow them accordingly.
a. The employee records are pulled from STI, and the Social Security Number is used to match the person with their Munis record.
b. If a Munis record is found for the teacher/admin, then their Munis data is used to populate the address and phone info.
c. Their Munis first name and last name are also used as the converted name.
d. The Social Security Number has to match between STI & Munis.
e. If the record only exists in STI, then we only use STI data for name, address and phone.
f. If there is not an employee STI record, then the employee data is not converted.
g. If the names do not match between Munis and STI, then 1 person record will be created for this social security number using the Munis name.
However, if this employee is also a guardian, then you will want to have the exact first and last name and phone number for the STI guardian info as is in Munis so that only 1 person record is created for them within Infinite Campus.
Therefore, it would convert best by having the names exactly the same between Munis and STI.
VI. Data Mapping
Another tool available for you is the STI to Infinite Campus Data Map. This map will help you identify which fields in STI correspond to which fields in Infinite Campus. You can download the data map by going to KDE’s website and following this link: http://www.education.ky.gov/KDE/Administrative+Resources/Data+and+Research/Student+Information+System/Data+Clean-Up+and+Conversion/
VII. Trial 1 & Trial 2 Data Review
Be prepared at your data reviews! Have examples of at least ten normal students from each school and check how they converted into Infinite Campus. In addition to bringing these standard examples, KDE also recommends that you bring the following data examples with you to the Trial 1 & Trial 2 Data Reviews: