Intros… Troubleshooting by making your data work for you

1. Intros… Troubleshooting by making your data work for you • Phil Bettendorf / Level Data • Background: Teacher, PowerSchool Admin, Network Admin, programmer, systems engineer

2. Agenda Troubleshooting by making your data work for you • Part 1 • The Academics of Troubleshooting • Part 2 • Applying Academics of Troubleshooting to Powerschool

3. TopicsTroubleshooting by making your data work for you part 1 • Assumptions • Background • What makes a good troubleshooter? • Skillsets • Priorities • Troubleshooting traps • The Troubleshooting Pie • Troubleshooting ABCs • Powerschool Toolkit • Resources

4. Assumptions… part 1 • You are the person responsible for administration of Powerschool… • You have access to Pearson’s Powersource • You are a member of your state’s Powerschool User Group • You have Favorites links to Powerschool resources across the country • You network with other District Powerschool administrators

5. Data Power School Teachers Admin Electronic Gradebook Web based Parents http://your URL/admin Web based http://your URL/public Subs Teachers Web based Web based http://your URL/subs http://your URL/teachers Background…Scope part 1

6. Background… part 1 Eleven Tips for Troubleshooting Software* Things to do before you call a tech-support hotline http://www.techsoup.org/learningcenter/software/page6149.cfm ______________________________________________________________ * techsoup.org - The Technology place for Non-Profits “Your computer software — be it a Web browser or a donor database application — likely plays a crucial role in your nonprofit's day-to-day operations. So when software problems such as unexplained crashes and strange error messages begin to occur, your workday can unceremoniously come to a silent (or not-so-silent) standstill.”

7. Background (contd)… part 1 There is no “Easy Button” when it comes to troubleshooting!

8. What makes a good troubleshooter? part 1 • Time • Patience • Tenacity • Ability to prioritize • Technical skills

9. Skillsets part 1 • Powerschool application knowledge • Ability to obtain and process data to reach a conclusion • Internal to product • DDE • DDE / Match Function • External to product • ODBC • Excel • Pivot Tables

10. Priorities part 1 Consider your audience (teachers/admin staff/parents/students), and then apply the following rules to determine when action needs to be taken depending on which audience is impacted… • Priority categories and examples of what might cause the categorization • Critical • Operations is at a standstill • High • A process is impacted that must be completed within hours • Medium • An operational requirement cannot be completed due to lack of knowledge or training • Low

11. Troubleshooting traps… part 1 Don’t get stuck in the “troubleshooting bubble”!!! “The solution was simple, and one I usually try as a first step, as should everyone. This time, it never crossed my mind, as I wrongfully assumed the user would also try this method as a first step. The short story, his software was out of date, and simply needed to be updated. Never crossed my mind to even ask. Sometimes I get stuck in a troubleshooting bubble, this happened to be one of those times.” http://osxhelp.com/an-important-lesson-on-troubleshooting-software-applications/

12. Importing Oracle Reporting Powerschool application stability Exporting Importing Functions withinThe Troubleshooting Pie part 1 Powerschool (whole pie)

13. Understanding “ingredients” to the pie… part 1 The “binding agents” that connect all the pieces of the pie are as follows: • Schema • Application logic / business rules • Data input from users Without any one of these “binding agents”, the application we know as Powerschool would be useless. When one (or any combination) of the items is not functioning as intended, that is when we get the call!

14. Troubleshooting ABCs part 1 A – Outside forces and processes (rule out and remediate if necessary)… • Prior to the issue, have any major changes been made to: • Powerschool (update, customization or external system interfaces)? • The network? • The firewall? • The Powerschool server? • Has this issue been reported by any other users? • Have Powerschool processes NOT been followed as outlined in Pearson documentation?

15. Troubleshooting ABCs part 1 B – Powerschool known issue or application defect (rule out and remediate if necessary)… • If a Powerschool function/process is not completing as expected, verify with Pearson (call or look up in Powersource) that the function/process is not experiencing a known issue or application defect.

16. Troubleshooting ABCs part 1 C – Setup wrong/incomplete or Data integrity (rule out and remediate if necessary)

17. Troubleshooting part 1 Expertise… 1st year 2nd year 3rd year 4th year Nth year Timeframe towards SME can be compressed… Beginner Time consumption high Dependant Experienced Time consumption medium Moving towards independance Subject Matter Expert (SME) Time consumption low Independant

18. Next Step… Data Mining… part 2 • The remainder of the session will cover data mining techniques to assist in troubleshooting and will be made up of: • Issues commonly experienced • Troubleshooting issues in your server (if time and access allows) • This class can only research known issues, application defects, Setup issues or Data integrity issues. Environment issues cannot be researched in this class.

19. Powerschool toolkit for admin troubleshooting part 2 • Powerschool server • System services • System log • Other • Pearson’s Powerscource, User Groups, Google • Powerschool application • System log within Powerschool • DALX log • DBlog • Knowing how to decipher table log records (limited to a subset of tables) • Data • Correlation • Validation reporting and remediation • Special character identification and remediation

20. Powerschool toolkit…Powerschool application – System Log part 2

21. Powerschool toolkit…Powerschool application – DALX Log part 2 • Oracle transactions can be viewed in Powerschool’s system log, but it requires use of the following URL (Place the text after your IP/admin): tech/log/viewsystemlog.html?filename=dalx.log.0&maxlines=500&mode=highlight&anchor=67388017&direction=next • The items in red are the items that can be changed. • Maxlines tells Powerschool how many rows to return • Highlight&anchor is just a number that is a record number within the log. You will have to play with this number to move around the log. • DO NOT USE THE SYSTEM LOG NAVIGATION BUTTONS WHEN USING THIS APPROACH TO VIEW THE DALX LOG.

