From time to time, it’s handy to have access to some good, solid sample data. Now, actually coming up with a robust set of sample data that has no tie back to any proprietary business data can actually be a tricky prospect. It’s easy to generate a small amount of good data yourself, or a large amount of really random data, but neither of these are very robust. Fortunately, Microsoft comes to the rescue with their fictional company, Adventure Works, and the Adventure Works Databases.
Microsoft provides the following bio for the Adventure Works company over at Technet:
Adventure Works Cycles, the fictitious company on which the AdventureWorks sample databases are based, is a large, multinational manufacturing company. The company manufactures and sells metal and composite bicycles to North American, European and Asian commercial markets. While its base operation is located in Bothell, Washington with 290 employees, several regional sales teams are located throughout their market base.
In 2000, Adventure Works Cycles bought a small manufacturing plant, Importadores Neptuno, located in Mexico. Importadores Neptuno manufactures several critical subcomponents for the Adventure Works Cycles product line. These subcomponents are shipped to the Bothell location for final product assembly. In 2001, Importadores Neptuno, became the sole manufacturer and distributor of the touring bicycle product group.
Coming off a successful fiscal year, Adventure Works Cycles is looking to broaden its market share by targeting their sales to their best customers, extending their product availability through an external Web site, and reducing their cost of sales through lower production costs.
In addition to providing a realistic scenario for the Adventure Works company and a database full of realistic sample data, Microsoft continually updates the Adventure Works database for new versions of SQL Server and makes numerous variations of the databases available to highlight different technologies.
A new version of the Adventure Works databases is typically made available after each new release of a major SQL Server version, starting with 2005. With each new version, there may be little to no changes from the previous, there may be updates to just the data (such as updating dates), or there may be larger changes to the schema to allow for the use of new functionality or techniques.
The largest change has been from 2005 to 2008, where a number of schema changes were made. Changes in more recent versions (2008 to 2012 to 2014) have been much more minor. As such, always be aware of the version you’re using and the version which was used to create a specific demo. If something isn’t working correctly, that may be a reason why.
As of this writing, the 2014 version of the Adventure Works database is the most current and is available here. The 2012 version available here is also good to know about, as not all variations exist in the 2014 version currently.
As well as updating the Adventure Works databases with each major version of SQL Server, Microsoft makes a number of variations of the database available. These different variations are intended to target different groups or learning scenarios (such as having a version specific to OLTP, a version specific to data warehousing, and a version specific to OLAP). It’s very important to ensure you have the correct variation which was used for a specific demo.
- Standard (OLTP) – The most common aka “regular” variation of the database. If only the term “Adventure Works database” is used, this is usually what is being referred to. This is the OLTP version of the database which simulates the back-end storage of the Adventure Works transactional system. This is usually Microsoft’s “recommended” download.
- LT – A lite version of the standard OLTP variation. This is designed to be a simpler database with a more entry-level target audience or for less sophisticated demos.
- DW – The Data Warehouse flavor of the Adventure Works database.
- Multidimensional – The Multidimensional (MDX) SSAS version of the Adventure Works database.
- Tabular – The Tabular (DAX) SSAS version of the Adventure Works database.
- Internet Sales Tabular – A special Tabular (DAX) SSAS version of the Adventure Works database with mock internet sales data.
In addition, there are some additional variations with minor tweaks made to the databases (such as being case sensitive) to cover other demo scenarios.
I won’t cover the specific on how to install the various Adventure Works databases, as there are already numerous resources on how to do this. Suffice it to say, there are usually two installation options — download a backup copy of the database and “restore” it into your environment, or download a creation script to create it in your environment.
After loading the Adventure Works database, the first thing you’ll notice is that it is actually pretty large and complex. While you can use the relationship diagrams inside the database, there are a few external resources available as well. The first is the Microsoft Data Dictionary, which gives an overview of each table and field in a reference guide. Additionally, Wilson Mar has rounded up a number of good bit of information here.
When creating sample reporting based on the Adventure Works data, it can really add to the presentation value of the reports to have appropriate branding. Most of the logos included in the Adventure Works samples are pretty old and low resolution. Fortunately, Bret Stateham was kind enough to go through and update the existing logos to higher resolutions, which I’ve mirrored here.
The Adventure Works set of databases provided my Microsoft make for an excellent source of demo data for learning and showing off countless features in the Microsoft SQL Server suite. While they can be pretty complicated to get started with, they are invaluable as they are used frequently across the industry for teaching new skills. Hopefully this collection will help to augment the information already out there on these excellent resources. When I make use of these tools from time to time, I’ll refer back to this article as a quick primer on getting started with Adventure Works.