General Tasks for Jobs | SAS 9.1.3 ETL Studio: Users Guide

< Day Day Up >

Creating and Running Jobs

Here is a summary of the main tasks for creating and running jobs. For examples that illustrates all of these tasks, see Chapter 10, "Loading Warehouse Data Stores," on page 131.

Prerequisites

It is easier to create a job if metadata for the sources and targets in the job are created first. For details about these tasks, see Chapter 7, "Specifying the Inputs to Warehouse Data Stores," on page 71 and Chapter 8, "Specifying Warehouse Data Stores," on page 89.

Check Out Any Metadata That Is Needed

You must check out the metadata for any existing sources and targets that you want to add to a job.

On the SAS ETL Studio desktop, select the Inventory tree.
In the Inventory tree, open the Tables folder.
Select all source tables and target tables that you want to add to the new job, then select Project Check Out. The metadata for these tables 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 sources and targets checked out in the Project tree, follow these steps to create and populate a new job. To populate a job, you will 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 displays. (You can use this wizard to create an empty job, into which you can drag and drop tables and transformations. That is the approach that is described here.)
Enter a name for the job and click .
An empty job will open in the Process Designer window.
Add metadata for sources, targets, and transformations as needed. The goal is to create a complete process flow diagram, from sources, through transformations, to targets. Drag and drop transformation templates from the Process Library tree. Drag and drop tables from the Inventory tree or from another tree in the tree view. If you try to drop an object in a zone where it is invalid, an error message will be written to the Status bar at the bottom of the SAS ETL Studio desktop.

As you add sources, targets, and transformations to a process flow diagram, SAS ETL Studio automatically maps source columns to target columns. Depending on the nature of the job, you might or might not need to update the automatic column mappings or the other default metadata in a job.

The next task is to view or update the job, as needed.

View or Update the Job as Needed

The following steps describe a general approach for viewing or updating the default metadata in a job. The specific updates will vary according to the sources, targets, and transformations in a job and the purpose of the job. The examples in Chapter 10, "Loading Warehouse Data Stores," on page 131 describe two scenarios in which a few, specific updates are needed to the automatic column mappings and the other default metadata in a job.

In the Process Designer window, select the first source in the flow, then select File Properties from the menu bar. A properties window displays.
Click the Columns tab to confirm that the needed columns are present. Add, delete, or replace columns as necessary. Repeat these steps for each source and target in the job, as needed. For details about updating column metadata, click the button on the Columns tab. See also "Updating Column and Mapping Metadata" on page 119.
In the Process Designer window, select the first transformation in the flow, then select File Properties from the menu bar. A properties window displays.
Update the transformation as necessary to achieve the purpose of the job. Be sure to display the Mapping tab for the transformation to be sure that data flows correctly through the transformation. As needed, repeat these steps for each transformation in the job, working from working in a source-to-target direction. For details about updating mapping metadata, click the Help button on the Mapping tab. See also "Updating Column and Mapping Metadata" on page 119.

When all metadata in the job is correct, the next task is to run the job.

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. The server executes the code. A pop-up window is displayed to indicate that the job is running.
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. (The code that was executed for the job is available in the Source Code tab of the Process Designer window. The source code is continuously updated as you make changes to the job, and it is checked and updated as necessary when you submit the job.)
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 targets contain the data you need, in the format that best communicates the purpose of the targets.

To view the data for a target in the job's process flow diagram, select the desired target, then select View View Data from the menu bar. The data in the target is displayed. Confirm that the correct data is displayed and that the data is correctly formatted for the purpose of the target.

If a target needs to be improved, change the properties of that target or the transformations that feed data to that target. If the outputs are correct, and you are working in a change-managed repository, you can check in the job.

Check In the Job

Perform these steps 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.

Working under Change-Management Control

Unless your user profile includes administrative privileges, you will be working under change management control. For a general description of how change management affects user tasks in SAS ETL Studio, see "Working with Change Management" on page 64.

When working with jobs, the main impacts of change management are as follows:

To update an existing job, you must check out the job.
When you check out a job, the metadata that is associated with the job will be checked out and will appear in the Project tree. The metadata that will be checked out includes the metadata object for the job as a whole and the metadata objects for any sources and targets that have been added to the job.
You must check out any existing sources and targets that you want to add to a job.
Metadata for new objects such as jobs, sources, and targets is added to the Project tree. At some point, you must check in new objects to the change-managed repository.

