Etl Untels

UNIVERSIDAD NACIONAL TECNOLOGICA DE LIMA SUR FACULTAD DE INGENIERÍA Y GESTIÓN CARRERA PROFESIONAL DE INGENIERIA DE SISTEMAS

INTELIGENCIA DE NEGOCIOS DESARROLLO DE DATA MART PARA MEJORAR LA TOMA DE DECISONES DE LOS PROCESOS DE VENTAS DE LA EMPRESA “DISTRIBUIDORA CASERITA”, 2019 INTEGRANTES: Hernández Sánchez, Lisseth PROFESOR: M.Sc. Leónidas Asto

LIMA – PERÚ Diciembre, 2019

1

INDICE DE CONTENIDOS 1. Planeación y administración del proyecto.............................................7 1.1

Planteamiento del problema........................................................................7

1.2

Estado del arte............................................................................................7

1.2.1

Antecedentes nacionales.............................................................................8

1.3

Procesos de negocio...................................................................................9

1.4

Alcance del Proyecto.................................................................................10

1.5

Objetivos....................................................................................................10

1.5.1

Objetivo General........................................................................................10

1.5.2

Objetivos específicos.................................................................................10

1.6

Justificación...............................................................................................11

1.7

Recursos y costos......................................................................................11

1.7.1

Personal.....................................................................................................11

1.7.2

Hardware de desarrollo..............................................................................11

1.7.3

Software de desarrollo...............................................................................12

1.8

Beneficios..................................................................................................12

1.9

Tareas a realizar........................................................................................12

1.10

Roles y equipos.........................................................................................13

1.11

Administración del proyecto.......................................................................13

.....................................................14 2. Análisis de requerimientos..................................................................15 2.1

Modelo de Negocio....................................................................................15

2.1.1

Descripción de los procesos de negocio....................................................15

2.1.2

Los problemas del Negocio.......................................................................15

2.1.3

Objetivos, Estrategias, Indicadores o KPIs................................................15

2.2

Resumen de los requerimientos obtenidos en la entrevista.......................16 2

2.2.1

Catálogo de Requisitos..............................................................................16

2.2.2

Definición de Medidas...............................................................................18

De acuerdo a los requerimientos definimos dos medidas:.........................................18 3. El modelo lógico: modelo dimensional................................................18 3.1

Selección del Data Mart.............................................................................18

3.2

Definir las dimensiones..............................................................................18

3.2.1

Mapeando las dimensiones del negocio en las tablas de dimensión.........19

3.2.2

Agregando las claves primarias a cada dimensión....................................19

3.2.3

Identificando las jerarquías analíticas........................................................20

3.2.4

Agregar las claves a cada atributo jerárquico............................................20

3.2.5

Determinar la granularidad de cada dimensión..........................................21

3.3 3.3.1

Definir la tabla de hechos..........................................................................21 Mapeando las medidas del negocio en las tablas de hechos....................22

3.4 Definiendo las claves primarias y las relaciones entre tabla de hecho y dimensiones..................................................................................................................23 3.5

Definiendo el Modelo de Estrellas o Modelo de Copo de nieves...............23 4. Diseño técnico de la arquitectura........................................................25

4.1

Descripción de la fuente de datos transaccional........................................25

4.1.1

Modelo de la Fuente de datos...................................................................25

4.1.2

Descripción breve del modelo....................................................................25

4.2

Infraestructura: servidores, equipos...........................................................25

4.3

Flujo técnico de la arquitectura: back room, front room.............................26 5. Procesos de extracción, transformación y carga.................................27

5.1

Identificar fuentes y destinos detallados....................................................27

5.2

Selección de herramienta de ETL..............................................................27

5.3

Cargar dimensiones...................................................................................28

5.4

Carga dimensiones y tabla de hechos incrementales................................29 6. Diseño físico.......................................................................................30

6.1 Trasladar el modelo dimensional al modelo físico. Aplicando nombre s y estándares de la base de datos, identificar el tipo de datos y la longitud de las columnas, etiquetar atributos como Nulos y No nulos...................................................30 6.1.1

Diseño fisico de la dimension cliente.........................................................30

6.1.2

Diseño fisico de la dimension vendedor.....................................................31

6.1.3

Diseño fisico de la dimension Articulo........................................................32

6.1.4

Diseño fisico de la dimension tiempo.........................................................32

6.2

Determinar la longitud de la BD del DWH..................................................33

6.3

Determinar la estrategia de indexación......................................................33

6.4

Sentencias SQL para crear el modelo.......................................................33 7. Aplicaciones para usuarios finales: características.............................33

7.1

Elegir la herramienta para procesamiento analítico...................................33

7.2

Lista los resúmenes de información requeridos por los usuarios...............33

7.3

Determinar los cubos.................................................................................33 3

7.4

Diseñar los reportes a mostrar...................................................................33

7.5 Selección de herramienta de aplicación para usuario final: Puede usar Ms. Excel u otra herramienta...............................................................................................33 7.6

Implementación de la herramienta Ms Excel u otra herramienta..............33

7.6.1

Crear las tablas dinámicas.........................................................................33

7.6.2

Construir los gráficos dinámicos................................................................33

7.6.3

Crear interface para navegador web.........................................................33 8. Conclusiones y recomendaciones.......................................................33

4

INDICE DE TABLAS Tabla 1. Beneficios de la implementación de una solución BI......................9 Tabla 2. Cronograma del proyecto.............................................................11 Tabla 3. Requerimiento 01.........................................................................13 Tabla 4. Requerimiento 02.........................................................................13 Tabla 5. Requerimiento 03.........................................................................13 Tabla 6. Requerimiento 04.........................................................................14 Tabla 7. Requerimiento 05.........................................................................14 Tabla 8. Requerimiento 06.........................................................................14 Tabla 9. Requerimiento 07.........................................................................14 Tabla 10. Requerimiento 08.......................................................................15 Tabla 11. Detalles del modelo dimensional................................................15 Tabla 12. Claves primarias........................................................................16 Tabla 13. Definiendo jerarquías por dimensión..........................................17 Tabla 14. Clientes_dim..............................................................................18 Tabla 15. Proveedor_dim...........................................................................18 Tabla 16. Articulo_dim................................................................................18 Tabla 17. Vendedor_dim............................................................................18 Tabla 18. Factura_dim...............................................................................18 Tabla 19. Medidas del negocio..................................................................19

