SQL script Accounts table depth_of_account & subaccount_ids

[MikkiMilligan]

We are migrating from CD1 to CD2. In the accounts table the depth_of_account column is a calculation. Does anyone have SQL script to calculate the depth_of_account hierarchy and the subaccount_ids? 

Currently, my script has hard-coded values - this is just a temporary fix. If the end user updates the accounts from the front end, we would have to manually update the script, which is not best practices.

Thank you, 

Mikki

[Jeff_F]

Being able to filter by Campus, College, Department, and so on is certainly important. 

Is there an easy button for this? 

[stimme]

I have been rewriting my account hierarchy SQL VIEW based on the CD1 account_dim table to use CD2's accounts table. Creating a view like this might be a start @MikkiMilligan & @Jeff_F. 

CREATE OR REPLACE VIEW canvas.view_accounts AS
SELECT a1.id
, a1.name
, CASE
  WHEN a1.parent_account_id IS NULL THEN 0
  WHEN a2.parent_account_id IS NULL THEN 1
  WHEN a3.parent_account_id IS NULL THEN 2
  WHEN a4.parent_account_id IS NULL THEN 3
  WHEN a5.parent_account_id IS NULL THEN 4
  ELSE NULL
  END AS depth
, a1.workflow_state
, a2.name AS parent_account
, a2.id AS parent_account_id
, a3.name AS grandparent_account
, a3.id AS grandparent_account_id
/* Extending generational metaphor of account_dim two steps further */
, a4.name AS greatgrandparent_account 
, a4.id AS greatgrandparent_account_id 
, a5.name AS gg_grandparent_account 
, a5.id AS gg_grandparent_account_id 
/* Building the root account & subaccount columns will require CASE statements
   similar to the one used to determine depth. */
, a1.sis_source_id
FROM canvas.accounts a1
LEFT JOIN canvas.accounts a2 ON a2.id = a1.parent_account_id
LEFT JOIN canvas.accounts a3 ON a3.id = a2.parent_account_id
LEFT JOIN canvas.accounts a4 ON a4.id = a3.parent_account_id
LEFT JOIN canvas.accounts a5 ON a5.id = a4.parent_account_id
/* Add more left joins if necessary */

[MikkiMilligan]

Hello, 

I tried your script and it does not provide the correct results. The Canvas 1 to Canvas 2 documentation states Depth in Account is a calculated field:

DepthSetting=20 . The "depth" field in CD2 table: "user_account_settings" defines how "close" a user is related to an account. 1 if you have a pseudonym or enrollment in a course in that account. 2 if it's the parent account of a course you're enrolled in, etc.

