SQL Script to Generate a List of Sections (& Instructors) from Blackboard Learning System CE8

We need to generate a list of all courses/ sections hosted in our Learning Management System (powered by Blackboard Learning System CE8, formerly known as WebCT).

In Blackboard's terminology, all courses and sections are learning contexts. They are hierarchical and their parent-child relationships along with other information are stored in the database. So, the challenge here is to figure out how to write a recursive query.

With a little help from an msdn article: Recursive Queries Using Common Table Expressions, I figured out how to do that today.

The following SQL query will generate a list of all sections ordered by the course names, and the by section names:

with temp1 (parent_learning_context_id, learning_context_id, name, level)
as
(
select parent_learning_context_id, learning_context_id,
name, 0 as level from rpt_learning_context

where parent_learning_context_id IS NULL

union all

select LC.parent_learning_context_id,
LC.learning_context_id, LC.name, level + 1

from rpt_learning_context as LC

inner join temp1 as T

on LC.parent_learning_context_id = T.learning_context_id

)

select T.parent_learning_context_id, LC.name,
T.learning_context_id, T.name from temp1 as T

inner join learning_context as LC

on T.parent_learning_context_id = LC.id

where T.level = 4

order by LC.name, T.name

With a little twist, I can easily modify it to generate a list of all instructors teaching this Spring semester:

with temp1 (parent_learning_context_id, learning_context_id, name, level)

as

(

select parent_learning_context_id, learning_context_id,
name, 0 as level from rpt_learning_context

where parent_learning_context_id IS NULL

union all

select LC.parent_learning_context_id,
LC.learning_context_id, LC.name, level + 1
from rpt_learning_context as LC

inner join temp1 as T

on LC.parent_learning_context_id = T.learning_context_id

)

select LC.name, T.name, P.webct_id_lowercase,
P.name_n_given, P.name_n_family, P.email from temp1 as T

inner join learning_context as LC

on T.parent_learning_context_id = LC.id

inner join rpt_member as M

on M.learning_context_id = T.learning_context_id

inner join person as P

on M.person_id = p.id

where T.level = 4

and LC.name like '%2009_spring%'

and M.role='SINS'

and M.active = '1'

and M.denied_access = '0'

order by T.name

SQL queries - I'm loving it.

Using Google Chart API to Implement Harvesting Grade Book

Our department has been working on a transformative assessment approach to teaching and learning. One of the challenges of implementing the idea is to provide rich renderings to help in conceptualizing and communicating the assessment data, one of which is in a form of what we currently called the Harvesting Grade Book (See "Rich Assessment From A Harvesting Grade Book" for more information).

Currently, the graphs in the Harvesting Grade Book example are generated manually. The data is downloaded into Excel and then graphed. This manual approach though, has its limitation: the results cannot be scaled and cannot be updated in real-time. We need to find ways to automate this process.

I looked at options.  The Microsoft LogParser utility was a potential solution I investigated to address the scaling issue. For those who are not familiar with it, LogParser is powerful command line utility. It takes input from a variety of sources, such as CVS, query a SQL database and even Active Directory via LDAP, etc. The output can also be in a variety of formats, such as XML, charts, etc. I have used it to generate a number of monitoring and usage graphs for the Blackboard Learning Management System. I believe my familiarity with LogParser would enable me to apply the utility, in conjunction with a wrapper script, to automate graphs generation for the Harvesting Grade Book in batch.

In order to overcome the second limitation - real-time updates, I decided to experiment with Google Chart API. For those who are not familiar with it, the Google Chart API lets you dynamically generate chart on the fly by sending it a REST web services call (which is basically a URL which contains the chart properties and data). The service will then return you the chart as an image in PNG format. What makes this solution elegant and easy to apply is that the URL contains the data. This kind of web-based charting is far easier and efficent than the manual compilations and complications of using Excel.

For example, a radar graph like those in the Harvesting Grade Book example can be created with a URL:

http://chart.apis.google.com/chart?

cht=r

&chs=410x270

&chtt=Self%20Assessment%20compared%20with%20Industry%20Ratings

&chdl=Competency|Team%201|Industry

&chxt=x,y&chxr=|1,0,6|

&chxs=0,000000,10|1,000000,10,,l

&chxl=0:|Problem|Context|Own%20Perspective|Data|Other%20Perspectives|Conclusions|Communication|1:|0|2|4|6

&chls=2.0,6.0,2.0|1.0,4.0,0.0|1.0,4.0,0.0

&chco=000000,888888,FE5900&chm=B,88888860,1,1.0,5.0|B,FE590060,2,1.0,5.0

&chd=t:67,67,67,67,67,67,67,67|73,77,73,80,67,73,90,73|33,33,50,50,33,33,33,33

&chdlp=r

Google will then return the following graph:
 

Perfect! And to any usage limitation concerns of using Google Chart API service, "There's no limit to the number of calls per day you can make to the Google Chart API... If you think your service will make more than 250,000 API calls per day, please let us know by mailing an estimate to ...". With a number like 250,000, I consider the scalability issue solved.

SQL Script to Download Grades from Blackboard Learning System CE8

I just finished a SQL query to download all grade book data from Blackboard Learning System CE8.

select

LC2.name as Semester,

GB.learning_context_name as Section,

P.source_id as StudentID,

GB.user_login_name,

GB.given_name,

GB.family_name,

GB.column_name,

GB.column_type,

