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

¿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. La forma más rápida fue 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

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

Al revisar el primer notebook descriptivo aparecieron dos hallazgos que 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: tildes y mayúsculas estaban contando entidades distintas. Ingeniería y INGENIERIA eran dos programas. Una auditoría sistemática reveló:

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) se eligió 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), tocaba cargarlas a Power BI y construir 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

Multiplicado por 17 dimensiones + dim_calendario. 18 relaciones, todas OneDirection. Para ocultar las 42 columnas de IDs en la tabla de hechos hubo 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

El núcleo está 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),
    ...
)

Marcado como date table con pbi calendar mark y conectado a la fact por una columna Fecha_InicioS que se construyó al unificar.

Trampas técnicas que costaron horas

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

Lo que dicen los datos

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

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 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 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).

El proyecto completo termina 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.