Connect to multiple Databases from different servers without Linked Servers

[EmilBras]

Is het it possible to connect to multiple Database connection on different servers without using linked servers?

[RPM]

If I'm understanding your question correctly, I don't see why not. Just drop in multiple input tools, each configured to connect to a different server and do your blending in Alteryx. 

[dataMack]

When you say ' different servers' and 'linked servers' are your referring to the servers the databases are running on (ex. you have 2 different Oracle servers with different databases you're trying to bring together), or the Alteryx servers (ex. you have 2 Alteryx server environments)?

If the former, there shouldn't be anything special you need to do.  Just setup 2 different input connections to each of your database servers and then stream the data into Alteryx to blend.

[EmilBras]

I have attached 2 screen shots in a word document.

I think it should be also possible with to input connectors...

Both a oracle servers, databases

[KaneG]

Hi Emil,

To break down what you are trying to do... the way I understand it, is that you are trying to connect to 2 different Oracle servers and join them together in Database.

What you are trying to do does not work, as the join needs somewherre to execute, and because you are using In-DB tools, one database does not have the information needed from the other DB, and vice-versa. You can't push that process In-DB, as it is not defined as to which DB server everything is going to run on. You can use a DataStream Out connector for each DB and then do the Join in memory on your machine or you can stream one DB into the other (I don't recommend that....). But, to think about it logically, where do you want this join to execute?

Kane

[EmilBras]

This is the flow i have made...

I don't understand what you mean with: Where do you want this join tot execute...

[KaneG · Accepted solution]

Hi Emil,

If you were to use standard tools in Alteryx then the workflow would execute in memory or using a swap file on your HDD, however you are using In-DB tools which means that those tools execute in the database where the data is.

So the data for the tools below marked in Box 1 sits in DB: DWH_P

The data for the tools in Box 2 sits in DB: DOKO_2

By using an In-DB tool to try and join these 2 sets of data together you are not defining where the tool will execute, an In-DB join has to happen in the database, but your data is spread across 2 databases, and so you would either have to stream the data from DOKO_2 into DWH_P and then do all the work in DWH_P or vice versa.

Another way to explain why this won't work is that when you press Run, all the tools connected with the blue In-DB connectors are bundled up and turned into a script, that script is then executed on the Database. It won't work if there is 2 Databases, as 2 scripts would need to be created.

The way to do this would be as below, where seperate scripts are sent to each DB, and the result is streamed in to Alteryx with the DataStream Out connector. Then the Join can be performed in Alteryx.

[EmilBras]

KaneG - Thank you for the reaction and this solution just works fine...