5

INDICE DE FIGURAS Figura 1. Organigrama de la empresa.......................................................10 Figura 2. Base de datos transaccional de la empresa...............................18 Figura 3. Modelo dimensional....................................................................21 Figura 4. MS SQL Server...........................................................................25 Figura 5. Pentaho......................................................................................25 Figura 6. Conexion de la base de datos con Pentaho...............................27 Figura 7. creacion de las tablas de dimension...........................................28 Figura 8. Carga de dimensiones................................................................29 Figura 9. Carga de dimensiones y hechos.................................................30 Figura 10. Cargar dim_cliente....................................................................31

6

1. Planeación y administración del proyecto 1.1

Planteamiento del problema La inteligencia de negocio actúa como un factor estratégico para una

empresa u organización, generando una potencial ventaja competitiva, que no es otra que proporcionar información privilegiada para responder a los problemas de negocio: entrada a nuevos mercados, promociones u ofertas de productos, eliminación de islas de información, control financiero, optimización de costes, planificación de la producción, etc. Actualmente la empresa, cuenta con una base de datos recargada y con información confusa para los trabajadores, por ese motivo no se realizan adecuadamente las estimaciones en los procesos del negocio. El gerente del negocio no tiene la información exacta de las venas y ganancias obtenidas, la falta de pronóstico y que no se utilicen las fuentes históricas en ventas, origina que las metas que se establecen a los vendedores no sean exactas y en ocasiones no llegan a cumplir, esta situación no permite aumentar la productividad de los empleados. La empresa tampoco cuenta con un control en las cajas, lo que produce que algunos vendedores por querer atender rápido a los clientes no generen comprobantes de pago. Actualmente tampoco se cuenta con un control de clientes, algunos vendedores no registran a los nuevos clientes, o registran más de una vez a un mismo cliente lo que genera exceso de datos repetidos. La empresa tiene la necesidad de analizar las ventas realizadas diariamente para así tomar decisiones de continuidad o expansión, pero la información que se tiene esta desordenada, Es por ello que la “Distribuidora Caserita” desea implementar un análisis de predicción mediante el Bussinnes Inteligence, comenzando por armar datos del Data Warehouse, depurarlos para crear la data mart y así finalmente hacer un análisis más preciso de los datos y crear predicciones óptimas para la toma de decisiones 1.2 Estado del arte Se encontraron diversos estudios sobre la aplicación de inteligencia de negocios orientadas a diferentes áreas de la empresa, de las cuales se 7

muestran las orientadas al área de venta, almacén y finanzas, que son las estudiadas en esta investigación. 1.2.1 -

Antecedentes nacionales

“DISEÑO DE UNA SOLUCION DE INTELIGENCIA DE NEGOCIOS COMO HERRAIENTA DE APOYO A LA TOMA DE DECISIONES EN EL AREA DE VENTAS DE LA EMPRESA FARMACEUTICA DISPEFARMA”, En la universidad Nacional Mayor de San Marcos. Este trabajo de tesis llego a las siguientes conclusiones La solución de inteligencia

de

negocios

mejora

significativamente la toma de decisiones en el área de ventas de la empresa Disperfarma. Se identifico los mejores clientes por producto y los clientes fidelizados en el tiempo lo cual facilitara el desarrollo de estos clientes a fin de ofrecerles nuevas promociones La solución de inteligencia de negocios

mejoro

significativamente la calidad de la información haciendo más confiables los reportes generados a partir de ella brindando un soporte para la toma de decisiones de la empresa.[CITATION Car17 \l 10250 ] -

“ANALISIS DISEÑO E IMPLEMENTACION DE UNA DATA MART QUE GARANTICE UNA ADECUADA TOMA DE DECISIONES EN EL AREA DE VENTAS DE LA EMPRESA PROMED E.I.R.L LIMA, 2017”, Este trabajo tiene como objetivo desarrollar e implementar un datamart para garantizar una adecuada toma de decisiones. En conclusión, se demostró que el análisis, diseño e implementación de un datamart garantiza una adecuada toma de decisiones, calidad de reportes y calidad de información en el área de ventas de la empresa.

-

[ CITATION Jos17 \l 10250 ] “DESARROLLO DE UN DATAMART PARA MEJORAR LA TOMA DE DECISIONES EN EL AREA DE VENTAS DE LA CORPORACION

FURUKAWA”,

Llego

a

las

siguientes

conclusiones

8

o

El modelo multidimensional de la solución logro abarcar las necesidades de información identificadas y fue

o

presentada utilizando diagramas de fácil comprensión. Los procesos de extracción, transformación y carga de

o

los datos fueron correctos y coherentes La elección de las herramientas de exploración fue la

o

adecuada, debido a que permitió una fácil interacción. El datamart cubrió las necesidades de los usuarios estratégicos logrando así que la gerencia de ventas tenga ahora una herramienta para el análisis de ventas. [ CITATION Ale14 \l 10250 ]

1.3

Procesos de negocio -

Descripción de la organización -

Razón Social: “Distribuidora Caserita”. Ubicación: Villa el Salvador. Rubro: venta de productos de primera necesidad, golosinas, y

-

bebidas al por mayor y menor. Clientes: Los principales clientes de esta empresa están las amas de casa, y los propietarios de bodegas ya que compran los

-

