Simple Star Schema Model for Sales Fact

Post Info: 7,244 views 0 Comments Post a comment

Following is a very simple Data Model to illustrate a Sales Fact Star Schema.

Sales_Fact is the fact table with four dimensions, date_dim, product_dim, store_dim and region_dim connected to it.

Here are the scripts in MySQL for creating the tables used in the model.

Date Dimension: Click following link to get the code to create the date dimension.

Product Dimension: Table Products_D

This is a very simple product dimension. Add more columns if necessary. Sample column may include: product_category, product_type, title, detailed_desc etc.

CREATE TABLE Products_D (
product_id INT PRIMARY KEY,
product_code VARCHAR(10),
product_name VARCHAR(20),
product_desc VARCHAR(50),
unit_price DECIMAL(10,2),
unit_cost DECIMAL(10,2)
);

Store Dimension: Table Stores_D

This is also very simple. More columns like store_city, store_state, address columns, store_division can be added.

CREATE TABLE Stores_D (
store_id INT PRIMARY KEY,
store_code VARCHAR(10),
store_city VARCHAR(20),
region_id INT,
region_name VARCHAR(20)
);

Region Dimension: Table Regions_D

CREATE TABLE Regions_D (
region_id INT PRIMARY KEY,
region_name VARCHAR(20)
);

Sales Fact: Table Sales_Fact

CREATE TABLE Sales_Fact (
date_id INT,
store_id INT,
region_id INT,
product_id INT,
sales_qty INT,
sales_price DECIMAL(10,2),
sales_cost DECIMAL(10,2)
);

Return to top

Leave a Reply

 

Are you finished?

Return to top