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.