Encontramos muchas veces Excel como herramienta que los usuarios utilizan para pre cocinar datos, crearse informes, navegar cubos… En esta entrada voy a compartir una experiencia reciente, en un escenario en el que Excel es la aplicación cliente para mostrar datos de un cubo, algo bastante común. Lo que no resulta tan común es Excel muestre un error cuando puedo ejecutar la misma consulta MDX en SQL Server Management Studio.
Para los ejemplos vamos a utilizar la base de datos OLAP Adventure Works DW 2008R2 que puedes descargarte desde Codeplex y una instancia de SQL Server Analysis Services 2008R2, pero el planteamiento es válido para las versiones posteriores a SQL Server 2000.
Escenario
En el cubo hemos creado la medida calculada [Ventas (últimos 12 meses)] en la que mostramos el acumulado de la medida [Sales Amount] (Suma de Ventas) de los doce meses anteriores al miembro actual utilizando la jerarquía [Calendar] de la dimensión [Date]. En caso de no filtrar por ningún miembro, es decir, si el miembro de la jerarquía [Calendar] es [All periods] mostrará el acumulado para los doce meses anteriores a la fecha actual:
CREATE MEMBER CURRENTCUBE.[Measures].[Ventas (últimos 12 meses)]
AS case when [Date].[Calendar].CurrentMember is [Date].[Calendar].[All Periods]
then sum((
StrToMember("[Date].[Calendar].[Month].&[" + cstr(year(now())) + "].&[" + cstr(month(now())) +"]").lag(12)
:StrToMember("[Date].[Calendar].[Month].&[" + cstr(year(now())) + "].&[" + cstr(month(now())) +"]").lag(1)
),[Measures].[Sales Amount])
when [Date].[Calendar].CurrentMember.level is [Date].[Calendar].[Calendar Year]
then ([Date].[Calendar].CurrentMember.lag(1),[Measures].[Sales Amount])
when [Date].[Calendar].CurrentMember.level is [Date].[Calendar].[Month]
then sum([Date].[Calendar].CurrentMember.lag(12):[Date].[Calendar].CurrentMember.lag(1),[Measures].[Sales Amount])
when [Date].[Calendar].CurrentMember.level is [Date].[Calendar].[Date]
then sum([Date].[Calendar].CurrentMember.Parent.lag(12):[Date].[Calendar].CurrentMember.Parent.lag(1),[Measures].[Sales Amount])
end,
VISIBLE = 1 ;
Anótense que en para el cálculo del miembro [All Periods] se toma el año y mes actual para generar la segmentación de la dimensión de tiempo.
Si utilizamos Excel para mostrar la medida sin añadir la dimensión de [Date], el resultado será el siguiente:
Cuando Excel muestra #Value! en la celda representa un error.
Mi primer paso fue intentar obtener un mensaje más detallado acerca del error. Para obtener la consulta que está realizando Excel en el cubo, abrimos SQL Profiler y conectamos a la base de datos OLAP. Con las opciones por defecto al conectar a una instancia de Analysis Services tenemos los eventos necesarios para capturar la consulta (Queries – Query Begin y Query End):
SELECT NON EMPTY Hierarchize({DrilldownLevel({[Date].[Calendar].[All Periods]},,,INCLUDE_CALC_MEMBERS)})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS
FROM [Adventure Works] WHERE ([Measures].[Ventas (últimos 12 meses)])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
Normalmente si ejecutamos una query MDX en SQL Server Management Studio, en la celda en la que se produce el error muestra el valor #Error y muestra un mensaje, así que ahí vamos. Abriendo una consulta MDX conectada al cubo Adventure Works, copiamos la consulta que he obtenido en el SQL Profiler y ejecutamos:
Nos devuelve los resultados sin ningún error, o eso parece.
Entonces, ¿que está pasando? ¿por qué devuelve resultados distintos la misma consulta en diferentes clientes?
Aislando incidencias
1. Distintos resultados entre clientes
Después perder algo de tiempo dándole vueltas a ideas raras y buscando las diferencias en la consulta que era la misma para los dos clientes, tomé aire y comencé por el principio: “Vamos a conectarnos a Analysis Services”…. y aquí hubo que parar. Cuando se realiza una conexión en Excel a un origen de datos de Analysis Services establecemos valores básicos como nombre de la base de datos OLAP, las credenciales para la conexión y la opción de almacenarlas, y el cubo al que queremos conectar. Pero la cadena de conexión que genera Excel guarda otras propiedades que agrega de forma automática y que además no se pueden cambiar. Entre ellas está la propiedad “MDX Missing Member Mode” establecida a Error:
Cuando realizamos una conexión desde SQL Server Management Studio no establecemos valor para esta propiedad, por lo que actúa el valor por defecto establecido en diseño en cada dimensión. Para equiparar el comportamiento de SQL Server Management Studio con Excel debemos añadir las mismas propiedades a la conexión (MDX Missing Member Mode=Error;):
Entonces la consulta ofrecerá los mismos resultados:
2. #Error en la consulta MDX.
Una vez resuelta la parte de los distintos resultados en diferentes clientes utilizando la misma consulta, queda la parte otra parte ¿por qué hay un error en la consulta cuando se activa la propiedad “MDXMissingMemberMode=Error”. En SQL Server Management Studio nos proporciona un mensaje relacionado con el error si hacemos doble clic sobre la celda que los muestra. En este caso nos anuncia que no existe el miembro &[2012].
Si examinamos los miembros del atributo [Year] de la dimensión [Date], comprobamos que efectivamente el año 2012 no existe. En la medida calculada [Ventas (últimos 12 meses)] intentamos obtener un miembro del cubo que no existe a través de la función StrToMember utilizando la fecha actual.
Solución
Existen varias aproximaciones para solventar esta situación:
Sobreescribir el valor de la propiedad MDX Missing Member Mode en Excel
La propiedad no se puede eliminar ni cambiar de las propiedades que añade Excel a la cadena de conexión, pero podemos sobreescribir el valor de la propiedad que nos afecta a través de las propiedades extendidas, que se aplican después de las propiedades especificadas en la cadena de conexión por Excel. Añadiendo Extended Properties=”MDXMissingMemberMode=Ignore”; a la cadena de conexión se soluciona el problema, haciendo que Excel devuelva un valor nulo cuando no encuentre el miembro de la dimensión. Esta es la opción más rápida ya que no requiere tocar el diseño del cubo.
Corregir el MDX utilizando la función ISERROR():
La opción más elegante es modificar nuestra medida calculada para controlar esta situación, añadiendo la función ISERROR() a nuestro código MDX:
CREATE MEMBER CURRENTCUBE.[Measures].[Ventas (últimos 12 meses)]
AS case when [Date].[Calendar].CurrentMember is [Date].[Calendar].[All Periods]
then case
when ISERROR(StrToMember("[Date].[Calendar].[Month].&[" + cstr(year(now())) + "].&[" + cstr(month(now())) +"]"))
then NULL
else
sum(
(StrToMember("[Date].[Calendar].[Month].&[" + cstr(year(now())) + "].&[" + cstr(month(now())) +"]").lag(12)
:StrToMember("[Date].[Calendar].[Month].&[" + cstr(year(now())) + "].&[" + cstr(month(now())) +"]").lag(1)
),[Measures].[Sales Amount])
end
when [Date].[Calendar].CurrentMember.level is [Date].[Calendar].[Calendar Year]
then ([Date].[Calendar].CurrentMember.lag(1),[Measures].[Sales Amount])
when [Date].[Calendar].CurrentMember.level is [Date].[Calendar].[Month]
then sum([Date].[Calendar].CurrentMember.lag(12):[Date].[Calendar].CurrentMember.lag(1),[Measures].[Sales Amount])
when [Date].[Calendar].CurrentMember.level is [Date].[Calendar].[Date]
then sum([Date].[Calendar].CurrentMember.Parent.lag(12):[Date].[Calendar].CurrentMember.Parent.lag(1),[Measures].[Sales Amount])
end,
VISIBLE = 1 ;
Conclusión
La propiedad MDXMissingMemberMode activada de Excel me ayudó a detectar la incidencia y mejorar el código, por lo que puede ser una buena práctica activarlo para diseñar consultas o expresiones MDX. Por otra parte, mientras buscaba información sobre este tema he encontrado una entrada de SQLCAT dónde figura esta propiedad y comentan modificándola mejora el rendimiento de Excel utilizando Analysis Services. Por tanto hay que estudiar cada escenario para determinar cual es la mejor opción para cada uno.
En cualquier caso espero que la información les sea de utilidad
Referencias de interés
http://msdn.microsoft.com/en-us/library/ms186627.aspx
