Últimamente he participado en un proyecto en el que la mayor parte de la carga de trabajo ha sido escribir MDX para Reporting, tanto a nivel de origen de datos (medidas calculadas) como en los propios informes (datasets parametrizados).Puede parecer una tontería, pero cuando escribes consultas de más de 30.000 caracteres te paras a cuestionarte como reducirla, donde implementar el código para que sea más usable o que alternativas existen para simplificar el que tienes. Así descubrí LinkMember. Esta función puede ser de poco valor para algunos por argumentos como la caída en rendimiento con su implementación o que su uso deriva de un mal modelado de los datos. Sin embargo, yo tengo cierto feeling con LinkMember ya que me ayuda a simplificar alguna de mis consultas MDX en ciertos escenarios sin que afecte al rendimiento de forma más grave que la solución anterior o incluso mejorándolo.

En esta entrada comprobaremos su funcionamiento, como afecta al rendimiento en las consultas y las alternativas que existen a su uso.

Para todos los ejemplos se ha utilizado la base de datos OLAP Adventure Works que puedes descargarte de Codeplex

LinkMember: cómo funciona.

LinkMember: Devuelve el miembro equivalente a un miembro especificado de una jerarquía especificada. (http://msdn.microsoft.com/es-es/library/ms146058.aspx)

Sintaxis: LinkMember(Member_Expression, Hierarchy_Expression)

Esta función es muy sencilla, utiliza la clave del miembro que se pasa como primera argumento para localizar el correspondiente en la jerarquía que representa el segundo argumento.

Está especialmente indicada para escenarios en los que se utilicen dimensiones de múltiples roles (role playing dimension).

Suele ser habitual que la dimensión Fecha tenga una sola estructura en el Datawarehouse (una sola tabla) y aparezca una sola vez en nuestro Data Source View. También suele ser común que se asocie a más de un campo de fecha por lo que en el uso en el uso de dimensiones, la asociaremos tantas veces con el(los) grupo(s) de medidas como fechas sea necesario mostrar para segmentar la información (Fecha pedido, Fecha Entrega, etc..). Ya tenemos una dimensión de base de datos jugando varios roles en el cubo.

Podemos ver un ejemplo de role playing dimension en el proyecto de OLAP de Adventure Works:

En el Data Source View podemos comprobar que desde la tabla de hechos de medidas Internet Sales Facts existen 3 vínculos con la tabla de dimensión Date, por los campos ShipDateKey, DueDateKey y OrderDateKey
En el uso de dimensiones observamos que se referencia 3 veces la dimensión Date, utilizando las distintas claves de la tabla de hechos

Al existir una sola dimensión de base de datos, la configuración de atributos y jerarquías será idéntica para cada rol de la dimensión, al fin y al cabo estamos ‘replicando’ su uso para varias fechas.

Volvamos a la función, que es el tema que nos ocupa, y escribimos una primera consulta MDX para comprobar que el funcionamiento de LinkMember es el adecuado en las dimensiones de cubo con las que vamos a trabajar en los ejemplos:

with
member [Fecha Entrega (link)] as
  linkmember([date].[calendar].currentmember,[delivery date].calendar).uniquename
member [Fecha Orden] as
  [date].[calendar].currentmember.uniquename

select {[Fecha Orden],[Fecha Entrega (link)]} on 0,
[date].[calendar].[date].members on 1
from [adventure works]

La función devuelve el miembro correspondiente de la jerarquía en la dimensión [Delivery Date] segmentando por la dimensión [Date], por ahora todo va como esperamos  🙂

Pongamos por ejemplo que queremos averiguar qué día se pusieron en distribución las ventas del día 26 Septiembre 2002 y en que día se entregaron:

select nonempty([Delivery Date].[Calendar].allmembers) on 0,
nonempty([ship date].[calendar].allmembers) on 1
from (select [Date].[Calendar].[Date].&[20020926] on 0 from [adventure works])
where [Measures].[Sales Amount]

 El resultado nos dice que de las ventas ordenadas el día 2002-09-26 se pusieron en distribución el día 3 de octubre y se entregaron el día 8 del mismo mes.

Ejemplo 1

¿Y si queremos ver las fechas de pedido en un eje y una medida que muestre la cantidad de ventas por fecha de orden y otra medida con la cantidad de ventas entregadas en esa misma fecha?

Aquí es donde podemos encontrar utilidad a la función LinkMember. Para seguir con el ejemplo anterior vamos a seleccionar el rango 2002-09-26 al 2002-10-08 de forma que podamos hacer el seguimiento de los resultados previos:

with
member [Sales by Delivery] as
([sales amount],linkmember([date].[calendar].currentmember, [delivery date].calendar))
, format_string='currency'

select {[sales amount], [sales by delivery]} on 0,
[date].calendar.members on 1
from (select ([Date].[Calendar].[Date].&[20020926]:[Date].[Calendar].[Date].&[20021008]) on 0 from [adventure works])

Parece que no son los resultados esperados. Hay varios puntos de observación:

• ¿Por qué aparecen los días con valor nulo?
• ¿Por qué aparecen los miembros [Q3 CY 2002] y [September 2002] a nulos y los miembros [Q4 CY 2002] y [October 2002] con valor para la medida [Sales by Delivery]?

Todo ello se debe a que la medida calculada devuelve el valor de [Sales Amount] en su intersección con el miembro correspondiente de [Delivery date] y con el miembro mostrado de la jerarquía [Date].[Calendar] (el miembro original) no existe, no hay valor. No se hace entrega ningún pedido el mismo día que se ordena.

En otras palabras, el miembro [September 2002], muestra null para la medida [Sales by Delivery] debido a que todas las entregas de los pedidos entre el día 26 y 30 de septiembre se realizaron en octubre. A nivel de día resulta más evidente y para verlo más claro podemos hacer la siguiente pregunta:

¿Cuánto de lo vendido el día 26-09-2002 se entregó ese mismo día?

select ([sales amount],[Date].[Calendar].[Date].&[20020926],[Delivery Date].[Calendar].[Date].&[20020926]) on 0
from [adventure works]

¿Qué hacemos para solucionarlo? Añadir en la medida calculada el miembro [All periods] de la jerarquía [Date].[Calendar] de forma que la segmentación del eje 1 no afecte al resultado:

with
member [Sales by delivery] as
   ([sales amount]
   ,[Date].[Calendar].[All Periods]
   ,linkmember(date.calendar.currentmember, [delivery date].calendar))
,format_string='currency'

select {[sales amount], [sales by delivery]} on 0,
[date].calendar.allmembers on 1
from (select ([Date].[Calendar].[Date].&[20020926]:[Date].[Calendar].[Date].&[20021008]) on 0 from [adventure works])

Los resultados muestran, a nivel de día, los datos que esperamos. Los niveles año, semestre y trimestre muestran el total de ventas para la cada nivel de la jerarquía en lugar de la suma de los días mostrados y como seguro has deducido es debido a la inclusión de [Date].[Calendar].[All Periods] en el cálculo.

Ejemplo 2

El siguiente ejemplo es más sencillo, sólo se implica a la dimensión [Date]. Nos piden un informe con un rango de fecha como parámetro que tenga como base el atributo [Date].[Date]. El informe debe mostrar la información en periodos trimestrales mostrando el total para el periodo, aunque se la fecha seleccionada como parámetro corresponda a la mitad del periodo.

La consulta en el query designer:

Con esta consulta hemos creado los parámetros y generado la estructura necesaria para el informe, pero en los datos mostrados se agregan sólo a los días seleccionados.

En esta ocasión vamos a utilizar la clave del atributo [Date].[Date] para encontrar el correspodniente en la jerarquía [Date].[Calendar]:

Vamos a modificar la consulta MDX y utilizar LinkMember para obtener el miembro equivalente en la jerarquía [Calendar] y posicionarnos en el nivel [Calendar Quarter of Year] correspondiente, ascendiendo dos niveles.

SELECT NON EMPTY {[Measures].[Sales Amount] } ON COLUMNS, 
NON EMPTY {([Date].[Calendar Quarter of Year].[Calendar Quarter of Year].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( 
SELECT (LinkMember(STRTOMEMBER(@FromDateDate, CONSTRAINED),[date].[calendar]).parent.parent : 
linkmember(STRTOMEMBER(@ToDateDate, CONSTRAINED), [date].[calendar]).Parent.Parent) ON COLUMNS
FROM [Adventure Works]) 
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE
, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

El resultado:

Rendimiento de LinkMember

Antes de comenzar con la batería de consultas para comprobar el tiempo de ejecución hacemos limpieza de la cache del cubo.

La primera consulta a ejecutar nos va a servir de tiempo base:

select [Measures].[Internet Sales Amount] on 0,
([date].[Calendar].[Date].members*[Customer].[Customer].[Customer].Members) on 1
from (select [Date].[Calendar].[Month].&[2002]&[9] on 0 from [Adventure Works])

Los detalles de ejecución de esta consulta:

Volvemos a limpiar la caché y modificamos la consulta anterior para incluir una medida calculada que utilice la función LinkMember

with member [Measures].[Test] as
    LinkMember([Date].[Calendar].CurrentMember,[Delivery Date].[Calendar]).name

select {[Measures].[internet sales amount],[Measures].[Test]} on 0,
([date].[Calendar].[Date].members * [Customer].[Customer].[Customer].Members) on 1
from (select [Date].[Calendar].[Month].&[2002]&[9] on 0 from [Adventure Works])

Vemos un incremento del 50% en el tiempo de ejecución

En este caso podemos sustituir la función LinkMember por StrToMember haciendo algunas modificaciones, antes de ejecutarla limpiamos la caché:

with member [measures].[Test2] as
    strtomember("[Delivery Date].[Calendar].[Date].&" + Right([Date].[Calendar].currentmember.UniqueName,10)).UniqueName

select {[Measures].[internet sales amount],[Measures].[Test2]} on 0,
([date].[Calendar].[Date].members * [Customer].[Customer].[Customer].Members) on 1
from (select [Date].[Calendar].[Month].&[2002]&[9] on 0 from [Adventure Works])

La misma consulta resuelta con StrToMember tarda un 67% más que LinkMember  en devolvernos el resultado.

Veamos el resumen

Estas pruebas nos indican que efectivamente el uso de LinkMember tiene un coste que hay que considerar en la construcción de consultas, pero la alternativa de StrToMember se resuelve con peor rendimiento.

Alternativas

Existen alternativas que podemos implementar directamente en MDX, como la función StrToMember que vimos en Rendimiento de LinkMember. También existe la posibilidad de modificar el modelo del cubo para obtener una relación Muchos-Muchos como explica Chris Webb en su blog.

Seguro que hay más alternativas a las que he mencionado, si conoces alguna te agradeceré que la añadas en los comentarios para mejorar el contenido.

Conclusiones

LinkMember puede ser de gran utilidad en algunos escenarios observando el impacto que pueda tener sobre el rendimiento, y puede ayudar a mantener tus consultas o expresiones MDX más legibles.

Referencias

http://msdn.microsoft.com/es-es/library/ms146058.aspx

http://richardlees.blogspot.com/2011/06/linkmember.html

http://cwebbbi.wordpress.com/2011/05/24/replacing-linkmember-with-a-many-to-many-relationship/

http://en.wikipedia.org/wiki/Dimension_(data_warehouse)#Role-playing_dimensions

http://msftdbprodsamples.codeplex.com/