Milestone 2 Review
For Milestone 2 you had to:
Create the Data Warehouse (Due by class 9)
Session 3: Designing the Data
Warehouse (Ch. 4)
Session 4: Building the Data
Warehouse (Ch4 and 5)
If your Source database was this, then you should have ended
with a variation of two different options...
SOURCE
Option 1: Snowflake design
Option 2: Star design
For this assignment I decided to go with the star design. This was a simpler approach and the data is relatively small in the database
List of things to work on
- Randal used 2 Fact Tables. I didn't
- I had multiple surrogate keys, Need to review this.
Here is my final ERD which was closet to the Star Design. The blue circle indicates a JOIN I needed to fix
Here is my ERD Code
/**************************************************************
Create the Data Warehouse
*************************************************************/
--******************
[DW_Project01] *********************--
-- This
file will drop and create the [AW_Project01_OLTP_DB]
--
database, with all its objects.
--******************
Students Version ***************************--
--******************
Tim Pauley 12/4/2016 ***************************--
USE
[master]
GO
If Exists (Select * from Sysdatabases Where
Name = 'DW_Project01')
Begin
ALTER
DATABASE [DW_Project01] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP
DATABASE [DW_Project01]
End
GO
CREATE DATABASE [DW_Project01]
CONTAINMENT = NONE
ON PRIMARY
(
NAME = N'DWPubsSales', FILENAME = N'C:\BIData201B\Assignment08TimPauley\DatabaseBackup\DW_Project01.mdf'
,
SIZE = 10MB
,
MAXSIZE = 1GB
,
FILEGROWTH = 10MB )
LOG ON
(
NAME = N'DWPubsSales_log', FILENAME = N'C:\BIData201B\Assignment08TimPauley\DatabaseBackup\DW_Project01_log.LDF'
,
SIZE = 1MB
,
MAXSIZE = 1GB
,
FILEGROWTH = 10MB)
GO
EXEC
[DW_Project01].dbo.sp_changedbowner @loginame=N'SA', @map=false
GO
ALTER DATABASE [DW_Project01] SET
RECOVERY BULK_LOGGED
GO
--********************************************************************--
-- Create
the Tables
--********************************************************************--
USE
[DW_Project01]
GO
/******
Object: Table [dbo].[DimAddress] ******/
CREATE TABLE [dbo].[DimAddress](
[AddressID] [int] NOT NULL ,
[AddressKey] [int] NOT
NULL PRIMARY KEY IDENTITY,
[AddressLine1] [nvarchar](100) NOT NULL,
[AddressLine2] [nvarchar](100) NOT NULL,
[City] [nvarchar](100) NOT NULL,
[PostalCode] [nvarchar](100) NOT NULL,
[StateProvidenceName] [nvarchar](100) NOT NULL,
[CountryRegionName] [nvarchar](100) NOT NULL,
)
GO
/******
Object: Table [dbo].[DimAddressType]
******/
CREATE TABLE [dbo].[DimAddressType](
[AddressTypeID] [int] NOT NULL,
[AddressTypeKey] [int] NOT NULL PRIMARY KEY IDENTITY,
[AddressTypeName] [nvarchar](100) NOT NULL,
)
GO
/******
Object: Table [dbo].[DimCustomerAddress]
******/
CREATE TABLE [dbo].[DimCustomerAddress](
[PersonID] [int] NOT
NULL,
[AddressID] [int] NOT NULL,
[PersonKey] [int] NOT NULL,
[AddressKey] [int] NOT NULL,
[AddressTypeID] [int] NOT NULL,
[AddressTypeKey] [int] NOT NULL,
CONSTRAINT
[PK_DimCustomerAddress] PRIMARY KEY CLUSTERED ([PersonKey],
[AddressKey])
)
GO
/******
Object: Table
[dbo].[DimCustomers]******/
CREATE TABLE [dbo].[DimCustomers](
[CustomerID] [int] NOT NULL,
[CustomerKey] [int] PRIMARY KEY IDENTITY,
[PersonID] [int] NOT
NULL,
[PersonKey] [int] NOT NULL,
[PersonFirstName] [nvarchar](100) NOT NULL,
[PersonLastName] [nvarchar](100) NOT NULL,
[StartDate] [int] NOT NULL,
[EndDate] [int] NOT
NULL,
[IsCurrent] [nchar](3) NOT NULL,
)
GO
/******
Object: Table [dbo].[DimDates]******/
CREATE TABLE [dbo].[DimDates](
[DateKey] [int] NOT
NULL PRIMARY KEY IDENTITY,
[USADateName] [nvarchar](100) NOT NULL,
[MonthKey] [int] NOT
NULL,
[MonthName] [nvarchar](100) NOT NULL,
[QuarterKey] [int] NOT NULL,
[QuarterName] [nvarchar](100) NOT NULL,
[YearKey] [int] NOT
NULL,
[YearName] [nvarchar](100) NOT NULL,
)
GO
/******
Object: Table [dbo].[DimProducts]******/
CREATE TABLE [dbo].[DimProducts](
[ProductID] [int] NOT NULL,
[ProductKey] [int] NOT NULL PRIMARY KEY IDENTITY,
[ProductName] [nvarchar](100) NOT NULL,
[ProductListPrice] [money] NOT NULL,
[ProductSubcategoryID] [int] NOT NULL,
[ProductSubcategoryName] [nvarchar](100) NOT NULL,
[ProductCategoryID] [int] NOT NULL,
[ProductCategoryName] [nvarchar](100) NOT NULL,
[StartDate] [int] NOT NULL,
[EndDate] [int] NOT
NULL,
[IsCurrent] [nvarchar](3) NOT NULL,
)
GO
/******
Object: Table [dbo].[FactSalesOrders]
******/
CREATE TABLE [dbo].[FactSalesOrders](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[ProductKey] [int] NOT NULL,
[DateKey] [int] NOT
NULL,
[CustomerKey] [int] NOT NULL,
[OrderQuantity] [int] NOT NULL,
[OrderUnitPrice] [money] NOT NULL,
CONSTRAINT
[PK_FactSalesOrders] PRIMARY KEY CLUSTERED
(
[SalesOrderID] ASC,
[SalesOrderDetailID] ASC,
[ProductKey] ASC,
[DateKey] ASC,
[CustomerKey] ASC
)
)
GO
--********************************************************************--
-- Create
the FOREIGN KEY CONSTRAINTS
--********************************************************************--
ALTER TABLE [dbo].[DimCustomerAddress]
ADD CONSTRAINT
[FK_DimCustomerAddress_DimCustomers]
FOREIGN KEY([PersonKey]) REFERENCES [dbo].[DimCustomers] ([CustomerKey])
ALTER TABLE [dbo].[DimCustomerAddress]
ADD CONSTRAINT
[FK_DimCustomerAddress1_DimAddress] FOREIGN KEY([PersonKey])
REFERENCES
[dbo].[DimAddress] ([AddressKey])
ALTER TABLE [dbo].[DimCustomerAddress]
ADD CONSTRAINT
[FK_DimCustomerAddress_DimAddressType]
FOREIGN KEY([AddressTypeKey]) REFERENCES [dbo].[DimAddressType] ([AddressTypeKey])
ALTER TABLE [dbo].[FactSalesOrders]
ADD CONSTRAINT
[FK_FactSalesOrders_DimCustomers]
FOREIGN KEY([CustomerKey]) REFERENCES [dbo].[DimCustomers] ([CustomerKey])
ALTER TABLE [dbo].[FactSalesOrders]
ADD CONSTRAINT
[FK_FactSalesOrders_DimDates]
FOREIGN KEY([DateKey]) REFERENCES [dbo].[DimDates] ([DateKey])
ALTER TABLE [dbo].[FactSalesOrders]
ADD CONSTRAINT [FK_FactSalesOrders_DimProducts]
FOREIGN KEY([DateKey]) REFERENCES [dbo].[DimProducts] ([ProductKey])
--********************************************************************--
-- 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
No comments:
Post a Comment