UDP Context store

The UDP Context store is a Google CloudSQL instance running PostgreSQL that stores and represents all of the context data imported into a UDP instance.

It is in the Context store that you will find data that is commonly-modeled and coalesced from the SIS, LMS, and other teaching and learning tools. The UDP Context store also contains the keymap for a particular UDP instance. The keymap maintains the relationships between the UDP Context store’s own primary, surrogate identifiers for context data and the corresponding identifiers from their native systems.

Accessing the Context store

Google maintains documentation on how to access and use a Google CloudSQL instance.

You can connect to the Context store with any tool that can connect to a PostgreSQL database. Generally speaking, most open-source and commercial SQL clients that support JDBC/ODBC connections will work.

Unizin Member access

Unizin is required to seek authorization from your Institution’s UDP Data steward. Unizin will not grant any individual access to the Context store without explicit authorization from a recognized UDP data steward.

Request access to the UDP Context store at your institution.

Unizin will only create user accounts after it has received authorization from the UDP Data steward. Once approved, a username and password are sent securely to the individual by email. Note that access to your institution's UDP context store is restricted by IP address. Your Institution’s UDP Sponsor team will provide Unizin Support with Institutional IP addresses (or IP blocks) to whitelist. Additionally, an authorized UDP Data Steward at your institution must allow any particular individual access to the UDP.

Relevant connection details include:

Hostname	Production environment hostname: context-store.<tenant>.cloud.unizin.org SIT (systems integration test) environment hostname: context-store.<tenant>.sit.cloud.unizin.org Replace <tenant> with the primary web domain of your institution. For example, the University of Iowa would replace <tenant> with "uiowa," given that the primary domain name of its institution is uiowa.edu.
Port	5432
Database	context_store

Schemas

The context_store database is composed of multiple schemas. Each schema is composed of views (not tables; see below). In aggregate, the schemas and tables of the context_store database make up the UDP Context store as a whole.

In PostgreSQL, database schemas act as namespaces in which tables, views, indexes, and other objects reside. By default when you access PostgreSQL, your “search path” (which defines the namespaces you can see) will be set to the “public” schema. You must change your search path to include the “entity” and possibly “keymap” schemas/namespaces. If you are using the psql command-line tool, for example, you would do that with this command:

The two critical schemas in the context_store database are the entity schema and keymap schema. There is also a report schema which lists metadata tables.

Entity schema	The entity schema is used to present all of the coalesced context data in the UDP in a relational model
Keymap schema	The keymap schema is used to present the UDP’s keymapping relationships between the surrogate identifiers created by the UDP (in postgres) and the native identifiers given by a source system.
Report schema	The report schema is used to present metadata tables. In the future, this schema may present data from other reports or logs from sources such as Airflow, Canvas data, or SIS that don't map into the UDP's context store.

Views

The UDP Context store's entity and keymap schemas are populated with views, not tables. The views point to tables in other schemas in the database, where entity and keymap data resides. It is important to highlight that you will interact with views over tables because:

• Views behave a bit differently than tables in PostgreSQL

• Most database clients separate views from tables in their presentation of the contents of a database schema

Entity schema

The views in the entity schema correspond to entities of the Unizin Common Data Model (UCDM).

UCDM Entities & elements correspond to Entity schema tables & columns.

There is one view in the entity schema per Entity defined in the Unizin Common Data Model’s data dictionary. The columns in the views correspond to the elements defined for the entity in the UCDM.

For example, the UCDM defines the Course offering entity as composed by the following elements:

Element	Element code	Definition
Academic career id	academic_career_id	The foreign key to the Academic career for which this Course offering is primarily offered (note: the Course offering may still be available to other Academic careers at the institution; what is required here is the primary Academic career for which this Course offering is offered).
Academic session id	academic_session_id	The foreign key to the Academic session to which this Course offering belongs.
Available credits	available_credits	Measured in Carnegie units, the amount of credit available to a student who successfully meets the objectives of the Course Offering.
Campus ID	campus_id	The foreign key to the Campus to which this Course offering belongs.
CIP Code 2k	cip_code2k	A code representing the Classification of Instructional Program for the Course offering.
Created date	created_date	The date the Course offering was created.
Description	description	The Course Offering description.
End date	end_date	The date and time of the last Course offering meeting, which may be different from the last Course section meeting. Note: If more than one meeting pattern exists, the first meeting pattern will be chosen.
Is Public?	is_public	Indicates whether the Course offering is publicly visible.
LE status	le_status	From the learning environment, the workflow state of the Course offering, usually in relation to its availability for teaching and learning.
Multiple meeting patterns?	multiple_meeting_patterns	Indicates whether the class has multiple meeting patterns.
Number	number	The official reference number portion of a course Id. This number normally designates the level of the course as well as the level of the individual expected to enroll in the course. E.g. "101" in "BIO 101. This is sometimes called the catalog number.
Start date	start_date	The date and time of the first Course offering meeting, which may be different from the first Course section meeting. Note: If more than one meeting pattern exists, the first meeting pattern will be chosen.
Status	status	The workflow state of the Course offering, usually in relation to its availability for teaching and learning.
Subject	subject	The alphabetic abbreviation of the academic department or discipline offering the course. It is one part of the total course Id number. E.g., "BIO" in "BIO 101.”
Syllabus content	syllabus_content	The syllabus content for this Course offering.
Title	title	The title of the Course Offering.

The corresponding view in the entity schema is called course_section:

context_store=> \d entity.course_offering
                              View "entity.course_offering"
          Column           |            Type             | Collation | Nullable | Default 
---------------------------+-----------------------------+-----------+----------+---------
 course_offering_id        | bigint                      |           |          | 
 academic_career_id        | bigint                      |           |          | 
 academic_session_id       | bigint                      |           |          | 
 campus_id                 | bigint                      |           |          | 
 available_credits         | numeric                     |           |          | 
 cip_code2k                | text                        |           |          | 
 created_date              | timestamp without time zone |           |          | 
 description               | text                        |           |          | 
 end_date                  | date                        |           |          | 
 is_public                 | boolean                     |           |          | 
 le_status                 | text                        |           |          | 
 multiple_meeting_patterns | boolean                     |           |          | 
 number                    | text                        |           |          | 
 start_date                | date                        |           |          | 
 status                    | text                        |           |          | 
 subject                   | text                        |           |          | 
 syllabus_content          | text                        |           |          | 
 title                     | text                        |           |          | 

Each Element of the Course section Entity maps to a column in the course_offering view.

Wherever an Element refers to an identifier in another Entity, a foreign key will be defined in the view. For example, Course offering's Academic career element (academic_career_id) is a foreign key to the entity.academic_career view).

You will notice that there is one column not present in the data dictionary that is present in the view: course_section_id, or, more generally, the primary key (PK) for the entity view. This is because the table’s PK is not relevant to the conceptual UCDM.

Most if not all of context data queries should be able to be run through some combination of entity.* views.

Keymap schema

The keymap schema contains views that implement the UDP Keymap concept. The keymap schema is useful if you want to look up a native Context store ID for an Entity with that Entity’s identifier from another system, like the SIS or LMS.

Like the entity schema, the keymap schema is populated with views whose names correspond to entities in the Unizin Common Data Model. Each view in the keymap schema is a keymap that associates data from disparate systems (e.g., SIS, LMS). This schema can be useful if you need to look up the UDP identifier but only have the LMS or SIS identifier in hand.

Consider, for example, the Course section entity, whose records will be sourced from many systems, including the SIS and the LMS. Given the multi-system source for Course section records, the Entity's corresponding keymap view contains columns for the identifiers of each source system. In the view definition below, we see that the columns correspond to the SIS and LMS identifiers for a common Course section:

