· 6 years ago · May 14, 2019, 09:42 PM
1-- DROP STATEMENTS
2
3DROP TABLE IF EXISTS DataWarehouse.Fact2;
4
5DROP TABLE IF EXISTS DataWarehouse.Fact1;
6
7DROP TABLE IF EXISTS DataWarehouse.ShipMethod;
8
9DROP TABLE IF EXISTS DataWarehouse.ProductModel;
10
11DROP TABLE IF EXISTS DataWarehouse.SaleDate;
12
13DROP TABLE IF EXISTS #SaleDate;
14
15DROP TABLE IF EXISTS DataWarehouse.Product;
16
17DROP TABLE IF EXISTS DataWarehouse.Territory;
18
19DROP SCHEMA IF EXISTS DataWarehouse;
20
21GO
22
23CREATE SCHEMA DataWarehouse;
24
25GO
26
27
28
29-- DIMENSION TABLES CREATION
30
31CREATE TABLE DataWarehouse.Territory(
32 SKEY INT IDENTITY (1, 1) PRIMARY KEY,
33 TerritoryID INT
34);
35
36CREATE TABLE DataWarehouse.Product(
37 SKEY INT IDENTITY (1, 1) PRIMARY KEY,
38 ProductID INT
39);
40
41CREATE TABLE #SaleDate(
42 SKEY INT,
43 SaleDate DATE
44);
45
46CREATE TABLE DataWarehouse.SaleDate(
47 SKEY INT NOT NULL PRIMARY KEY,
48 SaleDate DATE
49);
50
51CREATE TABLE DataWarehouse.ProductModel(
52 SKEY INT IDENTITY (1, 1) PRIMARY KEY,
53 ProductModelID INT
54);
55
56CREATE TABLE DataWarehouse.ShipMethod(
57 SKEY INT IDENTITY (1, 1) PRIMARY KEY,
58 ShipMethodID INT
59);
60
61
62
63-- FACT TABLES CREATION
64
65CREATE TABLE DataWarehouse.Fact1(
66 Territory_SKEY INT FOREIGN KEY REFERENCES DataWarehouse.Territory(SKEY),
67 Product_SKEY INT FOREIGN KEY REFERENCES DataWarehouse.Product(SKEY),
68 OrderDate_SKEY INT FOREIGN KEY REFERENCES DataWarehouse.SaleDate(SKEY),
69 TotalProductsSold INT,
70 TotalSum NUMERIC
71);
72
73CREATE TABLE DataWarehouse.Fact2(
74 Territory_SKEY INT FOREIGN KEY REFERENCES DataWarehouse.Territory(SKEY),
75 ProductModel_SKEY INT FOREIGN KEY REFERENCES DataWarehouse.ProductModel(SKEY),
76 ShipMethod_SKEY INT FOREIGN KEY REFERENCES DataWarehouse.ShipMethod(SKEY),
77 ShipDate_SKEY INT FOREIGN KEY REFERENCES DataWarehouse.SaleDate(SKEY),
78 TotalProductsSold INT,
79 TotalSum NUMERIC
80);
81
82
83
84-- DIMENSION TABLES INSERTION
85
86INSERT INTO DataWarehouse.Territory(TerritoryID)
87SELECT ST.TerritoryID
88FROM Sales.SalesTerritory ST
89 LEFT OUTER JOIN DataWarehouse.Territory DWT ON ST.TerritoryID = DWT.TerritoryID
90WHERE DWT.TerritoryID IS NULL;
91
92INSERT INTO DataWarehouse.Product(ProductID)
93SELECT P.ProductID
94FROM Production.Product P
95 LEFT OUTER JOIN DataWarehouse.Product DWP ON P.ProductID = DWP.ProductID
96WHERE DWP.ProductID IS NULL;
97
98INSERT INTO #SaleDate(SKEY, SaleDate)
99SELECT CAST(CONVERT(VARCHAR, OH.OrderDate, 112) AS INT), CAST(OH.OrderDate AS DATE)
100FROM Sales.SalesOrderHeader OH;
101
102INSERT INTO #SaleDate(SKEY, SaleDate)
103SELECT CAST(CONVERT(VARCHAR, OH.ShipDate, 112) AS INT), CAST(OH.ShipDate AS DATE)
104FROM Sales.SalesOrderHeader OH;
105
106INSERT INTO DataWarehouse.SaleDate(SKEY, SaleDate)
107SELECT DISTINCT(SD.SKEY), SD.SaleDate
108FROM #SaleDate SD
109 LEFT OUTER JOIN DataWarehouse.SaleDate DWSD ON SD.SKEY = DWSD.SKEY
110WHERE DWSD.SKEY IS NULL;
111
112DROP TABLE #SaleDate;
113
114INSERT INTO DataWarehouse.ProductModel(ProductModelID)
115SELECT PM.ProductModelID
116FROM Production.ProductModel PM
117 LEFT OUTER JOIN DataWarehouse.ProductModel DWPM ON PM.ProductModelID = DWPM.ProductModelID
118WHERE DWPM.SKEY IS NULL;
119
120INSERT INTO DataWarehouse.ShipMethod(ShipMethodID)
121SELECT SM.ShipMethodID
122FROM Purchasing.ShipMethod SM
123 LEFT OUTER JOIN DataWarehouse.ShipMethod DWSM ON SM.ShipMethodID = DWSM.ShipMethodID
124WHERE DWSM.ShipMethodID IS NULL;
125
126
127
128-- FACT TABLES INSERTION
129
130INSERT INTO DataWarehouse.Fact1(Territory_SKEY, Product_SKEY, OrderDate_SKEY, TotalProductsSold, TotalSum)
131SELECT DWT.SKEY,
132 DWP.SKEY,
133 DWSD.SKEY,
134 SUM(OD.OrderQty),
135 SUM(OD.LineTotal)
136FROM Sales.SalesOrderHeader OH
137 JOIN Sales.SalesOrderDetail OD ON OH.SalesOrderID = OD.SalesOrderID
138 JOIN Sales.Customer C ON OH.CustomerID = C.CustomerID
139 JOIN DataWarehouse.Territory DWT ON C.TerritoryID = DWT.TerritoryID
140 JOIN DataWarehouse.Product DWP ON OD.ProductID = DWP.ProductID
141 JOIN DataWarehouse.SaleDate DWSD ON OH.OrderDate = DWSD.SaleDate
142 LEFT OUTER JOIN DataWarehouse.Fact1 F1 ON DWT.SKEY = F1.Territory_SKEY AND
143 DWP.SKEY = F1.Product_SKEY AND
144 DWSD.SKEY = F1.OrderDate_SKEY
145WHERE F1.Territory_SKEY IS NULL AND
146 F1.Product_SKEY IS NULL AND
147 F1.OrderDate_SKEY IS NULL
148GROUP BY DWT.SKEY,
149 DWP.SKEY,
150 DWSD.SKEY;
151
152INSERT INTO DataWarehouse.Fact2(Territory_SKEY, ProductModel_SKEY, ShipMethod_SKEY, ShipDate_SKEY, TotalProductsSold, TotalSum)
153SELECT DWT.SKEY,
154 DWPM.SKEY,
155 DWSM.SKEY,
156 DWSD.SKEY,
157 SUM(OD.OrderQty),
158 SUM(OD.LineTotal)
159FROM Sales.SalesOrderHeader OH
160 JOIN Sales.SalesOrderDetail OD ON OH.SalesOrderID = OD.SalesOrderID
161 JOIN Sales.Customer C ON OH.CustomerID = C.CustomerID
162 JOIN Production.Product P ON OD.ProductID = P.ProductID
163 JOIN DataWarehouse.Territory DWT ON C.TerritoryID = DWT.TerritoryID
164 JOIN DataWarehouse.ProductModel DWPM ON P.ProductModelID = DWPM.ProductModelID
165 JOIN DataWarehouse.ShipMethod DWSM ON OH.ShipMethodID = DWSM.ShipMethodID
166 JOIN DataWarehouse.SaleDate DWSD ON OH.ShipDate = DWSD.SaleDate
167 LEFT OUTER JOIN DataWarehouse.Fact2 F2 ON DWT.SKEY = F2.Territory_SKEY AND
168 DWPM.SKEY = F2.ProductModel_SKEY AND
169 DWSM.SKEY = F2.ShipMethod_SKEY AND
170 DWSD.SKEY = F2.ShipDate_SKEY
171WHERE F2.Territory_SKEY IS NULL AND
172 F2.ProductModel_SKEY IS NULL AND
173 F2.ShipMethod_SKEY IS NULL AND
174 F2.ShipDate_SKEY IS NULL
175GROUP BY DWT.SKEY,
176 DWPM.SKEY,
177 DWSM.SKEY,
178 DWSD.SKEY;
179
180
181
182-- Fact 1 query
183
184SELECT F1.Territory_SKEY,
185 F1.Product_SKEY,
186 F1.OrderDate_SKEY,
187 DWT.TerritoryID,
188 DWP.ProductID,
189 DWSD.SaleDate OrderDate,
190 F1.TotalProductsSold,
191 F1.TotalSum
192FROM DataWarehouse.Fact1 F1
193 JOIN DataWarehouse.Territory DWT ON F1.Territory_SKEY = DWT.SKEY
194 JOIN DataWarehouse.Product DWP ON F1.Product_SKEY = DWP.SKEY
195 JOIN DataWarehouse.SaleDate DWSD ON F1.OrderDate_SKEY = DWSD.SKEY
196ORDER BY F1.TotalSum DESC;
197
198
199
200-- Fact 2 query
201
202SELECT F2.Territory_SKEY,
203 F2.ProductModel_SKEY,
204 F2.ShipMethod_SKEY,
205 F2.ShipDate_SKEY,
206 DWT.TerritoryID,
207 DWPM.ProductModelID,
208 DWSM.ShipMethodID,
209 DWSD.SaleDate ShipDate,
210 F2.TotalProductsSold,
211 F2.TotalSum
212FROM DataWarehouse.Fact2 F2
213 JOIN DataWarehouse.Territory DWT ON F2.Territory_SKEY = DWT.SKEY
214 JOIN DataWarehouse.ProductModel DWPM ON F2.ProductModel_SKEY = DWPM.SKEY
215 JOIN DataWarehouse.ShipMethod DWSM ON F2.ShipMethod_SKEY = DWSM.SKEY
216 JOIN DataWarehouse.SaleDate DWSD ON F2.ShipDate_SKEY = DWSD.SKEY
217ORDER BY F2.TotalSum DESC;