--Fact table
go
CREATE VIEW sales AS
	SELECT * FROM sales_2016
	union all --use union all as there should not be any duplicates
	SELECT * FROM sales_2017
go

SELECT quantity_sold,sale_price
FROM sales

--Geographic Dimension
SELECT top 20 *
FROM countries

--Product Dimension
SELECT top 20 *
FROM products

--chronological (time) dimension
go
SELECT distinct date
	  ,datepart(year,date) as year
      ,datepart(month,date) as month
	  ,datepart(quarter,date) as quarter
	  ,datepart(day,date) as day
	  ,cast(null as nvarchar(2000)) as notes
INTO times
FROM sales
go

SELECT top 20 *
FROM times

--the number of rows in our sales tables
SELECT count(*) as total_number_of_sales
FROM sales
SELECT count(*) as total_number_of_dates
FROM times

--Combining everything together
SELECT top 20 
       t.year,t.month,t.quarter,t.day,t.notes --from the chronological dimension
       ,c.country,c.city --from the geographical dimension
	   ,p.product_line,p.product_type,p.product_cost --from the product dimension
	   ,s.order_method_type,s.urgent,s.retailer_type --other dimensions not in own table	   ,s.quantity_sold,s.sale_price --measures from the sales table
FROM sales as s JOIN times as t ON s.date=t.date
                JOIN countries as c ON s.retailer_city= c.city
				JOIN products as p ON s.product_code = p.code
