Análisis SNIES Colombia 2014–2024: del Excel disperso al modelo estrella en Power BI

TL;DR: Once archivos Excel del SNIES (2014–2024) con esquemas distintos, 483.327 filas unificadas, 7.193 nombres canónicos limpios de variantes ortográficas y un modelo estrella con 18 dimensiones y 21 medidas DAX en Power BI. Un pipeline que vuelvo a correr en 30 minutos cuando el MEN publique el archivo de 2025.

¿Qué carrera universitaria está creciendo más rápido en Colombia, no en volumen, sino en tasa relativa y de forma estable? La respuesta vive en los datos abiertos del SNIES (Sistema Nacional de Información de la Educación Superior, MinEducación). Once años de información, repartidos en archivos Excel inconsistentes, con encabezados distintos cada año y caracteres mal codificados. Este artículo recorre, de punta a punta, cómo convertí ese material en un modelo estrella de Power BI con 18 dimensiones canónicas y 21 medidas DAX listas para responder preguntas de negocio.

Cifras del proyecto:

• 11 años cubiertos (2014–2024), 22 semestres.
• 483.327 filas unificadas tras pasar 11 archivos por un pipeline de unificación.
• 9.776.932 matriculados acumulados en primer curso.
• 17.411 programas SNIES, 7.193 nombres canónicos (2.790 variantes ortográficas colapsadas).

El problema: 11 archivos, 11 esquemas distintos

El portal del MEN publica cada año un Excel con los matriculados en primer curso por programa, IES, año, semestre y sexo. El detalle es que ningún año tiene la misma estructura:

• Hojas con nombres distintos: Sheet 1, Hoja1, Primer_Curso_2015, 1..
• Filas de encabezado en posiciones distintas: 6, 7, 9, 10, 11.
• De 33 a 41 columnas según el año (los más antiguos no tenían Id_Sector, Id_Nivel ni los códigos CINE).
• Variantes ortográficas en los nombres de columna del mismo concepto.

Sumar pd.concat no funciona. Hace falta un diccionario de mapeo (nombre original × año → nombre canónico) construido a partir de los archivos de «metadatos bases» que el propio MEN publica.

Fase 1 — Adquisición masiva

El portal no permite «descargar todo» filtrando por palabra clave. Resolví la descarga masiva con un script de DevTools Console que busca todos los <a> cuyo title contiene la palabra clave y dispara descargas con un delay para no saturar:

const PALABRAS_CLAVE = ["primer curso"]; // luego: "metadatos bases"
const DELAY_MS = 800;
const enlaces = [...document.querySelectorAll('a[href][title]')]
  .filter(a => PALABRAS_CLAVE.some(kw => a.title.toLowerCase().includes(kw)));

for (const a of enlaces) {
  const link = document.createElement('a');
  link.href = a.href; link.download = a.href.split('/').pop();
  document.body.appendChild(link); link.click(); link.remove();
  await new Promise(r => setTimeout(r, DELAY_MS));
}

Dos pases: uno por matriculados, otro por metadatos.

Fase 2 — Unificación con diccionario

Unifiqué los 11 archivos con un diccionario de mapeo que generé automáticamente desde los archivos de metadatos del propio MEN: cada columna de cada Excel anual se asigna a un nombre canónico sin intervención manual. El resultado fueron 483.327 filas con 44 columnas homogéneas en un único CSV, más tres columnas de auditoría que añadí para rastrear cualquier fila hasta el Excel y la hoja exacta de origen.

Pipeline de cuatro scripts:

extraer_diccionarios.py    → mapeo (nombre original × año) → canónico
inspeccionar_metadatos.py  → audita campos faltantes/sobrantes por año
unificar_data_u_co.py      → 11 Excel → un solo CSV con 44 columnas
auditar_unificado.py       → totales por archivo, conteo de filas, sanity check

