I have given this course Online: these are the notes
Sources === > staging zone ===> final destination
Exmple: DB2 , oracle database , sql server, csv files,excel ===> transformed data ===> in the staging zone ===> final destination
SSIS: integration
SSRS: Reporting
BI : Integrate (source ===> destination) after transforming ,,,,,REports (dashboards, …)
Poulina : MSBI + Powerbi , Qliksense ,quiksense
Clean/transformed Data ===> Data Warehouse ====> Data marts
Data mart for the marketing department
Data mart for the sales department
Data mart for HR department t
BI Analyst or Data Engineering/data analyst
ETL
1. First Normal Form (1NF)
2. Second Normal Form (1NF)
30
1 unique column 29
(col1,col2,col3 ) : coll123 primary key composed
27
Order table: primary key
2 column ⇒ (ag_no_code ,gb_order_code) = primary key
Normalize , denormalize
24 18
24 50
Model star schema ⇒when we denormalize ===> model following snowflake schema
Snowflake: fact + dimensions (dimensions for dimensions): denormalize our model, data
Star: fact + dims: normalize our model, data
https://www.studytonight.com/dbms/database-normalization.php
Fact table
dim s
Relationships: 1 to 1, 1 to many. many to one
1 have many customers
Store_p_1
Sales fact
Dimensions: date
Product
Customer
Codes:
CREATE TABLE sales (
sales_id PRIMARY KEY,
date_id INT REFERENCES date_dimension(date_id),
product_id INT REFERENCES product_dimension(product_id),
customer_id INT REFERENCES customer_dimension(customer_id),
quantity_sold INT,
amount_sold DECIMAL(10, 2)
);
CREATE TABLE date_dimension (
date_id PRIMARY KEY,
calendar_date DATE,
day_of_week VARCHAR(10),
month VARCHAR(10),
quarter VARCHAR(5),
year INT
);
CREATE TABLE product_dimension (
product_id PRIMARY KEY,
product_name VARCHAR(255),
category VARCHAR(50),
manufacturer VARCHAR(100)
);
CREATE TABLE customer_dimension (
customer_id PRIMARY KEY,
customer_name VARCHAR(255),
city VARCHAR(100),
country VARCHAR(100)
);
-- Dimension Table 4 (Location Dimension):
CREATE TABLE location_dimension (
location_id PRIMARY KEY,
location_name VARCHAR(255),
city VARCHAR(100),
state VARCHAR(50),
country VARCHAR(100)
);
-- Dimension Table 5 (Promotion Dimension):
CREATE TABLE promotion_dimension (
Promotion_id PRIMARY KEY,
promotion_name VARCHAR(255),
start_date DATE,
end_date DATE,
promotion_type VARCHAR(50)
);
CREATE TABLE sales (
Sales_id PRIMARY KEY,
date_id INT REFERENCES date_dimension(date_id),
product_id INT REFERENCES product_dimension(product_id),
customer_id INT REFERENCES customer_dimension(customer_id),
location_id INT REFERENCES location_dimension(location_id),
promotion_id INT REFERENCES promotion_dimension(promotion_id),
quantity_sold INT,
amount_sold DECIMAL(10, 2)
);
ALTER TABLE sales
ADD COLUMN location_id INT REFERENCES location_dimension(location_id),
ADD COLUMN promotion_id INT REFERENCES promotion_dimension(promotion_id);
