BIData 201: Assignment 02 - Demo of Data Warehouse

201B Business Intelligence: Building the Data Warehouse

Quick video on assignment

Demo of a Business Intelligence Solution for a two location Coffee Shop 

Scenario

Evergreen Coffee has two coffee shop locations in the Puget Sound Area. Employees occasionally work between multiple stores. The business has been doing well and the owners would like to know the following:

·       What time of day and what products do they sell the most of?

·       Which employees are selling the most products? Does the time of day matter?

·       Do the discounts help the business drive more Sales?

The owners have also asked that the Data Warehouse is flexible. Just in case they want to look at additional variables down the road.  

Solution Development Plan

Solution:

Owner: TPauley
Date: 10/17/2016

Change Log Plan (example)

Summary

Evergreen Coffee has requested a Business Intelligence Solution be built to address some of the questions that the owners have on a daily basis. With the solution in place, they are hoping to expand outside the Northwest and down the coast of Oregon and California. This model is a simplified version for a smaller company.

Expectations:

·        Address the three questions highlighted in the scenario

·        Build a OLAP Cube that can be taught to employees not familiar with reporting

·        Make a simple prototype that can be demoed to the owners

Open Issues

Closed Issues

Solution Naming Conventions

ETL Staging Database

Data Warehouse Database

Determining the Schedule

 Team Members

Roles

Example of the Data Warehouse Worksheet

Note: The entire data warehouse would be represented here. I did the first couple of tables just to show the process.

Create a Schedule

Table Schema

  (5) Tables

tblSALES, tblSTORES, DISCOUNTS, tblEMPLOYEE, & tblPRODUCTS

(2) Bridge Tables

tblPRODUCT_SALES, & tblEMPLOYEE_STORE

Below is a chart of the tables I created in SQL Server using  the Database Diagram Tool

Database Diagram

Evergreen Coffee Database Diagram

Author: Tim Pauley

Note: The modifications I did to the PK and FK were done using the database diagram tool and table design tool. However, I did script out the tables manually to practice my SQL Server Syntax. Here is the script for those tables.

CREATE DATABASE EvergreenCoffee
GO
USE EvergreenCoffee

CREATE TABLE tblSALES
(
SalesID INT NOT NULL,
DiscountsID INT NOT NULL,
SalesAmount money NOT NULL,
SalesQuantity INT NOT NULL,
SalesDate datetime NOT NULL
)
CREATE TABLE tblDISCOUNTS
(
DiscoutsID INT NOT NULL,
DiscountAmount money NOT NULL,
DiscountQuantity INT NOT NULL,
DiscountDate datetime NOT NULL
)
CREATE TABLE tblPRODUCT_SALES
(
ProductsID INT NOT NULL,
SalesID INT NOT NULL
)
CREATE TABLE tblPRODUCTS
(
ProductsID INT NOT NULL,
ProductsName Varchar(50) NOT NULL,
ProductsAmount money NOT NULL,
ProductsQuantity INT NOT NULL,
ProductsDate datetime NOT NULL
)
CREATE TABLE tblSTORES
(
StoreID INT NOT NULL,
StoreName Varchar(50) NOT NULL,
StoreAddress Varchar(50) NOT NULL,
StoreCity Varchar(50) NOT NULL,
StoreNumber Varchar(10) NOT NULL
)
CREATE TABLE tblEMPLOYEE
(
EmployeeID INT NOT NULL,
EmployeeName Varchar(50) NOT NULL,
EmployeeAddress Varchar(50) NOT NULL,
EmployeeCity Varchar(50) NOT NULL,
EmployeeNumber Varchar(10) NOT NULL
)
CREATE TABLE tblEMPLOYEE_STORE
(
EmployeeID INT NOT NULL,
StoreID INT NOT NULL
)

Conclusion

 This was a great exercise to outline a simple database and walkthrough the planning phases. The next phase would be creating data marts and data warehouse. This process would allow the reports to update quicker (rather than using the database views) and create accurate business rules that could be shared with the owners.

Sources:    

Root, Randal Pro SQL Server 2012 BI Solutions (2012) Chapters 3 & 4 Publisher Apress