By: Koen Verbeeck | Updated: 2016-08-15 | Comments (2) | Related: > SQL Server 2016
Problem
With a new release of SQL Server (SQL Server 2016) comes also a new sample database. The days of AdventureWorks are over, here's Wide World Importers! This tip gives an overview of how you can install these new sample databases on your system.
Solution
Wide World Importers (WWI) is the new SQL Server sample database. It aims to give a demonstration of the SQL Server 2016 capabilities and performance enhancements. It doesn't try to embed all possible SQL Server 2016 features, but rather goes for a more realistic approach.
From MSDN:
Wide World Importers (WWI) is a wholesale novelty goods importer and distributor operating from the San Francisco bay area. As a wholesaler, WWI’s customers are mostly companies who resell to individuals. WWI sells to retail customers across the United States including specialty stores, supermarkets, computing stores, tourist attraction shops, and some individuals. WWI also sells to other wholesalers via a network of agents who promote the products on WWI’s behalf. While all of WWI’s customers are currently based in the United States, the company is intending to push for expansion into other countries. WWI buys goods from suppliers including novelty and toy manufacturers, and other novelty wholesalers. They stock the goods in their WWI warehouse and reorder from suppliers as needed to fulfill customer orders. They also purchase large volumes of packaging materials, and sell these in smaller quantities as a convenience for the customers. Recently WWI started to sell a variety of edible novelties such as chilly chocolates. The company previously did not have to handle chilled items. Now, to meet food handling requirements, they must monitor the temperature in their chiller room and any of their trucks that have chiller sections.
Check out the overview page on MSDN to find out more about the workflows for Wide World Importers and other useful information.
Right now, you can download the latest release from GitHub. It's licensed under the MIT license.
The WWI sample databases come in two flavors:
- WideWorldImporters - the OLTP sample database. It can also
be used to demonstrate operational analytics. Some of the key features that
can be used:
- Nonclustered columnstore indexes for operational real-time analytics
- Query Store to keep track of query performance
- Historical data is maintained using temporal tables
- JSON integration
- Security features like row level security, data masking and Always Encrypted
- In-memory OLTP to optimize performance
- Clustered columnstore indexes for large tables with an insert-only workload
- Partitioning
- WideWorldImportersDW - the OLAP data warehouse for WWI.
The data is derived from the WideWorldImporters database. A couple examples
of the key features:
- Clustered columnstore indexes to reduce the size of large tables and to enhance performance
- Polybase to correlate data with a public data set in Azure Blob storage
- In-memory OLTP to improve the performance of the ETL process
- Partitioning
Not all of the features are automatically included. The release comes with a set of sample scripts to allow you to explore the new features. There's also the possibility to enable only features included in the Standard edition.
What sets WWI apart from AdventureWorks is that you can keep the data up to date. With a script, you can refresh the data in the WideWorldImporters database up to the current date. Using the provided ETL flow, you can then refresh the data warehouse as well. You can schedule those scripts to simulate a daily load. The data inside the databases is also not fixed, but randomly generated resulting in a realistic set of data.
In the first part of the tip, we'll focus on the installation of the OLTP database.
Back-up and Restore
The easiest method to install the sample databases is to 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. You can use a script to load data until the current date. More information on this will be provided at the end of the tip. There are three download options:
- Full backups: these are the backups intended for SQL Server 2016 Enterprise, Developer or Evaluation edition.
- Standard backups: these are the backups intended for SQL Server 2016 Standard edition. These backups have less features enabled.
- Bacpac for Azure SQL Database. This is Standard Edition.
In order to keep the download size reasonable, the data size is limited. Using the scripts explained in the following section, you can create a larger sample database.
For more information on how to restore a database: How to restore a SQL Server backup.
Creating WideWorldImporters from scratch
Another option to install the sample database is creating it from scratch using T-SQL scripts. The scripts can be found on Github. There are a total of 9 scripts to help you create your own copy of WWI. What's interesting is that data will be randomly generated and you can influence the data size and distribution. You can also choose to date until which the data is generated.
The first script - 1-wwi-metadata-population.sql - creates a metadata database called WWI_Preparation holding all the table, column and schema metadata required to create the sample database.
Some table metadata:
After running all the scripts, this database can be removed.
The second script - 2-wwi-construct-database-from-metadata-tables.sql - will generate all of the T-SQL required to construct the WWI database. It uses the metadata created in the first script and some system metadata as well.
In the result, you can see for example that 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 WWI database. Another option is to run the third script called 3-wwi-recreate.sql. This script is optional, as it contains the output of the second script but this time without your system settings.
In most cases, the best choice is to run the second script, copy-paste and execute the results instead of running the third script. Anyway, after running either of the scripts you end up with the empty WWI database. As you can see, it contains quite some system-versioned tables.
A couple of stored procedures have been created as well:
It's possible you get some warnings when those stored procedures have been created. You can however ignore them.
The fourth script - 4-wwi-configure-required-database-objects.sql - does some extra configuration, such as creating roles and enabling row level security. Again, some warnings may pop-up, but these can be ignored.
The 5th script - 5-wwi-load-seed-data.sql - loads seed data into the WWI database. This data will be used in a later script to randomly generate the rest of the data. On my machine, it took about 2 minutes and 40 seconds to insert the data.
An overview of the inserted rows by table:
Using the inserted rows from the previous script, sample data is generated in the 6th script called 6-wwi-data-simulation.sql. This is the only script so far where you need to change something, depending on your requirements.
By tweaking the following parameters, you can influence the data size and the distribution of the data:
- @StartDate and @EndDate: use these parameters to set the range for which data is generated. The default start date is the first of January 2013 and the default end date (at the moment) is the 31st of May 2016.
- @AverageNumberOfCustomerOrdersPerDay: setting this to a higher value will lead to more data being generated for the sample database. It will also make the script run considerably longer. Setting it to a lower value results in a more lightweight sample database. The default is an average of 60 orders per day (weekend not included).
- @SaturdayPercentageOfNormalWorkDay and @SundayPercentageOfNormalWorkDay: the respective percentages of the average number of customer orders (set by the @AverageNumberOfCustomerOrdersPerDay parameter). The default values are 50% and 0%. In the example here I set the percentage for Sunday to 25%, so more data would be generated.
If the @AreDatesPrinted parameter is set to 1, the script will output each day for which data has been generated, allowing you to follow the status. On average the script is supposed to run for about 40 minutes. On my system it took over 52 minutes, which is explained by the increased order count for Sundays.
After the script has completed, the following row counts can be found:
The 7th script - 7-wwi-enable-full-features.sql - is optional and allows you to turn on Enterprise only features. It's recommended though if you do want to use the Enterprise features. It will enable columnstore indexes, partitioning, in-memory OLTP (tables and procedures) and create full-text indexes.
On my machine, full-text indexing was not enabled, so those were skipped. The 8th script - 8-wwi-backup.sql - and the 9th script - 9-wwi-restore.sql - are optional and are respectively for backing up and restoring the WWI database.
Let's take a quick look at the distribution of the data in one of the biggest tables, Sales.Orders.
The top left chart gives a distinct count of order number by day of the week. We can indeed see Saturday is about 50% of a normal weekday and Sunday is about 50% of the Saturdays. The top right chart shows the number of distinct order numbers by month. The year 2016 is left out as it is still incomplete - there's only data for January 2016 till May 2016 - and you can see the data is fairly even distributed. Months with more days get on average more orders. The bottom chart finally displays the number of distinct customers per year and we can see is has been slowly been growing since 2013.
Conclusion
SQL Server 2016 is accompanied with a brand new set of sample databases: Wide World Importers. These databases are more realistic in their approach than AdventureWorks. A very nice new feature is that you can create the database yourself and choose the data size. You can also create the sample data up to the current date. This first part gave an overview of how to create the WWI database on your system. The second part will show you how to create the OLAP 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!
- Stay tuned for part 2 of this tip!
- For SQL Server 2016 tips, you can use this overview.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2016-08-15
