Ejercicio de bases de datos OLAP

1. Definición y delimitación del problema

1.1. Contexto

Uno de los métodos de transporte más usados por los seres humanos es el transporte aéreo. Aunque el número de vuelos diarios se vio afectado por la pandemia de la COVID-19, es bien sabido que son muchos los aviones que diariamente se movilizan de un sitio a otro. Es por ello que para tener un control sobre estos movimientos se han creado diversas organizaciones internacionales que lleven un registro de aerolíneas, aviones, itinerarios, equipajes, entre otra información. Una de ellas es la Asociación Internacional de Transporte Aéreo, IATA por sus siglas en inglés, asociación que surge en el año 1919 en los Países Bajos conformada en ese entonces por 57 miembros de 31 países pertenecientes principalmente a Europa y Norteamérica. Años después fue relanzada en Cuba en 1945. Actualmente incluye 290 aerolíneas de 120 países. Sus misiones principales comprenden la promoción de la seguridad, la protección y la fiabilidad en el transporte aéreo y el cuidado del medio ambiente. Todo esto en pro del beneficio económico de sus accionistas. Adicional a esto la IATA:

  1. Representa a las 290 aerolíneas que la conforman.

  2. Simplifica procesos y reduce costos que aumentan su flujo financiero y,

  3. Es un puente para asegurar el movimiento de las personas alrededor del mundo

1.2. Problema

La Asociación Internacional de Transporte Aéreo (IATA) posee una base de datos donde tiene almacenada la información recolectada de todos los vuelos hechos a nivel global a partir del año 1945. El Modelo Relacional que la Asociación utilizó para la construcción de sus bases de datos relacional fue el siguiente:

MR

El MR contiene la siguiente información:

• Las ciudades a las que pertenecen los usuarios.

• Cuál usuario tomó cuál avión en cuál itinerario (reflejado en la tabla vuelos).

• Los aeropuertos implicados en un itinerario y la ciudad a la que pertenecen.

• Las fechas de los itinerarios.

• Los modelos de los aviones y las aerolíneas a las que pertenecen.

La IATA quiere analizar en qué medida la pandemia del COVID-19 afectó el transporte aéreo de personas en el año 2020. Específicamente desea saber (requerimientos de análisis):

  1. Cuál aerolínea realizó el mayor número de vuelos a la ciudad de Roma en el año 2019 y en el año 2020.

  2. Total de dinero recaudado por vuelos de cada aerolínea en el primer semestre del año 2019 y en el primer semestre del año 2020.

  3. Cuál modelo de avión realizó el mayor número de vuelos en el año 2019 y en el año 2020.

  4. Cuál fue la ciudad cuyos habitantes viajaron más en el año 2019 y en el año 2020.

Para tal fin la IATA quiere contratar a un experto en bases de datos analíticas que implemente el mejor modelo multidimensional para obtener, a partir de su base de datos relacional, la información necesaria que responda a estas cuestiones.

2. Hipótesis para el problema

2.1. Pregunta

¿Puede la IATA a partir de una base de datos relacional y mediante herramientas de Business Intelligence como Pentaho Data Integration, aplicar un proceso de ETL, insumo principal para la herramienta PostgreSQL en el diseño un modelo multidimensional OLAP implementado el modelo estrella, saber si el COVID-19 afectó los ingresos de las empresas de aviación en el año 2020 con respecto al año 2019?

2.2. Hipótesis

Partiendo de una base datos relacional , aplicando el método de ETL y alimentando el entorno de Data Warehouse, se puede conocer las características y comportamientos de una base de datos multidimensional respondiendo a consultas de la IATA en referencia al impacto del CODID-19 en los ingresos de las empresas de aviación en el año 2020 con respecto al año 2019.

3. Desarrollo

3.1. Base de datos relacional - ORACLE

Origen de los datos

3.2. Creando tablas - ORACLE

3.2.1. Aerolineas y aeropuertos

3.2.2. Aviones y ciudades

3.2.3. Ejemplo

3.2.4. Itinerarios y modelos

3.2.5. Tiempo y usuarios

3.3. Diagrama - Modelo Estrella

3.3.1. Análisis de hechos

El primer paso que se debe tener en cuenta es el análisis de los hechos, los cuales son los que hacen referencia a un proceso de negocio de una organización. Por lo cual para el caso de estudio es lo relacionado con las aerolíneas, por lo que se podría determinar que el hecho seria la relación de los vuelos.

3.3.2. Análisis de las dimensiones

El segundo paso es el análisis de dimensiones. Para la primera pregunta lo que se busca conocer es cuál aerolínea realizó el mayor número de vuelos a la ciudad de Roma en el año 2019 y en el año 2020 que en nuestro caso por la información de la tabla se cambió a Bogotá. Por ello las dimensiones identificadas serian la aerolínea, ciudad que este caso está en itinerarios y la fecha.

Para la segunda pregunta se desea conocer el total de dinero recaudado por vuelos de cada aerolínea en el primer semestre del año 2019 y en el primer semestre del año 2020. Por esto las dimensiones identificadas para este punto serian a la aerolínea, el costo pero esto está relacionado con vuelos y estos hacen parte el itinerario vuelos y la fecha.

Para la tercera pregunta se busca conocer cuál modelo de avión realizó el mayor número de vuelos en el año 2019 y en el año 2020. Las dimensiones identificadas a las que hacen referencia al modelo que hace parte de aviones y la fecha.

Para el cuarto punto se desea saber cuál fue la ciudad para la que sus habitantes viajaron más en el año 2019 y en el año 2020. Las dimensiones para este serian la ciudad que este caso está en itinerarios , usuarios que estos están dentro de vuelos y este hace parte de itinerarios y la fecha.

3.3.3. Análisis de los indicadores o métricas

Para el tercer paso debemos analizar los indicadores que hacen referencia a las métricas del hecho analizado. En este caso los indicadores serían el total de vuelos vendidos y el total de ventas de vuelos vendidos (esta última métrica se puede entender como el total de dinero obtenido por las ventas de los vuelos).

Con lo anterior mencionado tendríamos:

  1. Hechos (proceso de analizar):
  1. Relación de los vuelos
  1. Dimensiones (puntos de vista del análisis):
  1. Aerolínea.
  2. Itinerario.
  3. aviones
  4. Tiempo
  1. Métricas (variables a analizar):
  1. Total, de vuelos por aerolínea
  2. Total, de ventas.
  3. Total de vuelos por modelo
  4. Total de vuelos por ciudad
  1. Análisis de las jerarquías de las dimensiones.

Para esto lo que se busca es determinar que ya teniendo cada una de la dimensiones identificadas, cuales podríamos organizar de manera jerárquica según lo que se está trabajando en el momento y lo que se busca en el futuro. Como el e caso de aviones que de ahí se puede determinar la aerolínea y el modelo, el tiempo que se puede sacar el año y el mes e itinerario que tendríamos la ciudades de origen y destino.

  1. Diseño del modelo.

El quinto y último paso es realizar el diseño del modelo

3.4. Base de datos PostgreSQL

3.4.1. Data Warehouse

3.4.2. Creación de tablas

3.4.2.1. Dim_areolineas

3.4.2.2. Dim_aviones

3.4.2.3. Dim_itinerarios_vuelos

3.4.2.4. Dim_tiempo

3.4.2.5. Fact_relacion_vuelos

3.5. Pentaho - Spoon

3.5.1. ETL

3.5.1.1. Aerolineas_extr

3.5.1.2. Aerolineas_load

3.5.1.3. Aviones_ext

3.5.1.4. Aviones_load

3.5.1.5. Tiempo_ext

3.5.1.6. Tiempo_load

3.5.1.7. Itin_vuelos_ext

3.5.1.8. Itin_vuelos_load

3.5.1.9. Fact_extr

3.5.1.10. Fact_extr

3.6. Verificación Oracle - PostgreSQL

3.6.1. Dimensiones

3.6.1.1. Dim_aerolineas

3.6.1.2. Dim_aviones

3.6.1.3. Dim_itinerario_vuelos

3.6.1.4. Dim_tiempo

3.6.1.5. Fact_relacion_vuelos

3.7. Modelando el cubo

3.7.1. Creando el cubo

3.7.2. Dim_fac_relación

3.7.3. Iata_cube_schema

3.8. Visualización de datos en Power BI

3.8.1. Conectando la base de datos postgreSQL

3.8.2. Creación de tableros de control para analisis de datos.

3.8.2.1. Analisis exploratorio

3.9. Respuestas a las preguntas

3.9.1. Cuál aerolínea realizó el mayor número de vuelos a la ciudad de Bogotá en el año 2019 y en el año 2020.

Como se observa en la gráfica anterior la aerolínea que realizó el mayor número de vuelos a la ciudad de Bogotá fue Satena

3.9.2. Total de dinero recaudado por vuelos de cada aerolínea en el primer semestre del año 2019 y en el primer semestre del año 2020.

En la gráfica se evidencia que en la aerolínea que mayor recuado obtuvo fue LANCO para el año 2019 fue de US$2.210.925 y para el año 2020 US$925.416. Sufrió una disminución de US$1.285.509 un 58% aproximadamente.

3.9.3. Cuál modelo de avión realizó el mayor número de vuelos en el año 2019 y en el año 2020.

En la gráfica de embudo se identifica que el modelo de avión que realizó mayor numero de vuelos es Boeing 737-700 con 20 vuelos

3.9.4. Cuál fue la ciudad cuyos habitantes viajaron más en el año 2019 y en el año 2020.

La ciudad que más habitantes viajaron en el año 2019 y 2020 fueron los de la ciudad de Bogotá

3.9. Crítica de la hipótesis

Hipótesis

Partiendo de una base datos relacional , aplicando el método de ETL y alimentando el entorno de Data Warehouse, se puede conocer las características y comportamientos de una base de datos multidimensional respondiendo a consultas de la IATA en referencia al impacto del CODID-19 en los ingresos de las empresas de aviación en el año 2020 con respecto al año 2019.

Al realizar los pasos establecidos en el numeral 3.3 de análisis de los hechos, el análisis de las dimensiones, el análisis de los indicadores y lograr dar respuesta en e numeral 3.9 a las preguntas planteadas en la definición y delimitación del problema en el numeral 1, no se hacen ajustes a la hipótesis.