In this blog post, you’ll find an Adventure Works DW database version with up-to-date data you can use to create demos, practice, and learn.
Table of Contents
Download the Database
Download the backup for the Adventure Works database with data from 2020 from the following link:
GitHub Link (there is a backup for SQL Server 2019 and one for SQL Server 2016)
I’ve always used the Adventure Works DW database for demos and as a reference because it gives you a lot of good artefacts to explain different Data Warehouse component.
You can also easily build reports using non-real information that make sense to your clients.
Additionally, you can use it for studying and learning.
Updating the Database
The data available in the database is outdated. Microsoft hasn’t continued adding more recent data. The maximum date in one of the fact tables was from January 2014, so I decided to run a few scripts to update the data.
Creating this new version of the database wasn’t difficult at all. I just updated the existing information to be more recent! So, if the date is 2014, add a few years… and convert it to 2020.
I had to delete some foreign keys to facilitate the modifications and compress some tables to be able to publish into Github (let me know if you have any issues).
Of course, you need to avoid some other dates like BirthDate or HireDate.
Summary
Now you have an up-to-date Adventure Works database for creating solutions and without feeling outdated!
What’s next?
In upcoming posts, I’ll continue to explore some of the great features and services available in the data analytics space within Azure services.
If you have any questions or there is anything specific that you’d like to learn about, please leave me a comment below!
6 Responses
Carlos
03 . 09 . 2020What version on SQL do I have to restore? I tried to restore to a SQL 2016 instance and it failed
David Alzamendi
04 . 09 . 2020Hi Carlos,
The original backup was created with SQL Server 2019.
I have created one backup with SQL Server 2016, you can download it from:
https://github.com/techtalkcorner/SampleDemoFiles/blob/master/Database/AdventureWorks/AdventureWorksDW2020ForSQLServer2016.bak
Let me know if you run into any issues.
Regards,
David
Carlos
11 . 09 . 2020Awesome. Thank you for providing. If I run into issues I will let you know, otherwise success!
HASAN MOSHARROF
10 . 03 . 2021When I update the dates by dropping FK constraints from FactInternateSales and other related tables
I tried to update the FK but it is not working.
David Alzamendi
11 . 03 . 2021Hi Hasan,
Can you please try run the script available in the following link? This script will take care of the FK.
https://www.techtalkcorner.com/update-adventureworks-dw-database-new-dates/
HASAN MOSHARROF
11 . 03 . 2021I have done the date updates using the new link. But still after date update there is no relationships in FactInterntSales tables Orderdate/DhipDate/DueDate key with DimDate date key.
I tried to alter the table add constraints but got the following error.
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “FK_FactInternetSales_DimDate”. The conflict occurred in database “AdventureWorksDW”, table “dbo.DimDate”, column ‘DateKey’.