Unizin Product Documentation
ProductsSupport and TrainingPolicies
  • Unizin Product Documentation
  • Products
    • Content
      • Unizin Engage
        • eReader User Guide
          • Notes, Highlights, and Citations
          • Appearance Settings
          • Download for Offline
          • eReader Layout
          • Keyboard Shortcuts
          • Navigating Your eBook
          • Print
          • Text to Speech
          • Copy and Paste
          • Creating Flashcards
          • Collaboration and Note Sharing
          • Pearson Titles
        • Institution Support
          • Disabled Student Services / Alt-Format
            • Best Practices for Republishing Course Content
            • Disabled Student Services
            • Requesting eTextbook Files for Accessibility Purposes
            • WCAG 2.0 AA evaluation for Engage
            • WCAG 2.0 AA evaluation for EPUB for Engage
          • Institution's Support Responsibilities
        • Caliper 1.1 sensor
        • Release Notes
          • 2.28.22
          • 2019-09-17
          • 2019-05-29
          • 2.26.8
          • 2.26.0
          • 2.25.0
          • 2.22.0
          • 2.21.6
          • 2.21.5
          • 2.20.8
          • 2.20.5
          • 2.20.3
          • 2.19.1
          • 2.18.0
          • 2.17.0
          • 2.14.0
          • 2.12.0
          • 2.11.0
          • 2.9.0
          • 2.8.3
          • 2016-03-17
          • 2016-02-11
          • 2016-01-28
        • Using Analytics (New)
      • Unizin Order Tool
        • Overview of the User Interface
        • Key Concepts
          • Profiles
          • Ordering periods
          • Coordinator permissions
          • Program administrator permissions
        • Courses & Ordering
          • Course filtering
          • Place an order
          • Add sections to a placed order
          • Edit a placed order
          • Cancel an order
          • Reordering
        • Order History
          • Instructor Order History
          • Coordinator and Program Administrator Order History
          • Order Activity
        • Student Choice
          • Student Choice (Program Administrators)
          • Student Choice (Students)
        • Entitlements
          • Entitlements (Program Administrators)
          • Entitlements (Students)
        • Catalog Tool
        • Schedule of Classes
        • Content Request Tool
        • Order Tool Dashboard
        • Vendor Sandbox Tenant
        • Institution Support
        • Implementation
          • SIS Data Integration
            • 1.0 - SIS Integration
            • 2.0 - SIS Integration
          • SSO integration
          • UI customizations
          • Order Feed
            • 1.0 - Order Feed
            • 2.0 - Order Feed
            • 3.0 - Order Feed
          • Publisher report
          • Final declined offers feed
          • Institutional (SIS) Catalog Import
          • Student Price
          • Historical Entitlements Import
        • Release Notes
          • Order Tool Bug Fixes and Enhancements
          • Order Tool Bug Fixes
          • Order Tool Accessibility Improvements
          • Order Feed Improvements
          • Content Request Form Update and Minor Bug Fix
          • Flat Markup Fee Update
          • Ordering Email Receipt Update & Minor Bug Fix
          • Bug Fix for Public Catalog Feature
          • Catalog Search Enhancements
          • Reordering Reminder Email Notifications
          • UX Improvements & Minor Bug Fixes
          • Historical Entitlements Import
          • Student Prices
          • Reordering Feature
          • Email Enhancements
          • Ordering Enhancements
          • Bug Fix for the Institutional Catalog Import
          • Bug Fix for the Final Declined Offers Feed (FDOF)
          • Order Activity Feature and Other Enhancements
          • Bug Fixes for Order History and Report an Issue Features
          • Public catalog feature
          • Minor Bug Fixes for Ordering and Student Choice
          • Entitlements Production Release, Bug fixes, and Minor updates
          • Minor Updates and Bug Fixes for Ordering Workflows
          • Catalog Search Optimization
          • Student Choice
          • Archive Terms Feature and Integration Improvements
          • Introduces the Program Administrator role, Catalog Tool, and Schedule
          • User interface updates and improvements
          • Order feed improvements
          • Order history, UI enhancements
          • Email notification upgrades, UI improvements
          • Order feed changes
          • New features for Course coordinators and upgrades to the UI
          • Changes to the Term, Course, and Section models; introduces a Session
          • Bug fixes, import improvements, and validation improvements
          • Tracking Order History
          • Publisher Reporting
          • Fixes the order feed, automates SIS data importing, and automates the generation of order feed repor
    • Data & Analytics
      • Unizin Data Platform
        • Key concepts
          • Platform overview
          • Data categories
          • Data models
          • Loading schemas
          • Keymap
        • Unizin Common Data Model
          • Academic structures (ERD)
          • Learners (ERD)
          • Course structures (ERD)
          • Course resources (ERD)
          • Learner activities (ERD)
          • Quizzes (ERD)
          • Social (ERD)
          • Course outcomes (ERD)
        • System overview
          • Context data pipeline
            • Context data ingress
            • Batch-ingest application
            • Batch-ingest db server
            • Context store
          • Event data pipeline
            • UDP Caliper endpoint
            • Approval process for implementing Caliper compliant tools
            • UDP Event enricher
            • Event store
        • Data stores
          • Data lake
            • UDP Context store
            • UDP Event store
              • Accessing the Event store
              • Expanded table
                • Expanded table: Canvas edApp mapping
            • Synthetic Data [beta]
              • Viewing Synthetic Data datasets within the BigQuery UI
              • Query Synthetic Data via client libraries
          • Data marts
            • UDP Distributions
            • Interaction sessions
            • Learning Environment Organization
            • File Interaction
            • Last Activity
            • Long Inactivity
            • Course Status
            • Daily Course Grade Record
            • LTI Tool Use
            • LMS Tool Use
            • Tool Usage Metrics
            • Links
            • Taskforce
              • Level 1 Aggregated
              • Level 2 Aggregated
              • Level 2 Course Weekly Distribution Summary
              • Student Term Profile
              • Course Profile
            • Student Activity Score
              • Student Course Metrics
              • Student Course Section Metrics
              • Final
              • Course Final
              • Course Section Final
        • Data integrations
          • Context data integration
            • Loading schema
            • Keymap support
            • Manifest file
            • File requirements
            • Integration mechanics
          • Event data integration
          • SIS data integration
          • LMS data integration
            • Instructure Canvas
        • Release Notes
          • UDP Marts Release Notes
            • 1.0.83
            • 1.0.80
            • 1.0.79
            • 1.0.78
            • 1.0.77
            • 1.0.72
            • 1.0.67
            • 1.0.58
            • 1.0.51
            • 1.0.44
            • 1.0.42
            • 1.0.32
            • 1.0.31
            • 1.0.0
            • Level 2 Taskforce data marts now available
          • 2.0.167
          • 2.0.152
          • 2.0.138
          • 2.0.137
          • 2.0.113
          • 2.0.112
          • 2.0.111
          • 2.0.110
          • 2.0.99
          • 2.0.98
          • 2.0.83
          • 2.0.80
          • 2.0.71
          • 2.0.66
          • 2.0.59
          • 2.0.58
          • 2.0.53
          • 2.0.47
          • 2.0.25
        • Miscellaneous
          • Canvas Data additions, ~Fall 2021
          • Canvas Live Events: from SQS to HTTPS
          • Canvas New Analytics vs. UDP
          • Course Section Enrollment Role Status Mappings
          • Migrating from UDW to UDP
      • Unizin Data Warehouse
        • Implementation Guide
        • Scope of Services
        • Access Provisioning
        • Access Revocation
        • Connecting to the UDW
      • Raw Canvas Data 2
        • Flat Files
        • BigQuery Datasets
    • Hosted Services
      • My Learning Analytics
        • Install MyLA via LTI 1.3
        • Custom configure MyLA
  • Support and Training
    • Professional Development
      • Stepping Stones: A Faculty Development Curriculum for Learning Analytics Use
      • Structured Conversations initiative
    • UDP Self-paced Training
    • Resources Site Broken Links
    • Status Pages
  • Policies
    • General policies
      • Sponsor Teams
      • Browser Support Policy
      • Opt-Out & Invoicing Policy (Order Tool)
    • Support Policy
      • Unizin Engage - SP
      • Unizin Order Tool - SP
      • Unizin Data Platform - SP
      • Unizin Data Warehouse - SP
      • Unizin Data Analysis - SP
      • Pressbooks Hosting - SP
    • Privacy Policy
      • Unizin Engage - PP
      • Unizin Order Tool - PP
      • Unizin Data Platform - PP
      • RStudio service - PP
    • End User License Agreements
      • Unizin Engage - EULA
      • Unizin Order Tool - EULA
    • Terms of Use
      • Unizin Data Platform - ToU
    • Incident Reports
Powered by GitBook
On this page
  • Features of the "expanded" table
  • Identifiers
  • Use of the STRUCT data type
  • Root nodes
  • Sub-nodes
  • Dates and times
  • Table partitioning
  • Canvas events data mapping
  • Schema
  1. Products
  2. Data & Analytics
  3. Unizin Data Platform
  4. Data stores
  5. Data lake
  6. UDP Event store

Expanded table

PreviousAccessing the Event storeNextExpanded table: Canvas edApp mapping

Last updated 11 hours ago

LogoLogo

Unizin Homepage

  • unizin.org

Data & Analytics

  • Unizin Data Platform
  • Unizin Data Warehouse

Content

  • Unizin Engage
  • Unizin Order Tool

Hosted Services

  • My Learning Analytics

Copyright © 2023, Unizin, Ltd.

The UDP Event store's expanded table explodes the nested attributes of IMS Global Caliper events into a tabular format. By decomposing and flattening an event's hierarchical data, the UDP Event store's expanded table makes querying event data far more performant and efficient.

Features of the "expanded" table

The UDP Event store schema explodes the nested attributes and values of a Caliper event into a single, tabular schema. The UDP Event store also computes a small number of variables that make downstream querying and reporting more convenient.

Identifiers

The expanded table presents many identifiers related to an event. The identifiers in the expanded table fall into one of two categories.

  • Given identifiers are the values in the id fields of a Caliper event. They are almost always that use a qualified namespace to communicate a native identifier. These identifiers are represented, unchanged, in id columns in the expanded table. For example, the group.id column in the expanded table contains the IRI value found in the group[id] location of the Caliper event.

  • Learning tool identifiers. The UDP is able to use event data to unambiguously identify, generate, or lookup a tool's native identifier. When this is possible, the UDP will store the learning tool's identifiers with column names that communicate to which tool an identifier belongs. For example, the engage_id and sis_id columns represent identifiers fro Unizin Engage and an SIS, respectively. The values for these identifiers are represented in their native form (not using IRIs). At present, learning tool identifiers are provided for Persons and Course offerings.

To illustrate the different categories of identifiers captured in the UDP Event store, consider a single event from Instructure Canvas. The actor.id column value for this event will be the given, fully qualified IRI value in the actor[id] location (e.g., "urn:instructure:canvas:user:1"). By contrast, the person.canvas_id value will be the native, unqualified identifier (e.g., "1").

Use of the STRUCT data type

The UDP Event store's expanded table is implemented in Google BigQuery, which supports the . A STRUCT data type is a container of ordered fields, each of which is defined by its own data type and a name. The advantage of a STRUCT data type is that it enables you to store multiple attributes related to a single object in a single row.

The UDP Event store's expanded table makes liberal use of the STRUCT data type to represent data points in Caliper events that contain 1 or more other data points. For example, the actor attribute in a Caliper event will typically look like this:

{
  "id": "urn:vendor:tool:user:12345",
  "type": "Person"
}

In the UDP Event store's expanded table, this data is represented as an actor STRUCT with two fields: id and type. To query a field in a STRUCTure, you use the dot (.) operator, like so:

SELECT
  actor.id AS actor_id
FROM
  event_store.expanded
WHERE
  event_time >= '2021-01-01'
;

Root nodes

As a JSON object, a Caliper event will contain a number of root attributes, many of which are nodes for nested attributes and sub-nodes. For example, the following event describes that an instructor has graded a particular student assignment:

