PROJECTS

SQL Adventure Works Database

Skills and techniques used

Restoring database, updating database using a script, SELECT, CONCAT, CASE statement, JOINS, WHERE clause, ISNULL, LEFT, ORDER BY.

The data extracted from the database in this project is later used in a Power BI project that you can view by clicking the button bellow.
View Project

STEPS

1. The first step was to install SQL Server and SSMS (SQL Server Management studio).
2. Download AdventureWorksDW2022.bak Data Warehouse from – https://learn.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms
3. Restore the databases using Microsoft SQL Server Management Studio.
4. I updated the database using script found on link: https://github.com/techtalkcorner/SampleDemoFiles/blob/master/Database/AdventureWorks/Update_AdventureWorksDW_Data.sql
5. What do I want to do with this database? I want to extract data so I can later create a Power BI dashboard where I can overview product sales, customer data and the income that is generated after the year 2015
6. Next I cleansed the DimDate table using a SQL query to create a smaller table only with columns that I will need and set it to show only the rows after the year 2015. I later saved the query and the table to the folder that I am using for this project.
7. After that I did some cleansing of the DimCustomer table. I filtered out only the important columns for the project. – I concatenated the first and last name using CONCAT. I used a CASE expression to turn the F and M in gender to MALE and FEMALE. – I joined the DimDate table so that I could only retrieve data after the year 2015 with the help of the WHERE clause. – I also joined the DimGeography table, because it might come in handy later in the Power BI dashboard to get more information. I then saved the query and the table to the folder that I am using for this project.
8. Next I cleansed the DimProduct table. So again I selected only the columns necessary to the project. – I joined the Category and Subcategory tables so I could add the Product Category and Subcategory column – I used the ISNULL function to fill the empty cells for the Descriprion and Status column. I later saved the query and the table to the folder that I am using for this project.
9. Lastly I cleansed the FactInternetSales table to extract only the necessary collumns. – I used the LEFT function to extract the year from the OrderDateKey column. – I used the WHERE clause to extract only data after the year 2015 – I used ORDER BY to sort the data by OrderDateKey ascending
Scroll to Top