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(),
'"|'|<|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,
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())
,'<|p>|/', '') 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
Help
Privacy
Behind the Blackboard
Blackboard.com
