Cascading Element Level Security in TM1

We are doing a large project at a multi business unit company. The client is very security conscious and wants to ensure t members of a business unit can only see the data for their business unit. Easy, I   thought, we’ll just assign rights to the the Business Unit parent elements of the Cost Centre dimension to respective security groups, and those rights will cascade down to all the cost centres beneath them. No. Not so easy. TM1’s rights do not cascade, so even though we had blocked access to the parent elements, the other groups could still see the child elements. Grrr! The solution was element level security down to the N level.

How to Add Security to an Entire Branch of a Dimension?

The solution the problem of how to restrict access to a branch of a dimension to only a specific group of users was remarkably simple – even with a dimension of 300,000 elements. We just used a TI to load up the relevant }ElementSecurity control cube with, in this case “WRITE”, for all elements we wanted a group to have access to.

Why not use a Rule to Manage Security?

Why use a TI for this, rather than a Rule, I hear you ask? Well that’ pretty simple too. Our structure is updated daily and remains large static. The TI literally takes a few seconds and a Rule would have added an overhead of calculation that just wasn’t needed.

Background – CAM Security

The model we are working on uses CAM security, so security mode 5 in tm1s.cfg. Users are grouped in Active Directory and those AD groups assigned to Cognos groups in Cognos security. The Cognos groups (CAM groups) are then added into TM1 through modelname > Security > Clients/Groups and then Groups > Add New Group. That’s pretty standard.

Attribute on the }Groups Control Dimension

Then to implement element level security itself, it gets clever. There is a Control Object dimension called }Groups.This dimension contains all the groups we have introduced from Cognos security. We then added an attribute to this dimension to correspond with the Business Units we want to limit security around and populated that attribute with the business unit ID, so that the attribute contains exactly the same ID as the Business Units in the Cost Centre dimension.

Turbo Integrator to Populate Element Level Security

We then wrote a TI to loop through each level of the Cost Centre dimension and enter “WRITE” to the “}ElementSecurity_CostCentre” control cube.

The full TI is pasted below for your reference.

The data source is the }Groups dimension we have added the attribute to above.

We start on the Prolog by zapping the entire target cube before continuing.

Then on the data tab we initially define a variable, sBU, to hold the contents of the attribute from the }Groups dimension from above. This corresponds with the record we are processing from that same }Groups dimension. We then check if it exists or not in the target dimension, and if not, or the attribute is blank, we skip that entry. Thus elements like “ADMIN” are skipped.

The next section is to add “WRITE”, or “READ” for that matter, into the the target cube for the sBU (as we know it exists). This defines the element level security.

Then we start the looping mechanism initially for the Division level. This first sets up a numeric variable (nDivs) of the number of Divisions that are child to the current Business Unit. Then there is a count setup that is assessed against nDivs and the process loops through and enters “WRITE” to each element until the loop completes for the Divisions in that Business Unit.

This process is then replicated for the next level down in the structure, the Cost Centre level and could be repeated as many times as are required to get through all levels of dimension.

The code for the Turbo Integrator process is pasted below for your reference (I have deliberately changed it away from the structure of our client, using different levels and names, so if you see an error in it now, please let me know!).

Please note that running this TI assumes you have already set up the }ElementSecurity control cube for the relevant dimension you want to have this type of security applied.

The TI Process

Data Source

is a TM1 Dimension Subset, using the }Groups dimension

Prolog Tab

# Define Variables
DimName1 = '}Groups';
DimName2 = 'Cost Centre';

CubeName = '}ElementSecurity_' | DimName2 ;

# Zap the entire target cube
CubeClearData ( CubeName );

Data Tab

# Define Variables
sEl = vEl;
sBU = ATTRS(DimName1, sEl, 'BU');

# Check if BU attribute is Blank or if BU is not in the target dim, then if it is skip the group
IF(sBU @= '' % DIMIX(DimName2, sBU) = 0) ;

## Business Unit Section
# Add security to the Business Unit
CellPutS('WRITE', CubeName, sBU, sEL);

## Division Section
#Determine the number of Divisions in the BU
nCount1 = 1;
nDivs = ELCOMPN(DimName2, sBU);

#Loop through each Division
WHILE(nCount1 <= nDivs);
# Get Division Name and add security to the Division
sDiv = ELCOMP(DimName2, sBU, nCount1);
CellPutS('WRITE', CubeName,sDiv, sEL);

## Cost Centre Section
# Determine the number of Cost Centres in the Division
nCC = ELCOMPN(DimName2, sDiv);

#Loop through the Cost Centres
nCount2 = 1;
WHILE(nCount2 <= nCC);

#Get Cost Centre ID and add security
sCC = ELCOMP(DimName2, sDiv, nCount2);
CellPutS('WRITE', CubeName, sCC, sEL);
nCount2 = nCount2 +1;


nCount1 = nCount1 + 1;


If you like this post, please spread the love…

About John

Managing Director of Infocube. We are a management accounting consultancy that specialises in Business Analytics.

One comment on “Cascading Element Level Security in TM1

This site uses Akismet to reduce spam. Learn how your comment data is processed.