For the lesson three homework, we were asked to design a
data warehouse for the NorthwindLite OLTP database. Quite a few changes were
made, but the two large changes to the source data were the addition of a date
table and the normalization of the Products and Categories tables.
I made
no changes to the naming convention at all. I felt that the naming convention
that was used for the NorthwindLite database was intuitive and held up to the
industry standard of naming conventions. The only changes that were made to any
of the names was the addition of the data warehouse table type. I added a fact
prefix for the Orders and OrderDetails table and a Dim prefix for the Products,
Customers, and Dates tables.
This is shown below:
|
DWNorthwindLite.dbo.FactOrders
|
Fact Table
|
NorthwindLite.dbo.Orders
|
|
DWNorthwindLite.dbo.FactOrders.OrderID
|
Dimension Key Column
|
NorthwindLite.dbo.Orders.OrderID
|
|
DWNorthwindLite.dbo.FactOrders.CustomerID
|
Dimension Key Column
|
NorthwindLite.dbo.Orders.CustomerID
|
|
DWNorthwindLite.dbo.FactOrders.OrderDate
|
Dimension Key Column
|
NorthwindLite.dbo.Orders.OrderDate
|
I kept most of the data types, but didn't keep all of them.
|
DWNorthwindLite.dbo.FactOrders.OrderDate
|
Dimension Key Column
|
NorthwindLite.dbo.Orders.OrderDate
|
datetime
|
int
|
For the order date, this was changed from a datetime data
type to an int, because of the addition of the Date table. The two tables will
be linked using an int primary key.
Here are two other data type changes that I made:
|
DWNorthwindLite.dbo.FactOrderDetails.UnitPrice
|
Measure Column
|
NorthwindLite.dbo.OrderDetails.UnitPrice
|
money
|
decimal (18,4)
|
|
DWNorthwindLite.dbo.FactOrderDetails.Quantity
|
Measure Column
|
NorthwindLite.dbo.OrderDetails.Quantity
|
smallint
|
int
|
I chose to change the money data type to a decimal. This was
done to make reporting easier by changing the data type to a more universal
format. I chose to change the smallint to an int, because the smallint data type
is unnecessary. An int will be able to serve the same purpose and also keep
things more uniform.
I chose to keep all of the other data types the same, since
there was no reason to change it. I did consider changing the nvarchar lengths
in the Customers table, but after looking at the data I decided that they were
set to those lengths for a purpose. For example, the CustomerID data was a
standardized five character code, so I kept it at five in the data warehouse.
|
NorthwindLite.dbo.Customers.CustomerID
|
nchar(5)
|
nchar(5)
|
One major change that I did, was combining the Categories
table and the Products table into one. This was done to make reporting easier.
Knowing the category and product type all at the same time will be more
beneficial than the benefits to the performance gained from eliminating the
redundancy.
|
DWNorthwindLite.dbo.DimCategories
|
Dimension Table
|
NorthwindLite.dbo.Categories
|
Table
|
Table
|
|
N/A
|
Dimension Key Column
|
Generated
|
na
|
N/A
|
|
N/A
|
Dimension Column
|
NorthwindLite.dbo.Categories.CategoryID
|
int
|
N/A
|
|
N/A
|
Dimension Column
|
NorthwindLite.dbo.Categories.CategoryName
|
nvarchar(15)
|
N/A
|
|
DWNorthwindLite.dbo.DimProducts
|
Dimension Table
|
NorthwindLite.dbo.Products
|
Table
|
Table
|
|
DWNorthwindLite.dbo.DimProducts.ProductKey
|
Dimension Key Column
|
Generated
|
na
|
int
|
|
DWNorthwindLite.dbo.DimProducts.ProductID
|
Dimension Column
|
NorthwindLite.dbo.Products.ProductID
|
int
|
int
|
|
DWNorthwindLite.dbo.DimProducts.ProductName
|
Dimension Column
|
NorthwindLite.dbo.Products.ProductName
|
nvarchar(40)
|
nvarchar(40)
|
|
DWNorthwindLite.dbo.DimProducts.CategoryID
|
Dimension Column
|
NorthwindLite.dbo.Products.CategoryID
|
int
|
int
|
|
DWNorthwindLite.dbo.DimProducts.ProductCategory
|
Dimension Column
|
N/A
|
N/A
|
nvarchar(15)
|
The final major change that I did to the data warehouse was
the addition of a date table.
|
DWNorthwindLite.dbo.DimDates
|
Dimension Table
|
na
|
na
|
Table
|
|
DWNorthwindLite.dbo.DimDates.DateKey
|
Dimension Key Column
|
na
|
na
|
int
|
|
DWNorthwindLite.dbo.DimDates.Date
|
Dimension Column
|
na
|
na
|
datetime
|
|
DWNorthwindLite.dbo.DimDates.DateName
|
Dimension Column
|
na
|
na
|
nvarchar(50)
|
|
DWNorthwindLite.dbo.DimDates.Month
|
Dimension Column
|
na
|
na
|
int
|
|
DWNorthwindLite.dbo.DimDates.MonthName
|
Dimension Column
|
na
|
na
|
nvarchar(50)
|
|
DWNorthwindLite.dbo.DimDates.Quarter
|
Dimension Column
|
na
|
na
|
int
|
|
DWNorthwindLite.dbo.DimDates.QuarterName
|
Dimension Column
|
na
|
na
|
nvarchar(50)
|
|
DWNorthwindLite.dbo.DimDates.Year
|
Dimension Column
|
na
|
na
|
int
|
|
DWNorthwindLite.dbo.DimDates.YearName
|
Dimension Column
|
na
|
na
|
nvarchar(50)
|
Due to the fact that we will be tracking the orders on a
daily basis, the addition of this table will benefit the data warehouse
greatly. We will be able to track the orders in more detail and also retain
performance by separating it out into its own table. As spoke about in class
and in the book, all data warehouses can benefit from a date table. The DateKey
will be an int, so that it can create a key relationship with the order table.
No comments:
Post a Comment