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).
In the image below, getting from the dim status to closed projects can be achieved via several paths:
|Dim Status||In Progress||Dim Date||Closed Projects|
|In Progress||Dim Project|
|In Progress||Dim Account|
|In Progress||Dim PM|
|Open Projects||Dim Date|
|Open Projects||Dim Project|
|Open Projects||Dim Account|
|Open Projects||Dim PM|
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:n, n:n.
- If all possible paths contain many-to-many relationships, the path with the least number of rows 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.
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.
SELECT DISTINCT p.Status FROM [InProgress] p
SELECT DISTINCT o.Status FROM [OpenProjects] o
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.
In this case, we only have the key field, but in case we have more fields, we'll need to add them as well. 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:
'-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.
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.