Sales alignment is to align different user groups to sales. It is very often that sales are assigned to a sale rep, product specialist and territory manager. Each user group will need to see their own data. There are two different ways to design security:
- Design each groups as separated dimensions, such as sale rep dimension, product specialist dimension and territory manager dimension, and then put data security on each dimension.
- Design a sales alignment, where we assign sale rep, product specialist and territory manager to a sale.
Due to nature of business, sales alignment is used in lot of times. This document is intended to describe how to handle Cognos data security multiple user groups based on sales alignment.
Data mart scheme behind the scene
Three tables are involved in data security. There is Sales alignment key created / updated in sales when loading data into DWH. It can be interpreted into diagram below
For given a user, sales can be indentified based on sales alignment and users table. The main challenge is to get right column to join according to user group.
Create User query subject
Create user query subject but with filter below
#sq($account.personalInfo.userName)# = [Data Layer].[User].[User ID]
Based on user login user 1, you will get user group 1, only single record will be retrieved.
Create a security mapping (or Parameters map) based on User query subject
Key: User Group
Value: User ID
By giving User group, User ID will be retrieved.
Create active directory group based on user group
All users will be added to each active directory group.
Apply data security for sales alignment
Only one filter will be valid. The solution is to get right column to join according to user group.