How to handle Cognos data security multiple user groups based on sales alignment

Check list of all posts

Overview

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:

1. 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.
2. 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.

Implementation

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.