Data Warehouse Design for Chemist Warehouse: A Case Study Analysis

This report presents a comprehensive data warehouse design for Chemist Warehouse, focusing on the application of dimensional modeling techniques. It begins with an executive summary outlining the design principles and the selection of relevant business processes, including maintenance, sales, and services. A data warehouse bus matrix is developed to illustrate the interconnections between dimensions such as retail sales, patient data, product information, and customer details. The report then details the creation of a star schema for the data marts, specifically for sales, market analysis, and medicine preparation. The report also covers the fact tables, granularity, and justification against granularity. The justification and attribute hierarchy of the dimension tables are discussed, along with a detailed examination of the data warehouse design features and the identification of key fields. The report concludes by summarizing the key findings and the benefits of the data warehouse design for Chemist Warehouse.

Running head: DATA WAREHOUSE DESIGN
Data Warehouse Design: A Case Study of Chemist Warehouse
Name of the Student
Name of the University

DATA WAREHOUSE DESIGN 2
Executive Summary
In the report the design principles and the different way of data warehouse design is depicted.
The process that includes the maintenance, sales, and the services that are provided are selected.
These services and the dimensions are interconnected and the bus matrix is used to gives the
information about the interconnection. The dimension includes the flight ticket booking,
customers, clients and may more. Star schema can be created by using the derived bus matrix.
This report also contains the table of facts, granularity, dimension types and many more. The
dimensions that are undertaken are supported by these dimensions. The management also relies
on the data that are there within the warehouse.

DATA WAREHOUSE DESIGN 3
Table of Contents
1. Introduction:................................................................................................................................4
2. Data Warehouse Bus Matrix:.......................................................................................................4
3. Star Schema for the Data Marts:..................................................................................................5
3.1 Maintenance Fact Table:........................................................................................................5
3.2 Services Fact Table:...............................................................................................................6
3.3 Sales:......................................................................................................................................7
4. Fact Table, Granularity and Justification against Granularity:....................................................7
5. Justification and Attribute Hierarchy of the Dimension Tables:.................................................9
6. Data Warehouse Design Features:.............................................................................................12
7. Identification of Fields:..............................................................................................................14
8. Conclusion:................................................................................................................................17
Bibliography:.................................................................................................................................18

DATA WAREHOUSE DESIGN 4
1. Introduction:
Data ware house is also referred to as the store house that stores a large volume of data
that are from different sources that also help the management to undertake several decisions for
the organization (George, Kumar and Kumar 2015). The warehouse also used to analyze the data
that are oriented to some subject areas and Chemist Warehouse is one such example. The several
business processes within the organization and the primary tools that is termed as the bus matrix.
Star Schema is the data warehouse design representation in a graphical manner.
This report also consists the bus matrix, star schema tables that consists of the facts that
also maintain the granularity, design dimension, design, types and many other. The
datawarehouse is engaged with the process of collecting and managing the data within the
organization thus supporting in the functioning of the organization. Thus it is essential for data
warehouse to maintain and follow the aims and the objectives of the Chemist Warehouse.
2. Data Warehouse Bus Matrix:
Business
processes
Dimensions
DATE
WAREHOUSE
PRODUCT
STORE
CUSTOMER
PROMOTION
PHARMACISTS
VENDOR
PRODUCT CATEGORY
MEDIA
PATIENT
Retail sales X X X X X X X X

DATA WAREHOUSE DESIGN 5
Inventory
management
X X X X X
Medicine
preparation
X X X X
Procurement X X X X X
Market analysis X X X X X X
Data mart focuses on specific unit, department or user group within an organization.
Taken as an example, sales, market analysis, preparing medications and more. Therefore the
choses data marts are sales, market and medicine preparation. The sales data mart will provide
solutions to inventory management, procurement, promotion and sales related concerns. The
medicine preparation will be associated with how Chemist Warehouse serves patients by
preparing medications. And the market analysis will be completely based on the external factors’
influence on sales and how the organization can implement better sales patterns.
The sales data mart has been selected because it can provide answer to all the business
questions as mentioned in the organization requirement file. Answer to these queries are of great
help for the managers of Chemist Warehouse.

