Skip navigation
All Places > Blackboard Developer Community > Blog > Author: mb23565

Hello and Welcome!

 

It's been awhile since I did a post, and I just got back recently from DevCon18. I was asked by several peers about if I have ever tried to do an Item Analysis over multiple courses. As a matter of fact, I have! A few years ago I was tasked to see if we can do an IAQ on an English Skills test. My first response was to have the instructors just run the builtin IAQ from with in the course. The response from that was that they wanted it from all English courses from lower level and higher level course (ENC1101 - ENC1102). The test they were using was exactly the same in every course: Name of the test, questions, answers. An exact copy. This was a really good start as I got a chance to manually review the test to know: How many questions? How many answers for each question? What types of questions there are? etc...

 

Once I have mapped that out I got a really good understanding of what to do when trying to collect this data. At first, the request was to know the grades of each student for this report. Ok, done. I mapped out the db tables pulled in the grades. Then, the request got bigger. They wanted to know what the student chose! Well that is a completely different approach. the original thought was that I assumed that they would look at the final scores and calculate that based of the points of each question to get a idea of the test overall and then review it further. Nope!

 

So now here comes the SQL Query and hopefully I will be able to break down the key parts:

 


SELECT 
     CM.COURSE_ID,
     CASE
          WHEN INSTR(CM.COURSE_NAME, 'INTERNET') > 0 THEN 'Internet'
          ELSE 'Face To Face'
     END COURSE_TYPE,
     T.SOURCEDID_ID AS TERM_CODE,
     T.NAME AS SEMESTER,
     U.BATCH_UID AS STUDENT_ID,
     REGEXP_REPLACE(
          REGEXP_SUBSTR(U.STUDENT_ID, '^\([[:alnum:]]+\)')
          ,'\(|\)'
          ,''
     ) COHORT,
     U.LASTNAME,
     U.FIRSTNAME,
     GM.TITLE ASSESSMENT,
     QRD.POSITION QUESTION_POSITION,
     TRIM(QAD.TITLE) QUESTION_TITLE,
          QAD.CHOICE_0,
          QAD.CHOICE_1,
          QAD.CHOICE_2,
          QAD.CHOICE_3,
     CASE QRD.STUDENT_RESPONSE
          WHEN '0' THEN QAD.CHOICE_0
          WHEN '1' THEN QAD.CHOICE_1
          WHEN '2' THEN QAD.CHOICE_2
          WHEN '3' THEN QAD.CHOICE_3
          ELSE QRD.STUDENT_RESPONSE
     END STUDENT_RESPONSE,
     QRD.CORRECT,
     A.SCORE,
     GM.POSSIBLE POINTS_POSSIBLE,
     GM.TOOL_COMPUTED_POINTS MANUAL_GRADE_OVERRIDE,
     CASE A.STATUS
          WHEN 3 THEN 'IN_PROGRESS' 
          WHEN 4 THEN 'SUSPENDED' 
          WHEN 6 THEN 'NEEDS_GRADING'
          WHEN 7 THEN 'COMPLETED' 
          WHEN 8 THEN 'IN_MORE_PROGRESS'
          WHEN 9 THEN 'NEEDS_MORE_GRADING'
     END STATUS
