BI Data 201A: Assignment 04: Creating a Data Warehouse

Assignment 04: Creating a Data Warehouse

In this “hands on” activity you will learn about creating a Data Warehouse using SQL programming. This activity will take you about 3 to 6 hours, so plan accordingly!

The activity consists of the follow steps:

1. Watch the module videos.
2. Read the 5th chapter of your textbook.
3. Perform the Exercises in the textbook.
4. Review the Instructor’s BI solution spreadsheet.
5. Create a data warehouse script.
6. Update your Blog (optional)
7. Submit your work to the Catalyst site.

Watch the module videos

Please watch the video I created about performing this homework. This can be found

https://youtu.be/KA_zIbCCDxs?list=PLfycUyp06LG8mxxAH5Lee42yigqkYIYrJ

NOTES for 2016:

1) We are not using Canvas, not Catalyst.

2) I have removed the Blog as a requirement this quarter.

3) My SQL Bootcamp video can be found here:

https://drive.google.com/file/d/0B5U6tT4eiM6aRlljeXRSTlJ6dkU/view?usp=sharing

Read the fifth chapter of your textbook.

I am assuming that you have the textbook by now, so I am not providing the .pdf files this time.

Perform the Exercises in the textbook.

EXERCISE 5-1. CREATING THE PUBLICATION INDUSTRIES DATABASE

In this exercise, you create a database for the Publication Industries data warehouse. 

Exercise 5-1 Building the DW

USE [master]

GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'DWPubsSales') 

      BEGIN   

-- Close connections to the DWPubsSales database   

            ALTER DATABASE [DWPubsSales] SET SINGLE_USER WITH ROLLBACK IMMEDIATE   

            DROP DATABASE [DWPubsSales] 

END

GO

CREATE DATABASE [DWPubsSales] ON PRIMARY

( NAME = N'DWPubsSales'

, FILENAME = N'C:\BIData201B\Assignment04TimPauley\Exercise5\DWPubsSales.mdf'

, SIZE = 10MB

, MAXSIZE = 1GB

, FILEGROWTH = 10MB )

LOG ON

( NAME = N'DWPubsSales_log' ,

FILENAME = N'C:\BIData201B\Assignment04TimPauley\Exercise5\\DWPubsSales_log.LDF'

, SIZE = 1MB

, MAXSIZE = 1GB

, FILEGROWTH = 10MB)

GO

EXEC [DWPubsSales].dbo.sp_changedbowner @loginame = N'SA'

, @map = false

GO

ALTER DATABASE [DWPubsSales] SET RECOVERY BULK_LOGGED

GO

EXERCISE 5-2. CREATING TABLES AND FOREIGN KEY CONSTRAINTS

In this exercise, you create the tables and foreign key constraints in the Publication Industries data warehouse. You can choose to use the SQL code presented here, the table designer, or the diagramming tools to accomplish your goal.

5-2 Building the Tables

USE [DWPubsSales]

GO

/****** Create the Dimension Tables ******/

CREATE TABLE [dbo].[DimStores](

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

  [StoreId] [nchar](4) NOT NULL, 

  [StoreName] [nvarchar](50) NOT NULL )

  GO

CREATE TABLE [dbo].[DimPublishers](

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

  [PublisherId] [nchar](4) NOT NULL, 

  [PublisherName] [nvarchar](50) NOT NULL )

  GO

CREATE TABLE [dbo].[DimAuthors](

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

  [AuthorId] [nchar](11) NOT NULL, 

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

  [AuthorState] [nchar](2) NOT NULL )

  GO

CREATE TABLE [dbo].[DimTitles](  [TitleKey] [int] NOT NULL PRIMARY KEY Identity,

  [TitleId] [nvarchar](6) NOT NULL, 

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

  [TitleType] [nvarchar](50) NOT NULL, 

  [PublisherKey] [int] NOT NULL,  [TitlePrice] [decimal](18, 4) NOT NULL, 

  [PublishedDateKey] [int] NOT NULL )

  GO

/****** Create the Fact Tables ******/

CREATE TABLE [dbo].[FactTitlesAuthors](

  [TitleKey] [int] NOT NULL, 

  [AuthorKey] [int] NOT NULL, 

  [AuthorOrder] [int] NOT NULL,

CONSTRAINT [PK_FactTitlesAuthors] PRIMARY KEY CLUSTERED

      ( [TitleKey] ASC, [AuthorKey] ASC )

   )

   GO

CREATE TABLE [dbo].[FactSales](

  [OrderNumber] [nvarchar](50) NOT NULL, 

  [OrderDateKey] [int] NOT NULL, 

  [TitleKey] [int] NOT NULL, 

  [StoreKey] [int] NOT NULL, 

  [SalesQuantity] [int] NOT NULL,

CONSTRAINT [PK_FactSales] PRIMARY KEY CLUSTERED

    ( [OrderNumber] ASC,[OrderDateKey] ASC, [TitleKey] ASC, [StoreKey] ASC )

  )

  GO

