Bodegas de datos

Adventure Works Cycles

PRESENTA:
Juan David Parada Rodriguez
Luisa Fernanda Rodriguez Sarmiento
John Rincon Cruz

DOCENTE:
Gustavo Florez Ortiz

Bogotá D.C, Colombia 19 de Septiembre de 2021

 Proyecto Adventure Works

Definición de requerimientos:
La compañía Adventure Works Cycles nos plantea las siguientes preguntas de negocio:

1. Con respecto al total de ventas en línea, ¿cuánto es el valor total mensual de

descuentos, aplicados por categoría , subcategoría y modelo de producto y por tipo de
cliente desde el 2003?.
2. ¿Cuál es el acumulado de la diferencia entre los precios de lista y el precio venta de
los productos por ciudad, provincia y país del vendedor, teniendo en cuenta el estado
de la orden clasificado por producto comprado o fabricado por la compañía?.
3. Para las transacciones realizadas en moneda extranjera (tasa promedio) , ¿cuál es el
total de ventas en dicha moneda por grupo de territorio de venta (correspondiente al
vendedor) para cada uno de los años donde se han generado órdenes?.
4. ¿Cuál es la cantidad de órdenes en estado cancelado o rechazados con su valor total y
su porcentaje con respecto a las que han sido enviadas en la historia de la empresa,
discriminado por empleados asalariados y no asalariados?.
5. ¿Cuál es el costo de envío de cada producto y categoría mes a mes?.

De acuerdo con dichas preguntas se debe plantear matriz de requerimientos, modelo de alto
nivel, análisis de fuentes, modelo lógico y bus dimensional para responder las inquietudes
que surgen y poder diseñar la bodega de datos.

Matriz de requerimientos

Matriz #1
La siguiente matriz cuenta con los atributos, indicadores y medidas definidas de
acuerdo a los requerimientos del proyecto. Se realizó un análisis detallado con todo el
equipo para cada uno de los requerimientos, evaluando qué datos corresponden a
atributos, indicadores y medias de acuerdo a nuestros conocimientos.

Indicador/Medida/ Dimensión R1 R2 R3 R4 R5
Métrica

Medida Total venta X X

Atributo Mes X X

Atributo Año X X

Atributo Total descuento X

Atributo Categorías X X

Atributo País X X X
Atributo Ciudad X

Atributo Subcategoría X

Atributo Producto X

Atributo Tipo de cliente X

Atributo Tipo de venta X

Atributo Modelo de producto X

Medida Precio de lista de producto X

Medida Precio de venta del producto X

Atributo Estado de la orden X X

Atributo Provincia X

Atributo Vendedor X X

Atributo Comprado X

Atributo Fabricado X

Atributo Tipo de moneda X

Medida Tasa promedio X

Medida Cantidad de órdenes X

Medida Valor Orden X

Indicador Porcentaje orden enviadas X

Atributo Asalariado X

Atributo No asalariado. X

Medida Costo de envío X

Atributo Empleado X

Matriz #2 (entrevista con el cliente):

Después de una entrevista con el cliente se pudieron determinar nuevos atributos de
acuerdo a las necesidades que este planteó, por está razón se creó una nueva matriz de
requerimientos con dichas modificaciones.

Indicador/Medida/ Dimensión R1 R2 R3 R4 R5
Métrica

Medida Total venta X X

Atributo Mes X X
 Atributo Año X X

Atributo Total descuento X

Atributo Categorías X X

Atributo País X X X

Atributo Ciudad X

Atributo Subcategoría X

Atributo Producto X

Atributo Tipo de cliente X

Atributo Tipo de venta X

Atributo Modelo de producto X

Medida Precio de lista de producto X

Medida Precio de venta del producto X

Atributo Estado de la orden X X

Atributo Provincia X

Atributo Vendedor X X

Atributo Elaborado (fabricado o comprado)* X

Atributo Tipo de moneda X

Medida Tasa promedio X

Medida Cantidad de órdenes X

Medida Valor Orden X

Indicador Porcentaje orden enviadas X

Atributo Asalariado (Asalariado o no asalariado)* X

Medida Costo de envío X

Atributo Empleado X
* Datos que cambiaron con respecto a la matriz anterior.

Matriz #3 (análisis de fuente)

Esta matriz cuenta con ajustes de acuerdo al análisis de fuente realizado más adelante,
buscando qué atributos, medidas o indicadores no se tuvieron en cuenta en la creación
de la matriz #1 y tampoco con la entrevista con el cliente, con el objetivo de generar
valor agregado al proyecto.
Indicador/Medida/ Dimensión R1 R2 R3 R4 R5
Métrica

Medida Total venta X X

Atributo Mes X X

Atributo Año X X

Atributo Total descuento X

Atributo Categoría X X

Atributo País X X X

Atributo Ciudad X

Atributo Subcategoría X

Atributo Producto X

Atributo CustomerType (Tipo de cliente) X

Atributo OnlineOrderFlag (Tipo de venta) X

Atributo Modelo de producto X

Medida Precio de lista de producto X

Medida Precio de venta del producto X

Atributo Estado de la orden X X

Atributo Provincia X

Atributo Vendedor X X

Atributo MakeFlag (fabricado o comprado) X

Atributo Tipo de moneda X

Medida AverageRate (Tasa promedio) X

Medida Cantidad de órdenes X

Medida Valor Orden X

Indicador Porcentaje orden enviadas X

Atributo SalariedFlag (Asalariado o no asalariado) X

Medida Costo de envío X

Modelo de alto nivel
De acuerdo al análisis anterior, se plantea el modelo de alto nivel, el cual está compuesto por
11 tablas, entre las cuales se encuentran vendedor, ubicación, producto, orden, descuento,
empleados, tipo de empleado, moneda, tiempo, venta, y cliente. Cada una está relacionada de
tal manera que se pueda responder los diferentes requerimientos que plantea el negocio.

Análisis de Fuentes:

Inicialmente, para realizar el análisis de fuentes hay que tener en cuenta que Adventure
Works Cycles cuenta con una base de datos relacional que se encuentra en Oracle, donde se
almacena toda la información, En la actualidad cuenta con 6 schemas con 67 tablas más 3 las
cuales son usadas para guardar logs, errores y versionamiento. De igual manera se realizó el
análisis de las tablas requeridas para el desarrollo del proyecto.

A continuación el modelo ER de la fuente:

URL: [Link]

Ya que los requerimientos del cliente están enfocados al análisis de ventas, determinamos que
la sección de ventas (SALES) compuesta por 22 tablas nos daría la información necesaria
para la construcción del Data WareHouse que responderá a las preguntas del negocio.

SALES PERSON

● SalesOrderHeader ● Person
● SalesPerson ● StateProvince
● Customer ● Address
● SalesTerritory ● CountryRegion
● Currency
● CurrencyRate PRODUCTION
● SalesOrderDetail
● Product
● CreditCard
● ProductListPriceHistory
● ContactCreditCard
● ProductSubcategory
● SalesReason
● ProductCategory
● Store
● ProductInventory
● SalesTerritoryHistory
● Location
● SalesPersonQuotaHistory
● ShoppingCartItem HUMANRESOURCES
● SalesTaxRate
● SpecialOfferProduct ● Employee
● SpecialOffer
● SalesOrderHeaderSalesReason PURCHASING
● CountryRegionCurrency
● Vendor
● CustomerAddress
A continuación, se muestra el diccionario de datos donde se puede encontrar la descripción
de cada una de las tablas anteriormente descritas, con sus respectivos atributos, tipo de dato,
longitud, el tamaño en bytes y nulabilidad. Esta información fue obtenida mediante el
siguiente query y el diccionario de datos suministrado por el cliente:

SELECT COLUMN_NAME as Columna, DATA_TYPE as Tipo_Dato, DATA_PRECISION

as Longitud, data_scale as escala, data_length as Tamano, NULLABLE as Nulabilidad from
all_tab_columns WHERE TABLE_NAME = 'SALES_SALESORDERHEADER';

SALES_SALESORDERHEADER

Tipo de
Columna datos Longitud Tamaño Nulabilidad Descripción
SalesOrderID NUMBER 10 22 Not NULL Id de orden de venta
RevisionNumber NUMBER 10 22 Null Número de revisión
OrderDate DATE N/A 7 Null Fecha de orden de servicio
DueDate DATE N/A 7 Null Fecha de vencimiento de producto
ShipDate DATE N/A 7 Null Fecha de envío de producto
Status NUMBER 10 22 Null Estado de producto

OnlineOrderFlag NUMBER 10 22 Null Pedidos en línea de la orden

SalesOrderNumber CLOB N/A 4000 Null Número de orden de venta
PurchaseOrderNumber CLOB N/A 4000 Null Número de orden de compra

AccountNumber NUMBER 10 4000 Null Numero de cuenta del cliente

CustomerID NUMBER 10 22 Null Numero de identificación del cliente
ContactID NUMBER 10 22 Null ID de contacto
SalesPersonID NUMBER 10 22 Null ID de vendedor
TerritoryID NUMBER 10 22 Null ID del Territorio de venta
BillToAddressID NUMBER 10 22 Null Direccion de facturacion
ShipToAddressID NUMBER 10 22 Null Direccion de envio
ShipMethodID NUMBER 10 22 Null Metodo de envio
CreditCardID NUMBER 10 22 Null ID de tarjeta de crédito
Código de aprobación de tarjeta de
CreditCardApprovalCode CLOB N/A 4000 Null crédito
CurrencyRateID NUMBER 10 22 Null ID de tasa de cambio
SubTotal NUMBER 28 22 Null Subtotal de venta
TaxAmt NUMBER 28 22 Null Impuesto de venta
Freight NUMBER 28 22 Null Valor de carga
TotalDue NUMBER 28 22 Null Total de debito
Coment_ CLOB N/A 4000 Null Comentario de venta
rowguid VARCHAR2 4000 4000 Null Dato de guia
ModifiedDate DATE N/A 7 Null Fecha de actualización de datos
 SALES_SALESPERSON

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

