Creación de un Data Warehouse como solución de inteligencia de negocios, para optimizar la toma de decisiones en un negocio de venta de comida china con envíos a domicilio.
- Identificar los procesos de toma de decisiones y analizar los requerimientos de información de acuerdo a las perspectivas y necesidades de la empresa.
- Creación de Diagrama relacional y el script de la base de datos OLTP.
- Diseño de la base de datos OLAP modelo en estrella.
- Desarrollo de ETL que se permita ejecutar siempre de forma exitosa.
La base de datos que utilizamos es una Base de Datos OLTP, es de creación inédita y está realizada para un restaurante llamado Samogi. Contiene 8 entidades, las cuales son las siguientes: Customers, Ordes, Sizes, Products, Categories, Prices, Employees y Dealers. La base de datos OLTP se diseñó utilizando un modelo entidad-relación, el cual permitió crear relaciones específicas entre cada una de las entidades.
A continuación, el siguiente diagrama representa el modelo entidad-relación de la Base de datos OLTP.
Así como los sistemas OLTP son típicos para bases de datos convencionales y data warehouse, los sistemas OLAP son propios de los datamart.
Se utilizó el modelo en estrella para la creación de nuestra base de datos OLAP.
Las tablas de dimensiones utilizadas fueron cinco y se llamaron de la siguiente forma (Dim Nombre).
Se realizaron las uniones correspondientes entre la tabla de hechos y las tablas de dimensiones.
Las tablas de dimensiones:
- Dim Products,
- Dim Employees ,
- Dim Customers,
- Dim Time.
En base a las preguntas del negocio y a la métrica (las cuales se definen en el siguiente enunciado) se determinó que la tabla de Hechos se llamará Hechos Orders Sales.
Campos de Dimensiones:
Dimensión Products:
- Product_ID.
- Product_name.
- Product_size.
- Product_price.
- Product Category.
Dimensión Employees:
- Employee_ID.
- Employee_FullName.
Dimensión Customers:
- Customer_ID.
- Customer_FullName.
Después de haber definido las tablas dimensionales del cubo, queda definir la tabla dimensional de time, la cual siempre debe ir en todo modelo de DWH. Los campos que tiene determinada tabla dimensional dependen de las necesidades del negocio. Para este caso se decidió la siguiente división de tiempo.
Dimensión Time:
- Time_ID.
- Month.
- Trimester.
- Semester.
- Week_day.
Por último, queda definir la tabla de hechos. Esta tabla contiene los id de las tablas dimensionales y las métricas, las cuales se utilizarán para la medición que proporcionan los datos del cubo.
Campos Tabla de Hechos:
- Hecho_ID (Llave primaria, valor autoincremental)
- Product_ID
- Employee_ID
- Customer_ID.
- Time_ID.
- Total_Amount (métrica).
- Quantity_Clients (métrica).
- Quantity_Sale_Products (métrica).
Figura 2 - Base de datos OLAP - Data Mart
El restaurante Samogi desea conocer lo siguiente:
- Se desea conocer el monto total de ventas en las entregas de platillos en base a los empleados. Es necesario conocer el id y nombre completo del empleado que hace la entrega.
- Las ventas deben analizarse por mes, trimestre, semestre (tiempo).
- Se desea conocer el día de la semana (conteo) que representa mayor consumo.
- Se desea conocer la cantidad de clientes atendidos en base al mes.
- Se desea conocer los productos que más y menos se han vendido en el restaurante. Es necesario mostrar el nombre, id del producto categoría y precio.
A partir de las preguntas de negocio planteadas en el punto anterior identificamos y decidimos que utilizaremos tres métricas. Dado que como primer objetivo de las preguntas del negocio es el de conocer el monto obtenido a través de las ventas, se creó una primera métrica con el nombre Total_Amount, la segunda métrica identificada está basada en el deseo de conocer la cantidad de clientes atendidos en base al mes, por lo tanto, esa métrica se llamó Quantity_Clients. Y para finalizar la métrica número tres que se estableció para formar parte de las que se utilizarían, fue Quantity_Sale_Products haciendo referencia a los productos que más se han vendido o que menos se han vendido en el restaurante.
- Se desea conocer el monto total de ventas en las entregas de platillos en base a los empleados. Es necesario conocer el id y nombre completo del empleado que hace la entrega.
- Se desea conocer la cantidad de clientes atendidos en base al mes.
- Se desea conocer los productos que más y menos se han vendido en el restaurante. Es necesario mostrar el nombre, id del producto categoría y precio.
Métricas:
- Total_Amount: Monto total de una orden (se debe multiplicar el precio del producto por el número de unidades).
- Quantity_Clients: Cantidad de clientes atendidos durante el mes.
- Quantity_Sale_Products: Cuáles son los productos más y menos demandados.
El proceso de ETL se encargará de extraer, transformar y cargar la información en la base de datos OLAP.
Fue creado en Pentaho, una plataforma de inteligencia empresarial (BI) “orientada a la solución” y “centrada en procesos” que incluye todos los principales componentes requeridos para implementar soluciones basadas en procesos tal como ha sido concebido desde el principio.
Figura 3 - ETL Completo
Limpieza de datos: Su objetivo es el de realizar diferentes tipos de acciones con la finalidad de solucionar problemas con datos erróneos, inconsistentes o irrelevantes.
Figura 4 - Limpieza de tablas Dimensiones
Una vez limpiadas las tablas de dimensiones procedemos a limpiar la tabla de hechos.
Figura 5 - Limpieza de tabla Hechos
Después de la limpieza se procede al llenado de las tablas de dimensiones
Figura 6 - Llenar tablas Dimensiones
Una vez que habíamos llenado la tabla de dimensiones. Fue posible llenar la tabla de hechos que depende de las tablas de dimensiones.
Figura 7 - Llenar tabla Hechos
Cuando las tablas se han llenado se procede a comprobar el éxito de nuestro ETL.
Figura 8 - ETL Completado con éxito
Para la creación de los informes utilizamos Tableau desktop ya que Tableau server pide muchos requerimientos que nuestras computadoras no cumplen.
Tableau es una herramienta de Inteligencia de Negocios que permite analizar, visualizar y compartir grandes volúmenes de información en forma rápida, flexible y amigable. Tableau se destaca por su flexibilidad y rapidez tanto en el procesamiento de los datos, como en la obtención de resultados. Según se necesiten, se pueden ir agregando y cambiando parámetros, añadir puntos de referencia o tendencias y otros elementos que enriquezcan el análisis a realizar. Esto permite que quien lo use, lo haga en forma independiente, liberando recursos del área de sistemas.
Ahora podremos realizar análisis con la información y múltiples cantidades de reportes que nos muestran los resultados de la exploración de los datos.
A continuación, se mostrarán algunos de esos análisis:
Figura 9 - Cantidad de productos vendidos por mes.
Figura 10 - Cantidad de productos vendidos en el año.
Figura 11 - Monto total por un cliente en un trimestre.
Figura 12 - Cantidad de Clientes atendidos por mes.
Figura 13 - Cantidad de productos distribuidos por cada transportista.
Figura 14 - Monto total generado por mes.
- Oracle 11g express
- SQL Server Management Studio
- SqlDeveloper edición portable
- Descarga de Pentaho
- Tableau Desktop
Facultad de Ingeniería
Departamento de Ingeniería en Sistemas