Module 9 Assignment 2 - Shipment DW
docx
keyboard_arrow_up
School
Yeshiva University *
*We aren’t endorsed by this school
Course
DAV-6050
Subject
Information Systems
Date
Apr 26, 2024
Type
docx
Pages
6
Uploaded by MajorSummerHare44
Module 9 Assignment 2 - Shipment DW
Daxit Golakiya
Show the DDL to create the objects, be sure to have referential integrity and surrogate keys.
Show the DML to load the data. Show the queries of some results Provide some color:
- Is it Star or snowflake?
- Any challenges?
- What is the grain for the fact(s) table?
- Why were the dimensions chose?
Query
-- Creating Shipment Dimension
CREATE TABLE Shipment_Dim (
ShipmentDateKey SERIAL PRIMARY KEY,
ShipmentDate DATE,
ShipmentMethod VARCHAR(50),
ShipmentStatus VARCHAR(50)
);
-- Creating Customer Dimension
CREATE TABLE Customer_Dim (
CustomerKey SERIAL PRIMARY KEY,
CustomerID INT,
CustomerName VARCHAR(100),
CustomerCity VARCHAR(50),
CustomerState VARCHAR(50)
);
-- Creating Shipment Fact
CREATE TABLE Shipment_Fact (
FactKey SERIAL PRIMARY KEY,
ShipmentDateKey INT REFERENCES Shipment_Dim(ShipmentDateKey),
CustomerKey INT REFERENCES Customer_Dim(CustomerKey),
Quantity INT,
Revenue DECIMAL(10, 2)
);
Adding Database in this phase INSERT INTO Shipment_Dim (ShipmentDate, ShipmentMethod, ShipmentStatus)
VALUES
('2023-01-01', 'Ground', 'Shipped'),
('2023-01-02', 'Water', 'Pending'),
('2023-01-03', 'Ground', 'Pickup'),
('2023-01-04', 'Air', 'Processing'),
('2023-01-05', 'Ground', 'Delivered'),
('2023-01-06', 'Water', 'Delivered');
INSERT INTO Customer_Dim (CustomerID, CustomerName, CustomerCity, CustomerState)
VALUES
(1, 'Raju', 'Surat', 'Gujarat'),
(2, 'Parth', 'Bhavnagar', 'Gujarat'),
(3, 'Chagan', 'Udaipur', 'Rajasthan'),
(4, 'Magan', 'Patna', 'Bihar'),
(5, 'Arvind', 'Ahmedabad', 'Gujarat'),
(6, 'Mayur', 'Mumbai', 'Maharastra');
INSERT INTO Shipment_Fact (ShipmentDateKey, CustomerKey, Quantity, Revenue)
VALUES
(1, 1, 100, 1000.00),
(2, 2, 35, 4507.00),
(3, 3, 45, 9876.00),
(4, 4, 78, 4325.00),
(5, 5, 62, 5656.00),
(6, 6, 84, 7878.00);
Query to get total revenue by customer:
SELECT CustomerName, SUM(Revenue) AS TotalRevenue
FROM Shipment_Fact
JOIN Customer_Dim ON Shipment_Fact.CustomerKey = Customer_Dim.CustomerKey
GROUP BY CustomerName;
Output:
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Query to get total revenue by shipment date and method:
Query: SELECT ShipmentDate, ShipmentMethod, SUM(Revenue) AS TotalRevenue
FROM Shipment_Fact
JOIN Shipment_Dim ON Shipment_Fact.ShipmentDateKey = Shipment_Dim.ShipmentDateKey
GROUP BY ShipmentDate, ShipmentMethod;
Provide some color:
- Is it Star or snowflake?
- Any challenges?
- What is the grain for the fact(s) table?
- Why were the dimensions chose?
The design presented is a Star Schema. In a Star Schema, there is a central fact table, which contains measures or facts, and it is connected to dimension tables.
In this case, the fact table is the Shipment_Fact, and the dimension tables are Customer_Dim and Shipment_Dim. The fact table connects directly to the dimension tables, making it a star schema.
Challenges:
Data Quality: Ensuring that the loaded data should be properly transformed, and accuracy and completeness of data are high.
Data Transformation: Cleaning and transforming data from various source systems into a consistent format for data warehouse.
Data Consistency: Managing data consistency when dealing with data from multiple sources and ensuring data aligns with the established business rules.
Query Performance: Optimizing query performance, special with big datasets, to provide timely and efficient access to analytical data.
What is the grain for the fact(s) table?
The grain for the Shipment_Fact table is at the shipment level.
This means that each record in the fact table represents a unique shipment. It captures details like the quantity and revenue for each individual shipment transaction.
Why were the dimensions chose?
This are chosen on their significance to the business and analytical needs:
1. Customer Dimension (Customer_Dim):
Customer information is crucial for analyzing sales and revenue.
Ensure which customers are involved in shipments enables the company to assess revenue contributions, customer preferences, and performance particular to individual customers.
2. Shipment Dimension (Shipment_Dim):
Shipment information is essential for monitoring and analyzing the efficiency and status of shipments.
Attributes like shipment date, method, and status provide insights into the shipment process, which can help identify potential areas for improvement or optimization.
This design allows for a more comprehensive view of the data and supports various analysis.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help