SalesPersonID NUMBER 10 22 NOT NULL ID de vendedor
TerritoryID NUMBER 10 22 NULL ID del territorio
SalesQuota NUMBER 38 22 NULL Cuotas de la venta
Bonus NUMBER 38 22 NULL Datos de bono
CommissionPct NUMBER 38 22 NULL Datos de comisión
SalesYTD NUMBER 38 22 NULL Venta Anual
SalesLastYear NUMBER 38 22 NULL Venta del año anterior
rowguid VARCHAR2 4000 4000 NULL Dato de guia
ModifiedDate DATE N/A 7 NULL Fecha de actualización de datos

SALES_CUSTOMER

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

CustomerID NUMBER 10 22 NOT NULL ID de cliente
TerritoryID NUMBER 10 22 NULL ID de territorio
AccountNumber CLOB N/A 4000 NULL Número de cuenta
CustomerType VARCHAR2 1 4 NULL Tipo de cliente

rowguid VARCHAR2 4000 4000 NULL Guia

ModifiedDate DATE N/A 7 NULL Fecha de actualización de datos

SALES_CUSTOMERADDRESS

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

CustomerID NUMBER 10 22 NOT NULL ID de Cliente
AddressID NUMBER 10 22 NULL ID de Dirección
AddressTypeID NUMBER 10 22 NULL ID Tipo de dirreccion Cliente

rowguid VARCHAR2 4000 4000 NULL Guia

ModifiedDate DATE N/A 7 NULL Fecha de actualización de datos

SALES_SALESTERRITORY

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

TerritoryID NUMBER 10 22 NOT NULL ID de territorio
 Name CLOB N/A 4000 NULL Nombre de territorio
CountryRegionCode CLOB N/A 4000 NULL Codigo de region
Grupo CLOB N/A 4000 NULL Gripo de territorio
SalesYTD NUMBER 38,2 22 NULL Venta anual
SalesLastYear NUMBER 38,2 22 NULL Venta año anterior
CostYTD NUMBER 38,2 22 NULL Costo anual
CostLastYear NUMBER 38,2 22 NULL Costo año anterior
rowguid VARCHAR2 4000 4000 NULL Zona demográfica
ModifiedDate DATE N/A 7 NULL Fecha de actualización de datos

SALES_CURRENCY
Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción
CurrencyCode VARCHAR2 2 2 NOT NULL Código de moneda
Name CLOB 4000 NULL Nombre de moneda
ModifiedDate DATE 7 NULL Fecha de actualización de datos

SALES_CURRENCYRATE
Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción
CurrencyRateID NUMBER 10 22 NOT NULL ID tasa de cambio
CurrencyRateDate DATE N/A 7 NULL Fecha de tasa de cambio
FromCurrencyCode VARCHAR2 3 12 NULL Código de tasa de cambio origen
ToCurrencyCode VARCHAR2 3 12 NULL Código tasa de cambio destino
AverageRate NUMBER 38,2 22 NULL Tasa promedio
EndOfDayRate NUMBER 38,2 22 NULL Tasa de cambio fin del dia
ModifiedDate DATE N/A 7 NULL Fecha de actualización de datos

SALES_COUNTRYREGIONCURRENCY
Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción
CountryRegionCode VARCHAR2 50 50 NOT NULL Código de región de país
CurrencyCode VARCHAR2 3 3 NOT NULL Código de moneda
ModifiedDate DATE 3 12 NULL Fecha de actualización de datos
 SALES_SALESORDERDETAIL

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

SalesOrderID NUMBER 10 22 Not NULL Id de orden de venta
SalesOrderDetailID NUMBER 10 22 Not NULL Detalle orden de venta
CarrierTrackingNumber CLOB N/A 4000 Null Número de seguimiento
OrderQty NUMBER 10 22 Null Cantidad de productos
ProductID NUMBER 10 22 Null Id de producto
SpecialOfferID NUMBER 10 22 Null Id de ofertas especiales
UnitPrice NUMBER 38,2 22 Null Precio unitario
UnitPriceDiscount NUMBER 38,2 22 Null Precio unidad con descuento
LineTotal NUMBER 38,2 22 Null Línea total de productos
rowguid VARCHAR2 4000 4000 Null Guia
ModifiedDate DATE N/A 7 Null Fecha de actualización de datos

SALES_CREDITCARD

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

CreditCardID NUMBER 10 22 NOT NULL ID de tarjeta de crédito
CardType CLOB N/A 4000 NULL Tipo de tarjeta
CardNumber CLOB N/A 4000 NULL Número de tarjeta
ExpMonth NUMBER 10 22 NULL Mes de expiración TC
ExpYear NUMBER 10 22 NULL Año de expiración TC
ModifiedDate DATE N/A 7 NULL Fecha de actualización de datos

SALES_CONTACTCREDITCARD (PersonCreditCard)

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

ContactId NUMBER 10 22 NOT NULL Id de contacto

CreditCardId NUMBER 10 22 NOT NULL Id Tarjeta de crédito

ModifiedDate DATE 7 NULL Fecha de actualización de datos

SALES_SALESREASON

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

SalesReasonID NUMBER 10 22 NOT NULL ID de razón de venta

Name CLOB N/A 4000 NULL Nombre del producto

 ReasonType CLOB N/A 4000 NULL Tipo de producto
ModifiedDate DATE N/A 7 NULL Fecha actualización de datos

SALES_STORE

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