{
  "@context": "http://purl.imsglobal.org/ctx/caliper/v1p1",
  "id": "urn:uuid:cc0115bd-da36-450a-87aa-6c29a2f632f7",
  "eventTime": "2020-10-01T00:01:31.480Z",
  "type": "GradeEvent",
  "action": "Graded",
  "actor": {
    "id": "urn:tool:user:1",
    "type": "Person"
  },
  "edApp": {
    "id": "http://learningtool.com/",
    "type": "SoftwareApplication"
  },
  "object": {
    "id": "urn:tool:submission:5",
    "type": "Attempt",
    "assignable": {
      "id": "urn:tool:assignment:2",
      "type": "AssignableDigitalResource"
    },
    "assignee": {
      "id": "urn:tool:user:1",
      "type": "Person"
    }
  }
}

In this event, the object node contains a mix of attributes and sub-nodes. For every Caliper event, the UDP Event store will represent a certain number of root nodes as STRUCTs. Each of these structs will contain the following attributes:

Attribute
Description

id

The value of the id attribute in the node.

type

The value of the type attribute in the node.

name

The value of the name attribute in the node.

extensions

The value of the extensions attribute in the node.

json

The node's full JSON.

Given the example above, here is how you might query the Event store for the attributes of the object node in Caliper events:

SELECT
  object.id AS object_id
  , object.extensions as object_extensions
FROM
  event_store.expanded
WHERE
  event_time >= '2021-01-01'
;

The following root nodes of Caliper events are:

  • actor

  • edApp

  • extensions

  • federatedSession

  • generated

  • group

  • membership

  • object

  • profile

  • referrer

  • session

  • target

Sub-nodes

In a Caliper event, the generated and object root nodes often contain sub-nodes called assignable, assignee, and attempt, which usually refer to an assigned learning activity (assignable) and the person who completed or created it (assignee).

The UDP Event store will extract these sub-nodes and as attributes of their parent nodes STRUCTs. The sub-nodes are then represented as nested STRUCTs.

Given the example "graded" event above, the following SQL can be used to query the type attributes of a nested assignable attribute:

SELECT
  object.assignable.type as object_assignable_type
FROM
  event_store.expanded
WHERE
  event_time >= '2021-01-01'
;

Dates and times

The UDP Event store records when the event was written to the UDP Event store (store_time) along with when the behavior occurred (event_time) and the date and hour when the behavior occurred (event_date and event_hour). The expanded table dates and times are listed in UTC.

Table partitioning

The UDP Event store table is a partitioned table. A partitioned table is divided into segments (called partitions) along a particular column in the table schema. Partitioned tables are more performant and cost-effective if queries on the table use the relevant column in queries on the table.

The UDP Event store table is partitioned on the event_time column. Consequently, queries against the UDP Event store that select fixed periods of time to query data (using event_time) will be generally more performant and cost less.

Canvas events data mapping

Schema

Each record in the UDP Event store table represents a single, timestamped event.

The UDP Event store table schema enables common event query patterns over sets of Caliper events.

The following table describes the Event record schema for the UDP Event store.

Column
Description

id

The event UUID is generated by the Caliper sensor that emitted the event or, if no UUID is part of the event, as generated by the UDP Caliper endpoint.

store_time

The date and time when the event record was written to the event store.

event_time

The timestamp representing when the behavior occured.

event_date

The date, in YYYY-MM-DD format, that corresponds to the event_time.

event_hour

The hour, in 0-23 integer format, that corresponds to the event_time.

type

The value of the Caliper 1.1 type, as required by the standard.

action

The value of the Caliper 1.1 action, as required by the standard.

ed_app

A STRUCT containing a number of attributes for the edApp data in the Caliper event.

ed_app.id

The value of the id attribute in the edApp node of the event.

ed_app.type

The value of the type attribute in the edApp node of the event.

ed_app.name

The value of the name attribute in the edApp node of the event.

