154 High-Function Business Intelligence in e-business
We do the scaleup by computing the average sales for a group in the sampled
table (that is, total sales for the group divided by the group size), and then
multiplying by g_size, the size of the group in the original table.
We also used a different standard error formula using the VAR function that
corresponds to the different estimator.
4.2 Retail
We have selected the following typical business queries for our examples:
1. Present annual sales by region and city.
2. Provide total quarterly and cumulative sales revenues by year.
3. List the top 5 sales persons by region this year.
4. Compare and rank the sales results by state and country.
5. Determine relationships between product purchases.
6. Determine the most profitable items and where they are sold.
7. Identify store sales revenues noticeably different from average.
8. Project growth rates of Web hits for capacity planning purposes.
4.2.1 Present annual sales by region and city
This is a typical report reviewing sales results for planning budgets, campaigns,
expansions/consolidations etc.
Data
Input for this report is primarily transaction data along with dimension information
relating to date, product, and location. Attributes of interest include:
򐂰 Date of transaction, product purchased, product price and quantity purchase
򐂰 Product code, product name, subgroup code, subgroup name and product
group and product group name
򐂰 Region, city
BI functions showcased
GROUP BY, ROLLUP
Steps
Our data resided in a FACT_TABLE and a LOOKUP_MARKET table.
Chapter 4. Statistics, analytic, OLAP functions in business scenarios 155
The SQL shown in Example 4-5 was run in DB2 Control Center.
Example 4-5 Annual sales by region and city
SELECT b.region_type_id, a.city_id, SUM(a.sales) AS TOTAL_SALES
FROM fact_table a, lookup_market b
WHERE YEAR(transdate)=1999 AND a.city_id=b.city_id
AND b.region_type_id=6
GROUP BY ROLLUP(b.region_type_id,a.city_id)
ORDER BY b.region_type_id, a.city_id
Figure 4-1 shows the results of this query.
Figure 4-1 Yearly sales by city, region
Note: To reduce the size of the query result, the foregoing SQL limits the
query to region 6, and the transaction date to 1999.

Get DB2 UDB's High-Function Business Intelligence in e-business now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.