One of the most powerful advantages of the TX DWA software is how it uses project metadata to "remember" all of the objects in a project and their relations to one another. Another of TX DWA's strengths is its ability to incorporate customized SQL code wherever the native functions are not sufficient to accomplish a task.
Through the use of script parameters, both of these advantages can be combined.
What is a script parameter?
A script parameter creates a link between the script and a TX DWA object. This link has two primary benefits. One benefit is that the link includes parameterized code in data lineage and impact analysis, and enables the application to automatically manage some object dependencies.
The other main benefit is that the the parameter replaces a hard-coded reference in the script with a dynamic reference to the linked object. This means that the object can be renamed or modified and the script will adapt to the changes instead of breaking.
Please note that a TX DWA script parameter is not the same thing as a user defined function or stored procedure parameter, where certain values are passed into the function or procedure when it is called. Since procedures and functions are examples of custom code, they can both have TX DWA parameters to help with metadata management, as well as the more traditional kind of parameter.
Creating a parameter
TX DWA offers the user the ability to enter custom code by opening a new window. This window contains three main sections:
- A text box for your custom code
- A list co-located SQL objects and project variables
- A list of all current parameters
To add a parameter to a script, drag in the object from the object list into the code input field:
The object's name will then appear in the code text box, and the parameter will appear in the parameter list in the bottom left:
Note that if you create a view by dragging a table onto the the view node, the view script will be automatically parameterized.
A script parameter has three main parts:
- Name value
- Object Name/Value
- Variant type
TX DWA uses this data to connect a parameterized script to its associated object or objects.
The Name value is the value TX DWA will look for in the code that indicates where the reference to the parameter object should appear. This name value is arbitrary, but the default value is the name of the object as it appears in the object and variable list.
The Object Name/Value is the current name of the object the parameter refers to. This value cannot be directly controlled through the custom code window. However, if the object's name changes, it will automatically be updated when the window is reopened.
Lastly, each parameter has a variant type. This is mostly important when dealing with TX DWA table objects. Using the Variant dropdown allows the user to select whether the table parameter should refer to the raw table, the valid table, or the table's transformation view. Note that the default value for table parameters is the raw table.
Mapping or renaming parameters
Once you add a parameter to a piece of custom code, TX DWA will automatically replace any instance of the Name value in the custom code with a complete reference to the parameter object when the code is run. This replacement action occurs whether the Name value in the code was created by inserting that particular parameter or not. The impact of this is that you can only have one parameter using a given Name value at a time in a given script.
In practical terms, this only becomes a consideration if you create a new parameter with the same Name value as an existing parameter. In that case, you need to tell TX DWA whether you are creating a new reference to an existing parameter, or whether you are trying to create an entirely new parameter. You make this choice through the a dialog box like the one shown here:
If you choose Map, TX DWA will simply insert the Name value into your code, which will be picked up by by the existing parameter that uses that Name value during execution. If you chose Rename, you will be prompted to create a new name value for your new parameter. Remember that this name value is arbitrary, and can be anything you like provided that it is not used by any other parameter in the script.
A common time you will need to use this function is when you are joining two tables together in your code. Often, the values you join on will have the same column name in both tables. If you are confident that the column names will not change, or will be changed together to the same value if a change is made, then you can probably get away with simply using Map.
If it's possible that the name of one column could change and the other stay the same, it may be necessary to create a separate parameter for each column using Rename.
Viewing your parameters
As mentioned before, parameters in your code will be replaced with their Object Name/Value when the code is executed. However, this can make it hard to tell what exactly your code will look like when it is run. Fortunately, there is a way to "preview" the code, which can be very helpful when checking for syntax errors.
This will do several things. It will turn the code text box gray and prevent any changes from being made. It will also replace every instance of a parameter Name with a complete reference to the object referenced by the parameter. The reference will appear as [SchemaName].[ObjectName], or simply [ObjectName] if the object cannot have a schema, e.g. a reference to a table column.
By using View Translation, we can understand a common pitfall of using parameters in a script: aliases. In the example below, we've created a view that joins two tables together. The primary key of MyTable shares the same name with its foreign key counterpart in MyTable2, so we have created two different parameters to keep track of the two different fields. However, the code as written here will not deploy:
The reason becomes obvious when we show the translation for the code by toggling the Show Translation radio button:
Although the parameters make the field names look different, they will be resolved when the code is deployed. Since the parameters will resolve to the same field name, an "ambiguous column name" error will result. To avoid this problem, we can simply alias the two column names:
If you no longer want to use a parameter in your code, best practice in most cases is to remove it. Th reason for this is that leaving parameters in place could potentially result in inaccurate data lineage, impact analysis, and dependency detection for that script.
There are two ways to remove a parameter: manually, and automatically.
To manually remove a parameter, click the box on the far left of the parameter's list entry. This will highlight the parameter, and put an arrow in the left-hand box, as shown here:
Once you have done this, you can remove the parameter by hitting Delete on your keyboard. This is the easiest way to remove a parameter that is still in use.
TX DWA will help you to remove unneeded parameters as well. When you close a custom script window, the application will check your code to see if your parameters' Name value is present. If all Name references have been removed from your script, TX DWA will offer to remove the unused parameters automatically using this dialog window:
If you want to leave your unused parameters in place, select No. To remove, them, select Yes.
Limitations of script parameters
TX DWA can only create parameters for objects that have been created through the TX DWA itself. This is because the parameter function uses TX DWA metadata to "see" other objects. This means any objects created outside of the application are essentially invisible.
Note also that parameters added to script actions will not be taken into account when TX DWA is detecting dependencies during execution. This is because TX DWA does not know if you are updating the objects referenced in the parameter or reading from them.
In order to insure that all tables that your script depends on are loaded before the script itself runs, you must manually configure table dependencies. Be sure to avoid creating circular references in this process.