This article presents a plugin to allow you to create a Left join within one certain widget.
In order to create a Left Join in Sisense Elasticube, Please create a Custom SQL Table and perform the Left Join connection there.
In data modeling, the action of merging tables together (usually for querying purposes) is called a join. It is a very common action that happens behind the scenes every time you create a widget with fields based on more than one table. You can find more information about joins and join types in the "Joining Multiple Sources 101: Inner and Outer Joins" blog.
The following article will explain how you can create a simple 1 dim to 1 fact left join in your widgets.
By default, the queries generated by Sisense are creating an inner join, thus excluding all values that do not exist in both of the joined tables. Imagine the following scenario:
In our Elasticube, we have a Products dimension table and a sales-details fact table.
Due to the inner join, every widget that will fetch fields from both tables will only fetch records that have a full match. For example: If we try to query a list of products and the revenue yielded from their sales, we will not see any reference to products that were never sold.
In most cases, this is the required behavior. However, in others, the lack of activity can tell us a lot about our business and low performing items.
Implementing the Solution
1. In your Elasticube, find the dimension, or the master table from which you want to initiate the left join.
2. Add a new custom field of type Int.The value of this field should be a hardcoded 0:
3. Build your Elasticube to submit the changes.
4. Open your dashboard of choice and create a new widget. In the Categories\Rows panel, choose any field from the master dimension table
5. Create a new value and go to the formula menu:
6. Find the dummy field from your dimension table, and sum up its value. Add that aggregation up to the original measure from the fact table:
This combination will provide you with all of the values from your dimension, whether they have recorded activity or not:
Identify Items with no Activity
Once the Left join field is added and the "left" value is defined, you can create a widget that would list all items that have seen no activity. In our example, products that were not sold at all.
To do so, follow these steps:
1. Hover over the newly created value and click on the funnel icon to create a value-based widget filter:
2. set your filter to 0:
Your widget will now list all items with no activity.
If you would like, you can now disable the value and leave only the "Rows" active. As long as you don't change the filter, it will still remain valid.