You can create SQL queries to retrieve the data necessary for the standard reports listed in the business-process summary (see the Summary of a Business Process). Use the following ad hoc queries to test that the dimensional database was properly implemented.
The following statement returns the monthly revenue, cost, and net profit by product line for each vendor:
SELECT vendor_name, product_line_name, month_name, SUM(revenue) total_revenue, SUM(cost) total_cost, SUM(net_profit) total_profit FROM product, time, sales WHERE product.product_code = sales.product_code AND time.time_code = sales.time_code GROUP BY vendor_name, product_line_name, month_name ORDER BY vendor_name, product_line_name;
The following statement returns the revenue and units sold by product, by region, and by month:
SELECT product_name, region, month_name, SUM(revenue), SUM(units_sold) FROM product, geography, time, sales WHERE product.product_code = sales.product_code AND geography.district_code = sales.district_code AND time.time_code = sales.time_code GROUP BY product_name, region, month_name ORDER BY product_name, region;
The following statement returns the monthly customer revenue:
SELECT customer_name, company_name, month_name, SUM(revenue) FROM customer, time, sales WHERE customer.customer_code = sales.customer_code AND time.time_code = sales.time_code GROUP BY customer_name, company_name, month_name ORDER BY customer_name;
The following statement returns the quarterly revenue per vendor:
SELECT vendor_name, year, quarter_name, SUM(revenue) FROM product, time, sales WHERE product.product_code = sales.product_code AND time.time_code = sales.time_code GROUP BY vendor_name, year, quarter_name ORDER BY vendor_name, yearHome | [ Top of Page | Previous Page | Next Page | Contents | Index ]