DATA WAREHOUSE DESIGN 6
3. Star Schema for the Data Marts:
Figure 1: Star Schema of Sales Data Mart
(Source: Created by Author)

DATA WAREHOUSE DESIGN 7
The start schema can be referred to as the basic schema among all the available data mart
schema. It is also the simplest form of schema. This schema is used for developing and the data
warehouse and dimensional data mart design.
The star schema is based on the sales and procurement related process of Chemist
Warehouse. The star schema has eight dimension tables and three fact tables. These three fact
tables shows the retail sales of products during normal time and promotion time along with that it
shows the procurement of product from the vendors as the sales is reducing the product
availability in inventory. The customer purchases the products from the retail stores and online
store. In both cases, the data mart checks the sales medium.
The customers of Chemist Warehouse are the people who purchases product from the
organization. The data warehouse stores all the customer data into the database to later use those
information for various decision making like the age group of user who make most purchase and
more. The store is for mentioning both the online and physical retail stores. The stores will have
name and address. In case online purchase has been made then store name can be online and
address can be headquarter address.
4. Fact Table, Granularity and Justification against Granularity:
Fact table name Fact granularity Fact table
type
Brief justification
Promotional_Sales_Fact_
Table
Total_Product_Co
st
Accumulatin
g
Promotion is one important
activity to improve the
business. Promotions include

DATA WAREHOUSE DESIGN 8
temporary price reductions,
newspaper and other media
ads, store displays and
coupons. Heavy price
reductions are the most
effective way to create
substantial increases in the
number of products sold.
Inventory_Fact_Table Total_Sales_of_Pr
oducts
Transaction This fact tables is associated
with the inventory
management process of
Chemist Warehouse. The
inventory management does
the work of checking the
stock of the products
available in the warehouse
and purchasing products from
vendor.
Sales_Fact_Table Total_Sales_of_Pr
oducts
Periodic
Snapshot
Each store has got a local
manager who maintains the
local business processes. The
store keeps the local
inventory at a satisfactory

DATA WAREHOUSE DESIGN 9
level by checking the balance
of products daily and
observing the hot selling
products. The inventory
management will notify the
central warehouse for their
orders of products, receiving
delivered products and
updating the inventory
database. The local
management sends their
weekly sales to the central
management at headquarters
5. Justification and Attribute Hierarchy of the Dimension Tables:
Dimension table name Brief justification Attribute hierarchies
Date_Dimension_Table Used for recognizing
the time stamp
date_key (PK)
day
day_of_week
date

DATA WAREHOUSE DESIGN 10
month
year
quarter
hour
minute
second
Store_Dimension_Table The details of the stores
is recorded into the
system
store_key (PK)
store_name
store_location
store_fax
Customer_Dimension_Table The customer data is
stored in the dimension
table
customer_key (PK)
customer_name
customer_address
customer_contact
customer_email
customer_job
customer_yearly_income

DATA WAREHOUSE DESIGN 11
customer_type
Promotion_Dimension_Table Store the details of the
promotion
promotion_key (PK)
type
duration
prmotion_motto
prmotion_success
Product_Dimension_Table This dimension table
stores the details of the
products sold
product_key (PK)
category_key (FK)
product_name
product_brand
product_type
product_cost
product_side_effect
product_expiery_date

DATA WAREHOUSE DESIGN 12
Warehouse_Dimension_Table Store the details of the
warehouse of the
organization along with
the stock of products
warehouse_key (FK)
warehous_stock
warehous_condition
Vendor_Dimension_Table Stores the details of the
vendor of raw products
vendor_key (PK)
vendor_name
vendor_location
vendor_priority
Product_Categiry_Dimension_Table Store the details of the
product category
category_key (PK)
category_name
category_rank
6. Data Warehouse Design Features:
Design feature Brief description Brief justification