search

American-School-of-the-Hague / psd2l-plugin

PowerQueries for PowerSchool SIS for use with Data Export Manager (DEM)
GNU General Public License v3.0
3 stars 3 forks source link

readme

PowerSchool-D2L Plugin

This plugin creates CSV files compatible with D2L IPSIS for managing student, staff, guardian and course synchronization from PowerSchool to Brightspace.

Feb-June 2022 : Aaron Ciuffo : aciuffo@ash.nl : aaron.ciuffo@gmail.com

Data Flow and Integration

Data is exported from PowerSchool SIS (PS) using the PowerSchool Plugin structure and imported to Brightspace using IPSIS. Plugins contain PS/SQL queries and are executed using the scheduled Data Export Manager functionality in PS. More information regarding the structure of the plugins can be found below.

Additional Tools

Several tools are provided by this repo to help package PowerQuery plugins and manage IPSIS imports.

Package PowerQuery Plugins

package.sh: create a .zip file that is appropriately structured for upload into PowerSchool's plugin interface. The script will append the current version sourced from the version string in PackageDir/plugin.xml and the date and time.

Usage:

package.sh ./path/to/directory

Example

$ ./package.sh BS_Organization
~/Documents/src/PowerQuery/BS_Organization ~/Documents/src/PowerQuery
  adding: permissions_root/ (stored 0%)
  adding: permissions_root/05_offerings.permission_mappings.xml (deflated 33%)
  adding: permissions_root/02_departments.permission_mappings.xml (deflated 33%)
  adding: permissions_root/05_offerings_ath.permission_mappings.xml (deflated 33%)
  adding: permissions_root/01_template.permission_mappings.xml (deflated 33%)
  adding: permissions_root/04_templates.permission_mappings.xml (deflated 33%)
  adding: permissions_root/06_sections.permission_mappings.xml (deflated 33%)
  adding: permissions_root/03_semesters.permission_mappings.xml (deflated 33%)
  adding: plugin.xml (deflated 39%)
  adding: queries_root/ (stored 0%)
  adding: queries_root/01_template.named_queries.xml (deflated 66%)
  adding: queries_root/05_offerings_ath.named_queries.xml (deflated 68%)
  adding: queries_root/05_offerings.named_queries.xml (deflated 68%)
  adding: queries_root/06_sections.named_queries.xml (deflated 70%)
  adding: queries_root/04_templates.named_queries.xml (deflated 66%)
  adding: queries_root/02_departments.named_queries.xml (deflated 65%)
  adding: queries_root/03_semesters.named_queries.xml (deflated 70%)

CREATED PLUGIN: BS_Organization-V1.2.03-20230227_081756.zip

SFTP Script for PowerSchool Server

The CSV files created by the PowerSchool Data Export Manager (DEM) need to be sent to D2L via SFTP as a single zip file. The .zip file must:

The run_SFTP_BS.bat can be run from the Windows OS that hosts the PowerSchool instance as a scheduled job. The script hard-codes the username and password for the D2L SFTP service. The username and password can be found in the IPSIS configuration screen.

Comparison and Validation Script

To ensure that an upgrade to the PowerSchool system does not result in major changes to the IPSIS CSV files, the following procedure is recommended:

  1. Prior to the PowerSchool upgrade, switch IPSIS integration to Mode: Validate from the IPSIS Administration screen.
  2. Obtain an IPSIS .zip file from the day prior to the upgrade and the day after the upgrade. The last seven days of IPSIS imports are stored on pkg.ash.nl/Downloads.
  3. Use the bspace_comparison.sh script to compare the two files.

The bspace_comparison tool compares two zip files that contain similar sets of IPSIS exports. If any of the archives have a change delta of more than 10%, the file will be flagged.

In the event a file is flagged, the related export and PowerQuery should be investigated to find the source of the change.

Implementation Notes

Automated exports are managed through PowerSchool PowerQuery Plugins. Plugins follow the structure outlined below.

Each CSV Export for Brightspace is managed through an individual plugin. Each plugin contains an SQL query that matches the required fields for the CSV. See the Automated Exports from PSL to Brightspace section for more information.

Important Implementation Choices

Parents & Guardians