ed_app.extensions

The JSON blob of the extensions attribute in the edApp node of the event.

ed_app.json

The JSON blob of the edApp node of the event.

actor

A STRUCT containing a number of attributes for the actor data in the Caliper event.

actor.id

The value of the id attribute in the actor of the event.

actor.type

The value of the type attribute in the actor of the event.

actor.name

The value of the name attribute in the actor of the event.

actor.extensions

The JSON blob of the extensions attribute in the actor node of the event.

actor.json

The JSON blob of the actor node of the event.

person

A STRUCT that contains a variety of identifies for the actor in the Caliper event.

person.udp_id

The UDP ID for the Person (actor).

person.canvas_id

The Canvas ID for the Person (actor).

person.roles

The role(s) array of the Person who performed the behavior.

person.canvas_username

The Canvas username for the Person (actor).

person.sis_id

The SIS ID for the Person (actor).

group

A STRUCT containing a number of attributes for the group data in the Caliper event.

group.id

The value of the id attribute in the group of the event.

group.type

The value of the type attribute in the group of the event.

group.name

The value of the name attribute in the group of the event.

group.extensions

The JSON blob of the extensions attribute in the group node of the event.

group.json

The JSON blob of the group node of the event.

course_offering

A STRUCT that contains a variety of identifiers for the courseOffering in the Caliper event.

course_offering.id

The ID of the Course offering, according to the learning tool that emitted the event.

course_offering.udp_id

The UDP ID for the Course offering.

course_offering.canvas_id

The Canvas ID for the Course offering.

membership

A STRUCT containing a number of attributes for the membership data in the Caliper event.

membership.id

The value of the id attribute in the membership of the event.

membership.type

The value of the type attribute in the membership of the event.

membership.name

The value of the name attribute in the membership of the event.

membership.extensions

The JSON blob of the extensions attribute in the membership node of the event.

membership.json

The JSON blob of the membership node of the event.

profile

A STRUCT containing a number of attributes for the profile data in the Caliper event.

profile.id

The value of the id attribute in the profile of the event.

profile.type

The value of the type attribute in the profile of the event.

profile.name

The value of the name attribute in the profile of the event.

profile.extensions

The JSON blob of the extensions attribute in the profile node of the event.

profile.json

The JSON blob of the profile node of the event.

referrer

A STRUCT containing a number of attributes for the referrer data in the Caliper event.

referrer.id

The value of the id attribute in the referrer of the event.

referrer.type

The value of the type attribute in the referrer of the event.

referrer.name

The value of the name attribute in the referrer of the event.

referrer.extensions

The JSON blob of the extensions attribute in the referrer node of the event.

referrer.json

The JSON blob of the referrer node of the event.

session

A STRUCT containing a number of attributes for the session data in the Caliper event.

session.id

The value of the id attribute in the session of the event.

session.type

The value of the type attribute in the session of the event.

session.name

The value of the name attribute in the session of the event.

session.extensions

The JSON blob of the extensions attribute in the session node of the event.

session.json

The JSON blob of the session node of the event.

target

A STRUCT containing a number of attributes for the target data in the Caliper event.

target.id

The value of the id attribute in the target of the event.

target.type

The value of the type attribute in the target of the event.

target.name

The value of the name attribute in the target of the event.

target.extensions

The JSON blob of the extensions attribute in the target node of the event.

target.json

The JSON blob of the target node of the event.

generated

A STRUCT containing a number of attributes for the generated node in the Caliper event.

generated.id

The value of the id attribute in the generated node of the event.

generated.type

The value of the type attribute in the generated node of the event.

generated.name

The value of the name attribute in the generated node of the event.

generated.extensions

The JSON blob of the extensions attribute in the generated node of the event.

generated.json

The JSON blob of the generated node of the event.

generated.assignable

A STRUCT containing a number of attributes for the generated.assignable data in the Caliper event.

generated.assignable.id

The value of the id attribute in the generated.assignable node of the event.

generated.assignable.type

The value of the type attribute in the generated.assignable node of the event.

generated.assignable.name

