--TABLA DIMENSION CLIENTE
CREATE TABLE Dim_Cliente(
Cliente_key INT IDENTITY,
Cliente_Compania NVARCHAR(40),
Cliente_Contacto NVARCHAR(30),
Cliente_Codigo NCHAR(5),
Cliente_Titulo_Cont NVARCHAR(30),
Cliente_Direccion NVARCHAR(60),
Cliente_Ciudad NVARCHAR(15),
Cliente_Region NVARCHAR(15),
Cliente_CodigoPostal NVARCHAR(10),
Cliente_Pais NVARCHAR(15),
Cliente_Telefono NVARCHAR(24),
Cliente_Fax NVARCHAR(24),
PRIMARY KEY(Cliente_key)
);
--TABLA DIMENSION EMPLEADO
CREATE TABLE Dim_Empleado(
Empleado_key INT IDENTITY,
Empleado_Nombre NVARCHAR(30),
Empleado_FechaCont DATETIME NULL,
Empleado_Codigo INT NULL,
PRIMARY KEY(Empleado_key)
);
--TABLA DIMENSION PRODUCTO
CREATE TABLE Dim_Producto(
Producto_key INT IDENTITY,
Producto_Nombre NVARCHAR(40),
Producto_Sustituto NVARCHAR(40),
Producto_Categoria NVARCHAR(15),
Producto_Codigo INT NULL,
Producto_PrecioUnit MONEY NULL,
PRIMARY KEY(Producto_key)
);
--TABLA DIMENSION TIEMPO
CREATE TABLE Dim_Tiempo(
Tiempo_key INT IDENTITY,
Tiempo_MesAno NVARCHAR(30),
Tiempo_DiaSemana NVARCHAR(20),
Tiempo_Feriado NVARCHAR(1),
Tiempo_FinSemana NVARCHAR(1),
Tiempo_Trimestre INT NULL,
Tiempo_Dia_Annio INT NULL,
Tiempo_Semana_Annio INT NULL,
Tiempo_Mes INT NULL,
Tiempo_Annio INT NULL,
Tiempo_Fecha DATETIME NULL,
PRIMARY KEY(Tiempo_key)
);
--TABLA DIMENSION TRANSPORTISTA
CREATE TABLE Dim_Transportista(
Transportista_key INT IDENTITY,
Transportista_Nombre NVARCHAR(40),
Transportista_Codigo INT NULL,
PRIMARY KEY(Transportista_key)
);
--TABLA FACTURA DE VENTAS
CREATE TABLE Fact_Ventas(
Descuento_Lineaitem MONEY NULL,
Cantidad_Lineaitem SMALLINT NULL,
Flete_Lineaitem MONEY NULL,
Total_Lineaitem MONEY NULL,
FechaRequerida DATETIME NULL,
Cliente_key INT NOT NULL,
Producto_key INT NOT NULL,
Transportista_key INT NOT NULL,
Tiempo_key INT NOT NULL,
Empleado_key INT NOT NULL,
CONSTRAINT fk_Cliente FOREIGN KEY (Cliente_key) REFERENCES Dim_Cliente(Cliente_key),
CONSTRAINT fk_Empleado FOREIGN KEY(Empleado_key) REFERENCES
Dim_Empleado(Empleado_key),
CONSTRAINT fk_Producto FOREIGN KEY(Producto_key) REFERENCES
Dim_Producto(Producto_key),
CONSTRAINT fk_Tiempo FOREIGN KEY(Tiempo_key) REFERENCES Dim_Tiempo(Tiempo_key),
CONSTRAINT fk_Transportista FOREIGN KEY(Transportista_key) REFERENCES
Dim_Transportista(Transportista_key),
);
Paso 8: DELETE Fact_Ventas DELETE Dim_Empleado DBCC
CHECKIDENT('Dim_Empleado',reseed,0) DELETE Dim_Tiempo DBCC
CHECKIDENT('Dim_Tiempo',reseed,0) DELETE Dim_Cliente DBCC
CHECKIDENT('Dim_Cliente',reseed,0) DELETE Dim_Transportista DBCC
CHECKIDENT('Dim_Transportista',reseed,0) DELETE Dim_Producto DBCC
CHECKIDENT('Dim_Producto',reseed,0) Paso 14:
SELECT*,Regioncita=ISNULL(Region,'Otros')FROM customers Paso 18: SELECT DISTINCT
[Link] AS TheDate, DateName(dw,[Link]) AS DayOfWeek,
DatePart(mm,[Link]) AS [Month], DatePart(yy,[Link]) AS [Year],
DatePart(qq,[Link]) AS [Quarter], DatePart(dy,[Link]) AS DayOfYear,
DateName(month,[Link]) +'_'+ DateName(year,[Link]) AS YearMonth,
DatePart(wk,[Link]) AS WeekOfYear FROM Orders S WHERE [Link] IS NOT
NULL Paso 26: select * , FirstName +',' + LastName as EmployeeName from employees Paso 30:
SELECT Northwind_Mart.dbo.Dim_Tiempo.Tiempo_Key,
Northwind_Mart.dbo.Dim_Cliente.Cliente_Key,
Northwind_Mart.dbo.Dim_Transportista.Transportista_key,
Northwind_Mart.dbo.Dim_Producto.Producto_Key,
Northwind_Mart.dbo.Dim_Empleado.Empleado_Key, Northwind_Unt.[Link],
[Link] * [Order Details].Quantity / ( SELECT SUM(Quantity) FROM [Order Details] od
WHERE [Link] = [Link] ) AS LineItemFreight, [Order Details].UnitPrice * [Order
Details].Quantity AS LineItemTotal, [Order Details].Quantity AS LineItemQuantity, [Order
Details].Discount * [Order Details].UnitPrice * [Order Details].Quantity AS LineItemDiscount
FROM Orders INNER JOIN [Order Details] ON [Link] = [Order Details].OrderID INNER
JOIN Northwind_Mart.dbo.Dim_Producto ON [Order Details].ProductID =
Northwind_Mart.dbo.Dim_Producto.Producto_Codigo INNER JOIN
Northwind_Mart.dbo.Dim_Cliente ON [Link] COLLATE Modern_Spanish_CI_AS =
Northwind_Mart.dbo.Dim_Cliente.Cliente_Codigo COLLATE Modern_Spanish_CI_AS INNER
JOIN Northwind_Mart.dbo.Dim_Tiempo ON [Link] =
Northwind_Mart.dbo.Dim_Tiempo.Tiempo_Fecha INNER JOIN
Northwind_Mart.dbo.Dim_Transportista ON [Link] =
Northwind_Mart.dbo.Dim_Transportista.Transportista_Codigo INNER JOIN
Northwind_Mart.dbo.Dim_Empleado ON [Link] =
Northwind_Mart.dbo.Dim_Empleado.Empleado_codigo WHERE ([Link] IS NOT
NULL)