5-2 Building the Foreign Keys

USE DWPubsSales

GO

/****** Add Foreign Keys ******/

ALTER TABLE [dbo].[DimTitles] WITH CHECK ADD CONSTRAINT [FK_DimTitles_DimPublishers]

FOREIGN KEY([PublisherKey]) REFERENCES [dbo].[DimPublishers] ([PublisherKey])

GO

ALTER TABLE [dbo].[FactTitlesAuthors] WITH CHECK ADD CONSTRAINT  [FK_FactTitlesAuthors_DimAuthors]

FOREIGN KEY([AuthorKey]) REFERENCES [dbo].[DimAuthors] ([AuthorKey])

GO

ALTER TABLE [dbo].[FactTitlesAuthors] WITH CHECK ADD CONSTRAINT  [FK_FactTitlesAuthors_DimTitles]

FOREIGN KEY([TitleKey]) REFERENCES [dbo].[DimTitles] ([TitleKey])

GO

ALTER TABLE [dbo].[FactSales] WITH CHECK ADD CONSTRAINT [FK_FactSales_DimStores]

FOREIGN KEY([StoreKey]) REFERENCES [dbo].[DimStores] ([Storekey])

GO

ALTER TABLE [dbo].[FactSales] WITH CHECK ADD CONSTRAINT [FK_FactSales_DimTitles]

FOREIGN KEY([TitleKey]) REFERENCES [dbo].[DimTitles] ([TitleKey])

GO

EXERCISE 5-3. CREATE A DATE DIMENSION

In this exercise, you create a date dimension table in the Publication Industries data warehouse. You can choose to use either the SQL code presented here, the table designer or the diagramming tools to accomplish your goal. (In Chapter 7 you fill the table with data using the code in Listing 5-11.)

5-3 Building Date Dim


USE [DWPubsSales]

GO

-- We should create a date dimension table in the database

CREATE TABLE dbo.DimDates (

 [DateKey] int NOT NULL PRIMARY KEY IDENTITY ,

 [Date] datetime NOT NULL ,

 [DateName] nVarchar(50) ,

 [Month] int NOT NULL ,

 [MonthName] nVarchar(50) NOT NULL ,

 [Quarter] int NOT NULL ,

 [QuarterName] nVarchar(50) NOT NULL ,

 [Year] int NOT NULL ,

 [YearName] nVarchar(50) NOT NULL

 )

USE [DWPubsSales]

GO

-- Because the date table has no associated source table we can fill the data

-- using a SQL script.

-- Create variables to hold the start and end date

DECLARE @StartDate datetime = '01/01/1990'

DECLARE @EndDate datetime = '01/01/1995'

-- Use a while loop to add dates to the table

DECLARE @DateInProcess datetime

SET @DateInProcess = @StartDate

WHILE @DateInProcess < = @EndDate

 BEGIN

 -- Add a row into the date dimension table for this date

 INSERT INTO DimDates (

    [Date]

      , [DateName]

      , [Month]

    , [MonthName]

      , [Quarter]

      , [QuarterName]

      , [Year]

      , [YearName]

      )

      VALUES (

       -- [Date]  

       @DateInProcess

       -- [DateName]

       , Convert(varchar(50), @DateInProcess, 110) + ', '

         + DateName( weekday, @DateInProcess )

       -- [Month]

       , Month( @DateInProcess )

       -- [MonthName]

       , Cast( Year(@DateInProcess) as nVarchar(4) ) + ' - '

         + DateName( month, @DateInProcess )

       -- [Quarter]

       , DateName( quarter, @DateInProcess ) 

       -- [QuarterName]

       , Cast( Year(@DateInProcess) as nVarchar(4) ) + ' - '

         + 'Q' + DateName( quarter, @DateInProcess )

       -- [Year]

       , Year(@DateInProcess)

       -- [YearName]

       , Cast( Year(@DateInProcess) as nVarchar(4) ) )

 -- Add a day and loop again

 SET @DateInProcess = DateAdd(d, 1, @DateInProcess)

 END

 --Check the table SELECT Top 10 * FROM DimDates

5-3b Building Database Diagram

EXERCISE 5-4. ADDING DATABASE FILES TO VISUAL STUDIO

In this exercise, you add scripts and backup files to the Visual Studio Solution you created in Chapter 3. You start by creating the files and a subfolder to hold the files. Then add these files to a logical Visual Studio folder, as shown in Figure 5-37.

5-4 Back Up Database

USE [DWPubsSales]

