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!
The problem of maintenance with data-level security
For example: 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 organisation 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 labour-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 labour associated with updating packages.
An example for a huge groups-filter for Data Security:
How to streamline and improve data-level security
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:
- 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>)
- Making sure the organisation dimension in your database contains all levels, with the code and name of every member in every level included.
- Creating multiple organisation 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.
- 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 Organisation 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 organisation’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
- 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.
By standardising 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 organisation an ease of use in its data security that many people still ignore in favour of manual updates. Take it easy on yourself, and try improving your data security!
For any questions about how to make it work for your organisation, make sure to get in touch with the team at Cornerstone. We specialise in IBM’s Cognos range, and can help you establish nearly any information management solution.
To learn more about Cognos Data Security and other IBM Cognos applications and features, please contact Ahmed Eltoukhy at email@example.com or on 0403 566 693.