Working With The Hierarchy


When reporting any kind of activity, the first and most obvious way to group data for summary is by the school, department or faculty that is responsible for the creation and maintenance of a list in the University.

The Hierarchy in Talis Aspire is ‘free form’ in that you can have as many hierarchy levels as you like, and the type of ‘node’ in each level is for you to choose from a set list of types. Each university can use its own terminology for how it describes a cluster of teaching blocks and the organisational group responsible for maintaining them.

In this example we we will use the following hierarchy nomenclature:

Questions you might be asking

Often people want to circulate reports to departments to show them how well they are getting on in rolling out reading lists, and to give them lists of modules for which some sort of action needs to be taken. These are often reported monthly, termly or yearly.

The questions people want to help answer in these reports are often as follows:

The Hierarchy data

In Talis Aspire, because the hierarchy is designed to be flexible to many different ways of describing an institution, there are some key things to consider when querying the hierarchy.

In the f_rl_hierarchy view you will see a flat representation of what is actually a one-to-many relationship between a node and its parents. For example where a node of type module, has more than one parent, then each parent is included in the parents columns separated by a semi-colon and space (; ).

You can check to see if this is likely to affect your data using a query like the following:

select hierarchy_url 
from f_rl_lists 
where hierarchy_url like '%; %'
limit 1; 

Because you often want to group and aggregate ‘up’ or ‘down’ the hierarchy, we have extracted this information into to separate tables to facilitate easier access to this data.

The two views are as follows:

A basic query using parent relationships

In this example we want to count how many nodes have parents but do not have lists.

select count(f_rl_hierarchy.url), parents.parent_url
    f_rl_hierarchy_parents as parents
    total_list_count = 0
    f_rl_hierarchy.url = parents.url
group by 

A more complex example with parent relationships

This query shows us list length across each department, and also calculates averages of list length per department. We can also see the same for section counts. This can be used to give an indication of how people are using lists and whether they are using sections to break up long lists into manageable chunks.

    min(item_count) OVER (PARTITION BY parent_url) as "Min Length", 
    max(item_count) OVER (PARTITION BY parent_url) as "Max Length", 
    avg(item_count) OVER (PARTITION BY parent_url) as "Average Length",
    min(section_count) OVER (PARTITION BY parent_url) as "Min Number of Sections",
    max(section_count) OVER (PARTITION BY parent_url) as "Max Number of Sections",
    avg(section_count) OVER (PARTITION BY parent_url) as "Average Number of Sections"
    f_rl_lists.hierarchy_url = f_rl_hierarchy_parents.url
group by parent_url, item_count, section_count

A Basic query using descendant relationships

Maybe we want to run a query that uses a specific department as the starting point, or where we want to know an aggregate at the level of the department. In this example, we are looking to find out how many nodes of type ‘Module’ we have below each department. Perhaps we have a hierarchy where modules can be combined in many different ways to form courses within those departments, but we don’t want to count at the Course level

    descendants.url as "department", 
    count(descendants.descendant_url) as "Children"
    f_rl_hierarchy_descendants as descendants,
    f_rl_hierarchy.type = 'Module'
    f_rl_hierarchy.url = descendants.descendant_url
group by 

Assumptions and limitations

The main thing to remember is that the hierarchy descendants views are pre-calculated to 5 levels of hierarchy. This means that where a node is a child of two different parents it will appear twice in any count, once for each parent. Often this is what you want… but not always!

The maximum number of parents that the f_rl_hierarchy_parents view can show for any given node is 100. In Talis Aspire there is no enforced limit. The number of children a node can have is unlimited. If this reasonable default causes you any issues in querying the table, do let us know through our support channels.