PSUG National Information Exchange Users Helping Users slides

PSUG National Information Exchange Users Helping Users

slides: aete. ch/psug 2018 Automating Data Tasks Adam Larsen Assistant Superintendent CUSD #220 – Oregon, IL Principal Developer Aurora Educational Technology

Agenda slides: aete. ch/psug 2018 • What and Why • Getting started – Software options • Tips for reducing query maintenance • Archiving data – Cool projects • Power Query / Python examples • Putting it together to leverage data

About the trainer. . . slides: aete. ch/psug 2018 • BS (2004) Psychology, Iowa State University • MA (2007) Psychology, Northern Illinois University • Ed. D (In progress) Curriculum Leadership, Northern Illinois University • Former web developer and school psychologist • Assistant Superintendent for CUSD 220 in Oregon, IL – Coach principals in use of data – Supervise technology department – Primary administrator for Power. School • Marathoner, trumpet player, web developer, geocacher, national parks junkie, graduate student, backpacker

What is Automation? slides: aete. ch/psug 2018 • Scheduling reports • Reducing maintenance • Removing opportunities for human error • Archiving for future mining

Why should I Automate? slides: aete. ch/psug 2018 • Saves time • Reduces errors • Synchronizes reporting times • Provides opportunities for future analysis

How do I get started? slides: aete. ch/psug 2018 • What are you doing with the data? – Sending to a vendor • Does it need to be manipulated first? – Saving to a file for another software to import – E-mailing to specific people in the school

slides: aete. ch/psug 2018 When to Use Task Self-Hosted Remote Hosted Send file to vendor via SFTP Data Export Manager + Remote Connection Navicat + SFTP batch file Data Export Manager + Remote Connection Save file locally for other software to import Auto. Send Navicat Oracle SQLCL Data Export Manager Power. Query + Download Script using API E-mail report as attachment Navicat Power. Query + E-mail Script using API Importing Auto. Comm (only certain fields, but includes 1: 1 DBE) Script using API (slightly more fields, and includes 1: M DBE)

How do I get started? slides: aete. ch/psug 2018 • Data Export Manager + Scheduled Exports – Native feature of Power. School • Scheduled Exports is a special security role – Many useful datasets – All database extension tables – Power. Queries – Can filter results before exporting – Result files can be saved locally or uploaded to Managed Connections (Remote Connection Manager)

How do I get started? • psnavigator Oracle account – Configured in Power. School Installer • Software options – Oracle SQL Developer • oss. oracle. com/sqldeveloper. html • Good – Free – Can schedule jobs – No addition drivers needed • Bad – Requires JAVA – Only connects to Oracle slides: aete. ch/psug 2018

How do I get started? slides: aete. ch/psug 2018 • Inside Power. School – sql. Reports • sisresources. com (Matt Freund) • Good – – Embeds right into Power. School Easy for end-users to run your queries at will Can take user input when executed Scheduled jobs • Bad – Not a development environment » Still need to build your queries in something else – Limited output types

How do I get started? slides: aete. ch/psug 2018 • Software options (cont. ) – Navicat • Navicat. com (Premium. Soft) • Good – – Free lite versions available via search Can schedule jobs Connects to all major databases Very lightweight program – runs quickly • Bad – – Need to install Oracle OCI Drivers Non-commercial Premium edition is $599 Non-commercial Oracle-only edition is $299 Only the paid version can schedule jobs Available from auroraedtech. com

How do I get started? • Software options (cont. ) – SQL Server Reporting Services • Microsoft. com • Good – You may already license it – Feature-rich reporting suite – Can schedule jobs • Bad – Requires Microsoft overhead – Building reports can be complicated – Takes some fiddling to work with Oracle slides: aete. ch/psug 2018

How do I get started? slides: aete. ch/psug 2018 • Power. School Data Dictionaries – https: //support. powerschool. com/dir/5933 – (Start with Data Dictionary Tables) • Tech on the Net – http: //www. techonthenet. com/sql/index. php • W 3 Schools – http: //www. w 3 schools. com/sql

Data Export Manager Basics slides: aete. ch/psug 2018 • Select Power. Query or data set on Export tag – Write your own Power. Query and install as plugin • Save as a template • Click calendar icon on My Templates tab – Requires user to have security role with “User can set up and run scheduled system templates” checked – Create a separate role for this – Give role to specific users

Data Export Manager slides: aete. ch/psug 2018

Data Export Manager slides: aete. ch/psug 2018

Navicat Basics slides: aete. ch/psug 2018 • Connect to server over ODBC – VPN required if hosted • Write query • Create export template – File type – Save location • Create job – Attach multiple export templates to one job – Can attach results to email and send – Creates task in Windows Task Scheduler

Navicat slides: aete. ch/psug 2018

Reducing maintenance slides: aete. ch/psug 2018 • Date math – Current school year: • Get the current calendar year • Check the current month – If >= 7, add 1 – If not, add nothing • SQL: – SELECT EXTRACT(YEAR FROM SYSDATE) + CASE WHEN EXTRACT(MONTH FROM SYSDATE) >= 7 THEN 1 ELSE 0 END school_year FROM DUAL

