Universidad Politécnica de Tlaxcala

José Miguel Alvarado Molina

Ángel Gabriel Buendía López

Ángel Carretero Quechol

Sara María Romero Blancarte

Metztli Xicohtencatl Romero

Ingeniería en Tecnologías de la Información

Ing. Ma. Guadalupe Tecuapacho Nahuatlato

Ing. María Luisa Xochihua Mendieta

10 de agosto 20225
 ÍNDICE

INTRODUCCIÓN....................................................................................................3

DESARROLLO...................................................................................................3

Descripción de la temática del Dataset..................................................3

Modelo de BD.....................................................................................3

Proceso ETL................................................................................... 3

Diseño de cubo Olap...................................................................3

 RESUMEN

El presente trabajo se enfoca en la implementación de un proyecto de inteligencia

de negocios (BI) que analiza datos operacionales de una aerolínea. Este análisis
es crucial en el sector de las aerolíneas para tomar decisiones informadas y
mejorar la eficiencia operativa. El proyecto utiliza un modelo de base de datos con
una topología de copo de nieve, lo que permite una organización detallada y
optimizada de la información.

La base de datos se construyó con una tabla de hechos central (flights) que
recopila datos de vuelos, y varias tablas de dimensiones que contienen
información sobre aviones, aeropuertos, asientos y reservas. Estas tablas están
normalizadas y divididas para evitar redundancia y mejorar la eficiencia del
almacenamiento. El proceso ETL (Extracción, Transformación y Carga) fue
fundamental para trasladar los datos desde una base de datos transaccional en
SQL Server hacia una base de datos analítica.

El análisis final de los datos se realizó utilizando cubos OLAP, que permitieron
examinar indicadores clave de desempeño (KPI) desde diversas perspectivas,
como la ocupación de vuelos y la eficiencia en el uso de la flota. Los cubos
proporcionaron insights valiosos que facilitaron la toma de decisiones estratégicas,
ayudando a la aerolínea a optimizar sus operaciones y mejorar su competitividad.

Este proyecto demuestra cómo el uso de inteligencia de negocios y un modelo de

base de datos bien estructurado puede transformar grandes volúmenes de datos
en información valiosa para la gestión y toma de decisiones en la industria de las
aerolíneas.

INTRODUCCIÓN

La competencia en el sector aéreo obliga a las aerolíneas a adoptar herramientas

tecnológicas avanzadas para mantenerse competitivas. La inteligencia de
negocios (BI) se ha consolidado como una herramienta esencial en este contexto,
permitiendo a las aerolíneas analizar grandes volúmenes de datos para optimizar
la toma de decisiones y mejorar la eficiencia operativa.

Este proyecto se centra en el desarrollo de un sistema de BI que permite a una

aerolínea analizar y optimizar sus operaciones diarias. Utilizando un dataset que
incluye información sobre vuelos, reservas, aviones y aeropuertos, se diseñó un
modelo de base de datos bajo la topología de copo de nieve. Este tipo de
modelado permite la normalización de las tablas de dimensiones, lo que reduce la
redundancia de datos y mejora la eficiencia en el almacenamiento y la ejecución
de consultas.

El proceso comenzó con la construcción del modelo de datos, identificando una

tabla de hechos (flights) que centraliza los datos de cada vuelo y varias tablas de
dimensiones que proporcionan detalles adicionales. La normalización de las
dimensiones en tablas específicas facilita un análisis más detallado y eficiente.

Una vez diseñado el modelo, se implementó un proceso ETL para extraer los
datos desde un servidor SQL Server, transformarlos para su análisis y cargarlos
en la nueva base de datos analítica. Este proceso incluyó operaciones de limpieza
y normalización, asegurando la calidad y consistencia de los datos.

Finalmente, se diseñaron cubos OLAP que permitieron el análisis multidimensional

de los datos, enfocándose en KPI críticos como la ocupación de vuelos y la
eficiencia en el uso de la flota de aviones. Estos cubos proporcionan a la aerolínea
la capacidad de desglosar los datos en múltiples dimensiones, facilitando la toma
de decisiones estratégicas basadas en datos.

Este proyecto resalta la importancia de un modelo de datos bien estructurado y un

proceso ETL robusto en la implementación de soluciones de inteligencia de
negocios.

DESARROLLO

Descripción de la temática del Dataset

