Databases Reference
In-Depth Information
Example: Creating a Job That Joins Two Tables and Generates a Report
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 10.1
Total Sales by Employee Report
One way to create this report is to create a SAS Data Integration Studio 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. This example demonstrates one way to
create such a process flow.
In the example, columns from source tables (ORGANIZATION_DIM and
ORDER_FACT) are extracted and mapped to columns in the main target table
(Total_Sales_By_Employee). The main target table, in turn, is the input to a report
transformation (Publish to Archive) that creates the desired report.
Assume the following about the example job:
Metadata for both source tables in the job (ORGANIZATION_DIM and
ORDER_FACT) is available in a current metadata repository. “Identifying Targets”
on page 48 describes how these tables were created by combining information from
other tables.
The ORGANIZATION_DIM and ORDER_FACT tables have the same key column:
Employee_ID. In the current example, these tables will be joined on the
Employee_ID column.
Metadata for the main target table in the job (Total_Sales_By_Employee) is
available in a current metadata repository. “Example: Using the Target Table
Designer to Register SAS Tables” on page 140 describes how the metadata for this
table could be specified. As described in that section, the
Total_Sales_By_Employee table has only those columns that are required for the
report. The metadata for these columns is shown in Display 9.5 on page 144.
You have selected a default SAS application server for SAS Data Integration
Studio, as described in “Selecting a Default SAS Application Server” on page 96.
This server can access all tables that are used in the job.
The main metadata repository is under change-management control. For the
current example, the metadata for the Total_Sales_By_Employee table must be
checked out because (a) the metadata for this table was created and checked in
earlier, as described in “Example: Using the Target Table Designer to Register
Search WWH ::
Custom Search