SQL Integration

Only the Enterprise version of the LRS supports SQL Integration.

The Veracity LRS can send xAPI statements to a SQL database in real time. The LRS opens a connection to your SQL server, and synchs statements every 300 milliseconds. The LRS won’t convert the data — it sends a one-to-one rendering of the incoming xAPI statements to the SQL server.

Veracity can connect to:

1. MariaDB
   
2. Microsoft SQL Server
3. MySQL
4. PostgreSQL
   
   

Setup

1. In the store you want to link to SQL, in the left menu, select Management > All Management Tools.
   
   
2. Scroll down to the Integrations and Extensions section and click Plugins.
   
   
3. On the Manage Plugins page, click the Activate a Plugin button.
   
   NOTE: The LRS administrator must give you the privilege to use this tool.
   
   
   
   
4. Choose SQL – Statements.
   
   
   
   
   
5. In the plugin's configuration page, enter the Connection String to your database.
   
   
   
   
   Here are some examples of a typical Connection String for the four databases:
   
1. mariadb://username:password@localhost/xapidemo
   
2. mssql://username:password@localhost/xapidemo
   
3. mysql://username:password@localhost:3306/xapidemo
   
4. postgres://user:pass@example.com:5432/dbname
   
   
6. The first time you install the plugin, the LRS will try to connect to the database. If the connection is successful, then you'll see a new menu item in the sidebar.
   
   
   

   
   
   
   If the LRS can’t connect, then it’ll show an error message instead. If the problem is simply a typo in the connection string, then go back to the plugin’s configuration page and edit the string. For all other errors, remove and re-add the plugin.
   
   
   
   
   
   
7. Once the connection is successful, click the Setup DB option. This will set-up the initial tables.
   
   

The plugin is now operational, and the LRS should start sending xAPI statements to your SQL database in real time.

Schema

The LRS extracts xAPI statements into a linked set of tables, joined by shared UUIDs. If the value of cell in a table is a UUID, then it’s a link to another table where the _id row in the target table contains the given UUID in the source table. Links may be one-to-one or one-to-many. If links are one-to-many, then several different rows in the target table will contain the same UUID. This shows that the field of the source table is an array, and each field with the given UUID in the target table is a member of that array. The above table shows only the links between tables. Each table also contains regular data fields. Column names represent JSON paths. For compatibly with SQL queries, the LRS replaces periods in JSON paths with underscores.

Example SQL Queries

Example 1

Get the basic xAPI triplet of actor, object, and verb.

SELECT

    obj.id, ag.name, stmt.verb_id

FROM statements stmt

JOIN objects obj ON stmt.object = obj._id

JOIN agents ag ON stmt.actor= ag._id

Object	User	Verb ID
https://www.coursera.org/specializations/python/1	Cathyleen Simonne	http://adlnet.gov/expapi/verbs/commented
https://www.coursera.org/specializations/python/1	Cathyleen Simonne	http://adlnet.gov/expapi/verbs/commented
https://www.coursera.org/specializations/python/1	Cathyleen Simonne	http://adlnet.gov/expapi/verbs/terminated
https://www.coursera.org/learn/neural-networks/1	Nerti Zandra	http://adlnet.gov/expapi/verbs/initialized
https://www.coursera.org/learn/neural-networks/1	Nerti Zandra	http://adlnet.gov/expapi/verbs/suspended
https://www.coursera.org/learn/neural-networks/1	Nerti Zandra	http://adlnet.gov/expapi/verbs/resumed

Example 2

Get terminated statements where the scaled score is greater than 0.6. Return the agent’s name, the object title, and the score. This query is somewhat more complex, as it must link to the language map table to find the display name of the object.

SELECT

    ag.name, stmt.verb_id, lg.value as Object_Title, res.score_scaled

FROM statements stmt

JOIN objects obj ON stmt.object = obj._id

JOIN agents ag ON stmt.actor= ag._id

JOIN definitions def ON def._id = obj.definition

JOIN langMaps lg ON def.name = lg._id

