Transformando datos en conocimiento: Análisis de un restaurante con SQL y Power BI

Recientemente, participé en una entrevista de trabajo para integrar el equipo Dataviz – BI de una gran empresa. Durante la conversación, me plantearon el caso de uso de cómo operarían los datos del funcionamiento de un restaurante, cómo estructurarlos, analizarlos y visualizarlos. Creo que mi solución fue demasiado práctica y no técnica, afortunadamente mi entrevistador me guió para encarrilar la explicación a una solución que aportara al negocio. Por esto, decidí crear un proyecto que involucre la creación de un Data Warehouse utilizando el modelo de esquema en estrella y el análisis de datos mediante SQL y Power BI. Aquí te cuento los pasos que seguí para completar este proyecto, desde el diseño del esquema en estrella hasta la creación de un dashboard interactivo en Power BI.

Introducción

El proyecto busca diseñar un Data Warehouse para un restaurante utilizando el esquema en estrella de modelado dimensional. Este esquema facilitará la manipulación y el análisis de datos operacionales del restaurante, permitiendo la generación de informes y análisis de rendimiento mediante consultas SQL en un principio. Luego llevaremos los datos a Power BI para crear un dashboard para su análisis y distribución a otros interesados.

Objetivos del Proyecto

Diseñar un esquema en estrella para el almacén de datos del restaurante.
Implementar las tablas necesarias en una base de datos SQL.
Poblar las tablas con datos simulados.
Realizar consultas SQL para analizar diversos aspectos del negocio.
Exportar la base de datos en formato .csv para ser importada en Power BI.
Crear un dashboard en Power BI para su análisis.
Conclusiones.

Diseño del Esquema en Estrella

El esquema en estrella es una técnica de modelado dimensional utilizada en la construcción de almacenes de datos. Es uno de los métodos más populares debido a su simplicidad y eficiencia en la organización de datos para análisis y consultas.

El primer paso del proyecto fue diseñar un esquema en estrella para el almacén de datos «Data Warehouse» del restaurante. Un esquema en estrella se caracteriza por tener una tabla central llamada «tabla de hechos» que contiene los datos transaccionales, y varias «tablas de dimensiones» que contienen los atributos descriptivos relacionados.

Para este proyecto, diseñé el siguiente esquema:

Tabla de Hechos: Sales_Fact
- Sales_ID (clave primaria)
- Customer_ID (clave foránea)
- Product_ID (clave foránea)
- Location_ID (clave foránea)
- Date_ID (clave foránea)
- Quantity_Sold
- Total_Amount
- Cost
Tablas de Dimensiones:
- Customer_Dim
  - Customer_ID (clave primaria)
  - Customer_Name
  - Customer_Email
- Product_Dim
  - Product_ID (clave primaria)
  - Product_Name
  - Product_Category
- Location_Dim
  - Location_ID (clave primaria)
  - Location_Name
  - Location_City
- Date_Dim (clave primaria)
  - Date_ID (clave primaria)
  - Date
  - Day
  - Month
  - Year

Ventajas del Esquema en Estrella

Simplicidad: La estructura es fácil de entender y navegar, tanto para desarrolladores como para usuarios finales.
Rendimiento de Consulta: Las consultas en un esquema en estrella suelen ser rápidas porque las relaciones entre la tabla de hechos y las tablas de dimensiones están simplificadas.
Escalabilidad: Es fácil de expandir agregando más dimensiones o hechos sin reorganizar toda la estructura.

Desventajas del Esquema en Estrella

Redundancia de Datos: Las tablas de dimensiones pueden contener datos redundantes, lo que puede aumentar el espacio de almacenamiento.
Mantenimiento: La redundancia y la duplicación de datos pueden hacer que el mantenimiento sea más complicado.

Implementación de las tablas en SQL

Después de diseñar el esquema en estrella, procedí a implementar las tablas en una base de datos SQL. Utilicé PostgreSQL para este propósito. Aquí está el código SQL para crear las tablas:

-- Crear tabla de hechos
CREATE TABLE Sales_Fact (
    Sales_ID SERIAL PRIMARY KEY,
    Customer_ID INT,
    Product_ID INT,
    Location_ID INT,
    Date_ID INT,
    Quantity_Sold INT,
    Total_Amount DECIMAL(10, 2),
    Cost DECIMAL(10, 2)
);

-- Crear tablas de dimensiones
CREATE TABLE Customer_Dim (
    Customer_ID SERIAL PRIMARY KEY,
    Customer_Name VARCHAR(100),
    Customer_Email VARCHAR(100)
);

CREATE TABLE Product_Dim (
    Product_ID SERIAL PRIMARY KEY,
    Product_Name VARCHAR(100),
    Product_Category VARCHAR(100)
);

CREATE TABLE Location_Dim (
    Location_ID SERIAL PRIMARY KEY,
    Location_Name VARCHAR(100),
    Location_City VARCHAR(100)
);

CREATE TABLE Date_Dim (
    Date_ID SERIAL PRIMARY KEY,
    Date DATE,
    Day INT,
    Month INT,
    Year INT
);

Poblar las tablas con datos simulados

Para simular datos en las tablas, utilicé la función RANDOM() de PostgreSQL. A continuación, se muestra un ejemplo de cómo inserté los datos simulados en la tabla de hechos:

-- Insertar datos en Date_Dim
INSERT INTO Date_Dim (Date_ID, Date, Day, Month, Quarter, Year) VALUES
(1, '2023-01-01', 1, 1, 1, 2023),
(2, '2023-01-02', 2, 1, 1, 2023);

-- Insertar datos en Product_Dim
INSERT INTO Product_Dim (Product_ID, Product_Name, Category, Price) VALUES
(1, 'Burger', 'Food', 5.99),
(2, 'Fries', 'Food', 2.99);

-- Insertar datos en Customer_Dim
INSERT INTO Customer_Dim (Customer_ID, Customer_Name, Gender, Age, City, Country) VALUES
(1, 'John Doe', 'M', 30, 'New York', 'USA'),
(2, 'Jane Smith', 'F', 25, 'Los Angeles', 'USA');

-- Insertar datos en Location_Dim
INSERT INTO Location_Dim (Location_ID, Store_Name, City, Region, Country) VALUES
(1, 'Main Street', 'New York', 'Northeast', 'USA'),
(2, 'Second Avenue', 'Los Angeles', 'West', 'USA');

-- Insertar datos en Sales_Fact
INSERT INTO Sales_Fact (Sale_ID, Date_ID, Product_ID, Customer_ID, Location_ID, Quantity_Sold, Total_Amount) VALUES
(1, 1, 1, 1, 1, 2, 11.98),
(2, 2, 2, 2, 2, 3, 8.97);

Realizar consultas SQL para el análisis

Una vez que las tablas estaban pobladas, ejecuté varias consultas SQL para analizar diferentes aspectos del negocio. Por ejemplo:

-- Análisis de Ventas por Cliente
SELECT C.Customer_Name, SUM(S.Total_Amount) AS Total_Spent
FROM Sales_Fact S
JOIN Customer_Dim C ON S.Customer_ID = C.Customer_ID
GROUP BY C.Customer_Name
ORDER BY Total_Spent DESC;

-- Total de Ventas por Producto
SELECT P.Product_Name, SUM(S.Quantity_Sold) AS Total_Quantity, SUM(S.Total_Amount) AS Total_Sales FROM Sales_Fact S JOIN Product_Dim P ON S.Product_ID = P.Product_ID GROUP BY P.Product_Name;

-- Ventas Totales por Mes
SELECT D.Month, D.Year, SUM(S.Total_Amount) AS Total_Sales
FROM Sales_Fact S
JOIN Date_Dim D ON S.Date_ID = D.Date_ID
GROUP BY D.Month, D.Year
ORDER BY D.Year, D.Month;

-- Ventas por Ubicación
SELECT L.Store_Name, L.City, SUM(S.Total_Amount) AS Total_Sales
FROM Sales_Fact S
JOIN Location_Dim L ON S.Location_ID = L.Location_ID
GROUP BY L.Store_Name, L.City;

El uso del esquema en estrella facilita la organización y análisis de datos en un almacén de datos, permitiendo la generación de consultas SQL eficientes. Esto demuestra cómo se puede estructurar y manipular datos de un restaurante para obtener información valiosa que apoye la toma de decisiones y mejore el rendimiento del negocio.

Exportar la Base de Datos a .csv

Para importar los datos en Power BI, primero exporté las tablas de la base de datos a archivos .csv. Utilicé las siguientes instrucciones en PostgreSQL:

COPY Sales_Fact TO '/path/to/sales_fact.csv' DELIMITER ',' CSV HEADER;
COPY Customer_Dim TO '/path/to/customer_dim.csv' DELIMITER ',' CSV HEADER;
COPY Product_Dim TO '/path/to/product_dim.csv' DELIMITER ',' CSV HEADER;
COPY Location_Dim TO '/path/to/location_dim.csv' DELIMITER ',' CSV HEADER;
COPY Date_Dim TO '/path/to/date_dim.csv' DELIMITER ',' CSV HEADER;

Si su PostgreSQL tiene problemas por versiones o permisos, una solución es hacerlo por Consola. Deberás instalar PSQL:

➜ psql -h localhost -d restaurants -U postgres

Ingresar tu User y Password y ya dentro de la Base de datos «Restaurants» hacer el query por cada tabla en SQL:

restaurants=# \copy customer_dim TO '/Users/javierladino/Documents/Javier/sql/customer_dim.csv' DELIMITER ',' CSV HEADER;

Creación del Dashboard en Power BI

Para un restaurante, el objetivo del dashboard es proporcionar información crítica que ayude a tomar decisiones estratégicas, optimizar operaciones y mejorar la satisfacción del cliente. Basándonos en el modelo estrella y los datos simulados, definimos las medidas y visualizaciones que serían las más importantes para incluir en un dashboard de Power BI.

Importamos los archivos .csv y configuramos las relaciones entre las tablas según el esquema en estrella. Luego, creamos diversas visualizaciones para comprobar el uso de los datos, tales como:

Ventas Totales por Cliente: Un gráfico de barras que muestra las ventas totales por cada cliente.
Ventas por Categoría de Producto: Un gráfico de pastel que muestra la distribución de las ventas por categoría de producto.
Ventas por Ubicación: Un mapa que muestra las ventas en diferentes ubicaciones.
Tendencias de Ventas por Mes: Un gráfico de líneas que muestra la tendencia de ventas a lo largo del tiempo.

Pero, qué preguntas podría hacerle el responsable del restaurante al analista de datos para conocer las principales medidas de su negocio ? (Este punto podría estar al inicio, pero aquí es dónde creo que toma validez).

El responsable del restaurante puede hacer una serie de preguntas para comprender mejor las principales medidas de su negocio y tomar decisiones informadas. Algunas preguntas clave que el propietario podría plantear, seguido del query en DAX (Expresiones de análisis de datos de Power BI) para crear las medidas o columnas respectivamente:

Preguntas sobre Ventas y Rendimiento

¿Cuáles son las ventas totales del restaurante en un período específico (mes, trimestre, año)?
- Medida: Ventas Totales (SUM(Sales_Fact[Total_Amount]))
¿Cuál es la ganancia neta del restaurante después de restar los costos?
- Medida: Ganancia (SUM(Sales_Fact[Total_Amount]) - SUM(Sales_Fact[Cost]))
¿Cómo varían las ventas a lo largo del tiempo (día, semana, mes)?
- Visualización: Gráfica de Líneas para Ventas Totales por Mes
¿Cuál es el margen de ganancia del restaurante?
- Medida: Margen de Ganancia (DIVIDE([Ganancia], [Ventas_Totales], 0))

Preguntas sobre Productos y Categorías

¿Cuáles son los productos más vendidos?
- Medida: Ventas por Producto (SUM(Sales_Fact[Total_Amount]) agrupado por Product_Dim[Product_Name])
¿Qué categorías de productos generan más ingresos?
- Medida: Ventas por Categoría (SUM(Sales_Fact[Total_Amount]) agrupado por Product_Dim[Category])
¿Cuántos productos se venden en promedio por cliente?
- Medida: Promedio de Productos Vendidos por Cliente (AVERAGE(Sales_Fact[Quantity_Sold]))

Preguntas sobre Clientes

¿Cuál es el consumo total de cada cliente?
- Medida/Columna: Total de Consumo por Cliente (SUM(Sales_Fact[Total_Amount]) filtrado por Customer_ID)
¿Cuál es el ticket promedio por cliente?
- Medida: Promedio de Venta por Cliente (AVERAGE(Sales_Fact[Total_Amount]))
¿Qué clientes son los que más gastan?
- Visualización: Tabla o Gráfica de Barras con Customer_Name y Total_Consumo_Por_Cliente

