cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member

Analytical Need

In a dashboard, we wish to analyze how our entities (sales, inventory, tickets etc.) behave and break them by several categories. However, not all entities share the same categories. 

We wish to avoid a situation in which selecting a value in a certain category will produce illogical/incorrect results due to the random path mechanism (because there is no direct path between each dim & each fact).

Modeling Challenge

In the image below, getting from the DimStatus to ClosedProjects can be achieved via several paths:

Community_Admin_0-1634475715659.png

 

Community_Admin_1-1634475715702.jpeg

Image 1. No direct path between Dim Status & Closed Projects fact table. In red are the status dimension & fields.

** Random path mechanism (based on Handling Relationship Cycles😞

When Sisense encounters numerous possible paths (examples described above), it chooses the shortest path, which will have less impact performance-wise on the query.

The logic in choosing the path is as follows:

  • Sisense will prefer relationships in the following order: 1:1, 1:m, m:m.
  • If all possible paths contain many-to-many relationships, the path with the least number of m2m connections is preferred.
  • If more than one possible path still exists, it will prefer the path containing the least number of tables.
  • Finally, if numerous paths are still possible, one is picked at random.

Solution

We will add a fake key to our schema in a 3 step solution. This will ensure the direct path between the Status & Closed Projects tables:

Step 1: Add a fake key to the Dim Status table.

SQL:

SELECT DISTINCT p.Status FROM [InProgress] p

union 

SELECT DISTINCT o.Status FROM [OpenProjects] o

union 

SELECT '-1'

The last union ( Select '-1' ) will add another record to this dimension. This is one side of the direct connection to our Closed Projects table.

As a rule of thumb, if there is a description for the key, put down a value that symbolizes that is it a fake record, like 'No Status'/ 'Fake Status' etc.

Step 2: Add the fake key to the Closed Projects fact table (either by the below example query or by adding a new custom field with the value of -1):

SQL: 

SELECT p.*,

 '-1' AS Status 

 FROM [ClosedProjects] p

Adding this '-1' as a new field will be the other side of the direct connection to the dim status table.

Step 3:

Connect the dim & fact table. The schema will look like this:

Community_Admin_3-1634475770261.jpeg

Image 2. Updated schema. Marked in red are the changes that have been made.

Include/exclude options:

Include : If nothing is selected in the filter (taken from the fake linked DimStatus) then the measure from the connected fact will still be calculated. When any value is selected then no results/null would appear for the measure - This is the solution described above.

Exclude : If there is a filter, then the measure would never be calculated - Skip step 1 in the solution.

Attachments

 - 545 KB - MultiFactNotAllDims.ecdata

Version history
Last update:
‎10-17-2021 06:03 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request