Summary of a business process

Suppose your organization wants to analyze customer buying trends by product line and region so that you can develop more effective marketing strategies. In this scenario, the subject area for your data model is sales.

After many interviews and thorough analysis of your sales business process, your organization collects the following information:
  • Customer-base information has changed.

    Previously, sales districts were divided by city. Now the customer base corresponds to two regions: Region 1 for California and Region 2 for all other states.

  • The following reports are most critical to marketing:
    • Monthly revenue, cost, net profit by product line from each vendor
    • Revenue and units sold by product, by region, and by month
    • Monthly customer revenue
    • Quarterly revenue from each vendor
  • Most sales analysis is based on monthly results, but you can choose to analyze sales by week or accounting period (at a later date).
  • A data-entry system exists in a relational database.
    To develop a working data model, you can assume that the relational database of sales information has the following properties:
    • The stores_demo database provides much of the revenue data that the marketing department uses.
    • The product code that analysts use is stored in the catalog table by the catalog number.
    • The product line code is stored in the stock table by the stock number. The product line name is stored as description.
    • The product hierarchies are somewhat complicated. Each product line has many products, and each manufacturer has many products.
  • All the cost data for each product is stored in a flat file named costs.lst on a different purchasing system.
  • Customer data is stored in the stores_demo database.

    The region information has not yet been added to the database.

An important characteristic of the dimensional model is that it uses business labels familiar to end users rather than internal tables or column names. After the analysis of the business process is completed, you should have all the information you need to create the measures, dimensions, and relationships for the dimensional data model. This dimensional data model is used to implement the sales_demo database that the section Implement a dimensional database describes.

The stores_demo demonstration database is the primary data source for the dimensional data model that this section builds. For detailed information about the data sources that are used to populate the tables of the sales_demo database, see Mapping data from data sources to the database.