context_store=> \d keymap.course_section
                                      Table "keymap.course_section"
  Column   |       Type        | Collation | Nullable |                      Default                     
------------+-------------------+-----------+----------+---------------------------------------------------
id         | bigint            |           | not null | nextval('keymap.course_section_id_seq'::regclass)
sis_int_id | character varying |           |          |
sis_ext_id | character varying |           |          |
lms_int_id | character varying |           |          |
lms_ext_id | character varying |           |          | 
...

The entity and keymap views share common identifiers. Hence, the primary key for a record in an entity view is associated with the primary key of the same value in the corresponding keymap view. This fact enables users to look up records in the entity schema using any particular identifier (UDP, SIS, LMS, and other identifiers) to lookup their kin from other systems.

For example, you can run the following query to return the Canvas global ID for a course section, given its UDP id of 10:

context_store=> select kcs.lms_ext_id 
from entity.course_section ecs 
join keymap.course_section kcs 
    on ecs.course_section_id = kcs.id 
where ecs.course_section_id = 10;

lms_ext_id
------------
12345

Report Schema

The report schema is used to present metadata tables. In the future, this schema may present data from other reports or logs from sources such as Airflow, Canvas data, or SIS that may not map into the UDP's context store.

You may run the below query to return results for the keymap and entity tables and their last published run information. Note: The dag_run is intended to display the name of the ingest’s ‘run.’

select * from report.publish_info;

