Funciones Cubo en MS Excel – Parte 2

Este artículo es la continuación de Funciones Cubo en MS Excel – Parte 1 , donde dimos una introducción a estas funciones. Esta segunda parte tiene como objetivo mostrar las funciones cubo más importantes, enseñar su funcionamiento y transmitir su gran importancia!

Antes de partir, algunas definiciones importantes:

DAX (Data analysis Expressions): Funciones usadas en Power Pivot para crear indicadores (columnas y/o medidas). Similar en sintaxis a las funciones comunes de MS Excel.

Filter Context: Conjunto de filtros aplicados al modelo de datos antes de realizar un cálculo. En una tabla dinámica estos filtros vienen de los valores de columnas colocados en las áreas “Filas”, “Columnas” y “Filtros” (o segmentación de datos). Por ejemplo, si una tabla dinámica muestra ventas por producto por año (con producto en área “Filas”, y año en área “Columnas”), cada venta tiene como “Filter Context” un producto y un año en particular.

Continuando con funciones cubo:

En la parte 1, vimos que es posible convertir a fórmulas una tabla dinámica en MS Excel conectada a Power Pivot, lo cual nos ofrece la flexibilidad de crear reportes celda a celda y toda la potencia analítica de Power Pivot. Para eso usamos datos de la empresa ficticia “Adventure Works” cargados a Power Pivot a partir del cual creamos un simple reporte de ventas por categoría, segmentado por año y mes.

Modelo Datos Power Pivot usado

Reporte ventas por categoria segmentado por año y mes

Tabla dinámica convertida en funciones cubo

Sin embargo, ¿es necesario seguir estos pasos para crear estas funciones? No, no es necesario. De hecho, podemos crear un reporte usando las fórmulas cubo directamente desde MS Excel partiendo de cero, como cualquier otra función de MS Excel. La única condición es que contemos con un modelo de datos (como Power Pivot). Existen varias funciones cubo como podemos ver en la siguiente figura.

¿Cómo funcionan? Veremos las 2 más importantes: “VALORCUBO” Y “MIEMBROCUBO”, cuyas sintaxis las podemos ver en las siguientes figuras.

Para usar estas funciones, debemos entender un nuevo concepto que llamaremos “Filter Context Cubo”, que será una serie de filtros aplicados al modelo de datos (“Filter context”) más un filtro extra que será la medida DAX que se quiere usar en el cálculo. Todos los filtros aplicados al modelo de datos los crearemos usando la función “MIEMBROCUBO”. Finalmente usaremos la función “VALORCUBO” para obtener un resultado mediante la referencia a todos los miembros cubos.

Para entenderlo replicaremos el reporte de ventas por categoría segmentado por año y mes desde cero, siguiendo estos pasos:

1- Sabemos que el objetivo es calcular la suma de ventas por categoría segmentado por año y mes. ¿Cuáles son los filtros que debemos aplicar al modelo de datos (“Filter Context Cubo”). Tenemos 4 filtros: la categoría, el año, el mes, y la medida “VentasTotales” (previamente creada con DAX).

2- Para crear los filtros de categoría y medida, usaremos la función “MIEMBROCUBO”. Por otro lado, para crear los filtros de año y mes agregaremos segmentación de datos.

3- “MIEMBROCUBO” recibe como primer argumento una conexión, como segundo una expresión miembro y como tercer argumento (opcional) un título. Para el primer argumento solo debemos escribir comillas y automáticamente nos saldrá el nombre de la conexión al modelo de datos (autocompletamos y cerramos comillas). En el segundo argumento debemos escribir el filtro que usaremos siguiendo la sintaxis “[NombreTabla].[NombreColumna].&[Valor]”. Por ejemplo, si filtro es “NombreCategoría = Accesorio” debemos escribir «[Categoria].[NombreCategoria].&[Accesorio]». Si cerramos la función y presionamos “Enter” veremos que la función entrega como resultado el valor “Accesorio”. En caso que queramos mostrar un título distinto, usamos el tercer argumento de la función “Titulo”, teniendo claro que solo será una representación visual pues el filtro al modelo de datos es “Accesorio”.                                                                             

4- Para crear el filtro de medida, usaremos también “MIEMBROCUBO” siguiendo la sintaxis “[Measures].[NombreMedidaDAX]” para el segundo argumento. Para el caso de la medida “VentasTotales” como filtro (previamente creada con DAX) debemos escribir “[Measures].[VentasTotales]”. [Measures] podemos interpretarlo como una tabla imaginaria en el modelo de datos que contiene a todas las medidas DAX creadas.

5- Las segmentaciones de datos no se crean con funciones cubo. En estos casos debemos ir a la pestaña “Insertar” de MS Excel, luego a la sección filtros, y seleccionar “Segmentación de datos”. Esto nos abrirá una interfaz donde elegiremos la pestaña “Modelo de datos”, seleccionaremos las tablas del modelo de datos y haremos clic en “abrir” donde finalmente elegiremos las columnas que queremos usar como segmentación (año y mes en nuestro ejercicio).

6- Respecto a estas segmentaciones, debemos tener en cuenta sus nombres pues luego los ocuparemos. Para saber los nombres de cada segmentación, hacemos clic derecho en la segmentación y elegimos “Configuración de Segmentación de datos” donde veremos la interfaz de configuración y encontraremos el nombre.                                                                                       

7- Con todos los filtros listos, solo nos falta el cálculo y para esto usaremos “VALORCUBO”. Esta función cubo recibe como primer argumento una conexión (igual que “MIEMBROCUBO”), y todos los demás argumentos serán referencias a expresiones miembros cubos, es decir, filtros creados con “MIEMBROCUBO” o filtros creados con segmentación de datos. La siguiente figura muestra la función “VALORCUBO” final, donde referenciamos a los filtros “VentasTotales”, “Accesorio” y a las segmentaciones de datos año y mes usando sus nombres.

De esta manera podemos, ampliamos aún más las capacidades de manejo y análisis de datos usando MS Excel y funciones cubo. Imaginen la cantidad de situaciones donde estas funciones son perfectas!! Maravilloso!