Setting up the AdventureWorks DW sample database

Now that the instance is up and running, we can load the data into it. First, we'll create a database that will be populated with sample data.

Getting ready

You need to have completed the previous recipe to continue, and then unzip the AdventureWorks 2008R2 Data Warehouse.zip file into the folder C:\install\, so that you will end up with a folder named AdventureWorks 2008R2 Data Warehouse containing a series of SQL script files; the main being named instawdwdb.sql.

How to do it...

Before running this script we need to modify it, in order to specify the name of the source folder containing the CSV files and the name of the destination folder that will store the resulting database:

1. Create a directory named C:\datawarehouse; this will be the location of our database.

2. Open in Notepad the file instawdwdb.sql and modify lines 36 and 37 so that they look like:

   :setvar SqlSamplesDatabasePath   "C:\datawarehouse\"
   :setvar SqlSamplesSourceDataPath "C:\install\AdventureWorks 2008R2 Data Warehouse\"

   Tip

   The two hyphens at the beginning of the lines (comment marks) have been removed and the path to data folder and source scripts folder have been modified, they both include the trailing backslash.

3. Save the file and close Notepad.

4. Open a command prompt and enter the following command:

   sqlcmd -S (localdb)\v11.0 –i "C:\install\AdventureWorks 2008R2 Data Warehouse\instawdwdb.sql"
   

   Tip

   This command must be written all in one line and including the double quotes.

How it works...

The script will create the database and all the needed tables. Then will import the CSV data files into the tables to populate them with data. The script will lastly exit the sqlcmd command interface.

There's more...

In order to check if everything was installed correctly, we will perform a couple of very simple SQL queries on the database:

1. Open the sqlcmd utility again:

   sqlcmd -S (localdb)\v11.0
   

2. At the 1> prompt, write the following instructions each on its own line, followed by the Enter key:

   use AdventureWorksDW2008R2
   go
   select count(1) from FactInternetSales
   go
   select count(1) from DimCustomer
   go
   exit
   

The database will respond with the number of rows in the main fact table and the customer dimension table, 60398 and 18484 respectively.

Congratulations, you correctly installed the data warehouse source database!

Note

You can watch a screencast of this operation at:

• http://at5.us/Ch1V4

Exercise 1

From the sqlcmd utility execute the queries to count the number of records in the following tables: FactCurrencyRate, FactResellerSales, DimGeography, DimDate, and DimCurrency. Note down the numbers.