Why are adhoc Cross Database Joins Not Supported?

[Korg95]

Problem

Cross database joins are not supported in cube without using rollup join. This means pulling and caching data within cube which might not be realistic for larger volumes of data.

I understand the issue at a fundamental level since I have seen application implementations of cross database joins go horribly for performance due to dealing with sorts across databases and inner joins that complicate the sort pagination and inflate what you pull across and join in memory.

Having all the data to join co-located or a linked server from there is a better approach but sometimes when data is in a different inaccessible location to the database but available to cube it would be great to have to unlock agility whilst you iterate to the better alternative.

In that sense I wanted to see if this conversation came up already to be linked to it to learn more since I can't find anything or have that conversation here.

[igorlukanin]

Hi @Korg95, thank for the question!

As you've mentioned, cross-database joins are generally quite tricky to implement and there are tools such as query engines that focus specifically on providing that feature. Cube can connect to many popular query engines and other types of data sources that have cross-database join functionality. So, it's a deliberate design decision not to re-implement that.

So, if you need universal cross database joining functionality with Cube, just put Cube downstream of a query engine like Trino. And rollup joins solve for some simpler use cases. I hope this explains it.