SQL Integration
SQL Integration is an Enterprise only feature
Veracity LRS can synchronize your xAPI statements into an SQL database in real time. The LRS will open a connection to your SQL server, and flush out statements every 300 milliseconds. Statements are not normalized - the data we send into the SQL server is a one-to-one rendering of the incoming xAPI statements.
Veracity can connect to
- Maria Db,
- MySQL
- MSSql
- PostgresSQL
Setup
Browse to the "all management tools" page for the LRS you wish to link to SQL.
Locate "Plugins" and click the link.
On the following page, choose "Activate a plugin"
You may need to add a permission for the give LRS to use this plugin in the administrative tools.
Choose "SQL - Statements"
The plugin's configuration page will appear. On this page, enter the connection string to your database.
See the connection string section for more info on connecting to your database.
When the plugin is first installed, it will attempt to connect to the database. If the connsection is successful, you'll see a new menu item in the sidebar.
If the connection cannot be established, error info will be displayed in the same location.
If this occurs, correct the problem, then remove and re-add the plugin. If the problem is simply an error in the entry of the connection string, you can return to the plugin configuration and edit the string.
Once the connection is successful, click the "Setup DB" option. This will set up the initial tables.
The plugin is now operational, and xAPI statements should be sent into your DB in real time.
Schema
xAPI statements are extracted into a linked set of tables, joined by shared UUIDs. When the value of cell in a given table is a UUID, this represents 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. In the case where links are one to many, several different rows in the target table will contain the same UUID. This indicates 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 show ONLY the links between tables. Each table also contains regular data fields. Column names represent JSON paths. For compatibly with SQL queries, periods in JSON paths are replaced with underscores.
Example SQL Queries
Get the basic xapi triple of actor, object, 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
Get terminated statements where the scaled score is greater than 0.6. Return the agent 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 |
This query finds events where the score is greater than .5, and displays the student 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 |
Connection String Examples
- postgres://user:pass@example.com:5432/dbname
- mysql://username:password@localhost:3306/xapidemo
- mssql://username:password@localhost/xapidemo
- mariadb://username:password@localhost/xapidemo
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
Integration with Power BI Integration is accomplished by pulling a saved statement viewer report into PowerBI. Before you can do this, you'll need an API key. Navigate to "All Mangement Tools" then find "Security" Click "API Keys" Click "Create New ...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 ...