Example: Using a SAS Code Transformation Template in a Job

< Day Day Up >

This example demonstrates how a user-written SAS code transformation template can be used in a job. This example is based on the PrintHittingStatistics template that is described in "Example: Creating a SAS Code Transformation Template" on page 120.

Preparation

Assume the following about the job in the current example:

A data warehouse project plan identified the need for a report that displays hitting statistics for baseball teams. The following display shows the kind of output that is desired.

Display 10.25: Example Hitting Report
The input for the report is a table that contains batting statistics for a baseball team. The columns in the source table are assumed to be similar to the columns shown in the following display.

Display 10.26: Contents of Table: TigersHitting2002
Metadata for the source table, a SAS data set called TigersHitting2002, is available in a current metadata repository.
The report will be produced by a SAS ETL Studio job, using the PrintHittingStatistics transformation template. The template has already been created as described in "Example: Creating a SAS Code Transformation Template" on page 120. Usage details for the template have been documented, as described in "Document Any Usage Details for the Template" on page 127.
Output for the report will be sent to the Output tab of the Process Designer window. The appropriate option must be set so that theOutput tab appears in the Process Designer window. For details, see "Process Designer Window" on page 105.
The main metadata repository is under change-management control.
You have selected a default SAS application server for SAS ETL Studio.

Start SAS ETL Studio and Open the Appropriate Metadata Profile

Perform the following steps to begin work in SAS ETL Studio:

Start SAS ETL Studio..
Open the appropriate metadata profile. For this example, the appropriate metadata profile would specify the project repository that will enable you to access the PrintHittingStatistics transformation template and the metadata for the required source, TigersHitting2002.

Check Out Any Metadata That Is Needed

To add a source or a target to a job, the metadata for the source or target must be defined and available in the Project tree. In the current example, assume that the metadata for the relevant source must be checked out. The following steps would be required:

On the SAS ETL Studio desktop, select the Inventory tree.
In the Inventory tree, open the Tables folder.
Select the source table that you want to add to the new job: TigersHitting2002.
Select Project Check Out from the menu bar. The metadata for this table will be checked out and will appear in the Project tree.

The next task is to create and populate the job.

Create and Populate the New Job

With the relevant source checked out in the Project tree, follow these steps to create a complete process flow diagram, from sources, through transformations, to targets.

From the SAS ETL Studio desktop, select Tools Process Designer from the menu bar. The New Job wizard is displayed.
Enter a name and description for the job. Type the name PrintHittingStats Job, press the TAB key, the enter the description Generates a report that prints hitting statistics for a baseball team.
Click . An empty job will open in the Process Designer window. The job has now been created and is ready to be populated with the PrintHittingStatistics transformation template and the source table, TigersHitting2002.
From the SAS ETL Studio desktop, click the Process tab to display the Process Library.
In the Process Library, open the UserDefined folder and the Reports subfolder.
Click, hold, and drag the PrintHittingStatistics transformation into the empty Process Designer window. Release the mouse button to display the template in the Process Designer window for the new job, as shown in the following display.

Display 10.27: PrintHittingStatistics Template, Unpopulated
From the SAS ETL Studio desktop, click the Project tab to display the Project tree. You will see the new job and the source table that you checked out, TigersHitting2002.
In the Project tree, click and drag the TigersHitting2002 table into the drop zone (dashed-line box) in the Process Designer window, then release the mouse button. The TigersHitting2002 table appears as a source in the new job.
Click and drag the Total_Sales_By_Employee table into the output drop zone in the Process Designer window. The target replaces the drop zone and a Loader transformation appears between the target and the SQL Join transformation template, as shown in the following display.

Display 10.28: PrintHittingStatistics Template, Populated

The job now contains a complete process flow diagram, from the source through the transformation. No target is required in the process flow diagram because output for the job will be sent to the Output tab of the Process Designer window.

The next task is to update the default metadata in the process flow diagram.

Update the Template as Necessary

The example job now contains a complete process flow diagram. The job is not ready to run, however. In order to produce the report that is shown in Display 10.25 on page 155, a title must be specified, a set of columns must be selected from the source, and the sum of the values in the HR column must be calculated. It is assumed that the steps for doing these tasks have been documented by the person who created the PrintHittingStatistics template.

Follow these steps to update the transformation in the process flow diagram:

In the Process Designer window, select the PrintHittingStatistics transformation, then select File Properties from the menu bar. A properties window is displayed.
Click the Options tab. The default options for the PrintHittingStatistics transformation are shown in the following display.

Display 10.29: Options Tab, PrintHittingStatistics Properties Window
In the Home runs field, enter the name of the source table column that contains home run values. In Display 10.26 on page 155, this is the HR column.
In the Report title field, enter a name for the report, such as Tigers Hitting Statistics 2002.
Click the Column Options tab. Use this tab to select columns from the source table that should appear in the report. For the report that is shown in Display 10.25 on page 155, select the columns Name, G, AB, HR, and RBI. When you are finished, the Column Options tab should look similar to the following display.

Display 10.30: Column Options Tab, PrintHittingStatistics Properties Window
When you are finished entering metadata, click to save your changes.

The job is now ready to run.

Run and Troubleshoot the Job

After the metadata for a job is complete, you must submit the job for execution in order to create targets on the file system.

With the job displayed in the Process Designer window, select Process Submit from the menu bar. SAS ETL Studio generates code for the job and submits the code to a SAS application server.
If a pop-up error message appears, or if you simply want to look at the log for the completed job, click the Log tab in the Process Designer window.
In the Log tab, scroll through the SAS log information that was generated during the execution of the job, as shown in the following display.

Display 10.31: Log Tab with Text from the Example Job

The code that was executed for the job is available in the Source Editor tab of the Process Designer window.
If you find errors in the source code for a step, select the corresponding transformation in the process flow diagram, then select File Properties from the menu bar. A properties window displays.
Correct the metadata and resubmit the job until there are no more errors.
After the job runs without error, save the job. Select File Save from the menu bar.

The next task is to verify that the job created the correct output.

Verify the Job's Outputs

After the job runs without error and has been saved, you should confirm that the target(s) contain the data you need, in the format that best communicates the purpose of the targets. In the current example, the output is sent to the Output tab of the Process Designer window. When you click that tab, a report similar to the one shown in Display 10.25 on page 155 should be displayed.

If the report needs to be improved, change the properties of the transformation that feeds data to the report. If the outputs are correct, you can check in the job.

Check In the Job

To check in a job in the Project tree:

In the Project tree, select the repository icon.
On the SAS ETL Studio desktop, select Project Check In Repository from the menu bar. All of the objects in the project repository are checked in to the change-managed repository.

< Day Day Up >