CustomerID NUMBER 10 22 No NULL Id Cliente
Name CLOB N/A 4000 No NULL Nombre Cliente
SalesPersonID NUMBER 10 22 NULL Id de vendedor
Demographics CLOB N/A 4000 NULL Zona demográfica
rowguid VARCHAR2 4000 4000 No NULL Guia
ModifiedDate DATE N/A 7 No NULL Fecha actualización de datos

SALES_SALESTERRITORYHISTORY

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

SalesPersonID NUMBER 10 22 NOT NULL ID de Vendedor
TerritoryID NUMBER 10 22 NOT NULL Id de territorio
StartDate DATE N/A 7 NULL Fecha Inicio
EndDate DATE N/A 7 NULL Fecha Fin
rowguid VARCHAR2 4000 4000 NULL Guia
ModifiedDate DATE N/A 7 NULL Fecha actualización de datos

SALES_SALESPERSONQUOTAHISTORY

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

SalesPersonID NUMBER 10 22 NOT NULL ID de vendedor
QuotaDate DATE N/A 7 NOT NULL Fecha de cuota de venta
SalesQuota NUMBER 38,2 22 NULL Monto de cuota de venta
rowguid VARCHAR2 4000 4000 NULL Guia
ModifiedDate DATE N/A 7 NULL Fecha actualización de datos

SALES_SHOPPINGCARTITEM

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

ShoppingCartItemID NUMBER 10 22 NOT NULL ID de artículo en carro de compras
 ShoppingCartID CLOB N/A 4000 NULL Id Carro de compras
Quantity NUMBER 10 22 NULL Cantidad de productos
ProductID NUMBER 10 22 NULL ID del producto
DateCreated DATE N/A 7 NULL Fecha de creación
ModifiedDate DATE N/A 7 NULL Fecha actualización de datos

SALES_SALESTAXRATE

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

SalesTaxRateID NUMBER 10 22 NOT NULL Id de tasa de impuesto a la venta
StateProvinceID NUMBER 10 22 NULL Id de provincia
TaxType NUMBER 10 22 NULL Tipo de Impuesto
TaxRate NUMBER 38,2 22 NULL Tasa de impuesto
Name CLOB N/A 4000 NULL Nombre de impuesto
rowguid VARCHAR2 4000 4000 NULL Guia
ModifiedDate DATE N/A 7 NULL Fecha actualización de datos

SALES_SPECIALOFFERPRODUCT

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

SpecialOfferID NUMBER 10 22 NOT NULL ID de oferta especial
ProductID NUMBER 10 22 NOT NULL ID de producto
rowguid VARCHAR2 4000 4000 NULL Guia
ModifiedDate DATE N/A 7 NULL Fecha actualización de datos

SALES_SPECIALOFFER

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

SpecialOfferID NUMBER 10 22 NOT NULL ID de oferta especial
Description CLOB N/A 4000 NULL Descripción de oferta
DiscountPct NUMBER 38,2 22 NULL Descuento
Type CLOB N/A 4000 NULL Tipo de oferta
Category CLOB N/A 4000 NULL Categoría de oferta

StartDate DATE N/A 7 NULL Fecha inicio de oferta

EndDate DATE N/A 7 NULL Fecha fin oferta
MinQty NUMBER 10 22 NULL Cantidad mínima de oferta
MaxQty NUMBER 10 22 NULL Cantidad máxima de oferta
rowguid VARCHAR2 4000 4000 NULL Guia
ModifiedDate DATE N/A 7 NULL Fecha actualización de datos

SALES_SALESORDERHEADERSALESREA

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

SalesOrderID NUMBER 10 22 NOT NULL ID de orden de venta
SalesReasonID NUMBER 10 22 NOT NULL ID Razon de venta
ModifiedDate DATE 7 NULL Fecha actualización de datos

PERSON_ADDRESS

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

AddressID NUMBER 10 22 NOT NULL ID direccion de persona
AddressLine1 CLOB 4000 NULL Dirección 1
AddressLine2 CLOB 4000 NULL Dirección 2
City CLOB 4000 NULL Ciudad
StateProvinceID NUMBER 10 22 NULL Estado
PostalCode CLOB 15 4000 NULL Código Postal
rowguid VARCHAR2 4000 4000 NULL Guia
ModifiedDate DATE 7 NULL Fecha de actualización de dato

PERSON_COUNTRYREGION

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

CountryRegionCode VARCHAR2 200 200 NOT NULL Codigo de region

Name VARCHAR2 200 200 NULL Nombre de región

ModifiedDate DATE 7 NULL Fecha de actualización de dato

PRODUCTION_PRODUCT

Tipo de
Columna datos Longitud Tamaño Nulabilidad Descripción
ProductID NUMBER 10 22 No NULL ID producto

Name CLOB 4000 NULL Nombre del producto.

Número único de identificación del
ProductNumber CLOB 4000 NULL producto.

MakeFlag NUMBER 10 22 NULL Comprado o Fabricado

