Problem
Advanced formulas can sometimes return wrong results (or no results at all) when combining dimension-affiliated calculations** with dashboard or widget filters based on a second dimension.
Certain calculations may include backstage joins between dimension tables. As dimension tables are not directly connected to each other, those joins may be prone to "random path" problems, going through unrelated fact table (given the fields that are part of the measure) and fetching incorrect results to the dashboard.
**In this post, the term "dimension affiliated calculation" (or DAC in short)>mese refers to functions that take a dimension as an input, such as:
- Measured Value
- ALL()
- Multipass
- Contribution
- Ranking Filter
The problem could occur when the following conditions apply:
- The widget is based on an Elasticube that contains multiple Dimensions and multiple Fact tables.
- The widget contains one or more leading dimensions in the form of categories or break-by's, taken from dimension tables.
- The problematic formula contains a dimension affiliated function that refers to one of the widget's categories. This formula returns wrong results, or no results at all, while its raw, non-affiliated form is calculated properly.
OR
The widget is subjected to a ranking filter based on one of the widget's categories dimensions. Once the filter is applied, the results are distorted. It could either return wrong results, no results, or the filter's ranking could be off (too many members, partial results etc). - The widget is subjected to either a dashboard filter or a widget filter based on another dimension, not included in the widget's categories. Once this filter is disabled, the calculation yields results.
In the following example, we see 2 instances of the same widget.
Both use the product name as the leading dimension and attempt to calculate its contribution to the general line total.
The difference between them is whether or not the date filter affects the widget or not.
The date filter's effect is disabled on the right widget. It takes no part in the query and the contribution is properly calculated.
On the left, when the date filter is enabled the contribution calculation is disrupted and no results are shown, even though the raw Line total value on which is based yields correct results.
Below is the model of the underlying elasticube:
Cause
When using DACs based on the widget's leading dimension, the widget's query will enforce all active dashboard and widget filters directly on that dimension.
In our case, the Date dashboard filter will be applied on the ProductCategory.
This filter requires a join between the Date and Products' dimension tables, and thus, without realizing that, we have an indirect relationship prone to random paths.
At this point, the query can take either one of 2 routes:
- The green route: Date → Orders→Products
- The red route: Date → Targets → Products
The path is selected by an algorithm that weighs both the path length and the order of table insertion.
Given 2 routes of the same length, the algorithm will prefer the one with the newest tables in it.
Since the algorithm takes a cross-system deterministic approach, it does not refer to the specific formula's context and does not take the underlying measure into account.
In our case, the Sales Targets table was added to the model after the Orders table was, and therefore, the algorithm prefers the red path, as we can see in the widget's underlying query:
Workaround
The widget's query can be routed by adding "guiding" filters that force the query, and all of its advanced calculations to go through the fact table we prefer:
Add a new dashboard or widget filter over the fact table's filtering foreign key (the field connected to the filtering dimension).
The filter does not have to select specific values. All Items would do.
This filter will be taken into account when the query's potential paths are weighed and will point out the preferred way to the algorithm.
Limitations
This foreign guide will work only as widget or dashboard filters, and not on a single formula level (measured value), meaning that we can determine only one route per widget.
It is, therefore, suitable for widgets that contain DAC's based only on a single fact table.
In complex widget with multiple DAC's based on different fact tables will all be directed from the same path, which might still sometimes result in wrong results.
Long-Term Solutions
In order to decrease user friction and involvement, the planned medium-term solution will adjust to the Elasticube's translation layer to prefer query context over chronological insertion order.
Due to translator limitations, this route preference will be determined on the widget level, and will be subjected to the same limitation as the workaround described above.
Once released, the perspective plugin may also be used to increase and refine the designer's query control.
