Access to cube cells in TM1
- Mar. 6, 2019
Introduction
TM1 holds data in cubes. Cubes are structured using and dimensions (that contain hierarchies). The idea is that users of a TM1 model can have access to the data in the cube cells, or can be denied access to them. Certain cells can be changed (like the monthly rolling forecast update cells) while other cells are read-only (like actuals loaded from source systems). Still other cells are not visible (like salary data).
In this article I ask myself the following question: given a cell and given a user, what can explain why the user has access to the cell (read/write or more), or no access. In case of an access that is not what it should be, either too much access or too little, where should one look to find the cause ?
So, my list goes as follows:
- Rules:
- the cell is calculated by rules. The cell is not editable unless the STET function was used.
- Security:
- no access at cube level (look for appropriate rights in cube }CubeSecurity)
- no access at dimension level (look for appropriate rights in cube }DimensionSecurity)
- no access at element level (look for appropriate rights in cube }ElementSecurity_DIMENSION)
- no access at cell level (look for appropriate rights in cube }CellSecurity_CUBE). Note: cell security does not need to involve all dimensions of the original data cube. In addition, have a look at the cube }CubeSecurityProperties to know whether the most restrive rights apply or not.
- ReadOnly user:
- a user can be marked as ReadOnly (look for the property ReadOnlyUser in the cube }ClientProperties)
- Holds:
- a hold was placed on certain cells (look for H (leaf level) or C (consolidated level) in cube }Hold_USERNAME_}}_CUBE). Holds will vanish when the user logs out but the property ALLOW_PERSISTENT_HOLDS in the cube }CubeProperties can undo this behaviour.
- Locks:
- a cube can be locked (look for the property LOCK in the cube }CubeProperties, the cell should contain the user name that locked the cube)
- a dimension can be locked (look for the property LOCK in the cube }DimensionProperties, the cell should contain the user name that locked the dimension) - listed for completeness although this does not have an impact on updating cube cells
- an element can be locked (look for the property LOCK in the cube }ElementProperties_DIMENSION, the cell should contain the user name that locked the element)
- Reservations:
- a cube or element can be reserved but I don't seem to be able to find a trace of this in TM1 control cubes. Does anyone know this ?
- Spreadsheets/websheets:
- you want to do input in an Excel spreadsheet with DBRW or DBR and certain cells are locked and the sheet is protected
- you use TM1Web and you want to do input in a websheet with DBRW or DBR but in the websheet properties the property "Allow Cell Write Back from Web" is unchecked (look at the properties of the websheet with a right-mouse button click)
- TM1p.ini parameters for the affected user:
- The user's configuration file for TM1 can contain the following properties: DisableWritebackOnTM1Formula, DisableWritebackOnDisconnect
- Application related Control Objects (Workflow and approval hierarchy):
- check for cubes starting with }tp_
- Security overlay:
- Given the existing security regarding cubes/dimensions/elements/cells, security can be overwritten by an overlay (look for the cube }SecurityOverlayGlobal_CUBE). Note: security overlay does not need to involve all dimensions of the original data cube.
- Data reservation:
- a data reservation was applied (look for the property DATARESERVATIONMODE in the cube }CubeProperties, the cell can contain several values: "OFF", "REQUIRED", "REQUIREDSHARED", "ALLOWED", "")
- Capabilities (including data spreading):
- a capability was used (look for the values of several properties in the cube }Capabilities, the cell can contain several values: "GRANT", "DENY", ""). Available properties: "Allow Spreading", "Consolidation TypeIn Spreading", "ManageDataReservation", "DataReservationOverride", "UseSandbox", "UsePersonalWorkspaceWritebackMode", "RunServerExplorer", "Allow Export as Text"). Please note that by default, all cells are empty but the effects are different. By default, some capabilities are granted, and other capabilities are denied. Refer to this page. Have a look at the tm1s.cfg file too.
- Excel/Cube viewer:
- you use Excel or the Cube viewer in Architect/Perspectives and want to overwrite a value at consolidated level to which you have security access but the input is refused (solution: use data spreading or enter a "p" followed by the new cell value)
- Disabled property:
- It's a bit of a no-brainer, but a user can be disabled. Of course, logging in will not be possible, but I still wanted to add it to this list. Look for the IsDisabled property in the }ClientProperties cube.
The last (related) item I would like to add, is the situation in which a user has access to a consolidated cell. The value is exactly 0. The user tries to overwrite the value of 0 with a non-zero value, and expects the new value to be spread equally over all the dependent leaf level cells. It is possible that this action raises an error message, in case a certain property in the tm1s.cfg configuration file is mentioned as follows: ProportionSpreadToZeroCells=F. In TM1 Architect/Perspectives the message reads: "Spreading operation has failed because the total value of the area or the consolidation is zero." If it would be True or omitted, overwriting the 0 value would be possible (and would default to the equal leaves spread because no basis exists to change cell values proportionally). Refer to this page.
Hopefully this gives the user a better understanding of what might be at stake in a certain cube cell.
