LAB 02 : Designing and implementing a Data Warehouse
Scenario
You have gathered analytical and reporting requirements from stakeholders at Adventure Works Cycles. Now
you must implement a data warehouse schema to support them.
Objectives
After completing this lab, you will be able to :
• Implement a dimensional star schema.
• Implement a snowflake schema.
• Implement a time dimension.
Estimated Time : 45 Minutes
Exercise 1 : Implement a Star Schema
Scenario
Adventure Works Cycles requires a data warehouse to enable information workers and executives to create
reports and perform analysis of key business measures. The company has identified two sets of related
measures that it wants to include in fact tables. These are separate sales order measures relating to sales to
resellers, and Internet sales. The measures will be aggregated by product, reseller (in the case of reseller sales),
and customer (for Internet sales) dimensions.
The data warehouse has been partially completed, and you must now add the necessary dimension and fact
tables to complete a star schema.
The main tasks for this exercise are as follows:
1. Prepare the Lab Environment
2. View a Data Warehouse Schema
3. Create a Dimension Table
4. Create a Fact Table
5. View the Revised Data Warehouse Schema
Task 1 : Prepare the Lab Environment
1. Start SQL Server Management Studio and connect to the (local) instance of the SQL Server database
engine by using Windows authentication.
2. Open and review the Transact-SQL code in the SetupLab02.sql file in the « …\LABS-Atelier
SID\Lab02\SetupFiles » folder.
3. Execute the query to create the AWDataWarehouse database.
Task 2 : View a Data Warehouse schema
1. Create a new database diagram in the AWDataWarehouse database (creating the required objects
to support database diagrams if prompted). The diagram should include all the tables in the database.
To be able de do that, you should execute the Transact-SQL code in the ChangeOwner.sql file in the
« …\LABS-Atelier SID\Lab02 » folder which will change the owner of the AWDataWarehouse to the
user ‘sa’.
2. In the database diagram, modify the tables so they are shown in standard view, and arrange them so
you can view the partially complete data warehouse schema.
3. Save the database diagram as AWDataWarehouse Schema.
Task 3 : Create a Dimension Table
1. Review the Transact-SQL code in the DimCustomer.sql query file in the « LABS-Atelier SID\ Lab02\
» folder. Note that it creates a table named DimCustomer in the AWDataWarehouse database.
2. Execute the query to create the DimCustomers dimension table.
Task 4 : Create a Fact Table
1. Review the Transact-SQL code in the FactInternetSales.sql query file in the « LABS-Atelier SID\
Lab02\ » folder. Note that it creates a table named FactInternetSales in the AWDataWarehouse
database,and that this table is related to the DimCustomer and DimProduct tables.
2. Execute the query to create the FactInternetSales dimension table.
Task 5 : View the Revised Data Warehouse Schema
1. Add the tables that you have created in this exercise to the database diagram that you created.
Note: When adding tables to a diagram, you need to click Refresh in the Add Table dialog box to see
tables you have created or modified since the diagram was initially created.
2. Save the database diagram.
3. Keep SQL Server Management Studio open for the next exercise.
Results: After this exercise, you should have a database diagram in the AWDataWarehouse database that
shows a star schema consisting of two fact tables (FactResellerSales and FactInternetSales) and four
dimension tables (DimReseller, DimEmployee, DimProduct, and DimCustomer).
Exercise 2 : Implementing a Snowflake Schema
Scenario
Having created a star schema, you have identified two dimensions that would benefit from being normalized
to create a snowflake schema. Specifically, you want to create a hierarchy of related tables for product
category, product subcategory, and product. You also want to create a separate geography dimension table
that can be shared between the reseller and customer dimensions.
The main tasks for this exercise are as follows:
1. Create Dimension Tables That Form a Hierarchy
2. Create a Shared Dimension table
3. View the Data Warehouse Schema
Task 1 : Create Dimension Tables That Form a Hierarchy
1. Review the Transact-SQL code in the DimProductCategory.sql query file in the
D:\Labfiles\Lab03\Starter folder. Note that it:
• Creates a table named DimProductCategory.
• Creates a table named DimProductSubcategory that has a foreign-key relationship to the
DimProductCategory table.
• Drops the ProductSubcategoryName and ProductCategoryName columns from the
DimProduct table.
• Adds a ProductSubcategoryKey column to the DimProduct table that has a foreign-key
relationship to the DimProductSubcategory table.
2. Execute the query to create the dimension tables.
Task 2 : Create a Shared Dimension table
1. Review the Transact-SQL code in the DimGeography.sql query file in the « LABS-Atelier SID\
Lab02\ » folder. Note that it:
• Creates a table named DimGeography.
• Drops the City, StateProvinceName, CountryRegionCode, CountryRegionName, and
PostalCode columns from the DimReseller table.
• Adds a GeographyKey column to the DimReseller table that has a foreign-key relationship to
the DimGeography table.
• Drops the City, StateProvinceName, CountryRegionCode, CountryRegionName, and
PostalCode columns from the DimCustomer table.
• Adds a GeographyKey column to the DimCustomer table that has a foreign-key relationship to
the DimGeography table.
2. Execute the query to create the dimension table.
Task 3 : View the Data Warehouse Schema
1. Delete the tables that you modified in the previous two tasks from the AWDataWarehouse Schema
diagram (DimProduct, DimReseller, and DimCustomer).
2. Add the new and modified tables that you created in this exercise to the AWDataWarehouse
Schema diagram and view the revised data warehouse schema, which now includes some snowflake
dimensions. You will need to refresh the list of tables when adding tables and you may be prompted
to update the diagram to reflect foreign-key relationships.
3. Save the database diagram.
Results: After this exercise, you should have a database diagram in the AWDataWarehouse database
showing a snowflake schema that contains a dimension consisting of a DimProduct,
DimProductSubcategory, and DimProductCategory hierarchy of tables, as well as a DimGeography
dimension table that is referenced by the DimCustomer and DimReseller dimension tables.
Exercise 3 : Implementing a Time Dimension Table
Scenario
The schema for the Adventure Works data warehouse now contains two fact tables and several dimension
tables. However, users need to be able to analyze the fact table measures across consistent time periods. To
enable this, you must create a time dimension table.
Users will need to be able to aggregate measures across calendar years (which run from January to December)
and fiscal years (which run from July to June). Your time dimension must include the following attributes:
• Date (this should be the business key).
• Day number of week (for example 1 for Sunday, 2 for Monday, and so on).
• Day name of week (for example Sunday, Monday, Tuesday, and so on).
• Day number of month.
• Day number of year.
• Week number of year.
• Month name (for example, January, February, and so on).
• Month number of year (for example, 1 for January, 2 for February, and so on).
• Calendar quarter (for example, 1 for dates in January, February, and March).
• Calendar year.
• Calendar semester (for example, 1 for dates between January and June).
• Fiscal quarter (for example, 1 for dates in July, August, and September).
• Fiscal year.
• Fiscal semester (for example, 1 for dates between July and December).
The main tasks for this exercise are as follows:
1. Create a Time Dimension Table
2. View the Database Schema
3. Populate the Time Dimension Table
Task 1 : Create a Time Dimension Table
1. Review the Transact-SQL code in the DimDate.sql query file in the « LABS-Atelier SID\ Lab02\ »
folder. Note that it:
• Creates a table named DimDate.
• Adds OrderDateKey and ShipDateKey columns to the FactInternetSales and
FactResellerSales tables that have foreign-key relationships to the DimDate table.
• Creates indexes on the OrderDateKey and ShipDateKey foreign-key fields in the
FactInternetSales and FactResellerSales tables.
2. Execute the query to create the dimension table.
Task 2 : View the Database Schema
1. Delete the tables that you modified in the previous two tasks from the AWDataWarehouse
Schema diagram (FactResellerSales and FactInternetSales).
2. Add the new and modified tables that you created in this exercise to the AWDataWarehouse
Schema diagram and view the revised data warehouse schema, which now includes a time
dimension table named DimDate. You will need to refresh the list of tables when adding tables and
you may be prompted to update the diagram to reflect foreign-key relationships.
3. Save the database diagram.
Task 3 : Populate the Time Dimension Table
1. Review the Transact-SQL code in the GenerateDates.sql query file in the « LABS-Atelier SID\ Lab02\ »
folder. Note that it:
• Declares a variable named @StartDate with the value 1/1/2000, and a variable named
@EndDate with the value of the current date.
• Performs a loop to insert appropriate values for each date between @StartDate and @EndDate
into the DimDate table.
• Execute the script to create the dimension table.
• When the query has completed, query the DimDate table to verify that it now contains time
values. Use the script bellow :
use AWDataWarehouse
select *
from dbo.DimDate;
go
2. Close Visual Studio, saving your work if prompted.
Results: After this exercise, you should have a database that contains a DimDate dimension table that is
populated with date values from January 1, 2000, to the current date.
