Ron Santos

1
Stats all folks! Extracting usable statistics
from Blackboard Vista using the Powersight Module
and a little elbow grease

• Ron Santos
• Simon Fraser University

2
What will you learn in this session?

• Sample Graphs using the Powersight tables (e.g.
  course growth, student distribution, tool
  distribution)
• Sample SQL used to generate the reports
• Other uses (beyond graphs and stats)
• Perl modules
• Google Analytics
• Presentation available at http//get.sfu.ca/bbworl
  d09

3
Simon Fraser University

• Named after famous explorer
• Opened on September 9, 1965
• Located on British Columbia, Canada
• One University - Three campuses
• Burnaby
• Vancouver
• Surrey
• 900 faculty
• 1600 staff
• 100,000 alumni

4
Simon Fraser University

• Student Enrollments (Fall2008)
• Products used Peoplesoft, LDAP/CAS3, Blackboard
  Vista 8.0.2
• Blackboard/WebCT history
• CE3 CE4 CE6 Vista4 Vista8
• 2000 2003 2006 2007 2008
• Blackboard Vista setup
• 6 Managed nodes (1 protected JMS server)
• Oracle 10g (500GB)
• Sun Sparc Solaris 10

5
Course Growth Graph
SELECT source_name, source_id FROM
rpt_learning_context WHERE ims_value
'SECTION' AND source_id LIKE 'ltsemCodegt-'
sourced.id ltsemCodegt-ltcourseNamegt-ltcourseNumbergt
-ltcourseSectiongt (e.g. 1094-math-100-d100)
6
Number of Unique Students
SELECT COUNT (DISTINCT p.person_id) FROM
rpt_learning_context lc, rpt_member m,
rpt_person p WHERE lc.learning_context_id
m.learning_context_id AND p.person_id
m.person_id AND m.active 1 AND m.role
'SSTU' AND p.demo_user 0 AND lc.source_id LIKE
'semCode-'
sourced.id ltsemCodegt-ltcourseNamegt-ltcourseNumbergt
-ltcourseSectiongt (e.g. 1094-math-100-d100)
7
Course Levels Graph
SELECT COUNT() FROM rpt_learning_context WHER
E ims_value 'SECTION' AND source_id LIKE
'semCode--d1'
sourced.id ltsemCodegt-ltcourseNamegt-ltcourseNumbergt
-ltcourseSectiongt (e.g. 1094-math-100-d100)
8
Student Levels Graph
SELECT COUNT(DISTINCT p.person_id) FROM
rpt_learning_context lc, rpt_member m,
rpt_person p WHERE lc.learning_context_id
m.learning_context_id AND p.person_id
m.person_id AND m.active 1 AND p.demo_user
0 AND m.role 'SSTU' AND lc.source_id LIKE
'semCode--d1'
sourced.id ltsemCodegt-ltcourseNamegt-ltcourseNumbergt
-ltcourseSectiongt (e.g. 1094-math-100-d100)
9
Tool Distribution (RPT_TRACKING)
10
Other uses

• SELECT
• (SELECT lc2.name FROM rpt_learning_context lc2
  WHERE b.learning_context_id lc.parent_learning_c
  ontext_id) as Parent,
• lc.name,
• lc.source_name,
• lc.source_id
• FROM rpt_learning_context lc, rpt_member m,
  rpt_person p
• WHERE lc.learning_context_id m.learning_context_
  id
• AND p.person_id m.person_id
• AND m.role 'SDES'
• AND lc.ims_value 'SECTION'
• AND m.active 1
• AND p.source_name 'ltsourced.id_sourcegt'
• AND p.source_id ltsourced.id_idgt'

11
Perl modules

• Application level
• DBI - http//dbi.perl.org/
• DBI stands for database interface
• Allows Perl of running SQL queries
• TextCSVSimple - http//search.cpan.org/tmtm/T
  ext-CSV-Simple-1.00/lib/Text/CSV/Simple.pm
• Parser for CSV files
• GDGraph - http//search.cpan.org/bwarfield/GDGr
  aph-1.44/Graph.pm
• Graphing module for Perl5

12
Perl modules

• Database level
• DBDProxy driver -http//search.cpan.org/timb/DB
  I-1.609/lib/DBI/ProxyServer.pm http//docstore.mik
  .ua/orelly/linux/dbi/ch08_02.htm
