Peter Blaney
BI Certification
Solution: Creating NorthwindLite Data Warehouse
Date: 11/3/2014
Blog:
http://blaneybi.blogspot.com/The Data Warehouse plan below shows the objects in the data warehouse solution for the Northwind Lite database.
The solution below shows the components of implementing a data warehouse solution, including the creation, backup and restoration, and backup file.
The below script accomplishes the first task of creating the database. This includes removing any existing instance of the data warehouse, and then reinstating it. With the database schema initiated, it then proceeds to create the physical tables. With the tables in place, the relationships are then defined with the foreign key constraints. A QA Report is also generated to confirm the data warehouse was implemented in accordance with planning documents.
/**************************************************************
Create the Data Warehouse
*************************************************************/
--****************** [DWNorthwindLite] *********************--
-- This file will drop and create the [DWNorthwindLite]
-- database, with all its objects.
--****************** Instructors Version ***************************--
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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
/****** [dbo].[DimProducts] ******/
CREATE TABLE [dbo].[DimProducts](
[ProductKey] [int] NOT NULL,
[ProductID] [int] NULL,
[ProductName] [nvarchar](100) NULL,
[ProductCategoryID] [int] NULL,
[ProductCategoryName] [nvarchar](15) NULL,
[StartDate] [int] NULL,
[EndDate] [int] NULL,
[IsCurrent] [char](3) NULL,
CONSTRAINT [PK_DimProducts] PRIMARY KEY CLUSTERED
(
[ProductKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** [dbo].[DimCustomers] ******/
CREATE TABLE [dbo].[DimCustomers](
[CustomerKey] [int] NOT NULL,
[CustomerID] [nchar](5) NULL,
[CustomerName] [nvarchar](100) NULL,
[CustomerCity] [nvarchar](100) NULL,
[CustomerCountry] [nvarchar](100) NULL,
[StartDate] [int] NULL,
[EndDate] [int] NULL,
[IsCurrent] [char](3) NULL,
CONSTRAINT [PK_DimCustomers] PRIMARY KEY CLUSTERED
(
[CustomerKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** [dbo].[DimDates] ******/
CREATE TABLE [dbo].[DimDates](
[DateKey] [int] NOT NULL,
[USADateName] [nvarchar](100) NULL,
[MonthKey] [int] NULL,
[MonthName] [nvarchar](100) NULL,
[QuarterKey] [int] NULL,
[QuarterName] [nvarchar](100) NULL,
[YearKey] [int] NULL,
[YearName] [nvarchar](100) NULL,
CONSTRAINT [PK_DimDates] PRIMARY KEY CLUSTERED
(
[DateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** [dbo].[FactOrders] ******/
CREATE TABLE [dbo].[FactOrders](
[OrderKey] [int] NOT NULL,
[OrderID] [int] NULL,
[CustomerKey] [int] NULL,
[OrderDateKey] [int] NULL,
[ProductKey] [int] NULL,
[ActualOrderUnitPrice] [money] NULL,
[ActualOrderQuantity] [smallint] NULL,
CONSTRAINT [PK_FactOrders] PRIMARY KEY CLUSTERED
(
[OrderKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
--********************************************************************--
-- Create the FOREIGN KEY CONSTRAINTS
--********************************************************************--
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.DimCustomers SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.DimDates SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.DimProducts SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.FactOrders ADD CONSTRAINT
FK_FactOrders_DimCustomers FOREIGN KEY
(
CustomerKey
) REFERENCES dbo.DimCustomers
(
CustomerKey
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
ALTER TABLE dbo.FactOrders ADD CONSTRAINT
FK_FactOrders_DimDates FOREIGN KEY
(
OrderDateKey
) REFERENCES dbo.DimDates
(
DateKey
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
ALTER TABLE dbo.FactOrders ADD CONSTRAINT
FK_FactOrders_DimProducts FOREIGN KEY
(
ProductKey
) REFERENCES dbo.DimProducts
(
ProductKey
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
ALTER TABLE dbo.FactOrders SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
--********************************************************************--
-- 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
The image below shows the resulting data structure generated by the above script.
The diagram below shows the relationships defined by constraints enforced by the creation script.
The QA Report below confirms that the database objects conform to the warehouse planning documents.
The below script completes the data warehouse solution with back up and restore capability.
1) Make a copy of the empty database before startig the ELT Process
**/
BACKUP DATABASE [DWNorthwindLite]
TO DISK =
N'C:\_BISolutions\Northwind\DWNorthwindLite\DWNorthwindLiteBackup.bak'
GO
/**
2) Send the file to other team members and tell them they can restore the database with this code...
**/
-- Check to see if they alread hav a database with that name...
IF EXISTS (SELECT name FROM sys.databases WHERE name=N'DWNorthwindLite')
BEGIN
-- If they do, they need to close connections to the DWNorthwindLite database, with this code.
ALTER DATABASE [DWNorthwindLite] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
END
-- Now they can restore the empty database...
USE MASTER
RESTORE DATABASE [DWNorthwindLite]
FROM DISK =
N'C:\_BISolutions\Northwind\DWNorthwindLite\DWNorthwindLiteBackup.bak'
WITH REPLACE
GO
No comments:
Post a Comment