I could not find user_account_settings table in the Entity Relationship Diagram for Canvas database tables (data-access-platform-api.s3.eu-central-1.ama...

I found user accounts association - which maybe the table to use to get the dept in account.

[stimme]

Hi @MikkiMilligan , the comment in the CD1 to CD2 spreadsheet is confusing. I'm not aware of anything in CD1 like the user_account_associations table or its depth column. The CD1 account_dim.depth column indicates the steps or generations between the root account (i.e. account 1) and the account in the row; it's an account-account relationship. The CD2 user_account_associations.depth column indicates steps between user pseudonym or enrollment and a given account; it tracks two different types of user-account relationship. Its values need not match CD1 account_dim.depth, because it doesn't indicate the same relationship.

@Edina_Tipter I see your reply indicates a small change to the schema mapping spreadsheet. Please take another look at the comment on account_dim.depth. I don't think CD1 account_dim.depth has anything to do necessarily with CD2 user_account_associations.depth, so that reference in cell F6 should be removed. The empty cells C6,D6 indicating that CD2 accounts has no equivalent for CD1 account_dim is clear and sufficient.

[MikkiMilligan]

I believe user_account_associations is a new table. I do not think this table or user_account_settings should be joined and used to calculate the depth in account as mentioned in the Public CD1 to CD2 schema mapping.

However, the subaccounts with the hierarchy structure are needed in our OBI reporting environment and Oracle data warehouse.

Public CD1 to CD2 schema mapping - Original

account_dim

depth

calculated field

DepthSetting=20 . The "depth" field in CD2 table: "user_account_settings" defines how "close" a user is related to an account. 1 if you have a pseudonym or enrollment in a course in that account. 2 if it's the parent account of a course you're enrolled in, etc.

Thank you for your feedback. Still looking for a solution and waiting to hear back from support.

Mikki

[stimme]

With two dozen people saying they had the same question, I decided to expand the CREATE VIEW statement I posted above. In this version, I recreated more columns from CD1's account_dim table (building the hierarchy down to subaccount_6).

The goal is getting the same results from this view and CD1 account_dim, so I wrote a SELECT statement to get comparable results from CD1. If you have both tables up-to-date, these results should match. It worked for me.

[ColinMurtaugh]

I've only just started to play around with this, but I think that postgresql recursive queries can be helpful here. For example, here's some SQL that will create a view of the accounts table called accounts_hierarchy that adds two new columns to the end: id_path and depth. The id_path column will contain a dot-separated path from the root to the specific account, and the depth will be the number of levels below the root that the account exists. 

create extension ltree;
create view accounts_hierarchy as
    with recursive x as (
        select accounts.*, cast(id as varchar)::ltree as id_path, 1 as depth
        from accounts
        where parent_account_id is null
        union all
        select y.*, ltree_addtext(x.id_path, cast(y.id as varchar)) as id_path, nlevel(x.id_path)+1 as depth
        from x, accounts as y
        where x.id = y.parent_account_id
    )
select * from x;

Now you can query this new accounts_hierarchy view to do things like find all of the sub-accounts that have a depth=2 (all of the accounts that are directly below the root account): 

select * from accounts_hierarchy 
where depth = 2 
and workflow_state='active';

Or you can select all of the sub-accounts under a particular account hierarchy (in my instance, all of the accounts that are underneath account 39 which is directly below the root account):

select * from accounts_hierarchy
where id_path <@ '1.39'
and workflow_state='active';

 I'm totally new to postgresql recursive queries and ltree functions; please chime in if you have other/better ideas! 

--Colin

[MikkiMilligan]

Hello, 

I tried your script and it does not provide the correct results. The Canvas 1 to Canvas 2 documentation states Depth in Account is a calculated field:

DepthSetting=20 . The "depth" field in CD2 table: "user_account_settings" defines how "close" a user is related to an account. 1 if you have a pseudonym or enrollment in a course in that account. 2 if it's the parent account of a course you're enrolled in, etc.

I could not find user_account_settings table in the Entity Relationship Diagram for Canvas database tables (data-access-platform-api.s3.eu-central-1.ama...

I found user accounts association - which maybe the table to use to get the dept in account.

[pgo586]

If you happen to be ingesting your Canvas Data into Splunk like we are (rare at this point, but if you are, be sure to reach out to us as we could collaborate!) we have a couple of SPL (Splunk) queries that obtain ancestry information for each account/subaccount (including depth and all their ancestors in the hierarchy). The two queries could be made into one single query although it would have more left joins that I feel comfortable including in a single query (6, one per sub-account hierarchy level in our organization). If interested, let me know and I'll share it. BTW, these queries/query would similar to what @stimme has posted above -although with some embellishments-, but for Splunk queries. 

[MikkiMilligan]

we are using Splunk. Thank you everyone for your reply. I will try to work with the sql queries provided.

[pgo586]

@MikkiMilligan , did you say you're using Splunk? I'd be glad to send you the two (SPL) queries I wrote for this purpose, if you'd like. Or did you really prefer to run an SQL query though? Also, if you are running SQL queries from within Splunk, I'd like to be in touch as this is something we're hoping to eventually do. 

[MikkiMilligan]

Sorry - we are not using Splunk.

Hello, 

The Canvas 1 to Canvas 2 documentation states Depth in Account is a calculated field:

DepthSetting=20 . The "depth" field in CD2 table: "user_account_settings" defines how "close" a user is related to an account. 1 if you have a pseudonym or enrollment in a course in that account. 2 if it's the parent account of a course you're enrolled in, etc.

I could not find user_account_settings table in the Entity Relationship Diagram for Canvas database tables (data-access-platform-api.s3.eu-central-1.ama...

I found user accounts association - which maybe the table to use to get the dept in account.

[Edina_Tipter]

@MikkiMilligan Thanks for pointing this out. The CD1 to CD2 schema mapping was misleading so it was adjusted and changed "user_account_setting" to "user_account_associations".

[MikkiMilligan]

Hello, 

I've attached an example of the Depth in Account levels we are trying to calculate. 

Root Account = University

Subaccount 1 = Campus

Subaccount 2 = College

Subaccount 3 = Department.

It has been a challenge to recreate the subaccounts based on the new account table.

The entity relationship Entity Relationship Diagram for Canvas database tables (data-access-platform-api.s3.eu-central-1.ama... and the Public CD1 to CD2 schema mapping excel spreadsheet indicate to calculate the depth in account column using the  CD2 table: "user_account_settings". This table does not exist. I did find a table user_account_associations. 

Unfortunately, the user account association table does not have the account id for all the accounts in the accounts table. I've attached the Oracle SQL that joins to user_account_association table. The results should look like the example I attached; however, only depth 0 and 1 . Perhaps the user_account_association is the not correct table. 

[pgo586]

@MikkiMilligan , It seems that it might be better to have a calculated depth value (and optionally, the ancestry list if needed) of all accounts based on the accounts table, and then simply join that with enrollments information for whichever users you are interested in finding out their own relationships to given accounts/sub-accounts (especially as this last information could vary over time depending on specific enrollments, but the accounts depth should hopefully not vary). So either of the options given above by @ColinMurtaugh or @stimme should help you out in calculating the depth value (note that I also posted to this list regarding this same topic of account depth a while ago). 

[MikkiMilligan]

Thank you for your reply. 

How the Account_Dim table in CD1 is set up the depth in account are related to

Root Account = University;  Depth = 0

Subaccount 1 = Campus; Depth = 1

Subaccount 2 = College; Depth =2

Subaccount 3 = Department; Depth = 3

I will look into the enrollments table. The possible solutions by @ColinMurtaugh or @stimme did not calculate the depth value according to our current set up. I tried them both. 

[ColinMurtaugh]

@MikkiMilligan -- my example starts with the root account as 1 instead of 0, but otherwise should do what you want. It should be pretty easy to modify it to start with the root account being 0 if that is a hard requirement for you. 

If there's something else about my example that doesn't seem right, let me know -- it might be easy to handle!

--Colin

[jeff_longland]

Just curious, where did folks end up with regards to views for account hierarchy?