Input Data vs. In-DB Connection Options

[elsastark]

Hello Community!

We have been researching the best times to use In-Database tools vs. a normal Input Data tool. 

One of the main differences is the connection options

In-Database

• Connection String - User can enter or build a connection string 
• Connection File - The tool can reference a pre-built connection file that contains the connection string

Input Data Tool

• Connection String - User can enter or build a connection string 
• Gallery Connection - The tool can reference a connection string stored on Alteryx Gallery , most likely stored on a shared network drive

We aren't wanting our users to use the Connection String approach since that will embed this information in the workflow, meaning that when that credential or connection information changes, this workflow would have to be touched and remigrated through our multiple Gallery environments. 

This is causing us to suggest In-Database if they want their connection information stored in a file that they can edit themselves or Input Data tools if they want it to be managed on Gallery by one of our admins. I feel like this isn't the real reason you should choose between these two paths but we are doing this since both options don't support all connection options, as far as we can tell. 

My question for the group is:

• Can In-Database support Gallery Connections? Where it would reference the connection information stored on Gallery. If not, why?
• Can the Input Data tool support connection files? Where it would reference a pre-built connection file that contains the connection string, most likely stored on a shared network drive. If not, why?

[gabrielvilella]

In-Database connections cannot use Gallery Data Connections. Input Data does not support connection files. 

Choosing between In-Database and the regular Input Data basically comes down performance. Using In-Db you will send a query to the database and the data is processed there, not on your local machine. The regular Input Data will download all the records to your local machine and it will use the resources of your machine to process the data. 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/FAQ-How-Do-the-In-Database-tools-Wo... 

[elsastark]

@gabrielvilella - I think where we are struggling is we are having to suggest to teams to use In-Database tools, not because of the way the data is processed but because of the fact that it supports connection files. Hopefully, as the Data Connection Manager feature improves there will be a more secure way for teams to manage their own data connections without an Alteryx Gallery administrator being involved. Currently, we can't use that solution as we need more security around how people share their data connections with each other. 

[gabrielvilella]

DCM is particular for each user. It is not possible to share a data connection or credential with someone else. The sharing option that you have in DCM is just to allow other users to use a connection from an Analytic app. But when you share the connection, the other use won't see this listed as an option in their DCM area. 

[elsastark]

@gabrielvilella We are ultimately trying to find a solution where our users can pick between having our group (Alteryx Gallery admins) manage their data connections or their team manage them. These teams want to have a locked-down shared network folder where they put this data connection file that the workflows point to so that they can update themselves.

We also have certain teams that do not want that information stored on our US-based controller as we have regional data concerns. 

That is where we are recommending that they use In-Database connections specifically because of the connection file option and I am trying to explore if there are any other options as they would have to change all their existing workflows to essentially have a Connect In-DB tool followed by a Data Stream Out tool (which is replacing their standard Input Data tool) which defeats the whole performance purpose of In-Database tools. 

It just feels a bit complex of an answer just to support connection files - but like you said above - I don't think there is a way to support them using an Input Data tool that can reference a connection file. 

[simonaubert_bd]

Hello @elsastark 

Some points to consider : 
-in-db connection is for in-db workflow, input data is for in-memory workflow. Always prefer in-db workflows when possible : it means not using Alteryx Server resources that are really expensive And, -the best part : it means the data stays in base, meaning faster workflow (sometimes by a 10,100 factor). On the other side, you have fewer tools on in-db. We have a lot of ideas to promote new ones ;) 
-You can go from in-db to in-memory (using only a in db alias) but you can't go from in-memory to in db without an in db alias.

.Can In-Database support Gallery Connections? Short answer : no but you can't still use indb for a gallery workflow. you can also vote for this idea.

Best regards,

Simon