First design in Alteryx - Multiple Tables

[michael_franz]

Hello,
I need to build out the work flow with 12 tables. Now sure the best way to start. We are using the product specifically for this task..

I have included how I built it in Tableau. Some of the reason for alteryx is the amount of records and calculation that need to happen and not running against the live database. There are no multiple joins.

I am looking for the best way to start this rolling.



 

[kane_glendenning · Accepted solution]

Hi Michael,

There are of course several ways to do this and I'm not sure if there is a BP. So... I'll tell you how I would do it.

If you have the In-DB tools enabled, then you can just use a Connect In-DB tool to connect to each table and a Join In-DB tool selecting Left-Outer Join as such:



Without the In-DB tools each time you Join, you'll have a Union tool afterwards to Union the output from the L & J outputs like in the below screenshot. 



Once you have layed out your 12 tables and selected the field that each join is performed on, you can put all this in a container and minimise it if you like as you won't need to reconfigure these again. Then the start of your flow will look something like this:


Otherwise you can use the SQL Editor on 1 input tool, however that then involves a script rather than point & click.

Kane

[michael_franz]

Yes, I have the In-DB tool, enabled. What is the technical difference between the two???
 

• At the end of the day, we will want to add additional calculation to the set.
• Output it to a sql server and run tableau off of it.
• The job will probably run overnight.

I was also trying it one different way and that was in the visual query builder. What is the difference in the second method above and the visual Query Builder??

And one final question would be when I am doing a join, I need the primary keys selectedand then just drop them off at the end with the select tool?

THANK YOU!


 

[kane_glendenning]

Hi Michael,

The In-DB tools will perform the jhoins and so forth in your database and bring out the information that is required, whereas the normal tools will bring each of the tables out, perform the joins and then drop the information not required. The In-DB tools should be better performance wise. I would advise using these if you can, after your last join, use a data stream out tool to bring the results into the normal workflow and perform your analytics. You will then be able to perform your calculation and then use an output tool to write to the database.

You can do it with the visual query builder as well if you like, it will end up the same result as using the SQL Editor, and should perform a lot of the calculations in the DB rather than on the machine the workflow is running.

Lastly, yes, you will use the primary keys to join the tables together and one select tool at the end of all the joins will allow you to drop them all.

Kane

[cbridges]

I wish I could see your 2nd and 3rd screenshots (no preview or ability to view image with right-click) because I've been curious about this too... sort of replicating the way Tableau considers multiple tables in an Access database as one data source.