Access to cube cells in TM1

Introduction

TM1 holds data in cubes. Cubes are structured using dimensions. 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:

  1. Rules:
    • the cell is calculated by rules. The cell is not editable unless the STET function was used.
  2. 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.
  3. ReadOnly user:
    • a user can be marked as ReadOnly (look for the property ReadOnlyUser in the cube }ClientProperties)
  4. 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.
  5. 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)
  6. 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 ?
  7. 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)
  8. TM1p.ini parameters for the affected user:
    • The user's configuration file for TM1 can contain the following properties: DisableWritebackOnTM1Formula, DisableWritebackOnDisconnect
  9. Application related Control Objects (Workflow and approval hierarchy):
    • check for cubes starting with }tp_
  10. 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.
  11. 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", "")
  12. 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")
  13. 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)

Hopefully this gives the user a better understanding of what might be at stake in a certain cube cell.




Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links