slides: aete. ch/psug 2018 SELECT EXTRACT(YEAR FROM SYSDATE) + CASE WHEN EXTRACT(MONTH FROM SYSDATE) >= 7 THEN 1 ELSE 0 END school_year FROM DUAL

slides: aete. ch/psug 2018 SELECT EXTRACT(YEAR FROM SYSDATE) + CASE WHEN EXTRACT(MONTH FROM SYSDATE) >= 7 THEN 1 ELSE 0 END - 1991 yearid FROM DUAL

Reducing maintenance slides: aete. ch/psug 2018 • Date math – Graduation year: • Calculate the school year • Add 12 years • Subtract the student’s grade level – – Grade 12: 2018 + 12 – 12 = 2018 Grade 8: 2018 + 12 – 8 = 2022 Grade 3: 2018 + 12 – 3 = 2027 Grade K: 2018 + 12 – 0 = 2030 • EXTRACT(YEAR FROM SYSDATE) + CASE WHEN EXTRACT(MONTH FROM SYSDATE) >= 7 THEN 1 ELSE 0 END + 12 - students. grade_level

slides: aete. ch/psug 2018 SELECT students. lastfirst, students. grade_level, EXTRACT(YEAR FROM SYSDATE) + CASE WHEN EXTRACT(MONTH FROM SYSDATE) >= 7 THEN 1 ELSE 0 END + 12 - students. grade_level grad_year FROM students

Reducing maintenance slides: aete. ch/psug 2018 • Date math – Use to avoid updating literally-specified termid values – Theory: • Year. ID is the number of school years since SY 91 • For SY 19 (2018 -2019), 2019 – 1991 = 28 – Practical Example: • Bad: – WHERE sections. termid >=2800 • Good: – WHERE FLOOR(sections. termid/100) = EXTRACT(YEAR FROM SYSDATE) + CASE WHEN EXTRACT(MONTH FROM SYSDATE) >= 7 THEN 1 ELSE 0 END - 1991 – Caution: • Watch out if you use over summer school.

Reducing maintenance • School > Current Grade Display • Indicates what the grade of interest is • Pull this value into your query to eliminate periodic changes slides: aete. ch/psug 2018

Reducing maintenance slides: aete. ch/psug 2018 • Current Grade Display is stored in Prefs table [009] • Example: – School 1 is High School – School 2 is Junior High – School 3 is Elementary name Value curfgname-S 1 S 2 curfgname-S 2 Q 4 curfgname-S 3 Q 4

Reducing maintenance slides: aete. ch/psug 2018 SELECT students. lastfirst student, students. grade_level, grade, courses. course_name FROM pgfinalgrades JOIN students on pgfinalgrades. studentid = students. id JOIN sections on pgfinalgrades. sectionid = sections. id JOIN prefs on prefs. name='curfgname-S' ||sections. schoolid JOIN courses on sections. course_number = courses. course_number WHERE pgfinalgrades. finalgradename = to_char(substr(prefs. value, 1, 2)) and FLOOR(sections. termid/100) = EXTRACT(YEAR FROM SYSDATE) + CASE WHEN EXTRACT(MONTH FROM SYSDATE) >= 7 THEN 1 ELSE 0 END - 1991 Only if “current” grade Make sure we are looking at this year

Reducing maintenance slides: aete. ch/psug 2018 • Interfaces to other systems • Example (Library book checkout system): SELECT students. student_number students. lastfirst, cc. expression, courses. course_name, teachers. lastfirst FROM cc JOIN students ON cc. studentid = students. id JOIN sections ON cc. sectionid = sections. id JOIN courses ON sections. course_number = courses. course_number JOIN teachers ON sections. teacher = teachers. id WHERE sysdate between cc. dateenrolled and cc. dateleft Always returns current classes

slides: aete. ch/psug 2018 Removing error • Commenting Lines --winter • Comment out blocks of SQL for past/future use --activities. basketballb, --activities. basketballg, --activities. bowlingb, --activities. bowlingg, --activities. wrestling, --activities. academicteam, --activities. poms, --activities. cheercompetitive, --activities. band, --activities. choir, --spring • Also saves typing time --activities. drama, activities. baseball, activities. softball, activities. trackteamb, activities. trackteamg, activities. fishing, activities. ffa

Archiving • Create a network drive or folder • Save all automatic reports here • Give access to necessary staff • Quick access to past reports • Cut and paste into aggregate report slides: aete. ch/psug 2018

Archiving • Our example – X: drive • Data e. Xchange – Folders – Datestamped files slides: aete. ch/psug 2018

slides: aete. ch/psug 2018 Archiving Datestamped files

slides: aete. ch/psug 2018 DF Counts WITH DF_grades AS ( SELECT sections. schoolid, substr(pgfinalgrades. grade, 1, 1) grade, count(*) total FROM pgfinalgrades JOIN sections ON pgfinalgrades. sectionid = sections. id JOIN students ON pgfinalgrades. studentid = students. id join prefs on prefs. name='curfgname-S' || students. schoolid --preference for current grade term name JOIN ( SELECT schoolid, yearid FROM terms WHERE isyearrec = 1 AND sysdate BETWEEN firstday AND lastday ) current_year ON sections. schoolid = current_year. schoolid AND FLOOR(sections. termid/100) = current_yearid JOIN cc ON pgfinalgrades. sectionid = (cc. sectionid) AND pgfinalgrades. studentid = cc. studentid WHERE students. enroll_status <= 0 AND ( --current students only pgfinalgrades. grade = 'F' OR pgfinalgrades. grade LIKE 'D%' ) and pgfinalgrades. finalgradename = to_char(substr(prefs. value, 1, 2)) --D/F grades only --preference for current grade term name GROUP BY sections. schoolid, substr(pgfinalgrades. grade, 1, 1) ) SELECT to_char(sysdate, 'mm/dd/yyyy') week, (select total from DF_grades where grade = 'D' and schools. school_number = DF_grades. schoolid) D, (select total from DF_grades where grade = 'F' and schools. school_number = DF_grades. schoolid) F FROM schools where schools. school_number = 1

DF Counts Result Data (Appended every week) Week 8/19/2013 8/26/2013 9/2/2013 9/9/2013 9/16/2013 9/23/2013 10/7/2013 10/14/2013 10/21/2013 10/28/2013 11/04/2013 11/11/2013 11/18/2013 11/25/2013 12/02/2013 12/09/2013 12/16/2013 12/23/2013 12/30/2013 01/06/2014 01/13/2014 01/20/2014 01/27/2014 02/03/2014 02/10/2014 02/17/2014 02/24/2014 03/03/2014 D Count 3 51 135 141 192 184 209 215 201 208 225 223 225 231 248 240 243 267 263 272 108 150 160 191 198 230 219 F Count 7 136 130 138 126 109 122 107 114 111 112 123 108 107 101 93 103 77 78 73 52 137 209 216 200 177 168 164 slides: aete. ch/psug 2018

slides: aete. ch/psug 2018 Automated Export + Auto. Comm • High school targeted study hall (Hawk Time) – 9 th and 10 th: same HT teacher all year – 11 th and 12 th • Report to same HT teacher first day of week • No D or F: Go home second day and beyond • D or F: Assigned to HT with teacher for your lowest grade

slides: aete. ch/psug 2018 Automated Export + Auto. Comm • Query (7: 00 am on Monday) Query all 11/12 students with 1+ DF grade Query all of their current grades, sort lowest first Query list of all teachers with HT section Find the teacher of the lowest grade who also has a HT section – Export as a CSV – – • Monday enrollment • Tue-Thu enrollment (if needed) • Auto. Comm (7: 15 am on Monday) – Import CSV to CC table

slides: aete. ch/psug 2018 Automated Export + Active Directory + Auto. Comm Power. Shell Script 1. Query all active students via Power. Query 2. Check against students in AD 3. If new student (not in AD), create AD account, profile, home directory, permissions 4. E-mail teachers with student’s user and default pass 5. Export CSV data – – AD name Check box for LDAP authentication

Archiving Grades (Excel) slides: aete. ch/psug 2018

Archiving Grades (PS) slides: aete. ch/psug 2018

slides: aete. ch/psug 2018 Archiving Missing Assignments

slides: aete. ch/psug 2018

Automation with Python slides: aete. ch/psug 2018 • Python scripts can leverage API – Access to Power. Queries – Insert/Update/Delete data at certain endpoints – Works even for hosted customers • Run on any machine • Scheduled Tasks

Python example - Demographics slides: aete. ch/psug 2018 • Simple Demographics query installed as a PQ with a plugin • Python – Use Oauth ID and Secret to generate Bearer Token – Stream results to memory, save as CSV – Values are hard-coded in script but could be passed in as arguments • • server query name id secret

Re-Importing Archived Data slides: aete. ch/psug 2018 • Write query to snapshot the data – PSUG SQL classes • Automate execution and export – PSUG Automating Data Tasks (this class) – Navicat, sql. Exports, Python, Power. Shell • Build child tables in Database Extensions – PSUG Extended Schema classes • Import data – Manual, Auto. Comm, Power. Shell • Build visualization – Fusion. Charts

Why? slides: aete. ch/psug 2018 • Monitoring of school improvement goals – Data dashboards – Automated email reports • Connect teachers, parents, and students with data – Regular intervals – Historical lookback

Contact slides: aete. ch/psug 2018 Adam P. Larsen adam@auroraedtech. com @aplarsen http: //www. linkedin. com/in/aplarsen

slides: aete. ch/psug 2018 Session/Conference Feedback • Please complete the feedback on Guidebook • Help us improve PSUG

slides: aete. ch/psug 2018