Forum Discussion

zoolee's avatar
zoolee
Data Storage
05-21-2025
Solved

Does the data cube use always inner join?

Hi Community!

I have many years experience with other BI tools but I’m new in the Sisense’s world. I just built up a basic model and recognise when I tried to create a table or pivot, I saw just those rows where the keys are same.

It seems that the cube default use inner join 🤔 Could it be possible? Can I configure somehow the way/direction of the connection (left join, right join, 1-1, 1-many, many-many)?

And it could be possible that I did something wrong 🙈

 Thanks for all comments!

Zoli

@datacube

 

Networking and Social Connections

3 Replies

  • Ido_QBeeQ's avatar
    Ido_QBeeQ
    ETL
    05-22-2025

    Hey zoolee 

    Indeed relationships within the Elasticube are always INNER JOINs, so the above result set is expected. Meaning, the rows which have those date values(5/19 and 5/20) which existing in both sides of the join will appear.

    In order to achieve other join types to more complex SQL queries in general, one can create a custom SQL expression and using standard SQL, create any query you like.

    For more on creating custom SQL expressions see this link 

    or this article on how to create custom SQL tables for live models

    If you need any help with setting up these custom tables, or if you have other more complex requirements, please let me know,

    We're always here to help 

     

    Ido from QBeeQ

    QBeeQ - Gold Sisense Partner

    [email protected]

    Feel free to subscribe to our newsletter to stay up to date with the latest QBeeQ news!

     

     

  • DRay's avatar
    DRay
    Admin
    05-23-2025

    Hello zoolee ,

    I’m following up to see if the solution offered by Ido_QBeeQ worked for you.

    If so, please click the 'Accept as Solution' button on the appropriate post. That way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.

    Thank you.

  • gwolfe's avatar
    gwolfe
    ETL
    06-17-2025

    This is a pain point for us as well zoolee​ . We have been able to bypass this with a custom table like Ido_QBeeQ​ recommended. What we did was union blank fields so that the inner join won't filter out needed rows from table A if they don't exist on table B. 

    For example:

    Table A

    order # | Amount | Sales Rep ID
123        | $500        | x
456        | $200       |   y
789        |  $100       | NULL

    Table B

    Sales Rep ID | Name
    x                  | John
   y                  | Mark

    In the scenario if we tried to join table A and table B on sales rep ID we would lose order number 789.

    By adding null values to table B we are then able to join the null ID on order 789 and keep all of the data from our fact table. 

    SELECT
    SalesRepID,
    Name
FROM SalesRep

UNION

SELECT
    NULL AS SalesRepID
    NULL AS Name