GB.original_value,

GB.override,

GB.max_points,

GB.final_value

from rpt_ext_gradebook as GB

inner join rpt_person as P

on P.person_id = GB.person_id

inner join rpt_learning_context as LC1

on GB.learning_context_id = LC1.learning_context_id

inner join rpt_learning_context as LC2

on LC1.parent_learning_context_id = LC2.learning_context_id

where P.demo_user = 0

order by LC2.name, GB.learning_context_name, column_name, user_login_name

This depends on the RPT_EXT_GRADEBOOK background job to run successfully to have the data populated properly.

I ran it against a clone of our production database. It took 1.5 minutes to retrieve 2.2 millions grade records. I spot checked a number of courses (sections). It included the resulting value of grades even when they were calculated by formula. It also gave me the original grades, any overrides made, and the final grades. Very nice!

Google Search Finds Missing Child

I find this BBC news Google search finds missing child interesting.  A nine-year-old girl, allegedly kidnapped by her grandmother, has been found using a mobile phone signal and Google Street View.

What a creative and brilliant way of using technology.

Blackboard CE8 SP2 Upgrade and Scholar Powerlink

We upgraded our Blackboard Learning System CE8 to Service Pack 2 level on production yesterday afternoon. After the upgrade, the Scholar powerlink was no longer working.

The error message was:

An error occured in the communication between your Blackboard system and Scholar.
The Blackboard system clock may be incorrect.
If you continue to see this error, contact your Blackboard system administrator.

Multiple of us tested it, and we consistently received the above error.

This was really upsetting because this was one piece of the upgrade we could not test on our development cluster. We had a lot of troubles when we initially registered our development cluster onto the Blackboard's Scholar system for configuration testing, which then took away the ability of our production system to be registered. It took Blackboard technical support and their Beyond team over three months to resolve this (basically deleted the registration record of our development cluster in their database). As it turns out, Blackboard does not support both development and production clusters to be registered onto their Scholar system. Therefore we cannot test Scholar powerlink when we tested SP2 upgrade on development.

To my surprise, when I logged on our Blackboard CE8 SP2 and clicked on the Scholar link today, it worked. I immediately checked with my colleagues who helped in testing yesterday. They confirmed that it was also working for them. None of us encountered the above error any more.

It's good that it's working.

But I'm uncomfortable of not being able to understand was why it worked after we left the upgraded system overnight. Is it because some background job needed to run first so that the Scholar Powerlink would work after an upgrade? Mystery.

MMR Vaccine and Autism

A friend of mine asked me for advice on whether she should give her 1-year-old daughter the MMR vaccine since there has been so much controversy about this vaccine causing autism.  

I have done a lot of readings a year ago about vaccine and have created my own spreadsheet to organize what I have learned.  But I'm glad that I decided to do more searching tonight to see if there was any new information.  And I found this New Study Shows No Link Between MMR Vaccine and Autism article in Dr. Sears' web site.

I sent her all the information I had.  I hope she can make an educated decision for her own daughter.

Vaccine Schedule

Given so much controversy about vaccine safety, I decided I need to do my own reading, and come up with my own decision about the vaccine schedule for my baby.

My rationales are how common, and how severe the disease is, what are the ingredients contained in the vaccine and their potential side effects, whether the vaccine contains live viruses.  Also, I would try to space out all the shots, and not take any all-in-one combined shot as much as possible so that the little body does not have to handle that much drugs all at once.

Here is the planned vaccine schedule I come up with for my baby last year:

Planned Age	Disease	Brand
2 months	DTaP	Daptacel
2 months 1 week	HIB	ActHIB
2 months 1 week	HIB	ActHIB
2 months 2 weeks	Pc	Prevnar
2 months 3 weeks	Polio	IPOL
4 months	DTaP	Daptacel
4 months 1 week	HIB	ActHIB
4 months 2 weeks	Pc	Prevnar
4 months 3 weeks	Polio	IPOL
6 months	DTaP	Daptacel
6 months 1 week	HIB	ActHIB
6 months 2 weeks	Pc	Prevnar
9 months	Polio	IPOL
10 months	Flu	Fluzone (half of the single dose vial)
11 months	Flu	Fluzone (half of the single dose vial)
15 months	DTaP	Daptacel
15 months 1 week	HIB	ActHIB
15 months 2 weeks	Pc	Prevnar
17 months	Varicella	Varivax
18 months (likely later)	MMR	MMR
2 years 6 months	Hepatitus B	Recombivax HB
2 years 7 months	Hepatitus A	Harvix
3 years 6 months	Hepatitus B	Recombivax HB
3 years 7 months	Hepatitus A	Harvix
4 years 4 months	DTaP	Daptacel
4 years 4 months 1 week	Polio	IPOL
4 years 6 months	Hepatitus B	Recombivax HB
5 years 6 monhts	Varicella	Varivax
5 years 7 months (likely later)	MMR	MMR
~12 years	Tdap	Adacel
~12 years	Meningococcal	Menactra

He is almost one year old now.  I have been following this schedule most of the time with a few delays.  For example, when he got sick (from a cold), I would delay the scheduled shot until he recovers.  I have been pretty happy with it.  Therefore I'm posting it online so others can learn about it.

The majority of the information I gathered is from Dr Sears' The Vaccine Book. Other than that, it is from various web sites Google directed me to.

I urge all parents to do their own readings and come up with their own decisions on the subject.