Optimizing Row Level security macro (Cognos Framework Manager)

A while back I wrote about a method to apply row level security to relational database based Cognos Framework Manager model. The setup in my previous post is valid and relevant still. What you might want to do is aply some more macro functions in order to optimize the output a bit.

Optimization might come in handy if you have a massive authentication provider. Say your company is using MS Active Directory not only to authenticate the workstation users but also is profiling software and mirriads of other things whilst also providing authentication user groups for Cognos. Using just the CSVIdentityNameList() macro you can potentially end up with a huge querry where every row is being matched with an array of 100 items. It is just as inefficient as it sounds.

How to deal with this? Well, there is a bunch of things to be done here in order to smooth things out. Click into the full post to read the details!


You should start by reducing the number of items that are returned by CSVIdentityNameList(). To do so we will need to use a whole bunch of macro functions and the whole sharade looks something like this in the end:
#
csv(
grep('Country_',
substitute('''', '',
substitute('''', '',
substitute(' ', '',
array(
split(',',
CSVIdentityNameList()
)))))))
#

We start with the original CSVIdentifuNameList. This function returns one huge string. We use split to chop it into pieces and then afterwards put all of those pieces into an array. The substitute macros are there to clean up remaining trash chars. Now we have a clean array of your users credentials. Using grep macro function we can identify the AD user groups which are of interest to us. grep will remove any array member that does not contain 'Country_' in it, in the end leaving us with a very short list of user groups which are of interest to us. All that is left now is to use csv macro to convert our fresh clean array back into a comma separated string.

If your users only contain ONE relevant user class (lets say they should have access to one and only one country at a time), you might want to take this optimization game even further. Name the Active Directory user groups 'Special_Country_XX'- a name that is specific to only this project. This way you grep for 'Special_Country' and you will always have only one item as the result. In the original filter definition change 'in' clause to '=' which is a more efficient comparisson operation.

Might sound like peanuts, but when we are talking millions of rows multiplied by hundreds of users, every peanut counts! Count yours too and have your users happy ;)

M