Today we were looking at how we could optimise a client’s rules to improve performance. The first couple of rows of rules were STET rules. In other words, rules that turn on or off the subsequent calculation of rules. These are really useful to limit what intersections are calculated, which in turn, can improve performance by not calculating unwanted elements. The rule had a long list of versions (or scenarios) and then if the version currently being evaluated was in that list, then the rule was STET. In other words, do nothing and move on and evaluate the next intersection.
Using an Attribute on the Version Dimension
We then thought about how we could make it easier to administer the enabling and disabling of rules for a cube and decided that instead of listing all the versions in the rules for each cube, we should use an attribute on the Version dimension so that if it is True, then CONTINUE to calculate the rules and if not, then STET. This, we decided, would work great when all cubes with the Version dimension have rules either calculating or not based on a common definition from that one attribute. In our case, we needed more flexibility.
Using a Simple Control Cube
Finally, we decided that the best method for us to enable and disable rules for our specific model was to create a simple cube that had the Version dimension, the }Cube control dimension and a simple measures dimension with a single element. In the single measure, we then place a drop-down picklist for Active or Inactive. We then set the entire cube to Inactive and only enabled the required versions for the required cubes. Finally, we reworked our STET rules to reference our new cube and where the intersection is Active, then CONTINUE and where Inactive, then STET.
Now we have the capability to turn rules on and off right across the model, so we can, for instance, turn the budget version on when it is time to budget and leave it off for the rest of the year.