productos por mayor a bajo precio. Competidores: Los supermercados son una fuerte competencia ya que tienen mayor publicidad y en algunos casos dan ofertas que iguala los precios actuales que tiene la empresa.

-

Organigrama.

9

Figura 1. Organigrama de la empresa

Gerente general Jefe de ventas

Jefe de almacen

Vendedores Fuente: propia 1.4 Alcance del Proyecto Con el presente proyecto se pretende mejorar el proceso de toma de decisiones en la empresa mediante la creación de un Datamart que permita a los encargados del área

venta (jefe de almacén y jefe de

ventas) tener un mejor control de sus procesos y realizar análisis para pronosticar en el futuro. En el área de ventas se tendrá mejor organización en cuanto a las ventas de cada empleado, lo que ayudará a saber el avance de los mismos y la eficiencia en su labor. También se tendrá un historial de ventas mensual, para tener los datos de los productos más vendidos en el mes y reforzar los menos vendidos con promociones o activaciones. 1.5 Objetivos 1.5.1 Objetivo General Desarrollar una data mart para mejorar la toma de decisiones de los procesos de venta de la empresa “Distribuidora Caserita” 1.5.2 Objetivos específicos -

Desarrollar una data mart para mejorar la toma de decisiones de los procesos de venta de la empresa “Distribuidora

-

Caserita”. Desarrollar un data mart que muestre las ventas realizadas

-

durante la semana. Desarrollar un data mart que muestre las ventas realizadas durante el mes.

10

-

Desarrollar un data mart que permita ver que productos se

-

vendieron. Desarrollar un data mart que permita hacer una comparación entre los vendedores.

1.6

Justificación La presente investigación se justifica en la necesidad que tiene la empresa de conocer el nivel de ventas, los productos involucrados y vendedores. Esta investigación propondrá el desarrollo de un DataMart para poder tomar decisiones que ayudará al crecimiento de la organización. Desde el punto de vista académico es justificable debido a que se pretende contribuir con nuevos conocimientos a los demás alumnos de la Carrera de Ingeniería de Sistemas fortaleciendo su formación profesional, sirviendo de ayuda para trabajos posteriores. Justificación metodológica, la metodología Ralph Kimball es una herramienta importante para la aplicación y cobra mucha importancia en su utilidad para obtener una mejora significativa en la toma de decisiones en el área gerencial. 1.7 Recursos y costos 1.7.1

Personal Tabla 1. Recursos y costos del Personal Cantidad

Jefe de proyecto Analista de base de datos Asistente 1.7.2

1

Tiempo (meses) 4

Importe mensual 3500

Importe total 14000

1

3

3000

9000

1 Total

4

1000

4000 27000

Hardware de desarrollo Tabla 2. Recursos y costos del hardware

CPU Monitor Impresor

Cantidad 1 1 1

Importe total 2000 250 500 11

a Teclado y mouse Total 1.7.3

1

100 2850

Software de desarrollo

Tabla 3. Recursos y costos del software MS SQL server Bizagi Pentaho Total 1.8

Cantidad 1 1 1

Costo $0 $0 $0 $0

Beneficios Tabla 4. Beneficios de la implementación de una solución BI

Humano

Beneficios - Equipo de trabajo efectivo capaz de tomar las decisiones apropiadas - Enriquecer el proceso de análisis de la información por parte de la gerencia - Optimizar la gestión dentro de la institución

Tecnológico

-

Material

-

Herramientas de información acorde a las necesidades de la empresa Tecnología amigable con los usuarios Reducción de tiempos y costos en la emisión de reportes y el proceso de análisis de información Información veraz y oportuna Toma de decisiones basadas en reportes dinámicos Fuente propia

1.9 Tareas a realizar Las tareas a realizar las definimos siguiendo la metodología Kimball. Son las siguientes: Planificación del proyecto: - Identificar los problemas. - Definir el alcance del proyecto. - Identificar los requerimientos. - Definir las tareas. - Controlar los procesos de la empresa. Definición de los requerimientos de negocio:

12

-

Realizar entrevistas a los encargados de las áreas en gestión.

Modelado dimensional: -

Elegir el proceso de negocio. Elegir las dimensiones. Establecer el nivel de granularidad. Identificar medidas y tabla de hecho.

Diseño físico Diseño e implementación del ETL Mantenimiento y crecimiento del DWH 1.10 Roles y equipos La única persona encargada de la planificación y desarrollo del proyecto es quien redacta este documento. A mi cargo está el monitoreo de la planificación del mismo, también mencionar que la parte técnica de este proyecto como son: la carga de los ETL, la creación de las dimensiones y de la solución BI también está a mi cargo, siendo el único responsable que toda la información sea consistente y que se trabaje con datos reales 1.11 Administración del proyecto El proyecto se realizará en aproximadamente 15 semanas que dura en ciclo académico, a continuación, el cronograma con las tareas a realizar:

13

Tabla 5. Cronograma del proyecto

Fuente: propia

14

CAPITULO 2 2. Análisis de requerimientos 2.1 Modelo de Negocio La empresa distribuidora caserita se dedica a la venta por mayor y menor de productos de primera necesidad, como también golosinas y bebidas. 2.1.1 Descripción de los procesos de negocio Proceso de ventas El vendedor consolida la venta y registra en el sistema para verificar si los productos están en stock, si los productos requeridos están disponibles hace la factura y deriva al cliente a la zona de despacho para la entrega de los mismos. El sistema hace el descuento y los ajustes necesarios para tener la cantidad exacta de los productos en stock, El encargado de almacén en ocasiones revisa que el stock del actual sea igual al que aparece en el sistema. 2.1.2 Los problemas del Negocio En el negocio principalmente se tiene el problema en el área de ventas, los vendedores no ingresan sus ventas al sistema, lo que no permite un control exacto de los productos vendido y genera problemas al realizar el conteo de productos, tampoco se tiene información de los comprobantes de pago. La gerencia no tiene la herramienta para obtener informes sobre sus ventas diarias, mensuales o anuales, lo que no permite realizar pronósticos a futuro. 2.1.3 Objetivos, Estrategias, Indicadores o KPIs En la terminología empresarial, un indicador clave de rendimiento (KPI) es una medida cuantificable para identificar los éxitos empresariales. Los ejecutivos de una compañía suelen utilizar KPI agrupados en una pestaña empresarial para obtener un resumen histórico rápido y preciso de los éxitos empresariales.[ CITATION Mic14 \l 10250 ] Para el presente proyecto definimos los siguientes indicadores o KPIs: 15