Cada fila lleva tres columnas extra (SOURCE_FILE, SOURCE_SHEET, SOURCE_ROW) que sirven como rastro de auditoría — si una métrica se ve rara, se puede regresar al Excel y la fila exacta de origen.

Resultado: 483.327 filas con 44 columnas comunes, listas para análisis.

Fase 3 — La auditoría dimensional que cambió el rumbo

Antes de modelar en Power BI, hice una auditoría sistemática del CSV unificado que reveló que el dato crudo no era comparable entre años: 3.151 códigos SNIES tenían más de un nombre por variantes ortográficas, y la distribución de matriculados era tan sesgada (mediana 9, máximo 10.645) que el crecimiento absoluto favorecía siempre al programa más grande, no al más dinámico. Replantear la métrica y la limpieza dimensional antes del modelado evitó construir un análisis sobre arenas movedizas.

Al revisar el primer notebook descriptivo encontré dos hallazgos que me obligaron a replantear el análisis:

1. Los matriculados crecen de 783K (2014) a 1.06M (2024), pero el conteo de programas únicos sube de 7.724 a 11.820. Hay programas que entran y salen del mercado, así que comparar crecimiento sin filtrar por cobertura completa sería engañoso.
2. Distribución muy sesgada: mediana de 9 matriculados por programa, percentil 99 en 170, máximo 10.645. Un crecimiento absoluto siempre favorecería al gigante. La métrica correcta es CAGR (tasa relativa).

Después vino la sorpresa: descubrí que tildes y mayúsculas estaban contando entidades distintas. Ingeniería y INGENIERIA eran dos programas. Al auditar todas las dimensiones encontré:

Auditoría dimensional: escala del problema

Dimensión	IDs únicos	Nombres únicos	IDs con varias variantes
CÓDIGO SNIES → PROGRAMA	17.411	9.983	3.151
MUNICIPIO PROGRAMA	853	1.547	555
CINE CAMPO DETALLADO	106	207	101

3.151 SNIES con más de un nombre. Sin limpieza dimensional, cualquier ranking sería ruido.

El caso del carácter ¿

Algunos archivos del MEN tenían acentos mal codificados: ADMINISTRACI¿N, BACTERIOLOGÏA, INGENIER¿A. La solución fue una lista de patrones regex de alta confianza:

PATRONES_REPARAR = [
    (re.compile(r"CI¿N\b"),      "CIÓN"),
    (re.compile(r"GESTI¿N"),     "GESTIÓN"),
    (re.compile(r"INGENIER¿A"),  "INGENIERÍA"),
    (re.compile(r"DISE¿O"),      "DISEÑO"),
    (re.compile(r"INFORM¿TIC"),  "INFORMÁTIC"),
    # ~100 más
    (re.compile(r"¿"),           ""),  # fallback: borra ¿ huérfano
]

Para cada par (id, nombre) elegí el nombre con menor «score malo» (menos ¿, menos chars raros), desempate por frecuencia y luego longitud. Después: MAYÚSCULAS y limpieza de dobles espacios.

Resultado: 0 nombres canónicos con ¿ o caracteres raros tras la limpieza. 9.983 nombres → 7.193 claves canónicas.

Fase 4 — Modelo estrella en Power BI

Con las 18 dimensiones canónicas listas (un CSV por dimensión), las cargué a Power BI y construí el modelo. Para esto usé pbi-cli, que permite operar el modelo desde scripts.

pbi connect    # autodetecta el puerto local de Power BI Desktop

El patrón por dimensión:

pbi table create  dim_modalidad --mode Import --m-expression -
pbi column create id_modalidad --table dim_modalidad --data-type int64 --is-key
pbi column create nombre_modalidad --table dim_modalidad --data-type string
pbi table refresh dim_modalidad --type Full

pbi relationship create \
  --from-table data_u_co_unificado --from-column "ID MODALIDAD" \
  --to-table   dim_modalidad       --to-column   id_modalidad \
  --cross-filter OneDirection --active

