SiXU7WA0

1Hurtownie danych – Lista 4.  
2PWr. WIZ, Data: 12-17.03.2018
3ImiÄ™ i nazwisko	Kamil MÅ‚ynarczyk	Ocena
4Nr indeksu	228170	
5Zestaw składa się z 9 zadań. Jeżeli nie potrafisz rozwiązać zadania, to próbuj podać chociaż częściowe rozwiązanie lub uzasadnienie przyczyny braku rozwiązania. Pamiętaj o podaniu nr. indeksu oraz imienia i nazwiska. 
6Baza danych: AdventureWorks
7Microsoft SQL Server Management Studio oraz Integration Services (Visual Studio)
8
9Zad 1. W bazie danych należy utworzyć schemat, którego nazwa będzie odpowiadać nazwisku wykonującego ćwiczenie (zapisać zapytanie tworzące ten schemat).
10
11Zad 2. W nowo utworzonym schemacie utworzyć tabele (zapisać skrypt create table), opisane w następujących podpunktach:
12•	DIM_CUSTOMER (CustomerID, FirstName, LastName, TerritoryName, CounrtyRegionCode, Group) – tabele źródłowe:
13·	Sales.SalesTerritory
14·	Sales.Customer
15·	Person.Person
16
17•	DIM_PRODUCT (ProductID, Name, ListPrice, Color, Rating, SubCategoryName, CategoryName) – tabele źródłowe:
18·	Production.Product
19·	Production.ProductReview
20·	Production.ProductSubcategory
21·	Production.ProductCategory
22
23•	DIM_SALESPERSON (SalesPersonID, FirstName, LastName, Title, Gender, CountryRegionCode, Group, Age, Seniority) – tabele źródłowe:
24·	HumanResources.Employee
25·	Person.Person
26·	Sales.SalesTerritory
27·	Sales.SalesPerson
28Uwaga: Wiek sprzedawcy i staż pracy należy wyliczyć wykorzystując pole BirthDate i HireDate.
29
30•	FACT_SALES (ProductID, CustomerID, SalesPersonID, OrderDate, ShipDate, OrderQty, UnitPrice, UnitPriceDiscount, LineTotal) – tabele źródłowe:
31·	Sales.SalesOrderDetail
32·	Sales.SalesOrderHeader
33Uwaga: Kolumny OrderDate oraz ShipDate przechowują dane typu całkowitego, gdzie cztery pierwsze cyfry oznaczają rok, dwie następne miesiąc, a dwie ostatnie dzień. Do pobrania poszczególnych części daty użyć funkcji datepart.
34
35Zad. 3. Wypełnić nowoutworzone tabele danymi znajdującymi się w tabelach źródłowych. Do wypełnienia użyć instrukcji INSERT INTO.
36Uwaga 1. Do tabeli DIM_PRODUCT należy także skopiować produkty, które nie mają przypisanej podkategorii.
37Uwaga 2. Do tabeli FACT_SALES należy skopiować transakcje, które nie mają sprzedawcy.
38
39Zad. 4. Dodać integralność referencyjną i klucze główne do tabel już zdefiniowanych.
40
41Zad. 5. Przygotować instrukcję INSERT INTO, która sprawdzi poprawność integralności referencyjnej oraz klucze główne.
42
43Zad. 6. Przygotować instrukcję usuwającą każdą z tabel utworzonych w trakcie dotychczasowej pracy.
44Uwaga: Instrukcja powinna być wykonana tylko pod warunkiem istnienia usuwanej tabeli. Należy sprawdzić, czy dana tabela istnieje, używając instrukcji IF oraz informacji zawartych w widoku systemowym INFORMATION_SCHEMA.TABLES.
45
46Zad. 7. Używając Visual Studio utworzyć projekt typu Integration Services (wybierając z Menu File -> New Project) zawierający zapytania SQL opracowane w zadaniach 1-6.
47Uwaga: Do umieszczenia instrukcji SQL w treści pakietu użyć zadania Execute SQL Task (Menu – View – Toolbox).
48Utworzony pakiet powinien działać sekwencyjnie i wykonywać następujące zadania:
49a)	Usunąć tabele z przedrostkiem DIM i FACT (oczywiście usunąć tylko te, które istnieją),
50b)	Utworzyć tabele z przedrostkiem DIM i FACT,
51c)	Wypełnić tabele danymi (instrukcje INSERT INTO),
52d)	Dodać więzy integralności z zadania 4,
53e)	Obsłużyć błędy i wyjątki,
54f)	Wysłać informację o pozytywnie zakończonym procesie na swój adres mailowy.
55
56Zad. 8. Wykonać polecenia z zad. 7 przy użyciu narzędzi dostępnych w zakładce Data Flow.
57
58Zad. 9. Do utworzonych pakietów dodać trzy zadania, które powinny:
59a)	Utworzyć i wypełnić danymi tabelę DIM_TIME. Tabela DIM_TIME powinna być tabelą zawierającą wymiar czasowy (klucze obce do tej tabeli znajdują się w tabeli faktów).
60b)	Tabela DIM_TIME powinna zawierać następujące kolumny:
61o	PK_TIME (klucz główny – liczba całkowita postaci yyyymmdd – format taki sam jak kolumn OrderDate, ShipDate)
62o	Rok
63o	Miesiąc słownie (wykorzystać tabelę pomocniczą z 12 rekordami dokonać odpowiedniego złączenia)
64o	Dzień tygodnia (wykorzystać tabelę pomocniczą z 7 rekordami dokonać odpowiedniego złączenia)
65o	Dzień miesiąca
66c)	Zamienić wszystkie wartości NULL w tabeli DIM_PRODUCT:
67o	w kolumnie Color (tabela DIM_PRODUCT) na „Unknown”,
68o	w kolumnie SubCategoryName (tabela DIM_PRODUCT) na „Unknown”.
69d)	Zamienić wszystkie wartości NULL w tabeli DIM_SALESPERSON:
70o	w kolumnie CountryRegionCode na 000,
71o	w kolumnie Group na „Unknown”.
72
73RozwiÄ…zania:
74
75Wnioski:
76
77
78Uwaga!
79•	Sprawozdanie, bez wniosków podsumowujących aspekt zagadnień analizowanych na zajęciach laboratoryjnych i zawartych w sprawozdaniu, jest automatycznie oceniane negatywnie!
80
81
82-- ZADANIE 1
83DROP SCHEMA Mlynarczyk;
84CREATE SCHEMA Mlynarczyk;
85-- ZADANIE 2
86
87-- DIM_CUSTOMER
88CREATE TABLE Mlynarczyk.DIM_CUSTOMER 
89(
90	CustomerID int NOT NULL,
91	FirstName NVARCHAR(100) NOT NULL,
92	LastName NVARCHAR(100) NOT NULL,
93	TerritoryName NVARCHAR(50) NOT NULL,
94	CountryRegionCode NVARCHAR(3) NOT NULL,
95	"Group" NVARCHAR(50) NOT NULL
96);
97
98--DIM_PRODUCT
99CREATE TABLE Mlynarczyk.DIM_PRODUCT 
100(
101	ProductID int NOT NULL,
102	"Name" NVARCHAR(50) NOT NULL,
103	ListPrice Money NOT NULL,
104	Color NVARCHAR(15),
105	Rating FLOAT,
106	SubCategoryName NVARCHAR(50),
107	CategoryName NVARCHAR(50)
108);
109
110-- DIM_SALESPERSON
111CREATE TABLE Mlynarczyk.DIM_SALESPERSON 
112(
113	SalesPersonID INT NOT NULL,
114	FirstName NVARCHAR(50) NOT NULL,
115	LastName NVARCHAR(50) NOT NULL,
116	Title NVARCHAR(50) NOT NULL,
117	Gender NCHAR(1) NOT NULL,
118	CountryRegionCode NVARCHAR(3),
119	"Group" NVARCHAR(50),
120	Age INT NOT NULL,
121	Seniority INT NOT NULL
122);
123
124-- FACT_SALES
125CREATE TABLE Mlynarczyk.FACT_SALES
126(
127	ProductID INT NOT NULL,
128	CustomerID INT NOT NULL,
129	SalesPersonID INT,
130	OrderQty SMALLINT NOT NULL,
131	OrderDate int,
132	ShipDate int,
133	UnitPrice MONEY NOT NULL,
134	UnitPriceDiscount MONEY NOT NULL,
135	LineTotal AS UnitPrice*(1-UnitPriceDiscount)*OrderQty
136);
137
138--3
139
140INSERT INTO Mlynarczyk.DIM_CUSTOMER(CustomerID, FirstName, LastName, TerritoryName, 
141CountryRegionCode, "Group") 
142SELECT 
143	c.CustomerID,
144	p.FirstName,
145	p.LastName,
146	st.Name,
147	st.CountryRegionCode,
148	st."Group"
149FROM Person.Person p 
150JOIN Sales.Customer c ON p.BusinessEntityID = c.PersonID 
151JOIN Sales.SalesTerritory st ON c.TerritoryID = st.TerritoryID;
152
153INSERT INTO Mlynarczyk.DIM_PRODUCT(ProductID, Name, ListPrice, Color, Rating, 
154SubCategoryName, CategoryName)
155SELECT
156	p.ProductID,
157	p.Name,
158	p.ListPrice,
159	p.Color,
160	AVG(pr.Rating),
161	sub.Name,
162	cat.Name
163FROM Production.Product p 
164LEFT JOIN Production.ProductReview pr ON p.ProductID = pr.ProductID
165LEFT JOIN Production.ProductSubcategory sub ON p.ProductSubcategoryID = sub.ProductSubcategoryID
166LEFT JOIN Production.ProductCategory cat ON sub.ProductCategoryID = cat.ProductCategoryID
167	GROUP BY p.ProductID, p.Name, p.ListPrice, p.Color, sub.Name, cat.Name;
168
169INSERT INTO Mlynarczyk.DIM_SALESPERSON(SalesPersonID, FirstName, LastName, Title, Gender, CountryRegionCode, "Group", Age, Seniority)
170SELECT 	
171	sp.BusinessEntityID,
172	p.FirstName,
173	p.LastName,
174	e.JobTitle,
175	e.Gender,
176	st.CountryRegionCode,
177	st."Group",
178	DATEDIFF(YY,e.BirthDate, GETDATE()),
179	DATEDIFF(YY,e.HireDate, GETDATE()) 
180FROM Sales.SalesPerson sp 
181JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID 
182JOIN HumanResources.Employee e ON sp.BusinessEntityID = e.BusinessEntityID
183JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID;
184
185
186INSERT INTO Mlynarczyk.FACT_SALES(ProductID, CustomerID, SalesPersonID, OrderDate, ShipDate, OrderQty,UnitPrice, UnitPriceDiscount)
187SELECT 
188	sod.ProductID,
189	soh.CustomerID,
190	soh.SalesPersonID,
191	DATEPART(YYYY, OrderDate)*10000 + 
192	(CASE 
193		WHEN DATEPART(MONTH, OrderDate) < 10 THEN '0' + DATEPART(MONTH, OrderDate) 
194		ELSE DATEPART(MONTH, OrderDate)
195	END)*100 +
196	(CASE 
197		WHEN DATEPART(DAY, OrderDate) < 10 THEN '0' + DATEPART(DAY, OrderDate)
198		ELSE DATEPART(DAY, OrderDate)
199	END),
200	DATEPART(YYYY, ShipDate)*10000 + 
201	(CASE 
202		WHEN DATEPART(MONTH, ShipDate) < 10 THEN '0' + DATEPART(MONTH, ShipDate) 
203		ELSE DATEPART(MONTH, ShipDate)
204	END)*100 +
205	(CASE 
206		WHEN DATEPART(DAY, ShipDate) < 10 THEN '0' + DATEPART(DAY, ShipDate)
207		ELSE DATEPART(DAY, ShipDate)
208	END),
209	sod.OrderQty,
210	sod.UnitPrice,
211	sod.UnitPriceDiscount
212FROM Sales.SalesOrderHeader soh 
213JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID;
214
215--4 
216
217ALTER TABLE Mlynarczyk.DIM_CUSTOMER
218ADD PRIMARY KEY (CustomerID);
219
220ALTER TABLE Mlynarczyk.DIM_PRODUCT
221ADD PRIMARY KEY (ProductID);
222
223ALTER TABLE Mlynarczyk.DIM_SALESPERSON
224ADD PRIMARY KEY (SalesPersonID);
225
226ALTER TABLE Mlynarczyk.FACT_SALES
227ADD FOREIGN KEY (ProductID) REFERENCES Mlynarczyk.DIM_PRODUCT(ProductID);
228
229ALTER TABLE Mlynarczyk.FACT_SALES
230ADD FOREIGN KEY (CustomerID) REFERENCES Mlynarczyk.DIM_CUSTOMER(CustomerID);
231
232ALTER TABLE Mlynarczyk.FACT_SALES WITH NOCHECK
233ADD FOREIGN KEY (SalesPersonID) REFERENCES Mlynarczyk.DIM_SALESPERSON(SalesPersonID);
234
235--4 
236
237INSERT INTO Mlynarczyk.FACT_SALES VALUES
238(1200, 11001, 275, 1,19941212,19941212,12,1), 
239(1, 1, 12, 275,19941212,19941212,12,1), 
240(1, 11001, 270, 1,19941212,19941212,12,1);
241
242INSERT INTO Mlynarczyk.DIM_CUSTOMER VALUES
243(11000, 'Kamil', 'Mlynarczyk', 'Poland', 'PL', 'Europe');
244
245INSERT INTO Mlynarczyk.DIM_PRODUCT VALUES
246(1, ' MTB 12" ', 3000, 'Black', 2, NULL, NULL);
247
248INSERT INTO Mlynarczyk.DIM_SALESPERSON VALUES
249(275, 'Manon', 'Mandino', 'Salesman', 'M', 'PL' , 'EUROPE', 21, 1);
250
251
252--6
253
254IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 
255'Mlynarczyk' AND TABLE_NAME = 'FACT_SALES')
256DROP TABLE Mlynarczyk.FACT_SALES;
257
258IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA =
259'Mlynarczyk' AND TABLE_NAME = 'DIM_PRODUCT')
260DROP TABLE Mlynarczyk.DIM_PRODUCT;
261
262IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA =
263'Mlynarczyk' AND TABLE_NAME = 'DIM_SALESPERSON')
264DROP TABLE Mlynarczyk.DIM_SALESPERSON;
265
266IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA =
267'Mlynarczyk' AND TABLE_NAME = 'DIM_CUSTOMER')
268DROP TABLE Mlynarczyk.DIM_CUSTOMER;
269
270IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA =
271'Mlynarczyk' AND TABLE_NAME = 'DIM_TIME')
272DROP TABLE Mlynarczyk.DIM_TIME;
273
274--9
275
276CREATE TABLE Mlynarczyk.DIM_TIME 
277(
278	PK_TIME int NOT NULL,
279	Rok NVARCHAR(4) NOT NULL,
280	"MiesiÄ…c slownie" NVARCHAR(11) NOT NULL,
281	"Dzień tygodnia" NVARCHAR(12) NOT NULL,
282	"Dzień miesiąca" int NOT NULL,
283);
284
285ALTER TABLE Mlynarczyk.DIM_TIME
286ADD PRIMARY KEY (PK_TIME);
287
288INSERT INTO Mlynarczyk.DIM_TIME 
289	SELECT base.OrderDate, 
290	YEAR(CONVERT(date, CAST(base.OrderDate as varchar), 112)) as yer, 
291	mm.polmonth,
292	dd.polday,
293	SUBSTRING(CAST(base.OrderDate as varchar),7,2)
294	FROM (SELECT OrderDate FROM Mlynarczyk.FACT_SALES GROUP BY OrderDate) base 
295	JOIN (
296		select DATENAME(day, CONVERT(date, '20180312', 112)) as engday,'Poniedzialek' as polday
297		UNION
298		select DATENAME(day, CONVERT(date, '20180313', 112)) ,'Wtorek'
299		UNION
300		select DATENAME(day, CONVERT(date, '20180314', 112)) ,'Åšroda'
301		UNION
302		select DATENAME(day, CONVERT(date, '20180315', 112)) ,'Czwartek'
303		UNION
304		select DATENAME(day, CONVERT(date, '20180316', 112)) ,'PiÄ…tek'
305		UNION
306		select DATENAME(day, CONVERT(date, '20180317', 112)) ,'Sobota'
307		UNION
308		select DATENAME(day, CONVERT(date, '20180318', 112)) ,'Niedziela'
309	) dd ON engday = DATENAME(day, CONVERT(date, CAST(base.OrderDate as varchar), 112))
310	JOIN (
311		select DATENAME(month, CONVERT(date, '20180101', 112)) as engmonth,'Styczeń' as polmonth
312		UNION
313		select DATENAME(month, CONVERT(date, '20180201', 112)),'Styczeń'
314		UNION
315		select DATENAME(month, CONVERT(date, '20180301', 112)),'Luty'
316		UNION
317		select DATENAME(month, CONVERT(date, '20180401', 112)),'Kwiecień'
318		UNION
319		select DATENAME(month, CONVERT(date, '20180501', 112)),'Maj'
320		UNION
321		select DATENAME(month, CONVERT(date, '20180601', 112)),'Czerwiec'
322		UNION
323		select DATENAME(month, CONVERT(date, '20180701', 112)),'Lipiec'
324		UNION
325		select DATENAME(month, CONVERT(date, '20180801', 112)),'Sierpień'
326		UNION
327		select DATENAME(month, CONVERT(date, '20180901', 112)),'Wrzesień'
328		UNION
329		select DATENAME(month, CONVERT(date, '20181001', 112)),'Październik'
330		UNION
331		select DATENAME(month, CONVERT(date, '20181101', 112)), 'Listopad'
332		UNION
333		select DATENAME(month, CONVERT(date, '20181201', 112)),'Grudzień'
334	) mm ON engmonth = DATENAME(month, CONVERT(date, CAST(base.OrderDate as varchar), 112))
335
336	UNION
337
338	SELECT base.ShipDate, 
339	YEAR(CONVERT(date, CAST(base.ShipDate as varchar), 112)) as yer, 
340	mm.polmonth,
341	dd.polday,
342	SUBSTRING(CAST(base.ShipDate as varchar),7,2)
343	FROM (SELECT ShipDate FROM Mlynarczyk.FACT_SALES GROUP BY ShipDate) base 
344	JOIN (
345		select DATENAME(day, CONVERT(date, '20180312', 112)) as engday,'Poniedzialek' as polday
346		UNION
347		select DATENAME(day, CONVERT(date, '20180313', 112)) ,'Wtorek'
348		UNION
349		select DATENAME(day, CONVERT(date, '20180314', 112)) ,'Åšroda'
350		UNION
351		select DATENAME(day, CONVERT(date, '20180315', 112)) ,'Czwartek'
352		UNION
353		select DATENAME(day, CONVERT(date, '20180316', 112)) ,'PiÄ…tek'
354		UNION
355		select DATENAME(day, CONVERT(date, '20180317', 112)) ,'Sobota'
356		UNION
357		select DATENAME(day, CONVERT(date, '20180318', 112)) ,'Niedziela'
358	) dd ON engday = DATENAME(day, CONVERT(date, CAST(base.ShipDate as varchar), 112))
359	JOIN (
360		select DATENAME(month, CONVERT(date, '20180101', 112)) as engmonth,'Styczeń' as polmonth
361		UNION
362		select DATENAME(month, CONVERT(date, '20180201', 112)),'Styczeń'
363		UNION
364		select DATENAME(month, CONVERT(date, '20180301', 112)),'Luty'
365		UNION
366		select DATENAME(month, CONVERT(date, '20180401', 112)),'Kwiecień'
367		UNION
368		select DATENAME(month, CONVERT(date, '20180501', 112)),'Maj'
369		UNION
370		select DATENAME(month, CONVERT(date, '20180601', 112)),'Czerwiec'
371		UNION
372		select DATENAME(month, CONVERT(date, '20180701', 112)),'Lipiec'
373		UNION
374		select DATENAME(month, CONVERT(date, '20180801', 112)),'Sierpień'
375		UNION
376		select DATENAME(month, CONVERT(date, '20180901', 112)),'Wrzesień'
377		UNION
378		select DATENAME(month, CONVERT(date, '20181001', 112)),'Październik'
379		UNION
380		select DATENAME(month, CONVERT(date, '20181101', 112)), 'Listopad'
381		UNION
382		select DATENAME(month, CONVERT(date, '20181201', 112)),'Grudzień'
383	) mm ON engmonth = DATENAME(month, CONVERT(date, CAST(base.ShipDate as varchar), 112));