Example Database for the Eclipse BIRT Project

The current document provides a high level description of the Classic Models Inc. example database, which has been developed as part of the Eclipse BIRT (Business Intelligence Reporting Tools) project in order to support the BIRT report examples.

Table of Contents

Legal Notice 1

Introduction 1

Database Tables 2
Offices Table 2
Employees Table 2
Customers Table 2
Orders Table 2
OrderDetails Table 2
Products Table 2
Payments Table 2
ProductLines Table 2

Sample Data Trends 3

Target Database Systems and Migration 3

Legal Notice

Copyright (c) 2005 Actuate Corporation.

All rights reserved. This file and the accompanying materials are made available under the terms of the Eclipse Public License v1.0 which accompanies this distribution, and is available at:

http://www.eclipse.org/legal/epl-v10.html

Contributors: Actuate Corporation - initial implementation

Classic Models Inc. sample database developed as part of the Eclipse BIRT Project. For more information, see:

http://www.eclipse.org/birt

Introduction

The Classic Models Inc. example database has been developed as part of the Eclipse BIRT (Business Intelligence Reporting Tools) project. Its main goal is to be obvious and simple, yet able to support a wide range of interesting report examples. The database represents a fictitious company: Classic Models Inc. which buys collectable model cars, trains, trucks, buses, trains and ships directly from manufacturers and sells them to distributors across the globe.

Database Tables

Offices Table

Classic Models Inc. has 7 offices worldwide (San Francisco, Boston, NYC, Paris, Tokyo, Sydney, London) and is headquartered in San Francisco, CA. Based on geography each office is assigned to a sales territory (APAC, NA, EMEA or JAPAN)

Employees Table

The company has 23 employees: 6 Execs and 17 Sales Reps, all assigned to one of the company’s seven offices. Sales Reps are also assigned to a number of customers (distributors) in their respective geographies that they sell to. New Sales Reps (that are still in training) don’t have customers assigned to them. Each Sales Rep reports to the Sales Manager for his/her territory. The only exceptions are the two Sales Reps in the Tokyo office. One of them acts as a Sales Manager and reports directly to the VP of Sales. The second one reports to him.

The Execs: President, VP Sales, VP Marketing, Sales Manager (JAPAN, APAC), Sales Manager (EMEA), Sales Manager (NA) don’t work directly with customers. Each Sales Manager reports to the VP of Sales. Nobody reports to the VP of Marketing. The two VPs report to the company’s President.

Customers Table

Classic Models Inc. has 122 customers across the world. Approximately 20 of those are brand new customers that don’t have an assigned sales rep and haven’t placed any orders yet. Each customer has a credit limit which determines their maximum outstanding balance.

Orders Table

Customers place their orders and expect to receive them approximately within 6 to 10 days. Once an order is placed it’s assembled and shipped within 1 to 6 days (7-8 for Japan). There are a total of 326 orders, which span the period from 1/1/2003 to 6/1/2005. Orders can be in one of these states: In Process (the initial state for all orders), Shipped, Cancelled (used to indicate that the customer called to cancel the order right after the order was placed and typically before it was shipped), Disputed (used to indicate that the customer received the order but doesn’t like it), Resolved (used to indicate that the order was disputed, but successfully resolved) or On Hold (used to indicate that the order will not ship until a payment is received because the customer’s credit limit has been exceeded). Approximately 93% of the orders are in the Shipped state.

OrderDetails Table

Each order contains an average of 9 unique products (order line items) with an average quantity of 35 per product (so there is an average total of 9x35 items per order). Each order line item reflects the negotiated price per product (which is based on the corresponding product’s MSRP) as well as the quantity per product.

Products Table

Classic Models Inc. sells 110 unique models which they purchase from 13 vendors. The models are classified as 7 distinct product lines: Classic Cars, Vintage Cars, Motorcycles, Trucks and Buses, Planes, Ships, Trains. Additionally models are classified based on their scale (e.g. 1:18, 1:72 etc.). For each product the price at which the product was purchased from the vendor (buyPrice) as well as the product’s MSRP are provided. The average MSRP is $100 ranging from $33 to $214. The MSRP price is on average 45% (30% to 60%) above the buyPrice.

Payments Table

Customers make payments (by check) on average 2-3 weeks after they place an order. In some cases one check covers more than 1 order.

ProductLines Table

Products are classified as 7 distinct product lines. Each product line is associated with a text description, html description as well as with an image.

Sample Data Trends

This section highlights some of the data trends present in the Classic Models Inc. example database. For more information review the provided charts.

1. Year over year (2003 versus 2004) sales growth of 33%
2. Sales peak in Q4 and especially in the months of October and November
3. EMEA (Europe, Middle East, Africa) territory generates the largest sales volume
4. There was a slight drop in sales in Q2, 2004. A possible story here can be that certain marketing programs were not utilized during this quarter.
5. Classic cars account for 40% of sales volume
6. On average customers place 1-2 orders per year. There are however two high volume customers (CustomerNumbers: 124, 141) with over 10 orders per year.
7. The shipping times (the difference between OrderDate and ShippedDate) in Japan are on average 7 to 8 days versus 1 to 6 days for other locations. A possible story here can be that since this is a new office (opened in 2004), it’s not very efficient yet.
8. On average customers in Japan send their payments (after placing an order) faster than customers in North America, EMEA and APAC.
9. There are 24 brand new customers in EMEA that will start placing orders soon. This means that probably more Sales Reps and offices will be needed in this sales territory.
10. The average MSRP is $100 ranging from $33 to $214 and it’s on average 45% above the buyPrice.
11. Each order contains an average of 9 unique products (order line items) with an average quantity of 35 per product (so there is an average total of 9x35 items per order).
12. Data for Q2, 2005 is up to June 1, 2005
13. Although 93% of orders have a status of Shipped, there are orders that in the states of In Process, Resolved, Cancelled, On Hold and Disputed.
14. The sample database can be used to present good examples of outer joins. Here are some examples: a. Not all customers have Sales Reps. This represents to represent brand new, just registered customers that haven’t placed any orders yet b. Not all Sales Reps have customers. This represents newly hired sales reps that are still in training. c. Not all products are included in sales orders. This represents products that are not in demand.

Target Database Systems and Migration

The example Classic Models database which is integrated with the BIRT designers can also be exported to any standards based relational database system. To facilitate this process the following have been provided:

• Data files: There is one such text file per table and it contains all of the table’s textual and numeric data in CSV format. Note that the CLOB and BLOB data used by correspondingly the htmlDescription and image columns of the ProductLines table is not included in the ProductLines.txt data file. The BLOB data can be found in the \ClassicModels\images\productlines folder part of this package. This folder contains an image for each of the seven product lines defined in the ProductLines table. The CLOB data can be extracted from the provided MS Access version of the ClassicModels database and more specifically from the htmlDescription column of the ProductLines table. • Derby scripts: A set of scripts that create and load the example database from the provided data files to a Derby database server. The CLOB and BLOB data used in the ProductLines table needs to be uploaded manually. • Archived Derby database: A JAR file containing the entire Classic Models database in binary, Derby format. This file can easily be imported into a Derby database server. • MySQL scripts: A set of scripts that create and load the example database from the provided data files to a MySQL database server. The CLOB and BLOB data used in the ProductLines table needs to be uploaded manually. • MS Access: The sample database stored as an MS Access file