Quiz Solution

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

This code created the database structure, including the schema and tables.

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

If Exists(Select name from master.dbo.sysdatabases Where Name = 'NorthwindQuiz')

Begin

       USE [master]

       ALTER DATABASE [NorthwindQuiz] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

       DROP DATABASE [NorthwindQuiz]

End

Go

Create Database NorthwindQuiz

Go

USE NorthwindQuiz

Go

CREATE TABLE [dbo].[FactOrders](

       [CustomerID] [nvarchar](5) NOT NULL,

       [CustomerName] [nvarchar](100) NULL,

       [CustomerCountry] [nvarchar](100) NULL,

       [CalendarYear] [int] NOT NULL,

       [OrderCount] [int] NULL,

 CONSTRAINT [PK_FactOrders] PRIMARY KEY CLUSTERED

(

       [CustomerID] ASC,

       [CalendarYear] 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

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

This is the SQL script executed through SSIS to populate the data warehouse.

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

INSERT INTO FactOrders

SELECT [CustomerID] = Northwind.dbo.Orders.CustomerID

, Northwind.dbo.Customers.CompanyName

, Northwind.dbo.Customers.Country

, [CalendarYear] =  Cast( DATEPART(year,Northwind.dbo.Orders.OrderDate) as int)

, [OrderCount] = CAST(Count(Northwind.dbo.Orders.OrderID) as int)

FROM            Northwind.dbo.Orders 

INNER JOIN

                Northwind.dbo.Customers

 ON Northwind.dbo.Orders.CustomerID = Northwind.dbo.Customers.CustomerID

GROUP BY 

Northwind.dbo.Orders.CustomerID

,Northwind.dbo.Customers.CompanyName

, Northwind.dbo.Customers.Country

, Cast( DATEPART(year,Northwind.dbo.Orders.OrderDate) as int)

Northwind Lite Data Warehouse ETL Script

This is the ETL Script supporting the Northwind Lite Database created with the previous setup script, through the below steps:

1. Drop Table Constraints

2. Reset the Table Data

3. Insert the data from the Northwind Lite database in accordance with the defined BI Solutions Data Warehouse Objects.

4. Re-attach the Table Contraints

5. Verify resulting data tables

The below table identifies the Data Warehouse Objects mapped to the source and ETL functions for the translation.

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

 Create the Data Warehouse using previous setup script

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

--****************** [DWNorthwindLite] *********************--

-- This file contains ETL code for use with the

-- [DWNorthwindLite] database.

--********************************************************************--

-- 1) Drop the FOREIGN KEY CONSTRAINTS and Clear the tables

--********************************************************************--

USE [DWNorthwindLite]

ALTER TABLE dbo.FactOrders

       DROP CONSTRAINT FK_FactOrders_DimProducts

GO

ALTER TABLE dbo.FactOrders

       DROP CONSTRAINT FK_FactOrders_DimDates

GO

ALTER TABLE dbo.FactOrders

       DROP CONSTRAINT FK_FactOrders_DimCustomers

GO

ALTER TABLE dbo.FactOrders SET (LOCK_ESCALATION = TABLE)

GO

Truncate Table [DWNorthwindLite].dbo.FactOrders;

Truncate Table [DWNorthwindLite].dbo.DimProducts;

Truncate Table [DWNorthwindLite].dbo.DimCustomers;

Truncate Table [DWNorthwindLite].dbo.DimDates;

--********************************************************************--

-- 2) FILL the Tables

--********************************************************************--

/****** [dbo].[DimProducts] ******/

-- INSERT INTO [DWNorthwindLite].dbo.DimProducts

/* Columns

ProductKey = Auto Generated

 [ProductID] = [NorthwindLite].dbo.Products.ProductID

,[ProductName] = CAST([NorthwindLite].dbo.Products.ProductName as nVarchar(100))

,[ProductCategoryID] = [NorthwindLite].dbo.Products.CategoryID

,[ProductCategoryName] = CAST([NorthwindLite].dbo.Categories.CategoryName as nVarchar(100))

, StartDate = 20000101

, EndDate = Null

, IsCurrent = 'Yes'

*/

INSERT INTO DimProducts

SELECT

 [ProductID] = [NorthwindLite].dbo.Products.ProductID

,[ProductName] = CAST([NorthwindLite].dbo.Products.ProductName as nVarchar(100))

,[ProductCategoryID] = [NorthwindLite].dbo.Products.CategoryID

