Data Warehouse Design and Analysis for Trade Master Company Report

This report provides a detailed analysis of a data warehouse designed for the Trade Master Company, a business that sells tools. The report begins with an introduction to data warehousing, defining its role in business intelligence and its structure based on the ETL approach. The core of the report is a deep dive into the database structure, including descriptions of the six key tables: CompanyDim, ProductDim, WarehouseDim, CustomerDim, DateDim, and SaleFacts. Each table's attributes and the significance of its primary key are explained. The report then elucidates the concepts of dimension and fact tables within a star schema, identifying SaleFacts as the central fact table and the other tables as dimension tables. Primary and foreign keys are meticulously identified and described, emphasizing their role in maintaining data integrity and relationships between tables. A database diagram visually represents the relationships between the tables. The report concludes with a series of SQL queries, each accompanied by an explanation of its function, demonstrating how data can be retrieved and analyzed from the warehouse. References are also provided.

Data and Information Management
1

Contents
INTRODUCTION...........................................................................................................................3
Part 1................................................................................................................................................3
a) Analyse and describe the tables...............................................................................................3
b) Description of dimension and fact tables................................................................................9
c) Identification and description of Primary and Foreign Key..................................................11
d) Draw database diagram.........................................................................................................13
e) Database queries and explain them.......................................................................................14
CONCLUSION..............................................................................................................................16
REFREENCES..............................................................................................................................17
2

INTRODUCTION
Datawarehouse can be defined as a type of relational database this design and used for query
and analysis rather than transaction processing. It is considered as a main and core component of
business intelligence (Moscoso-Zea and Luján-Mora, 2016). It can also be defined as a central
repository of integrated data from one or more desperate sources. It is used within large
organizations for storing both historical and current data in a single place for analysis so that
analytical reports can be created that can further be used for decision making. Datawarehouse is
built on the basis of ETL approach i.e. extract, transform and load. There are three main
approaches on the basis of which database is designed: bottom-up, top- down and hybrid design.
This assignment is based upon a case study of Trade Master company sells various tools who has
developed a data warehouse to store information about sales, customers and products. This
assignment will focus upon following task in detail: description and analysis of tables,
description of dimension and fact tables, description and identification of primary and foreign
key, drawing database diagram, explanation of database questions briefly.
Part 1
a) Analyse and describe the tables.
The database has been created by developer for trade master company that mainly used for
selling the product as well as service in marketplace (Li, Yang and Feng, 2019). The Trade
Master Company have developed the data warehouse to collect or store information regarding
consumers, sales and products. Database tables has been developed by using Structural query
language. This is considered as one of the most effective database table that help for performing
different commands and afterwards, it is running the process in step by step manner (Chen and
et. al., 2019). For creation of relational database, SQL is playing important role to use DDL,
DML for performing command. In order to generate accurate result or outcome.
Generally, it is mainly consists of six different database tables such as CompanyDim,
productDim, warehouseDim, customerDim, DateDim, SaleFacts.
CompanyDim Table
SELECT * FROM 'CompanyDim' LIMIT 0, 30
3

In this table, it is mainly representing the information about the company and also consists of
different attributes such as CompanyKey, BranchState, BranchAddress and branchEmail. These
are basically appropriate attributes to represent more information (Faqihuddin, Wahyuddin and
Nathasia, 2020). CompanyKey is a primary key and contain unique value. This will help for
establishing a unique relationship with another table. It can be possible when primary key behave
as foreign key into another table.
4

ProductDim Table
SELECT * FROM 'ProductDim' LIMIT 0, 30
In this table, it is mainly representing the information about the company and also consists of
different attributes such as ProductKey, ProductName, Brand, UnitPrice. These are basically
appropriate attributes to represent more information (Chen and et. al., 2019). ProductKey is a
primary key and contain unique value. This will help for establishing a unique relationship with
another table. It can be possible when primary key behave as foreign key into another table.
5

WarehouseDim Table
SELECT * FROM 'WarehouseDim' LIMIT 0, 30
In this table, it is mainly representing the information about the company and also consists of
different attributes such as warehousekey, warehousedivision and warehouse region. These are
basically appropriate attributes to represent more information (Xie, Yang and Yao, 2019).
Warehousekey is a primary key and contain unique value. This will help for establishing a
unique relationship with another table. It can be possible when primary key behave as foreign
key into another table.
6

CustomerDim Table
SELECT * FROM 'CustomerDim' LIMIT 0, 30
In this table, it is mainly representing the information about the company and also consists of
different attributes such as customerkey, customername, customeraddress, customer state,
customerEmail. These are basically appropriate attributes to represent more information.
CustomerKey is a primary key and contain unique value (Chen and et. al., 2019). This will help
for establishing a unique relationship with another table. It can be possible when primary key
behave as foreign key into another table.
7

