Sunday, October 26, 2014

Designing a Data Warehouse


For the lesson three homework, we were asked to design a data warehouse for the NorthwindLite OLTP database. Quite a few changes were made, but the two large changes to the source data were the addition of a date table and the normalization of the Products and Categories tables.

                I made no changes to the naming convention at all. I felt that the naming convention that was used for the NorthwindLite database was intuitive and held up to the industry standard of naming conventions. The only changes that were made to any of the names was the addition of the data warehouse table type. I added a fact prefix for the Orders and OrderDetails table and a Dim prefix for the Products, Customers, and Dates tables. 

This is shown below:

DWNorthwindLite.dbo.FactOrders
Fact Table
NorthwindLite.dbo.Orders
DWNorthwindLite.dbo.FactOrders.OrderID
Dimension Key Column
NorthwindLite.dbo.Orders.OrderID
DWNorthwindLite.dbo.FactOrders.CustomerID
Dimension Key Column
NorthwindLite.dbo.Orders.CustomerID
DWNorthwindLite.dbo.FactOrders.OrderDate
Dimension Key Column
NorthwindLite.dbo.Orders.OrderDate

I kept most of the data types, but didn't keep all of them.

DWNorthwindLite.dbo.FactOrders.OrderDate
Dimension Key Column
NorthwindLite.dbo.Orders.OrderDate
datetime
int

For the order date, this was changed from a datetime data type to an int, because of the addition of the Date table. The two tables will be linked using an int primary key.

Here are two other data type changes that I made:

DWNorthwindLite.dbo.FactOrderDetails.UnitPrice
Measure Column
NorthwindLite.dbo.OrderDetails.UnitPrice
money
decimal (18,4)
DWNorthwindLite.dbo.FactOrderDetails.Quantity
Measure Column
NorthwindLite.dbo.OrderDetails.Quantity
smallint
int


I chose to change the money data type to a decimal. This was done to make reporting easier by changing the data type to a more universal format. I chose to change the smallint to an int, because the smallint data type is unnecessary. An int will be able to serve the same purpose and also keep things more uniform.

I chose to keep all of the other data types the same, since there was no reason to change it. I did consider changing the nvarchar lengths in the Customers table, but after looking at the data I decided that they were set to those lengths for a purpose. For example, the CustomerID data was a standardized five character code, so I kept it at five in the data warehouse.

NorthwindLite.dbo.Customers.CustomerID
nchar(5)
nchar(5)

One major change that I did, was combining the Categories table and the Products table into one. This was done to make reporting easier. Knowing the category and product type all at the same time will be more beneficial than the benefits to the performance gained from eliminating the redundancy.
DWNorthwindLite.dbo.DimCategories
Dimension Table
NorthwindLite.dbo.Categories
Table
Table
N/A
Dimension Key Column
Generated
na
N/A
N/A
Dimension  Column
NorthwindLite.dbo.Categories.CategoryID
int
N/A
N/A
Dimension Column
NorthwindLite.dbo.Categories.CategoryName
nvarchar(15)
N/A
DWNorthwindLite.dbo.DimProducts
Dimension Table
NorthwindLite.dbo.Products
Table
Table
DWNorthwindLite.dbo.DimProducts.ProductKey
Dimension Key Column
Generated
na
int
DWNorthwindLite.dbo.DimProducts.ProductID
Dimension  Column
NorthwindLite.dbo.Products.ProductID
int
int
DWNorthwindLite.dbo.DimProducts.ProductName
Dimension Column
NorthwindLite.dbo.Products.ProductName
nvarchar(40)
nvarchar(40)
DWNorthwindLite.dbo.DimProducts.CategoryID
Dimension Column
NorthwindLite.dbo.Products.CategoryID
int
int
DWNorthwindLite.dbo.DimProducts.ProductCategory
Dimension Column
N/A
N/A
nvarchar(15)

The final major change that I did to the data warehouse was the addition of a date table.

