Homework Session 3

For this homework I created documentation on how I would name a DataWarehouse based on the NorthwindLite Database. I have changed some of the data types in my Datawarehouse design to better represent the data that is being stored.

Below is the source table names and data types provided from the NorthwindLite DB:

 

Source	Source Type
NorthwindLite	Database
NorthwindLite.dbo.Orders	Table
NorthwindLite.dbo.Orders.OrderID	int
NorthwindLite.dbo.Orders.CustomerID	nchar(5)
NorthwindLite.dbo.Orders.OrderDate	datetime
NorthwindLite.dbo.OrderDetails	Table
NorthwindLite.dbo.OrderDetails.OrderID	int
NorthwindLite.dbo.OrderDetails.ProductID	int
NorthwindLite.dbo.OrderDetails.UnitPrice	money
NorthwindLite.dbo.OrderDetails.Quantity	smallint
NorthwindLite.dbo.Categories	Table
Generated	na
NorthwindLite.dbo.Categories.CategoryID	int
NorthwindLite.dbo.Categories.CategoryName	nvarchar(15)
NorthwindLite.dbo.Products	Table
Generated	na
NorthwindLite.dbo.Products.ProductID	int
NorthwindLite.dbo.Products.ProductName	nvarchar(40)
NorthwindLite.dbo.Products.CategoryID	int
NorthwindLite.dbo.Customers	Table
Generated	na
NorthwindLite.dbo.Customers.CustomerID	nchar(5)
NorthwindLite.dbo.Customers.CompanyName	nvarchar(40)
NorthwindLite.dbo.Customers.ContactName	nvarchar(30)
NorthwindLite.dbo.Customers.Address	nvarchar(60)
NorthwindLite.dbo.Customers.City	nvarchar(15)
NorthwindLite.dbo.Customers.Country	nvarchar(15)

Below, you can see that I changed all of the nvarchar data types to a single type of nvarchar(40). This was done to simplify the amount of different data types in the datawarehouse. As the type is nvarchar, the database will reduce the amount of space used to store the data depending on the length. I have also changed the OrderDate from “datetime” to “date” as the source database was not actually recording any time other than midnight. Doing this will save a significant amount of space in the datawarehouse. I am also storing the Generated columns in the dimension tables to a Key column as a data type of int.

Initial datawarehouse design for the NorthwindLite database:

Object Name	Description	Destination Type
DWNorthwindLite	Data Warehouse	Database
DWNorthwindLite.dbo.FactOrders	Fact Table	Table
DWNorthwindLite.dbo.FactOrders.OrderID	Dimension Key Column	int
DWNorthwindLite.dbo.FactOrders.CustomerID	Dimension Key Column	nchar(5)
DWNorthwindLite.dbo.FactOrders.OrderDate	Dimension Key Column	date
DWNorthwindLite.dbo.FactOrderDetails	Fact Table	Table
DWNorthwindLite.dbo.FactOrderDetails.OrderID	Dimension Key Column	int
DWNorthwindLite.dbo.FactOrderDetails.ProductID	Dimension Key Column	int
DWNorthwindLite.dbo.FactOrderDetails.UnitPrice	Measure Column	money
DWNorthwindLite.dbo.FactOrderDetails.Quantity	Measure Column	smallint
DWNorthwindLite.dbo.DimCategories	Dimension Table	Table
DWNorthwindLite.dbo.DimCategories.CategoryKey	Dimension Key Column	int
DWNorthwindLite.dbo.DimCategories.CategoryID	Dimension  Column	int
DWNorthwindLite.dbo.DimCategories.CategoryName	Dimension Column	nvarchar(40)
DWNorthwindLite.dbo.DimProducts	Dimension Table	Table
DWNorthwindLite.dbo.DimProducts.ProductKey	Dimension Key Column	int
DWNorthwindLite.dbo.DimProducts.ProductID	Dimension  Column	int
DWNorthwindLite.dbo.DimProducts.ProductName	Dimension Column	nvarchar(40)
DWNorthwindLite.dbo.DimProducts.CategoryID	Dimension Column	int
DWNorthwindLite.dbo.DimCustomers	Dimension Table	Table
DWNorthwindLite.dbo.DimCustomers.CustomerKey	Dimension Key Column	int
DWNorthwindLite.dbo.DimCustomers.CustomerID	Dimension Column	nchar(5)
DWNorthwindLite.dbo.DimCustomers.CompanyName	Dimension Column	nvarchar(40)
Not Used	Dimension Column	na
Not Used	Dimension Column	na
DWNorthwindLite.dbo.DimCustomers.City	Dimension Column	nvarchar(40)
DWNorthwindLite.dbo.DimCustomers.Country	Dimension Column	nvarchar(40)

BI Homework For Session 02

Computer Solutions Inc. would like to perform hardware inventory for all of their office computers and monitors. They currently have asset tags on them and have all of the information stored in a SQL database. The current method of inventory is to scan the asset tags into a blank Excel document that they then compare manually to the database to ensure that the equipment is there. Ideally, they would like the inventory results stored in a data warehouse for automatic verification and inventory history.

Solution Development Plan Overview:

The following is an example of how a summary would be created for this BI Solution. It would include a quick description of the problem and how it would be solved.

Below are some examples of how data can be transformed and stored in this BI solution. This shows how we will be able to transform any piece of data that is not very descriptive into a more user friendly version.

 Below is an example of the existing inventory data from Computer Solutions. This is what the employee uses as they go around and perform the inventory of the company’s computers

Below is an example of the existing database from Computer Solutions. It is a simple database that stores what hardware they have, what it is (computer tower, monitor, etc.), and where it is located.

Upon performing the ETL process on the customer’s SQL database and inventory Excel documents, we should be able to create a data warehouse that can store the records for each inventory and provide accurate reports.

Here is our projected members of the BI Solution Development team and their roles:

Our estimated Hours for completion are:

This should be the initial schedule projection for the development of the whole BI Solution:

We have reviewed Computer Solution’s current Licensing and Solution requirements and found the following:

• The Customer already has an SQL server available.
•  The Customer already has a DBA that can maintain the BI Solution servers.