Sunday, November 9, 2014

Creating ETL Script

So far, this assignment has proven the most challenging. There were many issues that brought me to a halt, but eventually got resolved.

The first thing that I did was review the transformation spreadsheet and fill out as many transformations as I could just by looking at the DW design



I wasn't able to get them all, but I did get a lot.
After that, I began creating the dimension tables in the test script.
This test script made it easy to go through, step by step, and test each line of code individually.
Once I had working code, I copied that into the final script.

The date table was the most difficult, because I wasn't able to initially write the code for the transformation and have it show up as the correct data type. However, Randal left us a clue in the spreadsheet on how to write the code to convert a date time into an nvarchar and then cast that into an integer. 


Once this issue was resolved, the only other major issue that I had was the fact table. I initially attempted to use the on-board tools to generate join code, but found that to be more tedious and hassle than it was worth. I much preferred to write the joins manually.

While writing the joins, I learned that the order in which the tables are joined is important. I needed to first write the join to merge the Orders and OrderDetails table from the DB before I was able to write the joins within the DW for the surrogate key information.


After that, my script properly populated the DW with data and was able to be run over and over.

Sunday, November 2, 2014

Creating The Data Warehouse

When creating the DWNorthwindLite data warehouse, I pulled the information from the DW design and input it into the Table Designer utility to generate code.
I first opened the utility to correctly input the column names and data types into the table. After that, I used the Generate Change Script option within the Table Designer to create some code.
Once the code is created, I copied and pasted the generated code into my DWNorthwindLite script window.
This gave me a good base of code to work with before manually creating the foreign key restraints. This proved to be more difficult, since I decided to do it manually. I ended up reformatting a lot of the code to match the way it appeared in the book, since I was using that for my main reference.
I had one issue with properly creating the foreign key restraints. With the way I had my code written, none of the foreign key relationships would be created. After creating my database, I would get an error about not having a CustomersKey primary key in my DimCustomers table.

On top of that, the table diagram wouldn't show any table relationships.

After reviewing the code that I had generated in the Table Creator and cross referencing that with the PubsDW from the book material, I realized that I had too many primary keys in my dimension tables. 
I reviewed the course material and fixed my code to have only one primary key per dimension table, so the code looked like this: 

Once I had successfully troubleshooted this issue, my database was able to execute successfully.

The lesson that I learned in this exercise is to be careful when using the Table Creator utility. Since I had created my tables incorrectly to begin with, the code that was generated wouldn't allow my script to work.















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.