Transcript PowerSchool Michigan State Reporting Extended Schema

PowerSchool Michigan State
Reporting Extended Schema
Project update status as of April 2015
What is extended schema?

In simple terms the project is to convert the State of Michigan custom fields
to new data structures in PowerSchool.

Since new database structures (tables) are created, the project is not a
simple conversion.


The opportunity exists to improve system capabilities and functionality in the
system design process.

Once the project is completed, major changes to the existing system design
requirements is a costly undertaking.
Michigan State Reporting updates, directed by Pearson, include the following:

Eliminate use of PowerSchool virtual tables, replacing the functionality when
appropriate. A virtual table is a special data structure in PowerSchool storing
student history, course history, etc.

Take the opportunity to re-write all Michigan State reports, using the new Pearson
approved reporting technologies.

Convert and update all the Michigan State data entry forms:

Update references to new extended schema table names.

Update data entry forms for PowerSchool enhancements in field level security and field
level validation.
What is an extended table?

Map the old custom field to a new field in a new related table.

Each mapping consists of one parent record; such as student, to one related
extended table record (S_MI_STU_GC_X).

The Michigan state reporting field names in the new table are different, as
directed by Pearson (IE: map MI_TSDL_Exclude to the new field name
excludeTSDL).

Pearson provides a migration utility, moving the data from the old custom field(s)
to the new field(s) in the table extension.

Once moved to a new table extension, references to the old custom field in
screens, reports, and queries retrieve the data from the new table extension
record instead. The re-mapping works properly in most cases, with one known
exception.

Multiple table extensions are allowed for the parent record. For example: a
student may have individual table extensions for adult education, early
childhood, etc.

If a simple data conversion project is desired, then using extended tables has
many advantages.
What is a child table?

A child table provides additional functionality that is presently available using different methods
(virtual tables, repeating fields names, etc.).

A child table delivers improvements to system capabilities and functionality.

Compare last year statistics to this year.

Was the student provided a service on a specified date?

Simplified field level validation for the programmer and PowerSchool user.

A child table maintains many records, associated with a single parent record (IE: students and related
contacts).

Since more than one record may exist for a single parent record, there is:

No migration from the custom field(s) to a child table data field(s).

No mapping in screens, reports, and queries, referring back to a migrated custom field name.

PowerSchool has useful tools to enter child table records in data entry forms.

The use of child tables is highly desirable, providing additional reporting capabilities.

In Michigan State reporting the capability to save student history has been discussed:

Students participating in a special program, effective as of a date, with related programs and services provided.


For example: The Civil Rights Data Collection (CRDC) would not require saving data as of the last CRDC collection period.
Adding extended records to PowerSchool special programs is a possibility. An extended record would need to be
dependent on the selected program name.
What is a standalone table?

Database tables are defined for storing data and querying data.

The table has no internal parent/child relationship administered by the
Oracle database.

The table is used for maintaining relationships of interest in the database.

Example 1:


Create a standalone table and maintenance screen for valid student contact
relationships (Neighbor, Grandparent, Sibling) is created. New contact types are
added to the table when the need arises.

The student/Contact record saves the contact relationship code, verifying the code
value specified exits in the standalone table.
Example 2:

Load the State of Michigan educational entity master data into PowerSchool for
data validation in state reporting.
Where are we in the project?

The Teacher-Student Data Link (TSDL) re-write has defined table extension records
for all the Michigan State supporting system tables:

Student/Course (CC), table extension name (S_MI_CC_TSDL_X).

Courses, table extension name (S_MI_CRS_TSDL_X).

Grade Scale Items, table extension name (S_MI_GSI_TSDL_X).

Schools, table extension name (S_MI_SCH_TSDL_X).

School Staff, table extension name (S_MI_SSF_TSDL_X).

Terms, table extension name (S_MI_TRM_TSDL_X).

The TSDL school year version for 2015 removes the use of virtual tables in system
setup.

Almost all of all the Michigan State Reports have been re-written for the 2015
school year, removing reference to virtual tables.

The report program student data field names presently refer to the student custom
fields.

In testing the new reports, unknown data problems are being revealed and corrected.

Special Oracle (database) date functions for Michigan State Reporting have been
written.

The TSDL re-write and Michigan State Reports re-write are all pending Pearson
State reporting approval.
What needs to be done in the project?

The Michigan State student data revision needs to be endorsed by Pearson.

After very careful deliberation and evaluation, the Macomb Intermediate
School District (MISD) is intending to stay with the present Michigan State
reporting system design capabilities, using student table extension(s).


The MISD performed a thorough assessment of the PowerSchool tools and utilities,
sharing the analysis with Pearson, in the decision making process.

The ability to store student state reporting data history is desirable, though not
mandatory.
The MISD and Pearson state reporting project team are examining the system
design decision, with forthcoming system enhancements proposed by Pearson.

The decision to use only table extensions can change, depending on further
research and direction from Pearson.

To keep things very simple, there may only be one table extension for Michigan
State reporting student data.
What can we expect to see in the near
future?

The Teacher-Student Data Link (TSDL) for the 2015 school year will be
released in July of this year. The TSDL release is dependent on not interfering
with the TSDL 2014 school year submission.

In September of 2015, the Michigan State student universal identification
code (UIC) refers to the PowerSchool student data attribute
“State_StudentNumber”.

It is expected the re-write of all the Michigan State Reports will be released
on or before September of 2015.

The reports may be referring to the “old” student custom field names, dependent
on project advancement in the coming months.

If report changes are released on or before September 2015, then please reserve
time for assessing the October 2014 general collection file.
State of Michigan custom field data migration
will appear this summer, with the student
data migration to follow.
Student data migration and enhancements

Custom field data will be converted to their proper data types: string
(alphanumeric), Boolean (yes/no), date, double (numeric with decimal point),
and integer (…,-2,-1,0,1,2,…)

There will be cases where a standard practice is examined and an alternative
solution is proposed:


Example: using proper student grade levels instead of having an education setting
override code.

Example: a general education FTE of spaces (blank) implies a value of 1.0

Example: include in MSDS is a value of 2 for no.

Example: how is Early On student versus an Early Child student identified for
submission?

Example: use of the state student number in place of the universal identification code
(UIC) as a custom field.

Example: allowing invalid data to be updated, showing an error message, instead of
inhibiting the screen from performing the update.
Any system procedure that requires mass updating of student data will be
reviewed to find an alternative solution.
Student State/Province Link

All student data entry forms will be re-written, conforming to HTML 5
standards.

Data entry forms designed and tested in Microsoft Explorer 11.

Java script and forms tested in Mozilla Firefox.

You may see spinner controls and slider controls just for fun.

Tool tips will contain the new extended schema data name, with the data
type and data size.

Combo boxes will display the code value and description, except when listing
student names, teacher names, or an alphabetic list is required (Example: (1)
English language arts).

Collapsible boxes will be used to hide sections that do not apply to the
student. Example: homeless, personal curriculum, immigrant funding,
program eligibility, exit status, initial IEP, transition services, multiple
assessments, multiple services, multiple placements.

Child table records are still a possibility.
Student State/Province Link: Field level
security

Present data entry forms support view only or update at the form level.

New input forms will support field level security on each data entry field,
using PowerSchool field level security.


Field level validation will be required to determine if a data entry field is allowed
to be updated, before a data input error message is displayed.

Managing the error messages with the field level security is a difficult requirement.
School districts have the ability to restrict data access to selected state
reporting data fields, by user group, on the input form.
Student State/Province Link: Field level validation

Data input forms will continue to display error messages, allowing data in form to be updated. This
feature is unique to the State of Michigan user community.

Error messages will continue to refer to the MSDS business rule number when it applies.

To provide better data integrity, some data entry errors will not allow the form to be saved, similar
to PowerSchool system screens. Each case will be reviewed and documented.

Selected state reporting data fields will have field level validation loaded where deemed
necessary; such as the field is required.


By applying field level validation rules, it is expected the validation rule will be applied to data import
utilities by PowerSchool in the future.

Most validation rules have data dependencies; therefore, a field level validation rule may not be inclusive.
The Michigan State business rule error messages will be coded, avoiding conflicts with PowerSchool
field level validation.

Extend schema tables apply default field level validation that can be maintained by school districts.

The PowerSchool field level validation rules inhibit the form from being updated, until the data is
corrected.

The MSDS business rules will continue to operate in the same manner as before, working in conjunction
with the PowerSchool field level validation rules.
Student State/Province Link: Field level validation

When enrolling a student the school district has the option of enforcing data
entry rules, using PowerSchool field level validation feature.

Michigan State data attributes


Example 1: the student resident county code must be entered (required).

Example 2: the general education FTE must be specified (required).

Example 3: the student resident membership code must be determined and
entered (required).
PowerSchool data attributes

Example 1: the student date of birth must be entered (required).

Example 2: the student gender must be entered (required).

Example 3: the district entry date must be entered (required).
Michigan State Extended Schema data
dictionary…

Is found on Power Source when approved by Pearson.

Or contact the Macomb Intermediate School District
(MISD) help desk for pre-release help.
Items to consider in your
extended schema upgrade

The migration of a custom field to a type of date is not
transparent. The default date format in Oracle queries is not in
American Gregorian format (MM/DD/YYYY). This requires
careful analysis in any reports, extracts, or queries using date
conversion functions.

Notify vendors that references to custom fields will change to
new table names and new data field names. The use of the
new extended schema table should improve database
performance and will eventually be a requirement.

Careful coordination installing screens and reports, referring to
extended schema data names, is necessary with migrating the
data beforehand.

Analyze the capabilities of PowerSchool import, export, and
maintenance utilities referring to extended schema tables. Any
repeated procedures that rely on mass updates of data fields
needs careful review.
Items to consider in your extended
schema upgrade


Child table records are very useful, but there is no data
migration to the new table. Consideration for when a child table
feature is put into operation is of importance.

Implementing a child table record at the beginning of the school
year may be most practical, when the data needs to be entered
anyway.

Installing a child table record in the middle of the school year,
converting the data, and retraining will add to complication.

There will be situations where converting the data to a child table,
changing data import forms, and retraining is necessary during the
school year.
Complete the consolidate users function before running the
teacher extended schema migration.