Working with Staging Transformations

What is a Staging Transformation?

A staging transformation generates a SAS program that loads raw data into staged tables. Before loading raw performance data into staged tables, the staging transformation performs the processing that is necessary to create a SAS program that converts the raw data into standardized, normalized values. For example, the staging transformation might perform the following functions:

provide code that supports the input of single files, directories, or databases
detect and handle duplicate data
normalize measurement units
standardize timestamps
generate computed columns from the input data

When a staging transformation is executed by a job in SAS IT Resource Management, the SAS program accesses the parameters that are specified in the staging transformation and the metadata for the staged table that it will populate. The SAS program uses this information to populate the staged table that can be used as input to an Aggregation transformation or other transformations. Every time a staging transformation is executed, any existing data in the staged table is removed, and the new data is loaded.

You can also modify a staging transformation to include additional staged tables that are supported by a given adapter. This feature enables the system to process data from various staged tables for a given data source via the same staging transformations and aggregations. For more information about aggregations, see Working with Aggregation Transformations.

SAS IT Resource Management provides a unique staging transformation for each adapter that it supports. The staging transformations that are supplied for each adapter are available in the ITRM Transformations folder of the Transformations tree view of SAS IT Resource Management, as shown in the following display.

Supplied Staging Transformations in ITRM Transformations Folder

Properties of Staging Transformations

Staging transformations specify the staging parameters that are pertinent for the adapter on which they are based. Because raw data sources differ in format, file structure, and type of data collected, the staging parameters that are necessary for each adapter vary as well. Staging parameters enable you to indicate how you want to handle certain types of data based on your needs. For example, you can specify how to handle duplicate or future data in a raw data source. Staging parameters can also have different default values based on the adapter.

To access the staging transformation properties, right-click a staging transformation in the process flow diagram and select Properties. A Properties dialog box appears and enables you to view the staging transformation properties and specifications for the staging code that the transformation generates.

Sample Properties Dialog Box for a DT Perf Sentry Staging Transformation

The Properties dialog box contains the following tabs:

The General tab displays information that identifies the staging transformation such as name and description.
The Staging Parameters tab displays the staging parameters that are pertinent for the adapter on which the staging transformation is based. The parameters that are available on this tab vary based on the requirements of each adapter. You can click the value field of a parameter to modify the value. If there is a defined set of values available for this parameter, a drop-down list appears and enables you to select a value. For more information about staging parameters, see Staging Parameters.

Note: If you modify the value of any parameter in the source code of a job, that modification is not updated in the SAS Metadata Repository, and the new value is not reflected on the Staging Parameters tab. However, you can save the modified version of the source code to the local file system.
The Options tab displays information about how to further customize and generate the code.
The Code tab enables you to manage the code that is generated for the transformation.
The Precode and Postcode tab enables you to specify that user-written code should be inserted at the beginning or end of the current job or transformation.
The Parameters tab enables you to manage prompts or prompt groups.
The Notes tab displays any notes or documents that are associated with the transformation.
The Extended Attributes tab displays the custom properties that are available for the transformation.

For more information about the tabs that are available from the Properties dialog box, see the Help for SAS IT Resource Management.

Add a Staging Transformation to a Staging Job

You can use the Adapter Setup wizard to create staging jobs automatically for a supplied adapter. The Adapter Setup wizard provides a convenient way to create staging jobs as well as other jobs and the components that are necessary to stage and summarize the IT resource data that an adapter loads. The wizard guides you through the process of specifying the staging parameters, and it creates the necessary staging transformation, staging job, and other jobs that are required for the adapter that you select. For more information about using the Adapter Setup wizard, see About the Adapter Setup Wizard.

You can also create a staging job manually by adding a staging transformation to a job. To do so, perform the following steps:

Open the job that is to contain the new staging transformation. The job shows on the Diagram tab of the Job Editor window.

Note: If you want to add a staging transformation to a new job, you must first create the job. For more information about how to create a job, see Create a Job.
In the Transformations tree of SAS IT Resource Management, expand the ITRM Transformations folder and locate the staging transformation for the adapter that you choose.
Drag and drop the staging transformation onto the Job Editor window. The staging transformation appears in the process flow diagram for the job.

Sample Job with a New Staging Transformation
Right-click the staging transformation and select Add New Staged Table to open the Staged Table wizard. This wizard enables you to specify the parameters for the staged tables that the staging transformation code populates. For more information about using the Staged Table wizard, see Create Staged Tables.

Note: A staging transformation can process output for multiple staged tables. To add more staged tables, repeat this step as needed.
Right-click the staging transformation and select Properties.
Use the Staging Parameters tab to specify the staging parameters for your data such as the location of the raw data, how to handle duplicate data, how to handle future data, and so on. You must specify all parameters that are marked with an asterisk (*) before the staging transformation is complete. When the transformation is complete and ready to run in a staging job, the staging transformation object is marked with a green check in the process flow diagram.

For more information about the staging parameters for each adapter, see Staging Parameters.

Sample Job with a Complete Staging Transformation and Staged Tables

Note: The location of the raw data input for the staging transformation is specified in the staging transformation properties. Thus, a staging transformation object does not have an input object in the process flow diagram (with the exception of the VMware vCenter staging transformation that has the data acquisition table as input).
If you are adding an ASG TMON2CIC or IBM SMF staging transformation, you must specify the associated spin library. Use the Spin Library tab (available only for these two staging transformations) to view and modify the parameters for the corresponding spin library. For more information about setting the spin library parameters, see Data Sources Supported by SAS IT Resource Management Adapters.
Run the staging job to generate the staging code, execute the code, and load the staged tables.

Note: You can also generate the staging code without executing it. To do so, click the Code tab of the Job Editor window. You can then select from the Code generation mode options on this tab to edit the code manually before executing it.
Click the Log tab of the Process Designer window to check the SAS log and confirm that there were no errors or warnings during processing.

After the staging job executes successfully, you can view the staged data in the staged tables. To do so, right-click a staged table in the process flow diagram and select Open.

Edit a Staging Transformation

To edit the parameters that a staging transformation uses to locate and stage raw data, perform the following steps:

In the IT Data Marts tree, double-click the job that contains the staging transformation that you want to edit.
Right-click the staging transformation in the process flow diagram and select Properties. A Properties dialog box displays and enables you to modify the various parameters that are specified for the staging transformation and the staging code that it generates.

Note: The name of a staging transformation should not contain double quotation marks.

For more information about staging transformation properties, see Properties of Staging Transformations.
Click OK to save your changes.

Delete a Staging Transformation

Deleting a staging transformation disassociates it permanently from the staged tables in the job.

CAUTION:

If you delete a staging transformation and then try to re-create or reconnect it to the staged tables, the staged tables might lose computed columns or experience other errors.

For best results, make a copy of the staging job before deleting or modifying the staging transformation or any other components that you are not certain that you want to lose.

To delete a staging transformation from a job, perform the following steps:

In the IT Data Marts tree, double-click the job that contains the staging transformation that you want to delete.
Right-click the staging transformation in the process flow diagram and select Delete. The staging transformation object is deleted from the job. However, the staged tables that were associated with the staging transformation remain in the job.
Select FileSave to save your changes, or close the job and select Yes when prompted to save your changes.