FinishedGoodsFlag NUMBER 10 22 NULL El producto es vendible o no
Color CLOB 4000 NULL Color de producto
SafetyStockLevel NUMBER 10 22 NULL Cantidad mínima de inventario
ReorderPoint NUMBER 10 22 NULL Nivel de inventario
StandardCost NUMBER 38,2 22 NULL Costo estandar
ListPrice NUMBER 38,2 22 NULL Precio venta
Size CLOB NULL Tamaño
SizeUnitMeasureCod
e VARCHAR2 3 12 NULL Unidad de medida Size
WeightUnitMeasure
Code VARCHAR2 3 12 NULL Unidad de medida Weight
Weight NUMBER 38,2 22 NULL Peso de producto
DaysToManufacture NUMBER 10 22 NULL Numero de dias para fabricar producto
ProductLine VARCHAR2 2 8 NULL Línea de producto
Class VARCHAR2 2 8 NULL Clase de producto
Style VARCHAR2 2 8 NULL Estilo de producto
ProductSubcategoryI
D NUMBER 10 22 NULL ID de Subcategoría de producto
ProductModelID NUMBER 10 22 NULL ID de modelo de producto
SellStartDate DATE 7 NULL Fecha de disponibilidad
SellEndDate DATE 7 NULL Fecha de indisponibilidad
DiscontinuedDate DATE 7 NULL Fecha donde deja de fabricarse
rowguid VARCHAR2 4000 4000 NULL Guia
ModifiedDate DATE 7 NULL Fecha de actualización de dato

PRODUCTION_PRODUCTLISTPRICEHIS

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

ProductID NUMBER 10 22 NOT NULL Id de producto
StartDate DATE 7 NOT NULL Fecha de inicio de precio
EndDate DATE 7 NULL Fecha de fin de precio
ListPrice NUMBER 38,2 22 NULL Precio listado del producto
ModifiedDate DATE 7 NULL Fecha de actualización de dato

PRODUCTION_PRODUCTSUBCATEGORY

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

ProductSubcategoryID NUMBER 10 22 NOT NULL Id de subcategoría
ProductCategoryID NUMBER 10 22 NULL Id de categoría
 Name CLOB 4000 NULL Descripción de subcategoría
rowguid VARCHAR2 4000 NULL Guia
ModifiedDate DATE 7 NULL Fecha de actualización de dato

PRODUCTION_PRODUCTCATEGORY

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

ProductCategoryID NUMBER 10 22 NOT NULL Id de categoría

Name CLOB 4000 NULL Descripción de categoría

rowguid VARCHAR2 4000 4000 NULL Guia
ModifiedDate DATE 7 NULL Fecha de actualización de dato

PRODUCTION_PRODUCTINVENTORY

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

ProductID NUMBER 10 22 NOT NULL Id de producto
LocationID NUMBER 10 22 NOT NULL Id de ubicación de inventario
Shelf CLOB 4000 NULL Compartimiento
Bin NUMBER 10 22 NULL Contenedor en estantería
Quantity NUMBER 10 22 NULL Cantidad de productos

rowguid VARCHAR2 4000 NULL Guia

ModifiedDate DATE 7 NULL Fecha de actualización de dato

PRODUCTION_LOCATION

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

LocationID NUMBER 10 22 NOT NULL Id de ubicación

Name CLOB 4000 NULL Descripcion de ubicacion

CostRate NUMBER 38,2 22 NULL Costo por hora de fabricación
Availability NUMBER 38,2 22 NULL Capacidad de trabajo
ModifiedDate DATE 7 NULL Fecha de actualización de dato

HUMANRESOURCES_EMPLOYEE

Tipo de
Columna datos Longitud Tamaño Nulabilidad Descripción
EmployeeID NUMBER 10 22 NOT NULL Id de empleado
NationalIDNumber CLOB 4000 NULL Id de identificación nacional
ContactID NUMBER 10 22 NULL Id de contacto
LoginID CLOB 4000 NULL Id de login
ManagerID NUMBER 22 NULL Id de superior
Title CLOB 4000 NULL Nombre de cargo
BirthDate DATE 7 NULL Fecha de nacimiento
MaritalStatus VARCHAR2 4 4 NULL Estado civil
Gender VARCHAR2 4 4 NULL Género
HireDate DATE 7 NULL Fecha de contrato
Tipo de trabajo (por horas o
SalariedFlag NUMBER 10 22 NULL asalariado)
VacationHours NUMBER 10 22 NULL Vacaciones disponibles
SickLeaveHours NUMBER 10 22 NULL Número de horas de enfermedad

CurrentFlag NUMBER 10 22 NULL Estado Activo o inactivo

rowguid VARCHAR2 4000 4000 NULL Guia
ModifiedDate DATE 7 NULL Fecha de actualización

PURCHASING_VENDOR

Tipo de
Columna datos Longitud Tamaño Nulabilidad Descripción
VendorID NUMBER 10 22 NOT NULL Id de proveedor

AccountNumber CLOB 4000 NULL Número de cuenta del proveedor

Name CLOB 4000 NULL Nombre de proveedor

CreditRating NUMBER 10 22 NULL Calificación crediticia

PreferredVendorStatus NUMBER 10 22 NULL Estatus de preferencia

ActiveFlag NUMBER 10 22 NULL Estado activo o inactivo

PurchasingWebServic
eURL CLOB 4000 NULL URL del proveedor
ModifiedDate DATE 7 NULL Fecha de actualización

PERSON_CONTACT (Person)

