DB2 UDB's High-Function Business Intelligence in e-business

160 High-Function Business Intelligence in e-business

Figure 4-5 Top 5 sales persons by region

4.2.4 Compare and rank the sales results by state and country

This query compares the sales results rolled up to country and state level for

1994. Its purpose is to view global sales ranking, peer to peer ranking among

states and countries, with ranking within parent levels.

Data

Our data is sourced from sales transactions, using the same tables as used

earlier. The key attributes are transaction date, transaction amount, state, and

country. Our data resides in the following tables:

򐂰 TRANS

򐂰 TRANSITEM

򐂰 LOC

BI functions showcased

GROUPING, RANK, OVER, ORDER BY, ROLLUP

Steps

We executed multiple queries, each addressing a particular requirement.

Chapter 4. Statistics, analytic, OLAP functions in business scenarios 161

Query 1

The query shown in Example 4-8 globally ranks the countries and states by the

sales revenues:

Example 4-8 Globally rank the countries & states by sales revenues

SELECT SUM(ti.amount) AS sum, loc.country, loc.state,

GROUPING(loc.country) + GROUPING(loc.state) AS level,

RANK() OVER (ORDER BY SUM(ti.amount) DESC ) AS global_rank

FROM trans t, transitem ti, loc loc

WHERE t.transid =ti.transid AND loc.locid = t.locid AND YEAR(pdate) = 1994

GROUP BY ROLLUP (loc.country, loc.state)

ORDER BY global_rank

Figure 4-6 shows the results of this query.

Figure 4-6 Global ranking

162 High-Function Business Intelligence in e-business

States, countries, and the world have a level by hierarchy. The world has level 2,

states have level 1, and the countries have a level 0 within the LEVEL as shown

in Figure 4-7. The world is ranked 1 (GLOBAL_RANK) by virtue of the total sales

transactions.

Figure 4-7 Levels in hierarchy

Query 2

The query shown in Example 4-9 ranks the sales among peers, that is, rank all

the countries, and then the states, across multiple countries.

Example 4-9 Sales among peers

SELECT SUM(ti.amount) AS sum, loc.country, loc.state,

GROUPING(loc.country) + GROUPING(loc.state) AS level,

RANK () OVER (PARITION BY GROUPING (loc.country) + GROUPING(loc.state)

ORDER BY SUM(ti.amount) DESC) AS rank_within_peers

FROM trans t, transitem ti, loc loc

WHERE t.transid = ti.transid AND loc.locid = t.locid AND YEAR(pdate) = 1994

GROUP BY ROLLUP (loc.country, loc.state)

ORDER BY level DESC, rank_within_peers

Figure 4-8 shows the results of this query.

Chapter 4. Statistics, analytic, OLAP functions in business scenarios 163

Figure 4-8 Ranking within peers

Query 3

The query shown in Example 4-10 ranks the sales within each parent, that is,

rank all the countries, and then states within each country.

Example 4-10 Sales within each parent

SELECT SUM(ti.amount) AS sum, loc.country, loc.state,

GROUPING (loc.country) + GROUPING(loc.state) AS level,

RANK() OVER (PARTITION BY GROUPING (loc.country) + GROUPING(loc.state),

CASE WHEN GROUPING(loc.state) = 0 THEN loc.country END

ORDER BY SUM (ti.amount) DESC) AS rank_within_parent

FROM trans t, transitem ti, loc loc

WHERE t.transid = ti.transid AND loc.locid = t.locid

AND YEAR(pdate) = 1994