Two different methods for enrolling parents are provided BS_Users_Enrollments and D2L_Users_Enrollments. BS_Users_Enrollments relies on contact information stored in the U_STUDENTSUSERFIELDS view. As of February 2024 this data will be deprecated. The D2L_Users_Enrollments plugin uses the new unlimited contacts data in PowerSchool.

Parents & Guardians are enrolled in Brightspace with the following roles:

Parent & Guardian

Provides access to:

Learner

Provides access to:

ASH Staff/Parents

Some ASH staff that are also parents used their @ash.nl email address as their parent contact information. This caused collisions in between the Parent Role and the Teacher role when users were created.

To remedy this, ASH staff usernames are set to the username portion of their @ash.nl email address. Staff sign in to Brightspace using Google SSO. The teacher username is only used in the event that SSO fails for a user.

Examples

PowerSchool Setup and Installation

PowerQuery Plugin exports are scheduled through the Data Export Manager (DEM) in PowerSchool > My Templates. Create the template using the instructions under the Data Export Manager Setup section for each plugin in the documentation.

It is critical that the exact filename listed under Export File Name is used. The order in which the files is processed is important and governed by the filenames.

SIS Installation

  1. Download plugins from this GIT Repo; each plugin is stored as a .zip file
    • See list below for all plugins and their fuctions
  2. Install plugins throught the Plugin Management Dashboard:
    • Start Page > System Administrator > System Settings > Plugin Management Dashboard
    • If the plugin is already installed either choose to Update or Delete and reinstall using the .zip files
  3. Configure a template for automated export in Data Export Manager
    • Start Page > System Administrator > Page and Data Management > Data Export Manager

Data Export Manager Configuration

Start Page > System Administrator > Page and Data Management > Data Export Manager

Each plugin needs to be configured to produce CSV files with the appropriate data, character set and column headers. Each plugin documents the structure and settings under the Data Export Manager Setup heading. See the README.md in each plugin directory for more details.

The basic settings are as follows:

  1. Select Columns to Export:
    • Category: Show All
    • Export From: NQ com.txoof.brightspace.table.area (see the DEM section in each readme)
    • DEM Screen 00
  2. Select all of the fields:
    • DEM Screen 01 - Fields
  3. Remove the TABLE. portion in the Labels used on Export for every column (highlighted in blue/red) and click Next
    • DEM Screen 02 - labels
  4. On the following Select/Edit Records from NQ - com.txoof.brightspace.table.area screen click Next
    • No filtering should be needed
  5. On the following Export Summary and Output Options screen set:
    • Export File Name: See README.md for filename format (e.g. 1-Other.csv)
    • Line Delimiter: CR-LF
    • Field Delimiter: Comma
    • Character Set: UTF-8
  6. Click Save Template; see the README.md for each plugin for specifics
    • Name: 1-Other-%d.csv Export
    • Description: Updated: YYYY.MM.DD
  7. Click Save as New

Automated Exports from PSL to BrightSpace

After templates are created, they need to be scheduled to run using the Scheduled Systems Templates interface in the Data Export Manager screens. Note: PowerSchool users can only schedule templates that they have created. It is not possible to see other users templates.

To schedule a template:

  1. Navigate to My Templates tab in the Data Export Manager screen.
  2. Click the calendar icon to the right of the template in the Actions column
  3. Set the schedule with the following settings:
    • Schedule Active: ☑
    • Days To Execute: Monday, Tuesday, Wednesday, Thrusday, Friday, Saturday, Sunday
    • When to Execute: Any time between 12:00AM and 4:00AM
    • Send Output To: PowerSchool Folder
    • Path: C:\Brightspace\Exports

DEM Scheduled Template

Data is uploaded to Brightspace via the IPSIS interface. The data upload is managed from the PowerSchool Windows server using a scheduled task and is executed via a windows BATCH file.

The automated uploads should be scheduled at the following times:

The batch file depends on the following software:

Important IPSIS Notes

Though it is possible to create ORG Units such as Schools, Departments, Templates and Courses by hand within the Brightspace ORG Unit editor interface, IPSIS cannot access these ORG Units. IPSIS maintains its own internal records of ORG Units created and removed based on the imports that are processed vai the IPSIS tool (both by SFTP and web upload).

This means that creating a School ORG Unit with code 999 manually through the ORG Unit editor cannot be referenced an enrollment .csv upload (e.g. 08_e_s_school.named_queries.xml).

To resolve this, a .csv must be uploaded via IPSIS that contains a row that references the new name and code (see below)

01-Other.csv

action,code,custom_code,department_code,end_date,is_active,name,offering_code,semester_code,start_date,template_code,type
UPDATE,999,,,,,New School,,,,,school

IPSIS Upload

BrightSpace accepts imports via IPSIS. IPSIS expects flat zip files with at minimum 2 files: 1 CSV and a manifest.json V2.0.

Files are sent to IPSIS via SFTP. Find SFTP details within the platform here. NOTE: the username and password in the batch_upload.bat file need to be updated to match the credentials in the IPSIS configuration screen

List of Plugins and Functions

Plugin Errors & Resolutions

See this GitHub Gist for common problems and resolutions.

Plugin Documentation

Updating a Plugin

The Named Queries (NQ) within each plugin can be updated by editing the associated xxx.named_query.xml file. No changes to the files in the permissions_root should be necessary unless a new NQ is added. See the Basic PowerQuery Plugin Structure section below for more information.

After updating the NQ, it is necessary to update the version number in the plugin.xml file. PowerSchool will complain during the upgrade process if the version number remains the same or regresses. Always increment the version number.

PowerSchool SIS is very particular about the package structure of the plugin zip file. Use the ./package script included in this repository to repackage the script. The package script will generate a new plugin with the current version number and the current time specified in the zip filename.

Usage: package.sh PLUGIN_DIR

Example:

$ ./package.sh BS_Organization

Reference Documentation

Basic PowerQuery Plugin Structure

Plugins must be zipped such that the plugin.xml file is at the root of the structure with no top level folder. Multiple Named Queries (NQ) can be specified within one plugin. Each NQ needs to be placed in the queries_root directory with an associated permission_mappings.xml placed in the permissions_root directory.

The MessageKeys and web_root directories are not used in these plugins and can be ignored.

|
+-- plugin.xml
|
+-- queries_root
    |
    *-- partner.module1.named_queries.xml
    *-- partner.module2.named_queries.xml
+-- permissions_root
    |
    *-- partner.module1.permission_mappings.xml
    *-- partner.module2.permission_mappings.xml
+-- MessageKeys
    |
    *-- example-plugin-message-keys.US_en.properties
+-- web_root
    |
    *-- admin
        |
        *-- home.partner.content.footer.txt

Sets access permissions for this query plugin. See this blog post.

<permission_mappings>
    <!--Anyone that has access to the following page can run this query-->
    <permission name='/admin/home.html'>
        <!--.../query/BASE_PLUGIN should be identical to `name` in named_queries.xml-->
        <implies allow="post">/ws/schema/query/BASE_PLUGIN</implies>
    </permission>
</permission_mappings>

IPSIS Import Errors and Solutions

Course Offerings

ERROR: Course Offerings.Parent org unit mapping not found

EXAMPLE IPSIS ERROR:
IPSIS Field Data
ParentSourcedId Templ3
RecordType CourseOffering
Operation Replace
SourcedId co_3_FHA1IBSSSS2

Suggested Resolution: PowerSchool SIS COURSES.SCHED_DEPARTMENT field is empty; add a department. Search in Start Page > School Setup > Courses for the Course Number to verify the issue. The course number is the last portion of the SourceID generated by IPSIS: co_3_[FHA1IBSSSS2].

Users

ERROR: User(s) could not be processed.ParentPortalDeleteRelationshipHandler - Unable to find user mapping, parents not updated

EXAMPLE IPSIS ERROR:
SourcedId P_577106

Suggested Resolution: This is likely due to a parent that does not exist and is being deactivated or deleted. The parent can be identified by the ID number. This can likely be ignored.

ERROR: User(s) could not be processed.No user mapping found for source system

EXAMPLE IPSIS ERROR:
SourceSystem 3
IMIdentifier P_577106
RecordType User
Operation Delete
SourcedId P_577106

Suggested Resolution: This is likely due to a parent that does not exist and is being deactivated or deleted. The parent can be identified by the ID number. Check the SourceID if this matches the previous error, this can likely be ignored.