on - edited on by
DRay
Sisense V7.0 introduces a new mechanism to optimize and enhance the performance of all filters, as well as the ability to detect and avoid scenarios that caused Many-to-Many conditions.
These scenarios, also referred as Filter-by-Fact, are created when a filter is selected from a table that has a non-distinct relation towards another and possibly from a variety of schemes.
However, in all cases, the issue (prior to V7.0) is seen when one applies a specific filter and the resulting values increase instead of decrease.
Below are some prototype examples based on the common Northwind and AdventureWorks sample databases.
Example 1: Northwind, filtering from a Many-to-1 direction:
In the scenario above, the trivial relations are going from the employee and product dimensions towards the Order and Order Details tables. It is important to note though, that the Order detail has non-distinct (a 'Many') relation with the 'Orders' headers table.
Following that, here is a simple Pivot with measures from both [OrderDetails].[Quantity] and [Orders].[Freight], without a filter:
However, upon activating a filter from the Product table, the query path is duplicating each ProductID per multiple OrderIDs in the OrderDetails table. This Rresults in an increase in the 'Freight' measure, instead of a decrease like the 'Quantity' measure has:
In Sisense V7.0, the issue is now resolved:
Example 2: 'Adventure Works', Filter from one Fact to another, going through a Many-to-Many relationship:
In the pivot, 'PerAssemblyQty' is aggregated by the 'BOM Level', both fields are from the 'BillOfMaterials' table (and actually the Product table is not used anywhere in the query)
When OrderQTY filter was applied from the other Fact table, WorkOrder, the results were incorrect with 'BOM Level' = 3 even higher than it was before filtering. This happens since the filter is joined directly from one fact table to another, by the productId Many-to-Many relationship.
In Sisense V6.7, the numbers are correct:
Summary
Sisense now provides you with the flexibility and confidence of creating analytics without securing the filters and their query paths to be distinct in the ElastiCube model.
This ability is extremely useful in complex models which involve key tables and inherent M-2-M hierarchies (list of users who are both 'parents' and 'children' among themselves). Cases that used to take a great amount of modeling and machine resources can now be designed easily and use a fraction of the memory and query resolution time.
It is important to note that we continue to advise to always check and challenge your end results when working and designing new business questions.
A great effort was invested in making this feature stable, correct and efficient. If for any reason you believe this new ability hurts your existing analytics or degrade performance, please contact our technical support consultants at your earliest so we can look into the issue.