-

2.2

Ventas totales (por semana, mes, año). Ganancias (Por semana, mes y año).

Resumen de los requerimientos obtenidos en la entrevista Para obtener los requerimientos de los usuarios se pactó una entrevista con el gerente general y el encargado de almacén de la empresa. (Anexo 1 y 2) Esta reunión ayudó a comprender mejor las necesidades de los usuarios de tener la información precisa en el momento indicado y de esta forma reducir tiempos. 2.2.1 Catálogo de Requisitos Los requerimientos indicados por los usuarios fueron analizados y nos dio como resultado Lo siguiente: Tabla 6. Requerimiento 01 Requerimiento 01

Nombre

Generar reportes de

ventas Prioridad Alta Tipo Funcional Descripción: La solución de inteligencia de negocios permitirá generar reportes de ventas en el momento que el encargado lo desee. Tendrá en consideración lo siguiente: -

Fecha N° de factura Cliente Monto total Fuente propia Tabla 7. Requerimiento 02

Requerimiento 02 Nombre Medir las ventas Prioridad Alta Tipo Funcional Descripción: se requiere que la solución de inteligencia de negocios mida las ventas en base a la meta presupuestada indicando el porcentaje de cumplimiento obtenido. Fuente propia Tabla 8. Requerimiento 03 Requerimiento 03

Nombre

Generar reportes de productos vendidos 16

Prioridad Alta Tipo Funcional Descripción: Se requiere tener el detalle de productos vendidos por funcionario, además del porcentaje de ventas por producto. Fuente propia Tabla 9. Requerimiento 04 Requerimiento 04

Nombre

Generar ranking de

productos Prioridad Alta Tipo Funcional Descripción: Se requiere tener un ranking de los productos más vendidos en el mes. Fuente propia Tabla 10. Requerimiento 05 Requerimiento 05

Nombre

Generar reportes de productos con poca

venta Prioridad Media Tipo Funcional Descripción: Se requiere tener el detalle de productos poco vendidos para hacer activaciones o crear promociones Fuente propia Tabla 11. Requerimiento 06 Requerimiento 06

Nombre

Sistema amigable

con el usuario Prioridad Alta Tipo No Funcional Descripción: Se requiere que el sistema sea fácil de usar para los usuarios finales. Fuente propia La base de datos transaccional de la empresa se muestra en la Figura 2.

17

Figura 2. Base de datos transaccional de la empresa

2.2.2

Definición de Medidas De acuerdo a los requerimientos definimos dos medidas: -

Cantidad: se refiere a la cantidad de artículos. Monto total: Se refiere al precio total. Ganancia: se refiere a las utilidades de la empresa

3. El modelo lógico: modelo dimensional 3.1

Selección del Data Mart El proceso de negocio según lo analizado anterionemte en el proceso de ventas de la empresa “Distriuidora Caserita”

3.2

Definir las dimensiones Para definir las dimensiones con las que va a contar el data mart se empezó identificando las mas importantes para la elaboración de reportes de ventas que implican artículos, clientes, factura entre otras. Elegimos las siguientes dimensiones Tabla 12. Dimensiones encontradas para el datamart Dimensión encontrada Articulo Cliente Factura Vendedor

Dimensión definida Articulo_dim Cliente_dim Factura_dim Vendedor_dim

18

3.2.1

Mapeando las dimensiones del negocio en las tablas de dimensión Se identificaron las siguientes tablas de las dimensiones definidas anteriormente: Tabla 13. Detalles del modelo dimensional

DIMENSION

DESCRIPCION

NIVELES

Almacena los datos de los

Id_articulo, Art_nombre,

artículos que se venden en

Art_PrecioCompra,

Dim_cliente

la empresa Almacena datos de los

Art_PrecioVenta, Art_stock Id_Cliente, Cli_Nombre,

Dim_factura

clientes Almacena datos de los

NumeroFactura,

comprobantes de pago que

FechaFactura, MontoNeto,

se le entrega a los clientes

MontoIgv, MontoTotal,

Dim_vendedor

Almacena los datos del

utilidad Id_vendedor, Ven_Nombre,

Dim_tiempo

vendedor Almacena los valores de

Dim_Articulo

tiempo en día, mes y año Fuente propia 3.2.2

Agregando las claves primarias a cada dimensión Las claves primarias a cada dimensión son las siguientes: Tabla 14. Claves primarias

Dimensión Dim_cliente Dim_articulo Dim_Factura Dim_Vendedor Dim_tiempo

3.2.3

Clave primaria Id_Cliente Id_Articulo id Id_vendedor Id_tiempo Fuente propia

Identificando las jerarquías analíticas En la siguiente tabla se detalla las definiciones de jerarquía por cada tabla de dimensión. Tabla 15. Definiendo jerarquías por dimensión 19

Dimensión Clientes_dim Articulo_dim

Factura_dim

Vendedor_dim

Descripción Esta dimensión contiene datos de los clientes Esta dimensión abarca los productos en venta

Jerarquía 1. Cli_Nombre,

Esta dimensión contiene los detalles de los comprobantes de pago Esta dimensión contiene los datos de los vendedores Fuente propia

1. 2. 3. 4. 1. 2. 3. 4. 5. 1.

