Code Storage Options for SAS DI Studio Custom Transformations
SAS Data Integration Studio provides a comprehensive set of transformations for performing the ETL processes necessary to build and maintain a data warehouse. However with the plethora of tasks required it is not unlikely that a custom transformation will be required in some situations.
Custom transformations are registered in SAS metadata and bear a number of similarities to SAS Stored Processes in that they provide a means to specify input parameters which are then passed to underlying SAS code for execution.
When defining a custom transformation there is a choice to be made concerning the location of the SAS code itself, either directly within the metadata of the custom transformation itself:
Or externally as a SAS macro definition stored in an autocall library:
In which case the custom transform code would simply contain a call to the macro:
There are pros and cons to both approaches, and here we will discuss just a few related to change control.
If change management is enforced in your metadata repository then custom transformations are subject to the same change control as all your other DI Studio related metadata like jobs and tables. As such change control is effectively in place for your custom transformation code as the history of the metadata item will capture the details of any changes made. This also means that in order to update the transformation it must be checked out whilst no job that uses it is in use. Any deployed jobs which use the transformation will need to be re-deployed following the update to the transformation.
Storing the code as an external macro bypasses any change control in place on the custom transformation as the code can be edited outside of the DI Studio environment, however the macro is then available for use in other applications such as SAS Enterprise Guide. Deployed jobs which use the macro should only need to be re-deployed if the macro call itself has changed.
Whether these are pros or cons depend on your approach to change control.