Iterative Macro to Replace String Value in SQL of Connect In-DB Input Tools

Question

Good Morning Community,

I have an automated workflow that takes snapshot of various pieces of sales data from Teradata for the current date, merges them all together, then writes them to Tableau Server and Teradata user DB table. The end result is a table that gives a snapshot of every customer account (current amounts outstanding, amounts past due, ect) for that day. Each day, this workflow generates roughly 50,000 records.

I recently had to make a slight tweak to the queries inside each of the Connect In-DB tools and now need a way to back fill the rest of the data (roughly 4 months worth). My initial thought was to create an iterative macro that would read a text table containing all the dates i need to run the query for, and replace where i reference CURRENT_DATE in the Connect In-DB queires with those dates on each iteration.  The trouble is, i cannot seem to find the correct way to configure the tools to do so. I tried to convert all of the Connect In-DB tools to dynamic inputs and go that route, but since i am using some ranking functions, and utilizing TD_DAY_OF_WEEK in these queries, the workflow would error out.

Each of the In-DB connections circled below is a query that, among other things, filters the data by utilizing CURRENT_DATE and i essentially need to replace the CURRENT_DATE with one of the dates i need to back fill my table on each iteration. It is also worth noting that some of these SQL queries are fairly complex and utilize unions and call CURRENT_DATE multiple times in each query (luckily ever instance CURRENT_DATE is used is an instance that needs to be replaced with an iterative date) in order to capture transaction categories, ect.

1) Is this even possible?

2) If so, any tips or wisdom on how to go about configuration?

Thanks,

Steve

JoeS · Accepted Answer

Hi @Swest4190

I think you need to create a batch macro rather than an iterative macro.

If you create a control parameter that contains CURRENT_DATE, then using an action on each Connect In-DB replace the word "CURRENT_DATE" this will replace all instances in your query.

Then in a workflow outside of the batch macro pass in data as the CURRENT_DATE, with each row representing each date you need the instance to run.

As a note, it will run them in the order the data is presenting, so if you do need to run them chronologically, you can make sure the data is sorted that way before going into the control parameter.

danilang · Accepted Answer

Hi @Swest4190

If the macro runs correctly on its own but doesn't in a workflow, check these

1. Is the control parameter in the main workflow the correct type and format.  I notice that your date field is actually a string.

2. Do the Action tools in the Macro do what you think they do.  If you turn on Show all Macro Messages in the Workflow Configuration Runtime tab, you should be able to see the actual Sql that is executed.  Another option here is the run the macro in debug mode from the Interface designer screen.

For the 2 final messages

Info: Historical Run (5): 1 iterations were run (ended by a downstream tool)
Error: Historical Run (5): The output connection "Output" was not valid

These are caused by the fact the output of the macro is not connected to anything.  Connect a browse tool to the output and it will complete.

Dan