Context Navigation

Back to AdvancedTopics

AdvancedTopics: olap_dim.sql

File olap_dim.sql, 10.8 KB (added by 231123, 4 days ago)
Димензии за OLAP

Line
1	--Dimenzii
2
3
4	-- DimDate - tip 1 - ne se menuva
5
6	CREATE TABLE DimDate (
7	date_key INTEGER PRIMARY KEY, -- format YYYYMMDD
8	full_date DATE NOT NULL,
9	day_num SMALLINT NOT NULL, -- 1-31
10	day_name VARCHAR(10) NOT NULL, -- Monday, Tuesday...
11	is_weekend BOOLEAN NOT NULL,
12	week_num SMALLINT NOT NULL, -- 1-52
13	month_num SMALLINT NOT NULL, -- 1-12
14	month_name VARCHAR(10) NOT NULL,
15	quarter SMALLINT NOT NULL, -- 1-4
16	year SMALLINT NOT NULL,
17	season VARCHAR(10) NOT NULL -- Winter/Spring/Summer/Fall
18	);
19
20	INSERT INTO DimDate
21	SELECT
22	TO_CHAR(d, 'YYYYMMDD')::INTEGER,
23	d,
24	EXTRACT(DAY FROM d)::SMALLINT,
25	TO_CHAR(d, 'Day'),
26	EXTRACT(DOW FROM d) IN (0, 6),
27	EXTRACT(WEEK FROM d)::SMALLINT,
28	EXTRACT(MONTH FROM d)::SMALLINT,
29	TO_CHAR(d, 'Month'),
30	EXTRACT(QUARTER FROM d)::SMALLINT,
31	EXTRACT(YEAR FROM d)::SMALLINT,
32	CASE
33	WHEN EXTRACT(MONTH FROM d) IN (12,1,2) THEN 'Winter'
34	WHEN EXTRACT(MONTH FROM d) IN (3,4,5) THEN 'Spring'
35	WHEN EXTRACT(MONTH FROM d) IN (6,7,8) THEN 'Summer'
36	ELSE 'Fall'
37	END
38	FROM generate_series('2015-01-01'::DATE, '2027-12-31'::DATE, '1 day') d;
39
40
41	-- DimHotel - tip 1 - ne se menuva
42
43	CREATE TABLE DimHotel (
44	hotel_key SERIAL PRIMARY KEY,
45	hotel_id INTEGER NOT NULL,
46	hotel_name VARCHAR(100) NOT NULL,
47	city VARCHAR(100) NOT NULL,
48	country VARCHAR(100) NOT NULL DEFAULT 'USA'
49	);
50
51	INSERT INTO DimHotel (hotel_id, hotel_name, city)
52	SELECT hotel_id, name, location
53	FROM Hotel;
54
55
56
57	-- DimRoomType - tip 1 - ne se menuva
58
59	CREATE TABLE DimRoomType (
60	room_type_key SERIAL PRIMARY KEY,
61	room_type_id INTEGER NOT NULL,
62	type_name VARCHAR(50) NOT NULL,
63	description TEXT,
64	price_per_night NUMERIC(10,2) NOT NULL
65	);
66
67	INSERT INTO DimRoomType (room_type_id, type_name, description, price_per_night)
68	SELECT room_type_id, name, description, price_per_night
69	FROM Room_Type;
70
71
72	-- DimRoom - tip 2 - cenata moze da se promeni
73
74	CREATE TABLE DimRoom (
75	room_key SERIAL PRIMARY KEY,
76	room_id INTEGER NOT NULL,
77	room_number VARCHAR(20) NOT NULL,
78	capacity SMALLINT NOT NULL,
79	room_type_key INTEGER REFERENCES DimRoomType(room_type_key),
80	hotel_key INTEGER REFERENCES DimHotel(hotel_key),
81	-- tip 2 koloni
82	valid_from DATE NOT NULL DEFAULT '2015-01-01',
83	valid_to DATE NOT NULL DEFAULT '9999-12-31',
84	is_current BOOLEAN NOT NULL DEFAULT TRUE
85	);
86
87	INSERT INTO DimRoom (room_id, room_number, capacity, room_type_key, hotel_key)
88	SELECT
89	r.room_id,
90	r.room_number,
91	r.capacity,
92	rt.room_type_key,
93	h.hotel_key
94	FROM Room r
95	JOIN DimRoomType rt ON rt.room_type_id = r.room_type_id
96	JOIN DimHotel h ON h.hotel_id = r.hotel_id;
97
98
99
100	-- DimSpecies + DimBreed - tip 1 - ne se menuvaat
101
102	CREATE TABLE DimSpecies (
103	species_key SERIAL PRIMARY KEY,
104	species_id INTEGER NOT NULL,
105	species_name VARCHAR(50) NOT NULL,
106	description TEXT
107	);
108
109	INSERT INTO DimSpecies (species_id, species_name, description)
110	SELECT species_id, name, description
111	FROM Species;
112
113	CREATE TABLE DimBreed (
114	breed_key SERIAL PRIMARY KEY,
115	breed_id INTEGER NOT NULL,
116	breed_name VARCHAR(100) NOT NULL,
117	description TEXT,
118	species_key INTEGER REFERENCES DimSpecies(species_key)
119	);
120
121	INSERT INTO DimBreed (breed_id, breed_name, description, species_key)
122	SELECT
123	b.breed_id,
124	b.name,
125	b.description,
126	s.species_key
127	FROM Breed b
128	JOIN DimSpecies s ON s.species_id = b.species_id;
129
130
131
132	-- DimPet tip 2 - moze da se promeni
133
134	CREATE TABLE DimPet (
135	pet_key SERIAL PRIMARY KEY,
136	pet_id INTEGER NOT NULL,
137	pet_name VARCHAR(100) NOT NULL,
138	gender VARCHAR(10),
139	date_of_birth DATE,
140	age_group VARCHAR(20), -- 'Little', 'Adult', 'Senior'
141	species_key INTEGER REFERENCES DimSpecies(species_key),
142	breed_key INTEGER REFERENCES DimBreed(breed_key),
143	-- tip 2 koloni
144	valid_from DATE NOT NULL DEFAULT '2015-01-01',
145	valid_to DATE NOT NULL DEFAULT '9999-12-31',
146	is_current BOOLEAN NOT NULL DEFAULT TRUE
147	);
148
149	INSERT INTO DimPet (
150	pet_id, pet_name, gender, date_of_birth,
151	age_group, species_key, breed_key
152	)
153	SELECT
154	p.pet_id,
155	p.name,
156	p.gender,
157	p.date_of_birth,
158	CASE
159	WHEN DATE_PART('year', AGE(p.date_of_birth)) < 1 THEN 'Baby'
160	WHEN DATE_PART('year', AGE(p.date_of_birth)) < 3 THEN 'Young'
161	WHEN DATE_PART('year', AGE(p.date_of_birth)) < 8 THEN 'Adult'
162	ELSE 'Senior'
163	END,
164	s.species_key,
165	b.breed_key
166	FROM Pet p
167	JOIN DimSpecies s ON s.species_id = p.species_id
168	JOIN DimBreed b ON b.breed_id = p.breed_id;
169
170
171	-- DimCustomer - tip - 2 moze da se promeni
172
173	CREATE TABLE DimCustomer (
174	customer_key SERIAL PRIMARY KEY,
175	customer_id INTEGER NOT NULL,
176	first_name VARCHAR(50) NOT NULL,
177	last_name VARCHAR(50) NOT NULL,
178	full_name VARCHAR(100) NOT NULL,
179	city VARCHAR(100),
180	registration_date DATE,
181	customer_segment VARCHAR(20), -- 'New', 'Regular', 'VIP'
182	-- tip 2 koloni
183	valid_from DATE NOT NULL DEFAULT '2015-01-01',
184	valid_to DATE NOT NULL DEFAULT '9999-12-31',
185	is_current BOOLEAN NOT NULL DEFAULT TRUE
186	);
187
188	INSERT INTO DimCustomer (
189	customer_id, first_name, last_name,
190	full_name, city, registration_date, customer_segment
191	)
192	SELECT
193	c.customer_id,
194	c.first_name,
195	c.last_name,
196	c.first_name \|\| ' ' \|\| c.last_name,
197	TRIM(SPLIT_PART(SPLIT_PART(c.address, ',', 2), ',', 1)),
198	c.registration_date,
199	CASE
200	WHEN c.registration_date >= CURRENT_DATE - INTERVAL '1 year' THEN 'New'
201	WHEN c.registration_date >= CURRENT_DATE - INTERVAL '3 years' THEN 'Regular'
202	ELSE 'VIP'
203	END
204	FROM Customer c;
205
206
207	-- DimEmployee - tip 2 - role moze da se promeni
208
209	CREATE TABLE DimEmployee (
210	employee_key SERIAL PRIMARY KEY,
211	employee_id INTEGER NOT NULL,
212	first_name VARCHAR(50) NOT NULL,
213	last_name VARCHAR(50) NOT NULL,
214	full_name VARCHAR(100) NOT NULL,
215	role VARCHAR(50) NOT NULL,
216	hotel_key INTEGER REFERENCES DimHotel(hotel_key),
217	hire_date DATE,
218	-- tip 2 koloni
219	valid_from DATE NOT NULL DEFAULT '2015-01-01',
220	valid_to DATE NOT NULL DEFAULT '9999-12-31',
221	is_current BOOLEAN NOT NULL DEFAULT TRUE
222	);
223
224	INSERT INTO DimEmployee (
225	employee_id, first_name, last_name,
226	full_name, role, hotel_key, hire_date
227	)
228	SELECT
229	e.employee_id,
230	e.first_name,
231	e.last_name,
232	e.first_name \|\| ' ' \|\| e.last_name,
233	e.role,
234	h.hotel_key,
235	e.hire_date
236	FROM Employee e
237	JOIN DimHotel h ON h.hotel_id = e.hotel_id;
238
239
240	-- DimService - tip 1 - poretko se menuvaat uslugi
241	CREATE TABLE DimService (
242	service_key SERIAL PRIMARY KEY,
243	service_id INTEGER NOT NULL,
244	service_name VARCHAR(100) NOT NULL,
245	description TEXT,
246	price NUMERIC(10,2) NOT NULL,
247	duration_minutes INTEGER NOT NULL,
248	price_category VARCHAR(20) -- 'Budget', 'Standard', 'Premium'
249	);
250
251	INSERT INTO DimService (
252	service_id, service_name, description,
253	price, duration_minutes, price_category
254	)
255	SELECT
256	service_id,
257	name,
258	description,
259	price,
260	duration_minutes,
261	CASE
262	WHEN price < 20 THEN 'Budget'
263	WHEN price < 40 THEN 'Standard'
264	ELSE 'Premium'
265	END
266	FROM Service;
267
268
269
270	-- DimProduct - tip 1 - ne se menuva
271	CREATE TABLE DimProduct (
272	product_key SERIAL PRIMARY KEY,
273	product_id INTEGER NOT NULL,
274	product_name VARCHAR(100) NOT NULL,
275	category_name VARCHAR(50) NOT NULL,
276	supplier_name VARCHAR(100) NOT NULL,
277	price NUMERIC(10,2) NOT NULL,
278	price_range VARCHAR(20) -- 'Low', 'Mid', 'High'
279	);
280
281	INSERT INTO DimProduct (
282	product_id, product_name, category_name,
283	supplier_name, price, price_range
284	)
285	SELECT
286	p.product_id,
287	p.name,
288	c.name,
289	s.name,
290	p.price,
291	CASE
292	WHEN p.price < 30 THEN 'Low'
293	WHEN p.price < 70 THEN 'Mid'
294	ELSE 'High'
295	END
296	FROM Product p
297	JOIN Category c ON c.category_id = p.category_id
298	JOIN Supplier s ON s.supplier_id = p.supplier_id;

Download in other formats:

Original Format