4YhxT4ux

1-- •	DIM_CUSTOMER (CustomerID, FirstName, LastName, TerritoryName, CounrtyRegionCode, Group) – tabele źródłowe:
2-- ·	Sales.SalesTerritory
3-- ·	Sales.Customer
4-- ·	Person.Person
5
6-- Zadanie 1
7CREATE TABLE Liebert.DIM_CUSTOMER (
8  CustomerID        INT,
9  FirstName         NAME,
10  LastName          NAME,
11  TerritoryName     NAME,
12  CountryRegionCode NVARCHAR(3),
13  "Group"           NAME
14);
15
16-- •	DIM_PRODUCT (ProductID, Name, ListPrice, Color, Rating, SubCategoryName, Catego-ryName) – tabele źródłowe:
17-- ·	Production.Product
18-- ·	Production.ProductReview
19-- ·	Production.ProductSubcategory
20-- ·	Production.ProductCategory
21
22CREATE TABLE Liebert.DIM_PRODUCT (
23  ProductID       INT,
24  Name            NAME,
25  ListPrice       MONEY,
26  Color           NVARCHAR(15),
27  Rating          INT,
28  SubCategoryName NAME,
29  CategoryName    NAME
30);
31
32-- •	DIM_SALESPERSON (SalesPersonID, FirstName, LastName, Title, Gender, CountryRe-gionCode, Group, Age, Seniority) – tabele źródłowe:
33-- ·	HumanResources.Employee
34-- ·	Person.Person
35-- ·	Sales.SalesTerritory
36-- ·	Sales.SalesPerson
37-- Uwaga: Wiek sprzedawcy i staż pracy należy wyliczyć wykorzystując pole BirthDate i HireDate.
38
39CREATE TABLE Liebert.DIM_SALESPERSON (
40  SalesPersonID     INT,
41  FirstName         NAME,
42  LastName          NAME,
43  Title             NVARCHAR(8),
44  Gender            NCHAR(1),
45  CountryRegionCode NVARCHAR(3),
46  "Group"           NVARCHAR(50),
47  Age               INT,
48  Seniority         INT
49);
50
51-- •	FACT_SALES (ProductID, CustomerID, SalesPersonID, OrderDate, ShipDate, OrderQty, UnitPrice, UnitPriceDiscount, LineTotal) – tabele źródłowe:
52-- ·	Sales.SalesOrderDetail
53-- ·	Sales.SalesOrderHeader
54-- Uwaga: 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 po-szczególnych części daty użyć funkcji datepart.
55
56CREATE TABLE Liebert.FACT_SALES (
57  ProductID         INT,
58  CustomerID        INT,
59  SalesPersonID     INT,
60  OrderDate         DATETIME,
61  ShipDate          DATETIME,
62  OrderQty          SMALLINT,
63  UnitPrice         MONEY,
64  UnitPriceDiscount MONEY,
65  LineTotal         MONEY
66);
67
68-- Zadanie 2
69
70INSERT INTO Liebert.DIM_CUSTOMER
71  SELECT
72    CustomerID,
73    FirstName,
74    LastName,
75    SalesTerritory.Name,
76    CountryRegionCode,
77    "Group"
78  FROM Sales.SalesTerritory
79    JOIN Sales.Customer ON SalesTerritory.TerritoryID = Customer.TerritoryID
80    JOIN Person.Person ON Customer.PersonID = Person.BusinessEntityID;
81
82INSERT INTO Liebert.DIM_PRODUCT
83  SELECT
84    Product.ProductID,
85    Product."Name",
86    ListPrice,
87    Color,
88    AVG(Rating),
89    ProductSubcategory.Name,
90    ProductCategory.Name
91  FROM Production.Product
92    LEFT JOIN Production.ProductReview ON Product.ProductID = ProductReview.ProductID
93    LEFT JOIN Production.ProductSubcategory ON Product.ProductSubcategoryID = ProductSubcategory.ProductSubcategoryID
94    LEFT JOIN Production.ProductCategory ON ProductSubcategory.ProductCategoryID = ProductCategory.ProductCategoryID
95  GROUP BY Product.ProductID, Product."Name", ListPrice, Color, ProductSubcategory.Name, ProductCategory.Name;
96-- TRUNCATE TABLE Liebert.DIM_PRODUCT;
97
98TRUNCATE TABLE Liebert.DIM_SALESPERSON;
99INSERT INTO Liebert.DIM_SALESPERSON
100  SELECT
101    Person.BusinessEntityID,
102    FirstName,
103    LastName,
104    Title,
105    Gender,
106    CountryRegionCode,
107    "Group",
108    DATEDIFF(YEAR, BirthDate, GETDATE()),
109    DATEDIFF(YEAR, HireDate, GETDATE())
110  FROM Sales.SalesPerson
111    LEFT JOIN HumanResources.Employee ON Employee.BusinessEntityID = SalesPerson.BusinessEntityID
112    LEFT JOIN Sales.SalesTerritory ON SalesPerson.TerritoryID = SalesTerritory.TerritoryID
113    LEFT JOIN Person.Person ON Employee.BusinessEntityID = Person.BusinessEntityID
114
115INSERT INTO Liebert.FACT_SALES
116  SELECT
117    ProductID,
118    CustomerID,
119    SalesPersonID,
120    OrderDate,
121    ShipDate,
122    OrderQty,
123    UnitPrice,
124    UnitPriceDiscount,
125    LineTotal
126  FROM Sales.SalesOrderDetail
127    JOIN Sales.SalesOrderHeader ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID;
128-- DROP TABLE Liebert.FACT_SALES;
129
130-- Zadanie 4
131
132ALTER TABLE Liebert.DIM_SALESPERSON
133  ALTER COLUMN SalesPersonID INT NOT NULL;
134ALTER TABLE Liebert.DIM_SALESPERSON
135  ADD CONSTRAINT PK_DIM_SALESPERSON_SalesPersonID PRIMARY KEY CLUSTERED (SalesPersonID);
136
137ALTER TABLE Liebert.DIM_PRODUCT
138  ALTER COLUMN ProductID INT NOT NULL;
139ALTER TABLE Liebert.DIM_PRODUCT
140  ADD CONSTRAINT PK_DIM_PRODUCT_ProductID PRIMARY KEY CLUSTERED (ProductID);
141
142ALTER TABLE AdventureWorks2014.Liebert.DIM_CUSTOMER
143  ALTER COLUMN CustomerID INT NOT NULL;
144ALTER TABLE AdventureWorks2014.Liebert.DIM_CUSTOMER
145  ADD CONSTRAINT DIM_CUSTOMER_CustomerID_pk PRIMARY KEY (CustomerID);
146
147
148ALTER TABLE AdventureWorks2014.Liebert.FACT_SALES
149  ALTER COLUMN ProductID INT NOT NULL;
150ALTER TABLE AdventureWorks2014.Liebert.FACT_SALES
151  ALTER COLUMN CustomerID INT NOT NULL;
152ALTER TABLE AdventureWorks2014.Liebert.FACT_SALES
153  ADD FactSalesID INT NOT NULL IDENTITY;
154ALTER TABLE AdventureWorks2014.Liebert.FACT_SALES
155  ADD CONSTRAINT FACT_SALES_FactSalesID_pk PRIMARY KEY (FactSalesID);
156
157-- ALTER TABLE Orders
158--   ADD FOREIGN KEY (PersonID) REFERENCES Persons (PersonID);
159--
160-- ALTER TABLE AdventureWorks2014.Liebert.FACT_SALES
161--   ADD FOREIGN KEY (ProductID) REFERENCES DIM_PRODUCT (ProductID)
162
163
164-- Zadanie 6
165
166DROP TABLE IF EXISTS Liebert.FACT_SALES;
167DROP TABLE IF EXISTS Liebert.DIM_PRODUCT;
168DROP TABLE IF EXISTS Liebert.DIM_SALESPERSON;
169DROP TABLE IF EXISTS Liebert.DIM_CUSTOMER;
170
171
172-- Zadanie 7
173
174TRUNCATE TABLE [AdventureWorks2014].[dbo].[Liebert.DIM_CUSTOMER]
175TRUNCATE TABLE [AdventureWorks2014].[dbo].[Liebert.FACT_SALES]