174 High-Function Business Intelligence in e-business
Data
We assume that there is a data warehouse containing information about
customers and products/services they subscribe to. And we assume that we
have already done an analysis of how much profit each product or service brings
to the company per year.
The main sources of our data are:
? Product information which lists the products/services offered
? Customer details
? Products/Services purchased by customers
Customer profitability is the price of the product minus the cost the company
incurs in providing the service, as shown in Figure 4-21.
Figure 4-21 Profit from a customer
BI functions showcased
RANK, DENSE_RANK, ROW_NUMBER, ORDER BY
Companies rank their customers based on their business rules. In this example,
we show ranking using RANK, DENSE_RANK and ROWNUMBER in order to
show the differences in the results of these functions.
Steps
The SQL shown in Example 4-17 provides the desired result.
Example 4-17 Most profitable customers
SELECT a.custid,SUM(c.profit) AS total_profit,
RANK() OVER (ORDER BY SUM(c.profit) DESC) AS rank,
DENSERANK() OVER(ORDER BY SUM(c.profit) DESC) AS denserank,
ROW_NUMBER() OVER(ORDER BY SUM(c.profit) DESC) AS rownum
FROM cust a, prod_owned b, prod c
WHERE a.custid=b.custid AND b.prodid=c.prodid
GROUP BY a.custid
Customer yearly profit is calculated:
Customer Total Profit (CTP) = (N1 x P1) + (N2 x P2) +...(Nn x Pn)
Where:
N = number of product/service a customer has per product type
P = profit for product type