22. Powerschool toolkit…Powerschool application – DALX Log part 2 The screenshot at right shows a Stored Grades process under way… If there were any issue with the Stored Grades process, it would clearly be spelled out in the DALX log… Any issue that is Oracle based, will be accompanied by an ORA – XXXX error. Look up the error in cyberspace.

23. Powerschool toolkit…Powerschool application – DBLog part 2 • The DBlog table is a school year long transaction table of system initiated and user initiated transactions with the database. Where users interact with the database, the UserID is recorded so it can be used as an Audit log. Export the records and Pivot them in Excel.

24. Powerschool toolkit…Powerschool application – DBLog part 2

25. The following string is written to each Stored Grade. It is used to have a trace of who did what to the Stored Grade. [01/23/2008:08:45:51-u0-s2106]Created by store grades UserID SchoolID Powerschool toolkit…Powerschool application – Decipher log records part 2

26. Powerschool toolkit…Powerschool application – Data correlation part 2 • When exporting data from Powerschool, export related table data. Related table data puts the export table data in context and many times leads one to a more complete understanding of the data (at a minimum) and an answer to a problem (best case). • Where do you obtain related table links? Data Dictionary of the DDE lookup tool offered at MI PSUG High School. The following three screens will show you some examples of export maps that can be used in data correlation…

27. Attendance analysis • The export map below is used from the Attendance table and will assist you in troubleshooting attendance data. Powerschool toolkit…Powerschool application – Data correlation part 2

28. Teacher schedules • There are times when you need to verify a teacher’s schedule for a school or an entire District. This Section table export will provide that data for Excel for analysis. Powerschool toolkit…Powerschool application – Data correlation part 2

29. Student FTE analysis • To fully understand FTEs for a given school or district, the export map below from the Student table will provide an insight into the FTEs defined. Powerschool toolkit…Powerschool application – Data correlation part 2

30. Correcting Invalid Data Using Data Validation Reports KB 54710 Issue: When using PowerSchool Premier 5.1.1 or above with PowerTeacher Gradebook, certain database transactions may not complete due to erroneous data violating certain data validation rules that exist between the tables used by PowerTeacher (Milan) and those used by PowerSchool Premier, resulting in the following errors: In Browser: "The system encountered an error while accessing the database. Please contact your system administrator." In PowerSchool logs: "Description=Milan sync error: DALX-Milan Atomic Sync Failure" Data may also be missing from the PowerTeacher Gradebooks Powerschool toolkit…Powerschool application – Validation reporting and remediation part 2

31. Powerschool toolkit…Powerschool application – Validation reporting and remediation part 2 • When should Validation reporting be run? • After each term “settling in” timeframe. • Due to the transient nature of students for a period of time after term start dates, there is a potential for issues with data related to the teacher gradebook. Regular running and remediation of data with Validation Reports will assure your database remains as trouble free as available tools will allow.

32. Powerschool toolkit…Powerschool application – Special character identification and remediation part 2 How to locate special characters KB 57565 Special characters are known to cause various issues including problems with printing reports, viewing data in the PowerSchool application, and viewing data in PowerTeacher Gradebook. We have provided a tool that may help identify special characters in several known problem areas. The utility identifies problems with special characters in tables and fields which have been identified to have problems.

33. Identified tables and fields • Selections Table • (Stored Selections) • a. Name • 5. Students Table • a. GuardianEmail • b. Father • c. LastFirst • d. Mother • Teachers Table • (All Staff members) • a. LastFirst • 1. Courses Table • a. Course_Name • 2. PGAssignments Table • (Gradebook Assignments) • a. Name (Assignment Name) • 3. Schools Table • a. Name (School Name) Powerschool toolkit…Powerschool application – Special character identification and remediation part 2

34. Powerschool toolkit…Powerschool server – System services part 2

35. Powerschool toolkit…Powerschool server – System services part 2

36. The system log on the Powerschool server can be a wealth of information… Powerschool toolkit…Powerschool server – System log part 2

37. Powerschool toolkit…Other – Server Statistics – Process Status part 2

38. An example of a process Detail Call chain is below… Think of it like Task Manager for Windows… • The word “Semaphore” will be encountered… A Semaphore is a logical resource block that allows processes to work on data in order so that there are no conflicts… “Waiting for Semaphore” simply means the system is waiting for another process to release access to the data… Powerschool toolkit…Other – Server Statistics part 2

39. Powerschool toolkit…Other part 2 • Powersource, PSUG, Google

40. A – Outside forces and processes (rule out and remediate if necessary)… B – Powerschool known issue or application defect (rule out and remediate if necessary)… C – Setup wrong/incomplete or Data integrity (rule out and remediate if necessary) Practical example A school is running the Web based PowerTeacher gradebook. Teachers can log into PowerTeacher, but cannot launch the Web gradebook. Why not? Apply the Troubleshooting ABCs

41. Resources • Excel Pivot table • http://www.ozgrid.com/Excel/PivotTables/ExCreatePiv1.htm • Informing Practices and Improving Results with Data-Driven Decisions • http://www.nsba.org/site/docs/9200/9154.pdf