Batch / Iterative Macro Loop over List of Tables in DB & Apply Basic Profile Union Results

Question

Hi, Struggling a bit to create a Batch (or possibly Iterative) Macro to loop over a list of tables and apply a basic profile tool to each table.

End Goal: I'd like to be able to take a list of tables (schema.table_name) from an Oracle database which is a subset of the tables in a database, loop through each table and apply the Basic Profile Tool and ideally add the name of the table to the output, union the results from the Basic Profile Tool and then transpose the final output.  By analogy with the videos on input multiple excel sheets it should be straightforward but I'm not getting it.  Advice would be appreciated.

Input file:

TABLE_NAME    <<HEADER ROW

SCHEMA.TABLEFIRST   << LIST OF TABLES TO LOOP OVER

SCHEMA.TABLESECOND

SCHEMA.TABLETHIRD

Current version of the workflow along with the error indicating that the second and future files are skipped because it doesn't match the number of fields.  This is the template condition not being met but in this case the fields won't match until after the basic profile tool is run.  What am I doing wrong here?

Ideally, I'd like to take the tablename and add it as a column after each table is profiled and then union the results from *all* table profiles so I can transpose the output so the fields coming out of Basic Profile are column headers.

Your collective help in setting me straight would be appreciated.

CharlieS · Accepted Answer

Here's how I would go about this:

- The only input you need to this macro is the list of table names (as the control parameter).

- Use a regular Input tool so there aren't troubles with field schema differences like you've encountered with the Dynamic Input tool

- A second action will add the table name to all records using a Formula tool.

- Since this is a batch macro, all the results will be unioned so you can transform the data from there how you prefer.

willhaye · Accepted Answer

Using a combination of advice from @CharlieS and a former post from @LordNeilLord, I think I have a good integrated solution.  I think it can be improved if anyone has the patience. As seems to be usual it is incredibly simple when it finally worked.  Enclosed is the SQLServer version.  I have a note at the end for the modification necessary for the Oracle version. Here is a full summary.

Goal: Run the "Basic Profile" tool for multiple tables in a database based on a , union the output from all tables and then transpose the data fields so they are columns.

Workflow:

The filter is just to keep other tables in the schema out of the output.  Note the macro after the RecordID tool.  What goes into the macro is a two column list with ID (connected to the control parameter) and the table list.  Also, in the macro control, i had to select the groupby parameters to both be RecordID.

Batch Macro:

For the macro, I had to configure the dynamic input tool to point to the same database and selected a specific table as the example.  Note I didn't need to explicitly include the control parameter in this case.  I think trying to stuff it in *and* not putting the recordID outside of the macro was the key problems I was having.

NOTE: Oracle Database:  Use this command to find the full list of tables to start from:

SELECT table_name from all_tables where owner = 'USER_U_R_INTERESTED_IN'

Hope this helps others too.  This is a simple way to profile a subset of the tables in the database pretty quickly.

Thanks to the community in general and @CharlesS and @LordNeilLord for their direct or indirect contributions to my learning.

willhaye · Accepted Answer

... Better package export.  Looks like I forgot the macro in the above.  -Hayes