Art_nombre, Art_stock, Art_PrecioCompra, Art_PrecioVenta. FechaFactura, MontoNeto, MontoIgv, MontoTotal, utilidad Ven_Nombre,

3.2.4 Agregar las claves a cada atributo jerárquico. Tabla 16. Dimension clientes Id_Cliente Cli_nombre

CHAR(6) VARCHAR(60) Fuente propia

Clave del cliente Nombre

Tabla 17. Articulo_dim Id_articulo Art_nombre Art_stock Art_PrecioCompra Art_PrecioVenta

CHAR(18) VARCHAR() DECIMAL(10,2) DECIMAL(10,2) DECIMAL(10,2) Fuente propia

Clave del articulo Nombre Cantidad en stock Precio de compra Precio de venta

Tabla 18. Vendedor_dim Id_Vendedor Ven_nombre

CHAR(18) VARCHAR() Fuente propia

Clave del vendedor Nombre

Tabla 19. Factura_dim id FechaFactura

CHAR(18) Datatime

MontoNeto MontoIgv MontoTotal Utilidad

DECIMAL(10,2) DECIMAL(10,2) DECIMAL(10,2) DECIMAL(10,2)

Clave de la factura Fecha de la factura Monto neto Igv Monto total Ganancia por factura 20

Fuente propia 3.2.5 Determinar la granularidad de cada dimensión Llegaremos a un detalle oportuno para este proyecto, no será genérico ni tampoco muy detallado ya que los requerimientos no solicitan una gran granularidad 3.3

Definir la tabla de hechos A continuación, definiremos el modelo dimensional según la metodología Kimball, donde se muestra la relación entre la tabla de hechos y las tablas de dimensiones Figura 3. Modelo dimensional.

Fuente propia 3.3.1 Mapeando las medidas del negocio en las tablas de hechos En la siguiente tabla definimos las medidas de negocio Tabla 20. Medidas del negocio Medida MontoIgv MontoNeto Utilidad

Descripción Monto igv de cada factura Monto neto de cada factura Utilidad recibida por cada venta Fuente propia

3.3.2. Identificando formulas Para definir las medidas, utilizaremos las siguientes formulas: 21

-

MontoTotal: Suma se las medidas MontoIgv y MontoNeto Utilidad:Monto ventas – Monto compras

3.3.3. Granularidad de la tabla de hecho Los requerimientos del proyecto no exigen una alta granularidad, por lo tanto, tenemos una granularidad no muy alta pero si precisa para cada dimensión. 3.4

Definiendo las claves primarias y las relaciones entre tabla de hecho y dimensiones. Una clave primaria de una tabla es la columna cuyos valores son diferentes y en cada fila. Debido a esta diferenciación hacen que cada fiila sea exclusiva . Cada tabla debe tener una clave primaria.[ CITATION IBM19 \l 10250 ] Las claves primarias de la tabla de hecho y sus relaciones son las siguientes: Tabla 21. Claves primarias y relación entre la tabla de hechos y las dimensiones Clave Primaria

Relación

Id_cliente

Relaciona la tabla de hechos con la

Id_articulo

dimensión cliente. Relaciona la tabla de hechos con la

Id_vendedor

dimensión artículo. Relaciona la tabla de hechos con la

Id_factura

dimensión vendedor. Relaciona la tabla de hechos con la

Time_id

dimensión factura. Esta unión, servirá mas adelante para realizar las consultas de fechas, extrae la fecha y la transforma en días, meses y años

Fuente: propia 3.5

Definiendo el Modelo de Estrellas o Modelo de Copo de nieves Un modelo estrella es un tipo de esquema de base de datos que consta de una sola tabla de hechos central rodeada de tablas de

22

dimensiones. Un esquema estrella puede tener cualquier numero de tablas dimensionales. [ CITATION IBM19 \l 10250 ] Por la siguiente definición, el modelo del datamart de este proyecto es el modelo estrella, ya que la única tabla que tiene relación con las demás es la tabla de hechos, por lo tanto, toda información relacionada con una dimensión debe estar en la misma tabla. Se

escogió

este

modelo

por

la

ventaja

que

trae

a

la

desnormalización, es decir obviar las uniones entre las tablas cuando se realizan consultas, procurando asi un mejor tiempo de respuesta , y mayor sencillez en cuanto a la utilización.

23

CAPITULO 4

4. Diseño técnico de la arquitectura Se detalla el ambiente de integración junto con las tecnologías a utilizar y el funcionamiento del datamart uniendo los factores como requerimiento, entorno y estrategias de diseño 4.1 Descripción de la fuente de datos transaccional 4.1.1

Modelo de la Fuente de datos

…………. X ESQUEMAS si es muy grande 4.1.2

Descripción breve del modelo

4.2 Infraestructura: servidores, equipos. Para definir el modelo físico del datamart se establece el modelo copo de nieve y para la construcción del mismo se recomienda el uso de: Software: -

MS SQL Server Pentaho data integration Figura 4. MS SQL Server

Fuente: Google.com Figura 5. Pentaho

Fuente: Google.com 24

Hardware: 4.3

Procesador Core i3 Memoria RAM 6 GB Memoria interna 700 GB

Flujo técnico de la arquitectura: back room, front room Para el flujo técnico de la arquitectura nos basaremos en dos divisiones: -

Back room: la arquitectura Kimball supone que los datos utilizados por las aplicaciones de BI están estructurados dimensionalmente, organizados por procesos comerciales y unidos por la arquitectura

-

del almacén de datos de la empresa.[ CITATION Kim19 \l 10250 ] Front room: Es la cara publica del datawarehouse, es lo que los usuarios ven y trabajan.[ CITATION Kim19 \l 10250 ]

25

5. Procesos de extracción, transformación y carga 5.1

Identificar fuentes y destinos detallados Se identifico como origen a la base de datos transacional “cowapi” y en la a continuacion se muestran las tablas de origen para el proceso de extracción de datos: -

dbo.cliente dbo.factura dbo.vendedor dbo.articulo Como destino se creó la base de datos “etlfinal”, en la siguiente tabla

se detalla las tablas y la relación con la tabla de origen Tabla 22,Rrelacion de tablas de origen y destino Origen

Destino

dbo.CLIENTE

cliente

dbo.FACTURA

ventas

dbo.VENDEDOR

vendedor

dbo.ARTICULO

articulo

Fuente propia

5.2

Selección de herramienta de ETL Usaremos la herramienta Pentaho para nuestra ETL para ello debemos primero crear la tabla “etlfinal” que será la tabla de destino, donde se cargaran las dimensiones y la tabla de hechos de ventas, para ello necesitamos establecer una conexión entre el gestor que es MS SQL Server y la herramienta de integración de datos PENTAHO, asignaremos una clave y usuario a la tabla. Figura 6. Conexion de la base de datos con Pentaho

26

Crearemos las tablas para cada dimension Figura 7. creacion de las tablas de dimension

fuent

e propia 5.3

Cargar dimensiones Para cargar las dimensiones establecemos la conexión entre la base de datos transacsional de MS SQL Server y Pentaho.  

Localhost.cowapi.sa es la conexión a la base de datos origen Localhost.etlfinal.sa es la conexión a la base de datos de destino, donde cargaremos las dimensiones. En el Pentaho, creamos un job que llamaremos “Carga de

dimensiones” en este se extraerán los datos seleccionados de las tablas de la base de dato origen y se cargaran a las tablas de dimensiones correspondientes en la base de datos destino.

27

Figura 8. Carga de dimensiones

Fuente propia 5.4

Carga dimensiones y tabla de hechos incrementales. Para la carga de dimensiones y hechos creamos un Job principal en Pentaho, que funcionara de la siguiente forma: -

El proceso empieza con la el código SQL que limpiara las tablas de

-

dimensiones. Sigue el Job “Carga de dimensiones”, el que explicamos en el

-

capitulo anterior. Se carga a la tabla de hechos los datos seleccionados en el job

-

“Carga de dimensiones” El proceso termina cuando todo salga exitoso

Figura 9. Carga de dimensiones y hechos

Fuente propia

28

6. Diseño físico 6.1

Trasladar el modelo dimensional al modelo físico. Aplicando nombre s y estándares de la base de datos, identificar el tipo de datos y la longitud de las columnas, etiquetar atributos como Nulos y No nulos. 6.1.1 Diseño fisico de la dimension cliente Nombre de tabla: dim_cliente Tabla 23. diseño fisico de la dimension cliente

Campo

Tipo

Longitud

Atributo

Id_cliente

NCHAR

10

NOT NULL

Cli_nombre

NVARCHAR

60

NULL

Fuente propia Para cargar la tabla dim_cliente en pentaho, seleccionamos los datos de entrada de la base de datos transaccional “cowapi”, que son los mencionados en la tabla anterior (Id_cliente y Cli_nombre), le añadimos la transformación secuencia, para que estos datos esten ordenados y lo dirigimos a la base de datos de destino y estaría completa la carga de la dimensión cliente Figura 10. Cargar dim_cliente

Fuente propia 6.1.2 Diseño fisico de la dimension vendedor Nombre de tabla: dim_Vendedor Tabla 24. diseño fisico de la dimension Vendedor

Campo

Tipo

Longitud

Atributo

Id_Vendedor

NVARCHAR

10

NULL

Ven_Nombre

NVARCHAR

50

NULL 29

Fuente propia Para

cargar

la

tabla

dim_Vendedor

en

pentaho,

seleccionamos los datos de entrada que son los mencionados anteriormente (Id_Vendedor y Ven_nombre) le añadimos la transformación secuencia, para que estos datos esten ordenados y lo dirigimos a la base de datos de destino y estaría completa la carga de la dimensión vendedor.

Fuente propia 6.1.3 Diseño fisico de la dimension Articulo Nombre de tabla: dim_Articulo Tabla 25. diseño fisico de la dimension articulo

Campo

Tipo

Longitud

Atributo

Id_Articulo

NVARCHAR

10

NULL

Art_Nombre

NVARCHAR

50

NULL

Art_PrecioCompra NUMERIC

10,2

NULL

Art_PrecioVenta

NUMERIC

10,2

NULL

Art_Stock

NUMERIC

10,2

NULL

Fuente propia Para cargar la tabla dim_artucilo en pentaho, seleccionamos los datos de entrada que son los mencionados anteriormente le añadimos la transformación secuencia, para que estos datos esten ordenados y lo dirigimos a la base de datos de destino y estaría completa la carga de la dimensión articulo.

30

6.1.4 Diseño fisico de la dimension tiempo La dimensión tiempo la creamos para generar las consultas posteriores a la tabla de hechos. Para esto se extrae la fecha de las facturas, se genera una fila nueva y se transforma dichas fechas en dia, mes, año. Se generaron los siguientes datos: CAMPO The_date The_year Month_of_year Time_id The_day The_month quarter Day_of_week Day_of_year Is_leap_year Week_of_month Week_of_year

TIPO DATETIME int Int Int Varchar Varchar int Int int

LONGITUD

100 100

Int Int

ATRIBUTO NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

6.2 Determinar la longitud de la BD del DWH 6.3 Determinar la estrategia de indexación. 6.4 Sentencias SQL para crear el modelo

31

Figura 11. Agregando claver foranea a la dimension vendedor

Figura 12. Agregando clave foranea a la dimension cliente

Figura 13. agregando claves foraneas a la base de datos ventas

7. Aplicaciones para usuarios finales: características

32

7.1

Elegir la herramienta para procesamiento analítico. 7.2 Lista los resúmenes de información requeridos por los usuarios. 7.3 Determinar los cubos. 7.4 Diseñar los reportes a mostrar. Mostramos los mejores vendedores

