{"id":4065,"date":"2023-08-24T12:21:15","date_gmt":"2023-08-24T12:21:15","guid":{"rendered":"https:\/\/quebit.com\/askquebit\/?p=4065"},"modified":"2026-01-20T16:05:22","modified_gmt":"2026-01-20T16:05:22","slug":"cell-level-security-in-ibm-planning-analytics","status":"publish","type":"post","link":"https:\/\/quebit.com\/askquebit\/cell-level-security-in-ibm-planning-analytics\/","title":{"rendered":"How Do I Use Cell-Level Security in IBM Planning Analytics?"},"content":{"rendered":"

Cell Level Security<\/strong><\/p>\n

\"\"Planning Analytics cell level security<\/strong> provides the ability to secure individual cells without the need for a complicated series of element-level<\/strong> security cubes, which sets control on cells in a cube identified by dimension elements. Cell-level security can add a more finite level of control to a cube but can also become cumbersome, as each cell within a cube requires its own security setting. Cell level security requires a cell security cube (}CellSecurity_cube_name) to be created for the cube to apply cell security on.<\/p>\n

Creating a Cell Security Cube<\/strong><\/p>\n

A cell security cube can be created manually (right-click the cube and select \u201cSecurity\u201d, then \u201cCreate Cell Security Cube\u201d) or programmatically (using the CellSecurityCubeCreate<\/strong>\u00a0<\/a>function). Using the manual method<\/em> is easier (as long as you are a PA Administrator) and produces a cell security cube containing all<\/u> of the dimensions of the target cube. This is preferred if your cell security requirements could evolve in the future to scenarios that possibly include combinations of all of the dimensions within the cube. If the cell security requirements are explicitly defined (and have a low probability of changing to include all of the dimensions), then the programmatic approach<\/em> can be used. Using the programmatic approach, you can indicate which dimensions of the target cube to include in the cell security cube, by using string parameters of a 1 (include) or 0 (exclude). The programmatic approach will result in a cell security cube with a reduced set of dimensions.<\/p>\n

Applying Cell Security<\/strong><\/p>\n

All cell level security is driven from the values (READ, WRITE or NONE) in the target cubes cell security cube. Obviously, someone with write access to the cell security cube can manually set and maintain these values but that is typically not practical (especially in a controlled environment) and not recommended. Other methods for maintaining the values in a cell security cube are by using CELLPUT functions within a TurboIntegrator process or by assigning rules (to the cell security cube). The preferred approach is to use rules.<\/p>\n

Creating Cell Security Rules<\/strong><\/p>\n

Once you have a cell security cube<\/strong> created, you\u2019ll need to create a rules file<\/em> for it. A cube rules file is where rules are saved and is named cube_name.rux. This can again be accomplished manually (right-click the cube and select \u201cCreate Rule\u201d) or programmatically (using the RuleLoadFromFile<\/a> function). The program approach requires a text file source and, typically, is more applicable for rule maintenance automation, rather than an original rule implementation application.<\/p>\n

Manually Applying Cell Security Rules<\/strong><\/p>\n

Before implementing any cell security rules, you should have a thorough understanding of what your security scheme<\/a> will need to look like –\u00a0 including what groups and permissions – you\u2019ll need. Additionally, the following are some general principals to consider when creating cell security rules:<\/p>\n

    \n
  1. Make sure to add the key words SKIPCHECK<\/a> and FEEDERS<\/a> (or FEEDSTRINGS) to the rules file.<\/li>\n
  2. Use spaces, indenting and annotations within the rules file to improve clarity.<\/li>\n
  3. Not all functions work in rules (some are valid in TurboIntegrator only) so make sure you know which functions are supported within rules<\/a>.<\/li>\n
  4. Rules should be organized in \u201cblocks<\/strong>\u201d and \u201cstacks<\/strong>\u201d (also referred to as sets). A block<\/em> is a single, complete logical rule statement, and a stack<\/em> is a series of rule blocks, typically applying to particular area in a cube. Liberally use spacing, indenting, and clear annotations to define your blocks and stacks of rules.<\/li>\n
  5. The AREA<\/a> (left side) of a rule identifies one or more cells in a cube and should be as \u201cnarrow\u201d or explicit as possible.<\/li>\n
  6. Rules in a rules file are executed from top-to-bottom<\/strong>. That is, PA will evaluate the rules sequentially and if more than one (rule) statement in a stack of rules applies to the\u00a0same<\/em>area, the first statement takes precedence. The number of levels (rule statements) of stacking that can be accommodated is limited only by available memory, however care should be taken to be as explicit<\/em> as possible in each rule statement and avoid unnecessary levels of rule statements with a rule set.<\/li>\n
  7. Typically, cell security rule statements would NOT be used to define<\/u> consolidations, but in an effort of completeness, good practice<\/em> asserts that although you can (define consolidations using rules), it is not recommended since consolidations defined within a dimension process much faster than those defined in rules, especially in very large, sparse cubes.<\/li>\n
  8. Thoroughly understanding the logic in a rule stack is important since circular references<\/strong> can easily occur causing errors and\/or invalid results. A circular reference in Planning Analytics is very similar to a circular reference in MS Excel where a formula refers to its own cell or refers to a cell that’s dependent on the formula’s result.\u00a0In some cases, a circular reference may occur when you implement many large rule stacks.<\/li>\n
  9. Determining the \u201coptimal\u201d number of rule blocks and rule set levels (for a cube) is typically based upon specific scenarios and may take some experimentation. Generally, \u201cless is more<\/em>\u201d, avoid the \u201coverlap<\/em>\u201d and use variables<\/em> instead of hard-coded string values<\/em> wherever you can.<\/li>\n
  10. Verify when the use of STET (cancels or \u201cbypasses\u201d the effect of a rule) or CONTINUE (allows a subsequent rule with the same area definition to be executed ) makes sense. Each will have quite a different effect<\/em> on how overall cell security will be applied.<\/li>\n
  11. Consider the use of []:=S as the final rule block in the cell security rules file. Since rule blocks are executed from the top to the bottom, this rule will cover all remaining intersections in the cube that were not \u201ccaught\u201d by the rule blocks above and is particularly useful to cover any new intersections triggered by cube meta data changes.<\/li>\n<\/ol>\n

    Using the manual approach (for creating cell level security rules), creates a blank<\/u> cube rule file, which you can then open in one of the Planning Analytics rules editor<\/a> options and begin \u201ccomposing\u201d your cell security rules. If you utilize the Planning Analytics Workspace rules editor<\/a>, you will see line numbers<\/strong> corresponding to line numbers in the TM1 rules .rux file, however the traditional<\/em> Rules Editor does not display line numbers. If you chose to use the traditional editor, you can open the .rux file in a text editor that supports line numbers to view the line numbers for a rule. Line numbers are helpful during rule editing but are particularly important when leveraging the }StatsByRule control cube<\/strong><\/a> to optimize or debug your rules (more on this later).<\/p>\n

    Debugging<\/strong><\/p>\n

    While testing your security, keep in mind that if you have both cell-level AND element-level security in place, cell-level security overrides<\/em><\/strong> element-level security, so cell-level security for the cell must be undefined<\/strong>. Cell-level security applies to leaf elements<\/u> and generally does not apply to consolidations, although you can use the NONE and READ security rights to control the display or editing of consolidations. If you do have both security methods in place , allow additional time for testing each scenario.<\/p>\n

    Planning Analytics provides a tool called the Rules Tracer<\/a> to assist in the development and debugging of rules and the rule tracer will work with cell security rules. Reviewing specific views within the cube cell security rule is a method of testing the effectiveness of the implemented rule, however in more complicated scenarios, having specific non-admin users (who are assigned to appropriate groups) log into the environment and verify READ, WRITE and NONE intersections is the only way to guarantee correct results.<\/p>\n

    Rule Statistics<\/strong><\/p>\n

    Normally, you can monitor rule statistics to gain some insight as to how frequently<\/em> individual rule blocks execute and how long it takes<\/em> to run each rule block. These statistics are stored in the }StatsByRule<\/strong> control cube. Each time a rule block is changed or compiled, the data for that rule block is cleared and updated in the }StatsByRule control cube. This can help you to instantly see the impact of changes you make in your rules.<\/p>\n

    The }StatsByRule cube contains three dimensions:<\/p>\n