Which Sales Person Is Making the Most Sales?

---

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.

Display 4.1: Total Sales by Employee (mockup)

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.

Display 4.2: The STAFF Table

Source for Organization Information

The following ORGANIZATION table identifies the organization to which an employee belongs.

Display 4.3: The ORGANIZATION Table

Source for Order Information

The following ORDERS table contains information about orders placed with salespersons, including date, salesperson ID, type of order, and customer.

Display 4.4: The ORDERS Table

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.

Display 4.5: The ORDER_ITEM Table

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.

Display 4.6: The CUSTOMER Table

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.

---