Feeders, Invalidations and Rules ​

Quick comparison to Spreadsheets for the uninitiated ​

In regular Spreadsheets we have to have two axis (or dimensions), rows and columns. Rows are numbered and columns are lettered.

In Multidimensional Spreadsheets we have any number of axis (or dimensions) and instead of identifying items on each axis with numbers or letters we use names which describe their contents. So instead of refering to A1 (or R1C1) we may be refering to ["Actual","Jan 2020","Units Sold","George St Store"]. This has a lot of benefits which are well documented but for one we can easily confirm that we are looking at the numbers we want based on their address.

Formulas ​

Formulas in Multidimensional Spreadsheets are not per cell as they are in traditional spreadsheets, instead they apply to a range of cells within a workbook (or Cube). This is typically called the scope of the formula. A common user-error within many traditional workbooks is making a change to a formula and then not filling or copying the change across the correct number of cells. This problem is completely eliminated in multidimensional spreadsheets.

Revenue Formula Example ​

["Revenue"] = ["Units Sold"] * ["Price"]

["Revenue"] = ["Units Sold"] * ["Price"]

Two additional systems are at play with this formula. Both an invalidation and a feeder. Both systems work from the components of the formula (Units sold and Price) and apply to the target of the formula (Revenue).

• Feeder tells the system that if there is a value in "Units Sold" or against "Price", make a place for "Revenue" so that when this cell is requested, it exists and the resulting calculation can take place.
• Invalidation tells the system that if there is a change made to "Units Sold" or to "Price" that the "Revenue" cell should have its cache invalidated so that next time it is requested it will calculate the updated result.

This will generate the equivalent feeders and invalidators in the system:

["Units Sold"] ⇒ ["Revenue"]
["Price"] ⇒ ["Revenue"]

["Units Sold"] ⇒ ["Revenue"]
["Price"] ⇒ ["Revenue"]

Explained in english, the feeder will make sure a "Revenue" cell exists if there is either a "Units Sold" or a "Price" value and when changes are made to Units sold or Price, invalidate Revenue cached value.

Turning off a Feeder ​

Since we know that we will only have a resulting number against "Revenue" if both "Units Sold" and "Price" have values, we can choose to disable one of the feeders. We will likely always have a price so feeding only this (instead of both this and units sold) would be inefficient (to the same degree as not disabling any feeders). Thus "Units sold" is the primary determinant factor of whether there is going to be "Revenue" values so we can turn off the feeder for "Price" like so.

["Revenue"] = ["Units Sold"] * [Δ"Price"]

["Revenue"] = ["Units Sold"] * [Δ"Price"]

The delta symbol in the above formula will turn off the generated feeder from cells associated with "Price".

Needless to say, feeding less cells to achieve the same result for a formula, means more efficient memory usage in our models and more responsive calculations across the system.

Why the Delta Symbol ​

The reason the Δ (Delta) symbol is chosen is because it denotes that the feeder is disabled however the change in "Price" will still invalidate the cache against "Revenue". It can be further explained as "Only process the invalidation for delta (change in) "Price" against the "Revenue" cell, not the feeder".

Feeder Ratio ​

The feeder ratio is a concept used to evaluate the efficiency of a Formulas feeders. In the above example, once the feeder for price has been disabled the feeder ratio is 1:1 that is Formula Components:Fed (created) Cells. Previously, before disabling the feeder for "Price" this ratio would be 2:1. Two cells, "Price" and "Units Sold" generates one "Revenue" cell. Generally if a feeder ratio is too skewed like so 1:80 of more it is considered to be a large feeder.

Formula Restrictions ​

In the above example, the formula only applies to the "Revenue" element. Should this calculation differ between Brands then it could either employ an "IF" function to differ the logic based on the department or the formula could be further restricted to a one or more Brands allowing additional formulas to be created which applies different logic to diffferent Brands.

Formula Restrictions determine the target or scope of cells which a formula applies to, which in turn affects the scope of cells affected by generated feeders and invalidations from that formula. In the next example the restrictions need to be set in order to aide in the feeder and invalidations correctly targeting the cells.

Brand Allocation Formula Example ​

["Brand:Default","Allocated Amount"] = 
["No Brand","Unallocated Amount"] * ["Allocation %"]

["Brand:Default","Allocated Amount"] = 
["No Brand","Unallocated Amount"] * ["Allocation %"]

The purpose of this formula is to allocate the "Unallocated Amount" which is currently not stored against a brand (it is presently against a placeholder called "No Brand" in a Brand dimension) across any number of Brands based on a percentage input.

