Maximum Miniatures, Inc.

Throughout the remainder of this book, Maximum Miniatures, Incorporated serves as the basis for all of our examples. Maximum Miniatures, or Max Min, Inc., as it is referred to by most employees, manufactures and sells small, hand-painted figurines. It has several product lines, including the Woodland Creatures collection of North American animals; the Mythic World collection, which includes dragons, trolls, and elves; the Warriors of Yore collection containing various soldiers from Roman times up through World War II; and the Guiding Lights collection featuring replica lighthouses from the United States. The miniatures are made from clay, pewter, or aluminum.

Max Min markets these miniatures through three different channels. It operates five of its own "Maximum Miniature World" stores dedicated to selling the Max Min product line. Max Min also operates MaxMin.com to sell its products online. In addition, Max Min sells wholesale to other retailers.

Business Needs

Max Min, Inc. has experienced rapid growth in the past three years, with orders increasing by over 300%. This growth has put a strain on Max Min's only current Source of business intelligence, the printed report. Reports that worked well to support decision making just a few years ago now take an hour or more to print and even longer to digest. These reports work at the detail level with little summarization. Max Min's current systems provide few, if any, alternatives to the printed reports for viewing business intelligence.

In addition, Max Min, Inc. is facing tough competition in a number of its product areas. This competition requires Max Min to practice effective decision making to keep its competitive edge. Unfortunately, Max Min's current business intelligence infrastructure, or lack thereof, is making this extremely difficult.

Because of these issues, Max Min has launched a new project to create a true business intelligence environment to support its decision making. This project includes the design of a data warehouse structure, the population of that data warehouse from its current systems, and the creation of analysis applications to serve decision makers at all levels of the organization.

The new business intelligence platform is based on SQL Server 2005. After an extensive evaluation, it was decided that the SQL Server 2005 platform would provide the highest level of business intelligence capability for the money spent. SQL Server 2005 was also chosen because it features the tools necessary to implement the data warehouse in a relatively short amount of time.

We will examine each step of Max Min's implementation project as we learn about the various business intelligence tools available in SQL Server 2005. Before we begin, let's take a quick look at Max Min's current systems.

Current Systems

Max Min has five data processing systems that are expected to serve as sources for business intelligence. See Figure 2-4.

Figure 2-4: Sources for business intelligence at Max Min, Inc.

Manufacturing Automation

The manufacturing automation system tracks the materials used to make each product. It also stores which products are manufactured on which production lines. Finally, this system tracks the number of items manufactured during each shift.

The manufacturing automation system uses a proprietary data storage format. Data can be exported from the manufacturing automation system to a comma-delimited text file. This text file serves as the source for loading the manufacturing data into the business intelligence systems.

Order Processing

The order processing system manages the inventory amounts for all products. It tracks wholesale orders placed by non-Max Min retailers. The system also records product amounts sold through the Max Min retail stores and the Max Min online store to maintain inventory amounts.

The order processing system tracks order fulfillment including product shipping. It also generates invoices and handles the payment of those invoices. In addition, this system records any products returned from the retailer.

The order processing system uses a Microsoft SQL Server database as its backend.

Point of Sale

The point of sale (POS) system manages the cash registers at each of the five Max Min-owned retail stores. This system also tracks the inventory at each retail store using UPC bar code stickers placed on each item. The POS system handles both cash and credit card transactions. It also tracks information on any products returned by the customer.

Information from each of the five POS systems is exported to an XML file. This XML file is transferred, using FTP, to a central location nightly. These XML files serve as the source for loading the POS data into the business intelligence systems.

MaxMin.com

The MaxMin.com online store is an ASP.NET application. It uses SQL Server as its backend database. All sales through the online store are paid with a credit card. All customers of the online store must provide name, address, phone number, and e-mail address with each purchase.

The online store tracks the shipping of orders. It also handles any products returned by customers. Finally, the online store saves information on product promotions and discounts that are run on the store site.

Accounting

The accounting system tracks all the financial transactions for Max Min, Inc. This includes the purchase of raw materials for manufacturing. The accounting system uses a Visual FoxPro database for its backend.