Coffing Data Warehousing Software

The Query That Broke the Camel’s Back: How to Perform a Cross-System Join

Tera-Tom here! You probably think that the query that breaks the camel’s back is a cartesian join or even worse – a cartesian product join because maybe someone forgot the table aliases in the join condition.  And it is not someone trying to get a row from the left table by using a primary key. It is definitely not a cross join, a left outer join or any outer join at all. All of the above may make your result set extra-large, but the future that is too difficult to bear is a join across different systems.  Some call them cross-system joins while others call it a federated query. We call it “the future” and with Nexus, you can call it “easy and effective”.

Can you imagine how much of a competitive advantage a company could get if they could access and join data from different platforms?

There are two standard ways to perform a cross-system join.

  1. Move all of the tables to a single system temporarily and join the data.
  2. Use software to query each table separately and join it on the local PC.

The problem with solution number one is that when a user moves data from one system to another the actual data travels from the source system to the user’s PC and onto the target system. If the data is large, this can saturate a local network.  

You don’t try and land a 747 on a country road unless you are extremely desperate. And of course, that road is going to be completely shut down for some time. Likewise, the problem with moving tables to a single system isn’t just that it’s a massive undertaking but that you lose control over your local network, which you share with many of your co-workers.

The problem with solution two is that if one or more of the tables is extremely large you can also saturate the local network. Even more importantly, it can take forever for the join to take place. Your PC is designed to query big data, not process big data.

How to perform a cross-system join with Nexus

The solution is the Nexus client and the NexusCore Server. These two pieces of software work together in order for any user to be able to move data between systems, schedule long-running queries, do compare and synchronizations between systems, and perform cross-system joins (federated queries).  

The NexusCore Server is a server with a lot of memory on a high-bandwidth network that allows the user to run cross-system joins as if the user was querying from the server. It can do everything the Nexus can do, but it has additional logic that impersonates the user’s credentials and runs the job for the user directly from the NexusCore Server. The server then sends the final answer set to the user.

The fact that a company can have many NexusCore Servers and place them strategically where they make sense completely opens up cross-system joins and data movement for anyone needing to access any data at any time.

We have made Nexus so easy to use that almost anyone can create cross-system joins in minutes. They can schedule them to run on the NexusCore Server and when the cross-system join data has been joined on the server the answer set is returned to the user. The Nexus and NexusCore Server were designed for this capability.  

There is no longer a reason not to do cross-system joins or let users move data from one system into their sandbox on another system. No data goes over the local network except a confirmation message that the data has been moved or the answer set of a cross-system join.  

Look at how easy Nexus joins tables from different systems together. Nexus will automatically convert the table structures and data types between each system, move the data to the server, and join the data.

With Nexus and NexusCore Servers, almost anyone can create cross-system joins. The NexusCore Server eliminates any data moving over the local network, except the final answer set. Now, there aren’t any queries that will break the camel’s back!  

Please schedule a demo if you would like to learn more.