Creating Northwind Lite Data Warehouse

In this blog we are going to create Northwind Lite Data Warehouse solution and the below plan shows the objects in the data warehouse solution.

First step in creation of data warehouse is to create data warehouse in this example we will create data warehouse DWNorthwindLite and then create the dimension, fact and Date dimension tables within this data warehouse.
The following script accomplishes the task of creating data warehouse and defining the tables.

/**************************************************************

 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 PRIMARY KEY Identity,

            [ProductId] [int] NOT NULL,

            [ProductCategoryId] [int] NOT NULL,

            [ProductName] [nvarchar](100) NOT NULL,

            [ProductCategoryName] [nvarchar](100) NOT NULL,

            [StartDate] [date] NULL,

            [EndDate] [date] NULL,

            [Iscurrent] [int] NULL,

            )

Go

/****** [dbo].[DimCustomers] ******/

CREATE TABLE [dbo].[DimCustomers](

            [CustomerKey] [int] NOT NULL PRIMARY KEY Identity,

            [CustomerID] [nvarchar] (5) NULL,

            [CustomerName] [nvarchar](100) NULL,

            [CustomerCity] [nvarchar](100) NULL,

            [CustomerCountry] [nvarchar](100) NULL,

            [StartDate] [int] NULL,

            [EndDate] [int] NULL,

            [Iscurrent] [nvarchar](3) NULL,

)

Go

/****** [dbo].[DimDates] ******/

CREATE TABLE dbo.DimDates (

            [DateKey] int NOT NULL PRIMARY KEY IDENTITY,

            [USADateName] nVarchar(100) NULL,

            [MonthKey] int NOT NULL,

            [MonthName] nVarchar(100) NULL,

            [QuarterKey] int NOT NULL,

            [QuarterName] nVarchar(100) NULL,

            [YearKey] int NULL,

            [YearName] nVarchar(100) NULL,

)

Go

/****** [dbo].[FactOrders] ******/

CREATE TABLE dbo.FactOrders (

            [OrderKey] [int] NOT NULL,

            [OrderID] [int] NULL,

            [OrderDateKey] [int] NOT NULL,

            [CustomerKey] [int] NOT NULL,

            [ProductKey] [int] NOT NULL,

            [ActualOrderUnitPrice] [money] NOT NULL,

            [ActualOrderQuantity] [int] NOT NULL,

 CONSTRAINT [PK_FactOrders] PRIMARY KEY CLUSTERED

            ( [OrderKey] ASC, [OrderDateKey] ASC, [CustomerKey] ASC, [ProductKey] ASC )

)

Go

Second step is define key constraints including primary and foreign key relationship between tables and objects within it according to the objects defined in the plan document.

--********************************************************************--
-- Create the FOREIGN KEY CONSTRAINTS
--********************************************************************--
Alter Table [dbo].[FactOrders] With Check Add Constraint [FK_FactOrders_DimProducts]
Foreign Key  ([ProductKey]) References [dbo].[DimProducts] ([ProductKey])

Alter Table [dbo].[FactOrders] With Check Add Constraint [FK_FactOrders_DimCustomers]
Foreign Key  ([CustomerKey]) References [dbo].[DimCustomers] ([CustomerKey])

Alter Table [dbo].[FactOrders]  With Check Add Constraint [FK_FactOrders_DimDates]
Foreign Key ([OrderDateKey]) References [dbo].[DimDates] ( [DateKey] )
Go

The below screenshot shows, the tables and the data warehouse tree diagram generated in object explorer.

Third step is to generate database diagram showing the full data warehouse solution in graphical format.

The below scripts describes the backup and restoration of data warehouse solution.

/**
1) Make a copy of the empty database before startig the ELT Process
**/

BACKUP DATABASE [DWNorthwindLite]
TO DISK =
N'E:\_DWCert\Assignment Submission\Assignment03_Shubhada\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'E:\_DWCert\Assignment Submission\Assignment03_Shubhada\DWNorthwindLite\DWNorthwindLiteBackup.bak'
WITH REPLACE
GO

The below shown QA report confirms that the database object conform to the warehouse planning documents.

Finally we will include all the data warehouse solution and it's related planning documents in one solution place in Visual Studio.

The below screenshot shows the complete data warehouse solution in one place.