7.5 Selección de herramienta de aplicación para usuario final: Puede usar Ms. Excel u otra herramienta. 7.6 Implementación de la herramienta Ms Excel u otra herramienta 7.6.1 Crear las tablas dinámicas. 7.6.2 Construir los gráficos dinámicos. 7.6.3 Crear interface para navegador web 8. Conclusiones y recomendaciones

33

34

ANEXOS ANEXO N° 1: ENTREVISTA AL GERENTE DE LA EMPRESA

Entrevistado: Javier Rojas, gerente general de la empresa Entrevistador: Lisseth Hernández

Fecha: Setiembre 2019

Objetivo: conocer la situación actual de la tecnología de la empresa Dirigido a: Encargado de la empresa “Distribuidora Caserta” Preguntas realizadas: ¿De que manera se realizan los reportes en la empresa actualmente? ¿siente usted que es correcto el uso que se le esta dando al sistema por parte de sus trabajadores? ¿considera que esta nueva implementación ayudara al crecimiento y expansión de su empresa?

Resumen: Actualmente los reportes se hacen por medio del sistema que utiliza la empresa, pero demoran mucho en cargar y solo generan datos especifico que no son muy útiles para el entrevistado. El gerente indica que sus empleados ven el sistema poco amigable por lo que su trabajo les resulta pesado. Para el gerente esta implementación le será de mucha ayuda porque podrá tener la información exacta de su empresa.

35

ANEXO N°2: Entrevista al encargado de almacén

Entrevistado: Juan Sánchez Entrevistador: Lisseth Hernández

Fecha: Setiembre 2019

Objetivo: conocer la situación actual en el área de almacén Dirigido a: Encargado de realizar las compras Preguntas realizadas: ¿De qué manera estima la cantidad a comprar de los productos? ¿Considera que esta nueva implementación ayudara realizar mejor su trabajo?

Resumen: Actualmente no cuenta con reportes o pronósticos para realizar las compras, el encargado tiene en una libreta los productos mas vendidos y revisa semanalmente el almacén para verificar si ya es momento de realizar la compra, por otro lado, los vendedores le informan cuando un producto se está terminando para que él pueda realizar la compra. Considera que esta implementación de una solución BI le ayudara a realizar su trabajo desde su oficina y de manera mas exacta en cuanto a las cantidades de compras.

36

-

GLOSARIO DE TERMINOS Jerarquias de dimensiones: Es un constructor de datos utilizado para agrupar datos en intervalos basados en valores. Una jerarquía de dimensiones puede contener varios niveles, cada uno de los cuales con su propio conjunto de grupos. Los grupos de cada nivel inferior se deben

-

acumular en grupos en niveles superiores. [ CITATION IBM191 \l 10250 ] ETL: Son las siglas en inglés de Extraer, Transformar y Cargar (Extract, Transform and Load). Es el proceso que permite a las organizaciones mover datos desde múltiples fuentes, reformatearlos y limpiarlos, y cargarlos en otra base de datos, data mart, o data warehouse para analizar, o en otro sistema operacional para apoyar un proceso de negocio. [ CITATION

-

Esp19 \l 10250 ] OLP Datamining Pentaho: Es una herramienta desarrollada en Java, lo que le permite ser compatible con cualquier sistema operativo. Esta diseñado para atudar enlos procesos ETL´s , facilita la construcción, actualizacon y mantenimiento del Data Warehouses.[ CITATION Pen19 \l 10250 ]

-

37

REFERENCIAS BIBLIOGRAFICAS Carhuallanqui. (2017). DISEÑO DE UNA SOLUCION DE INTELIGENCIA DE NEGOCIOS COMO HERRAIENTA DE APOYO A LA TOMA DE DECISIONES EN EL AREA DE VENTAS DE LA EMPRESA FARMACEUTICA DISPEFARMA. Durand, A. (2014). DESARROLLO DE UN DATAMART PARA MEJORAR LA TOMA DE DECISIONES EN EL AREA DE VENTAS DE LA CORPORACION FURUKAWA. Espinosa. (11 de 2019). Obtenido de Herramientas ETL: /www.dataprix.com/blogs/respinosamilla/herramientas -etl-que-sonpara-quevalen-productos-mas-conocidos-etl-s-open-sour Group, K. (11 de 2019). Kimball Group. Obtenido de Kimball Group: kimballgroup.com IBM. (11 de 2019). ¿Que es la jerarquia de dimensiones? Obtenido de www.ibm.com/support/knowledgecenter/es/SSCVKV_9.1.2/Campaign IBM. (11 de 2019). ibm. Obtenido de ibm: ibm.com Microsoft. (2014). Microsoft KPI. Samame, J. (2017). ANALISIS DISEÑO E IMPLEMENTACION DE UNA DATA MART QUE GARANTICE UNA ADECUADA TOMA DE DECISIONES EN EL AREA DE VENTAS DE LA EMPRESA PROMED E.I.R.L LIMA, 2017.

38

9. CAPÍTULO 5 10.

PROCESOS DE EXTRACCIÓN, TRANSFORMACIÓN Y CARGA Para llevar a cabo de manera correcta el proceso de extracción, primera fase del ETL, hay que seguir los siguientes pasos:

  



Extraer los datos desde los sistemas de origen. Analizar los datos extraídos obteniendo un chequeo. Interpretar este chequeo para verificar que los datos extraídos cumplen la pauta o estructura que se esperaba. Si no fuese así, los datos deberían ser rechazados. Convertir los datos a un formato preparado para iniciar el proceso de transformación

4.1. Identificar fuentes y destinos detallados Para el presente proyecto los datos de la empresa EnergyPla BI de origen o fuente de entrada son las hojas de Excel para luego ser cargadas y transformadas mediante la herramienta de BI Pentaho para obtener como resultado una base de datos en MySQL.