DWNorthwindLite.dbo.DimDates
Dimension Table
na
na
Table
DWNorthwindLite.dbo.DimDates.DateKey
Dimension Key Column
na
na
int
DWNorthwindLite.dbo.DimDates.Date
Dimension Column
na
na
datetime
DWNorthwindLite.dbo.DimDates.DateName
Dimension Column
na
na
nvarchar(50)
DWNorthwindLite.dbo.DimDates.Month
Dimension Column
na
na
int
DWNorthwindLite.dbo.DimDates.MonthName
Dimension Column
na
na
nvarchar(50)
DWNorthwindLite.dbo.DimDates.Quarter
Dimension Column
na
na
int
DWNorthwindLite.dbo.DimDates.QuarterName
Dimension Column
na
na
nvarchar(50)
DWNorthwindLite.dbo.DimDates.Year
Dimension Column
na
na
int
DWNorthwindLite.dbo.DimDates.YearName
Dimension Column
na
na
nvarchar(50)



Due to the fact that we will be tracking the orders on a daily basis, the addition of this table will benefit the data warehouse greatly. We will be able to track the orders in more detail and also retain performance by separating it out into its own table. As spoke about in class and in the book, all data warehouses can benefit from a date table. The DateKey will be an int, so that it can create a key relationship with the order table. 

Sunday, October 19, 2014

Chapter 3 Assignment

BI Solution Planning: Scooter's Lemonade Stand
                For this assignment, we will be outlining the steps necessary for planning to implement a business intelligence solution for Scooter's Lemonade Stand. Ultimately, we need to take a look at the entire project, break it down into smaller pieces, and then plan as many pieces as possible before beginning development. This will allow us to estimate the time, the cost, as well as plan the best solution for the customers needs.

The first step in the planning stage is the interview process, in which we attempt to gather as much information as we can about the lemonade stand's current data storage practices, their expectations, and their needs. This will allow us to more accurately estimate the scope, cost, and time of the project.

The Client: Scooter's Lemonade stand has been in business for three summers and has only one location on the sidewalk in front of Scooter's Mom's house. He currently has three products that he sells, regular lemonade, strawberry lemonade, and sugar free lemonade. Scooter has been keeping his sales records in a spiral notebook.



                                               
After interviewing Scooter about his business intelligence needs, he has clearly outlined what he wants:
                1. One place to store data to improve on current organization practices.
                2. The ability to easily import sales data.
                3. The ability to report on the sales data to improve on current decision making processes.
On the flip side, he also outline what he doesn't want from his BI solution:
                1. The solution must not be expensive.
                2. The solution must not be complicated
                3. The finalization date must not be during summer.

Now that we have all of the client's requirements from the interview stage, we must document this in a development plan. This document will clearly describe the scope of the project, so that everybody is on the same page when the project begins.

Now that we have outline the project from the big picture perspective, we need to break it down and figure out exactly what needs to be done to create this solution. This will allow us to estimate time and cost more accurately.

For Scooter's solution, we need to:
                1. Create the database
                2. Create the data warehouse
                3. Create the ETL process
                4. Create cubes
                5. Create reports
                6. Test
                7. Train Scooter/Create documentation.

Due to the fact that Scooter's time frame is so long, the fact that we have a one man team will not hinder the project. As seen in the chart below, even though each job must be completed before moving to the next, the project will still be done in the allotted time frame.

Now that we have an idea of how long this project will take, we need to estimate how much this will cost. The overall cost is a huge deal for Scooter, as it is with all businesses. According to our breakdown schedule, this project will take an estimated 36 hours to complete. Working at $30/ hr, this works out to about $1080. 

Once the time frame and cost are approved by the client, we are able to begin logically planning out our database and data warehouse before developing them.

Scooter has been keeping all of his sales data in a spiral notebook. He has been keeping records of each transaction that takes place. Due to the nature of his business, he does not keep records of his customers. A simple database will allow him to store his information in one secure spot.

After that, we need to document how we will build the data warehouse. While Scooter may not need one yet, since he has to input all of his data manually, he may need one in the future. Due to the fact that the timeframe is so wide, we are able to implement this feature.

Now, we have planned out all that we can before the actual development begins. We have a road map for the entire project, as well as blueprints to create our database and data warehouse. 
During the development process, it's important to log all issues that are encountered and their resolutions in the Solution Outline. This will help avoid issues and help with better planning on future projects.