create view dim_country
select countrycode, 
case when countrycode = 'N' then 'Norway'
when countrycode = 'S' then 'Sweden'
when countrycode = 'DK' then 'Denmark'
end countryname
from customer 
group by countrycode
-- PK: countrycode

create view dim_paymentmethod as
select paymentmethodid methodid, paymentmethodname methodname from paymentmethod
-- PK: methodid

create view dim_saleschannel as
select saleschannelid channelid, saleschannelname channelname from saleschannel
-- PK: channelid

create view dim_maincategory as
select categoryid maincategoryid, categoryname maincategoryname from maincategory
--PK: maincategoryid

create view dim_subcategory as
select * from subcategory
-- PK: subcategoryid

create view dim_storeinfo as
select storeid,storename from storeinfo
-- PK: storeid

create view fact_sale as
select t.transactionid, saleitemid, t.storeid, cm.subcategoryid,cm.maincategoryid,t.saleschannelid,t.paymentmethodid,
c.countrycode, i.quantity,i.totalprice,i.finalprice, to_char(purchasedate, 'dd-MM-YYYY') saledate, 
currencycode
from saleitem i join saletransaction t 
on t.transactionid = i.transactionid join categorymap cm
on i.categoryid = cm.categorymapid join customer c
on t.customerid = c.customerid
--PK: SaleItemid and is not relevant