SAS 9.1.3 ETL Studio: Users Guide
|
| < Day Day Up > |
|
Identifying Relevant Information
To answer the question, Which sales person is marking the most sales?, the data warehousing team decided to design a report that listed total sales by employee. The following display shows an example of such a report.
The next step is to identify how such a report could be created.
Identifying Sources
The data warehouse team examined existing tables to determine if they could be used to the create the report shown in the previous display. They identified a number of tables that could be used. These tables are described in the following sections.
Source for Staff Information
The STAFF table contains information about employees, such as name, ID, department, supervisor, and salary, as shown in the following display.
Source for Organization Information
The following ORGANIZATION table identifies the organization to which an employee belongs.
Source for Order Information
The following ORDERS table contains information about orders placed with salespersons, including date, salesperson ID, type of order, and customer.
Source for Order Item Information
The following ORDER_ITEM table contains information about orders placed with the company, and includes product ID, amount ordered, price of items, and other data.
Source for Customer Information
The following CUSTOMER table contains information about the customers who are placing orders with the company. Information includes name, address, birthdate, and other data.
In reviewing the previous tables, the data warehousing team identified the following issues:
-
The salesperson and salesperson ID must be correlated to determine sales.
-
The sales totals for each order must be correlated with the correct salesperson.
-
The sales for each sales person must be totaled.
-
Some information does not exist in current source tables. New columns and tables must be created.
The next step is to specify the new tables that must be created in order to produce the desired reports.
Identifying Targets
To simplify the SAS ETL Studio job that will be used to create the desired report, the team decided to combine certain columns from existing tables into a smaller number of new tables:
-
A new table will be created that joins the CUSTOMER, ORDERS, and ORDER_ITEMS tables.
-
A new table will be created that joins the STAFF and ORGANIZATION tables.
-
In order to answer the question of who made the most sales, the two new tables will be combined to create a third new table on which the report will be based.
By combining tables, the warehouse team can easily answer the specified question, as well create a diverse range of reports to answer other business questions. Details about each new table are provided in the following sections.
Target That Combines Order Information
The ORDER_FACT table is created by joining the CUSTOMER, ORDERS, and ORDER_ITEMS tables. The new table will include all order data, including salesperson ID, customer, price, and quantity.
Target That Combines Organization Information
The ORGANIZATION_DIM table is created by joining the STAFF and ORGANIZATION tables. The new table will include all employee information including name, ID, salary, department, and managers.
Target That Lists Total Sales by Employee
The Total_Sales_by_Employee table is created by joining the ORDER_FACT table and ORGANIZATION_DIM table. The new table will include employee name, total revenue, employee ID, job title, company, and department. It will be used to produce the report shown in Display 4.1 on page 29.
|
| < Day Day Up > |
|