AdventureWorks Sample Data Warehouse

The Adventure Works DW sample data warehouse is used in SQL Server Books Online to demonstrate the business intelligence features that are available in SQL Server. The data in the data warehouse has been trended to support both data mining and Online Analytical Processing (OLAP) scenarios.

The Data Warehouse

Adventure Works DW contains a subset of the tables from the OLTP database, in addition to financial information that is pulled from a separate data source. The data is kept in synch with the OLTP database to support typical Integration Services scenarios such as loading and updating the data warehouse. The AWDataWarehouseRefresh Package Sample demonstrates how to use an Integration Services package to load data into Adventure Works DW from the Adventure Works OLTP.

Adventure Works DW contains two subject areas, finance and sales, which are described in the following sections. Additionally, the data warehouse contains views that are used to support the data mining scenarios that are described later in this topic.

Finance

The finance subject area of the data warehouse is broken down into two schemas that have the following characteristics:

Finance

Contains finance data for Adventure Works corporate and its subsidiaries.

Contains data in the local currency of the organization to which it is associated.

Supports the Analysis Services Finance measure group.

Currency Rates

Contains currency conversion data, including daily average rates and end of day rates in relationship to the United States Dollar (USD). .

Supports the Analysis Services Currency Rates measure group.

Sales

The sales subject area is broken down into four schemas with the following characteristics:

Reseller Sales

Contains sales only to resellers.

Contains only shipped orders.

Contains data in USD and tracks original currency.

Supports the Analysis Services Reseller Sales measure group.

Sales Summary

Contains a summary view of the reseller and Internet sales data.

Has reduced dimensionality compared to the reseller and Internet sales schemas.

Internet Sales

Contains individual customer Internet sales order and detail data.

Contains only shipped orders.

Contains data in USD and tracks original currency.

Supports the Analysis Services Internet Sales measure group.

Sales Quota

Contains sales quota data for sales representatives.

Supports the Analysis Services Sales Quota measure group.

The Internet sales tables have been trended to support data mining scenarios, whereas the other tables have been trended to support OLAP scenarios.

Data Mining Scenarios

The trending in the Adventure Works DW database supports the following data mining scenarios:

Forecasting: Supports the scenario of an analyst investigating the growth of bicycle models by time and region.

Targeted Mailing Campaign: Supports the scenario of an analyst applying various data mining algorithms to Adventure Works customer and Internet sales data to determine the demographic attributes of customers who are likely to purchase a bicycle. The analyst can then apply the data mining model to a list of potential customers in order to determine which customers are most likely to respond to a targeted mailing that promotes Adventure Works bikes.

Market Basket Analysis: Supports the scenario of a developer creating a market basket solution that suggests a product based on other products that already exist in a customers shopping cart.

Sequence Clustering: Supports the scenario of an analyst investigating the sequence in which customers put items in a shopping cart.

These scenarios and the trending in the data warehouse are demonstrated in the Data Mining Tutorial in SQL Server Books Online and in the Adventure Works DW sample project.

OLAP Scenarios

The trending in the Adventure Works DW database supports the following data mining scenarios:

Financial Scenarios

Financial Reporting: Supports the scenario of reporting income statements and balance sheets that include all subsidiaries. Also supports the ability to report the financial data in a specified local currency.