Not all dimensions relate to all facts

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:

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:

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.