Data and Analytics

Word lid

Create Org Unit with Higherarchy Info in Analytics Builder

Katie.B.1305
Katie.B.1305 Berichten: 14 🌱
in Social Groups

Hello,

I am hoping to get some help on how to create something in Analytics Builder (Amazon Quicksights) where I can have all course offering org units as rows with their semester, department/program, and school parts of the hierarchy displayed as columns.

In a simple form, this is what essentially I would want for the fields across the top:

  • CourseOfferingOrgUnitID, SemesterOrgUnitID, Department/ProgramOrgUnitID, SchoolOrgUnitID

What I have right now is more like:

  • OrgUnitID, ParentOrgUnitID, Parent Type
  • OrgUnitID, ParentOrgUnitID, Parent Type
  • OrgUnitID, ParentOrgUnitID, Parent Type

Does anyone know how in Analytics Builder to write a custom formula to pivot this information into rows or otherwise how to achieve what I am looking to do?

Thanks,

Katie Bush

· Share on Twitter

Reacties

  • Carolyn.V.387
    Carolyn.V.387 Berichten: 1 Analytics Builder Transition

    Hi Katie, I use temp tables to accomplish this (which may not be the prettiest/most elegant, but it works for our needs). I'm happy to share it if a framework is helpful to get you started! Feel free to shoot me a message with your email and I can send you the details.

    · Share on Twitter
  • Katie.B.1305
    Katie.B.1305 Berichten: 14 🌱

    @Carolyn.V.387 that would be helpful. I’ll message you.

    · Share on Twitter
  • Jennifer.M.128
    Jennifer.M.128 Berichten: 5 🌱
    Screenshot 2025-04-25 at 8.53.18 AM.png

    (Attached is a screenshot so that you can view it formatted; below is text to copy/paste. This actually takes the names instead of the orgunitids, though the same logic could be used.)

    WITH Semester As (SELECT OU.name as Course, OUS.name as SemesterFROM brightspace_data_sets_5d0ac761_c893_4b1a_b2fe_d6ec1e1e454a.organizationalunits_9_16_4 OUINNER JOIN brightspace_data_sets_5d0ac761_c893_4b1a_b2fe_d6ec1e1e454a.organizationalunitancestors_9_16_4 OUA on OU.orgunitid = OUA.orgunitidINNER JOIN brightspace_data_sets_5d0ac761_c893_4b1a_b2fe_d6ec1e1e454a.organizationalunits_9_16_4 OUS on OUS.orgunitid = OUA.ancestororgunitidWHERE OU.type = 'Course Offering' AND OUS.type = 'Semester'),
    Department As (    SELECT OU.name as Course, OUD.name as Department    FROM brightspace_data_sets_5d0ac761_c893_4b1a_b2fe_d6ec1e1e454a.organizationalunits_9_16_4 OU    INNER JOIN brightspace_data_sets_5d0ac761_c893_4b1a_b2fe_d6ec1e1e454a.organizationalunitancestors_9_16_4 OUA on OU.orgunitid = OUA.orgunitid    INNER JOIN brightspace_data_sets_5d0ac761_c893_4b1a_b2fe_d6ec1e1e454a.organizationalunits_9_16_4 OUD on OUD.orgunitid = OUA.ancestororgunitid    WHERE OU.type = 'Course Offering' AND OUD.type = 'Department')
    SELECT OU.name as Course, OUM.name as Modality, CONCAT(SUBSTRING(OU.code,1,6),SUBSTRING(OU.code,11,16)) as Term_CRN, S.Semester, D.DepartmentFROM brightspace_data_sets_5d0ac761_c893_4b1a_b2fe_d6ec1e1e454a.organizationalunits_9_16_4 OUINNER JOIN brightspace_data_sets_5d0ac761_c893_4b1a_b2fe_d6ec1e1e454a.organizationalunitancestors_9_16_4 OUA on OU.orgunitid = OUA.orgunitidINNER JOIN brightspace_data_sets_5d0ac761_c893_4b1a_b2fe_d6ec1e1e454a.organizationalunits_9_16_4 OUM on OUM.orgunitid = OUA.ancestororgunitidINNER JOIN Semester S on OU.name = S.CourseINNER JOIN Department D on OU.name = D.CourseWHERE OU.type = 'Course Offering' AND OUM.type = 'Modality'

    · Share on Twitter
  • Katie.B.1305
    Katie.B.1305 Berichten: 14 🌱

    @Jennifer.M.128 thank you so much for this. I'll give it a whirl along with what Carolyn sends.

    · Share on Twitter

Categorieën