• Module for implementing a proxy for the DBI proxy
  driver
• Allows PERL access to a database over the network
• DBI proxy architecture allows for on-the-fly
  compression of query and result data, and also
  encryption of that data. These two facilities
  make DBI a powerful tool for pulling large
  results sets of data over the network
• dbiproxy
• A proxy server for the DBDProxy driver
• This tool is just a front end for the
  DBIProxyServer package
• /usr/local/bin/dbiproxy --configfile
  /etc/dbiproxy.cfg
• Perl DBDOracle - http//search.cpan.org/pythian
  /DBD-Oracle-1.23/Oracle.pm
• Oracle database driver for the DBI module

13
Perl modules

• Sample access configuration for the DBI proxy
  server (/etc/dbiproxy.cfg)

• facility gt 'daemon',
• pidfile gt '/var/dbiproxy/dbiproxy.pid',
• user gt 'nobody',
• group gt 'nobody',
• localport gt '3333',
• mode gt 'fork',
• user gt 'nobody',
• group gt 'nobody',
• Access control
• clients gt
• Accept the local LAN ( 192.168.1. )
• mask gt '192\.168\.1\.\d',
• accept gt 1,
• users gt 'wctsupport' ,
• ,

• Accept our off-site machines ( 192.168.2. )
  but with a cipher
• mask gt '192\.168\.2\.\d',
• accept gt 1,
• users gt 'wctsupport' ,,
• cipher gt CryptIDEA-gtnew(
  'be39893df23f98a2' )
• ,
• Deny everything else
• any IP-address is meant here
• mask gt '(\d)\.(\d)\.(\d)\.(\d)',
• accept gt 0,

14
Perl modules

• Basic Perl script
• Connect to DB (via DBI Proxy)
• dsn "DBIProxyhostnamehostnameportportd
  snDBIOraclesid"
• dbh DBI -gt connect(dsn, user, passwd)
  die ("Database connection failed.")
• Run SQL
• my sth dbh -gt prepare( sql)
• sth -gt execute()
• while ( _at_row sth -gt fetchrow_array )
• push ( _at_return, _at_row)
• Store SQL results into a CSV file
• Parse CSV file using TextCSVSimple
• my parser TextCSVSimple-gtnew()
• my _at_data parser-gtread_file(file)

15
Perl modules

• 5. Graph CSV using GDGraph
• my graph GDGraphlinespoints-gtnew(600,
  500)
• graph-gtset(
• x_label gt 'Year',
• x_label_position gt 0.5,
• y_label gt 'Course Per Semester',
• title gt 'WebCT Course Growth',
• y_max_value gt 700,
• y_tick_number gt 7,
• zero_axis_only gt 0,
• zero_axis gt 0,
• show_values gt 1,
• markers gt 1, 5, 8,
• marker_size gt 3,
• skip_undef gt 1,
• transparent gt 0,
• ) or die graph-gterror
• my format graph-gtexport_format

16
Google Analytics

• Require Google account
• Need custom entry page for Blackboard
• Simply put the tracking code (JavaScript)
  immediately before the lt/bodygt tag of each
  page you want to track (entry page, login,
  logout)
• Example tracking code 
• ltscript type"text/javascript"gt
• var gaJsHost (("https" document.location.pro
  tocol) ? "https//ssl." "http//www.")
• document.write(unescape("3Cscript src'"
  gaJsHost "google-analytics.com/ga.js'
  type'text/javascript'3E3C/script3E"))
• lt/scriptgt
• ltscript type"text/javascript"gt
• try 
• var pageTracker _gat._getTracker("UA-xxxxxx-x")
  
• pageTracker._trackPageview()
• catch(err)  
• lt/scriptgt
• Does not track SSO logins (e.g. logins from
  portals)
• Personally identifiable info are not tracked
  (username and IP address)
• Provides nice graphs and reports that can be
  exported emailed

17
Google Analytics

• Browser information usage (monthly view)

18
Google Analytics

• OS information usage (monthly view)

19
Google Analytics

• Many other useful information

20
Questions?

• Email santos_at_sfu.ca
• Who wants to print out a handout? Instead, check
  out my presentation on http//get.sfu.ca/bbworld09