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!
The Product Idea boards have gotten an update to better integrate them within our Product team's idea cycle! However this update does have a few unique behaviors, if you have any questions about them check out our FAQ.

Alteryx Designer Desktop Ideas

Share your Designer Desktop product ideas - we're listening!
Submitting an Idea?

Be sure to review our Idea Submission Guidelines for more information!

Submission Guidelines

Parent Child Insert Improvements / Improved Key Generation Techniques

Hello Dev Gurus - 

 

Populating a parent / child relationship into a RDBMS in Alteryx is a lot harder than it should be.  The hacks workflows must go through to do anything other than populating single tables makes Alteryx extremely cumbersome to use for any non-trivial ETL process; especially if you live in an existing database environment using database housed, sequentially generated primary key values.

 

Consider a workflow that generates data to the point where you create a cascading set of rows:

 

  • One super parent.  Table A. 
  • Two children rows in Table B. 
  • Two children rows to rows in Table B, into Table C.

In Alteryx, the only way I have found to do this is by altering my tables to have UUID columns, populating them and then using dynamic selects afterwards, i.e.: 

 

  • Populate my row bound for table A with a UUID column I generate in stream.  
  • Make a new insert into Table A. 
  • Perform dynamic selects from Table A where GUID == current row GUID.
  • Get the primary key back out from that data set. 
  • Add the primary key to the data bound for Table B. 
  • Generate a UUID column for the data bound for Table B. 
  • Repeat. . . . 

Not only this, but this 'technique' mandates you either use a block until done and a batch macro to insure your data going to Table A finishes up, or block until done using WaitASecond tool for the lazy among us (like me).  It is amazingly clunky.  

 

In my coding days, there were a variety of ORM tools that would let you do an insert and then immediately make back available to you the primary key that was generated, whether the key was created via database or by the code library itself.  If Hibernate 3.0 released 12 years ago can make this work, I'm pretty sure that the people creating Alteryx tomorrow can do the same thing one way or the other.  

 

Basically what we need is an output-insert tool that has a data stream back out that comes with the data stream and the fancy new primary key that is to the row.  Easy mode is to have it only operate for tables that have sequentially generated primary key values.  Alteryx 2022.1 mode is to give the user some key generation technique options at tool configuration time.  

 

Thank you for listening to my Ted Talk regarding improvements to the output tool to make ETL operations more efficient.  

 

 

2 Comments
KylieF
Alteryx Community Team
Alteryx Community Team

Thank you for your feedback and such a thorough walk through of the issue and ask!

 

I've reviewed your idea and it looks good for product once the necessary like criteria has been met. Please be sure to check out our Submission Guidelines as well as like any other ideas that would be most helpful to your Alteryx experience.

AlteryxCommunityTeam
Alteryx Community Team
Alteryx Community Team
Status changed to: Accepting Votes