DateDim Table
SELECT * FROM 'DateDim' LIMIT 0, 30
In this table, it is mainly representing the information about the company and also consists of
different attributes such as Datekey, saledate, year, Month, day, Quarter These are basically
appropriate attributes to represent more information (Chen and et. al., 2019). Datekey is a
primary key and contain unique value. This will help for establishing a unique relationship with
another table. It can be possible when primary key behave as foreign key into another table.
8

SaleFacts Table
SELECT * FROM 'SaleFacts' LIMIT 0, 30
In this table, it is mainly representing the information about the company and also consists of
different attributes such as ID, Datekey, CompanyKey, Customerkey, productkey,
warehousekey, salesQuantity (Menon and Narwane, 2020). These are basically appropriate
attributes to represent more information. ID is a primary key and contain unique value. This will
help for establishing a unique relationship with another table. It can be possible when primary
key behave as foreign key into another table.
b) Description of dimension and fact tables
Data modelling is one of the most important and crucial steps of converting raw data into
an effective form which further helps in making data readily accessible (Walha, Ghozzi and
Gargouri, 2019). Database star schema is one of the simplest style of data mart schema or data
9

model. It is a common approach which is most widely used for development of data warehouse
and dimensional data marts. Star schema consist of one or more than one Fat table that can refer
to any number of dimension tables (Chen and et. al., 2019). It works as a base for snowflake
schema which further helps in effective handling of database queries. It is named as star schema
because in this fact table is at the centre and dimension tables surround tact table which makes it
look like a star. In this schema denormalization of data modelling is done for creation of simple
ad easy schema. Fact table consist of two main types of tables: First is Fact table and second is
dimension table.
Fact table: This table focuses upon recording matrix or measurement of specific events. It
mostly consists of numeric values, foreign keys to dimensional data in which descriptive
information or data for numeric data is stored (Menon and Narwane, 2020). It can help in storing
large number of records within fact table over time. Fact tables many times consist of surrogate
key for ensuring that each row can be uniquely identified. Surrogate key is a simple primary key
of this table.
In this database SaleFacts is a Fact table that consist of foreign keys to all the other
dimensional table that consist of only numeric data.
Dimension table: It is a kind of table that consist of small number of records as compared to fact
table. But this table can consist of large number of attributes as compared to fact table. This table
can be defined as a wide variety of characteristics (Brown, 2019). Each dimension table is linked
to Fact table and each of these tables consist of a surrogate primary key which is usually of
integer data type. This database star schema consists of five main dimension tables that are liked
to SaleFacts fact table.
 CompanyDim: This table helps in explaining details of company like BranchState,
BranchAddress, BranchEmail.
 CustomerDim: This table helps in explaining details of customers like CustomerName,
CustomerAddress, CustomerState and CustomerEmail.
 DateDim: This table helps in explaining details of sales date like SaleDate, Year, month,
Day, Quarter.
10

 ProductDim: This table helps in explaining details of products like productname, Brand,
UnitPrice.
 WarehouseDim: This table helps in explaining details of warehouse like WarehouseKey,
WarehouseDivision and WarehouseRegion.
Figure 1 Database star schema
c) Identification and description of Primary and Foreign Key
There are various kinds of keys that are used while creating a column within a table.
These keys help in explaining characteristics of data that can be stored with it as well as helps in
explaining relationship with other tables as well (Menon and Narwane, 2020). But out of all the
keys two most important keys are primary and foreign key. Primary Key is unique constraint that
identified each record in a table. Primary keys must be unique and cannot be null (Vassiliadis
and et. al., 2019). Each table consist of only one primary key. Whereas foreign key is a kind of
key used to link two tables. Primary key of one table used in another is known as foreign key. A
table can have more than one foreign key.
Primary and foreign keys of all tables have been explained below:
11

Table name Primary Key Foreign Key
SaleFacts ID  CompanyKey
 CustomerKey
 DateKey
 ProductKey
 WarehouseKey
CompanyDim CompanyKey
CustomerDim CustomerKey
DateDim DateKey
ProductDim ProductKey
WarehouseDim WarehouseKey
Database referential integrity: Referential integrity is a property of data stating that all of its
references are valid. Referential mostly describes actions of foreign key as it performs,
‘referring’ or a kind of link to another column (Menon and Narwane, 2020). It can also be
explained as that each of the values stored within foreign key must be stored a unique value in
primary key in referred table. Foreign key within a table helps in maintaining referential integrity
od a relational database.
12