GO

BACKUP DATABASE [DWPubsSales]

TO DISK =

N'C:\BIData201B\Assignment04TimPauley\Exercise5\DWPubsSales_BeforeETL.bak'

GO

RESTORE DATABASE [DWPubsSales]

FROM DISK =

N'C:\BIData201B\Assignment04TimPauley\Exercise5\DWPubsSales_BeforeETL.bak'

WITH REPLACE

GO

5-4b  Generate Scripts

5-4c Transfer to Visual Studio

Note: I have videos that cover these exercises if you need additional help.

https://www.youtube.com/playlist?list=PLfycUyp06LG80E0O3d0HYZfGRBJAT4vez

Review the Instructor’s BI solution spreadsheet.

Please review the current data warehouse design given to you by your project manager (me, your instructor).  I have provided you with my version of the spreadsheet, called Instructors BISolutionWorksheets.xlsx, so please use that version for the assignment.


Assignment 04 Building the DW

Build the Database

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]

ON PRIMARY ( NAME = N'DWNorthwindLite'

, FILENAME = N'C:\BIData201B\Assignment04TimPauley\Assignment04TimPauley\DWNorthwindLite.mdf'

, SIZE = 10MB

, MAXSIZE = 1GB

, FILEGROWTH = 10MB )

LOG ON

( NAME = N'DWPubsSales_log'

, FILENAME = N'C:\BIData201B\Assignment04TimPauley\Assignment04TimPauley\DWNorthwindLite_log.LDF'

, SIZE = 1MB

, MAXSIZE = 1GB

, FILEGROWTH = 10MB)

GO

EXEC [DWNorthwindLite].dbo.sp_changedbowner @loginame = N'SA'

, @map = false

GO

ALTER DATABASE [DWNorthwindLite] SET RECOVERY BULK_LOGGED

GO

Build the Tables

USE [DWNorthwindLite]

GO

/****** Object:  Table [dbo].[DimCustomers]    Script Date: 10/31/2016 4:59:42 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[DimCustomers](

       [CustomerKey] [int] IDENTITY(1,1) NOT NULL,

       [CustomerID] [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,

 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

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[DimDates]    Script Date: 10/31/2016 4:59:42 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[DimDates](

       [DateKey] [int] IDENTITY(1,1) NOT NULL,

       [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,

 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

/****** Object:  Table [dbo].[DimProducts]    Script Date: 10/31/2016 4:59:42 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[DimProducts](

       [ProdutCategoryID] [int] NOT NULL,

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

       [ProductKey] [int] IDENTITY(1,1) NOT NULL,

       [ProductID] [int] NOT NULL,

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

       [StartDate] [int] NOT NULL,

       [EndDate] [int] NOT NULL,

       [IsCurrent] [char](3) NOT 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

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[FactOrders]    Script Date: 10/31/2016 4:59:42 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[FactOrders](

       [OrderID] [int] NOT NULL,

       [CustomerKey] [int] NOT NULL,

       [DateKey] [int] NOT NULL,

       [ProductKey] [int] NOT NULL,

       [ActualOrderUnitPrice] [money] NOT NULL,

       [ActualOrderQuantity] [int] NOT NULL,

 CONSTRAINT [PK_FactOrders] PRIMARY KEY CLUSTERED

(

       [OrderID] ASC,

       [CustomerKey] ASC,

       [DateKey] ASC,

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

Build the Foreign Keys

GO

ALTER TABLE [dbo].[FactOrders]  WITH CHECK ADD  CONSTRAINT [FK_FactOrders_DimCustomers] FOREIGN KEY([CustomerKey])

REFERENCES [dbo].[DimCustomers] ([CustomerKey])

GO

ALTER TABLE [dbo].[FactOrders] CHECK CONSTRAINT [FK_FactOrders_DimCustomers]

GO

ALTER TABLE [dbo].[FactOrders]  WITH CHECK ADD  CONSTRAINT [FK_FactOrders_DimDates] FOREIGN KEY([DateKey])

REFERENCES [dbo].[DimDates] ([DateKey])

GO

ALTER TABLE [dbo].[FactOrders] CHECK CONSTRAINT [FK_FactOrders_DimDates]

GO

ALTER TABLE [dbo].[FactOrders]  WITH CHECK ADD  CONSTRAINT [FK_FactOrders_DimProducts] FOREIGN KEY([ProductKey])

REFERENCES [dbo].[DimProducts] ([ProductKey])

GO

ALTER TABLE [dbo].[FactOrders] CHECK CONSTRAINT [FK_FactOrders_DimProducts]

GO

USE [master]

GO

ALTER DATABASE [DWNorthwindLite] SET  READ_WRITE

GO

DW Diagram

Upload to Visual Studio