NUEVA ECIJA UNIVERSITY OF SCIENCE AND TECHNOLOGY
GS
MASTER OF SCIENCE IN INFORMATION TECHNOLOGY
CHAPTER IV
DIMENSIONAL MODELING
DR. RACHEL T. ALEGADO
PROFESSOR
NUEVA ECIJA UNIVERSITY OF SCIENCE AND TECHNOLOGY
GS
MASTER OF SCINCE IN INFORMATION TECHNOLOGY
Step 1: Define the ER Model
The ER model for the Online Retail Store consists of the following entities,
attributes, and relationships:
Entities
1. Customer
Attributes: CustomerID (PK), Name, Email, Phone
2. Product
Attributes: ProductID (PK), Name, Category, Price
3. Order
Attributes: OrderID (PK), OrderDate, CustomerID (FK), TotalAmount
4. OrderItem
Attributes: OrderItemID (PK), OrderID (FK), ProductID (FK), Quantity, LineTotal
Chapter IV Dimensional Modeling
NUEVA ECIJA UNIVERSITY OF SCIENCE AND TECHNOLOGY
GS
MASTER OF SCINCE IN INFORMATION TECHNOLOGY
Step 2: Identify the Business Process
Determine the primary business process the dimensional model will
represent. For the Online Retail Store, the primary business process
could be Sales Transactions—capturing sales data and tracking
customer purchases.
Chapter IV Dimensional Modeling
NUEVA ECIJA UNIVERSITY OF SCIENCE AND TECHNOLOGY
GS
MASTER OF SCINCE IN INFORMATION TECHNOLOGY
Step 3: Identify the Fact Table
The fact table represents the measurable events in the business
process. In this case, the Sales Fact table will contain details of each
sales transaction.
Fact Table: Sales
Grain (Level of Detail): One row per order item (each line item in an
order)
Measures:
Quantity
LineTotal (calculated as Quantity * Price per item)
TotalAmount (for the entire order)
Chapter IV Dimensional Modeling
NUEVA ECIJA UNIVERSITY OF SCIENCE AND TECHNOLOGY
GS
MASTER OF SCINCE IN INFORMATION TECHNOLOGY
Step 4: Identify the Dimensions
Dimensions describe the context of the business process. For this
example, dimensions will be:
Customer Dimension: Contains details about customers.
Attributes: CustomerID, Name, Email, Phone
Product Dimension: Contains details about products.
Attributes: ProductID, Name, Category, Price
Date Dimension: Tracks the date of each sale.
Attributes: DateKey, FullDate, Year, Quarter, Month, Day
Chapter IV Dimensional Modeling
NUEVA ECIJA UNIVERSITY OF SCIENCE AND TECHNOLOGY
GS
MASTER OF SCINCE IN INFORMATION TECHNOLOGY
Step 5: Design the Fact Table Structure
Create the Sales Fact Table with the following columns:
Keys:
OrderItemID (PK of the fact table)
CustomerID (FK, links to the Customer Dimension)
ProductID (FK, links to the Product Dimension)
DateKey (FK, links to the Date Dimension)
Measures:
Quantity
LineTotal
TotalAmount
Chapter IV Dimensional Modeling
NUEVA ECIJA UNIVERSITY OF SCIENCE AND TECHNOLOGY
GS
MASTER OF SCINCE IN INFORMATION TECHNOLOGY
Step 6: Design the Dimension Tables
Each dimension table should be designed to provide context to the
fact table data.
Customer Dimension Table
Attributes: CustomerID (PK), Name, Email, Phone
Product Dimension Table
Attributes: ProductID (PK), Name, Category, Price
Date Dimension Table
Attributes: DateKey (PK), FullDate, Year, Quarter, Month, Day
Chapter IV Dimensional Modeling
NUEVA ECIJA UNIVERSITY OF SCIENCE AND TECHNOLOGY
GS
MASTER OF SCINCE IN INFORMATION TECHNOLOGY
Step 7: Create the Star Schema
Combine the fact table and the dimension tables into a star
schema:
•Sales Fact Table (center of the schema) connects to:
• Customer Dimension
• Product Dimension
• Date Dimension
Each dimension table directly links to the Sales Fact Table,
creating a star-shaped structure.
Chapter IV Dimensional Modeling
NUEVA ECIJA UNIVERSITY OF SCIENCE AND TECHNOLOGY
GS
MASTER OF SCINCE IN INFORMATION TECHNOLOGY
Step 8: Validate and Optimize the Model
[Link] for Consistency: Verify foreign keys in the fact
table match primary keys in the dimension tables.
[Link]: Add surrogate keys if needed and ensure the
Date Dimension is preloaded with date values to optimize
query performance.
This completes the dimensional model for the Online Retail
Store’s Sales process.
Chapter IV Dimensional Modeling
NUEVA ECIJA UNIVERSITY OF SCIENCE AND TECHNOLOGY
CICT