SAS ETL Studio CaseStudy – Adding a Target Table Definition

Using the Target Designer

The Target Designer is a wizard that can create new metadata about a single table that might or might not already exist in physical storage.

It can also be used to create and edit metadata about an OLAP cube.

The Target Designer is an easy to use wizard interface.


Selecting Cube Designer in the first window of the Target Designer will launch the Cube Designer wizard.

SAS ETL Studio CaseStudy – Define Targets


Add a Target Table Definition

The next step in the implementation of the Orion Star marketing data mart is to use the Target Table Designer to define the target tables for the ETL process.


  1. Select Target Designer from the Shortcut bar of the SAS ETL Studio desktop.
  2. Select Target Table as the type of target to design.


Select Screenshot_1212

      3. Type OrderFact for the name of the target table.


Screenshot_1300can be used to display the interface where you can specify notes, user responsibilities, table attributes, and extended attributes for the target table you are defining.

Select Screenshot_1212

      4. Move the following column definitions to the Selected Columns list:

ORDER_ITEM table Order_ID Product_ID Quantity Total_Retail_Price


ORDERS table Order_Type

Order_Date Delivery_Date


Select Screenshot_1212

      5. Review and verify the metadata that was imported for each of the columns.


To add an additional column definition, select  Screenshot_1215

To remove a column definition, select    Screenshot_1303         .

To define (or modify) indexes for the target table, select  Screenshot_1304                   .

Several index types can be created:

  • Simple – consists of a single column.
  • Composite – consists of two or more related columns.
  • Unique – is an index (simple or composite) that is required to have a unique value in each row.

Additional metadata items can also be specified for each column:

Summary Role defines the role of the column in summary data
Sort Order defines how the values in the column are sorted
Informat defines the SAS informat to be used in reading the data.
Format defines the SAS format to be used in writing the data.
Is Nullable indicates whether or not the column can contain null or missing values.

Select   Screenshot_1212              .

      6. Select Target Tables Library for the Library


The type of file selected in the DBMS field controls the type of storage intended to be utilized for the new target table. The selection arrow shows a list of DBMSs that are valid in the current context (that is, SAS, Oracle, SAP, Sybase, and so on).

To add a new library definition, select  Screenshot_1215.

To edit an existing library definition, select Screenshot_1306

To specify encryption, compression, or other valid SAS data set or view options, select  Screenshot_1311.

Select Screenshot_1212                  .

      7. Verify that the information is correct, then select Screenshot_1223


The metadata definition for the target table OrderFact is now defined.

      8. Move the table OrderFact to the Target Tables group.


Updating Table Metadata

 It is possible to update a defined table in the metadata repository with additional information. The following demonstrates adding additional columns – this could have been done during the initial specifications but can also be done at a later point.

      9. From the Custom tab in the tree view area of SAS ETL Studio, expand the Target Tables group.

      10. Right-click on the OrderFact table and select Properties from the pop-up menu.

      11. In the OrderFact Properties window, select the Columns tab.


      12. Select the Delivery_Date row so that the new columns will be added to the end of the list.

 New columns are added after the row of information that is currently selected.

      13. Select Screenshot_1215, a new row for an additional column definition

     a. Type Order_Year in the Name field.

     b. Advance to the Description field by using the Tab key or by clicking in the field. Enter Year Order was Placed as a description.

     c. Advance to the Type field and select Numeric.

  A valid SAS variable name starts with a character and has a maximum of 32 characters.

A valid length is 2 to 8 for numeric types and up to 32,767 for character types. The description is the label that gets associated with the column when it is displayed in selection lists in SAS ETL Studio or other SAS applications.

     14. Repeat the steps above to add the following new columns:

Name Description Type
Order_Qtr Quarter Order was Placed numeric
Order_Month Month Order was Placed numeric
Delivery_Time Number of Days Until Delivery numeric

     15. Select Screenshot_1207to close the Properties window and save the







0 Responses on SAS ETL Studio CaseStudy – Adding a Target Table Definition"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.