Comment on page
Migrating from UDW to UDP
The UDP 2.0.53 release introduced a new feature whereby all Canvas-native data, independent of whether it can be joined with SIS data, is imported into the UDP context store. This means that Unizin member institutions may begin migrating their reports, applications, and other dependencies on the Unizin Data Warehouse (UDW) to the UDP Context store.
To facilitate the transition from the Canvas Data 1.0 schema (which is supported by the UDW) to the Unizin Common Data Model, we recommend using a mapping document. In the mapping file, every Canvas Data 1 table and column is matched to a UCDM entity and element.
For example, this query in the UDW:
Becomes the following query in the UDP Context store:
UDW Query
WITH course_modules AS (
SELECT
md.course_id AS course_id
, COUNT(1) AS num_modules
FROM
module_dim AS md
WHERE
workflow_state = 'active'
GROUP BY
md.course_id
)
SELECT
cd.code AS course_code
, cd.name AS course_name
, cm.num_modules AS num_modules
FROM
course_dim AS cd
LEFT JOIN course_modules AS cm ON cd.id=cm.course_id
;
Becomes the following query in the UDP Context store:
UDP Query
WITH course_offering_modules AS (
SELECT
m.course_offering_id AS course_offering_id
, COUNT(1) AS num_modules
FROM
entity.module AS m
WHERE
status = 'active'
GROUP BY
m.course_offering_id
)
SELECT
co.subject AS course_subject
, co.number AS course_number
, co.title AS course_title
, com.num_modules AS num_modules
FROM
entity.course_offering AS co
LEFT JOIN course_offering_modules AS com ON co.course_offering_id=com.course_offering_id
;
Last modified 1mo ago