FROM 
     BBLEARN.COURSE_MAIN CM,
     BBLEARN.GRADEBOOK_MAIN GM,
     BBLEARN.GRADEBOOK_GRADE GG,
     BBLEARN.COURSE_USERS CU,
     BBLEARN.USERS U,
     BBLEARN.ATTEMPT A,
     (WITH xmlResultData
          AS (SELECT 
               PARENT_PK1,
               QTI_ASI_DATA_PK1,
               PK1,
               POSITION,
               BBMD_GRADE CORRECT,
               XMLTYPE(DATA, nls_charset_id('AL32UTF8')) data
               FROM BBLEARN.QTI_RESULT_DATA)
     SELECT
          x.PARENT_PK1,
          x.QTI_ASI_DATA_PK1,
          x.PK1,
          x.POSITION,
          x.CORRECT,
          REGEXP_REPLACE(
             NVL(x.data.EXTRACT('/item_result/response/response_value/text()').getStringVal(),
             x.data.EXTRACT('/item_result/response/response_value/formatted_text/text()').getStringVal())
             ,'<|p>|/', '') STUDENT_RESPONSE
     FROM xmlResultData x) QRD,
     (WITH xmlData
      AS (SELECT
               qad.PK1,
               qad.PARENT_PK1,
               qad.DESCRIPTION,
               qad.POSITION,
               XMLTYPE(qad.DATA, nls_charset_id('AL32UTF8')) data
           FROM BBLEARN.QTI_ASI_DATA qad)
     SELECT
          x.PK1,
          x.PARENT_PK1,
          x.POSITION,
          x.DESCRIPTION TITLE,
          TRIM(
               REGEXP_REPLACE(
                  REGEXP_REPLACE(
                      x.data.EXTRACT('/item/presentation/flow/flow/response_lid/render_choice/flow_label[1]/response_label/flow_mat/material/mat_extension/mat_formattedtext/text()').getStringVal(),
                      '"|'|<|p>|/',
                      ''
                  ),
               '[' || CHR(10) || CHR(13) || ']',
               ' '
            )
          ) CHOICE_0,

          TRIM(REGEXP_REPLACE(
               REGEXP_REPLACE(
                   x.data.EXTRACT('/item/presentation/flow/flow/response_lid/render_choice/flow_label[2]/response_label/flow_mat/material/mat_extension/mat_formattedtext/text()').getStringVal()
                   ,'"|'|<|p>|/'
                   , ''
               )
          ,'[' || CHR(10) || CHR(13) || ']',' ')) CHOICE_1,

          TRIM(REGEXP_REPLACE(
               REGEXP_REPLACE(
                   x.data.EXTRACT('/item/presentation/flow/flow/response_lid/render_choice/flow_label[3]/response_label/flow_mat/material/mat_extension/mat_formattedtext/text()').getStringVal()
                   ,'"|'|<|p>|/'
                   , ''
               )
          ,'[' || CHR(10) || CHR(13) || ']',' ')) CHOICE_2,

          TRIM(REGEXP_REPLACE(
               REGEXP_REPLACE(
                   x.data.EXTRACT('/item/presentation/flow/flow/response_lid/render_choice/flow_label[4]/response_label/flow_mat/material/mat_extension/mat_formattedtext/text()').getStringVal()
                   ,'"|'|<|p>|/'
                   , ''
               )
          ,'[' || CHR(10) || CHR(13) || ']',' ')) CHOICE_3
FROM xmlData x) QAD,
     BBLEARN.COURSE_TERM CT,
     BBLEARN.TERM T
WHERE CM.PK1 = GM.CRSMAIN_PK1
     AND GM.PK1 = GG.GRADEBOOK_MAIN_PK1
     AND CU.PK1 = GG.COURSE_USERS_PK1
     AND U.PK1 = CU.USERS_PK1
     AND A.QTI_RESULT_DATA_PK1 + 1 = QRD.PARENT_PK1
     AND QAD.PK1 = QRD.QTI_ASI_DATA_PK1
     AND CM.PK1 = CT.CRSMAIN_PK1
     AND T.PK1 = CT.TERM_PK1
     AND GG.PK1 = A.GRADEBOOK_GRADE_PK1
     AND CU.ROLE = 'S'
     AND REGEXP_LIKE(GM.title, '^M\w+\s+English Skills Test\s+#[1|2|3].*$')
     AND GM.title NOT LIKE '%Final%'
     AND T.SOURCEDID_ID >= 20163
     AND REGEXP_LIKE(CM.COURSE_ID, '^ENC110(1|2)-\d{6}$')
     AND ROWNUM<=100
ORDER BY CM.COURSE_ID, U.BATCH_UID, GM.TITLE, QRD.POSITION ASC;




 

 

 

 

 

WHAT THE?????

Seriously, that took awhile to compose. So, a quick little story. So after I started poking around the database tables I came across the QTI_ASI_DATA table. This is the table that houses the assessments. Yep, old Angel users, this is the same type of table but slightly different. So after following the foreign key trails I ended up at the QTI_RESULT_DATA. I came across this DATA field with a CLOB. I first ignored it. I poke around more but could not find the responses from each question.....So I started reaching about the CLOB data type and turns out if you extract that data, you get an xml snapshot of the test response from a particular attempt! So with some good ole hacker'y I managed to extract the data out!

Breakdown, from the top

Getting the Choices

 


