Calculation Paths

ElastiCubes are usually created from more than one table.  In order to be able to perform calculations on fields that reside in separate tables, a relationship must be defined between the tables so the calculation mechanism knows how to navigate from one field to another.   A calculation path is made up of all relationships leading from one table to another.  

When a single possible path exists leading from one field to another, there is no issue.  For example, performing a query involving the Customer Name and Amount field on the ElastiCube schema below can yield only one result as there is only one possible path leading from the Customer Name field to the Amount field.

Relationship Cycles

In some cases, more than one path exists leading from one field to another.  Sometimes this is due to poor database design, and other times it is just a realistic necessity due to the way the data is structured.  In such cases, there is no absolute way to determine the require path to take when asking to calculate results.

For example,  in the ElastiCube schema below the Customer Name and Amount fields now have multiple paths between them:
- Customer ---> Sales History (over the Customer ID field)
- Customer ---> Commercial (over the Customer ID field) ---> Sales History (over the Business ID field)
- Customer ---> Private (over the Customer ID field) ---> Sales History (over the Customer ID field)

Theoretically, any one of these possible paths could be the correct one in terms of the results you are trying to calculate.  When the calculation engine encounters numerous possible paths, it picks the path that has less impact, performance-wise, on query processing:

1. The calculation engine would always prefer paths that have no many-to-many relationships.  If all possible paths contain many-to-many relationships, the path with the least number of this type of relationship is preferred.  Similarly, the engine would prefer one-to-one relastionships over one-to-many relationships.
2. If at the end of the first selection process more than one possible path still exists, the engine would prefer the path containing the least number of tables
3. Finally, if numerous paths are still possible, one is picked at random.

 

Breaking Relationship Cycles

To assure the calculation engine takes the path you want it to take, the cycle must be broken.  There are a few techniques of doing this, depending on the required scenario. 

 

Removing Relationships

One option is to remove one of the relationships causing the cycle.  For example, removing the Customer ID relationship from the Sales History table will eliminate the cycle.  This approach is only applicable when the removed relationship is not used by other queries.

Duplicating Table Elements

Duplicating table elements is also a method from breaking relationship cycles.  In the example below, the Sales History table was duplicated and named "Private Customers Sales History".  In addition, the Customer ID relationship was removed from the original Sales History table.  This causes two separate Amount fields to be created, one for commercial customers residing in the original Sales History table and one for private customers residing in the new Private Customrs Sales history.  Because the Customer ID relationship was removed from the original table, whichever field you use will determine the correct calculation path.