Data Level Security in Cubes Using ParameterMaps

Data Level Security in Cubes Using Parameter Maps

In this article I will look at a real-world example of data level security filtering.

Cognos does provide some role based security filtering capability.
I think the problem with roles is that there is usually an attribute associated with the role... and that on the other hand is not supported out-of-the-box.

Consider the following example, to undertsand what I mean by "attribute associated with the role"

Suppose we have the usual GO-Sales kind of data. We have a Sales measure and one of our dimensions is Branch.
We have two roles: CEO and Branch_Manager
Our requirement is that CEO can see everything but a Branch_Manager can only see Sales by his or her own branch.

This is the point where role-based security goes week.

Somehow we need to store which Branch a Branch_Manager is allowed to see.
It's not sufficient to know that a user has Branch_Manager role... we need to know (and handle) a BranchID associated with that role.
This is what I call the "attribute associated with the role" - in our example the BranchID.

Usually this implies we need to store this somewhere, eg in the database or maybe in a directory server like LDAP.

To implement data level security I find the best option is to use Slicers all over the place where cubes are referenced - in queries used by crosstabs and charts on the reports, in queries used to populate prompts on the filter pages.
(Keep in mind, I firmly believe that lists should not be used with a dimensional datasource.)
The slicer will make sure that filtering is applied to the entire cube, regardless of the dimensions used for display.

To continue with the example we need to write a slicer that

• figure out who the current user is
• what role the current user has - is the user a Branch_Manager
• what attribute is associated with that role - value of the BranchID

So what tools are in or arsenal to tackle this?

Session Parameters

Session parameters provide a way to get an identifier of the current user.

e.g.

account.userInfo

Tamas Simon

Session parameters can be used in macro expressions. The syntax is to prefix them with a ``$'' (dollar sign)

e.g.
#sq($account.userInfo)#
will result in
'Tamas Simon'

The exact details depend on how Cognos is configured...
Bottom line is that in every Cognos installation there should be some sort of identifier of the current user and it's accessible to report expressions using session parameters.

You can check the session parameters in Framework Manager by selecting Project / Session Parameters... from the menu.

Parameter Maps

Parameter maps are similar to session parameters in that they can be accessed from macro expressions.
They are name-value pairs, or rather "key-value" pairs - as Cognos calls it.
They are defined in the Framework Model.
They can be based on a query, just select a column for Key and another for Value.

