Left Joining Tables in Cube

Comments

5 comments

  • Avatar
    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?

  • Avatar
    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.

     

  • Avatar
    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.

  • Avatar
    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.

  • Avatar
    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.

Please sign in to leave a comment.