Columna Tipo de datos Longitud Tamaño Nulabilidad Descripción

ContactId NUMBER 10 22 NOT NULL Id de Contacto

NameStyle NUMBER 10 22 NOT NULL Nombre de la familia

 Title CLOB 4000 NULL Trato de cortesía

FirstName CLOB 4000 NULL Nombre de la persona

Segundo nombre o iniciales de la
MiddleName CLOB 4000 NULL persona
LastName CLOB 4000 NULL Apellidos de la persona
Suffix CLOB 4000 NULL Sufijo del apellido
EmailAddress CLOB 4000 NULL Dirección de correo
EmailPromotion NUMBER 10 22 NULL validación de promociones
Phone CLOB 4000 NULL Numero de telefono
PasswordHash CLOB 4000 NULL Contraseña del correo
Valor aleatorio concatenado a la
PasswordSalt CLOB 4000 NULL contraseña
AdditionalConta Información de contacto
ctInfo CLOB 4000 NULL adicional
RowGuid VARCHAR2 4000 4000 NULL Número de ROWGUIDCOL
ModifiedDate DATE 7 NULL Fecha de actualización

Para la validación de la fuente se desarrollaron los siguientes queries que posiblemente

lleguen a responder las preguntas ya mencionadas anteriormente.

Queries:

1. Con respecto al total de ventas en línea, ¿cuánto es el valor total mensual de

descuentos, aplicados por categoría , subcategoría y modelo de producto y por tipo de
cliente desde el 2003?.

Select [Link] id, DBMS_LOB.SUBSTR([Link]) producto,

sum([Link]) descuento, extract(month from [Link]) mes, extract(year
from [Link]) año,
[Link] "% DESCUENTO", DBMS_LOB.SUBSTR([Link]) descripcion,
DBMS_LOB.SUBSTR([Link]) subcategoria, DBMS_LOB.SUBSTR([Link])
categoria, DBMS_LOB.SUBSTR([Link]) modelo
from sales.SALES_SALESORDERDETAIL sod
inner join sales.SALES_SPECIALOFFERPRODUCT sop ON
[Link] = [Link]
inner join sales.SALES_SPECIALOFFER so ON
[Link] = [Link]
inner join sales.SALES_SALESORDERHEADER soh ON
[Link] = [Link]
inner join production.PRODUCTION_PRODUCT pp ON
[Link] = [Link]
inner join production.PRODUCTION_PRODUCTSUBCATEGORY ppsc ON
[Link] = [Link]
inner join production.PRODUCTION_PRODUCTCATEGORY ppc ON
[Link] = [Link]
 inner join production.PRODUCTION_PRODUCTMODEL ppm ON
[Link] = [Link]
where extract(year from [Link]) >= 2003 AND [Link] != 0 AND
[Link] = 0
GROUP BY [Link], DBMS_LOB.SUBSTR([Link]), extract(month from
[Link]), extract(year from [Link]),
[Link], DBMS_LOB.SUBSTR([Link]), DBMS_LOB.SUBSTR([Link]),
DBMS_LOB.SUBSTR([Link]), DBMS_LOB.SUBSTR([Link]);

2. Total ventas por año:

select extract(year from quotadate) as "Año", SUM(salesquota) as "Total Venta"

from SALES.SALES_SALESPERSONQUOTAHISTORY
GROUP BY extract(year from quotadate) ORDER BY extract(year from quotadate);

Año Total Venta

2001 9513000

2002 29009000

2003 38782000

2004 18410000

3. Total ventas por mes y año:

select extract(month from quotadate) as "Mes", extract(year from quotadate) as "Año",

SUM(salesquota) as "Total Venta"
from SALES.SALES_SALESPERSONQUOTAHISTORY
GROUP BY extract(MONTH from quotadate), extract(year from quotadate) ORDER BY
extract(year from quotadate)

Mes Año Total Venta

7 2001 3886000

10 2001 5627000
 1 2002 4750000

4 2002 5068000

7 2002 10537000

10 2002 8654000

1 2003 5913000

4 2003 8039000

7 2003 13733000

10 2003 11097000

1 2004 8051000

4 2004 10359000

4. Top 10 de la suma de las ventas por vendedor por año:

select * from ( select
DBMS_LOB.SUBSTR([Link]) as "Nombre", DBMS_LOB.SUBSTR([Link]) as
"Apellido",sum([Link]) as "Ventas", extract(year from [Link]) as "Año"
from SALES.SALES_SALESPERSONQUOTAHISTORY spq
INNER JOIN PERSON.person_contact pp
ON [Link] = [Link]
group BY DBMS_LOB.SUBSTR([Link]),DBMS_LOB.SUBSTR([Link]),
[Link], extract(year from [Link])
Order by [Link] desc )
where rownum <= 10;

Nombre Apellido Venta Año

Gail Erickson 1898000 2002

Linda Ecoffey 1600000 2002

Maciej Dusza 1575000 2003

Shelley Dyck 1525000 2003

Gail Erickson 1506000 2003

Maciej Dusza 1429000 2002

Gail Erickson 1419000 2003

Linda Ecoffey 1369000 2003

Shelley Dyck 1355000 2002

 Linda Ecoffey 1352000 2002

5. Para las transacciones realizadas en moneda extranjera (tasa promedio) , ¿cuál es el

total de ventas en dicha moneda por grupo de territorio de venta (correspondiente al
vendedor) para cada uno de los años donde se han generado órdenes?.
Total vendido por territorio

Select DBMS_LOB.SUBSTR([Link])"Territorio", sum([Link] * [Link]) "Total

vendido"
from sales.SALES_SALESORDERHEADER sh
inner join sales.SALES_SALESORDERDETAIL so ON
[Link] = [Link]
inner join sales.SALES_SALESTERRITORY st ON
[Link] = [Link]
group by [Link], DBMS_LOB.SUBSTR([Link])
order by sum([Link] * [Link]) desc;

Territorio Total vendido

Southwest 24316097,66

Canada 16441051,45

Northwest 16172880,5

Australia 10683861,34

Central 7935817

Southeast 7920522,22

United Kingdom 7702812,78

France 7291443,15

Northeast 6963173,94

Germany 4945844,87

6. Lista de empleados

select pc.*
from HUMANRESOURCES.humanresources_employee he
inner join person.person_contact pc
on [Link] = [Link]
Hallazgos:

● El modelo relacional entregado por el cliente y el generado por Oracle difieren ya que
algunas relaciones no coinciden, adicional el número de tablas en los schemas es
diferente, por ejemplo en el schema Sales según la documentación entregada por el
cliente son 18 tablas pero en la BD de Oracle hay 22 tablas.

● Los tipos de datos, longitud y nulabilidad difieren de los insumos entregados por el
cliente y la base de datos Oracle.

● Se identifica información de órdenes de venta desde el año 2001 hasta el año 2004,
con un mayor crecimiento en los años 2002 y 2003.

SELECT extract(year from modifieddate),count(extract(year from modifieddate))

FROM SALES.SALES_SALESPERSONQUOTAHISTORY
group by extract(year from modifieddate);

Año Órdenes

2001 30

2003 65

2004 17

2002 51

● No se cuenta con una tabla que pueda discriminar el tipo de venta como se pide en el
requerimiento 1, en este caso se debe usar el atributo OnlineOrderFlag de la tabla
SALES_SALESORDERHEADER.
 ● Se encuentra que en muchas tablas aparece el atributo Rowguid el cual no tiene
información (NULL) se hace el supuesto de que este es usado como guía o
información complementaria.

● Se encuentran 504 productos en la tabla PRODUCTION_PRODUCT, 128 modelos en

la tabla PRODUCTION_PRODUCTMODEL, divididos en 4 categorías
(PRODUCTION_PRODUCTCATEGORY) y a su vez divididos por 37 subcategorías
(PRODUCTION_PRODUCTSUBCATEGORY).

● Para el requerimiento 1; según lo encontrado en los datos de la BD en la tabla

SALESCUSTOMER y confirmando con el diccionario de datos, el tipo de cliente se
define como “I o S”, donde “I” es el cliente individual y “S” es una tienda.

● Para identificar un vendedor o empleado de la tienda desde el schema SALES es

necesario cruzar el atributo salespersonID con contactID en la tabla
PERSON_CONTACT del schema PERSON.

● Para identificar el tipo de empleado, si es asalariado o no asalariado (por horas) en el

requerimiento 4 es necesario validar el estado en el atributo SalariedFlag de la tabla
HUMANRESOURCES_EMPLOYEE.

Diagrama arquitectura:

Para la finalización exitosa del proyecto se hace necesario cumplir ciertas condiciones o
requerimientos de hardware y de software, por lo que se plantean dos modelos uno de real y
otro propuesto. La idea de este diagrama de arquitectura es definir el rendimiento, el ciclo de
vida del proyecto, los recursos (Memoria Ram, Espacio en Disco, IP, etc) de la fuente, staging
y target, esto enfocado en los casos ya mencionados.

Caso Real.

Para el caso real el proyecto de Adventureworks Cycle en los ambientes de desarrollo

y producción se cuenta con componentes limitados, esto se debe a que se usó un
computador Lenovo con sistema operativo Windows 10, con una memoria RAM de
16 GB y un procesador Intel Core i7. Cabe aclarar que sobre este dispositivo se
instaló una máquina virtual con el programa VirtualBox, al cual se le asignó una
memoria RAM de 10 GB e incluye el sistema operativo Oracle Enterprise Linux 2.6,
allí se encuentra instalado el sistema de administración de bases de datos Oracle
Enterprise Edition 11G. Por consiguiente las características físicas de este caso son:

Fuentes Staging Target

Memoria Ram 2 GB 5 GB 3 GB
 Espacio de disco 10 GB 10 GB 30 GB

Procesador Intel Core i7 Intel Core i7 Intel Core i7

IP [Link] [Link] [Link]

Detalles a Se debe tener en cuenta que el ambiente de producción y

considerar desarrollo aunque se encuentran separados estos están
contenidos en el mismo sistema de administración de base de
datos.
Por otro lado, debido a que contamos con un total de 10 GB
de memoria RAM y 50 GB de almacenamiento, se debe tener
en cuenta que los recursos serán compartidos entre las
diferentes herramientas usadas.

Caso propuesto:

