Cell Level Security

Planning Analytics cell level security provides the ability to secure individual cells without the need for a complicated series of element-level 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.

Creating a Cell Security Cube

A cell security cube can be created manually (right-click the cube and select “Security”, then “Create Cell Security Cube”) or programmatically (using the CellSecurityCubeCreate function). Using the manual method is easier (as long as you are a PA Administrator) and produces a cell security cube containing all 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 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.

Applying Cell Security

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.

Creating Cell Security Rules

Once you have a cell security cube created, you’ll need to create a rules file 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 “Create Rule”) or programmatically (using the RuleLoadFromFile 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.

Manually Applying Cell Security Rules

Before implementing any cell security rules, you should have a thorough understanding of what your security scheme will need to look like –  including what groups and permissions – you’ll need. Additionally, the following are some general principals to consider when creating cell security rules:

  1. Make sure to add the key words SKIPCHECK and FEEDERS (or FEEDSTRINGS) to the rules file.
  2. Use spaces, indenting and annotations within the rules file to improve clarity.
  3. Not all functions work in rules (some are valid in TurboIntegrator only) so make sure you know which functions are supported within rules.
  4. Rules should be organized in “blocks” and “stacks” (also referred to as sets). A block is a single, complete logical rule statement, and a stack 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.
  5. The AREA (left side) of a rule identifies one or more cells in a cube and should be as “narrow” or explicit as possible.
  6. Rules in a rules file are executed from top-to-bottom. That is, PA will evaluate the rules sequentially and if more than one (rule) statement in a stack of rules applies to the samearea, 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 as possible in each rule statement and avoid unnecessary levels of rule statements with a rule set.
  7. Typically, cell security rule statements would NOT be used to define consolidations, but in an effort of completeness, good practice 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.
  8. Thoroughly understanding the logic in a rule stack is important since circular references 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. In some cases, a circular reference may occur when you implement many large rule stacks.
  9. Determining the “optimal” number of rule blocks and rule set levels (for a cube) is typically based upon specific scenarios and may take some experimentation. Generally, “less is more”, avoid the “overlap” and use variables instead of hard-coded string values wherever you can.
  10. Verify when the use of STET (cancels or “bypasses” 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 on how overall cell security will be applied.
  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 “caught” by the rule blocks above and is particularly useful to cover any new intersections triggered by cube meta data changes.

Using the manual approach (for creating cell level security rules), creates a blank cube rule file, which you can then open in one of the Planning Analytics rules editor options and begin “composing” your cell security rules. If you utilize the Planning Analytics Workspace rules editor, you will see line numbers corresponding to line numbers in the TM1 rules .rux file, however the traditional 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 to optimize or debug your rules (more on this later).

Debugging

While testing your security, keep in mind that if you have both cell-level AND element-level security in place, cell-level security overrides element-level security, so cell-level security for the cell must be undefined. Cell-level security applies to leaf elements 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.

Planning Analytics provides a tool called the Rules Tracer 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.

Rule Statistics

Normally, you can monitor rule statistics to gain some insight as to how frequently individual rule blocks execute and how long it takes to run each rule block. These statistics are stored in the }StatsByRule 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.

The }StatsByRule cube contains three dimensions:

  • }Cubes – Contains elements corresponding to each cube on your Planning Analytics server.
  • }LineNumber – Contains elements 1 through 10,000, corresponding to line numbers in a Planning Analytics rules .rux file (remember, to see the line numbers use the Planning Analytics Workspace rules editor).
  • }RuleStats – Contains measure elements that correspond to the information and statistics that are stored for rules on your database.

To view the }StatsByRule cube, you’ll need to set “Display Control Objects” (or, in Planning Analytics Workspace, check for it under the Control Objects). If you don’t see any statistics, make sure that you set the Rule_Stats property for the associated cube to “YES” in the }CubeProperties control cube and then start Performance Monitor (Performance Monitor is a feature of Planning Analytics that monitors and writes statistics to the }StatsByRule and other cubes). If you still do not see any statistics, wait “a bit” and recheck, since statistics are updated on sixty-minute intervals (also, keep in mind that this data is cleared every time that you restart your Planning Analytics server).

Still not finding any statistics? Well, times recorded are in milliseconds and, some rules do execute faster than 1 millisecond, resulting in an entry of 0 or no entry at all showing in the }StatsByRule cube. In most cases, cell security rules implemented are (should) be relatively simple and therefore the }StatsByRule cube doesn’t help.

Removing a Cell Security Cube

Planning Analytics uses control objects to perform integral system tasks. Most control objects are automatically generated by planning analytics and can be considered “system control” objects. Cube cell security cubes are also control objects – but created by you. Like all control objects, a cube cell security cube cannot be deleted by right-clicking and selecting “Delete”; they must be deleted using the CubeDestroy function. Using this function will delete the cube and will also (even though the rules exist in a separate file), delete all of the rules you have implemented in that cubes rule file.

Conclusion

The above information should provide you with a foundation for implementing and maintaining cell level security within a Planning Analytics environment, or simply provide some insight into how security works. In the end, try to keep things as simple as possible and be sure your test plan includes logging into Planning Analytics as actual users to validate your assumptions.