When developing dashboards that display sensitive information, there are often a variety of security requirements that must be considered during design and development. These requirements depend on the number and types of users and the structure of the business. Very commonly, some users should have access to only a subset of data while others should be able to view all of the data. This is known as row-level security.
In this article, we will walk through such a scenario that we encountered when developing a dashboarding and reporting solution for a healthcare company’s High Performance Contact Center. This solution leveraged the QlikView reporting tool and the information below should be useful for QlikView developers who encounter similar security requirements.
The contact center’s hierarchical structure required the following row-level security:
- Any non-agent user (i.e. a user that doesn’t take calls; typically supervisors and executives) that has access to view the application should be able to see all data, with the ability to drill-down to see data on specific agents.
- Agent users should be able to see detailed data for themselves, no detailed data on other agents, and only aggregated data for their team.
There are a variety of methods to apply row-level security within QlikView, but we found that the Section Access security model was the simplest to implement and maintain given the requirements provided.
Every transactional record within our data model is specific to an agent denoted by the EmployeeID field. We used this field as the reduction field within our Section Access security model. This reduction field filters the data for each user by associating to the data model upon accessing the application and reducing the available data based on the reduction field values related to that user. This made it possible to provide Supervisor users access to all employee data through the security model by using the asterisk symbol (*) in the reduction field denoting all values listed, and provide Agent users access only to their own data by explicitly listing their EmployeeID in the reduction field.
NOTE: whenever Section Access is used, all reduction field names and values must be completely uppercased to produce consistent results. Forgetting this step can lead to incorrect reduction of the data.
The Section Access security model looked similar to the one below, where the top table represents the Section Access table, and the bottom table represents the Section Application table, both of which are necessary for providing access and filtering the data model.
Suppose also that our transactional table in our data model has the following structure:
This configuration sets EmployeeID as the reduction field, which will associate to our data model on the same field name. The data model ends up looking like the diagram below.
This security model allows users WMP\Employee3 and WMP\Administrator access to all values listed within the reduction field (Employee1 and Employee2 data), hence the ‘*’ denoting ‘All’. This indicates that users WMP\Employee3 and WMP\Administrator are both Supervisors in terms of access permissions. Employee1 and Employee2 have access to only their own data since they have a specific value listed in the EmployeeID column denoting their respective EmployeeIDs.
While we needed to limit agent users to their own detailed data, we also needed to allow them to compare their performance against their team as a whole. This required us to create aggregated tables in our data model at the agent- and team-level that were not associated to each other. The data model must be split at these levels to ensure that users do not unintentionally filter the data in a way that distorts the comparative metrics.
Currently, our EmployeeName and TeamName columns are in the same table, thus relating them. Even if they were split into two tables but associated on a unique identifier, e.g. TeamID, they would still be implicitly related. With this structure, we would not be able to accurately give Employee2 the total calls for his or her Team1 on the date 7/11/2016. Barring the fact that Employee1’s records would correctly be removed from the data model with the Section Access settings in place, the application would not display the correct call total of 50 for Agent2’s team on 7/11/2016 anyhow because Agent2 simply did not have data for that day, even though other members of his team did.
Avoiding this issue requires a two-step mitigation. First, the data model needs to be split so that the team metrics and the agent metrics are not associated with each other. We used the configuration below, where the team metrics table on the bottom is an aggregated version of the employee metrics table on the top, only without employee-specific information.
With the above configuration, our data model will not implicitly filter out Team1 call totals for 7/11/2016 for Employee2, despite Employee2 not having individual data for that day, because the two tables are not associated within the data model. Ultimately the data model, including the Section Access security configuration, will end up looking like this:
Notice that the EmployeeCallDetail table joins to the SectionApplication table on the reduction field of EmployeeID, in effect filtering the data available to a given Employee. The TeamCallDetail table is not part of the reduction, nor is it related to the EmployeeCallDetail on any fields. The reduction portion is handled through set expressions as described below.
This structure allows the application to show data for Employee 2’s employee-detail call total of 0 for 7/11/2016 in one visualization, and Employee 2’s team-detail call total of 50 for the same day in another visualization, even though Employee 2 did not take calls on 7/11/2016. QlikView set analysis expressions can be used to display the team-detail call total and would have syntax like the following, which filters the team metrics table by Team_TeamName where the values for Team_TeamName intersect with the selected and available values of TeamName:
Team_TeamName = p(TeamName)
This expression filters down team calls only to teams that the employee viewing the dashboard has taken calls for.
The second step is to filter the Team_Date field when the user selects values for the Date field. Without filtering Team_Date values, the aggregation for your team metrics will always include the full time period of your dataset, rather than the Date range selected with filters. Instead of requiring the user to manually filter the Date and Team_Date values separately, add an action that automatically triggers the Team_Date values to filter down to the same values selected in the Date field.
By following the above approach, a QlikView developer can create a flexible security model that can handle most common row-level security requirements.