SAS ETL Studio CaseStudy – Adding a Target Table Definition in SAS BI

 

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.

Select Target Designer from the Shortcut bar of the sas etl studio desktop.
Select Target Table as the type of target to design.

Select 

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

 can 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 
4. Move the following column definitions to the Selected Columns list:

ORDER_ITEM table	Order_ID Product_ID Quantity Total_Retail_Price CostPrice_Per_Unit
ORDERS table	Order_Type Order_Date Delivery_Date

Select 

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

To add an additional column definition, select  

To remove a column definition, select             .
To define (or modify) indexes for the target table, select                     .
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.

                                             Checkout SAS BI Interview Questions

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  

             .
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  

.
To edit an existing library definition, select 
To specify encryption, compression, or other valid SAS data set or view options, select .
Select                  .
7. Verify that the information is correct, then select 

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

, 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

 to close the Properties window and save the changes.