Repetí el patrón para las 17 dimensiones + dim_calendario. 18 relaciones, todas OneDirection. Para ocultar las 42 columnas de IDs en la tabla de hechos tuve que exportar a TMDL, editar la línea isHidden y reimportar (la CLI no expone column hide directamente):

column 'CÓDIGO DE LA INSTITUCIÓN'
    isHidden
    dataType: int64
    sourceColumn: CÓDIGO DE LA INSTITUCIÓN

Quedaron visibles solo 3 columnas de la fact: AÑO, SEMESTRE y MATRICULADOS PRIMER CURSO. Todo lo demás se filtra y etiqueta desde las dimensiones canónicas.

Fase 5 — 21 medidas DAX

Construí 21 medidas DAX divididas en dos grupos: 11 de crecimiento (CAGR, volatilidad YoY, Score Estable) y 10 de tiempo (YTD, semestre anterior, RANKX de programa). La lógica central calcula el YoY programa a programa en DAX puro, sin columnas calculadas, para que el modelo escale a los 17.411 programas sin degradar el rendimiento en consultas de ranking.

Organicé el núcleo en dos folders:

Crecimiento (11 medidas): Matriculados, Año Inicial, Año Final, N Años con Datos, Matric Año Inicial, Matric Año Final, CAGR, YoY Promedio, YoY Volatilidad, Coef Variación YoY, Score Estable.

El patrón YoY a nivel de programa:

VAR _aniosV =
    FILTER(
        ADDCOLUMNS(VALUES('data_u_co_unificado'[AÑO]), "_v", [Matriculados]),
        [_v] > 0
    )
VAR _yoys =
    ADDCOLUMNS(
        _aniosV, "_yoy",
        VAR _year = 'data_u_co_unificado'[AÑO]
        VAR _vAct = [_v]
        VAR _vAnt = CALCULATE([Matriculados], 'data_u_co_unificado'[AÑO] = _year - 1)
        RETURN IF(_vAnt > 0, DIVIDE(_vAct, _vAnt) - 1)
    )
RETURN AVERAGEX(FILTER(_yoys, NOT ISBLANK([_yoy])), [_yoy])

Tiempo (10 medidas): YTD, año anterior, semestre anterior, dos años atrás, deltas, porcentajes y RANKX de programa.

El dim_calendario es una tabla calculada con CALENDAR(2014, 2024) + columnas en español hardcodeadas (no confío en el locale de FORMAT(..., "MMMM")):

ADDCOLUMNS(
    CALENDAR(DATE(2014, 1, 1), DATE(2024, 12, 31)),
    "Anio",       YEAR([Date]),
    "Mes Nombre", SWITCH(MONTH([Date]), 1,"Enero", 2,"Febrero", ...),
    "Semestre",   IF(MONTH([Date]) <= 6, 1, 2),
    ...
)

Lo marqué como date table con pbi calendar mark y lo conecté a la fact por una columna Fecha_InicioS que construí al unificar los 11 Excel.

Trampas técnicas que costaron horas

En este proyecto me encontré con siete incompatibilidades de tipos, encodings y comportamientos de la CLI que no están documentadas en los tutoriales oficiales de Power BI ni de pbi-cli. Las dejo escritas porque se repiten en cualquier proyecto que combine Python, pbi-cli y datos del MEN.

Errores de tipo e integración Python → Power BI

Estas no salen en los tutoriales, vale la pena dejarlas escritas:

1. pandas guarda IDs como 1.0, 2.0 en CSV. Power BI no relaciona Int64 con Float. Solución: _coerce_id() con Int64 nullable antes de exportar.
2. CINE como String en la fact pero Int64 en la dim. Relación rota silenciosa. Hay que ajustar el tipo en el M de la dim.
3. pbi table create con M no expone columnas automáticamente. Tras table create hay que llamar pbi column create por cada columna y después table refresh --type Full.

Errores de CLI, DAX y encoding