schema_name |                      table_name                      | published  |  dag_run   
-------------+------------------------------------------------------+------------+------------
 keymap      | learning_outcome_item_response                       | 2022-07-08 | 2022-07-07
 keymap      | facility                                             | 2022-07-08 | 2022-07-07
 keymap      | learner_group_membership                             | 2022-07-08 | 2022-07-07
 keymap      | room                                                 | 2022-07-08 | 2022-07-07
 entity      | learner_activity_group_grade                         | 2022-07-08 | 2022-07-07
 keymap      | academic_minor                                       | 2022-07-08 | 2022-07-07
 entity      | academic_degree                                      | 2022-07-08 | 2022-07-07
 keymap      | admission_test_score                                 | 2022-07-08 | 2022-07-07
 keymap      | learning_outcome_result                              | 2022-07-08 | 2022-07-07
 keymap      | grading_period_grade                                 | 2022-07-08 | 2022-07-07
 keymap      | academic_major                                       | 2022-07-08 | 2022-07-07
 keymap      | annotation                                           | 2022-07-08 | 2022-07-07
 keymap      | person_email                                         | 2022-07-08 | 2022-07-07
 keymap      | quiz_item                                            | 2022-07-08 | 2022-07-07
 keymap      | conversation_message                                 | 2022-07-08 | 2022-07-07
 entity      | quiz_item_response                                   | 2022-07-08 | 2022-07-07
 entity      | institutional_affiliation                            | 2022-07-08 | 2022-07-07
 keymap      | academic_term                                        | 2022-07-08 | 2022-07-07
 keymap      | course_section                                       | 2022-07-08 | 2022-07-07
 keymap      | course_offering                                      | 2022-07-08 | 2022-07-07
 entity      | quiz_result                                          | 2022-07-08 | 2022-07-07
 keymap      | file                                                 | 2022-07-08 | 2022-07-07
 entity      | quiz_item_group                                      | 2022-07-08 | 2022-07-07
 entity      | grading_period                                       | 2022-07-08 | 2022-07-07
 entity      | annotation                                           | 2022-07-08 | 2022-07-07
 entity      | discussion                                           | 2022-07-08 | 2022-07-07
 entity      | academic_major                                       | 2022-07-08 | 2022-07-07
 keymap      | quiz_result                                          | 2022-07-08 | 2022-07-07
 entity      | grading_period_grade                                 | 2022-07-08 | 2022-07-07
 keymap      | participant_session                                  | 2022-07-08 | 2022-07-07
 keymap      | academic_degree                                      | 2022-07-08 | 2022-07-07
 keymap      | wiki_page                                            | 2022-07-08 | 2022-07-07
 entity      | course_section                                       | 2022-07-08 | 2022-07-07
 entity      | facility                                             | 2022-07-08 | 2022-07-07
 keymap      | quiz_item_group                                      | 2022-07-08 | 2022-07-07
 entity      | academic_organization                                | 2022-07-08 | 2022-07-07
 entity      | academic_session                                     | 2022-07-08 | 2022-07-07
 entity      | participant_session                                  | 2022-07-08 | 2022-07-07
 keymap      | academic_organization                                | 2022-07-08 | 2022-07-07
 entity      | participant_session_role                             | 2022-07-08 | 2022-07-07
 keymap      | module_item                                          | 2022-07-08 | 2022-07-07
 keymap      | learning_outcome_group                               | 2022-07-08 | 2022-07-07
 keymap      | participant_session_role                             | 2022-07-08 | 2022-07-07
 keymap      | academic_session                                     | 2022-07-08 | 2022-07-07
 keymap      | learner_activity_result                              | 2022-07-08 | 2022-07-07
 entity      | file                                                 | 2022-07-08 | 2022-07-07
 entity      | learner_activity_result                              | 2022-07-08 | 2022-07-07
 keymap      | academic_major_specialization                        | 2022-07-08 | 2022-07-07
 entity      | course_offering                                      | 2022-07-08 | 2022-07-07
 keymap      | person_phone_number                                  | 2022-07-08 | 2022-07-07
 entity      | module_progression                                   | 2022-07-08 | 2022-07-07
 entity      | module_item                                          | 2022-07-08 | 2022-07-07
 keymap      | person__academic_degree                              | 2022-07-08 | 2022-07-07
 entity      | person__academic_major_specialization__academic_term | 2022-07-08 | 2022-07-07
 keymap      | role                                                 | 2022-07-08 | 2022-07-07
 keymap      | grading_period                                       | 2022-07-08 | 2022-07-07
 entity      | quiz_item                                            | 2022-07-08 | 2022-07-07
 keymap      | learning_outcome_rubric_criteria                     | 2022-07-08 | 2022-07-07
 keymap      | learner_activity_override                            | 2022-07-08 | 2022-07-07
 keymap      | learner_activity_group_grade                         | 2022-07-08 | 2022-07-07
 keymap      | academic_program                                     | 2022-07-08 | 2022-07-07
 entity      | person__academic_term                                | 2022-07-08 | 2022-07-07
 keymap      | module                                               | 2022-07-08 | 2022-07-07
 entity      | person__academic_minor__academic_term                | 2022-07-08 | 2022-07-07
 keymap      | discussion_entry                                     | 2022-07-08 | 2022-07-07
 keymap      | conversation                                         | 2022-07-08 | 2022-07-07
 keymap      | academic_career                                      | 2022-07-08 | 2022-07-07
 keymap      | grading_period_group                                 | 2022-07-08 | 2022-07-07
 entity      | academic_program                                     | 2022-07-08 | 2022-07-07
 keymap      | module_progression                                   | 2022-07-08 | 2022-07-07
 entity      | wiki                                                 | 2022-07-08 | 2022-07-07
 entity      | wiki_page                                            | 2022-07-08 | 2022-07-07
 entity      | academic_career                                      | 2022-07-08 | 2022-07-07
 entity      | conversation                                         | 2022-07-08 | 2022-07-07
 entity      | grading_period_group                                 | 2022-07-08 | 2022-07-07
 entity      | module                                               | 2022-07-08 | 2022-07-07
 keymap      | quiz_item_response                                   | 2022-07-08 | 2022-07-07
 entity      | learner_activity_override                            | 2022-07-08 | 2022-07-07
 entity      | person_email                                         | 2022-07-08 | 2022-07-07
 entity      | learning_outcome_rubric_criteria                     | 2022-07-08 | 2022-07-07
 entity      | conversation_message                                 | 2022-07-08 | 2022-07-07
 keymap      | module_item_completion                               | 2022-07-08 | 2022-07-07
 entity      | person__academic_major__academic_term                | 2022-07-08 | 2022-07-07
 entity      | course_grade                                         | 2022-07-08 | 2022-07-07
 entity      | discussion_entry                                     | 2022-07-08 | 2022-07-07
 keymap      | person                                               | 2022-07-08 | 2022-07-07
 entity      | person__academic_degree                              | 2022-07-08 | 2022-07-07
 entity      | learning_outcome_result                              | 2022-07-08 | 2022-07-07
 entity      | course_section_enrollment                            | 2022-07-08 | 2022-07-07
 keymap      | learner_group                                        | 2022-07-08 | 2022-07-07
 keymap      | quiz                                                 | 2022-07-08 | 2022-07-07
 keymap      | learner_activity                                     | 2022-07-08 | 2022-07-07
 entity      | learning_outcome_group                               | 2022-07-08 | 2022-07-07
 entity      | person_phone_number                                  | 2022-07-08 | 2022-07-07
 keymap      | discussion                                           | 2022-07-08 | 2022-07-07
 keymap      | wiki                                                 | 2022-07-08 | 2022-07-07
 entity      | room                                                 | 2022-07-08 | 2022-07-07
 keymap      | campus                                               | 2022-07-08 | 2022-07-07
 entity      | role                                                 | 2022-07-08 | 2022-07-07
 keymap      | learner_activity_group                               | 2022-07-08 | 2022-07-07
 entity      | module_item_completion                               | 2022-07-08 | 2022-07-07
 keymap      | learning_outcome                                     | 2022-07-08 | 2022-07-07
 entity      | campus                                               | 2022-07-08 | 2022-07-07
 entity      | learner_group_membership                             | 2022-07-08 | 2022-07-07
 entity      | learning_environment_organization                    | 2022-07-08 | 2022-07-07
 keymap      | learning_environment_organization                    | 2022-07-08 | 2022-07-07
 entity      | academic_major_specialization                        | 2022-07-08 | 2022-07-07
 entity      | learning_outcome_item_response                       | 2022-07-08 | 2022-07-07
 entity      | learner_group                                        | 2022-07-08 | 2022-07-07
 entity      | learning_outcome                                     | 2022-07-08 | 2022-07-07
 entity      | academic_term                                        | 2022-07-08 | 2022-07-07
 entity      | academic_minor                                       | 2022-07-08 | 2022-07-07
 entity      | admission_test_score                                 | 2022-07-08 | 2022-07-07
 entity      | learner_activity_group                               | 2022-07-08 | 2022-07-07
 entity      | quiz                                                 | 2022-07-08 | 2022-07-07
 entity      | person                                               | 2022-07-08 | 2022-07-07
 entity      | learner_activity                                     | 2022-07-08 | 2022-07-07

Materialized Views

Materialized views are a snapshot of certain data derived from a specific query in the context_store. Performance benefits can be realized when using a materialized view as compared to querying the context_store's schema views. Since this is a snapshot of data, it is automatically refreshed according to a schedule.

Auto-Refreshed Materialized Views

Each day, at 14:00 UTC (9 AM CDT, 8 AM CST), any materialized view with the owner of the institution role is automatically refreshed.

-- To use the "institution" owner, set the role before
-- creating your views ...
set role institution;
create materialized view example_view as (...);

-- ... Or, alter existing views to be owned by 'institution'
alter materialized view example_view owner to institution;

These views are refreshed in order based on their name.

-- If you need to guarantee a particular order,
-- name your views with a prefix that sorts well.
create materialized view r1_example_view as (...);
create materialized view r2_example_view as (...);

-- To check the order is as you expect, run the same
-- query the scheduled refresh job runs
select
  schemaname, matviewname
from pg_matviews
where
  matviewowner = 'institution'
  order by schemaname, matviewname asc;