Scribd
Upload
13 views

Script

Uploaded by

anistar :3
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
13 views

Script

Uploaded by

anistar :3
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 4

--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
S.ShippedDate AS TheDate, DateName(dw,S.ShippedDate) AS DayOfWeek,
DatePart(mm,S.ShippedDate) AS [Month], DatePart(yy,S.ShippedDate) AS [Year],
DatePart(qq,S.ShippedDate) AS [Quarter], DatePart(dy,S.ShippedDate) AS DayOfYear,
DateName(month,S.ShippedDate) +'_'+ DateName(year,S.ShippedDate) AS YearMonth,
DatePart(wk,S.ShippedDate) AS WeekOfYear FROM Orders S WHERE S.ShippedDate 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.dbo.Orders.RequiredDate,
Orders.Freight * [Order Details].Quantity / ( SELECT SUM(Quantity) FROM [Order Details] od
WHERE od.OrderID = Orders.OrderID ) 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 Orders.OrderID = [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 Orders.CustomerID 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 Orders.ShippedDate =
Northwind_Mart.dbo.Dim_Tiempo.Tiempo_Fecha INNER JOIN
Northwind_Mart.dbo.Dim_Transportista ON Orders.ShipVia =
Northwind_Mart.dbo.Dim_Transportista.Transportista_Codigo INNER JOIN
Northwind_Mart.dbo.Dim_Empleado ON Orders.EmployeeID =
Northwind_Mart.dbo.Dim_Empleado.Empleado_codigo WHERE (Orders.ShippedDate IS NOT
NULL)

You might also like