BI: Building the Data Warehouse Lesson 4

Building a New Data Warehouse to PM Specifications
Korry Kelley | Lesson 4

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After studying the PMs specifications I was discern between what tables, fields and field types were required for the clients Data Warehouse.

 

 

USE [MASTER]
GO
If Exists (Select * from Sysdatabases Where Name = 'DWNorthwindLite')
 Begin
  ALTER DATABASE [DWNorthwindLite] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
  DROP DATABASE [DWNorthwindLite]
 End
GO
Create Database [DWNorthwindLite]
Go

--********************************************************************--
-- Create the Tables
--********************************************************************--
USE [DWNorthwindLite]

Go
/****** [dbo].[DimProducts] ******/
Create Table DimProducts
(
ProductCategoryID int NOT NULL,
ProductCategoryName nVarchar(100)NOT NULL,
ProductKey int NOT NULL PRIMARY KEY,
ProductID int NOT NULL,
ProductName nVarchar(100)NOT NULL,
StartDate int NOT NULL,
EndDate int NOT NULL,
IsCurrent char(3) NOT NULL,
);

Go
/****** [dbo].[DimCustomers] ******/
Create Table DimCustomers
(
CustomerKey int NOT NULL PRIMARY KEY,
CutomerID nChar(5) NOT NULL,
CustomerName nVarchar(100) NOT NULL,
CustomerCity nVarchar(100) NOT NULL,
CustomerCountry nVarchar(100) NOT NULL,
StartDate int NOT NULL,
EndDate int NOT NULL,
IsCurrent char(3) NOT NULL,
);

Go
/****** [dbo].[DimDates] ******/
Create Table DimDates
(
DateKey int NOT NULL PRIMARY KEY,
[Date] datetime NOT NULL,
USADateName nVarchar(100),
MonthKey int NOT NULL,
[MonthName] nVarchar(100),
QuarterKey int NOT NULL,
QuarterName nVarchar(100),
YearKey int NOT NULL,
YearName nVarchar(100),
);

Go
/****** [dbo].[FactOrders] ******/
Create Table FactOrderDetails
(
OrderID int NOT NULL,
CustomerKey int NOT NULL,
OrderDateKey int NOT NULL,
ProductKey int NOT NULL,
ActualOrderUnitPrice money,
AtualOrderQuantity int,
CONSTRAINT [FactOrders] PRIMARY KEY CLUSTERED
 ([CustomerKey] ASC,[OrderDateKey] ASC,[ProductKey] ASC)
);
Go

--********************************************************************--
-- Create the FOREIGN KEY CONSTRAINTS
--********************************************************************--
USE [DWNorthwindLite]
Go

ALTER TABLE [FactOrderDetails] WITH CHECK ADD CONSTRAINT [FK_FactOrderDetails_DimDates] FOREIGN KEY([OrderDateKey])
REFERENCES [DimDates]([DateKey])
Go

ALTER TABLE [FactOrderDetails] WITH CHECK ADD CONSTRAINT [FK_FactOrderDetails_DimCustomers] FOREIGN KEY([CustomerKey])
REFERENCES [DimCustomers]([CustomerKey])
Go

ALTER TABLE [FactOrderDetails] WITH CHECK ADD CONSTRAINT [FK_FactOrderDetails_DimProducts] FOREIGN KEY([ProductKey])
REFERENCES [DimProducts]([ProductKey])
Go

--********************************************************************--
-- Review the results of this script
--********************************************************************--
Select 'Database Created'
Select Name, xType, CrDate from SysObjects
Where xType in ('u', 'PK', 'F')
Order By xType desc, Name

 

Using the SQL Script above I implemented the data warehouse and created all the required Primary and Foreign key constraints.

 

 

 



The above image illustrates the specific tables and relationships