The value of the name attribute in the generated.assignable node of the event.

generated.assignable.extensions

The JSON blob of the extensions attribute in the generated.assignable node of the event.

generated.assignable.json

The JSON blob of the generated.assignable node of the event.

generated.assignee

A STRUCT containing a number of attributes for the generated.assignee data in the Caliper event.

generated.assignee.id

The value of the id attribute in the generated.assignee node of the event.

generated.assignee.type

The value of the type attribute in the generated.assignee node of the event.

generated.assignee.name

The value of the name attribute in the generated.assignee node of the event.

generated.assignee.extensions

The JSON blob of the extensions attribute in the generated.assignee node of the event.

generated.assignee.json

The JSON blob of the generated.assignee node of the event.

generated.attempt.assignable

A STRUCT containing a number of attributes for the generated.assignable data in the Caliper event.

generated.attempt.assignable.id

The value of the id attribute in the generated.attempt.assignable node of the event.

generated.attempt.assignable.type

The value of the type attribute in the generated.attempt.assignable node of the event.

generated.attempt.assignable.name

The value of the name attribute in the generated.attempt.assignable node of the event.

generated.attempt.assignable.extensions

The JSON blob of the extensions attribute in the generated.attempt.assignable node of the event.

generated.attempt.assignable.json

The JSON blob of the generated.attempt.assignable node of the event.

generated.attempt.assignee

A STRUCT containing a number of attributes for the generated.attempt.assignee data in the Caliper event.

generated.attempt.assignee.id

The value of the id attribute in the generated.attempt.assignee node of the event.

generated.attempt.assignee.type

The value of the type attribute in the generated.attempt.assignee node of the event.

generated.attempt.assignee.name

The value of the name attribute in the generated.attempt.assignee node of the event.

generated.attempt.assignee.extensions

The JSON blob of the extensions attribute in the generated.attempt.assignee node of the event.

generated.attempt.assignee.json

The JSON blob of the generated.attempt.assignee node of the event.

object

A STRUCT containing a number of attributes for the object node in the Caliper event.

object.id

The value of the id attribute in the object node of the event.

object.type

The value of the type attribute in the object node of the event.

object.name

The value of the name attribute in the object node of the event.

object.extensions

The JSON blob of the extensions attribute in the object node of the event.

object.json

The JSON blob of the object node of the event.

object.assignable

A STRUCT containing a number of attributes for the object.assignable node in the Caliper event.

object.assignable.id

The value of the id attribute in the object.assignable node of the event.

object_assignable.type

The value of the type attribute in the object.assignable node of the event.

object_assignable.name

The value of the name attribute in the object.assignable node of the event.

object_assignable.extensions

The JSON blob of the extensions attribute in the object.assignable node of the event.

object_assignable.json

The JSON blob of the object.assignable node of the event.

object.assignee

A STRUCT containing a number of attributes for the object.assignee node in the Caliper event.

object.assignee.id

The value of the id attribute in the object.assignable node of the event.

object.assignee.type

The value of the type attribute in the object.assignable node of the event.

object.assignee.name

The value of the name attribute in the object.assignable node of the event.

object.assignee.extensions

The JSON blob of the extensions attribute in the object.assignable node of the event.

object.assignee.json

The JSON blob of the object.assignable node of the event.

extensions_json

The JSON blob of the extensions node of the event.

client_ip

The IP address reported in the event. This usually refers to the public IP addresses of the individual who performed the event.

federated_session_json

The JSON blob of the federated_session node of the event.

Unique to the expanded events table is how the STRUCT data type allows for the normalization of Canvas events data. For further information, please visit our .

Beyond storing the entirety of a Caliper event payload itself, the UDP Event store schema defines a set of columns for values that are common to query patterns. The values are extracted from the Caliper event payload during the event ETL process. The values include the UDP identifiers that correspond to an event’s native identifiers if the event qualifies for . For example, values in the actor_id column of the Event store correspond to the id value in the actor node of the Caliper event.

IRIs
STRUCT data type
documentation on Canvas edApp mapping
enrichment