Home  >  Blog  >   SAS BI

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

Rating: 4
  
 
2478
  1. Share:
SAS BI Articles

 

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.

Cube Designer

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

SAS ETL Studio CaseStudy – Define Targets

SAS ETL Studio CaseStudy

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.

Target Table

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

Target Designer

Select 

Next

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

OrderFact

Additional properties
 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 
Next

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

ORDER_ITEM tableOrder_ID Product_ID Quantity Total_Retail_Price
CostPrice_Per_Unit
ORDERS tableOrder_Type
Order_Date Delivery_Date

Target Table designer

Select 

Next

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

Verify tab

To add an additional column definition, select  

New

To remove a column definition, select    
Delete
        .
To define (or modify) indexes for the target table, select  
Define indexes
                  .
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 Roledefines the role of the column in summary data
Sort Orderdefines how the values in the column are sorted
Informatdefines the SAS informat to be used in reading the data.
Format defines the SAS format to be used in writing the data.
Is Nullableindicates whether or not the column can contain null or missing values.

.Select  

Next
             .
6. Select Target Tables Library for the Library

Target Tables 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  

New
.
To edit an existing library definition, select 
Edit

To specify encryption, compression, or other valid SAS data set or view options, select
Table options
.
Select
Next
                 .
7. Verify that the information is correct, then select 
Finish

Meta data creation tab

The metadata definition for the target table OrderFact is now defined.
8. Move the table OrderFact to the Target Tables group.

OrderFact

MindMajix Youtube Channel

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.

OrderFact Properties

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

New
, 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 Placednumeric
Order_Month Month Order was Placednumeric
Delivery_Time Number of Days Until Deliverynumeric


15. Select

Ok
 to close the Properties window and save the changes.

 

Join our newsletter
inbox

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

About Author

Vinod M is a Big data expert writer at Mindmajix and contributes in-depth articles on various Big Data Technologies. He also has experience in writing for Docker, Hadoop, Microservices, Commvault, and few BI tools. You can be in touch with him via LinkedIn and Twitter.

read more
Recommended Courses

1 / 15