Working With Data
- Data Mining
- Query Structure
- Field Names
- Comparators
- Special Groups
- Special Prefixes
- Compound Queries
Data Mining
PowerSchool provides multiple ways to obtain student data. Data mining is normally a choice between information on a single individual or a group of individuals sharing a common data point. Consider the following before attempting to retrieve data from PowerSchool.
- Does the information needed pertain to a group of students or a specific student?
Answering this question will help determine the method used to obtain data. If the information needed pertains to a single student, enter the student's last name or student number into the student search field, located on the main page in PowerSchool. However, if the information pertains to a group of students, the next consideration should be applied. - Is there a common data point shared between the students in question; if so, what is it?
Examples of commonly used student data points:
- Grade Level
- Home Room Teacher
- Live on the same street
- Enrolled or not enrolled in a specific class
- Receiving ESL Services
- Is of a specific race/ethnicity
- Once a commonly used data point is identified, determine if said data point is contained in a specific PowerSchool field.
Examples of commonly used data point fields contained within PowerSchool:
- Grade_Level - Students [001] table
- Ethnicity - Students [001] table
- Gender - Students [001] table
- Home_Room - Students [001] table
- Enrolled in specific course - Course_Number - CC [004] table
- Once the commonly used data point and the location of said data point is determined, consider how to obtain the required data set.
Datasets can be obtained in the following ways:
- Query the field name in the main page search bar
- Run a Stored Search
- Perform a MultiSelect from the main page
- MultiSelect is a free third party plug-in customization that can be obtained from PowerSchool's PowerSource.
- Utilize a pre-developed search code within the main page search bar.
- Utilize the Direct Data Export (DDE)
Query Structure
A query is a set of instructions entered into the search field on the PowerSchool main page. A query tells PowerSchool what the user is looking for and consists of three parts.
- Parts of a Query:
- The Field Name
- The Comparator
- The Search Argument
Example:
Locate every student whose gender is identified as Female.
- Field Name: Gender
- Comparator: =
- Search Argument: F
- Actual Query Text String: Gender = F
Queries can do the following:
- Provide answers to a simple question
- Perform calculations
- Combine data from different tables
- Add, change, or delete data from a database
Field Names
A list of PowerSchool fields can be found in the following areas:
- View Field List menu
- PowerSchool Data Dictionary
The difference between PowerSchool's unique student identifiers:
- Student_Number
- Generated according to preset creiteria set forth by the district
- Identifies each student in a district
- Is a unique number - no two students will be allowed to have the same number
- ID
- Generated by PowerSchool without intervention from the school district
- Can be found in the bottom center of a student's Teacher Comments page
- State_Studentnumber
- Generated by the State, according to the State's specified criteria
- Is unique to each student
- DCID
- Generated by Oracle
- Identifies students within extended database tables
Things to Remember:
- Field Names must be spelled correctly when querying, importing, or exporting data
- Field Names are not case sensitive however the data will not import or export if the following occurs:
- The underscore is omitted
- A field name is entered differently from what exists in the field list
- Words are misspelled
Comparators
A Search Command is a statement of search instructions and must contain the following elements:
- A Field Name
- A Comparator
- A Search Argument
Comparators are tools used to search and retieve records by combining two or more criteria. A combination of comparators and fields are used in searches to narrow the result to a very small group of studens
The following are some of the most commonly used comparators:
Comparator | Query | Returns |
---|---|---|
Is Equal To ( = ) *Searches for an "exact" match to the search argument |
Last_Name = Smith | Returns any currently enrolled student, in the selected school, whose last name is Smith. |
Less Than ( < ) *Searches for any match that is less than th search argument. |
Grade_Level < 11 | Returns all students, in the selected school, who are currently enrolled in a grade less than 11th. |
Greater Than ( > ) *Searches for any that is greather than the search argument. |
Grade_Level > 5 | Returns all students, in the selected school, who are currently enrolled in a grade greater than 5th. |
Greater Than ( > or Equal To = ) *Searches for all matches "greater than or equal to" the search argument. |
Grade Level >= 4 | Returns all students, in the selected school, are currently enrolled in grade 4 to 12. |
Less Than ( < or Equal To = ) *Searches for all matches "less than or equal to" the search argument. |
Grade Level <= 4 | Returns all students, in the selected school, are currently enrolled in grade PK to 4. |
Contains ( contains ) *Searches for all matches where the search argument is contained anywhere within the field. |
Street contains Main | Returns all currently enrolled students, in the selected school, whose street address contains the word "Main". |
Does Not Contain ( !contain ) *Searches for matches where the search argument IS NOT contained anywhere within the field. |
Street !contain Main | Returns all currently enrolled students, in the selected school, whose street address does not !contain the word "Main". |
In ( in ) *Allows for multiple search arguments contained within one query. |
Grade_level in 6,8,10,12 | Returns all currently enrolled students, in the selected school, who are in the 6th, 8th, 10th, and 12th grades. |
Is Not Null ( # ) *Searches for matches where the search argument has some type of physical value in the field. |
Alert_Guardian # | Returns all currently enrolled students, in the selected school, who have a Guardian Alert entered into PowerSchool./td> |
Is Not Equal To( # ) *Searches for matches where the search argument, contained within the PowerSchool field, equals anything other than the search argument. |
City # Madison | Returns all currently enrolled students, in the selected school, whose residential city is not equal to # Madison. |
Wildcard ( @ ) *Replaces unknown information in the search argument. |
First_Name = Mat @ | Returns all currently enrolled students, in the selected school, whose first name begins with Mat. Results could be Matthew, Matty, Mathias, Matteo, Mathusela, Mather, and Mataniah. |
Special Groups
In addition to simple field/value searching, special groupings can be utilized. A set of students can be selected using criteria that would be impossible to reproduce using simple field/value searching along with a significant amount of effort to produce using the "search in results" functionality.
Search | Who is Selected |
---|---|
transferred-in | All students who have been transferred into this school, but not yet re-enrolled. In database terms, this means that the "SchoolID" is for the current school, but the "Enrollment_SchoolID" is for some other school, AND that the "Enroll_Status" field is "2" ("Transferred Out"). The "/" prefix is implied in this search. This is the equivalent of searching "/Enroll_Status=2", "&/Enrollment_SchoolID#[my school number]". |
Special Prefixes
There are three search prefixes you may wish to use. A search prefix adjusts how the search is carried out.
Things to Remember:
- Search prefixes are always followed by a search term
- A search prefix is never valid all by itself
- When the Advanced checkbox is selected, some comparators do not function requiring the Within button to be used in its place
Prefix | Effect on Search Line |
---|---|
/ | Includes non-active students Normal searches are restricted to the school's actively enrolled student body Used to search across ALL students (active and inactive) Note: When transferring students in from another school, students who are no longer active may need to be included |
& | Search within results Example - To find all students transferred in after 4/10/2006, search first for "transferred-in", then search for "&/ExitDate>4/10/2006". Note that the "&" must come BEFORE any "/" prefix. |
+ | Add results of new search Example - To find all tenth graders and all ninth graders with the field "InstrLevel" set to "Advanced" search for "Grade_Level=9" and then "&InstrLevel=Advanced", and finally for "+Grade_Level=10". Note that the "+" must come BEFORE any "/" prefix. |
Compound Queries
A compound query allows multple queries to be used simultaneously by placing an AND(;) between the two querie
- Grade_Level>9;Grade_level<12 will return all students currently enrolled in the 10th and 11th grade.
In this example, Grade_Level>9 (Query 1) is joined by the ; (And comparator) to Grade_Level<12 (Query 2) returning all currently enrolled 10th and 11th grde students
Compound Query Examples:
Query | Returns |
---|---|
Grade_Level=9;Gender=M | All male students who are currently enrolled in Grade 9 |
Home_Room=Smith;Grade_Level>10;Grade_Level<12;Gender=M | All currently enrolled males, in 10th, 11th, or 12th grade, who are in Smith's homeroom |
*enrolled_in=H3011ES | Returns all students enrolled in Earth Science during any part of the current school year |
*enrolled_in=H3011ES.7;grade_level=11 | Returns all students enrolled in a specified section of Earth Science, during any part of the current school year,AND who are currently enrolled in the 11th grade |