[ARCHIVED] Deobfuscate user_dim.id?

[ghp5h]

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.

[robotcars · Accepted solution]

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.