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

zoolee

Posted 1 year ago·Last reply 1 year ago

3 comments

Garrett Wolfe

·1 year ago

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

DRay

·1 year ago

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.

Ido Darnell

·1 year ago · Edited

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

info@qbeeq.io

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