fernandorioseco.es

AdventureWorks 2022 – 360° Management Report

—

por

Fernando
en

Microsoft Power BI Data Analyst (PL-300) – Proyecto Final «Tonificado».

Proyecto de Dashboard 360° para AdventureWorks 2022. Se extrajeron y exploraron los datos con SQL, se limpiaron y transformaron usando Python y Pandas, y finalmente se visualizaron en Power BI. El proyecto abarca todo el proceso ETL: consultas y modelado de datos en SQL, manipulación y preparación de datos en Python, y construcción de visualizaciones interactivas en Power BI para análisis integral de finanzas, ventas, clientes y productos.

El proyecto de curso original solo incluía elaborar un dashboard con datos provenientes de tablas de Excel aportadas por el curso.

Tecnologías utilizadas

  • SQL Server (base transaccional)
  • SQL Server Management Studio + VS Code (extensión MSSQL)
  • Python (pandas, pyodbc, sqlalchemy, seaborn)
  • Power BI Desktop + Power BI Service

Diagrama de ETL del proyecto

1. Exploración profunda de la base de datos

Lo primero y más importante: nunca empieces a extraer datos sin entenderlos al 100 %.

Acciones realizadas:

  • Generé un script SQL individual de exploración para cada una de las 18 tablas relevantes. Cada script incluye:
    • Descripción de las variables
    • Rango de fechas
    • Valores únicos y ejemplos de las columnas clave
    • Identificación de claves primarias y foráneas reales (aunque no estén definidas en la BD)

Como resultado se creó un Diccionario de Datos completo en Excel y un diagrama relacional.

2. Diseño del Modelo Analítico

Definición de un modelo dimensional robusto, siguiendo las mejores prácticas de arquitectura BI. Para garantizar un rendimiento óptimo en Power BI y un análisis consistente, se diseñó un modelo en estrella (Star Schema), manteniendo únicamente algunas jerarquías naturales con forma de snowflake cuando aportaban claridad sin afectar el rendimiento.

Definición de la Granularidad del Hecho

Establecer la granularidad exacta de cada tabla de hechos:

  • Ventas: transacción por línea de pedido.
  • Inventario: snapshot por producto y almacén.
  • Finanzas: nivel contable por periodo.
  • Encuestas: respuesta por cliente.

Definición de Métricas y KPIs Clave

Se seleccionaron los indicadores esenciales para la visión 360°:

  • Ventas Totales (Sales Amount)
  • Coste del Producto (Total Product Cost)
  • Beneficio Bruto (Profit)
  • Margen (%)
  • Tendencias temporales: variaciones y crecimiento frente a periodos anteriores (YOY/MOM)
  • Valor Medio del Pedido (Average Order Value / AOV)
  • Rotación de Inventario y Alertas de Bajo Stock
  • Presupuesto vs Real (indicadores financieros)
  • Efectividad de Promociones

Entregables Generados

Durante esta fase se produjeron los documentos técnicos clave del proyecto:

  • Modelo_Dimensional: esquema visual del modelo en estrella.
  • Documento de Requerimientos Funcionales (.pdf): base formal para validar las necesidades del negocio con cada área.

3. Extracción desde SQL Server

Preparación de datos mediante vistas SQL

Se crearon vistas en SQL Server, segun el documento de Requerimientos, que estructuran y preparan los datos para su posterior procesamiento en Python y análisis en Power BI.

Las vistas incluyen:

  • Claves y relaciones: Se conservaron solo las necesarias para unir hechos y dimensiones sin duplicar información.
  • Atributos descriptivos y demográficos: Se mantuvieron columnas relevantes para análisis y segmentación (edad, género, ingresos, educación, categoría de producto), mientras que la información sensible de clientes se anonimizó o eliminó.
  • Métricas y valores numéricos: Se seleccionaron columnas necesarias para calcular KPIs como ventas, margen, ticket promedio, inventario y cumplimiento de cuotas.

Además, se aplicaron transformaciones básicas dentro de las vistas, como el cálculo de edad a partir de la fecha de nacimiento y la unificación de jerarquías de productos, simplificando la carga y limpieza de datos en Python.

Resultado: Una capa de datos limpia, consistente y segura, lista para análisis avanzado en Python y visualización interactiva en Power BI, cumpliendo los objetivos del dashboard ejecutivo.

4. Limpieza y Transformación de Datos

En esta fase se consolidan los datos de SQL Server y se preparan para su análisis en Power BI, asegurando datasets limpios, consistentes y listos para modelado.

Extracción de Datos

Se crearon cuadernos de Jupyter para extraer vistas SQL a CSV:

  • Conexión a SQL Server mediante SQLAlchemy.
  • Exportación de cada vista a /data/raw, eliminando prefijos vw_ en los nombres.
  • Mensajes de seguimiento en consola para validar éxito o fallo de cada extracción.

Carga y Exploración

Los CSV se cargaron en DataFrames de Pandas para exploración:

  • Inspección de registros y tipos de datos.
  • Identificación de valores nulos e inconsistencias.
  • Funciones reutilizables (CheckData) para revisión rápida de los datasets.

Limpieza y Transformación

Se aplicaron transformaciones consistentes por tabla de hechos:

  • Conversión de tipos, fechas y valores categóricos.
  • Tratamiento de nulos mediante reglas específicas según columna y tipo.
  • Estilo de limpieza coherente con los notebooks iniciales, mostrando resúmenes y primeras filas.

Automatización del ETL

Se desarrollaron scripts para consolidar el flujo:

  1. config.py: define rutas de trabajo y conexión a SQL Server con SQLAlchemy.
  2. etl_pipeline.py: ejecuta automáticamente:
    • Carga consultas SQL.
    • Genera DataFrames.
    • Limpia los datos según reglas definidas.
    • Guarda datasets limpios en /data/processed.
    • Proporciona logs y seguimiento en consola.

Este enfoque permite reproducir todo el proceso con un solo comando, manteniendo la trazabilidad y calidad de los datos para análisis en Power BI.

5. Exportación de Datasets Limpios

Tras la limpieza y transformación, los datasets se preparan para su consumo en Power BI:

  • Se exportan en formato Parquet (recomendado por eficiencia) o CSV comprimido para reducir espacio.
  • Cada tabla del modelo se guarda en un archivo independiente: dimCliente.parquet, factVentas.parquet, etc.
  • Se incluye la fecha de generación en el nombre del archivo o en una tabla de control.
  • Se mantiene un registro maestro de datasets (datasets_control.xlsx) que documenta: nombre del archivo, número de filas y columnas, fecha de exportación.
  • Todos los archivos se almacenan en /data/final/, listos para el análisis.

Este procedimiento asegura que los datos sean consistentes, auditables y fácilmente reutilizables por cualquier miembro del equipo BI.

6. Importación y Modelo en Power BI

La fase final consiste en construir el modelo semántico para análisis y reporting:

  • Se importan todos los archivos Parquet al proyecto de Power BI.
  • Se define un modelo estrella con relaciones claras entre hechos y dimensiones.
  • Columnas técnicas o auxiliares se ocultan para mejorar la experiencia del usuario.
  • Se crean medidas DAX avanzadas para KPIs clave: ventas, margen, cuotas, inventario, etc.
  • Se aplican optimizaciones de rendimiento: agregaciones, filtrado y segmentación, para mejorar la velocidad y la eficiencia.

El resultado es un archivo Proyecto_Ventas.pbix completamente funcional y un documento de Modelo de Datos (Modelo de Datos en Power BI.pdf) con capturas de relaciones, medidas y estructura general, que permite navegar, analizar y tomar decisiones basadas en datos de manera rápida y confiable.