Databases Reference
In-Depth Information
Example: Using the Target Table Designer to Register SAS Tables
Preparation
Suppose that you wanted to create a report that shows which sales person is making
the most sales, as described in “Which Salesperson Is Making the Most Sales?” on page
45. You decide to extract columns from several existing tables, write that information to
a new table, and run a report on the new table. An example of this report is shown in
the following display.
Display 9.1
Total Sales by Employee Report
One way to create a new table whose columns are extracted from existing tables is to
create a job that joins the required tables and writes the output of the join to a new
table. The new table would be the input to a report transformation. An example of such
a process flow is shown in Display 10.3 on page 153.
However, in order to create this process flow, you must first register both the source
tables and the new target table. SAS Data Integration Studio needs the target table
metadata to specify the content and structure of the information that is extracted from
the source tables.
This example describes how to use the Target Table Designer wizard to register a
new table whose columns are modeled after the columns in several existing tables.
Assume that the following preparations have been made:
The sales report will be based on a new table that is named
Total_Sales_By_Employee. This table will have the same columns in the same
order as the report that is shown in Display 9.1 on page 140.
Total_Sales_By_Employee will be created by a SAS Data Integration Studio job
that joins two existing tables: ORDER_FACT and ORGANIZATION_DIM.
Metadata for ORDER_FACT and ORGANIZATION_DIM is available in a current
metadata repository.
Metadata for the following columns will be imported from the
ORGANIZATION_DIM table into the Total_Sales_By_Employee table:
Employee_Name, Employee_ID, Job_Title, Company, Department, Section,
Org_Group. These fields are needed to identify the employee in the report.
Metadata for the following column will be imported from the ORDER_FACT table
into the Total_Sales_By_Employee table: Total_Retail_Price. This field lists the
price of each item sold by an employee. This information will be used calculate the
total sales for each employee.
The Total_Sales_By_Employee table will be in SAS format and will be stored in a
SAS library named Report Table Lib.
The Report Table Lib library has been registered in a current metadata repository.
For details about libraries, see “Registering Libraries” on page 59.
Search WWH ::
Custom Search