Homework Session 3

For this homework I created documentation on how I would name a DataWarehouse based on the NorthwindLite Database. I have changed some of the data types in my Datawarehouse design to better represent the data that is being stored.

Below is the source table names and data types provided from the NorthwindLite DB:

 

Source	Source Type
NorthwindLite	Database
NorthwindLite.dbo.Orders	Table
NorthwindLite.dbo.Orders.OrderID	int
NorthwindLite.dbo.Orders.CustomerID	nchar(5)
NorthwindLite.dbo.Orders.OrderDate	datetime
NorthwindLite.dbo.OrderDetails	Table
NorthwindLite.dbo.OrderDetails.OrderID	int
NorthwindLite.dbo.OrderDetails.ProductID	int
NorthwindLite.dbo.OrderDetails.UnitPrice	money
NorthwindLite.dbo.OrderDetails.Quantity	smallint
NorthwindLite.dbo.Categories	Table
Generated	na
NorthwindLite.dbo.Categories.CategoryID	int
NorthwindLite.dbo.Categories.CategoryName	nvarchar(15)
NorthwindLite.dbo.Products	Table
Generated	na
NorthwindLite.dbo.Products.ProductID	int
NorthwindLite.dbo.Products.ProductName	nvarchar(40)
NorthwindLite.dbo.Products.CategoryID	int
NorthwindLite.dbo.Customers	Table
Generated	na
NorthwindLite.dbo.Customers.CustomerID	nchar(5)
NorthwindLite.dbo.Customers.CompanyName	nvarchar(40)
NorthwindLite.dbo.Customers.ContactName	nvarchar(30)
NorthwindLite.dbo.Customers.Address	nvarchar(60)
NorthwindLite.dbo.Customers.City	nvarchar(15)
NorthwindLite.dbo.Customers.Country	nvarchar(15)

Below, you can see that I changed all of the nvarchar data types to a single type of nvarchar(40). This was done to simplify the amount of different data types in the datawarehouse. As the type is nvarchar, the database will reduce the amount of space used to store the data depending on the length. I have also changed the OrderDate from “datetime” to “date” as the source database was not actually recording any time other than midnight. Doing this will save a significant amount of space in the datawarehouse. I am also storing the Generated columns in the dimension tables to a Key column as a data type of int.

Initial datawarehouse design for the NorthwindLite database:

Object Name	Description	Destination Type
DWNorthwindLite	Data Warehouse	Database
DWNorthwindLite.dbo.FactOrders	Fact Table	Table
DWNorthwindLite.dbo.FactOrders.OrderID	Dimension Key Column	int
DWNorthwindLite.dbo.FactOrders.CustomerID	Dimension Key Column	nchar(5)
DWNorthwindLite.dbo.FactOrders.OrderDate	Dimension Key Column	date
DWNorthwindLite.dbo.FactOrderDetails	Fact Table	Table
DWNorthwindLite.dbo.FactOrderDetails.OrderID	Dimension Key Column	int
DWNorthwindLite.dbo.FactOrderDetails.ProductID	Dimension Key Column	int
DWNorthwindLite.dbo.FactOrderDetails.UnitPrice	Measure Column	money
DWNorthwindLite.dbo.FactOrderDetails.Quantity	Measure Column	smallint
DWNorthwindLite.dbo.DimCategories	Dimension Table	Table
DWNorthwindLite.dbo.DimCategories.CategoryKey	Dimension Key Column	int
DWNorthwindLite.dbo.DimCategories.CategoryID	Dimension  Column	int
DWNorthwindLite.dbo.DimCategories.CategoryName	Dimension Column	nvarchar(40)
DWNorthwindLite.dbo.DimProducts	Dimension Table	Table
DWNorthwindLite.dbo.DimProducts.ProductKey	Dimension Key Column	int
DWNorthwindLite.dbo.DimProducts.ProductID	Dimension  Column	int
DWNorthwindLite.dbo.DimProducts.ProductName	Dimension Column	nvarchar(40)
DWNorthwindLite.dbo.DimProducts.CategoryID	Dimension Column	int
DWNorthwindLite.dbo.DimCustomers	Dimension Table	Table
DWNorthwindLite.dbo.DimCustomers.CustomerKey	Dimension Key Column	int
DWNorthwindLite.dbo.DimCustomers.CustomerID	Dimension Column	nchar(5)
DWNorthwindLite.dbo.DimCustomers.CompanyName	Dimension Column	nvarchar(40)
Not Used	Dimension Column	na
Not Used	Dimension Column	na
DWNorthwindLite.dbo.DimCustomers.City	Dimension Column	nvarchar(40)
DWNorthwindLite.dbo.DimCustomers.Country	Dimension Column	nvarchar(40)