Extracting Useful Information with the PowerSight Kit

1. Extracting Useful Information with the PowerSight Kit Scott Kodai Distributed Learning California State University, Chico

2. Evolution • First focus was stability (Vista 4.0) • Next we looked at basic usage information • Number of concurrent users • VistaStats application • Then more detailed statistics • Count(*) queries on rpt_tracking • Even more detailed statistics • Processing each “hit” • Pivot tables, cubes and, eventually, a data warehouse

3. What is the PowerSight Kit? • A set of views in the database that are denormalized to make them easier to query • rpt_gradebook • rpt_learning_context • rpt_learning_context_size • rpt_member • rpt_person • rpt_template • rpt_tracking

4. Chico Specifics • In Spring 2009: • 17,810 students enrolled • 1,060 faculty enrolled • 4,067 sections with enrollments • A Vista section created for every section offered • Learning Context Hierarchy matches institution structure • A division for every college and a group for every department • Crosslisting • In Spring 09: 302 crosslists created, combining 711 child sections

5. Challenge of Crosslisted Sections • Division and Group for crosslist parents is special • Sometimes sections from different departments, or even colleges, are crosslisted • Tracking activity should be counted for the child section, but it’s recorded with the parent section’s LCID in rpt_tracking • This makes it necessary to do queries by enrollment, and not just by section

6. Basic Idea • Get a list of sections for the given term • For each section: • Get a list of enrolled students and faculty • For each enrolled person: • Query rpt_tracking for all “hits” for that person in that section • Process each record, adding to accumulators as you go • I used Perl hashes (and hashes of hashes) extensively • Output files for further analysis • I am not a programmer nor a DBA • TMTOWTDI

7. SQL Examples • Getting all sections in a given term • select tm.assigned_lcid, lc.source_id, lc.name, lc.description, lc.delivery_unit_type from webct.lc_term_mapping tm, webct.lc_term t, webct.learning_context lc where tm.lc_term_id = t.id and tm.assigned_lcid = lc.id and t.source_id = ?; • Why not use rpt_learning_context?

8. SQL Examples • Finding the parent section for a crosslisted child • select master_lcid from webct.xlist_lc_mapping where child_lcid = ?

9. SQL Examples • Finding the Division for a given section • select lc.name from webct.learning_context_index lci, webct.learning_context lc where lc.id = lci.left_lc_id and lci.hierarchy_level = 4 and right_lc_id = ?"; • Finding the Group for a given section • select lc.name from webct.learning_context_index lci, webct.learning_context lc where lc.id = lci.left_lc_id and lci.hierarchy_level = 3 and right_lc_id = ?";

10. SQL Examples • Getting a list of enrolled students for a given section • select distinct p.webct_id from webct.member m, webct.role r, webct.role_definition rd, webct.person p where m.id = r.member_id and r.role_definition_id = rd.id and m.person_id = p.id and m.learning_context_id = ? and m.delete_status = 0 and rd.name = 'SSTU' and p.webct_id != ?"; • Demo student id = “webct_demo_<lcid of section>”

11. SQL Examples • Getting a list of activity for a person in a section • select t.tracking_id, t.session_id, t.event_time_mil, p.webct_id, t.tool_name, t.action_name, t.page_name, t.dwell_time from webct.rpt_tracking t, webct.person p where t.person_id = p.id and t.learning_context_id = ? and t.event_time_mil >= ? and t.event_time_mil <= ? and p.webct_id = ?; • Event_time_mil = milliseconds since 1/1/1970 (stored in UTC)

12. Output Files • Summary_stats.txt • Summary_stats.csv • Faculty_list.csv • Student_list.csv • Detailed_stats.csv • Section_activity_stats.csv • Person_activity_stats.csv • Pageview_stats.csv

13. Answers… Or More Questions? • Certainly usage statistics provide answers to many questions • On the other hand, they often lead to more questions • How do you determine whether a person (faculty or student) is ‘active’? • Are hits or sessions a better indicator of activity? • How active is active? What is the difference between an ‘active’ section and an ‘engaged’ section? • Counting hits? Unique sessions? Tools used?

14. Active Users Total enrolled faculty: 1060 Total faculty (logged in at least once): 775 Total faculty with at least 25 hits/week avg: 418 Total faculty with avg one login per week: 630 Total faculty with avg three or more logins per week: 454 Total faculty with avg one or more logins per day: 256 Total enrolled students: 17810 Total students (logged in at least once): 16032 Total students with at least 25 hits/week avg: 11273 Total students with avg one login per week: 15130 Total students with avg three or more logins per week: 11642 Total students with avg one or more logins per day: 4300 Total students with avg 10 or more logins per day: 1

15. Avg Weekly Hits by Faculty

16. Faculty Use

17. Section activity Total Sections: 4966 Sections with > 0 enrollments: 4067 (includes 411 with only 1 student) Sections with Content: 2253 Sections with any activity: 2175 Sections with avg at least one login per student per week: 1465 Sections with avg at least three login per student per week: 103 Sections with avg one login per student per day: 47 Sections using 5 or less tools: 432 Sections using more than 5 tools: 1725 Average tools used per section (all sections): 4.25 Average tools used per section (tools used > 0): 9.79

18. Active vs. Engaged Sections

19. Tools Used

20. Third Party Powerlinks

21. Pivot Tables • Pivot tables in Excel are a powerful way to “explore” large amounts of data • Unfortunately, we’re dealing with way more data than Excel and Access can handle • Currently starting a project to move this activity data to our data warehouse • Overall goal is to maintain usage data across LMSs to enable longitudinal assessment

22. Questions? Scott Kodai www.aimlessmusing.com