Handling Relationship Cycles

Good relationships between tables are the key to pulling in data in logical ways. ElastiCube s are usually created from more than one table. To perform calculations on fields that reside in separate tables, you must define a relationship between the tables so the calculation mechanism knows how to navigate from one field to another.

When a single possible path exists between two fields, 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, since 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 required path to take 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)

Automatically Dealing with Relationship Cycles

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 shortest path, which will have less impact performance-wise on query processing. Below are some general guidelines.

• The calculation engine will always prefer the shortest path.
• If at the end of the first selection process more than one possible path still exists, the engine will prefer the path containing the least number of tables.
• Finally, if numerous paths are still possible, the engine will prefer the path with the table that was most recently added or the table whose schema was most recently updated.

Breaking Relationship Cycles in Sisense

To ensure the calculation engine takes the path you want it to take, the cycle must be broken. There are a few techniques to do this depending on the required results.

Removing Relationships

One option is to remove one of the relationships in 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 another method to break relationship cycles. In the example below, the Sales History table was duplicated and renamed Private Customers Sales History. In addition, the Customer ID relationship was removed from the original Sales History table. This results in the creation of two separate Amount fields, one for commercial customers residing in the original Sales History table, and one for private customers residing in the new Private Customers Sales History. As the Customer ID relationship was removed from the original table, whichever field you use will determine the correct calculation path.

Breaking Relationship Cycles in the Sisense

You can also use the Sisense web app to invoke a specific path to be used during query execution (as opposed to the solutions mentioned above that are defined on the schema level, in Sisense).

Each widget in Sisense has an associated data layout. If you have more than one path connecting corresponding fields between two tables, you can enforce a specific path by filtering the widget, and selecting just the field that you want to use. For example if the Commerce table is connected to Brands via the Brand and Brand ID fields, then add a filter to the widget and select Brand ID to use that path in the query.

.r.