Este dataset recoge todo lo relacionado con los vuelos en la región de

Euroasia, desde cómo se hacen las reservas hasta los detalles de los vuelos y las
aeronaves. Incluye varias tablas conectadas entre sí que cubren diferentes
aspectos: quién reserva, qué boleto compra, dónde se sienta, y cuándo despega y
aterriza cada vuelo. En el dataset, se encuentra información sobre las reservas,
los boletos que se emiten, los asientos que se asignan, y los detalles técnicos de
los vuelos, como los aeropuertos de salida y llegada, los horarios y el estado del
vuelo. También incluye datos sobre las aeronaves, como el modelo y la capacidad
de vuelo, y una tabla que conecta los boletos con los vuelos, lo cual es útil para
gestionar vuelos con escalas o tarifas especiales.
Este dataset fue elegido ya que es perfecto para analizar cómo funcionan
las aerolíneas en Euroasia, cómo se ocupan los vuelos, y qué rutas son más
eficientes. Es una herramienta muy útil para entender mejor el funcionamiento de
los vuelos en esta región y para mejorar las operaciones aéreas.
 MODELO DE BD

El modelo de base de datos desarrollado sigue una topología de copo de nieve

(snowflake), en la cual las tablas de dimensiones están normalizadas. La tabla de

hechos central es flights, que recoge eventos específicos de cada vuelo, como las

horas de salida y llegada, los códigos de aeropuerto, y los aviones utilizados.

Las dimensiones están distribuidas en varias tablas relacionadas entre sí, como
aircrafts_data, airports_data, seats, y bookings. Por ejemplo, la tabla seats está
vinculada a aircrafts_data, reflejando una estructura en la que los datos se
subdividen para eliminar redundancias y optimizar el almacenamiento.

La estructura en copo de nieve permite a la aerolínea realizar consultas complejas

y obtener insights detallados sobre su operativa, manteniendo la consistencia y
calidad de los datos.
 PROCESO ETL

Importación de los datos desde SQL SERVER a Power BI:

Para extraer los datos de SQL server es necesario primero tener la base de datos
de donde obtendremos los datos, en este caso primero lo teníamos en Mysql asi
que realizamos la importación de esta base de datos.

Primero creamos una nueva base de datos en SQL SERVER, después usaremos
el programa de SQL SERVER MIGRATION para conectarnos a MySQL, lo primero
es elegir la versión de SQL SERVER, en nuestro caso fue el 2022, después
presionamos conectar a MySQL e ingresamos los datos, como sería el driver de
SQL , en este caso en el nombre usaremos el LocalHost ya que es una base de
datos local , el puerto será el mismo que usamos en XAMPP y nuestro usuario es
root. Después de llenar los datos presionáremos en aceptar y buscaremos la base
de datos que queremos importar en nuestro caso se llama Travel , para
conectarnos a SQL SERVER ingresamos el nombre del servidor en este caso será
el nombre de nuestro equipo , una vez estamos conectados a MySQL y a SQL
SERVER presionamos clic derecho en las opciones de SQL SERVER y elegimos
la opción de sincronizar con esta base de datos y en la parte de MySQL elegimos
la opción de convertir Schema, presionamos nuevamente en sincronizar con esta
base de datos y listo ya tenemos los datos importados en SQL SERVER.

Ahora para conectarse con Power BI es necesario abrir el software de

power bi, elegir un archivo en blanco y en la parte de obtener datos elegiremos
SQL SERVER , donde pondremos el nombre de nuestra base de datos ,
presionamos en aceptar y se nos mostraran las tablas y sus datos a convertir, en
este caso elegimos todos las tablas de la base de datos Travel.
 Transformación:
Una vez elegidas las tablas de la base de datos Travel , presionamos en
transformar datos , lo primero que cambiaremos será el nombre de las columnas ,
ya que todas tienen un nombre en inglés, este proceso se hará con todas las
tablas , después se cambiara el tipo de dato de las tablas amount o cantidad como
ahora se conoce , ya que tiene el tipo de dato Texto y se cambiara a Numero
entero , en la tabla de “flights” se filtraron los datos para que no se muestren los
datos NULL en las columnas “Salida actual” y “Salida Programada” , en la tabla
“Airports data” se eliminó la columna coordenadas , ya que las coordenadas no
eran correctas con la ubicación de los aeropuertos, en la tabla “seats” y “tickets
flights” se filtró para que no apareciera el tipo de dato “EN BLANCO” , posterior a
todos estos cambios se presionó el botón “Aplicar y Cerrar”

