Lab 02: Designing and Implementing a Data Warehouse

« …\LABS-Atelier SID\Lab02 » folder which will change the owner of the AWDataWarehouse to the

user ‘sa’.

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.

2.

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

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

the DimProductCategory.sql query

the

file

in

in

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

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.

contains a dimension

snowflake

schema

that

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.