4.2. Selección de herramienta de ETL PENTAHO. es una herramienta de Business Intelligence desarrollada bajo la filosofía del software libre para la gestión y toma de decisiones empresariales. Es una plataforma compuesta de diferentes programas que satisfacen los requisitos de BI. 4.3. Cargar dimensiones Para cargar las dimensiones se estableció la conexión la data de una hoja de Excel y el gestor de base de datos MySQL y Pentaho. En el Pentaho, creamos un “Trabajo” que llamaremos “ETL_ENERGY_PLAST” en este se extraerán y se transforman los datos seleccionados de las hojas de Excel de origen y se serán cargadas a las tablas de dimensiones correspondientes en la base de datos destino. 39

Figura 14: ETL

Fuente: Propia.

Figura 15: Carga con éxito de los datos del ETL.

4.4. Carga dimensiones y tabla de hechos incrementales. A la hora de realizar la carga de dimensiones y hechos crearemos un “Trabajo” principal en Pentaho, que tendrá las siguientes funciones: -

El proceso empieza con un programa SQL a ejecutar. Luego comienza con la extracción de datos de la hoja de cálculo Excel. Paso seguido habrá una transformación de la información requerida. El proceso finaliza cuando todo salga el mensaje “Job execution finished ”.

40

Figura 16: ETL y flujo de datos de la tabla de hechos PRODUCCION.

Fuente: Propia

Figura 17: Código SQL en PENTAHO de la tabla de hechos.

Fuente: Propia

1.11.1.1

ETL y flujo de datos de la dimensión ARTICULOS. Figura 18: ETL y flujo de datos de la dimensión ARTICULO.

41

Fuente: Propia

Figura 19: Carga con éxito el ETL de la dimensión ARTICULO.

Fuente: Propia

1.11.1.2

ETL y flujo de datos de la dimensión MAQUINAS. Figura 20: ETL y flujo de datos de la dimensión MAQUINAS.

Fuente: Propia

Figura 21: Código SQL generado en PENTAHO.

42

Fuente: Propia

Figura 22: Datos Cargados mediante el ETL.

Fuente: Propia

1.11.1.3

ETL y flujo de datos de la dimensión EMPLEADOS_DIM. Figura 23: ETL y flujo de datos de la dimensión MAQUINISTAS.

Fuente: Propia

43

Figura 24: Código SQL generado en la herramienta BI PENTAHO.

Fuente: Propia

Figura 25:Datos Cargados mediante el ETL.

Fuente: Propia

1.11.1.4

ETL y flujo de datos de la dimensión TIEMPO_DIM. Figura 26: ETL y flujo de datos de la dimensión TIEMPO.

Fuente: Propia

44

1.11.1.5

ETL y flujo de datos de la dimensión TURNO_DIM. Figura 27: ETL y flujo de datos de la dimensión TURNO.

Fuente: Propia

1.11.1.6

ETL y flujo de datos de la dimensión MATERIA PRIMA. Figura 28: ETL y flujo de datos de la dimensión MATERIA PRIMA.

Fuente: Propia 4.5.

45

11.

CAPÍTULO 5

12.

CONCLUSIONES Y RECOMENDACIONES

5.1. CONCLUSIONES Se identificó los requerimientos de análisis de información para las áreas de Producción. Lo logró elaborar un modelo de base de datos multidimensional que permita el análisis y explotación de la información identificada. Se pudo construir el DataMart para mostrar la información que se necesita para poder tomar decisiones estratégicas en el área de producción. Se podrá tomar mejores decisiones en cuanto al uso de los recursos, promociones y personal apoyados por una herramienta estratégica de gran alcance como lo es un Datamart. Los usuarios contarán con información real y oportuna, sin que exista la dependencia de otra aplicación. Es importante la correcta definición de las jerarquías de las dimensiones del modelo de la base de datos, ya que de lo contrario se podría obtener data incompleta y/o errónea al momento de implementar una herramienta de Inteligencia de Negocios sobre todo en la explotación de los cubos, lo cual se solucionaría mediante el remodelamiento de la base de datos y los cubos. 5.2. RECOMENDACIONES Se recomienda que la solución siga creciendo con la adición de información de todas las demás áreas con las que cuenta EnergyPlast, al modelo ya construido. Se debe mantener la administración y continuidad del seguimiento a la carga de información hacia el DataMart, a fin de que se mantenga actualizado y realmente sea de beneficio para los usuarios finales y tomadores de decisiones. Es importante que la información ingresada a la base de datos sea la original, es decir datos verdaderos y no manipulados o cambiados, pues los reportes que luego se generarán no serán fiables y éste será un motivo principal de malas decisiones que se puedan tomar.

46

13.

ANEXOS

14.

BIBLIOGRAFÍA Ordoñez Cabrera, L. M. (2007). DataMart de estadísticas médicas. México. Rosales Sedano, C. P. (2009). Análisis, diseño e implementación de un DataMart para el soporte de toma de decisiones y evaluación de las estrategias sanitarias en las direcciones de salud. Lima, Perú.

47

Alfaro Mendoza, L. A., & Paucar Moreyra, D. A. (2016). Construcción de un Datamart que apoye en la toma de decisiones de la gestión de incidencias en una mesa de ayuda: caso Consorcio Peruano de Empresas. Lima, Perú. Espinoza Vargas, J. M., & Palomino Ruiz, C. A. (2016). Desarrollo de un datamart para optimizar la generación de información estratégica de apoyo a la toma de decisiones en la Vicepresidencia de Banca Comercial de Interbank. Lima, Perú. Gamarra Ramirez, A. J. (2011). Solución integral para explotar eficientemente la información de los contactos con los clientes utilizando DataMart en Telefónica del Perú. Lima, Perú. Guillén Rodríguez, F. S. (2012). Desarrollo de un DataMart para mejorar la toma de decisiones en el área de tesorería de la Municipalidad Provincial de Cajamarca. Cajamarca.

48