Ahora para realizar las gráficas se realizaron los siguientes pasos y se tomaron en
cuenta los siguientes criterios:

Gráfico 1 Mapa

En la tabla “airports data” contamos con la zona horaria de los aeropuertos, así
que elegimos la ubicación de los aeropuertos en base a su zona horario y la
leyenda que se le coloco fue el nombre de los aeropuertos
Gráfico 2 Gráfico de columnas Apiladas

En este gráfico se tomó en cuenta la fecha de reserva por Mes y Año junto al ID
del vuelo para realizar un recuento de los boletos vendidos en los modelos de los
aviones, es por ello que se colocó en la leyenda el Código de los aviones

Gráfico 3 Gráfico de columnas Apiladas

En este grafico se tomó en cuenta las condiciones de tarifa en base a los boletos
comprados tomando como base las Salidas programadas por Dia, Mes y Año,
para así saber cuántos boletos se compran en la categoría “Bussisness”,
“Economy” y “Confort”.
Gráfico 4 Gráfico de anillos

En este grafico se realizó un recuento del número de asiento y su id en base a la

tarifa, para saber que asientos son más comunes en las categorías “Bussisness”,
“Economy” y “Confort”.

Gráfico 5 Gráfico de áreas apiladas

En este grafico se realizó un conteo del modelo de los aviones en base a que tipo
de vuelo se realiza en las diferentes categorías de “Bussisness”, “Economy” y
“Confort”.
Se agregaron dos segmentaciones de datos con la siguiente finalidad
Segmentación de datos 1

En la primera segmentación se encuentra el código de los aviones para así saber

cuántos boletos, vuelos y que tipo de vuelos hace en base a las categorías
“Bussisness”, “Economy” y “Confort”.

Segmentación de datos 2

En esta segmentación de datos se usa para buscar el tipo de categoría

“Bussisness”, “Economy” o “Confort”, para así saber cuántos vuelos se hacen de
estas categorías, cuantos boletos venden , los asientos asignados para las
categorías y la cantidad de vuelos que realizan estas categorías
 DISEÑO DE CUBO OLAP

1. KPI: Ocupación de Vuelos

Descripción del KPI: La ocupación de vuelos mide el porcentaje de asientos

ocupados en cada vuelo. Este KPI es crucial para evaluar la eficiencia en el uso
de la flota y la demanda en diferentes rutas.

- Dimensiones
Aircraft Model (Modelo del Avión): Permite analizar la ocupación en función de
diferentes modelos de aviones.
-Airport City (Ciudad del Aeropuerto): Facilita el análisis de la ocupación según
las ciudades de origen y destino.
-Flight Date (Fecha del Vuelo): Permite desglosar la ocupación por fechas
específicas o períodos de tiempo.
-Fare Conditions (Condiciones Tarifarias): Proporciona una vista de la ocupación
según las diferentes clases de tarifa (económica, business, etc.).

-Celdas: Las celdas del cubo representan la ocupación de los asientos en los
vuelos, es decir, el número de asientos ocupados frente al total de asientos
disponibles.

-Medidas:
Total Passengers (Total de Pasajeros): Cuenta el número de pasajeros que han
abordado cada vuelo.
Total Seats (Total de Asientos): Calcula el número total de asientos disponibles en
cada vuelo.
Seat Occupancy Rate (Tasa de Ocupación de Asientos): Se calcula dividiendo el
número de pasajeros por el número total de asientos y multiplicando por 100 para
obtener un porcentaje.
Ejemplo de Consulta: "Mostrar la ocupación promedio de los vuelos operados por
el modelo de avión 'Boeing 737' en la ruta de Ciudad de México a Nueva York
durante el primer trimestre de 2023."

---
2. KPI: **Ingresos por Pasajero**

