RandyStewartMiller.com

Mapping xAPI to CTDL JSON

First why would you want to map xAPI JSON statements to CTDL JSON? Both are data entry tools.

Think of it this way. In instructional design we start with a transformative goal. For an example, maybe a learner wants to receive a credential in XYZ for their job so they can do their job. Credentials are the domain of CTDL. CTDL stands for Credential Transparency Description Language. It’s a standardized language designed to describe credentials, competencies and learning and career pathways. The goal of CTDL is to make it easier to discover, understand, and compare different credentials and related information across various systems.

CTDL JSON-LD (JavaScript Object Notation for Linked Data) is a format used to publish and share credential data in a structured and interoperable way. This format allows organizations to programmatically output and update credential data, making it easier to manage and share information about credentials. Here is an example of a CTLD JSON statement.

 

{

  "@context": https://credreg.net/ctdl/schema/context/json,

  "@type": "LearningOpportunity",

  "ceterms:name": "Computer Science Course",

  "ceterms:description": "An introductory course on computer science.",

  "ceterms:learningMethodType": "Online",

  "ceterms:assessmentMethodType": "Examination",

  "ceterms:learningOpportunityType": "Course",

  "ceterms:identifier": http://example.com/activities/computer-science-course

}

This example includes basic information about the credential, such as the name, description, category, etc. You can customize and expand this statement based on your specific needs and the information you want to include about the credential.

The Experience API (xAPI), is a specification for learning technology that makes it possible to collect data about a wide range of experiences a person has (both online and offline). This API allows learning content and learning systems to communicate with each other in a manner that records and tracks all types of learning experiences.

Here are some key components of xAPI:

  1. Statements: These are the core of xAPI. They follow the format “Actor verb object,” such as “John completed the course.” Each statement captures a specific learning experience.
  2. Learning Record Store (LRS): This is a system that stores learning records. It can exist within a traditional Learning Management System (LMS) or on its own. Here at RandyStewartMiller.com we are interested in PostgreSQL database for housing an LRS because its open source and PostgreSQL is powerful.
  3. Activities: These are the learning experiences or tasks that are being tracked. They can be anything from reading a book to completing a course. Here is an example of an xAPI statement.

While Admins are most interested in CTDL JSON just like admins would be interested in archaic SCORM based Learning Management Systems of by-gone eras (and unfortunately almost all companies to this day still use these archaic LMSs – which makes the organization also archaic). Instructional Designers or Experience Designers are more concerned about experience API (xAPI) to document the experiences that lead to a credential! Instructional Designers are interested in Outcomes (nouns) and Terminal and Enabling Learning Objectives (verbs). In our example, COMPLETED is the verb we want to register in the LRS, and then tie it to a credential.

