Alteryx Migration (ECC to SAP) Workflows Converted to SQL Queries

[sharfor]

Hi,  we've got a dilemma where by we would like to convert the Alteryx workflows we created for ETL of migrating data in ECC (source system) to S4/Hana (target system).into SQL queries, which we could then leverage for merging archived data (untransformed) with S4/Hana data (transformed data) for reports using ad-hoc transformed data from the archive and already transformed data from target systems as needed.

The idea being that we could leverage the time/effort spent in creating Alteryx ETL workflows for transforming migrated data for the unmigrated archive data by presenting the SQL queries to workers who are not familiar with Alteryx but are familiar with SQL.

Does anyone know if this can be done with regular Alteryx tools such as In Database tools and possibly still use the ACS/Alteryx SAP tools for data retrieval (log-on and table)? 

Also the Alteryx workflows which transform the migrating data are at times quite complex.  Is there any way to mitigate this complexity and still have SQL queries that work to transform the data as the Alteryx workflows did?

[danilang]

Hi @sharfor 

In theory, any transformation that can be performed in Alteryx can be analyzed and translated into pure SQL and vice versa.  In practice, the translation process is usually quite complex.   This is because there isn't a one-to-one mapping between Alteryx tools and SQL functions.  For instance the SQL statement 

Select *
from Table1 t1
Join Table 2 t2
   on Upper(t1.ID)=Upper(t2.ID)

is represented Alteryx by 

where the two formula tools convert the ids to to upper case before the join tool.

More complex transformations may involve the the creation of temp tables on the sql side to accomplish what is a relatively simple workflow in Alteryx.  As such, there are no existing tools or packages that will automatically translate from one format to another.   

One technique you may want to investigate is reading the data out of ECC and pushing it directly to corresponding raw tables in a SQL database.  Then you can use In-db tools on the raw tables.  By adding Dynamic Output in-DB tools you can return the corresponding SQL query at any point in the transformation.

Dan