Sunday, October 26, 2014

Designing a Data Warehouse


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