Now That Is A Lotta Connections!

Question

I am building this engine to calculate expected future outcome and the calculated values depends on a lot of variables and tables.

I've condensed all the single value variables into a table, but condensing multiple data tables into a single table is not feasible, because each table holds different type of data. Therefore, each table is passed into the engine as an input source.

This gets a bit unwieldly as the number of input source types increases, see below.

I am wondering if anyone got experience with this and what are some of your best practices.

The most viable solution I can think of is putting the file path of each data source into a table as STRING. then pass the table in as a single source, and then load the individual data sources from inside the macro. I usually refrain from doing that because i don't like using the engine to manage I/O or data connections.

caltang · Answer

I can tell you from experience that having that many inputs will just spell disaster down the road...

What you suggested is a better alternative. Use file paths but go by Record ID. Within the Standard Macro, you can filter them out one by one and that is less taxing than this in terms of maintenance. Navigating the interface designer with that many inputs is a massive headache.

For other applications: I would take out some and put it on the workflow. Have standard macros at most with 5 inputs to maintain it well.

CoG · Answer

Other potential answer is to compress further with Union Tool, but first adding a new column [Source] that keeps track of which data belongs where (basically creating a table of tables with a lot of nulls). In the macro, you can then Filter by [Source] and use the Data Cleansing Tool to remove Null columns.

Alternative, which I think is what @Raj suggested: You can cast all columns as strings, rename columns with standardized naming system (e.g. "Column1", "Column2", etc.) while also building a Dynamic Rename Table (You can make a standard macro to handle this). Again add [Source] column (You could add this step to the standard macro from before with Text Box Tool), Union, and then your macro will have 2 inputs, Main Data & Column Names. Filter off Main Data by [Source] and then use Column Name Table to Dynamically rename back to original names.

Edit: I am attaching the "Standardize" Macro for you should you decide to go the second route I mentioned.

Standardize.yxmc

Raj · Answer

You are thinking right. 
first make them all in a single format and then within macro you can change the data type according to requirement.
It is not a good practice to pull this number of inputs.