Friday, July 1, 2016

SQL Server 2016 Sample Database - Wide World Importers- Restoring and adding more records

We have been using Adventure Works database with almost all versions of SQL Server and no questions, it can be used with SQL Server 2016 as well. Everyone likes Adventure Works but when a complex testing over functionalities is required, Adventure Works is considered as a simple database and many tend to use some other databases instead of Adventure Works. One popular database was Contoso database which was available but for most of cases, the size was not enough.

For SQL Server 2016, Microsoft introduces a new database called Wide World Importers. This database covers many new features of SQL Server and it allows you to load data to current date. Not only that, you decide how many Customer Orders you need per day to increase the volume.

Here is the way of adding Wide World Importers databases.

WideWorldImporters Database
Go to this link and download WideWorldImporters-Full.bak file. Restore it either using TSQL or GUI.

USE master
RESTORE DATABASE WideWorldImporters
FROM  DISK = N'D:\Software\SQL Server 2016\WideWorldImporters-Full.bak' 
WITH  FILE = 1,  
MOVE N'WWI_Primary' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\WideWorldImporters.mdf',  
MOVE N'WWI_UserData' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\WideWorldImporters_UserData.ndf',  
MOVE N'WWI_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\WideWorldImporters.ldf',  
MOVE N'WWI_InMemory_Data_1' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\WideWorldImporters_InMemory_Data_1',  
NOUNLOAD,  STATS = 5
GO

Once restored, you should see the database in your instance. If you query Sales.Orders table, you should see 73,595 records. These orders are populated till 2016-05-31.


If you want to add more data to the current data, you can call DataLoadSimulation.PopulateDataToCurrentDate stored procedure with relevant parameter values as below.

DECLARE @return_value int

EXEC @return_value = [DataLoadSimulation].[PopulateDataToCurrentDate]
  @AverageNumberOfCustomerOrdersPerDay = 80,
  @SaturdayPercentageOfNormalWorkDay = 30,
  @SundayPercentageOfNormalWorkDay = 5,
  @IsSilentMode = 0,
  @AreDatesPrinted = 0

SELECT 'Return Value' = @return_value

You can simply have a larger number of @AverageNumberOfCustomerOrdersPerDay if you need more records.

WideWorldImportersDW database
If you need the relational data warehouse related to Wide World Importers, then you need to download the WideWorldImportersDW-Full.bak from same page. Restore it just like the previous database using following script;

USE master
GO

RESTORE DATABASE WideWorldImportersDW
FROM  DISK = N'D:\Software\SQL Server 2016\WideWorldImportersDW-Full.bak' 
WITH  FILE = 1,  
MOVE N'WWI_Primary' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\WideWorldImportersDW.mdf',  
MOVE N'WWI_UserData' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\WideWorldImportersDW_UserData.ndf',  
MOVE N'WWI_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\WideWorldImportersDW.ldf', 
MOVE N'WWIDW_InMemory_Data_1' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\WideWorldImportersDW_InMemory_Data_1',  
NOUNLOAD,  STATS = 5
GO

If you have repopulated WideWorldImporters database using PopulateDataToCurrentDate, then you need to transfer new data to the data warehouse. You do not need to manually load it, you can use a given package for populating the data warehouse.

Download Daily.ETL.ispac from the same page and add it to Integration Services catalog by running the file. Once it is added, you should see the package;


Right-click on the package and execute it for updating the data warehouse. Make sure connection string set are correct as per your environment. If not, change it and execute it.


Once executed, your data warehouse is updated based on new data added to WideWorldImporters database.


2 comments:

  1. Hi Dinesh, Wanted to know if there is any documentation or er diagram representing the WideWorldImporters DB dump? I mean to work on a database you need to understand the data and the relationship between them. So any such documentation for this sample database?

    ReplyDelete
  2. Hi Keshav,

    Sorry, I have not come across any documents on this but diagram can be generated once it is restored. This database is not complex, hence once the diagram is created, you will be easily understand it.

    This is the only page that explains little bit about the DB: https://msdn.microsoft.com/library/mt734199(v=sql.1).aspx

    ReplyDelete