Preguntas sobre Operaciones y Eficiencia

¿En qué horas del día se generan más ventas?
- Medida: Ventas por Hora (SUM(Sales_Fact[Total_Amount]) agrupado por Date_Dim[Hour])
¿Cuál es el desempeño de ventas por cada ubicación del restaurante (si hay varias sucursales)?
- Medida: Ventas por Ubicación (SUM(Sales_Fact[Total_Amount]) agrupado por Location_Dim[Location_Name])
¿Qué días de la semana tienen más ventas?
- Medida: Ventas por Día de la Semana (SUM(Sales_Fact[Total_Amount]) agrupado por Date_Dim[DayOfWeek])

Preguntas sobre Promociones y Descuentos

¿Cómo afectan las promociones a las ventas?
- Medida: Comparar Ventas durante periodos de promoción vs periodos sin promoción
¿Qué descuentos generan más ventas?
- Medida: Ventas por Tipo de Descuento (SUM(Sales_Fact[Total_Amount]) agrupado por Promotion_Dim[Promotion_Type])

Ejemplos de Preguntas Detalladas

«¿Cuál ha sido el producto más vendido en el último trimestre y cuánto ha generado en ventas?»
«¿Cuál es el margen de ganancia promedio de nuestros platillos principales?»
«¿Cuáles son nuestros clientes más valiosos y cuánto han gastado en total?»
«¿Qué días de la semana debemos enfocarnos más en promociones para aumentar las ventas?»
«¿Cómo han variado nuestras ventas durante las diferentes estaciones del año?»
«¿Qué ubicación del restaurante (si hay múltiples) tiene el mayor ingreso y cuál el menor?»
«¿Cuál es el impacto de nuestras promociones en las ventas totales y en el margen de ganancia?»
«¿Cuáles son los tiempos pico de ventas durante el día?»
«¿Qué porcentaje de nuestras ventas totales proviene de nuestros productos más vendidos?»
«¿Cuál es el promedio de gasto por cliente y cómo varía entre clientes frecuentes y nuevos?»

Al incluir estas medidas y visualizaciones en tu dashboard de Power BI, obtendrás una visión integral del rendimiento del restaurante, cubriendo aspectos como ventas, ganancias, márgenes de ganancia, y tendencias a lo largo del tiempo. Te permitirán identificar oportunidades de mejora, monitorizar el rendimiento y tomar decisiones informadas para el crecimiento del negocio.

Lo que hice fue crear un Dashboard en 5 secciones: Home (Dashboard), Sales, Products, Customers y Operations. Algunas métricas o valores se repiten, pero lo hice intencionalmente para mejorar la experiencia del usuario al filtrar los elementos en Power BI.

restaurants Descarga

Podrás interactuar con el dashboard iniciando sesión en tu cuenta de Power BI:

Conclusiones

Las respuestas a estas preguntas proporcionarán al propietario una visión clara de las áreas clave del negocio, ayudándole a tomar decisiones estratégicas para mejorar las operaciones, optimizar el menú, enfocar las promociones y aumentar la rentabilidad. Estas preguntas también ayudan a identificar tendencias y patrones en el comportamiento de los clientes y el rendimiento del restaurante.

En lo personal, este proyecto me permitió demostrar mis habilidades en el diseño e implementación de un esquema en estrella, el uso de SQL para manipular y analizar datos, y la creación de visualizaciones efectivas en Power BI. Las principales conclusiones incluyen por ejemplo:

- Identificación de Clientes Clave: Los datos mostraron qué clientes son los que más contribuyen a las ventas totales.

- Productos Más Vendidos: Identificar las categorías de productos más populares ayudó a tomar decisiones informadas sobre el inventario.

- Rendimiento por Ubicación: Analizar las ventas por ubicación reveló las áreas con mejor desempeño.

En resumen, este proyecto no solo mostró mi capacidad técnica, sino que también proporcionó información valiosa para la gestión del restaurante. Hacer este tipo de proyectos mezclando varias herramientas, cometiendo errores, investigando y apoyarse en todo recurso es la base para seguir avanzando.

Espero les guste y puedan replicarlo para practicar, y no duden en comentarme qué les parece, si hay errores o descubren nuevos caminos para el análisis y visualización de datos. Saludos!