Skip to main content

Left Joining Tables in Cube



  • Tal Admon

    Every relationship in the model is a join - to have a left join you need to have a custom query, unfortunately.

    @sisense - any plans to allow left join from the elasticube manager?

  • Adi Hecht

    I am also (left) joining in on this request.

    This is really necessary especially when you have dimension tables with missing dimensions, for example if you have a fact table with all the orders and a dimension table with customer details, but some customer IDs in the orders table do not have a match in the customers table. With a very large fact orders table but rather small customers table.


  • Ian Tebbutt

    We have also hit this issue in multiple cubes, but we have  get round that works well. In our instance a Docket should have a HaulerId but that field is occasionally null. Out of the box Sisense will hide any records without a HaulerId when those tables are joined.

    Our fix is to create a calculated field HaulerId_Fixed in the Docket table, and join on that instead

    CASE WHEN IsNull([HaulerId]) THEN 77 ELSE HaulerId END

    And add an 'unknown hauler' record to the Hauler table, in this example that has a HaulerId of 77. Everything now works fine, and those hidden dockets appear, even better our users can see who hasn't got a Hauler set and fix the data in our upstream system.

  • Malinda Jepsen

    We have a standard practice of including an "?Unknown" row in every dimension. We assign the ID of -1 so that it's very clear when querying the data. If you don't want to add the data to your source, you can handle this with a SQL Server view (e.g. use COALESCE).  If you aren't using SQL Server, there may be a similar concept or you could do it with a custom query in Sisense.  We also have a row with an identifier of -2 to indicate that it is "_N/A". This is the case when we have a dimension that doesn't apply to every fact row rather than showing ?Unknown when it doesn't apply. We use the convention of the ? or _ to start the name so they bubble to the top of sorts and allow end users to see there may be an underlying data issue.  BTW: We don't allow any NULL values in our fact tables to simplify querying anyway, so this wasn't anything new to do with Sisense.

  • Ian Tebbutt

    I like that approach, but we're in a non DW environment, just pulling tables straight from production systems, hence the get round.

    Fixing these issues in the ETL, and keeping the warehouse clean is exactly what I'd be doing normally and the view with a coalesce is a neat idea.

  • Ed Trembicki-Guy

    When using the SiSense Elasticube Manager and selecting 'Custom SQL Expression' from 'Add Data',
    I was surprised when a result set row count was lower after left joining a table.
    I discovered that SiSense may have an issue with null values, because I was able to construct a work-around
    by using an inner select on the join.


    select count(*) from x -- returned 1552 rows

    select count(*) from x
    left outer join y on y.user_id = x.user_id and y.program_id = x.program_id and y.prev_date is null -- returned 1548 rows

    select count(*) from x
    left outer join (select user_id,program_id from y where prev_date is null) py on py.user_id = x.user_id and py.program_id = x.program_id -- returned 1552 rows

  • Sree Jarugula

    Any updates in the newer versions on the left join ?

  • Bart van Velden

    I hope so this will fixed soon. We have a hard time understanding why joins in Sisense behave different from any other SQL engine in the world.

  • Scott Thibodeau

    It is simply crazy that Sisense does not support joins other than inner join. Do they even use their own platform? Having the ability to do different join types within the elasticube would unlock so much potential for Sisense to complete with the other large players and eliminate the need for "Builders" (who Sisense promotes as their #1 focus client) to spend needless time writing custom SQL queries and wasting extra storage space. Cmon Sisense! Deliver basic functionality before all the bells and whistles. 


Please sign in to leave a comment.