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
;