Deobfuscate user_dim.id?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Canvas Data portal question: how do I link from somewhere (say Quiz_submission_fact.user_id) to a user? the "id" field in the user_dim table (downloaded) is described as "obfuscated" and does not match format of user_id elsewhere.
Sorry if this is an FAQ/obvious, but I did search and did not find it. How do I de-obfuscate? I need to link to the user and to their pseudonym (sis_id).
I am using file downloads, if relevant.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @ghp5h
It's not exactly obfuscated, more like a unique key for the user_dim.id, specific to CD.
Any table with user_id column is meant to be joined back to user_dim and pseudonym as you stated.
To link a user with their SIS ID or any other relevant id's the following query should help explain.
SELECT
user_dim.id AS user_id
, user_dim.canvas_id AS canvas_user_id
, user_dim.global_canvas_id AS global_canvas_user_id
, pseudonym_dim.sis_user_id
, pseudonym_dim.unique_name AS login_id
--, user_dim.*
--, pseudonym_dim.*
FROM user_dim
JOIN pseudonym_dim ON (pseudonym_dim.user_id = user_dim.id AND pseudonym_dim.workflow_state = 'active' AND pseudonym_dim.position = 1)
- Pseudonym_dim, are the logins associated to a user. Since a user in canvas can have multiple login_id's each is stored separately from user_dim. Joining is a matter of expecting an active workflow (if required) and the position at 1, generally being the users default.
- user_dim.canvas_id or canvas_user_id, is the short Canvas id usually seen in user profiles, api calls etc.
- user_dim.global_canvas_id, is the user id, with the shard prefix, this is interchangeable when dealing with the API and some services.
Then connect the data you want.
SELECT
user_dim.id AS user_id
, user_dim.canvas_id AS canvas_user_id
, user_dim.global_canvas_id AS global_canvas_user_id
, pseudonym_dim.sis_user_id
, pseudonym_dim.unique_name AS login_id
--, user_dim.*
--, pseudonym_dim.*
, quiz_submission_fact.*
FROM quiz_submission_fact
JOIN user_dim ON quiz_submission_fact.user_id = user_dim.id
JOIN pseudonym_dim ON (pseudonym_dim.user_id = user_dim.id AND pseudonym_dim.workflow_state = 'active' AND pseudonym_dim.position = 1)
Please let me know if that's helpful or if you have any more questions.