JOIN results res ON stmt.result = res._id

where stmt.verb_id = "http://adlnet.gov/expapi/verbs/terminated"

and res.score_scaled > .6

User	Object English title	Score
Cathyleen Simonne	Programming for Everybody (Getting Started with Python)	0.99
Coral Bev	Reproducible Research	0.69
Coral Bev	Programming Languages, Part B Lesson	0.83
Coral Bev	Using Python to Access Web Data	0.81
Coral Bev	Regression Models	0.82
Coral Bev	Exploratory Data Analysis	0.91
Inga Phillie	Programming Languages, Part B Lesson	0.98
Inga Phillie	The Data Scientist’s Toolbox	0.79

Example 3

This query finds events where the score is greater than 0.5 and displays the student’s name and both the lesson display name and the parent course display name. It is somewhat more complex as it must join to the language map table twice, once for the course and once for the lesson.

SELECT

    ags.name as Student,

    parentMap.value AS Course,

    lessonMap.value AS Lesson,

    ress.score_scaled AS Score

FROM statements stmts

JOIN contexts as ctxobj on stmts.contexts = ctxobj._id

JOIN objects as objs on stmts.object = objs._id

JOIN contextActivities as ctxparent on ctxobj.contextActivities_parent = ctxparent._id

JOIN results as ress on stmts.result = ress._id

JOIN agents as ags on stmts.actor = ags._id

JOIN definitions as obj ON objs.definition = obj._id

JOIN langMaps as lessonMap ON obj.name = lessonMap._id

JOIN definitions as parent ON ctxparent.definition = parent._id

JOIN langMaps as parentMap ON parent.name = parentMap._id

WHERE

    ress.score_scaled > .5

ORDER BY Student, Course, Lesson;

Student	Parent Course	Object	Score
Thelma Cherey	Deep Learning	Neural Networks and Deep Learning	0.88
Thelma Cherey	Deep Learning	Sequence Models	0.58
Thelma Cherey	Deep Learning	Structuring Machine Learning Projects	0.57
Thelma Cherey	Learn to Program and Analyze Data with Python	Programming for Everybody (Getting Started with Python)	0.58
Thelma Cherey	Learn to Program and Analyze Data with Python	Python Data Structures	0.93
Theresina Benedikta	Deep Learning	Improving Deep Neural Networks: Hyperparameter tuning, Regularization and Optimization	0.57
Theresina Benedikta	Deep Learning	Improving Deep Neural Networks: Hyperparameter tuning, Regularization and Optimization	0.68
Theresina Benedikta	Deep Learning	Improving Deep Neural Networks: Hyperparameter tuning, Regularization and Optimization	0.51
Theresina Benedikta	Deep Learning	Neural Networks and Deep Learning	0.6
Theresina Benedikta	Deep Learning	Neural Networks and Deep Learning	0.54

• Related Articles

• LMS Integration

  The Veracity Learning offers seamless integration with your LMS to allow instructors and students to access the services of the LRS without leaving their LMS environment. This is accomplished via LTI (Learning Tools Interoperability), a standard from ...

• Elasticsearch Integration

  Overview The LRS can accelerate its dashboard and statement viewer by leveraging Elasticsearch in addition to MongoDB. When configured with the optional Elasticsearch connection, the LRS will synchronize xAPI statements in real time between these two ...

• Power BI Integration

  How to get LRS data into Power BI You can easily connect the Veracity LRS to Microsoft Power BI. In the LRS, save a Statement Viewer report and create an API key. In Power BI, configure and activate a data download channel with this information. In ...

• Single Sign On (SSO) Integration

  Veracity can integrate with your single sign on provider using OpenID Connect or Security Assertion Markup Language (SAML). This integration allows your enterprise users to log into the LRS user interface without providing a password. Integrating a ...

• Tableau Integration

  Veracity LRS supports Tableau via a Web Data Connector. This connector allows you to import your saved statement reports into Tableau for analysis. This tool can only pull saved statement viewer reports. If you have none, first create any report in ...