CASE QRD.STUDENT_RESPONSE
     WHEN '0' THEN QAD.CHOICE_0
     WHEN '1' THEN QAD.CHOICE_1
     WHEN '2' THEN QAD.CHOICE_2
     WHEN '3' THEN QAD.CHOICE_3
     ELSE QRD.STUDENT_RESPONSE
END STUDENT_RESPONSE,


 

This above snippet show how to provide the multiple choice 'choices'. This helps knowing the max amount of possible choices in the test. I am pulling from my aliased QRD (QTI_RESULT_DATA) table then mapping it to the QAD aliased table. This allowed me to build the test back into my result data set!

But how to get the data out of the CLOB?

 


     (WITH xmlData
      AS (SELECT
               qad.PK1,
               qad.PARENT_PK1,
               qad.DESCRIPTION,
               qad.POSITION,
               XMLTYPE(qad.DATA, nls_charset_id('AL32UTF8')) data
           FROM BBLEARN.QTI_ASI_DATA qad)
     SELECT
          x.PK1,
          x.PARENT_PK1,
          x.POSITION,
          x.DESCRIPTION TITLE,
          TRIM(
               REGEXP_REPLACE(
                  REGEXP_REPLACE(
                      x.data.EXTRACT('/item/presentation/flow/flow/response_lid/render_choice/flow_label[1]/response_label/flow_mat/material/mat_extension/mat_formattedtext/text()').getStringVal(),
                      '&quot;|&apos;|&lt;|p&gt;|/',
                      ''
                  ),
               '[' || CHR(10) || CHR(13) || ']',
               ' '
            )
          ) CHOICE_0,

          TRIM(REGEXP_REPLACE(
               REGEXP_REPLACE(
                   x.data.EXTRACT('/item/presentation/flow/flow/response_lid/render_choice/flow_label[2]/response_label/flow_mat/material/mat_extension/mat_formattedtext/text()').getStringVal()
                   ,'&quot;|&apos;|&lt;|p&gt;|/'
                   , ''
               )
          ,'[' || CHR(10) || CHR(13) || ']',' ')) CHOICE_1,

          TRIM(REGEXP_REPLACE(
               REGEXP_REPLACE(
                   x.data.EXTRACT('/item/presentation/flow/flow/response_lid/render_choice/flow_label[3]/response_label/flow_mat/material/mat_extension/mat_formattedtext/text()').getStringVal()
                   ,'&quot;|&apos;|&lt;|p&gt;|/'
                   , ''
               )
          ,'[' || CHR(10) || CHR(13) || ']',' ')) CHOICE_2,

          TRIM(REGEXP_REPLACE(
               REGEXP_REPLACE(
                   x.data.EXTRACT('/item/presentation/flow/flow/response_lid/render_choice/flow_label[4]/response_label/flow_mat/material/mat_extension/mat_formattedtext/text()').getStringVal()
                   ,'&quot;|&apos;|&lt;|p&gt;|/'
                   , ''
               )
          ,'[' || CHR(10) || CHR(13) || ']',' ')) CHOICE_3
FROM xmlData x) QAD,




 

So this is done the cast a selected data set as xml, then telling it to extract the data via an xpath. I have some convenience trim and replace functions to help clean up the extract data as it is xml with html entities. This helped when exporting this data set to an csv file to be imported into Excel.

 