The formula is restricted to a measure called "Allocated Amount" and a hierarchy in the Brand dimension called "Default".

Brand Dimension ​

The brand dimension has two hierarchies, "Default" which includes our business structure and "No Brand" which includes a placeholder element for information which is not currently per Brand.

Hierarchy:Default

• All Brands
  • Brand A
  • Brand B
  • Brand C
  • Brand D
  • Brand E
  • Brand F

Hierarchy:No Brand

• No Brand

Formula Restrictions impact on Invalidations and Feeders ​

If the formula excluded the "Brand:Default" hierarchy restriction, here are the resulting feeders.

["Allocated Amount"] = 
["No Brand","Unallocated Amount"] * ["Allocation %"]

["Allocated Amount"] = 
["No Brand","Unallocated Amount"] * ["Allocation %"]

Feeders and Invalidations:

["No Brand","Unallocated Amount"] => ["Allocated Amount"];
["Allocation %"] => ["Allocated Amount"];

["No Brand","Unallocated Amount"] => ["Allocated Amount"];
["Allocation %"] => ["Allocated Amount"];

Any changes to the "Allocation %" will correctly invalidate and feed the "Allocated Amount" cells, however changes made to "Unallocated Amount" cells will only feed from "No Brand" to "Allocated Amount" against "No Brand" which is not where we are calculating the results of the allocation.

In effect

The Problem

Changes to "Unallocated Amount" will neither feed nor invalidate the target measure "Allocated Amount" per Brands as desired, instead it will affect "No Brand" which is not the intended behavior.

The Solution ​

By updating the formula restrictions to include a restriction by "Brand:Default" hierarchy we then adjust the resulting Invalidations like so.

Including the restriction on "Brand:Default" hierarchy

["Brand:Default","Allocated Amount"] = 
["No Brand","Unallocated Amount"] * ["Allocation %"]

["Brand:Default","Allocated Amount"] = 
["No Brand","Unallocated Amount"] * ["Allocation %"]

Resulting Feeders:

["No Brand","Unallocated Amount"] => ["Brand:Default","Allocated Amount"];
["Brand:Default","Allocation %"] => ["Brand:Default","Allocated Amount"];

["No Brand","Unallocated Amount"] => ["Brand:Default","Allocated Amount"];
["Brand:Default","Allocation %"] => ["Brand:Default","Allocated Amount"];

In effect

Now changes to "Unallocated Amount" against "No Brand" will correctly invalidate the cache for "Allocated Amount" across all Brands.

The Feeder Ratio ​

The current feeder ratio for each of the generated feeders is as follows:

["No Brand","Unallocated Amount"] => ["Brand:Default","Allocated Amount"];

["No Brand","Unallocated Amount"] => ["Brand:Default","Allocated Amount"];

Ratio 1:6

For every one cell with an Unallocated Amount we will create 6 cells against "Allocated Amount", one per Brand in the Default hierarchy.

["Brand:Default","Allocation %"] => ["Brand:Default","Allocated Amount"];

["Brand:Default","Allocation %"] => ["Brand:Default","Allocated Amount"];

Ratio 1:1

For every "Allocation %" we create once cell against "Allocation Amount".

Performance Tuning

This formula can be further improved through turning off a feeder. Having an "Unallocated Amount" will not result in us having a "Allocated Amount", the driver of the result is where we have an "Allocation %". Thus we can disable the feeder for the "Unallocated Amount".

["Brand:Default","Allocated Amount"] = 
[Δ"No Brand","Unallocated Amount"] * ["Allocation %"]

["Brand:Default","Allocated Amount"] = 
[Δ"No Brand","Unallocated Amount"] * ["Allocation %"]

This results in us eliminating the 1:6 feeder and leaving us with a performance tuned formula with only a 1:1 feeder. Changes to any inputs of the formula, either "Unallocated Amount" or "Allocation %" still invalidates the cache and calculates the formula correctly.

User Interface for Designing Formulas ​

Our unique user interface for building formulas includes four distinct components -

1. Formula Description

2. Formula Restrictions, also considered the left hand side of the equation before the equals.

3. Formula Logic (the formula itself)

4. Formula application to bottom most cells or parent cells (Normal vs Average or Rate Calculation).

The formula above places the annual salary at the beginning of the Plan, divided into a monthly amount, into the employment start month.

By changing to the Formula list tab we can see the full list of formulas for this cube. The drag and drop interface allows us to re-order the calculations. Double clicking lets us enter an existing formula to modify it.