Section Access with Hierarchical Visibility

[jobru-07]

Hi everyone,

I’m building a Qlik Sense application for a sports club.
The data model consists of:

• Three fact tables connected through a link table

• The link table contains:

  • key = autonumber(team_id & date)

  • team_id

  • calendar date

• team_id links to a team dimension

• The team dimension contains department_id and team_group_id

Section Access currently restricts data based on team_id, because all facts refer only to teams.

---

Roles and Access Requirements

There are two leadership levels in this club:

1. Department Leaders

• Responsible for all team groups in their department

• Should see total performance numbers for all other departments

• Must not drill down into team groups or individual teams of other departments

• Drilldown should only be possible inside their own department

2. Team Group Leaders

• Responsible for one specific team group

• Should see aggregated results for all groups in their department

• Can drill down to individual teams only within their own group

• Should not see team-level details for groups they don’t manage

---

Desired User Experience (Pivot Example)

• A department leader opens a pivot table and sees:

  • All departments and their total performance for a given month

  • Only the department they manage can be expanded into team groups → teams

• A team group leader opens the same pivot and sees:

  • All team groups of their department (aggregated)

  • Only their own group can be expanded into individual teams

---

Core Technical Challenge

Since Section Access currently works via team_id, we need to:

1. Generate aggregated fact rows at

   • department level

   • team group level

2. Introduce artificial keys such as:

   • AGG_DEPT_<department_id>

   • AGG_GROUP_<team_group_id>

3. Add these artificial IDs to Section Access

4. Maintain correct behavior across three fact tables linked via the link table

5. Ensure the access model supports:

   • global visibility of high-level totals

   • local drilldown restricted to one’s responsibility area

---

What I'm Asking the Qlik Community

Do you have best practices or recommended patterns for:

• Implementing hierarchical access where detail visibility depends on the user’s role?

• Combining normal fact rows with synthetic aggregated rows?

• Designing a Section Access model that shows:

  • totals everywhere

  • details only in the user’s department or team group?

Any insights, examples, or patterns from similar implementations would be very helpful.
Thanks in advance!

[Daniel_Castella]

Hi @jobru-07 

I think there are different ways to do this, but I have been able to reproduce the behavior you expect on the dashboard below. 

Since I didn't have any data sample, I built a simple one in excel. I'm attaching it too in order you can reproduce the code, perform modifications if needed and to understand how the data is built.

I'm attaching both since I think it would be very difficult to explain it only via text. Hence, I believe it would be easy for you to understand if you read the code directly.

I hope this helps you and if you have any doubt, let me know.

Kind Regards

Daniel

[marcus_sommer]

AFAIK there is no direct possibility for different access-rights in regard to their hierarchy-level respectively levels of granularity because it's against the core-aim of an access-control to allow access to a certain data-level or to deny them.

Strictly spoken you have not a single access rule else n ones but you want to combine them. I suggest not to discard the idea of keeping them in n application separate too easily because it keeps the matter logically and technically simple - and it may not have mandatory disadvantages in regard to performance & redundancy as wells as in the usability against more complex approaches.

Nevertheless there are possibilities to combine conflicting access rules with some extra efforts. You may take the following (and the there linked postings) as starting point:

Basics for complex authorization - Qlik Community - 1465872

to see if it might be adaptable for your scenario. Personally I would tend to your already mentioned approach by adding appropriate aggregated layer to the data-set. This could be added to the origin fact-tables with an approach like:

Fact Table with Mixed Granularity - Qlik Community - 1468238

 and does not mandatory require extra access-keys.

Beside the above I suggest to consider a check if you really need 3 fact-tables because quite often contain the facts the same kind of information (like sales + budget + forecast  - only the direction of the data is different) and they might be merged (mainly per concatenate but also with n join/mapping measurements) to a single fact-table which would simplify the data-model, avoiding linking-troubles and saving efforts.