The actual part is here:

 

     (WITH xmlData
      AS (SELECT
               qad.PK1,
               qad.PARENT_PK1,
               qad.DESCRIPTION,
               qad.POSITION,
               XMLTYPE(qad.DATA, nls_charset_id('AL32UTF8')) data
           FROM BBLEARN.QTI_ASI_DATA qad)

 

The WITH <alias> AS SELECT statement is what does the casting of the DATA field by the use of XMLTYPE. The major key note here is the charset! This took me awhile to find but this is what helps in extracting the data out.

Now onto the fancy stuff

 


     (WITH xmlResultData
          AS (SELECT 
               PARENT_PK1,
               QTI_ASI_DATA_PK1,
               PK1,
               POSITION,
               BBMD_GRADE CORRECT,
               XMLTYPE(DATA, nls_charset_id('AL32UTF8')) data
               FROM BBLEARN.QTI_RESULT_DATA)
     SELECT
          x.PARENT_PK1,
          x.QTI_ASI_DATA_PK1,
          x.PK1,
          x.POSITION,
          x.CORRECT,
          REGEXP_REPLACE(
             NVL(x.data.EXTRACT('/item_result/response/response_value/text()').getStringVal(),
             x.data.EXTRACT('/item_result/response/response_value/formatted_text/text()').getStringVal())
             ,'&lt;|p&gt;|/', '') STUDENT_RESPONSE
     FROM xmlResultData x) QRD,



 

Same as with QAD, we need to do the same for QRD.

 

x.data.EXTRACT('/item/presentation/flow/flow/response_lid/render_choice/flow_label[1]/response_label/flow_mat/material/mat_extension/mat_formattedtext/text()').getStringValue(),



 

The extract method is what allows you to use the xpath to drill down to the data you what to show. You can get more information here: IMS Question & Test Interoperability v1.2 QTILite Specification | IMS Global Learning Consortium on the QTI IMS1.x Specification.

 

Getting the data across multiple courses.

 



WHERE CM.PK1 = GM.CRSMAIN_PK1
     AND GM.PK1 = GG.GRADEBOOK_MAIN_PK1
     AND CU.PK1 = GG.COURSE_USERS_PK1
     AND U.PK1 = CU.USERS_PK1
     AND A.QTI_RESULT_DATA_PK1 + 1 = QRD.PARENT_PK1
     AND QAD.PK1 = QRD.QTI_ASI_DATA_PK1
     AND CM.PK1 = CT.CRSMAIN_PK1
     AND T.PK1 = CT.TERM_PK1
     AND GG.PK1 = A.GRADEBOOK_GRADE_PK1
     AND CU.ROLE = 'S'
     AND REGEXP_LIKE(GM.title, '^M\w+\s+English Skills Test\s+#[1|2|3].*$')
     AND GM.title NOT LIKE '%Final%'
     AND T.SOURCEDID_ID >= 20163
     AND REGEXP_LIKE(CM.COURSE_ID, '^ENC110(1|2)-\d{6}$')
     AND ROWNUM<=100
ORDER BY CM.COURSE_ID, U.BATCH_UID, GM.TITLE, QRD.POSITION ASC;




 

Now, usually I always tend to do LEFT JOINS, they tend to be more accurate and time efficient. But, the data being pull has some oddities in such that; not all choice would the same amount of choices. I believe this example I lucked out and they all ended up with the same amount. Obviously, you will need to modify the GM.TITLE, TERM (if applicable), and the COURSE_ID to match  your specific need. Note: REMOVE ROWNUM , I left this in this example as when you are running tests, you want to make sure that you start off with minimal results then increase when you are more confident in your results. Once completed, remove the rownum to get all.

 

Conclusion

I would like to thank you for sticking through this blog post and that if you have any questions, feel free to contact me via community!

 

Thanks,

 

Mike

Hello everyone! I made a comment in a post a while back about using JS Hacks to override some CSS rules that the new 2016 Theme enforces. I have a few people ask me about this and I though this would be a good opportunity to write up a small post about it.

 

 

Why won't my custom theme stick!

If you are using the new 2016 theme, you may have notice that some things are very opinionated and you want to change some things. So you download the theme and start hacking away at the CSS files. You finally re-upload your theme and....the rules are not sticking!!! What gives?!?!

 

How do I overcome this issue?

Well what is happening is that there is a them1.css file that is being injection during the page render time. Since the last rule set wins when it comes to css, your beautiful css rules are now squashed by Blackboard! Bummer!

 

Conquering the CSS Takeover!

Well, there is another way! You can make all the css rules that are being squashed, into another css file. Then you can place either as an attachment via JS Hacks, or what I have done is place this file in the Content Collection and made it publicly available. Now I can copy this URI path and use it in my diabolical plan to take over the world! Blackboard!

 

 

Here is the JS Hack:

 

 

<!--

    your.blackboard.com = your instance of Blackboard Learn

    CC_FOLDER = is just a folder created in the Content Collection

    your_theme1 = the id you wish you give your element

 -->

<script type="text/javascript">

// https://your.blackboard.com/bbcswebdav/institution/CC_FOLDER/theme/theme1.css

    Event.observe(document,"dom:loaded", function() {

        //Your javascript goes here.

        // console.log($$('head')[0])

        $$('head')[0].insert({

            bottom: new Element('link', {

                href: '/bbcswebdav/institution/CC_FOLDER/theme/theme1.css',

                type: 'text/css',

                rel: 'stylesheet',

                id: 'your_theme1'

            })

        });

    });

</script>


Hello, I recently just had to run a report via Open DB and supply some Learn Information to a vendor that will be creating a PoC. I was tasked on getting this information but the information of course would violate FERPA. So I had to come up with a way to mask the user information to still supply an accurate model but protect the user information at the same time. So I decided to use an old python script that converted CSV files to XLSX and make some modifications.


 

I used openpyxl to create the Excel XLSX files, Docopt to make the CLI creation easy, and some built in core modules: csv, json, and random.


 

The main challenge was that I had two reports from Open Db that correlated between Activity and Gradebook Data by the USER_ID. I ended up doing some Python wizardry and use a global (gasp!! what globals in Python, your fired!) Anyways, yes the use of a global in Python is frowned upon, but hey it got the job done! That’s what re-factoring is for...right?


 

Anyways, I thought I would share this application and share my work and hopefully see how useful this can be. I plan on implementing a way to connect directly to Open Db (Already have it done in another project) and then add in some common functionality: Mongo DB Storage, Better Configuration Methods, etc.


 

You are all welcome to use, share, clone, hack, and destroy this code here:

https://github.com/elmiguel/CSV2XLSX


 

You will just need to query and save your own data sets… Luckily for you, I will share my queries:

 

Activity Accumulator:


 

SELECT AA.PK1
,AA.EVENT_TYPE
,U.USER_ID
,CM.BATCH_UID
,AA.GROUP_PK1
,AA.FORUM_PK1
,AA.INTERNAL_HANDLE
,AA.CONTENT_PK1
,AA.DATA
,AA.TIMESTAMP
,AA.STATUS
,AA.MESSAGES
,AA.SESSION_ID
FROM BBLEARN.ACTIVITY_ACCUMULATOR AA
INNER JOIN BBLEARN.USERS U
ON AA.USER_PK1 = U.PK1
INNER JOIN BBLEARN.COURSE_MAIN CM
ON AA.COURSE_PK1 = CM.PK1
WHERE CM.COURSE_ID IN (‘LIST COURSE_IDS HERE’
)
ORDER BY AA.TIMESTAMP DESC;







 

 

 
  Gradebook Data:


 

SELECT
CM.COURSE_ID,
U.USER_ID,
U.FIRSTNAME,
U.LASTNAME,
GM.TITLE,
GM.DUE_DATE,
A.SCORE,
CASE A.STATUS
WHEN 1 THEN 'NOT_ATTEMPTED'  
WHEN 2 THEN 'ABANDONED'
WHEN 3 THEN 'IN_PROGRESS'  
WHEN 4 THEN 'SUSPENDED'
WHEN 5 THEN 'CANCELLED '
WHEN 6 THEN 'NEEDS_GRADING'  
WHEN 7 THEN 'COMPLETED'
WHEN 8 THEN 'IN_MORE_PROGRESS'  
WHEN 9 THEN 'NEEDS_MORE_GRADING'
ELSE 'NO STATUS'
END AS STATUS,
A.ATTEMPT_DATE,
A.FIRST_GRADED_DATE,
A.LAST_GRADED_DATE,
A.DATE_ADDED,
A.DATE_MODIFIED,
A.LATEST_IND
-- A.STUDENT_SUBMISSION
-- A.PK1 ATTEMPT_PK1,
-- A.QTI_RESULT_DATA_PK1,
-- GG.GRADEBOOK_MAIN_PK1,
-- GG.PK1 GG_PK1
FROM GRADEBOOK_MAIN GM  
INNER JOIN GRADEBOOK_GRADE GG
ON GG.GRADEBOOK_MAIN_PK1 = GM.PK1
INNER JOIN ATTEMPT A
ON A.GRADEBOOK_GRADE_PK1 = GG.PK1
INNER JOIN COURSE_USERS CU
ON GG.COURSE_USERS_PK1 = CU.PK1
INNER JOIN COURSE_MAIN CM
ON CU.CRSMAIN_PK1 = CM.PK1
INNER JOIN USERS U
ON CU.USERS_PK1 = U.PK1
WHERE CM.COURSE_ID IN (‘LIST COURSE_IDS HERE’);