An OLAP Requirements Example

Following is an abbreviated requirement that reflects an actual implementation that was done for a large Silicon Valley company. You will follow the mini-methodology as closely as possible to implement this requirement in AS, pointing out which facilities of AS should be used for which purpose along the way.

CompSales International

A large computer manufacturer named CompSales International needs to do basic analytical processing of its product data in a new DSS environment. The main business issues at hand are related to minimizing channel inventory and better understanding market demand for the company's most popular products. The detailed data processing requirements are as follows :

1. View sales unit actuals and sales returns for system and non-system products for the past two years via the product hierarchy (All Products, Product Types, Product Lines, Product Families, SKUs), geography hierarchy (All Geos, Major Geos, Countries, Channels, Customers) and for different time levels (All Time, Years , Quarters, Months).

2. View data primarily at the yearly and monthly levels, although the finance department also uses it a little bit at quarterly levels.

3. View net sales (sales “ returns) at all levels of the hierarchy.

4. Viewing of the fiscal and Gregorian calendar are the same for CompSales International.

5. One day past "month end" processing, all "actuals" data from the prior month is available (sales units and returns).

You will implement some general design decisions using AS, including the following:

• Hierarchies (dimensions) ”This includes product, geography, and time.

• Facts (measures) ”This includes sales units, sales returns, and net sales (units “ returns) calculated.

Figure 42.5 illustrates the desired hierarchies and facts for CompSales International's requirements.

• OLAP storage ”This includes HOLAP (to leverage off of the star-schema data mart that already contains most of what you want).

• Physical tables that exist ”This includes Geo_Dimension, Prod_Dimension, Time_Dimension, and CompSalesFacts. This data is updated monthly. Each of these tables uses an artificial key into the main facts table for performance reasons (GeoID, ProductID, TimeID).

Figure 42.5. CompSales International OLAP requirements.