Database Data Warehouse Design Our Company, Data

Database Data Warehouse Design

Our company, Data Analytic Limited, specializes in collecting and analyzing data for various organizations. Over the years, we have assisted various companies to turn raw data into valuable information that assists the companies in making effective decision profitable in the short and long run. Our research and data analytics are geared towards giving extra edge to various companies. Our services include processing and analyzing terabytes of data to provide customer meaningful information for business decision and enhance competitive market advantages. Recent growth of our company necessitates the needs to design and develop data warehouse that will accommodate large volume of customer data.

Objective of this project is to design and develop the data warehouse for our company.

Importance of Data Warehousing for our Organization

Comprehensive portfolios of our business include Business, Market, and Financial research, Data processing services and Domain based analytics. While the relational database that our company is currently using provides a necessity for our current needs, however, data warehousing has become a strategy priority for our organization because of enormous amount of data that our company is required to analyze, which comes from various sources. If we continue using relational database to store our data, we will need to delete certain volume of data to make room for new data, and doing so, we will not be able to create historical data to assist our clients to make analytical processing from these data. With the volume of data that we need to store, our company will need to create multiple database to maintain historical data and such practice will not assist our company to implement centralized database that can be accessed by all staff, which consequently lead to data redundancy.

Data warehousing is a priority for our business because of the enormous volume of data that our organization will analyze and process. (Microsoft, 2012). Typically, data warehouse will assist our organization to organize data into multidimensional cubes using OLAP to rapidly summarize data for analytical queries. (Tera Data, 2012).

Rostek, (2010) argue that data warehouse stores, processes and integrates operational data to support an effective decision making process. Efficient analysis of data requires historical data to complete and data warehouse is the biggest known database that will assist our company to organize data in the centralized manner to deliver analytical information. Moreover, our organization will be able to overcome data latency with data warehouse. The data latency occurs when organizations take too much time necessary to collect, prepare analyze and transform data for an effective decision-making. The most valuation information that is not up-to-date may not deliver a significant benefit for management. The data warehouse will assist our company to overcome the data latency, which will assist us to classify, forecast and stimulate data for a decision making.

The main idea of data warehouse is to collect data from different sources in one base. Data warehousing supports business decision by consolidating, collecting and organizing data for analyzing and reporting using tools such as data mining and online analytical processing (OLAP). Although, our company is currently using relation database to analyze and process data, however, relation database will not be able to accommodate large amount of data received by our company that need immediate processing and analyzing to make an analytical decision.

By taking advantages of data warehouse, our company will enjoy the superior performances delivered by OLAP. Online analytical processing is the technology that is designed to deliver superior and intelligence performances to businesses. By integrating data warehouse into our business operation, our company will be able to use OLAP to operate efficiently with data organized in accordance with dimensional model.

Using a data warehousing to process data, our company will be able to deliver a maximum performance because the data warehouse will assist our company to process total sales income and quantity sold in various methods. The query of total sales income of a specific geographical location will be answered within few seconds regardless of hundred of million rows of data stored in our data warehouse. More importantly, our company will be able to enjoy superior performances delivered by OLAP because OLAP will assist our company to summarize information in analytical queries.

Similarly, our company will enjoy superior advantage delivered by data mining technology by integrating data warehouse for our data services. Data miming technology applies complex and sophisticated data analyzing technology used to assist information analysts in enhancing decision making. "Data mining is a natural extension and supplement of data warehouses creation, which means organization of large, multidimensional data collection that aids analytical gathering process." (Rostek, 2010 p 105). Thus, our company will be able to use data mining tool to support data-driven analysis.

Data Warehousing Schema

Database schema provides the graphical representation or text-based used to generate database physical model. The database schema for our company will consist of the following entities:

Database Entities:

Customers: stores clients' data,

Employees: stores all staff information that includes organizational unit structure.

Offices: stores data of sale office.

Services: stores list of services the company delivers,

Product Lines: stores a list of category line of product and service,

Orders: stores orders placed by clients,

OrderDetails: stores the detailed line of items of each order,

Payments: stores payments made by clients based on client's account,

Using the information presented above, the paper designs the following database schema:

CREATE TABLE 'customers' (

'customerNumber' int (15) NOT NULL,

'customerFirstName' varchar (55) NOT NULL,

'customerLastName' varchar (45) NOT NULL,

'phone' varchar (55) NOT NULL,

'addressLine1' varchar (48) NOT NULL,

'addressLine2' varchar (55) DEFAULT NULL,

'city' varchar (55) NOT NULL,

'state' varchar (55) DEFAULT NULL,

'postalCode' varchar (20) DEFAULT NULL,

'country' varchar (30) NOT NULL,

'salesRepemployeeNumber" int (15) DEFAULT NULL,

CREATE TABLE 'employees' (

'employeeNumber' int (15) NOT NULL,

'firstName' varchar (55) NOT NULL,

'lastName' varchar (55) NOT NULL,

'extension' varchar (15) NOT NULL,

'email' varchar (80) NOT NULL,

'officeCode' varchar (15) NOT NULL,

'reportsTo' int (15) DEFAULT NULL,

'jobTitle' varchar (55) NOT NULL,

CREATE TABLE 'offices' (

'officeCode' varchar (15) NOT NULL,

'city' varchar (55) NOT NULL,

'phone' varchar (55) NOT NULL,

'addressLine1' varchar (55) NOT NULL,

'addressLine2' varchar (55) DEFAULT NULL,

'state' varchar (55) DEFAULT NULL,

'country' varchar (55) NOT NULL,

'postalCode' varchar (20) NOT NULL,

'territory' varchar (15) NOT NULL,

CREATE TABLE 'orderdetails' (

'orderNumber' int (18) NOT NULL,

'productCode' varchar (19) NOT NULL,

'quantityOrdered' int (25) NOT NULL,

'orderLineNumber' smallint (10) NOT NULL,

CREATE TABLE 'orders' (

'orderNumber' int (15) NOT NULL,

'orderDate' date NOT NULL',

'requiredDate' date NOT NULL,'

'shippedDate' date DEFAULT NULL',

'status' varchar (20) NOT NULL',

'comments' text, 'customerNumber' int (15) NOT NULL',

'customers' ('customerNumber')'

CREATE TABLE 'payments' (

'customerNumber' int (15) NOT NULL,

'checkNumber' varchar (55) NOT NULL,

"paymentDate" date NOT NULL',

"amount" double NOT NULL',

CREATE TABLE 'productlines' (

'productLine' varchar (55) NOT NULL,

'textDescription' varchar (5500) DEFAULT NULL,

CREATE TABLE 'products' (

'productCode' varchar (30) NOT NULL,

'productName' varchar (90) NOT NULL,

'productLine' varchar (65) NOT NULL,

'productScale' varchar (25) NOT NULL,

'productVendor' varchar (65) NOT NULL,

Entity-Relationship (E-R) Diagram

In this project, the entity relationship (ER) diagram delivers a conceptual model that provides the graphical illustration of the database. The rationale behind ER diagram is to deliver the logical presentation of the company data. More importantly, ERD identifies entities and provide the relationships among entities. The major benefits of ER diagram are that it assists analysts and designers to gain a better understanding about the information in the database. An ERD presents the data in the following format: