• Author / Uploaded
• Erwin Segurondo Aramayo

• Categories
• SQL
• Ciencias de la Información
• Gestión de la información
• Informática
• Tecnología

Citation preview

FACULTAD DE CIENCIA Y TEGNOLOGIA CARRERA DE INGENIERIA DE SISTEMAS

Título

Autor/es

Documentación Para El Sistema De Datawarehouse “Northwind” Bajo La Metodología Hefesto Nombres y Apellidos

Código de estudiantes

Erwin Humberto Segurondo Aramayo

201503145

Fecha

30/11/2019

Carrera

Ing. De Sistemas

Asignatura

Nuevas Tecnologías

Grupo

“A”

Periodo Académico

2/2019

Subsede

Santa Cruz

Índice EMPRESA ANALIZADA ........................................................................................... 1 1.Identificación De La Empresa ............................................................................. 2 1.1Objetivos........................................................................................................... 2 1.2 Políticas ........................................................................................................... 3 1.3 Estrategias ....................................................................................................... 4 1.5 Datos del Entorno Específico ........................................................................... 5 1.6 Relación de las metas de la organización con las del DW ............................... 5 1.7 Procesos.......................................................................................................... 6 PASOS Y APLICACIÓN METODOLÓGICA ............................................................. 7 PASO I ANÁLISIS DE REQUERIMIENTOS ............................................................. 8 2.Análisis De Requemientos .................................................................................. 9 2.1. Identificar Preguntas ....................................................................................... 9 2.2. Identificar Indicadores y perspectivas de Análisis ......................................... 10 2.3. Modelo Conceptual ....................................................................................... 12 PASO II ANÁLISIS DE LOS OLTP ......................................................................... 13 3.1. Determinación de Indicadores ...................................................................... 14 3.2. Establecer Correspondencia ......................................................................... 16 3.3. Nivel de Granularidad ................................................................................... 16 3.4 Modelo Conceptual Ampliado ........................................................................ 18 PASO III MODELO LÓGICO DEL DW.................................................................... 19 4. Modelo Lógico del Data Warehouse ................................................................ 20 4.1 Tipo De Modelo Lógico Del Data Warehouse ................................................ 20 4.2 Tablas de Dimensiones ................................................................................. 21 4.3. Tablas de Hechos ......................................................................................... 24 4.4. Uniones ........................................................................................................ 25 PASO IV PROCESOS ETL ..................................................................................... 26 5 Procesos ETL ................................................................................................... 27 5.1 Creación De Cubos Multidimensionales......................................................... 36 5.1.1 Creación De Indicadores ......................................................................... 37 5.1.2 Creación De Atributos ............................................................................. 39 5.1.3 Creación De Jerarquías........................................................................... 41

3

Lista de Figuras Ilustración 1 Modelo Conceptual ................................................................... 12 Ilustración 2 Diagrama Completo .................................................................. 16 Ilustración 3 Diagrama Completo Venta .......................................................... 1 Ilustración 4 Modelo Conceptual ................................................................... 18 Ilustración 5 Diagrama Completo .................................................................. 25 Ilustración 6 Diagrama Completo .................................................................. 37

4

EMPRESA ANALIZADA

1

1. Identificación De La Empresa Rabié ha llegado a ser la empresa más grande, competitiva e innovadora de la industria a través de una historia de más de 100 años de esfuerzo y superación. En 1925, el fundador estableció un sistema de distribución a puntos claves del sur y norte del país.

Rabie S.A. tiene como su actividad principal la Distribución de Productos, con una posición en el mercado nacional de líder en su giro. De forma interna Rabie S.A. cuenta con un total de 1.200 empleados y posee sobre 6.000 productos a nivel nacional. Y externamente cuenta con 1.500 transportistas externos y 200 proveedores, además de un total aproximado de 45.000 clientes a lo largo del país.

1.1 Objetivos “Objetivo” "Ser el nexo entre la Industria y el Comercio Minorista" El objetivo más amplio de Distribuidora Rabié es ser la solución logística más conveniente del país, tanto para los fabricantes como para los comerciantes. Es la proyección e imagen ideal que comparten sus propietarios, ejecutivos y trabajadores. Visión "Ser la solución logística más conveniente"

2

El gran propósito de Distribuidora Rabié es permitir que los fabricantes coloquen sus productos en cualquier lugar de Chile, y que los comerciantes tengan un abastecimiento seguro, confiable y con posibilidades de competir sin importar el lugar geográfico en el que se encuentren

1.2 Políticas La empresa posee pocos grandes clientes con un gran poder adquisitivo, y son precisamente estos, los que adquieren el volumen de los productos que se comercializan. Debido a ello, la política que se utiliza para cubrir los objetivos antes mencionados, es la de satisfacer ampliamente las necesidades de sus clientes, brindándoles confianza y promoviendo un ambiente familiar entre los mismos. Esta acción se realiza con el fin de mantener los clientes actuales y para que los nuevos se interesen en su forma de operar. Existe otra política que es implícita, por lo cual, no está definida tan estrictamente como la anterior, y es la de mejorar continuamente, con el objetivo de sosegar las exigencias y cambios en el mercado en el que actúa y para conseguir una mejor posición respecto a sus competidores.

3

1.3 Estrategias Dentro de las estrategias existentes, se han destacado dos por considerarse más significativas, ellas son: Expandir el ámbito geográfico, creando varias sucursales en puntos estratégicos del país. Añadir nuevos rubros a su actividad de comercialización.

1.4 Organigrama

4

1.5 Datos del Entorno Específico

Los clientes con que cuenta son bastantes variados y cubren un amplio margen. Los mismos son tanto provinciales, como nacionales, con diferentes tipos de poder adquisitivo. Con respecto a sus proveedores, la empresa posee en algunos rubros diversas opciones de las cuales puede elegir y comparar, pero en otros solo cuenta con pocas alternativas. Además, tiene como rivales a nivel de mayoreo, varios competidores importantes y ya consolidados en el mercado, pero, a nivel minorista aventaja por su tamaño y volumen de actividades a sus principales competidores. 1.6 Relación de las metas de la organización con las del DW

El DWH coincide con la meta de la empresa, ya que ésta necesita mejorar su eficiencia en la toma de decisiones y contar con información detallada a tal fin. Esto es vital, ya que es muy importante para procurar una mayor ventaja competitiva conocer cuáles son los factores que inciden directamente sobre su rentabilidad, como así también, analizar su relación con otros factores y sus respectivos por qué. El DWH aportará un gran valor a la empresa; entre las principales ventajas e inconvenientes que solucionará se pueden mencionar los siguientes: •

Permitirá a los usuarios tener una visión general del negocio.

•

Transformará datos operativos en información analítica, enfocada a la toma de decisiones.

•

Se podrán generar reportes dinámicos, ya que actualmente son estáticos y no ofrecen ninguna facilidad de análisis.

5

•

Soportará la estrategia de la empresa.

•

Aportará a la mejora continua de la estructura de la empresa.

1.7 Procesos En la mayoría de los casos, en el canal tradicional se identificó los siguientes pasos en la generación de un pedido. Hay ciertos vendedores que tienen la tarea de vender los productos a los bodegueros, restaurantes, etc. Es en este caso cuando el vendedor ya identificó la necesidad de los compradores, llena su base de datos que luego será enviada a la distribuidora. Luego, que la distribuidora ya tiene la demanda de cada vendedor, la agrupa por la hoja de ruta, para que así sea eficiente la entrega de sus productos organizándolos por zonas Estos productos deberán ser encargados con el distribuidor con el fin de que al día siguiente se entreguen los pedidos según la cantidad establecida por el vendedor a cada local que lo haya solicitado.

VENTA DIRECTA VENTA INDIRECTA

operador logístico - autoservicios, cuentas claves, instituciones y cruceristas. centros de distribución - canal tradicional y canal mayoristas.

6

PASOS Y APLICACIÓN METODOLÓGICA

7

PASO I ANÁLISIS DE REQUERIMIENTOS

8

2. Análisis De Requemientos En este paso se recopilan las necesidades de información, lo cual se llevó a cabo a través de entrevistas, cuestionarios y observaciones.

2.1. Identificar Preguntas Para ello, se entrevistó a los principales usuarios en busca de sus necesidades de información, identificando de esta forma la actividad principal de Rabie y del área de Soporte de Información, siendo éste “Ventas”, ya que diariamente se realizan aproximadamente un 85% de consultas con respecto a esta área. Luego se procedió a identificar las necesidades más relevantes con respecto a este proceso y además de las variables o perspectivas que deben tenerse en cuenta para poder tomar decisiones basadas en él. A posterior se identificaron cuáles eran los indicadores que representan de mejor modo el proceso de Ventas y qué es exactamente lo que se desea analizar del mismo. A modo de respuesta se obtuvo que era relevante conocer las ganancias, los montos de ventas Para simplificar esta tarea se les presentó una serie de ejemplos concretos de otros casos similares. El resultado por área obtenido fue el siguiente:

Área de Venta: 1. Se desea conocer cuál fue el monto de las ventas de categorías de productos por zona en un tiempo determinado. 2. Se desea conocer cuál fue el monto de las ventas de las zonas por vendedor en un tiempo determinado. 3. Se desea conocer cuál fue el monto de las ventas, el monto total de costos y la ganancia por sector de la compra en un tiempo determinado.

9

Área Logística:

4. Se desea conocer cuál fue el monto de las ventas, el monto de los costos y la ganancia de productos por vendedor en un tiempo determinado. 5. Se desea conocer cuál fue el monto de las ventas, el monto de los costos y la ganancia de categorías de productos por zona en un tiempo determinado.

Área Comercial:

6. Se desea conocer la cantidad de unidades pedidas por categorías de productos en un tiempo determinado

2.2. Identificar Indicadores y perspectivas de Análisis

Área de Venta:

10

Área de Comercial:

En síntesis, los indicadores son: •

Unidades Vendidas

•

Monto total de ventas

•

Monto de Costos

Y las perspectivas de análisis son: •

Cliente

•

Producto

•

Distribuidor

•

Tiempo

11

•

Empleado

•

Proveedor

2.3. Modelo Conceptual

Se presentará un diagrama conceptual basado en los indicadores y dimensiones que se identificaron en el paso anterior. A través de este diagrama se podrá observar los alcances del proyecto.

Cliente

Unidades Vendidas

Producto

Distribuidor

Monto Total de Ventas

Venta Tiempo Monto de Costos

Proveedor Empleado

Ilustración 1 Modelo Conceptual

12

PASO II ANÁLISIS DE LOS OLTP

13

3. ANÁLISIS DE LOS OLTP Se identificarán las fuentes que alimentarán el Data Warehouse, para ello se tomará como referencia los procesos OLTP (On Line Transaction Processing), los cuales representan toda aquella información transaccional que genera la empresa en su accionar diario, además, de las fuentes externas con las que puede llegar a disponer. Entre los OLTP más habituales que pueden existir en cualquier organización se encuentran: •

Archivos de textos.

•

Hipertextos.

•

Hojas de cálculos.

•

Informes semanales, mensuales, anuales, etc.

•

Bases de datos transaccionales.

3.1. Determinación de Indicadores En este paso se definirán los cálculos a realizar para obtener los indicadores, como se especifica a continuación: •

“Monto de ventas”: o Hechos: Venta o Función de sumarización: SUM.

Aclaración: el indicador “Monto total de ventas” representa la sumatoria de las ventas de un producto en particular.

14

•

“Monto de costos”: o Hechos: Costo o Función de sumarización: SUM.

Aclaración: el indicador “Monto total de costos” representa la sumatoria de los costos de la adquisición de un producto en particular.

•

“Unidades vendidas”. o Hechos: Unidades o Función de sumarización: SUM.

Aclaración: el indicador “Unidades vendidas” representa la sumatoria de las unidades de un producto en particular en una venta.

15

3.2. Establecer Correspondencia Se procede a la examinación los OLTP disponibles que contengan la información requerida, como así también sus características, para poder identificar las relaciones entre el diagrama conceptual y las fuentes de datos. La idea es que todos los elementos del diagrama conceptual estén correspondidos en los OLTP.

Ilustración 2 Diagrama Completo

16

Ilustración 3 Diagrama Completo Venta

1

Las relaciones identificadas fueron las siguientes: •

La tabla “Producto” se relaciona con la perspectiva “Producto”.

•

La tabla “Distribuidor” se relaciona con la perspectiva “Distribuidor”.

•

La tabla “Proveedor” se relación con la perspectiva “Proveedor”

•

La tabla” Cliente” con la perspectiva “Cliente”.

•

El campo” fecha” de la tabla” Pedido” con la perspectiva “Tiempo” (debido a que es la fecha principal en el proceso de venta).

•

El campo “cantidad” de la tabla “Detalle_pedido” con el indicador “Cantidad Unidades Vendidas”.

•

El campo “cantidad” de la tabla “Detalle_pedido” multiplicado por el campo “Precio_unitario” de la misma tabla, con el indicador” Monto Total de Ventas”.

•

El campo “Precio_unitario” de la tabla “Detalle_pedido” con el indicador “Monto de Costo”.

3.3. Nivel de Granularidad Con respecto a la perspectiva “Clientes”, los datos disponibles son los siguientes: • id_Cliente: es la clave primaria de la tabla “Clientes”, y representa unívocamente a un cliente en particular. • Región: representa la localidad del cliente. • Nombre: nombre o razón social del cliente. • Dirección: dirección de vivienda del cliente.

1

En la perspectiva “Producto”, los datos que se pueden utilizar son los siguientes: •

id_prod: es la clave primaria de la tabla “Productos”, y representa unívocamente a un producto en particular.

•

Unidades en stock: stock actual del producto.

•

Precio_unitario: precio del producto

•

Unidades_en_pedido: productos que están en pedido.

En la perspectiva “Producto”, los datos que se pueden utilizar son los siguientes: Corresponde a la fecha en que se realiza una venta. Es la que determinará la granularidad del depósito de datos, los datos más típicos que pueden emplearse son los siguientes: •

fecha fecha completa con el formato original Ejemplo: 04/07/1996

•

Año: número del año

•

trimestre: número de trimestre

•

mes: número de un mes en un año

•

dia: número del día

17

3.4 Modelo Conceptual Ampliado

Cliente: Nombre

Unidades Vendidas: SUM(Unidade s Vendidas)

Producto: Nombre

Distribuidor: Nombre

Tiempo: Año,Mes, Dia,Trime stre

Proveedor: Nombre

Monto Total de Ventas:SUM(Unid ades Vendidas * Precio de Venta)

Venta Monto de Costos: SUM(Costo)

Empleado: Nombre

Ilustración 4 Modelo Conceptual

18