Using the New Job Wizard

See "New Job Wizard" on page 103.

Using Source or Target Designers to Create Jobs

Most source designers and target designers do not create jobs, but some do. The External File source designer and the Cube Designer create and execute jobs. The metadata for the job is saved so that you can run it as desired—to refresh the data in the target, for example.

For details about using the External File source designer, see "Example: Extracting Information from a Flat File" on page 78.

For details about using the Cube Designer, see "Example: Building a Cube from a Star Schema" on page 164.

Creating Jobs That Retrieve User-Written Code

When you create a job, SAS ETL Studio will generate code for that job unless you specify otherwise. This generated code will often suffice, but in some cases, you might want to specify user-written code for a whole job or for transformations in a job.

In order to track the jobs in a data warehouse, it is best to capture as much metadata as possible about a job, even if the job is handled by user-written code. Accordingly, a good way to specify user-written code for a job is to use SAS ETL Studio wizards to create a job as usual, then update the properties for the whole job or for transformations within the job so that the metadata specifies the location of user-written code.

The general steps for doing this are as follows:

Use SAS ETL Studio wizards to create a job.
Display the properties window for the job or for a transformation within a job. Follow the instructions in "Updating the Basic Metadata for a Job" on page 117 or "Updating the Metadata for a Table or Transformation in a Job" on page 118.
In the properties window for the job or a transformation within the job, click the Process tab.
On the Process tab, specify the location of user-written code.

The online Help for SAS ETL Studio provides more details about user-written code for jobs and transformations. To display the relevant Help topics:

From the menu bar on the SAS ETL Studio desktop, select Help Contents. The online Help window displays.
In the left pane of the Help window, select Task Overviews User-Written Components and SAS ETL Studio Understanding User-Written Source Code for Jobs.

Viewing the Basic Metadata for a Job

Use the property window for a job to view its basic metadata. For example, you can find out if user-written code has been specified for the entire job, or if any code is supposed to run before or after the job.

From the SAS ETL Studio desktop, display the Inventory tree.
In the Inventory tree, expand the Jobs folder.
Select the desired job, then select File Properties from the menu bar. A properties window for the job is displayed.
Use the tabs in this window to view the metadata for the jobs. Each tab has its own Help button.

Updating the Basic Metadata for a Job

Use the property window for a job to update its basic metadata. For example, you can specify user-written code for the entire job, or you can specify code that should be run before or after the job. Assume that the metadata for the job is currently checked into a change-managed repository.

On the SAS ETL Studio desktop, select the Inventory tree.
In the Inventory tree, open the Jobs folder.
Select the desired job, then select Project Check Out. The metadata that is associated with the job will be checked out and will appear in the Project tree. The metadata that will be checked out includes the metadata object for the job as a whole and the metadata objects for any sources and targets that have been added to the job.
In the Project tree, select the metadata for the job, then select File Properties from the menu bar. The properties window for the job displays.
Use the tabs in this window to update the metadata for the job. Each tab has its own Help button.
When you are finished updating the metadata, you can check in your changes. In the Project tree, select the repository icon.
From the menu bar on the SAS ETL Studio desktop, select Project Check In Repository.

Viewing the Data for a Source or a Target in a Job

After the metadata for a source table or a target table has been added to a job, you might want to verify that the corresponding physical table contains the data that you were expecting. Perform the following steps to view the data that corresponds to the metadata for a source or a target.

Note

The metadata for a target might not point to a physical table until after the target's job has been run for the first time. Before the first run, new target tables might exist as metadata only.

The following steps describe one way to view the data for a source or a target in the process flow diagram for a job:

From the SAS ETL Studio desktop, display the Inventory tree.
In the Inventory tree, expand the Jobs folder.
Select the desired job, then select View View Job from the menu bar. The process flow diagram for the job displays in the Process Editor tab of the Process Designer window.
To view the data for a source or a target in the process flow diagram, select the desired source or target, then select View View Data from the menu bar. The data in the source or target is displayed. If the data is correctly displayed, the metadata for the source or target is correct.

Viewing the Metadata for a Table or Transformation in a Job

To view the metadata for a data store or a transformation in the process flow diagram for a job, perform the following steps:

From the SAS ETL Studio desktop, display the Inventory tree.
In the Inventory tree, expand the Jobs folder.
Select the desired job, then select View View Job from the menu bar. The process flow diagram for the job displays in the Process Editor tab of the Process Designer window.
To view the metadata for a data store or transformation in the process flow diagram, select the desired object, then select File Properties from the menu bar. A properties window for the object is displayed.
Use the tabs in this window to view the metadata for the object. Each tab has its own Help button.

Updating the Metadata for a Table or Transformation in a Job

Perform the following steps to update the metadata for a data store or transformation in a job. Assume that the metadata for the job is currently checked into a change-managed repository.

On the SAS ETL Studio desktop, select the Inventory tree.
In the Inventory tree, open the Jobs folder.
Select the desired job, then select Project Check Out. The metadata that is associated with the job will be checked out and will appear in the Project tree. The metadata that will be checked out includes the metadata object for the job as a whole and the metadata objects for any data stores that have been added to the job.
In the Project tree, select the metadata for the job, then select View View Job from the menu bar. The process flow diagram for the job displays in the Process Editor tab of the Process Designer window.
To update the metadata for a data store or transformation in the process flow diagram, select the desired object, then select File Properties from the menu bar. A properties window for the object is displayed.
Use the tabs in this window to update the metadata for the object. Each tab has its own Help button.
When you are finished updating the metadata, you can check in your changes. In the Project tree, select the repository icon.
From the menu bar on the SAS ETL Studio desktop, select Project Check In Repository.

Impact of Updating a Table's Metadata

Keep in mind that a table, such as a source table or a target table, can be used in multiple jobs. A table can also be used in multiple places in the same job. Accordingly, when you update the metadata for a table, make sure that the updates are appropriate in all contexts where the metadata is used. For example, if you update the columns for Table 1 in one job, the updates would also have to be appropriate for Table 1 in the context of another job.

Updating Column and Mapping Metadata

In general, perform the following steps to update the column metadata or mapping metadata in a job. Assume that the metadata for the job is currently checked into a change-managed repository.

On the SAS ETL Studio desktop, select the Inventory tree.
In the Inventory tree, open the Jobs folder.
Select the desired job, then select Project Check Out. The metadata that is associated with the job will be checked out and will appear in the Project tree. The metadata that will be checked out includes the metadata object for the job as a whole and the metadata objects for any sources and targets that have been added to the job.
In the Project tree, select the metadata object for the job, then select View View Job from the menu bar. The process flow diagram for the job is displayed in the Process Editor tab of the Process Designer window.
In the Process Designer window, select the table whose columns you want to update, or select the transformation whose mappings you want to update. Then select File Properties from the menu bar. The properties window for the table or transformation displays
For a table, click the Columns tab. Update the columns as needed. For a transformation, click the Mappings tab. Update the mappings as needed. Click the button on each tab to see topics that describe how to edit columns and mappings.
After making your changes, make sure that source columns are correctly mapped through the job. For one-to-one mappings, the column lengths and data types for the source and target columns must match. For derived mappings (mappings in which the target column is a function of the source column), the column lengths and data types for the source and target columns might be different.

To verify that the updated columns are correctly mapped through the job, display the property windows for tables and transformations that follow the updated table. For tables, review the metadata in the Columns tab. For transformations, review the metadata in the Mapping tab. Make updates as needed. Each tab has its own Help button.
When you are finished updating the metadata, you can check in your changes. In the Project tree, select the repository icon.
From the menu bar on the SAS ETL Studio desktop, select Project Check In Repository.

Running a Job

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

If the job to be submitted is displayed in the Process Designer window, select Process Submit from the menu bar. The job is submitted to the default SAS application server and to any server that is specified in the metadata for a transformation within the job.

If the job to be submitted is not displayed in the Process Designer window, perform the following steps:

From the SAS ETL Studio desktop, display the Inventory tree.
In the Inventory tree, expand the Jobs folder.
Select the desired job, then select View View Job from the menu bar. The process flow diagram for the job displays in the Process Editor tab of the Process Designer window.
Select Process Submit from the menu bar. The job is submitted for execution.

Deploy a Job for Scheduling

If the appropriate software has been installed, you can deploy a SAS ETL Studio job for scheduling. After a job is deployed, an administrator can use SAS Management Console to schedule the job to run at a specified date and time or when a specified event occurs. For details, see "Jobs Can Be Scheduled" on page 102.

< Day Day Up >