Syntax is similar to session parameters.
``$'' (dollar sign) infornt of the name
``{}'' (curly braces) surrounding the key
``''' (single quote) surrounding the key if it is a string literal

e.g.
#$pmUserAccessableBranches{'Tamas Simon'}#

Here are some important notes I'd like to make:

1., There is no way to include or exclude parameter maps from a package. In my understanding when you re-publish a pckage all parameter maps are published with it.

2., The query subject that the parameter map is based on has to be published in the package. Otherwise you won't get any error or warning messages but the parameter map will be empty, always returning its default value if you have defined any.
I remember reading that the query is executed everytime an expression references the parameter map - but I have not confirmed this.

string aggregate function

3., Keys are unique. This is tricky... to continue our example imagine that we want to be able to assign multiple branches to the same user.
In other words we want to control which branches a user is allowed to access (maybe only one, maybe more)
So in the parameter map we need to have a list of IDs associated with a Key, all within a single string (stored as the Value)

e.g.

database table

UserName	BranchID
Tamas Simon	B1
Tamas Simon	B2
Tamas Simon	B3

we would want the parameter map to look like

pmUserAccessableBranches

Key	Value
Tamas Simon	B1 B2 B3

This is tricky considering that we need to write a query to populate the parameter map... but it's doable.
Oracle for example does not provide a string aggregate function out-of-the-box but one can write his own.

select  UserName as Key,  stragg(BranchID) as Value from  UserAccessableBranches group by  UserName
You can find a string aggregate function e.g. here: http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php#user_defined_aggregate_function

With these three tools: session parameters, parameter maps based on DB query and string aggregation we can solve our data level filtering challenge.

We end up with slicers something like this:
filter(  [Branches Dimension].[Branch],  roleValue('_businessKey', [Branches Dimension].[Branch])  in (   #csv(split(' ', $pmUserAccessableBranches{$account.userInfo}))#  ) )
from the inside out:
get the current user via session parameter: $account.userInfo
use this as a Key to look up the user accessable branches from the parameter map: $pmUserAccessableBranches{$account.userInfo}
massage the macro until it returns a comma separated string: #csv(split(' ', $pmUserAccessableBranches{$account.userInfo}))#
use the whole thing to filter branches... resulting in a slicer member set.

We could just generate a set() expression using the prompt...

set( #join(',', substitute('^', '[Branches Dimension].[Branch]->:[PC].[@MEMBER].[', substitute('$', ']', split(' ', $pmUserAccessableBranches{$account.userInfo}))))# )

This would work if the IDs are in synch with the cube. Otherwise we might get an error from Cognos, saying basically that we are refering to a member that does not exist. I find it safer to use the filtering expression.

How to use the "Range Prompt" - some undocumented features

The TextField prompt has an interesting feature: it can be used to get two values instead of one by setting its ``Range'' property to YES.

This is handy when you want to filter by a range that has a lower and/or an upper value.

The extra that the prompt gives you is that you can have open intervals ie. cases where the user only specifies and upper or a lower value.

e.g. filter lines where some ratio is

• less than 80%
• beteen 60% and 80%
• greater than 80%

It saves you from having to do some sort of javascript voodoo with radio buttons etc.

The trick is that you want to set the prompt as optional (= not required) because otherwise it would be just two textfields, which is not any better than using two textfield from the first place.

Now... since the prompt is optional the user does not have to input values.
If the user leaves both the lower and the upper values unspecified... the prompt does not return anything.
And this is a problem...
...because you are trying to use the prompt in an expression using the in_range operator that looks something like this

filter(my_dimension, my_measure in_range ?pRange?)

and this blows up!
After the prompt is evalued it becomes the following expression:

filter(my_dimension, my_measure in_range )

...that's right, the prompt did not return absolutely anything

To fix this you need to use the long form of the prompt macro and set the datatype to ``range''.
This is undocumented as of 8.3 ... but it works

to fix the expression specify a default range that the prompt should return when the user left both lower and upper values unspecified. e.g. can be an open range starting with 0

filter(my_dimension, my_measure in_range #prompt('pRange', 'range', '{0:}')#)

[updated] Relative Package Names

It is possible to have a report be built on a package that is specified with a relative (to the report) location.
It is not possible to set relative package names through the UI in ReportStudio but you can copy the XML source into an editor, make the changes and paste it back to RS.
The reference to the package is right at the beginning of the XML in the <modelPath> tag.
I tested with relative path on 8.3 and it worked.

This is great for keeping multiple environments on the same Cognos box.
The report age can be the same. There is no need to relink the report with a different package. The package can be redeployed into different folders (new feature in 8.3) with the same name. The packages would be practically the same only they would use different datasources. Changing the datasource is easy... just run a sed script on the model's XML and publish.
If you need to maintain a lot of environments eg. QA, staging, production, support on the same Cognos then you will find this very useful!

Update
Damn, it doesn't work. Report Studio axccepts the relative path but before saving the report it substitutes it with an absolute path.
the good news is that in 8.3 you can relink a report to another package through the portal by setting the properties... there's no need to open up the report in RS.

Crosstabs with Column Headers

aka How to make Crosstabs that look like lists

                     | sales
------+--------------+---------
branch| sales person | <1234>
      +--------------+---------
      | sales person | <1234>

will look like

                     | sales 
-----------+---------+--------
1st Avenue | Aaron A | 1,000
           +---------+--------
           | Betty B | 2,000
-----------+---------+--------
2nd Street | Clare C | 1,500 
...

If it was a list it would have column headers and would look like this:

branch     | sales person | sales 
-----------+--------------+--------
1st Avenue | Aaron A      | 1,000
           +--------------+--------
           | Betty B      | 2,000
-----------+--------------+--------
2nd Street | Clare C      | 1,500 
...

To achieve this using a crosstab you need to add extra Text Items and hide some cells by setting their box type to ``none''

    HIDE             | "branch" | "sales person" | sales
---------------------+----------+----------------+------
branch| sales person |  HIDE    |   HIDE         | <1234>
      +--------------+----------+----------------+-------
      | sales person |  HIDE    |   HIDE         | <1234>

Hiding the top left corner will pull the extra Text Items to the left so that the "headers" will nicely align with their columns.
But adding these new text item still creates extra empty columns in the crosstab... these have to be hidden as well.

The resulting crosstab will look just like a list, displaying column headers.

You may need to play around with styling it to get everything in the right color....

Difference in dimensional expressions between RS8.2 and 8.3 - part two

I found another one

code in 8.2

set( emptySet([My Dimension].[My Hierarchy]) )

code in 8.3

set( item(emptySet([My Dimension].[My Hierarchy]),0) )

without the use of item() you get an error
Invalid coercion from 'memberSet' to 'member, measure'
even though the expression validates.

This is important when you want to create prompts that return MUNs but are optional.
As a recap from my older posts optional for a prompt means that it has a default value. The default is ... no members selected
...in other words: empty set

Difference in dimensional expressions between RS8.2 and 8.3

We are just upgrading to Cognos 8.3 (I know, I know...) and I've just found this difference in the use of the filter() function:


I think it actually makes more sense in 8.3... probably that's why they fixed it.

Code in 8.2

filter(
[My Dimension].[My Hierarchy].[My Level],
roleValue('_businessKey', currentMember([My Dimension].[My Hierarchy])) contains ?Param1?
)


Code in 8.3

filter(
[My Dimension].[My Hierarchy].[My Level],
roleValue('_businessKey', [My Dimension].[My Hierarchy].[My Level]) contains ?Param1?
)

In 8.2 one had to use the currentMember() function to refer to the member being filtered.
In 8.3 it is not needed. Instead one can just use the level.

Version 2 is Coming ;-)

My second baby is about to be born this week.
You won't see much blogging for the next couple of weeks.
When I come back I'll write about our experiences switching the DMR model to OLAP.
We're evaluating both Cognos PowerPlay and MS Analysis Services...