Cell security

by ·Comments Off on Cell security

Product:

TM1

Issue:

Set security with code?

Solution:

Check out this from https://exploringtm1.com/cell-security-in-tm1-the-complete-guide/

TM1’s security can be as simple or as complex as you need. We tend to start with the broadest possible definition of security and then refine it down to the specific, to the cell if required. TM1 cell security places an overhead for your administrator to manage as it can get complex, not only within a cube but also with the interaction of it with element or dimension security. This guide will take you through how to create Cell Security the right way, that minimises the overhead on your server and administrator.

Standard “Create Cell Security Cube” Method

When you right-click on a cube and select Security, you are prompted to Create a Cell Security Cube. If you do this, it will create the cube that replicates the primary cube, but with the addition of the }Groups dimension. Let’s say you have a GL cube with Time, Version, Entity, Cost Centre, Account, and Measure. Using the automated method will give you those plus }Groups.

This is a dead-simple way to create a Cell Security cube and from it you can assign cell-level security. However, if you have, say six dimensions in the underlying cube, then you’ll have seven dimensions in the resulting cube. Great flexibility, because you can assign security to any corresponding intersection to the primary cube. Huge overhead through because you have to maintain all those intersections and if you want to go down the path of having rules manage the cell security, then it could have a big hit on performance.

Customised Cell Security Cube

So what do we do? We want a cell security cube that only has the dimensions you need to assign security for the primary cube. If, from our GL cube above, we only need Time, Version, and Account for administering security, then we create a security cube with only those plus the }Groups dimension. Administering a 4 dimension cube is very much easier than a 7 dimension cube!

Create a Custom Cell Security Cube

The only way two create a customised cell security cube is via a special TI. This TI contains just two lines, namely:

CubeName = 'General Ledger';
CellSecurityCubeCreate ( CubeName, '1:1:0:0:1:0');

This has a set of simple binary switches that enable or disable a dimension from the primary cube. So obviously, our primary cube, the General Ledger cube, here has six dimensions, they are referred to here in the exact order they are in the primary cube and are separated by a colon (one of these “:”). Finally, the zero and one switches are contained inside a single inverted comma.

Running this TI will then create a cell security cube with only the required dimensions. So, with our dimensions above, we would end up with Time, Version, Account, and }Groups in the new cube. This corresponds with the 1’s in the command in the TI.

Rules

Once the Cell Security cube has been created, then we can assign rules to it. In the rule below we have six blocks. YOU can read the annotation in the rules. Note the last one is a catch-all with the scope of []. This sets it to be for all remaining intersections not caught by the rules above.

# Set Actuals to have Write access to future Weeks only
['Actual'] = S: IF ( ELLEV ( 'Time', !Time) = 0
	,IF ( ATTRN ('Time', !Time, 'FY Week No') <= DB('System Control','Current Week','Value'), STET,'WRITE')
	,CONTINUE
	);

# Set Actuals to be Read for historic Periods (months) up until the most recent completed Month End
['Actual'] = S: IF ( ATTRS ('Time', !Time, 'Monthend Completed') @= 'Yes', STET,'WRITE');

# Set Budget to be all Read only
['Budget'] = S: 'READ';

# Set WEEKS for Active Forecast Versions to Write from Forecast Start Week onwards
[] = S: IF ( ELISANC ( 'Version', 'Active Forecast Versions', !Version ) = 1 & ELLEV ( 'Time', !Time) = 0 
	,IF ( ATTRN ('Time', !Time, 'FY Week No') < DB('System Control','Forecast Start Year-Week','Value'), STET,'WRITE')
	,CONTINUE 
	);

# Set PERIODS for Active Forecast Versions to Write from Forecast Start Week onwards
[] = S:  IF ( ELISANC ( 'Version', 'Active Forecast Versions', !Version ) = 1
	,IF ( ATTRN ('Time', !Time, 'FY Period No') < ATTRN ( 'Time' ,ATTRS ( 'Time', DB('System Control','Forecast Start Year-Week','String'), 'Current Period'), 'FY Period No'), STET,'WRITE')
	,CONTINUE 
	);

# Set ALL else to READ
[] = S: 'READ';

Deleting a Control Cube

Like other Control Objects, these cubes are special and cannot be elated by the normal right-click method. They must be deleted via a TI process as well. The TI just needs to contain the following

CubeName = '}CellSecurity_General Ledger';
CubeDestroy ( CubeName );

Obviously, if you run this, it will delete any data in the cube and any rules you have written against it. So copy the rules out first if you want to re-use them!