AdventureWorks Sample Data Warehouse
The Adventure Works DW sample data warehouse is used in SQL Server 2005 Books Online to demonstrate the business intelligence features that are available in SQL Server 2005. The data in the data warehouse has been trended to support both data mining and Online Analytical Processing (OLAP) scenarios.
About the Data Warehouse
The following sections provide more information about the data warehouse itself and about the scenarios that it supports:
- The Data Warehouse
- Data Mining Scenarios
- 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 SQL Server 2005 Integration Services (SSIS) 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.
Back to Top
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.
For More Information:Data Mining Tutorial
Back to Top
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.
Actual versus Budget: Supports the scenario of analyzing actual expenses against budgeted expenses.
Sales Scenarios
Product Profitability Analysis: Supports the scenario of analyzing the product sales margin by tracking costs, discounts, and selling prices.
Sales Force Performance: Supports the scenario of tracking the variance between sales quotas and actual sales.
Trend and Growth Analysis: Supports the scenario of analyzing how the current period compares to prior periods in terms of sales.
Promotion Effectiveness: Supports the scenario of analyzing how promotions affect sales performance.
These scenarios and the trending in the data warehouse are demonstrated in the Analysis Services Tutorial in SQL Server Books Online and in the Adventure Works DW sample project.
For More Information:SQL Server 2005 Analysis Services Tutorial
Back to Top
See Also
Other Resources
AdventureWorks Sample OLTP Database
Installing AdventureWorks Sample Databases and Samples