1. @v, @yoy se eliminan del DAX. La CLI los interpreta como parámetros. Renombrar a _v, _yoy.
2. _año como variable DAX rompe el parser. Las medidas y columnas sí aceptan ñ/tildes; las VAR locales no. Renombrar a _year.
3. Locale de FORMAT([Date], "MMMM") puede dar inglés según el modelo. Hardcodear meses con SWITCH.
4. Consola Windows imprime ? en lugar de tildes. Setear PYTHONIOENCODING=utf-8 antes de correr scripts.

Lo que dicen los datos

Al cruzar los datos encontré que los programas de mayor crecimiento relativo son licenciaturas y tecnologías TI, no las ingenierías tradicionales. El bloque de software crece rápido y de forma sostenida; las licenciaturas crecen más rápido pero con alta volatilidad interanual, lo que el Score Estable que diseñé penaliza. En cifras globales, los matriculados de primer curso pasaron de 782.781 (2014) a 1.058.036 (2024): +35 % total pero solo +3 % anual ponderado.

Top 5 programas por CAGR (con cobertura ≥ 8 años, base inicial ≥ 30, vigentes en 2024):

Programa	CAGR	Inicial → Final
LIC. EN EDUCACIÓN INFANTIL	+98,0 %	37 → 8.743
LIC. EN LENGUAS EXTRANJERAS C/ ÉNFASIS EN INGLÉS	+69,2 %	146 → 5.805
LIC. EN EDUCACIÓN FÍSICA, RECREACIÓN Y DEPORTES	+63,7 %	90 → 2.835
TÉC. PROFESIONAL EN SOPORTE DE SISTEMAS EN INFORMÁTICA	+54,5 %	158 → 5.128
TECNOLOGIA EN GESTION DEL TALENTO HUMANO	+52,6 %	56 → 3.842

Estabilidad y cobertura global

Crecimiento estable (CAGR alto + baja volatilidad): INGENIERÍA DE SOFTWARE y TECNOLOGIA EN DESARROLLO DE SOFTWARE lideran. El bloque tech crece rápido y de forma sostenida; las licenciaturas crecen más rápido pero a saltos (probablemente por convenios masivos puntuales o cambios regulatorios).

Cobertura global: matriculados de primer curso pasaron de 782.781 (2014) a 1.058.036 (2024). +35 % total, pero la media anual ponderada es solo +3 %. El sector es estable en agregado; las dinámicas interesantes están a nivel de programa.

Cuándo replicar este enfoque

Este pipeline es replicable sin modificaciones mayores en cualquier fuente de datos abiertos que publique un archivo por período con estructura variable. El esfuerzo real está en el diccionario de mapeo y la auditoría dimensional, no en el modelado; en este proyecto ambas fases tomaron 2 días de los 5 que duró el proyecto completo.

Este pipeline aplica casi tal cual a cualquier dataset público fragmentado por año:

• Datos de salud (RIPS, ENSIN), trabajo (PILA), tributación (DIAN), etc.
• Cualquier conjunto donde la estructura cambie año a año y existan documentos de «metadatos» del propio organismo.
• Cualquier modelo donde haya que distinguir entre el ID (estable) y el nombre (con variantes ortográficas).

Las cuatro decisiones con más retorno

Las cuatro decisiones que más rendimiento dieron:

1. Diccionario de mapeo desde los metadatos del propio MEN, no a ojo.
2. Auditoría dimensional sistemática (IDs con varios nombres / nombres con varios IDs) antes de modelar.
3. Reparación regex de alta confianza + clave canónica normalizada por programa.
4. TMDL editable para todo lo que la CLI de Power BI no expone (ocultar columnas, anotaciones, propiedades finas).

Terminé el proyecto con 18 dimensiones, 18 relaciones, 42 columnas ocultas, 21 medidas DAX y un calendario marcado, todo reproducible desde scripts de Python. La diferencia entre un análisis que se publica y uno que se queda en draft es exactamente esto: un pipeline que se vuelve a correr en 30 minutos cuando el MEN suba el archivo de 2025.