Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Transpose every table in database

thughes
6 - Meteoroid

Hi all,

 

I'm hoping to transpose every table in a SQL Server database into three columns: table name, field name, & field value.  My end goal is to cross join the result set to itself on the field values (with grouping and summarization to reduce the size of the data set) in order to determine the match percentages between fields, thus informing other users how best to join tables.

 

I understand how to do the cross join and summarization, but setting up a batch macro to transpose each table into a unified result set has flummoxed me.  My thought was to use the INFORMATION_SCHEMA.TABLES view to retrieve a list of table names to use as a control parameter, but I don't understand how to use that list to change the data input.  Perhaps I'm on the completely wrong track.  See attached.

 

Thank you in advance,

Tim

 

7 REPLIES 7
rdoptis
11 - Bolide

Hi Tim,

 

You would use a combination of an input tool and batch macro for this. The rough order of operations would be:

 

1) Use standard input tool to query the "tables" table in your DB to get a list of the table names.

2) Feed this list of table names into a batch macro that looks like:

     Batch Crosstab Tables.png

I setup the query in the input tool with a select distinct * so the database would dedupe your data and shrink the amount of data brought back to Alteryx. 

 

3) In the interface settings for the macro, set the following as your data values will be of different types:

interface.png

 

 

4) Place this macro in your workflow like:

workflow.png

 

 

This should produce the data you want, but be warned, it will create some amazingly tall tables. You will have a row for every {Column domain count * column count * table}. Let me know if you have any other questions.

 

 

Thanks,

Ryan

 

 

 

PS. As soon as I saw your workflow name containing Alpo, I thought of Tableau's internal instance ;-)

thughes
6 - Meteoroid

Hi Ryan,

 

Thanks for your speedy response!  Your workflow makes a lot of sense; it's quite similar to the stored procedures I was writing (until I ran out of hardware to process them).

 

This is my first time using macros in Alteryx and I'm still struggling to figure out exactly how updated values are received into the SQL query.  My understanding is that the macro uses a data input as a template, and in order to build the rest of the macro said template either has to be a text input or an actual connection (i.e., not a placeholder query).  So then how do I use the action to pass new table names to the table in the FROM clause of the SQL query?

 

Would you be willing to post your workflow file so I can a look at the nuances?

 

Thanks so much for your help,

Tim

 

P.S. The use case for this is in fact Tableau's internal SQL Server database :)

rdoptis
11 - Bolide

Hi Tim,

 

My example has metadata within it from my testing which is confidential so I am unable to post the module, but I will explain further. 

 

There are different kinds of macro inputs - namely data inputs and control parameter inputs. With a data input (macro input) you have to define a template. With parameter (control parameter) inputs there is no template - these can be thought of as variables to inject in the macro configuration. Batch macros in general often leverage control parameters to alter what should modified between run in a batch. In our case the table name is what we want to change for each run within the batch. For each record being fed into the control parameter, it will run an loop of the batch macro.

 

 

From the control parameter, you would use an Action Tool to modify another tool's configuration with the control parameter. The action tool configuration should look similar to this:

action.png

 

In this case, the action tool is doing something of a "find and replace" of the string "TABLENAME" within the query, and subbing in each of the table names. 

 

Does this help clear things up?

 

 

Best regards,

Ryan

thughes
6 - Meteoroid

Ahhhhhhhhh.  I swear I tried using Orders instead of TABLENAME in my connection with the replace string value option.  But I don't think I clicked the connection string first, so obviously Alteryx wouldn't know what to replace.  #usererror

 

Thank you for your help, it works perfectly!  You are a quality human being.

 

-Tim

rdoptis
11 - Bolide

Thats the nicest thing iv heard all week! Cat Tongue

 

Glad to hear you got your workflow up and running.

 

 

Cheers,

Ryan

levonbjohnson
6 - Meteoroid

I am working on a similar process with an extremely large database (more for the purpose of creating a data inventory of tables and fields that meet certain parameters) and am hoping one of you can provide a little more clarity on how you were able to get the list of table names used in your Control Parameter tool. Tables are added somewhat regularly to the database, so I need a solution that will pull the current list of tables each time the workflow is run (and I cannot use a metadata table to get this because there are admin tables that I would like to include but are not listed in the metadata). Any help you can provide is appreciated!

thughes
6 - Meteoroid

I used the metadata table, so unfortunately I don't have a solution for you.  Best of luck.

Labels