Now suppose for a second that once your beautiful course was completed by the learner (call it the capstone course for the credential) and you wanted the learner to automatically obtain the credential upon exit of the course how would you make this happen without a single sheet of paper on the part of an Admin? What if you combined the two JSON functions and an xAPI statement feeds the CTDL JSON? Now compare the CTDL JSON above with an xAPI JSON statement that feeds the CTDL.

 
{

  "actor": {

    "mbox": mailto:example@example.com,

    "name": "John Doe"

  },

  "verb": {

    "id": http://adlnet.gov/expapi/verbs/completed,

    "display": { "en-US": "completed" }

  },

  "object": {

    "id": http://example.com/activities/computer-science-course,

    "definition": {

      "name": { "en-US": "Computer Science Course" },

      "description": { "en-US": "An introductory course on computer science." }

    }

  }

This xAPI statement includes key components:

  • Actor: The person who completed the credential.
  • Verb: The action taken, in this case, “completed.”
  • Object: The activity, representing the Bachelor’s degree program.
  • Result: The outcome of the activity, indicating completion and success.
  • Context: Additional information about the context in which the activity took place, such as the university as applicable.

Now suppose that all of the JSON statements were housed in the PostgreSQL database and you were the admin and you need to do a report of who in the organization held the credential. How would you query the database?

To query a PostgreSQL database to find out who holds the Bachelor of Science in Computer Science credential, you would need to store the relevant data from both xAPI and CTDL JSON documents in appropriate tables. Here’s an example of how you might structure the tables and write a query:

Structure the tables:

  • A table for xAPI statements
  • A table for CTDL credentials
  • A table for actors (to store information about individuals)

Example Table Definitions ( this can be examined further after creating an LRS on the PostgreSQL database). A free commercial open source LRS for this purpose is located here.

CREATE TABLE actors (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

CREATE TABLE ctdl_credentials (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  credential_category TEXT,
  educational_level TEXT,
  provider_name TEXT
);

CREATE TABLE xapi_statements (
  id SERIAL PRIMARY KEY,
  actor_id INTEGER REFERENCES actors(id),
  verb TEXT NOT NULL,
  object TEXT NOT NULL,
  result_completion BOOLEAN,
  result_success BOOLEAN,
  context_parent TEXT,
  timestamp TIMESTAMP

We want the following to be done automatically with the xAPI statement.

INSERT INTO actors (name, email) VALUES ('John Doe', 'john.doe@example.com');

INSERT INTO ctdl_credentials (name, description, credential_category, educational_level, provider_name)
VALUES ('Bachelor of Science in Computer Science', 
        'A program that prepares individuals to apply mathematical and scientific principles to the design, development, and operational evaluation of computer software and systems.',
        'Bachelor's Degree', 'Undergraduate', 'Example University');

INSERT INTO xapi_statements (actor_id, verb, object, result_completion, result_success, context_parent, timestamp)
VALUES ((SELECT id FROM actors WHERE email = 'john.doe@example.com'),
        'completed',
        'http://example.com/credential/bsc-computer-science',
        TRUE,
        TRUE,
        'http://example.com/university/example-university',
        '2025-02-22T15:56:00Z');

Query to Find Actors with Specific Credential

SELECT a.name, a.email
FROM actors a
JOIN xapi_statements x ON a.id = x.actor_id
JOIN ctdl_credentials c ON x.object = 'http://example.com/credential/bsc-computer-science'
WHERE c.name = 'Bachelor of Science in Computer Science'
AND x.verb = 'completed'
AND x.result_completion = TRUE
AND x.result_success = TRUE

This query selects the names and emails of actors who have completed the Bachelor of Science in Computer Science credential successfully. It joins the actors, xapi_statements, and ctdl_credentials tables to match the appropriate records.

Mapping xAPI JSON to CTDL JSON-LD involves transforming the data captured in xAPI statements into the structure required by CTDL. Here are the steps to achieve this:

  1. Understand xAPI Statements: xAPI statements are JSON objects that capture specific moments in a stream of activity. They typically include an actor, verb, and object. Familiarize yourself with the structure of xAPI statements by referring to the xAPI Developer’s Guide.
  2. Identify Relevant xAPI Data: Determine which xAPI data elements correspond to CTDL properties. For example, an xAPI statement capturing a learning activity can be mapped to a CTDL learning opportunity.
  3. Create a Mapping Plan: Develop a plan to map xAPI properties to CTDL properties. This involves identifying the relevant fields in both schemas and determining how to transform the data.
  4. Transform xAPI Data: Extract the relevant xAPI data and transform it into CTDL JSON-LD format. This involves creating a JSON-LD object that conforms to the CTDL schema. Here’s an example of how you might map an xAPI statement to a CTDL learning opportunity:

BUT WAIT! Isn’t this too hard for admins to understand SQL Queries? No worries teach the Admins PowerBI, teach the Instructional Designers xAPI, teach the Curriculum Developers CTDL and leave the database to IT.

You can connect Power BI to your PostgreSQL database and create queries using the graphical user interface rather than writing command line queries. Here’s a step-by-step guide to achieve this:

Step-by-Step Guide to Query PostgreSQL Database with Power BI

Step 1: Install the PostgreSQL ODBC Driver

Ensure you have the PostgreSQL ODBC driver installed. You can download it from the PostgreSQL website.

Step 2: Launch Power BI Desktop

Open Power BI Desktop on your computer.

Step 3: Connect to PostgreSQL Database

  1. In Power BI Desktop, click on “Home” in the top menu.
  2. Click on “Get Data” and then select “More…”
  3. In the “Get Data” window, select “Database” and then “PostgreSQL database.”
  4. Click “Connect.”

Step 4: Enter Database Details

  1. In the “PostgreSQL Database” dialog, enter the server name and database name.
  2. Optionally, you can also enter any specific SQL query you want to run at this stage, or you can import the data and filter it within Power BI.
  3. Click “OK.”

Step 5: Provide Credentials

  1. If prompted, enter your PostgreSQL database credentials (username and password) and click “Connect.”

Step 6: Load Data

  1. Once connected, a navigator window will open where you can select the tables or views you want to import.
  2. Select the relevant tables (e.g., actors, ctdl_credentials, xapi_statements) and click “Load.”

Step 7: Create Relationships

  1. After loading the data, you can create relationships between the tables.
  2. Go to the “Model” view (click on the “Model” icon on the left sidebar).
  3. Drag and drop fields to create relationships between the tables based on foreign keys (e.g., actor_id in xapi_statements to id in actors).

Step 8: Create Visuals and Query Data

  1. Now, you can create visuals and query data using the Power BI interface.
  2. To find out who holds the Bachelor of Science in Computer Science credential, you can create a table visual that combines data from the related tables.
  3. Drag and drop fields from the actors, ctdl_credentials, and xapi_statements tables to the table visual.
  4. Apply filters to display only the records that match the credential criteria (e.g., completed verb, Bachelor of Science in Computer Science credential name).

Example: Creating a Visual

  1. Create a new table visual.
  2. Add the name and email fields from the actors table.
  3. Add the name field from the ctdl_credentials table.
  4. Add the verb and result_completion fields from the xapi_statements table.
  5. Apply filters to show only records where the verb is “completed,” result_completion is TRUE, and the ctdl_credentials.name is “Bachelor of Science in Computer Science.”

This setup will allow you to query the PostgreSQL database using Power BI’s graphical interface, making it easier to visualize and analyze your data without writing SQL commands.