Descripción del KPI: Este KPI mide el ingreso promedio generado por cada
pasajero, lo cual es fundamental para evaluar la rentabilidad de las rutas y las
estrategias de precios.
Dimensiones:
-Passenger ID (ID del Pasajero): Permite analizar los ingresos generados por
cada pasajero.
-Flight Route (Ruta del Vuelo): Desglosa los ingresos por las diferentes rutas
operadas por la aerolínea.
-Booking Date (Fecha de la Reserva): Analiza los ingresos según la fecha en que
se realizó la reserva.
-Fare Conditions (Condiciones Tarifarias): Proporciona una vista de los ingresos
según las diferentes clases de tarifa.
-Celdas: Las celdas del cubo representan el ingreso generado por cada pasajero
en cada vuelo.

- **Medidas**:
- **Revenue (Ingresos)**: Suma los ingresos generados por cada pasajero en un
vuelo específico.
- **Passenger Count (Cantidad de Pasajeros)**: Cuenta el número de pasajeros
por vuelo.
- **Revenue per Passenger (Ingreso por Pasajero)**: Calcula el ingreso promedio
por pasajero dividiendo los ingresos totales por la cantidad de pasajeros.
**Ejemplo de Consulta**: "Calcular el ingreso promedio por pasajero en vuelos
internacionales desde enero a marzo de 2023, segmentado por clases tarifarias."

---

#### 3. KPI: **Puntualidad de Vuelos**

**Descripción del KPI**: La puntualidad de vuelos es un KPI que mide la

cantidad de vuelos que salen y llegan a tiempo. Es crucial para evaluar la
eficiencia operativa de la aerolínea.

- **Dimensiones**:
- **Flight Number (Número de Vuelo)**: Permite analizar la puntualidad
según el número de vuelo.
- **Departure Airport (Aeropuerto de Salida)**: Analiza la puntualidad en
función del aeropuerto de origen.
- **Arrival Airport (Aeropuerto de Llegada)**: Analiza la puntualidad en
función del aeropuerto de destino.
- **Scheduled Date (Fecha Programada)**: Desglosa la puntualidad por la
fecha programada del vuelo.

- **Celdas**: Las celdas del cubo indican si un vuelo salió y/o llegó a
tiempo, con un posible indicador binario (1 = Puntual, 0 = No Puntual).

- **Medidas**:
- **On-Time Departure (Salida a Tiempo)**: Cuenta el número de vuelos
que salieron a tiempo.
- **On-Time Arrival (Llegada a Tiempo)**: Cuenta el número de vuelos que
llegaron a tiempo.
- **Punctuality Rate (Tasa de Puntualidad)**: Se calcula dividiendo el
número de vuelos puntuales por el número total de vuelos programados.
**Ejemplo de Consulta**: "Determinar la tasa de puntualidad de los vuelos
saliendo de Aeropuerto Internacional de Los Ángeles en el segundo
trimestre de 2023."

---

#### 4. KPI: **Costo Operativo por Vuelo**

**Descripción del KPI**: Este KPI mide el costo promedio asociado a la

operación de cada vuelo, lo cual es esencial para evaluar la rentabilidad y
eficiencia de la aerolínea.

- **Dimensiones**:
- **Aircraft Model (Modelo del Avión)**: Desglosa los costos operativos
según el modelo de avión.
- **Flight Route (Ruta del Vuelo)**: Permite el análisis de costos por
diferentes rutas.
- **Flight Date (Fecha del Vuelo)**: Analiza los costos operativos en
función de la fecha del vuelo.
- **Fuel Consumption (Consumo de Combustible)**: Relaciona el costo
operativo con el consumo de combustible.

- **Celdas**: Las celdas representan el costo total asociado a la operación

de un vuelo, desglosado por las diferentes dimensiones.

- **Medidas**:
- **Total Operating Cost (Costo Operativo Total)**: Suma todos los costos
operativos asociados a un vuelo.
- **Cost per Flight (Costo por Vuelo)**: Calcula el costo promedio por cada
vuelo.
 - **Fuel Cost (Costo de Combustible)**: Analiza la parte del costo total
atribuible al consumo de combustible.

**Ejemplo de Consulta**: "Evaluar el costo operativo promedio por vuelo en

la ruta Nueva York-Londres durante 2023, segmentado por modelo de
avión."

---

Estos ejemplos de diseño de cubos OLAP muestran cómo cada KPI puede
ser analizado desde diferentes perspectivas mediante la configuración de
dimensiones, celdas y medidas adecuadas. Este enfoque permite a la
aerolínea realizar análisis detallados que apoyen la toma de decisiones
estratégicas.

Si necesitas más detalles o ajustes, estaré encantada de ayudarte.