Question

Business Scenario:

Please install the AdventureWorks database, read the AdventureWorks case study, and analyze the ERD OLTP schema before starting this assignment.

Question:
The CEO of AdventureWorks Inc. wants to build a data mart to keep track of sales performance. Specifically, he wants to know:

How many products the company sells to each customer.
What is the sales productivity of their salesperson.
Currently, the company uses the total number of orders and the sales performance ratio (defined by SalesYTD divided by SalesLastYear) to measure the business process.

Tasks:

Provide a 4-steps dimensional model in the SQL comment section.
Create a data mart solution in SQL for the dimension model that you propose.
Provide SQL commands to answer the two questions above.

Answer 1

To address the CEO's requirements for AdventureWorks Inc., a four-step dimensional model is proposed, focusing on establishing the business process, determining the analysis grain, defining dimensions and fact tables. A SQL data mart model consisting of a star schema would then be implemented, and specific SQL queries would be used to calculate the number of products sold to each customer and the salesperson's sales productivity.

Step-by-step explanation:

Creating a Dimensional Data Mart Model

To build a data mart that tracks sales performance for AdventureWorks Inc., a four-step dimensional model would generally include the following steps:

1. Identify the business process to model, which in this instance is the sales performance tracking.
2. Determine the grain of the business process, which means establishing the most atomic level at which data must be analyzed, such as sales transactions by date, product, and customer.
3. Define the dimensions that give context to the facts, like Product, Customer, Time, Salesperson, and potentially others related to sales activities.
4. Identify the fact tables that contain the measures of the business process, such as sales amount or quantities.

Using SQL to create a simple data mart for this application could involve creating a star schema with a central fact table (e.g., SalesFact) connected to related dimension tables (e.g., CustomerDim, ProductDim, SalespersonDim).

SQL Commands to Answer Business Questions

To provide the CEO with the number of products sold to each customer and the sales productivity of their salesperson, we would use SQL aggregate functions like COUNT and SUM over the fact table, grouped by the appropriate dimensions. An example SQL command might look like:

-- Number of products sold to each customer
SELECT CustomerID, COUNT(DISTINCT ProductID) AS ProductCount
FROM SalesFact
GROUP BY CustomerID;

-- Sales productivity of each salesperson
SELECT SalespersonID, SalesYTD / SalesLastYear AS SalesPerformanceRatio
FROM SalesFact
GROUP BY SalespersonID;