Para el caso propuesto a diferencia del real se desea mostrar una arquitectura ideal
para la construcción de una bodega de datos, por lo que se propone la implementación
de varios equipos proporcionados por alta tecnología para el correcto funcionamiento
de la bodega de datos en su totalidad, a continuación se detallan las características
físicas en cada ambiente:

Ambiente de desarrollo:
 Fuentes Staging Target

Memoria Ram 8 GB o superior 32 GB o superior 16 GB o Superior

Espacio de disco 3 TB 5TB 10TB

Procesador Intel Xeon de 2.7 Intel Xeon de 3.8 Intel Xeon de 3.6
GHz GHz GHz

Arquitectura 64 bits 64 bits 64 bits

Ambiente de producción

Fuentes Staging Target

Memoria Ram 16 GB 64 GB o superior 32 GB o Superior

Espacio de disco 3 TB 10 TB 20TB

Procesador Intel Xeon de 2.7 Intel Xeon de 3.8 Intel Xeon de 3.6
GHz GHz GHz

Arquitectura 64 bits 64 bits 64 bits

Software:

Para esta parte del software requerido para el proyecto de Adventure Works Cycle
decidimos tener en cuenta la información y análisis suministrada por el cuadrante de
Gartner para decidir por la mejor opción o la que más se ajuste a las necesidades del
negocio.

Base de datos:

Después de evaluar varias opciones de herramientas, por decisión del equipo

se eligió como gestor de base de datos Oracle 19C o versiones superiores,
siendo esta uno de los líderes de la industria, con bastante experiencia y
cobertura en el mercado. Además que este gestor puede ejecutarse en todas las
plataformas, soporta funciones complejas, permite el uso de particiones para
mejoras de eficiencia, replicación e incluso algunas versiones aceptan
administración de bases de datos distribuidas.

Backend:

Partiendo de la herramienta anterior se optó usar una subyacente de la empresa

Oracle para la transformación e integración de datos, esta es ODI Oracle Data
Integrator 12c, esta herramienta permite integrar datos con las arquitecturas,
debido a su alto desempeño para procesar eventos en tiempo real, con ODI se
 eliminan el motor ETL intermediario ya que realiza el trabajo de
transformación directamente de las fuentes de datos de origen.

Front End:

En el mercado actual existe una gran variedad de herramientas para la

inteligencia de negocios cada una con sus ventajas y características a destacar,
pero considerando aspectos de facilidad, dinamismo y rapidez se optó por
seleccionar la herramienta BI Tableau, que cuenta con la capacidad de
contestar cualquier pregunta del negocio, detectar correlaciones de datos en
corto tiempo y detectar tendencias y comportamientos erróneos en la
información.

Modelo Lógico:

El modelo lógico se construyó a partir de seguir una serie de pasos, los cuales son, primero se
debe seleccionar un tema, luego definir la granularidad (nivel de detalle que tendrá el
modelo), después se debe definir el hecho, lo siguiente es elegir las medidas y las
dimensiones.
 ● Selección de tema: Para realizar el modelo lógico decidimos seleccionar el tema de
venta debido a que este asunto es bastante requerido en la organización y se deben
responder las preguntas del negocio.
● Granularidad: La granularidad definida para este modelo lógico se aplicó a cada
venta generada diariamente.
● Medidas: Para determinar estas medidas se observó dos tipos de datos los cuales son
numéricos pero agregan a la venta en momentos diferentes, los cuales son:

Al incluir un producto en la Al registrar una venta:

venta:
● Subtotal
● Descuento ● Impuestos
● Precio unitario de venta ● Costo de envío
● Cantidad ● Total venta

● Dimensiones: las dimensiones definidas para el tema seleccionado son:

● Tiempo
● Producto
● Cliente
● Ubicación
● Empleado
● Orden de venta
● Moneda

Modelo lógico:
.

El modelo lógico fue desarrollado específicamente para responder las preguntas que plantea
el negocio de Adventure Works Cycle, en donde a partir del análisis de los requerimientos se
pudieron identificar los datos cuantitativos y cualitativos. De igual manera se determinó el
nivel de granularidad de forma diaria para las ventas generadas, y luego de esto se
clasificaron las dimensiones de tal manera que se pudo general el modelo lógico final.

Como se mencionó anteriormente, se hizo la selección de unas dimensiones las cuales se

utilizaron en el modelo lógico, las cuales contienen las siguientes características:

● Dimensión Tiempo: Almacena las fechas en que se realizan las ventas,

pudiendo consultar por día, mes y año.
● Dimensión Producto: Contiene la información de los productos con sus
categorías y subcategorías que usa Adventure Works.
● Dimensión Cliente: Almacena algunos datos de los clientes
● Dimensión Ubicación: Contiene la información del país, ciudad y provincia.
● Dimensión Moneda: Contiene algunos identificadores de monedas.

Bus Dimensional:

De acuerdo al análisis realizado en los puntos anteriores pudimos determinar que hay 7
dimensiones las cuales son de gran importancia para responder las preguntas del negocio, se
debe tener en cuenta el siguiente bus dimensional.

DIMENSIONES
HECHO Empleado Tiempo Cliente Producto Ubicación Categoría Moneda

Venta 1 1 1 1 1 1 1
Detalles