En entradas anteriores del blog publiqué sobre el uso de las funciones CUBO, pero sin profundizar en el verdadero potencial que encierran...
Hoy nos adentraremos en el fascinante mundo de las funciones de CUBO y sus expresiones MDX (MultiDimensional eXpressions)
Correcto... lo primero que debes saber es que estas funciones CUBO atacan al Modelo de datos a través de expresiones en MDX (una especie de SQL adaptado)...
Por lo que, en efecto, otro lenguaje entra a escena además de los ya conocidos: DAX, lenguaje M, etc...
En el post de hoy mencionaré las expresiones más habituales y que nos facilitarán el trabajo de una forma insospechada!!.
Para explorar este asombroso mundo partiremos de una sencilla base de datos agregada al Modelo de datos.
Dentro del Modelo de datos, además hemos incorporado una tabla de fechas (Editor de Power Pivot > ficha Diseñar > grupo Calendarios > botón Tabla de fechas > Nueva), y que relacionaremos con la base de datos anterior: Fecas - Date
Adicionalmente crearemos en el Área de cálculo la siguiente medida (muy simple):
Sum_Ventas:=SUM([Ventas])
Listos para volver a nuestra hoja de cálculo y trabajar 'con cubo y pala' ;-)
Las dos primeras expresiones que debes conocer son: .Members y .Children.
En ambos casos listarán los miembros/elementos de la dimensión donde nos movamos... la diferencia es que:
.Members muestra TODOS los miembros (incluido el 'All') ordenados.
mientras que .Children extrae solo los 'hijos' dependientes igualmente ordenados.
Veamos un ejemplo... Crearemos un par de conjuntos, usando la función CONJUNTOCUBO en las celdas J4 y M4.
En J4 insertamos:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"[Tabla1].[País].Members";
"paises")
y en M4:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"[Tabla1].[País].Children";
"países")
Para verificar cuales osn los miembros que componen cada Conjunto creado podemos, en primer lugar, obtener un conteo de éstos, con la función RECUENTOCONJUNTOCUBO aplicado sobre nuestras celdas J4 y M4 (donde habíamos definido los conjuntos).
Y por otra parte, listar los miembros con la función MIEMBRORANGOCUBO.
En J7 podemos escribir:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";J4;SECUENCIA(RECUENTOCONJUNTOCUBO(J4)))
donde vemos que los miembros listados son: 'All', 'DE', 'ES', 'FR', 'PT'
En M7 podemos escribir:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";M4;SECUENCIA(RECUENTOCONJUNTOCUBO(M4)))
donde vemos que los miembros listados son: 'DE', 'ES', 'FR', 'PT'
Un uso estándar de este listado de miembros podría ser aplicar sobre ellos la función VALORCUBO.
En K7 podemos escribir:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[Sum_Ventas]";$J7)
donde vemos que para cada miembro, obtemos el resultado de nuestra medida 'Sum_Ventas'... obviamente para 'All' tenemos el total acumulado de 'todos' los miembros.
Otro ejemplo que podemos mostrar es cómo conseguir datos filtrados por fechas, por ejemplo.
Supongamos que queremos obtener los datos de ciertos años, entre el 2013 y 2017...
Para ello crearemos un CONJUNTO y en J16 escribiríamos:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"[Calendar].[Date Hierarchy].&[2013]:[Calendar].[Date Hierarchy].&[2017]";
"Fechas-13-17")
Podemos averiguar el número de miembros únicos en ese intervalo de tiempo... que debes fijarte se ha expresado como:
inicio : fin (ojo que equivaldría a >=inicio y <=fin).
En K16 añadimos:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[Sum_Ventas]";J16)
que nos devuelve el acumulado de las Ventas (obtenido de nuestra 'medida' del Modelo de datos) para los miembros del CONJUNTO recién creado (6.845,18 para nuestro ejemplo).
para comprobarlo, por si aún no te fías de la seguridad de estos cálculos, podemos construir una tabla dinámica como la siguiente y aplicar los filtros correspondientes:
Otro cálculo muy interesante lo podemos realizar empleando la expresión .Parent, que devuelve el 'padre' (lo contrario de '.Children') sobre un miembro en concreto.
Por ejemplo en la celda L16 escribimos:
=VALORCUBO("ThisWorkbookDataModel";
"[Measures].[Sum_Ventas]";
"[Calendar].[Date Hierarchy].&[2010].Parent")
que nos devuelve el acumulado de ventas para TODOS los años... esto es, a pesar de indicar un miembro en concreto, una fecha en particular: 2010, al indicar que deseamos su .Parent, recuperamos la totalidad de los años (17.320,79).
Un paso más allá consistiría en combinar este conjunto de años 2013-2017 con otro conjunto de comerciales: ANA+EVA.
Así creamos un nuevo conjunto de dos miembros, en la celda J21 añadimos:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"{[Tabla1].[Comercial].&[ANA],[Tabla1].[Comercial].&[EVA]}";
"ana+eva")
fijémosnos en que forma hemos indicado uno y otro... entre llaves y separados por coma (como una matriz de elementos).
Igualmente podríamos haber escrito en un par de celdas los nombres ANA y EVA (en J24:J25). Y haber creado el siguiente conjunto referido a esas celdas... =CONJUNTOCUBO("ThisWorkbookDataModel";
"[Tabla1].[Comercial].["&$J$24:$J$25&"]";
"ana+eva")
En cualquiera de ambos casos, cruzando los miembros 'Años' de .Parent con el conjuntos en J21 de ambos comerciales (ANA + EVA) obtendríamos un resultado ajustado (10.484,80).
Como se comprueba con nuestra tabla dinámica (de momento sigo demostrando la fiablidad de esta formulación):
Terminaré el post de hoy con algunas otras funciones MDX con las que podemos trabajar dentro del contexto de nuestras funciones CUBO:
.LastChild
.FirstChild
.Lead
.Lag
.NextMember
.PrevMember
Son facilmente comprensibles...
.LastChild nos devuelve el último miembro, el último 'hijo' del conjunto.
.FirstChild nos devuelve el primer miembro, o 'hijo' del conjunto.
.NextMember nos devuelve el siguiente miembro desde el especificado.
.PrevMember nos devuelve el miembro previo o anterior del conjunto.
.Lead y .Lag :
Recuperamos la posición de un miembro dentro de un nivel.. sabiendo que la numeración está en base cero.
Si especificamos .Lead(0) la función nos devolvería el miembre específicado.
Si a la función .Lead le aplciamos un número o posición negativa devuelve el miembro anterior.
Por tanto .Lead(1) es equivalente a la función .NextMember; y .Lead(-1) equivale a .PrevMember
Las funciones .Lead y .lag son similares, excepto porque .lag va en dirección contraria que .Lead. Es decir, .Lead(n) equivale .Lag(-n).
Unos ejemplos sencillos que nos permiten ver su funcionamiento:
Las fórmulas empleadas en el rango O2:O7 son:
En O2 =MIEMBROCUBO("ThisWorkbookDataModel";"[Tabla1].[Año].LastChild")
En O3 =MIEMBROCUBO("ThisWorkbookDataModel";"[Tabla1].[Año].FirstChild")
En O4 =MIEMBROCUBO("ThisWorkbookDataModel";"[Tabla1].[Año].[2015].Lead(-1)")
En O5 =MIEMBROCUBO("ThisWorkbookDataModel";"[Tabla1].[Año].[2015].Lag(-1)")
En O6 =MIEMBROCUBO("ThisWorkbookDataModel";"[Tabla1].[Año].[2015].NextMember")
En O7 =MIEMBROCUBO("ThisWorkbookDataModel";"[Tabla1].[Año].[2015].PrevMember")
Funciones muy útiles para poder referirnos a distintos miembros del conjunto...
Por ejemplo, si queremos comparar un periodo con el anterior.
Veamos la distribución de nuestras fórmulas en el minireport.
En la celda N12 escribimos:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"[Calendar].[Date Hierarchy].&[2013]:[Calendar].[Date Hierarchy].&[2017]";
"Fechas-13-17")
que estará compuesto, como ya vimos por los miembros entre los ejercicios 2013 y 2017.
En N18 listamoms dichos miembros de nuestro conjunto con la fórmula:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";
$N$12;
SECUENCIA(RECUENTOCONJUNTOCUBO($N$12)))
Por otra parte definimos un nuevo conjunto, también visto, con los comerciales ANA y EVA, así en O11:
=CONJUNTOCUBO("ThisWorkbookDataModel"; "{[Tabla1].[Comercial].&[ANA],[Tabla1].[Comercial].&[EVA]}"; "ana+eva")
Escribimos nuestros encabezados como textos planos en O12:Q12
Actual // Y-1 // Todos comerciales
Y para retornar los importes de las ventas de cada año acumulados para ANA y EVA añadiremos en O13
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[Sum_Ventas]";N13;$O$11)
función con la recuperamos los valores de nuestro cruce de cubos...
Para recuperar las ventas acumuladas del periodo previo usaremos la función MDX .Lag(1) en P13 y siguientes:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[Sum_Ventas]";"[Calendar].[Date Hierarchy].&["&$N13&"].Lag(1)";$O$11)
esto es, ventas del año previo acumulados de los dos comerciales ANA y EVA.
Finalemente, para cada año del periodo de estudio 2013-2017, obtendremos el dato de ventas acumulado de TODOS los comerciales... En Q13 añadimos:
=VALORCUBO("ThisWorkbookDataModel"; "[Measures].[Sum_Ventas]"; N13; "[Tabla1].[Comercial].&["&MIEMBRORANGOCUBO("ThisWorkbookDataModel";$O$11;1)&"].Parent")
Donde hacemos una llamada a la totalidad de los comerciales con .Parent, a partir de un elemento (del primer miembro) del conjunto de comerciales...
Podemos comprobarlo en la siguiente tabla dinámica:
Continuaré en entradas del blog siguientes ahondando en este mundo de las funciones CUBO y este lenguaje MDX.
Hoy nos adentraremos en el fascinante mundo de las funciones de CUBO y sus expresiones MDX (MultiDimensional eXpressions)
Correcto... lo primero que debes saber es que estas funciones CUBO atacan al Modelo de datos a través de expresiones en MDX (una especie de SQL adaptado)...
Por lo que, en efecto, otro lenguaje entra a escena además de los ya conocidos: DAX, lenguaje M, etc...
En el post de hoy mencionaré las expresiones más habituales y que nos facilitarán el trabajo de una forma insospechada!!.
Para explorar este asombroso mundo partiremos de una sencilla base de datos agregada al Modelo de datos.
Dentro del Modelo de datos, además hemos incorporado una tabla de fechas (Editor de Power Pivot > ficha Diseñar > grupo Calendarios > botón Tabla de fechas > Nueva), y que relacionaremos con la base de datos anterior: Fecas - Date
Adicionalmente crearemos en el Área de cálculo la siguiente medida (muy simple):
Sum_Ventas:=SUM([Ventas])
Listos para volver a nuestra hoja de cálculo y trabajar 'con cubo y pala' ;-)
Las dos primeras expresiones que debes conocer son: .Members y .Children.
En ambos casos listarán los miembros/elementos de la dimensión donde nos movamos... la diferencia es que:
.Members muestra TODOS los miembros (incluido el 'All') ordenados.
mientras que .Children extrae solo los 'hijos' dependientes igualmente ordenados.
Veamos un ejemplo... Crearemos un par de conjuntos, usando la función CONJUNTOCUBO en las celdas J4 y M4.
En J4 insertamos:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"[Tabla1].[País].Members";
"paises")
y en M4:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"[Tabla1].[País].Children";
"países")
Para verificar cuales osn los miembros que componen cada Conjunto creado podemos, en primer lugar, obtener un conteo de éstos, con la función RECUENTOCONJUNTOCUBO aplicado sobre nuestras celdas J4 y M4 (donde habíamos definido los conjuntos).
Y por otra parte, listar los miembros con la función MIEMBRORANGOCUBO.
En J7 podemos escribir:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";J4;SECUENCIA(RECUENTOCONJUNTOCUBO(J4)))
donde vemos que los miembros listados son: 'All', 'DE', 'ES', 'FR', 'PT'
En M7 podemos escribir:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";M4;SECUENCIA(RECUENTOCONJUNTOCUBO(M4)))
donde vemos que los miembros listados son: 'DE', 'ES', 'FR', 'PT'
Un uso estándar de este listado de miembros podría ser aplicar sobre ellos la función VALORCUBO.
En K7 podemos escribir:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[Sum_Ventas]";$J7)
donde vemos que para cada miembro, obtemos el resultado de nuestra medida 'Sum_Ventas'... obviamente para 'All' tenemos el total acumulado de 'todos' los miembros.
Otro ejemplo que podemos mostrar es cómo conseguir datos filtrados por fechas, por ejemplo.
Supongamos que queremos obtener los datos de ciertos años, entre el 2013 y 2017...
Para ello crearemos un CONJUNTO y en J16 escribiríamos:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"[Calendar].[Date Hierarchy].&[2013]:[Calendar].[Date Hierarchy].&[2017]";
"Fechas-13-17")
Podemos averiguar el número de miembros únicos en ese intervalo de tiempo... que debes fijarte se ha expresado como:
inicio : fin (ojo que equivaldría a >=inicio y <=fin).
En K16 añadimos:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[Sum_Ventas]";J16)
que nos devuelve el acumulado de las Ventas (obtenido de nuestra 'medida' del Modelo de datos) para los miembros del CONJUNTO recién creado (6.845,18 para nuestro ejemplo).
para comprobarlo, por si aún no te fías de la seguridad de estos cálculos, podemos construir una tabla dinámica como la siguiente y aplicar los filtros correspondientes:
Otro cálculo muy interesante lo podemos realizar empleando la expresión .Parent, que devuelve el 'padre' (lo contrario de '.Children') sobre un miembro en concreto.
Por ejemplo en la celda L16 escribimos:
=VALORCUBO("ThisWorkbookDataModel";
"[Measures].[Sum_Ventas]";
"[Calendar].[Date Hierarchy].&[2010].Parent")
que nos devuelve el acumulado de ventas para TODOS los años... esto es, a pesar de indicar un miembro en concreto, una fecha en particular: 2010, al indicar que deseamos su .Parent, recuperamos la totalidad de los años (17.320,79).
Un paso más allá consistiría en combinar este conjunto de años 2013-2017 con otro conjunto de comerciales: ANA+EVA.
Así creamos un nuevo conjunto de dos miembros, en la celda J21 añadimos:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"{[Tabla1].[Comercial].&[ANA],[Tabla1].[Comercial].&[EVA]}";
"ana+eva")
fijémosnos en que forma hemos indicado uno y otro... entre llaves y separados por coma (como una matriz de elementos).
Igualmente podríamos haber escrito en un par de celdas los nombres ANA y EVA (en J24:J25). Y haber creado el siguiente conjunto referido a esas celdas... =CONJUNTOCUBO("ThisWorkbookDataModel";
"[Tabla1].[Comercial].["&$J$24:$J$25&"]";
"ana+eva")
En cualquiera de ambos casos, cruzando los miembros 'Años' de .Parent con el conjuntos en J21 de ambos comerciales (ANA + EVA) obtendríamos un resultado ajustado (10.484,80).
Como se comprueba con nuestra tabla dinámica (de momento sigo demostrando la fiablidad de esta formulación):
Terminaré el post de hoy con algunas otras funciones MDX con las que podemos trabajar dentro del contexto de nuestras funciones CUBO:
.LastChild
.FirstChild
.Lead
.Lag
.NextMember
.PrevMember
Son facilmente comprensibles...
.LastChild nos devuelve el último miembro, el último 'hijo' del conjunto.
.FirstChild nos devuelve el primer miembro, o 'hijo' del conjunto.
.NextMember nos devuelve el siguiente miembro desde el especificado.
.PrevMember nos devuelve el miembro previo o anterior del conjunto.
.Lead y .Lag :
Recuperamos la posición de un miembro dentro de un nivel.. sabiendo que la numeración está en base cero.
Si especificamos .Lead(0) la función nos devolvería el miembre específicado.
Si a la función .Lead le aplciamos un número o posición negativa devuelve el miembro anterior.
Por tanto .Lead(1) es equivalente a la función .NextMember; y .Lead(-1) equivale a .PrevMember
Las funciones .Lead y .lag son similares, excepto porque .lag va en dirección contraria que .Lead. Es decir, .Lead(n) equivale .Lag(-n).
Unos ejemplos sencillos que nos permiten ver su funcionamiento:
Las fórmulas empleadas en el rango O2:O7 son:
En O2 =MIEMBROCUBO("ThisWorkbookDataModel";"[Tabla1].[Año].LastChild")
En O3 =MIEMBROCUBO("ThisWorkbookDataModel";"[Tabla1].[Año].FirstChild")
En O4 =MIEMBROCUBO("ThisWorkbookDataModel";"[Tabla1].[Año].[2015].Lead(-1)")
En O5 =MIEMBROCUBO("ThisWorkbookDataModel";"[Tabla1].[Año].[2015].Lag(-1)")
En O6 =MIEMBROCUBO("ThisWorkbookDataModel";"[Tabla1].[Año].[2015].NextMember")
En O7 =MIEMBROCUBO("ThisWorkbookDataModel";"[Tabla1].[Año].[2015].PrevMember")
Funciones muy útiles para poder referirnos a distintos miembros del conjunto...
Por ejemplo, si queremos comparar un periodo con el anterior.
Veamos la distribución de nuestras fórmulas en el minireport.
En la celda N12 escribimos:
=CONJUNTOCUBO("ThisWorkbookDataModel";
"[Calendar].[Date Hierarchy].&[2013]:[Calendar].[Date Hierarchy].&[2017]";
"Fechas-13-17")
que estará compuesto, como ya vimos por los miembros entre los ejercicios 2013 y 2017.
En N18 listamoms dichos miembros de nuestro conjunto con la fórmula:
=MIEMBRORANGOCUBO("ThisWorkbookDataModel";
$N$12;
SECUENCIA(RECUENTOCONJUNTOCUBO($N$12)))
Por otra parte definimos un nuevo conjunto, también visto, con los comerciales ANA y EVA, así en O11:
=CONJUNTOCUBO("ThisWorkbookDataModel"; "{[Tabla1].[Comercial].&[ANA],[Tabla1].[Comercial].&[EVA]}"; "ana+eva")
Escribimos nuestros encabezados como textos planos en O12:Q12
Actual // Y-1 // Todos comerciales
Y para retornar los importes de las ventas de cada año acumulados para ANA y EVA añadiremos en O13
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[Sum_Ventas]";N13;$O$11)
función con la recuperamos los valores de nuestro cruce de cubos...
Para recuperar las ventas acumuladas del periodo previo usaremos la función MDX .Lag(1) en P13 y siguientes:
=VALORCUBO("ThisWorkbookDataModel";"[Measures].[Sum_Ventas]";"[Calendar].[Date Hierarchy].&["&$N13&"].Lag(1)";$O$11)
esto es, ventas del año previo acumulados de los dos comerciales ANA y EVA.
Finalemente, para cada año del periodo de estudio 2013-2017, obtendremos el dato de ventas acumulado de TODOS los comerciales... En Q13 añadimos:
=VALORCUBO("ThisWorkbookDataModel"; "[Measures].[Sum_Ventas]"; N13; "[Tabla1].[Comercial].&["&MIEMBRORANGOCUBO("ThisWorkbookDataModel";$O$11;1)&"].Parent")
Donde hacemos una llamada a la totalidad de los comerciales con .Parent, a partir de un elemento (del primer miembro) del conjunto de comerciales...
Podemos comprobarlo en la siguiente tabla dinámica:
Continuaré en entradas del blog siguientes ahondando en este mundo de las funciones CUBO y este lenguaje MDX.
No hay comentarios:
Publicar un comentario
Nota: solo los miembros de este blog pueden publicar comentarios.