AW-1: Obtaining, Loading and Querying Adventure Works Database

Introduction

Hello and welcome to the first in a series of tutorials utilising Microsoft’s Adventure Works database samples. The focus of these tutorials will be on database and analytics concepts and software. In particular, we will look at performing queries and other important tasks with SQL Server Management Studio, interacting with databases with python, creating Power BI reports and many other topics.

As you may already know, SQL server is not a database it is database management system. Microsoft does, however, have several sample databases that can be freely downloaded. These are very useful when coming to grips with database concepts and also Microsoft’s database and analytics software, e.g., SQL Server, SQL Server Management Studio, Power BI, SSIS, and so on.

In this series of tutorials we will be taking a close look at the Adventure Works databases. Adventure Works is a fictitious bicycle company (Adventure Works Cycles). The AdventureWorks database is the Online Transaction Processing (OLTP) sample database and AdventureWorksDW data warehouse is the Online Analytics Processing (OLAP) sample. The OLTP database sample supports standard online transaction processing scenarios for the company, e.g., manufacturing, sales, purchasing,
product management, and human resources, while the OLAP data warehouse is set up for related analytics scenarios. Over the years new versions of these databases have been released making them compatible with newer features of SQL server. Although, these previous databases a not that dissimilar to the current versions. In these tutorials, we will be using the AdventureWorks2017 and AdventureWorksDW2017 databases. To take advantage of SQL server’s more recent features and Azure SQL Database, one should look into the World Wide Importer database samples.

A typical company will generally have many OLTP systems which support their day to day business. These OLTP systems are usually highly normalised. Data from these systems is quite often Extracted, Transformed and then Loaded (ETL) into OLAP systems. These systems tend to have less normalised tables, usually implementing star and/or snowflake schemas. We will discuss these details in future tutorials. In our first tutorial our aim is to simply obtain and load these two databases into SQL Server using SQL Server Management Studio and then perform a simple query.

Obtaining

The first thing to do is to obtain the back up files for these Adventure Works databases. The Adventure Works samples can be obtained from Microsoft’s documentation pages or their GitHub repository. Simply click on and download AdventureWorks2017.bak and AdventureWorksDW2017.bak.

Place these backup files in

 C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup

Loading

Now open SQL Server Management Studio. You should see the screen shown below.

Set Server type to Database engine, select your server name and chose your authentication method. If you are not sure about this, a typical default for a personal setup is Windows Authentication. Then finally click connect.

Now in Object Explorer window right click on Databases and click on Restore Database.

Under Source select device, then click on the ellipsis (the 3 dots).

Click Add.

Navigate to the backup folder and select AdventureWorks2017 and click ok.

Click ok.

Click ok again.

Now AdventureWorks2017 database has been added. Click ok and repeat the previous steps to add AdventureWorksDW2017.

Querying with SQL Server

Now that we have some databases added to SQL Server. Expand the Databases tree by clicking on the plus sign to see the available databases, then expand the AdventureWorks2017 database and then expand the Tables folder. This will allow you to see the many tables in the database.

To write a query in SQL Server click on New Query.

Before you write your query go to Edit > IntelliSense > Refresh Local Cache. This will update the autocompletion for the new databases you have just added.

Now you can writer your very first query. In the new query text editor window type

USE AdventureWorks2017;
SELECT * FROM HumanResources.Employee;

Click execute.

This query simply displays the contents of the Employee table, which appears in the window at the bottom of the screen.

You can similarly query the AdventureWorksDW2017 data warehouse in the same way, but notice that the list of tables is quite different. The OLAP system uses a dimensional model with many fact and dimension tables. This will be discussed in later tutorials. In our next tutorial we will query this database using python.

Conclusion

In this tutorial we looked at obtaining, loading and querying the Adventure Works OLTP and OLAP databases. We have barely scratched the surface of what’s in and what can be done these databases. In subsequent tutorials we will make extensive use of these databases to practice writing SQL queries, creating reports with Power BI and much more.

Introduction

Obtaining

Loading

Querying with SQL Server

Conclusion

Further Reading