Modelo OLAP en Excel: guía para analizar sus datos

Un modelo OLAP es una estructura preparada para analizar datos desde varias perspectivas: no solo registrar cada operación, sino responder preguntas como cuántas incidencias se resolvieron por servicio y trimestre. En este tutorial usarás un libro de Excel con incidencias de soporte y aprenderás a reconocer medidas, dimensiones y filtros, explorar resultados publicados por un cubo y comprobar con fórmulas que las cifras coinciden. La conexión real requiere un servidor OLAP configurado; las prácticas del libro reproducen esa exploración con datos tabulares.

Qué es un modelo OLAP y cómo se explora en Excel

OLAP significa procesamiento analítico en línea. Agrupa los datos en medidas, como el total de incidencias, y dimensiones, como servicio, estado o fecha. Esa estructura permite pasar de un total anual a un trimestre, un mes o un día sin recalcular cada informe desde cero.

El libro de ejemplo se llama Incidencias_OLAP.xlsx y ya contiene una tabla de seis incidencias. Servirá para comprobar y explicar los resultados de un cubo, no para construir uno. Un cubo real se define en SQL Server Analysis Services (SSAS), como ocurre con los cubos de Service Manager: Excel consume sus resultados, pero no importa un archivo MDX como una tabla normal.

La explicación de MDX, el lenguaje para consultar cubos, y el papel de las agregaciones precalculadas está en la documentación de los cubos OLAP de Service Manager. La introducción al concepto OLAP añade el contexto general.

Medidas, dimensiones y jerarquías en un modelo OLAP

Una medida es un valor que se agrega: Total, Incidencias resueltas o Porcentaje resuelto. Una dimensión describe desde qué perspectiva desde se observa: Servicio, Estado o Fecha. La dimensión Fecha admite una jerarquía de Año, Trimestre, Mes, Semana y Día.

Las agregaciones del cubo sustentan los resultados que Excel muestra. Por ejemplo, Correo tiene cuatro incidencias en el trimestre y tres resueltas: 75 %. La jerarquía temporal permite comparar enero con febrero o con el trimestre completo.

Consultas MDX y resultados precalculados

MDX es el lenguaje estándar para crear consultas, medidas calculadas y miembros calculados en sistemas OLAP. Una consulta puede pedir, por ejemplo, el total de incidencias resueltas de Correo durante el primer trimestre.

Excel no ofrece un importador directo de archivos MDX. Cuando abre un cubo publicado, solicita los datos que necesita y presenta los resultados como una lista de lista con filtros. En segundo plano, SSAS responde con agregaciones almacenadas o precalculadas para acelerar la consulta.

La diferencia es clave: MDX describe qué se pregunta al cubo; Excel ofrece la interfaz para explorarlo. En Service Manager, los cubos se despliegan y mantienen en SSAS, con particiones, traducciones, localización y cambios de esquema, según la documentación oficial ya citada.

Conectar Excel con un origen de datos OLAP

La conexión se realiza desde la cinta de opciones. El procedimiento empieza con Datos > Obtener datos y continúa en el asistente hasta elegir un origen OLAP. El servidor, el cubo y las credenciales deben estar configurados por el administrador de la plataforma.

En el asistente de conexión se indica el origen OLAP disponible. Excel autentica al usuario, recupera la estructura del cubo y la abre en una hoja. A partir de ahí se pueden mostrar medidas, expandir dimensiones y aplicar filtros.

Diferenciar una conexión OLAP de una consulta tabular

Ambas conexiones pueden convivir en el mismo libro, pero funcionan de forma distinta. La conexión tabular entrega un conjunto de registros; la conexión OLAP ofrece medidas precalculadas que el usuario puede desglosar.

Explorar un cubo OLAP desde una hoja de Excel

La práctica comienza con la tabla de incidencias. En un cubo real, este papel lo desempeña la hoja obtenida al conectar el origen OLAP. Para aprender de forma autónoma, escribe la tabla siguiente en Incidencias_OLAP.xlsx, en la hoja Incidencias.

1. Abre el libro y selecciona la pestaña Incidencias.
2. En la celda A1, escribe ID.
3. En B1, escribe Servicio, y en C1, Mes.
4. En D1, escribe Trimestre; en E1, Estado.
5. En F1, escribe Total y en G1, Resueltas.
6. Copia cada valor de la tabla en su celda correspondiente.

Ahora añade una medida calculada de porcentaje. En H1 escribe Porcentaje resuelto.

=G2/F2

Arrastra el controlador de relleno de H2 hasta H7.

Desglosar una medida por mes y servicio

Con un cubo conectado, la operación equivalente es arrastrar Servicio y Mes a la zona de filtros o columnas, y Total a las celdas. En la tabla, creamos el mismo resultado con SUMAR.SI.CONJUNTO (SUMIFS).

En J1 escribe Servicio y en J2, Correo. En K1 escribe Mes y en K2, Enero. En L1 escribe Total y en L2 escribe esta fórmula:

=SUMAR.SI.CONJUNTO($F$2:$F$7;$B$2:$B$7;$J$2;$C$2:$C$7;$K$2)

Copia L2 a L3 y cambia K2 a Febrero; luego a L4 y cambia K2 a Marzo.

El desglose muestra una diferencia entre enero y febrero: Correo pasó de una incidencia a dos, mientras que marzo vuelve a una. En la exploración del cubo, este mismo cambio aparece al expandir o reducir la jerarquía.

Filtrar el modelo OLAP con sus dimensiones

Ahora limita el análisis a Impresoras y al primer trimestre. En un cubo, eliges Servicio = Impresoras y Trimestre = T1 en los filtros. En el libro, la misma restricción se aplica con dos criterios.

En N1 escribe Servicio, en N2 Impresoras; en O1 escribe Trimestre, en O2 T1; en P1, Total y en P2:

=SUMAR.SI.CONJUNTO($F$2:$F$7;$B$2:$B$7;$N$2;$D$2:$D$7;$O$2)

El filtro deja dos incidencias: una de enero y otra de marzo. Ninguna corresponde a Resuelta, por lo que el porcentaje resuelto del subconjunto es 0 %.

Cambiar un filtro y comprobar la medida

Cambia N2 de Impresoras a Correo y observa el resultado de P2: 4. En Q1 escribe Porcentaje resuelto y en Q2:

=CONTAR.SI($B$2:$B$7;$N$2;$E$2:$E$7;"Resuelta")/P2

El filtro de Estado hace el cambio equivalente en el cubo: Abierta, En curso y Resuelta. En Excel, modificar N2 o los criterios de la fórmula permite simular esa selección.

Analizar tendencias con la jerarquía de fechas

La jerarquía Fecha permite pasar de año a trimestre, mes, semana y día. En el libro, se resume el primer trimestre por mes. Crea un bloque nuevo: en J10, Mes; en K10, Total; en J11, Enero; en J12, Febrero; en `J13, Marzo**.

En K11 usa:

=SUMAR.SI($C$2:$C$7;J11;$F$2:$F$7)

Copia la fórmula hasta K13. Los resultados son 2, 2 y 2: el total del trimestre es 6.

Esta tabla es el equivalente de un resumen mensual del cubo. La jerarquía trimestral lo sitúa dentro de T1 y, si el origen tiene más historial, dentro del año correspondiente.

Comparar el trimestre actual con el anterior

Continúa con el resumen de enero a junio para que haya dos trimestres comparables. Añade abril, mayo y junio a J14:J16, con una incidencia cada uno, y usa la misma fórmula.

En J18 escribe Trimestre 1 y en K18 la fórmula =SUMA(K11:K13). En J19 escribe Trimestre 2 y en K19, =SUMA(K14:K16).

En J20 escribe Diferencia y en K20:

=K19-K18

En J21 escribe Variación y en K21:

=(K19-K18)/K18

La caída del 50 % coincide con la tendencia trimestral que mostraría el cubo: el segundo trimestre tiene menos incidencias que el primero.

Usar medidas calculadas y reglas de negocio

SSAS calcula Incidencias resueltas y Porcentaje resuelto mediante medidas y cálculos con nombre, aplicando reglas de negocio. Por ejemplo, una regla puede considerar «resuelta» una incidencia con un estado determinado. Excel solo recibe y presenta ese resultado; no es quien decide el criterio.

Para verificarlo en el libro, crea un resumen por estado. En J23 escribe Estado, en K23** **Total**, y en J24:J26 escribe Resuelta, En curso y Abierta**. En K24:

=CONTAR.SI($E$2:$E$7;J24)

Copia hasta K26.

En J28 escribe Porcentaje resuelto y en K28:

=CONTAR.SI($E$2:$E$7;"Resuelta")/CONTAR(A2:A7)

El resultado es 3/6, es decir, 50 % para todas las incidencias. Si el cubo publica una cifra distinta para el mismo periodo, la diferencia no es un error de fórmula: puede deberse a la regla de negocio, al periodo o al conjunto de datos considerado.

Detectar una medida que no coincide con el detalle

Compara el porcentaje del cubo con el detalle real. Si el cubo indica 67 %, comprueba primero qué periodo incluye y si tiene filtros aplicados. En el detalle de este libro no es posible obtener exactamente 67 %, porque 4 de 6 daría 66,67 %, y ninguna combinación de estados y meses completa esa proporción.

La fórmula de comprobación es:

=CONTAR.SI(E2:E7;"Resuelta")/CONTAR(A2:A7)

Si no coincide, revisa cuatro puntos: filtros del cubo, reglas de negocio, periodo comparado y fecha de actualización del origen. No cambies la fórmula hasta identificar cuál de ellos explica la diferencia.

Cuándo Excel no sustituye al modelo OLAP

Excel explora, filtra y publica resultados. No crea por sí solo el cubo multidimensional. Definir dimensiones, jerarquías, medidas, cálculos con nombre, particiones y reglas de agregación corresponde al modelador y a la plataforma OLAP.

El cubo es una implementación clásica de OLAP; el concepto es más amplio. Para entender mejor la separación entre plataforma y hoja de cálculo, consulta la explicación de OLAP de Databricks.

Power Query y los cubos de Service Manager

Power Query consolida y transforma datos de distintas fuentes. Un cubo de Service Manager es una estructura multidimensional de SSAS, con medidas, dimensiones y agregaciones. Las dos herramientas tienen propósitos distintos.

No existe un flujo automático que convierta por sí solo una transformación de Power Query en un cubo de Service Manager. La documentación oficial de cubos OLAP de Service Manager describe cómo la consola administra y despliega esos cubos.

Actualizar y guardar una exploración OLAP

Una hoja conectada mantiene un vínculo con el origen. Para actualizarla, abre el libro y usa la opción de actualización de la pestaña Datos.

1. Abre Incidencias_OLAP.xlsx.
2. Selecciona la hoja Incidencias.
3. En Datos, comprueba que la conexión aparece en la lista de conexiones y de tablas.
4. Pulsa Actualizar.
5. Revisa que las medidas y los totales conserven los valores esperados.
6. Guarda el libro con Archivo > Guardar como y elige el formato *Libro de Excel (.xlsx)**.

Errores al explorar un modelo OLAP

Los fallos más frecuentes aparecen antes de ver cifras: no se encuentra el origen, expiran las credenciales, el cubo no está disponible o los resultados no se actualizan. La hoja no suele ser la causa.

• El origen OLAP no aparece al conectar. Puede faltar una configuración del administrador o no disponer del controlador adecuado. Verifica el origen disponible y pide al administrador que revise la conexión.
• La actualización rechaza las credenciales. La contraseña pudo caducar o cambiar el permiso del usuario. Vuelve a autenticar la conexión en lugar de modificar fórmulas.
• El cubo solicitado no está disponible. Puede haberse retirado, retirado del despliegue o no estar publicado en el origen. Comprueba su existencia en el origen y con el administrador de SSAS.
• Los datos siguen sin actualizar. La actualización puede estar deshabilitada o el servidor puede no estar accesible. Actualiza manualmente y comprueba el estado del origen.
• Una medida no coincide con el detalle. Revisa filtros, reglas de negocio y periodo antes de concluir que el cálculo de Excel es incorrecto.

Revisar conexión, origen y actualización

1. Abre Datos > Consultas y conexiones.
2. Selecciona la conexión OLAP y comprueba el nombre del origen.
3. Ejecuta Actualizar y anota el mensaje exacto que aparezca.
4. Verifica que el servidor esté disponible y que tus permisos sigan vigentes.
5. Contrasta el resultado publicado con la fórmula de comprobación de Excel.

Lo que ya sabes hacer con un modelo OLAP

• Reconoces un modelo OLAP como una organización de medidas, dimensiones y jerarquías para analizar tendencias.
• Diferencias una medida, como Total, de una dimensión, como Servicio o Fecha.
• Entiendes que MDX consulta el cubo y que las agregaciones de SSAS sustentan los resultados mostrados en Excel.
• Conectas Excel con un origen OLAP preparado mediante la cinta Datos, exploras medidas y aplicas filtros.
• Desglosas un total por mes y servicio, y comparas trimestres mediante fórmulas de Excel.
• Compruebas Incidencias resueltas y Porcentaje resuelto con CONTAR.SI (COUNTIF) y una división.
• Reconoces cuándo una diferencia procede de filtros, reglas de negocio o actualización, no de la fórmula.
• Diferencias la exploración de un cubo de la transformación con Power Query.
• Actualizas una hoja conectada y la guardas como .xlsx, teniendo presente que el acceso al origen depende del equipo.

Tu siguiente paso es abrir el libro, guardar la hoja con tus filtros y pedir al administrador el nombre del cubo y la conexión disponible para sustituir la tabla de ejemplo por resultados reales.

Dudas al analizar un modelo OLAP en Excel