Construcción completa de un almacén de datos analítico sobre SQL Server: extracción desde una base operativa transaccional, carga en un modelo estrella de cinco dimensiones y una tabla de hechos, y un modelo de regresión entrenado sobre el warehouse resultante.
Resultado del modelo: R² de 0.9092 en prueba, con validación cruzada de 0.9073 ± 0.0048.
SuperHerbolario ──(ETL)──> DW_Super ──(consulta)──> Modelo predictivo
base operativa modelo estrella scikit-learn
normalizada desnormalizado
La base operativa SuperHerbolario está normalizada para transacciones. El warehouse DW_Super
está desnormalizado para consultas analíticas. El ETL hace el puente.
| Tabla | Tipo | Registros |
|---|---|---|
DIM_PRODUCTO |
Dimensión | 47 |
DIM_CLIENTE |
Dimensión | 162 |
DIM_VENDEDOR |
Dimensión | 50 |
DIM_SUCURSAL |
Dimensión | 23 |
DIM_TIEMPO |
Dimensión | 134 |
FT_VENTA |
Hechos | ~40,000 |
La dimensión de tiempo se construye por separado a partir de las fechas de venta — descomponiendo cada fecha en año, mes, trimestre y día para permitir agregaciones temporales sin funciones de fecha en cada consulta.
Dos cuadernos cubren el proceso:
etl_dimensiones.ipynb — carga las cinco dimensiones y la tabla de hechos. Extrae con
pandas.read_sql, renombra columnas para que coincidan con el esquema destino y carga con
to_sql. Usa if_exists='replace' para permitir recargas idempotentes durante el desarrollo.
etl_dim_producto.ipynb — versión documentada paso a paso del flujo para una sola dimensión,
útil como referencia del patrón.
Un detalle del proyecto que vale la pena señalar: las dos bases se conectan de forma distinta.
# Base operativa — autenticación SQL con usuario y contraseña
mssql+pyodbc://{USER}:{PASSWORD}@{HOST}/SuperHerbolario?driver=...
# Warehouse — autenticación integrada de Windows
mssql+pyodbc://@{HOST}/DW_Super?driver=...&trusted_connection=yesRefleja un escenario real: la base operativa la administra un tercero y da credenciales explícitas; el warehouse es interno y usa la identidad del dominio.
prediccion_ventas.ipynb reconstruye la vista analítica completa con cinco merge sobre la tabla
de hechos, dejando un dataset de 40,005 filas y 22 columnas.
Objetivo: monto_total de la venta.
Variables:
| Tipo | Variables |
|---|---|
| Numéricas | cantidad_vendida, horas_capacitacion |
| Categóricas | nombre_producto, nombre_sucursal, nombre_vendedor, capacitacion_vendedor |
Pipeline: ColumnTransformer con passthrough para numéricas y OneHotEncoder con
handle_unknown='ignore' para categóricas, seguido de LinearRegression. Todo dentro de un
Pipeline de scikit-learn, de modo que el preprocesamiento se ajusta solo con los datos de
entrenamiento y no hay fuga entre particiones.
| Métrica | Valor |
|---|---|
| R² entrenamiento | 0.9094 |
| R² prueba | 0.9092 |
| R² validación cruzada (5-fold) | 0.9073 |
| Desviación estándar de la CV | 0.0048 |
La diferencia entre entrenamiento y prueba es de 0.0002 y la desviación de la validación cruzada es de medio punto porcentual. No hay sobreajuste: el modelo generaliza de forma estable entre particiones.
Un R² de 0.91 se ve muy bien, pero conviene entender de dónde sale. monto_total es
esencialmente cantidad_vendida × precio_unitario, y el precio unitario está determinado por el
producto — que el modelo recibe codificado. Buena parte de la varianza explicada es entonces
estructural, no un descubrimiento sobre el negocio.
La prueba está en los errores individuales: la tabla comparativa de predicciones muestra errores relativos que rondan el 20% en registros concretos, con desviaciones de decenas de miles de pesos. El modelo captura bien la varianza agregada y falla en la predicción puntual, precisamente porque una regresión lineal no puede representar el producto entre cantidad y precio: solo los suma por separado.
Es un buen ejercicio de por qué una sola métrica no basta para evaluar un modelo. Con un modelo de árboles capaz de capturar esa interacción, el error individual bajaría de forma notable.
Python · SQL Server · pyodbc · SQLAlchemy · Pandas · scikit-learn
Necesitas SQL Server (Express o LocalDB sirven) con las dos bases creadas, y el ODBC Driver 17 for SQL Server instalado.
pip install -r requirements.txtConfigura las credenciales de la base operativa por variables de entorno:
export DB_USER=tu_usuario
export DB_PASSWORD=tu_password
export DB_HOST=localhostEjecuta los cuadernos en orden:
etl_dimensiones.ipynb— pueblaDW_Superprediccion_ventas.ipynb— entrena y evalúa el modelo
├── etl_dimensiones.ipynb # ETL completo: 5 dimensiones + tabla de hechos
├── etl_dim_producto.ipynb # El patrón explicado paso a paso
├── prediccion_ventas.ipynb # Modelo de regresión sobre el warehouse
├── docs/
│ ├── modelo_estrella.drawio # Diagrama del esquema dimensional
│ └── modelo_estrella.png
└── requirements.txt
Proyecto desarrollado para la asignatura de Bases de Datos Estructuradas, Licenciatura en Ciencia de Datos, UNAM FES Acatlán. Autor: Arturo Jaramillo Salmerón.