PASO III MODELO LÓGICO DEL DW

19

4. Modelo Lógico del Data Warehouse Para generar el modelo lógico, se debe tener presente que los atributos de las respectivas dimensiones se deben organizar en jerarquías definidas por el usuario, que proporcionan rutas de exploración para ayudar a los usuarios a examinar los datos de un cubo. Luego de obtener como base el diagrama conceptual, se procede a crear el modelo lógico de la estructura del Data Warehouse, definiendo el tipo de modelo que se utilizará, de esta forma se podrán aplicar las acciones propias al modelo, para diseñar las tablas de dimensiones y de hechos. Finalmente, se realizarán las uniones pertinentes entre estas tablas.

4.1 Tipo De Modelo Lógico Del Data Warehouse Mediante un análisis aplicado a la estructura de datos que actualmente posee la empresa, se propone el modelo estrella (star), que consiste de una gran tabla central que contiene información sobre los hechos, y tablas más pequeñas (relacionadas a la tabla de hechos) con información sobre las dimensiones. Esto se debe principalmente a que los datos almacenados en el área de Soporte de Información no se encuentran normalizados, por lo que lo más adecuado es utilizar el esquema estrella. Las ventajas que trae aparejada la desnormalización, son las de obviar uniones (Join) entre las tablas cuando se realizan consultas, procurando así un mejor tiempo de respuesta y una mayor sencillez con respecto a su utilización. El esquema en estrella es el más simple de interpretar y es soportado por casi todas las herramientas de consulta y análisis, y los metadatos son fáciles de documentar y mantener.

20

4.2 Tablas de Dimensiones Perspectiva “Cliente” •

La nueva tabla de dimensión tendrá el nombre “Cliente”.

•

Se le agregará una clave principal con el nombre “id”.

•

Se modificara el nombre del campo”nombre_compañia” por “cliente”

Cliente Nombre_compañia

Perspectiva “Producto” •

La nueva tabla de dimensión tendrá el nombre “Producto”.

•

Se le agregará una clave principal con el nombre “id”.

•

Se modificara el nombre del campo”categoriaID” por “categoria”

Producto categoriaID

21

Perspectiva “Distribuidor” •

La nueva tabla de dimensión tendrá el nombre “Distribuidor”.

•

Se le agregará una clave principal con el nombre “id”.

•

Se

modificara

el

nombre

del

campo”nombre_compañia”

“nombre_dist”

Distribuidor Nombre_compañia

Perspectiva “Tiempo” •

La nueva tabla de dimensión tendrá el nombre “Tiempo”.

•

Se le agregará una clave principal con el nombre “id”.

•

Se generará a partir del campo “fecha” el campo “año”.

•

Se generará a partir del campo “fecha” el campo “trimestre”.

•

Se generará a partir del campo “fecha” el campo “mes”.

•

Se generará a partir del campo “fecha” el campo “dia”.

Tiempo fecha

22

por

Perspectiva “Proveedor” •

La nueva tabla de dimensión tendrá el nombre “Proveedor”.

•

Se le agregará una clave principal con el nombre “id”.

•

El nombre del campo”nombre_compañia” no será cambiado.

Proveedor Nombre_compañia

Perspectiva “Empleado” •

La nueva tabla de dimensión tendrá el nombre “Empleado”.

•

Se le agregará una clave principal con el nombre “id”.

•

Se modificará el nombre del campo” Lastname”,” FirtsName” por “Apellido”

•

Se modificará el nombre del campo” Reports To” por “Informe”

Empleado Lastname FirtsName Reports To

23

4.3. Tablas de Hechos

•

La tabla de hechos tendrá el nombre “Pedido”.

•

Su clave principal será la combinación de las claves primarias de las tablas de dimensiones antes definidas: “productoID”, “clienteID”, “empleadoID”, “tiempoID”, “proveedorID”,” distribuidorID”, en conjunto con los atributos “precio” y “cantidad” de la tabla.

•

Se crearan 3 hecchos, que corresponden con los 3 indicadores y serán nombrados “Unidades Vendidas” por “cantidad”, “Monto Total de Ventas” por “precio” y “Monto de Costo” por “precio”

Unidades Vendidas: SUM(Unidades Vendidas)

Monto Total de Ventas:SUM(Uni dades Vendidas * Precio de Venta)

Venta Monto de Costos: SUM(Costo)

24

4.4. Uniones Se realizarán las uniones pertinentes, de acuerdo corresponda:

Ilustración 5 Diagrama Completo

25

PASO IV PROCESOS ETL

26

5 Procesos ETL

Una vez definido el modelo lógico, comienza la fase de prueba con datos, a través de los procesos de ETL, esto quiere decir que se extraerán datos de diferentes fuentes, para luego integrarlos, filtrarlos y depurarlos, para ello se utilizarán sólo sentencias SQL que contendrán los datos que serán de interés. La información que estará almacenada en el depósito de datos tendrá condiciones adicionales y restricciones, siendo éstas analizadas con anticipación para evitar pérdidas de datos importantes. A modo de resumen, el proceso consta de las siguientes partes; Primero se cargarán los datos de las dimensiones y luego los de la tabla de hechos, siempre consciente de la correcta correspondencia entre cada elemento. Para finalmente, luego de la carga de los datos, establece las políticas de actualización o refresco de los datos. A continuación, se generarán las sentencias SQL para cargar las diferentes tablas de dimensiones y la tabla de hechos.

