Business Scenario: At the front line of the business operation are the operators who take the phone calls from the users. The calls are entered into a computer system and recorded as incidents.
The structure of the organization has the operators as the individual contributors.
Managing the operators are the managers, which is the next level up.
Then, managing the managers are the directors.
Finally, managing the directors is the vice-president.
Business Requirement: Set up a Tableau workbook with incident data to have access restricted by employee hierarchy. Managers can only see data of their operators; Directors, only their managers and operators; and Vice-president, all data.
Procedure:
1) Create a user hierarchy table that has the operator name, sys_id, username, and a username_concat column that contains all the usernames of the operator’s manager and director who roll up to the vice-president’s organization.
The user hierarchy table should be created from an employee table that has the employee name and the employee’s manager, using self-join.
Note that it’s better to concatenate the usernames instead of the full names of the managers because multiple employees can have the same full name, but each employee has a unique username. For example, a company can have three employees with the same name John Smith, but each of these employees should have a unique username. Using unique username will avoid confusion and data duplication.
In this sample user_hierarchy table, John Smith has Pearl Buck as the manager, Ernest Hemmingway as the director, and Jane Austen as the vice-president.
2) In Tableau workbook, the data source should link the incident table to the user_hierarchy table based on the operator’s sys_id. Since each incident is assigned to and worked on by the operator, the query below will result in each incident having a username_concat column.
SELECT a.*,
b.username_concat
FROM schema.incident_table a
LEFT JOIN schema.user_hierarchy b ON a.operator_sysid = b.sys_id
3) If you have people who don’t work directly with the operators but are allowed to see all data (bypass group), create a formula in Tableau workbook to add their usernames to the username_concat column. Note that this step is optional if you don’t have a ‘bypass group’.
@username_concat + bypass
[username_concat] + ' ' +
'tam.nguyen alice.walker'
4) Create another formula to verify that the username of the person who accesses the Tableau workbook is included in the formula @username_concat + bypass.
@Is username_concat + bypass?
CONTAINS([username_concat + bypass],USERNAME())
5) Finally, apply this user filter to the worksheets and select ‘True’ to restrict access and to allow the managers to see only data of their operators.