,[ProductCategoryName] = CAST([NorthwindLite].dbo.Categories.CategoryName as nVarchar(100))

,[StartDate] = 20000101

,[EndDate] = Null

,[IsCurrent] = 'Yes'

FROM [NorthwindLite].dbo.Categories

INNER JOIN [NorthwindLite].dbo.Products

ON [NorthwindLite].dbo.Categories.CategoryID = [NorthwindLite].dbo.Products.CategoryID;

Go

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

/*

Columns

CustomerKey

, CustomerID

, CustomerName = CAST([NorthwindLite].[dbo].[Customers].[CompanyName] as nVarchar(100))

, CustomerCity = CAST([NorthwindLite].[dbo].[Customers].[City] as nVarchar(100))

, CustomerCountry = CAST([NorthwindLite].[dbo].[Customers].[Country] as nVarchar(100))

, StartDate = 20000101

, EndDate = Null

, IsCurrent = 'Yes'

*/

INSERT INTO DimCustomers

SELECT [CustomerID] = [NorthwindLite].[dbo].[Customers].[CustomerID]

       ,[CustomerName] = CAST([NorthwindLite].[dbo].[Customers].[CompanyName] as nVarchar(100))

       ,[CustomerCity] = CAST([NorthwindLite].[dbo].[Customers].[City] as nVarchar(100))

       ,[CustomerCountry] = CAST([NorthwindLite].[dbo].[Customers].[Country] as nVarchar(100))

       ,[StartDate] = 20000101

       ,[EndDate] = Null

       ,[IsCurrent] = 'Yes'

  FROM [NorthwindLite].[dbo].[Customers];

Go

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

/* Columns

DateKey

, USADateName

, MonthKey

, MonthName

, QuarterKey

, QuarterName

, YearKey

, YearName

*/

DECLARE @DateKey Date

DECLARE @EndDate Date

SET @DateKey = '1/1/1950'

SET @EndDate = '12/31/2050'

WHILE (@DateKey <= @EndDate)

BEGIN

INSERT INTO DimDates

    SELECT [DateKey] = Cast( Convert(nVarchar(100), @DateKey, 112) as int)

       , [USADateName] = Convert(nVarchar(100), @DateKey, 110)

       , [MonthKey] = Cast( Convert(nVarchar(100), DatePart(MONTH,@DateKey)) as int)

       , [MonthName] = Convert(nVarchar(100), DateName(MONTH,@DateKey), 110)

       , [QuarterKey] = Cast( Convert(nVarchar(100), DatePart(QUARTER,@DateKey), 110) as int)

       , [QuarterName] = Convert(nVarchar(100), DateName(QUARTER,@DateKey), 110)

       , [YearKey] = Cast( Convert(nVarchar(100), DatePart(Year,@DateKey), 110) as int)

       , [YearName] = Convert(nVarchar(100), DateName(Year,@DateKey), 110)

       SET @DateKey = DATEADD(DAY,1, @DateKey)

END

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

/* Columns

OrderID

, CustomerKey

, OrderDateKey

, ProductKey

, ActualOrderUnitPrice

, ActualOrderQuantity

*/

INSERT INTO DWNorthwindLite.dbo.FactOrders

SELECT Orders.OrderID

       , DimCustomers.CustomerKey

       , OrderDateKey = Cast( Convert(nVarchar(50), [NorthwindLite].[dbo].[Orders].[OrderDate], 112) as int)

       , DimProducts.ProductKey

       , [AcutalOrderUnitPrice] = OrderDetails.UnitPrice

       , [ActualOrderQuanity] = Cast(OrderDetails.Quantity as smallint)

FROM            [NorthwindLite].[dbo].[Orders]

INNER JOIN

                [NorthwindLite].[dbo].[OrderDetails]

                           ON Orders.OrderID = OrderDetails.OrderID

INNER JOIN

                           [DimCustomers]

                           ON Orders.CustomerID = DimCustomers.CustomerID

INNER JOIN

                           [DimProducts]

                           ON OrderDetails.ProductID = DWNorthwindLite.dbo.DimProducts.ProductID

--********************************************************************--

-- 3) Re-Create the FOREIGN KEY CONSTRAINTS

--********************************************************************--

--BEGIN

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 * from [dbo].[DimProducts]

Select * from [dbo].[DimCustomers]

Select * from [dbo].[DimDates]

Select * from [dbo].[FactOrders]

Creating a Northwind Lite Data Warehouse - Assignment #4

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