Tabla Dimensión “Cliente” Se tomará como fuente de entrada la tabla “Cliente”, del OLTP mencionado anteriormente. Se consultó a Soporte de Información y se averiguó que se deseaba tener todos los clientes que estuvieran relacionados a una venta y que no contengan ningún campo con valor NULL. Se presenta a continuación la consulta SQL para la creación de la tabla “Cliente”:

27

CREATE TABLE [dbo].[Cliente]( [id] [int] IDENTITY(1,1) NOT NULL, [nombre] [varchar](50) NOT NULL, [ciudad] [varchar](50) NOT NULL, [pais] [varchar](50) NOT NULL, CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED (

Ahora para la carga y actualización de los datos provenientes de la table “Cliente” se utilizó la siguiente sentencia SQL, la cual inserta solo productos que no estén presentes en el Data Warehouse: ALTER procedure [dbo].[poblarcliente] @fecha varchar(50) as IF EXISTS (SELECT * FROM Northwind_DW.dbo.Pedido) BEGIN DELETE FROM Northwind_DW.dbo.Pedido DELETE FROM Northwind_DW.dbo.Cliente insert into Northwind_DW.dbo.Cliente(nombre,ciudad,pais) SELECT DISTINCT ContactName, City, Country from Northwind.dbo.Cliente ve where not exists(select nombre,ciudad,pais from Northwind_DW.dbo.Cliente dw where ve.ContactName = dw.nombre) exec poblarpedido @fin=@fecha END ELSE DELETE insert SELECT where

FROM Northwind_DW.dbo.Cliente into Northwind_DW.dbo.Cliente(nombre,ciudad,pais) DISTINCT ContactName, City, Country from Northwind.dbo.Cliente ve not exists(select nombre,ciudad,pais from Northwind_DW.dbo.Cliente dw

where ve.ContactName = dw.nombre)

28

Tabla Dimensión “Producto” Se tomará como fuente de entrada la tabla “Producto”, del OLTP mencionado anteriormente. Se consultó a Soporte de Información y se averiguó que se deseaba tener todos los clientes que estuvieran relacionados a una venta y que no contengan ningún campo con valor NULL. Se presenta a continuación la consulta SQL para la creación de la tabla “Producto”: CREATE TABLE [dbo].[Producto]( [id] [int] IDENTITY(1,1) NOT NULL, [nombre] [varchar](50) NOT NULL, [categoria] [varchar](50) NOT NULL, CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED

Ahora para la carga y actualización de los datos provenientes de la table “Producto” se utilizó la siguiente sentencia SQL, la cual inserta solo productos que no estén presentes en el Data Warehouse: ALTER procedure [dbo].[poblarproducto] @fecha varchar(50) as IF not EXISTS (SELECT * FROM Northwind_DW.dbo.Pedido) BEGIN DELETE FROM Northwind_DW.dbo.Pedido DELETE FROM Northwind_DW.dbo.Producto insert into Northwind_DW.dbo.Producto(nombre,categoria) SELECT DISTINCT pro.nombre AS Producto, cat.nombre AS Categoria FROM Producto pro INNER JOIN Categoria cat ON (pro.categoriaID=cat.categoriaID) where not exists(select nombre,categoria from Northwind_DW.dbo.Producto dw where pro.nombre=dw.nombre) exec poblarpedido @fin=@fecha END ELSE DELETE FROM Northwind_DW.dbo.Producto insert into Northwind_DW.dbo.Producto(nombre,categoria) SELECT DISTINCT pro.nombre AS Producto, cat.nombre AS Categoria FROM Producto pro INNER JOIN Categoria cat ON (pro.categoriaID=cat.categoriaID) where not exists(select nombre,categoria from Northwind_DW.dbo.Producto dw where pro.nombre=dw.nombre)

29

Tabla Dimensión “Distribuidor” Se tomará como fuente de entrada la tabla “Shipper”, del OLTP mencionado anteriormente. Se consultó a Soporte de Información y se averiguó que se deseaba tener todos los clientes que estuvieran relacionados a una venta y que no contengan ningún campo con valor NULL. Se presenta a continuación la consulta SQL para la creación de la tabla “Distribuidor”: CREATE TABLE [dbo].[Distribuidor]( [id] [int] IDENTITY(1,1) NOT NULL, [nombre_dist] [varchar](50) NULL, PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

Ahora para la carga y actualización de los datos provenientes de la table “Distribuidor” se utilizó la siguiente sentencia SQL, la cual inserta solo productos que no estén presentes en el Data Warehouse: ALTER procedure [dbo].[poblardistribuidor] @fecha varchar(50) as IF EXISTS (SELECT * FROM Northwind_DW.dbo.Pedido) BEGIN DELETE FROM Northwind_DW.dbo.Pedido DELETE FROM Northwind_DW.dbo.Distribuidor insert into Northwind_DW.dbo.Distribuidor(nombre_dist) SELECT DISTINCT CompanyName from Northwind.dbo.Shippers ve where not exists(select nombre_dist from Northwind_DW.dbo.Distribuidor dw where ve.CompanyName = dw.nombre_dist) exec poblarpedido @fin=@fecha END ELSE DELETE FROM Northwind_DW.dbo.Distribuidor insert into Northwind_DW.dbo.Distribuidor(nombre_dist) SELECT DISTINCT CompanyName from Northwind.dbo.Shippers ve where not exists(select nombre_dist from Northwind_DW.dbo.Distribuidor dw where ve.CompanyName = dw.nombre_dist

30

Tabla Dimensión “Empleado” Se tomará como fuente de entrada la tabla “Empleado”, del OLTP mencionado anteriormente. Se consultó a Soporte de Información y se averiguó que se deseaba tener todos los clientes que estuvieran relacionados a una venta y que no contengan ningún campo con valor NULL. Se presenta a continuación la consulta SQL para la creación de la tabla “Empleado”: CREATE TABLE [dbo].[Empleado]( [id] [int] IDENTITY(1,1) NOT NULL, [apellido] [varchar](50) NOT NULL, [informe] [int] NOT NULL, CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

Ahora para la carga y actualización de los datos provenientes de la table “Empleado” se utilizó la siguiente sentencia SQL, la cual inserta solo productos que no estén presentes en el Data Warehouse:

ALTER procedure [dbo].[poblarempleado] @fecha varchar(50) as IF EXISTS (SELECT * FROM Northwind_DW.dbo.Pedido) BEGIN DELETE FROM Northwind_DW.dbo.Pedido DELETE FROM Northwind_DW.dbo.Empleado insert into Northwind_DW.dbo.Empleado(apellido,informe) SELECT DISTINCT CONCAT(FirstName,LastName), ReportsTo FROM Northwind.dbo.Empleado em WHERE not exists(select apellido,informe from Northwind_DW.dbo.Empleado dw where CONCAT( em.FirstName,em.LastName)= dw.apellido) and ReportsTo is not null exec poblarpedido @fin=@fecha

31

END ELSE DELETE FROM Northwind_DW.dbo.Empleado insert into Northwind_DW.dbo.Empleado(apellido,informe) SELECT DISTINCT CONCAT(FirstName,LastName), ReportsTo FROM Northwind.dbo.Empleado em WHERE not exists(select apellido,informe from Northwind_DW.dbo.Empleado dw where CONCAT( em.FirstName,em.LastName)= dw.apellido) and ReportsTo is not null

Tabla De Hechos“Pedido” Se tomará como fuente las tablas “Orden” y “Detalle_orden” del OLTP mencionado anteriormente. Se consultó a Soporte de Información y se averiguó que se deseaba tener todos los clientes que estuvieran relacionados a una venta y que no contengan ningún campo con valor NULL. Se presenta a continuación la consulta SQL para la creación de la tabla “Pedido”:

CREATE TABLE [dbo].[Pedido]( [productoID] [int] NOT NULL, [clienteID] [int] NOT NULL, [empleadoID] [int] NOT NULL, [tiempoID] [int] NOT NULL, [proveedorID] [int] NOT NULL, [precio] [float] NOT NULL, [cantidad] [int] NOT NULL, [distribuidorID] [int] NULL ) ON [PRIMARY]

Ahora para la carga y actualización de los datos provenientes de la table “Pedido” se utilizó la siguiente sentencia SQL, la cual inserta solo productos que no estén presentes en el Data Warehouse:

32

ALTER procedure [dbo].[poblarpedido] @fin varchar(50) as DELETE FROM Northwind_DW.dbo.Pedido insert into Northwind_DW.dbo.Pedido(productoID,clienteID,empleadoID,tiempoID,proveedorID,pr ecio,cantidad,distribuidorID) SELECT DISTINCT prodD.id AS ProductoID, cliD.id AS ClienteID, empD.id AS EmpleadoID, tiD.id AS TiempoID, provD.id AS ProveedorID, (detoN.Quantity * detoN.UnitPrice)AS PrecioTotal,detoN.Quantity AS Cantidad, distD.id AS DistribuidorID FROM Northwind.dbo.Producto prodN INNER JOIN Northwind.dbo.Categoria catN ON (prodN.categoriaID=catN.categoriaID) INNER JOIN Northwind.dbo.Proveedor provN ON (prodN.provedorID=provN.proveedorID) INNER JOIN Northwind.dbo.DetalleOrden detoN ON (prodN.productoID=detoN.productoID) INNER JOIN Northwind.dbo.Orden ordN ON (ordN.ordenID=detoN.ordenID) INNER JOIN Northwind.dbo.Cliente cliN ON (cliN.clienteID=ordN.clienteID) INNER JOIN Northwind.dbo.Empleado empN ON (empN.empleadoID=ordN.empleadoID) INNER JOIN Northwind.dbo.Shippers shipperN ON (shipperN.ShipperID=ordN.ShipVia) INNER JOIN Northwind_DW.dbo.Producto prodD ON (prodD.nombre=prodN.nombre) INNER JOIN Northwind_DW.dbo.Cliente cliD ON (cliD.nombre=cliN.ContactName) INNER JOIN Northwind_DW.dbo.Empleado empD ON (empD.apellido=CONCAT(empN.FirstName,empN.LastName)) INNER JOIN Northwind_DW.dbo.Tiempo tiD ON (tiD.fecha= CONVERT(DATE,ordN.fecha)) INNER JOIN Northwind_DW.dbo.Proveedor provD ON (provD.nombreCompania=provN.nombreCompania) INNER JOIN Northwind_DW.dbo.Distribuidor distD On (distD.nombre_dist=shipperN.CompanyName) where not exists(select productoID,clienteID,empleadoID,tiempoID,proveedorID,precio,cantidad,distribuid orID from Northwind_DW.dbo.Pedido dw where prodN.productoID=dw.productoID) AND CONVERT(DATE, ordN.fecha) BETWEEN '1996-07-04' and @fin

33

Tabla Dimensión “Proveedor” Se tomará como fuente de entrada la tabla “Proveedor”, del OLTP mencionado anteriormente. Se consultó a Soporte de Información y se averiguó que se deseaba tener todos los clientes que estuvieran relacionados a una venta y que no contengan ningún campo con valor NULL. Se presenta a continuación la consulta SQL para la creación de la tabla “Proveedor”:

CREATE TABLE [dbo].[Proveedor]( [id] [int] IDENTITY(1,1) NOT NULL, [nombreCompania] [varchar](50) NOT NULL, [pais] [varchar](50) NOT NULL, CONSTRAINT [PK_Supplier] PRIMARY KEY CLUSTERED (

Ahora para la carga y actualización de los datos provenientes de la table “Pedido” se utilizó la siguiente sentencia SQL, la cual inserta solo productos que no estén presentes en el Data Warehouse: ALTER procedure [dbo].[poblarproveedor] @fecha varchar(50) as IF EXISTS (SELECT * FROM Northwind_DW.dbo.Pedido) BEGIN DELETE FROM Northwind_DW.dbo.Pedido DELETE FROM Northwind_DW.dbo.Proveedor insert into Northwind_DW.dbo.Proveedor(nombreCompania,pais) SELECT DISTINCT nombreCompania, pais FROM Proveedor proovN where not exists (select nombreCompania, pais from Northwind_DW.dbo.Proveedor dw where proovN.nombreCompania= dw.nombreCompania) exec poblarpedido @fin=@fecha END ELSE DELETE FROM Northwind_DW.dbo.Proveedor insert into Northwind_DW.dbo.Proveedor(nombreCompania,pais) SELECT DISTINCT nombreCompania, pais FROM Proveedor proovN where not exists (select nombreCompania, pais from Northwind_DW.dbo.Proveedor dw where proovN.nombreCompania= dw.nombreCompania)

34

Tabla Dimensión “Tiempo” Se tomará como fuente de entrada la tabla “Fecha”, del OLTP mencionado anteriormente. Se consultó a Soporte de Información y se averiguó que se deseaba tener todos los clientes que estuvieran relacionados a una venta y que no contengan ningún campo con valor NULL. Se presenta a continuación la consulta SQL para la creación de la tabla “Tiempo”: CREATE TABLE [dbo].[Tiempo]( [id] [int] IDENTITY(1,1) NOT NULL, [fecha] [date] NOT NULL, [anio] [int] NOT NULL, [trimestre] [int] NOT NULL, [mes] [varchar](20) NOT NULL, [dia] [int] NOT NULL, CONSTRAINT [PK_Time] PRIMARY KEY CLUSTERED

Ahora para la carga y actualización de los datos provenientes de la table “Tiempo” se utilizó la siguiente sentencia SQL, la cual inserta solo productos que no estén presentes en el Data Warehouse: ALTER procedure [dbo].[poblartiempo] @fecha varchar(50) as IF EXISTS (SELECT * FROM Northwind_DW.dbo.Pedido) BEGIN DELETE FROM Northwind_DW.dbo.Pedido DELETE FROM Northwind_DW.dbo.Tiempo insert into Northwind_DW.dbo.Tiempo(fecha,anio,trimestre,mes,dia) SELECT DISTINCT CONVERT (DATE,fecha) AS Fecha, CONVERT(INT, DATEPART(YEAR, fecha)) AS Anio, CONVERT(INT, DATEPART(QUARTER, fecha)) AS Trimestre, CONVERT(VARCHAR,DATEPART(MONTH, fecha)) AS Mes, CONVERT(INT, DATEPART(DAY, fecha)) AS Dia FROM Northwind.dbo.Orden ord where not exists(select fecha from Northwind_DW.dbo.Tiempo dw where ord.fecha = dw.fecha) exec poblarpedido @fin=@fecha END ELSE DELETE FROM Northwind_DW.dbo.Tiempo insert into Northwind_DW.dbo.Tiempo(fecha,anio,trimestre,mes,dia)

35

SELECT DISTINCT CONVERT (DATE,fecha) AS Fecha, CONVERT(INT, DATEPART(YEAR, fecha)) AS Anio, CONVERT(INT, DATEPART(QUARTER, fecha)) AS Trimestre, CONVERT(VARCHAR,DATEPART(MONTH, fecha)) AS Mes, CONVERT(INT, DATEPART(DAY, fecha)) AS Dia FROM Northwind.dbo.Orden ord where not exists(select fecha from Northwind_DW.dbo.Tiempo dw where ord.fecha = dw.fecha)

5.1 Creación De Cubos Multidimensionales Los cubos multidimensionales o cubos OLAP, contienen datos resumidos de grandes Bases de datos o Sistemas Transaccionales (OLTP). Se utilizan principalmente en informes de negocios de ventas, marketing, informes de dirección, minería de datos y áreas similares. Es necesario el uso de estos cubos debido a la rápida respuesta para las consultas aplicadas a grandes volúmenes de datos, como los que almacena Rabie S.A. Los cubos multidimensionales se componen de hechos numéricos llamados medidas, que se clasifican por dimensiones. El cubo de metadatos para este caso es creado a partir de un esquema tipo estrella, el cual es definido para una base de datos relacional específica. Las medidas se obtienen de los registros de una tabla de hechos y las dimensiones se derivan de la dimensión de los cuadros. Los cubos estarán basados en el modelo lógico diseñado, siguiendo los pasos de la metodología Hefesto [1], además se presentará la correcta distinción entre: •

Hechos de una tabla de hechos e indicadores de un cubo.

•

Campos de una tabla de dimensión y atributos de un cubo.

36

Ilustración 6 Diagrama Completo

5.1.1 Creación De Indicadores En este momento se crearán dos indicadores que serán incluidos en el cubo “Cubo de Pedidos”: De la tabla de hechos “Ventas”, se sumarizará el hecho “Cantidad” para crear el indicador denominado: • “Unidades Vendidas”. La fórmula utilizada para crear este indicador es la siguiente: • “Unidades Pedidas” = SUM(Ventas.Cantidad).

37

De la tabla de hechos “Ventas”, se sumarizará el hecho “Total” para crear el indicador denominado: • “Monto Total de Ventas”. La fórmula utilizada para crear este indicador es la siguiente: • “Total de ventas” = SUM(Ventas.Total). Entonces, el cubo quedaría conformado de la siguiente manera:

Cantidad Total

38

5.1.2 Creación De Atributos Ahora se crearán y agregarán al cubo seis atributos: De la tabla de dimensión “Cliente”, se tomará el campo “nombre” para la creación del atributo denominado: • “Nombre”. De la tabla de dimensión “Producto”, se tomará el campo “Nombre” para la creación del atributo denominado: • “Nombre”. De la tabla de dimensión “Distribuidor”, se tomará el campo “marca” para la creación del atributo denominado: • “NombreCompañia”. De la tabla de dimensión “Empleado”, se tomarán los campos “Nombre” concatenándolos para la creación del atributo denominado: • “Nombre”. De la tabla de dimensión “Proveedor”, se tomará el campo “sexo” para la creación del atributo denominado: • “Nombre_compañia”. De la tabla de dimensión “TIEMPO”, se tomará el campo “fecha” para la creación del atributo denominado: • “Años”. De la tabla de dimensión “TIEMPO”, se tomará el campo “fecha” para la creación del atributo denominado:

39

• “Meses”. De la tabla de dimensión “TIEMPO”, se tomará el campo “fecha” para la creación del atributo denominado: • “trimestre”. De la tabla de dimensión “TIEMPO”, se tomará el campo “fecha” para la creación del atributo denominado: • “dia”.

Producto Empleado Proveedor Categoría Dia Años Meses Cantidad Total

40

5.1.3 Creación De Jerarquías Finalmente se crearán y agregarán al cubo tres jerarquías. Se definió la jerarquía “Jerarquía Productos”, que se aplicará sobre los atributos recientemente creados, “Categorías”, y “Productos”, en donde:

Categoría Jerarquía Producto

Se definió la jerarquía “Jerarquía Fechas”, que se aplicará sobre los atributos recientemente creados, “Años”, “Semestres”, “Meses” y “Días”. Un día del mes pertenece solo a un mes del año. Un mes del año tiene uno o más días del mes. Un mes del año pertenece solo a un semestre del año. Un semestre del año tiene uno o más meses del año. Un semestre del año pertenece solo a un año. Un año tiene uno o más semestres del año.

Año

Trimestre

Mes Jerarquía Dia

41

42