TM1 Cell Security
Had a few ‘let’s improve performance’ cases recently that were all related to TM1 Cell Security, so here’s a couple of notes on it. You’ve heard all of this before, just putting it together.
Self-referencing cell security rule
Planning Analytics Workspace has a feature (or a bug, depending on your point of view) that it doesn’t ‘gray-out’ the Cells based on rules, only based on security definitions.
So to make things look user-friendly, you write cell security rules like:
[{'Spread Month From','Spread Month From'}] =S:
IF (DB(source_cube, 'Spread Method') @<>'', 'WRITE','READ');This looks very innocent (and super nice in PaW cube views), but these rules are very nasty as they both kill performance in 2 ways:
Cell Security cube depends on source cube causing a circular calculation and, more importantly,
This calculation ‘locks’ the cell security cube and causes a lock on source cube
you essentially turn your cube in ‘single-writer at a time and enable full recalc after each write’ mode. Don’t.
Do rule in source cube like this
[{'Spread Month From','Spread Month From'}] = S:
IF (DB(source_cube, 'Spread Method') @<>'', STET,'');and wait for PaW to get smart enough to draw it nicely (Tm1web did it since the dawn of time, so it couldn’t be far away).
Only necessary dimensions in CellSecurity cubes
You can (and should wherever possible) create cell security cubes with fewer dimensions than source cubes, there’s a CellSecurityCubeCreate TI function for it.
All cell security rules are strings, not cached, so calculating as little as possible makes everything heaps faster.
Using STET rules for security
You can change the default CellSecurity value from None to something else by modifying CELLSECURITYDEFAULTVALUE in }CubeProperties cube.
I find that this allows me to use STETs in cell security more widely and therefore ‘terminate’ rule execution, or sometimes avoid writing rules at all. Be wary of showing too much to users though once you move from the default of NONE, only use this in combination with Element Security.
Enabling feeders for CellSecurity cubes
Even though you can enable feeders in CellSecurity cubes and improve rules performance by feeding only required intersections, I never do and don’t recommend it.
This blog was originally posted on Applied Dimensionality https://ykud.com/blog/cognos/tm1-cognos/tm1-cell-security/
