Improve Data-level Security in Cognos Framework Manager Part II

Ahmed Etoukhy, April 25, 2018

Data-level Security

In a previous article, we explained the process and general approach of implementing data-level security in your IBM Cognos BI environment which is absolutely important. However, the great drawback of this approach is the ongoing maintenance!  In this article, I will examine how to streamline and improve data-level security in Cognos Framework Manager.

Table of Contents

The problem of maintenance with data-level security

As an example let’s consider a business with 600 users, all of whom have access to BI reports in Cognos, using AD as the authentication provider. They can be placed into 300 groups and organization units, which correspond to the structure and hierarchy of the business and each have their own filter set up through data-level security.

When this business reworks its structure or natural turnover occurs, it will result in the creation of new unit codes – let’s say 20 or 30 per year.

This means that for every new code, the BI admin has to open up Cognos FM projects, update the data-level security filters (clean / remove old ones and add new ones) and publish the package(s)!

It’s labor-intensive and increases the risk of manual errors like incorrect filters, group assignments or even spelling mistakes. By streamlining your data-level security, BI admins can mitigate a lot of the risk and labor associated with updating packages.

How to streamline and improve data-level security in Cognos FM

Now we come to the steps you can take to make data-level security changes more efficient.

Using Cognos Analytics macros, you can facilitate changes without adding new data security filters in the first place. The key is to have a logical link between the group/role name and the data used for reporting and filtering at creation. This can mean:

1. When you create groups in Cognos FM, label them <Org Code> – <Org Name>.  It could be further enhanced including the application, system, project name if access would be different (for example: <BI Project Name>_<Business Unit> _<Org Code>)
2. Making sure the organization dimension in your database contains all levels, with the code and name of every member in every level included.
3. Creating multiple organization groups for different user locations and aligning each group with the relevant data from the BI modeling. In most cases, you can find location dimensions in the source tables.
4. Ensuring all groups / roles used or imported in Cognos Administration are aligned with the user’s designation (position in the business  – department, division, etc). This way, users with a set designation can directly access information specific to that position. This mapping can be maintained in the database, or with Parameter Maps in Cognos FM.

These steps create a logical link between a group’s name and the data its members can access. The BI admin can then use this link to create a filter on the Query subject in Cognos FM using Cognos Macros. To set up this filter, run the following steps:

1. Create a filter on the Organization dimension. This can be the database query subject, or the logical query subject.

2. Insert the following code into the filter.

                 (#sq(CSVIdentityNameList())#)  contains [Database Layer].[DIM_ORG].[DIV_CODE]

3. If required, you can make this filter more complex – for example, if data is likely to appear in several columns without being tethered to a specific one. Below are examples of codes to differentiate between different parts of an organization’s hierarchy.

(#sq(CSVIdentityNameList())#)  contains [Database Layer].[DIM_ORG].[CEO_CODE]

  Or (#sq(CSVIdentityNameList())#)  contains [Database Layer].[DIM_ORG].[DIRECTOR_CODE]

  Or (#sq(CSVIdentityNameList())#)  contains [Database Layer].[DIM_ORG].[DEPART_CODE]

  Or (#sq(CSVIdentityNameList())#)  contains [Database Layer].[DIM_ORG].[DIVISION_CODE]

4. If there is a special group who should override all data security, a simple statement on top of the filter code can give them   access.

(#sq(CSVIdentityNameList())#)  contains ‘Super Users’

An example of a Data Security Filter combining all of the above cases:

It is complex to establish, but tethering group / role names to the information they can view makes it much easier to have new staff or roles fit into the existing framework without manually adding each and every one. It’s not just a time-saver either, as you’ll see below.

Benefits of streamlining your data security in Cognos FM

1. Simplified access

With these groups established and aligned with specific information, the only requirement for adding or removing users would be to grant the Cognos Connection consumer access. Whether groups are in Cognos or your authentication provider, it should update perfectly.

2. Low maintenance

The BI admin does not have to do anything to remove old groups or filters from the list. If a user remains in an old group, they should still have access to relevant information. If they were removed and added to a new group, they will automatically only see the information associated with it.

3. Streamlined control

By establishing an exception group with a special condition, it is easy to provide top-level access to all information in the BI modeling.

4.  Easy disabling

To disable data security in a specific BI model, the admin simply needs to disable the filter on the Query subject. It can be activated at a later date.

5.  Streamlined renaming

Sometimes, group names or the security logic will need to be changed. The BI admin can do this using new groups and columns with the logical links covered earlier. There is no need to remove and re-add a whole new set of security filters to satisfy data security requirements.

Conclusion

By standardizing and streamlining logical links in your data-level security in Cognos FM, you learn the framework by which you can manage security in almost any Cognos Analytics implementation.  It gives your organization an easier way to improve data-level security in Cognos that many people still ignore in favor of manual updates. Take it easy on yourself, and try improving your data security!I hope you enjoyed this article on how to streamline and improve data-level security in Cognos Framework Manager.  You can get more business and analytics content on our blog and newsletter.  If you haven’t already, be sure to subscribe to our e-newsletter the PMsquare Journal for more technical articles and updates delivered directly to your inbox.

Next Steps

If you have any questions or would like PMsquare to provide guidance and support for your analytics solution, contact us today.