How to: Dynamic In-DB filtering without database writing permissions

[MatthiasSeil]

As you all might know, the Alteryx In-Database tools are a great way to process a large amount of data direct on the database layer without streaming it to your computer. It thus can save an incredible amount of loading time.

Customers however often come back to me and ask how to dynamically filter data from a database with some other information like data from a list/file on their computer without losing the performance of the In-DB functionality.

Alteryx usually solves this with the Data Stream In tool, where the users can bring in data from a different source into their In-DB workflow. On the database a temporary layer is created, and all data treated as In-DB. It can then be joined In-DB as a next step.

However, this procedure often requires specific writing permissions on the respective Database which business users hardly can obtain from their company.

In order to still be able to achieve the desired results Alteryx offers the possibility to use custom SQL code for querying the database.

So, in case you do not have the writing permissions you can simply create a simple SQL statement with the help of standard Alteryx tools like Formula, Summarize, etc. and then use this result for querying your DB with the Dynamic Input In-DB tool.

In my example here I have some data in my Database:

And I want to get the results only for these particular Order IDs:

You see that a simple “Inner Join” would quickly give me the required result.

As we cannot use the In-DB join in our scenario due to lacking write permissions from our company we simply need to adapt the SQL query for the entire table in a way which dynamically filters the table for all our target Order_IDs.

As a first step we create a formula achieving this first query part:

Concatenate it in a second step:

And then, third, combine it with the query for the entire table view (just add the “where” query):

Together with the connection name we can then use it together with the database connection name as an Input to the Dynamic Input In-DB tool:

Done!

Result:

Please also see attached Workflow for details!

[GirouxM]

Hi - first time questioner

Is there any documentation on how to correctly use the 'Connection' part of the Dynamic Input In-DB tool?

I see 'Demo' used a lot on various postings, but don't really know what that points to - or how it points to an actual database. 

I am using file based connections, stored on a network drive, e.g. \\server\dir1\dir2\db_connection.indbc, and not sure how to pass that into the Dynamic Input In-DB tool.

thanks,

mark

[PNau]

Mark, the above solution was great but I had the same confusion with the source for demo and the query, not sure if your query is still open, but this might help others.

Go to your In-DB Connection tool where you have successfully entered your Connection name and Table.  The first part is the source of the Connection and the second part is the beginning of the Query that we are sending to the Dynamic In-DB Tool.  You will need access to the DB, the file .indbc or a different DB file locally and for Alteryx InDB tools, I had to install an Oracle Driver to get to my Oracle DB.

Personally, I find entering the query in the InDB Filter Tool more efficient as this is not typical Join process, but I am sure I will learn benefits as I work with more database source files.