Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Install SQL Server 2016 Sample Database: Wide World Importers Data Warehouse


By:   |   Read Comments   |   Related Tips: More > SQL Server 2016

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

With a new release of SQL Server (SQL Server 2016) comes also a new sample database: Wide World Importers! In part 1 in this tip series, we saw how you could install and configure the OLTP database. In part 2 of this tip series, we will show you how you can do the same for the data warehouse sample database.

Solution

It's important to have already the OLTP sample database - WideWorldImporters - in place. When you create the sample data warehouse from scratch using scripts only, the data from the OLTP database will be used to populate the star schemas of the data warehouse. Make sure to go over part 1 of this tip series to have this covered.

Back-up and Restore the Wide World Importers Database

Just like the OLTP database, you can simply download the back-up files from GitHub (this URL links to the version 1.0 release) and restore them on your system. In the current release, the data ranges from January 1st, 2013 to May 31st, 2016.  In order to keep the download size reasonable, the data size is limited. The only way to change the size is to create the OLTP database using the scripts and adjust the parameters so that more random data is generated. This data is then imported into the data warehouse.

For more information on how to restore a database: How to restore a SQL Server backup.

Using Scripts to Create the Wide World Importers Data Warehouse

The most flexible option is creating the data warehouse sample database from scratch using T-SQL scripts, which can be found on Github. There are a total of 8 scripts to help you create your own copy of WWI. A prerequisite is that you already have the OLTP sample database on your system. If you create the OLTP sample database using scripts as well, you can influence the size of the sample database. This might be important, because the initial sizes of the sample databases might be too small to do performance tuning demos for example.

Script 1 - Create Metadata Database - WWI_DW_Preparation

The first script - 1-wwi-dw-metadata-population.sql - creates a metadata database called WWI_DW_Preparation.

Create Metadata Database - WWI_DW_Preparation

This database holds all the table, column and schema metadata required to create the sample data warehouse.

This database holds all the table, column and schema metadata required to create the sample data warehouse

Some table metadata:

Table metadata

After running all the scripts, this database can be removed.

Script 2 - Construct the WWI Data Warehouse

The second script - 2-wwi-dw-construct-database-from-metadata-tables.sql - will generate all of the T-SQL required to construct the WWI data warehouse. It uses the metadata created in the first script and some system metadata as well.

Construct the WWI Data Warehouse

You can see for example the data directories for the database files use the server default of your system. You need to copy paste the result and execute it in another query window in order to create the data warehouse. After executing the script you end up with the empty WWI data warehouse, called WideWorldImportersDW. In contrast with the OLTP database, there are no system versioned tables.

Empty Wide World Importers Data Warehouse

Script 3 - Optional Script to create the WideWorldImportersDW Database

Another option is to run the third script called 3-wwi-dw-recreate.sql. The script is optional because it contains the output of the second script, but this time without your system settings, as indicated in the following screenshot.

Optional Script to create the WideWorldImportersDW Database

In most cases, the best choice is to run the second script, copy-paste and execute the results instead of running the third script.

Script 4 - Create Roles, Views and Stored Procedures

The fourth script - 4-wwi-dw-configure-required-database-objects.sql - creates some extra objects, such as roles, views and stored procedures.

Create Roles, Views and Stored Procedures

Numerous stored procedures are generated. Most of them assist with the ETL process that loads data from the OLTP database to the data warehouse.

Stored Procedures Generated

Script 5 - Load the Seed Data in the WWI Database

The 5th script - 5-wwi-dw-load-seed-data.sql - loads seed data into the WWI database. This script will populate the time dimension until the year 2016. This is hardcoded, but the ETL process will insert additional dates if necessary at a later point in time. The script also loads the ETL Cutoff table, which is needed for incrementally loading the data warehouse and it will insert one dummy row into each dimension.

Load the Seed Data in the WWI Database

The cutoff table:

Cutoff table

The date dimension:

date dimension

An overview of the inserted rows by table:

Overview of Inserted Rows by Table

Script 6 - Enable Enterprise Features

The 6th script - 6-wwi-dw-enable-full-features.sql - will enable additional Enterprise features, such as partitioning, in-memory OLTP for the staging tables and Polybase. It's optional, but recommended if you want to use the Enterprise features of course.

Enable Enterprise Features

Scripts 7, 8 and 9 - Polybase Installation, Backup and Restore

I did not install Polybase on my machine, so the script skips this feature. The 8th script - 8-wwi-dw-backup.sql - and the 9th script - 9-wwi-dw-restore.sql - are optional and are respectively for backing up and restoring the WWI data warehouse.  They are not discussed any further.

At this point, the data warehouse is still pretty empty. In order to get some data into it, we need to run the ETL. On Github you can find an SSIS solution that provides you with the ability to run a daily load on the data warehouse. This solution contains a single package: DailyETLMain. A detailed treatment of the package is out of scope for this tip.

SSIS package

To populate the data warehouse, you simply need to run this package. It will check the ETL cutoff table to find out which data it has to load incrementally from the OLTP data warehouse. The 5th script has set every date in the cutoff table to the end of December 2012, guaranteeing a full load of the data warehouse (the data in the OLTP database starts at the 1st of January 2013). On my machine, the package finished in less than two minutes.

SSIS package finished

The data warehouse is now fully populated:

SSIS package finished and data warehouse fully populated

As you can see, the number of records are still fairly low, considering the average data warehouse size. The data is loaded right until the end of May 2016.

Review data in the Order Fact Table

Conclusion

SQL Server 2016 is accompanied with a brand new set of sample databases: Wide World Importers. In part 1 of the tip we saw how we could create the OLTP database. This part showed us how we could create the OLAP data warehouse and how we could populate it with data directly from the OLTP database.

Next Steps
  • Try it out yourself! You can download all the source code from Github.
  • Make sure to check out all of the sample scripts and application workflows as well!
  • Since the data warehouse uses the data of the OLTP sample database, you can use part 1 of this tip to install and configure that database.
  • For SQL Server 2016 tips, you can use this overview.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools