teoria-BI.sql

use dw_semana2;
---------------
--Ampliar el modelo estrella, es decir, el modelo copo de nieve
---------------
create table dimCategoria(
categoryid int primary key,
categoryname nvarchar(40))
--------------------------------
create table dimProducto(
productid int primary key,
productname nvarchar(50),
categoryid int references dimCategoria(categoryid))
----------------------------------
create table factVentasProductos(
ventaid bigint primary key identity,
orderid int,
customerid nchar(10) references dimCliente(customerid),
employeeid int references dimEmpleado(employeeid),
fechaid int references dimTiempo(fechaid),
productid int references dimProducto(productid),
importe money,
cantidad int)
--------------------------------------
insert into dimCategoria select categoryid, categoryname from NORTHWIND.dbo.Categories;
------------------------------------------
insert into dimProducto select productid, productname, categoryid from NORTHWIND.dbo.Products;
-------------------------------------------
insert into factVentasProductos 
select o.orderid, o.customerid, o.employeeid, 
year(o.orderdate)*10000+month(o.orderdate)*100+day(o.orderdate), 
od.productid, od.unitprice*od.quantity, od.quantity
from NORTHWIND.dbo.[Order Details] as od
join NORTHWIND.dbo.Orders as o
on o.OrderID = od.OrderID