Data Modeling - Creation of Facts and Dimension

CREATE TABLE dim_Product (
sk_product_id AS (CAST(Product_ID AS VARCHAR(10)) + Product_Name) PERSISTED NOT NULL PRIMARY KEY,
Product_ID INT not null,
Product_Name VARCHAR(255) not null,
Category VARCHAR(100),
Brand VARCHAR(100),
Supplier VARCHAR(100),
Cost_Price DECIMAL(10, 2),
product_start_date date,
product_end_date date,
active_flag varchar(10)
);


CREATE TABLE dim_Time (
sk_time_id AS (CONVERT(VARCHAR(10), Time_ID, 120) + ' ' + Month + Quarter + CAST(Year AS VARCHAR(4))) PERSISTED NOT NULL PRIMARY KEY,
Time_ID DATE NOT NULL,
Day_Of_Week VARCHAR(10),
Month VARCHAR(20),
Quarter VARCHAR(10),
Year INT,
Holiday_Indicator CHAR(1),
start_date DATE,
end_date DATE,
active_flag VARCHAR(10)
);




CREATE TABLE dim_Customer (
sk_cust_id AS (CAST(Customer_ID AS VARCHAR(10)) + Name + Age_Group) PERSISTED NOT NULL PRIMARY KEY,
Customer_ID INT NOT NULL,
Name VARCHAR(255) NOT NULL,
Gender CHAR(1),
Age_Group VARCHAR(20),
Location VARCHAR(255),
Loyalty_Membership CHAR(1),
start_date DATE,
end_date DATE,
active_flag VARCHAR(10)
);



CREATE TABLE dim_Store (
sk_store_id AS (CAST(Store_ID AS VARCHAR(10)) + Store_Name + Location) PERSISTED NOT NULL PRIMARY KEY,
Store_ID INT NOT NULL,
Store_Name VARCHAR(255) NOT NULL,
Location VARCHAR(255) NOT NULL,
Store_Manager VARCHAR(255),
Size VARCHAR(50),
start_date DATE,
end_date DATE,
active_flag VARCHAR(10)
);



CREATE TABLE Fact_Sales (
Sales_ID INT PRIMARY KEY,
Product_ID VARCHAR(265),
Time_ID VARCHAR(45),
Customer_ID VARCHAR(285),
Store_ID VARCHAR(520),
Sales_Amount DECIMAL(10, 2),
Quantity_Sold INT,
Discount_Amount DECIMAL(10, 2),

FOREIGN KEY (Product_ID) REFERENCES dim_Product(sk_product_id),
FOREIGN KEY (Time_ID) REFERENCES dim_Time(sk_time_id),
FOREIGN KEY (Customer_ID) REFERENCES dim_Customer(sk_cust_id),
FOREIGN KEY (Store_ID) REFERENCES dim_Store(sk_store_id)
);