SSAS – Curiosidad Propiedad DisplayFolder y Excel

Buenas a todos, la verdad que hace mucho tiempo que no escribo y aunque si es cierto que estamos a tope de trabajo no tengo escusas e intentaré escribir algo más a menudo.

Comenzamos… En un momento dado recibimos una notificación de un cliente que nos indica que no puede conectarse a un cubo mediante el Excel de una base de datos OLAP que contiene otros 3 cubos más. Es decir, puede conectarse a todos los cubos de la base de datos OLAP menos a uno en concreto. Partiendo de esta comunicación analizamos si se trataba de una problema de permisos, sin embargo no era posible porque incluso para los usuarios administradores de la base de datos daba el mismo error.

Tras intentar ver el mensaje de error que daba al producirse la conexión nos encontramos que no reportaba ningún error simplemente al dar a «Aceptar» en la ventana de Importar Datos esta volvía a salir una y otra vez si darnos acceso al cubo desde el Excel:

Llegado a este punto nos planteamos conectar al cubo mediante otros clientes:

  • Management Studio –> Conecta correctamente
  • Informes de Reporting –> Conecta correctamente

Viendo que no tenía error ni en la conexión, ni haciendo un procesado entero del cubo y que podía conectar mediante otras herramientas cliente, opté por hacer una traza desde el SQL Server Profiler. Parecía que todo iba normal, sin embargo cuando conectaba con unos de los cubos que no me daba problemas desde Excel se observa que hay un conjunto de eventos que no aparecen cuando intento conectar al cubo que me da problemas:


En este momento me plantee que podría ser el nombre de alguno de los objetos del esquema del cubo, que igual tenía algún carácter que no era capaz entender y por ello no conectaba. Hablo del esquema del cubo porque en ningún momento llego a hacer una consulta de datos al mismo ya que no es posible conectar. Después de analizar diversos miembros calculados, nombres de medidas todo parecía normal y seguía con los mismos problemas así que decidí rehacer el cubo ya que se trataba de uno sencillo. Cual fue mi sorpresa que tras hacerlo de nuevo con sus miembros calculados y demás pruebo la conexión y funciona correctamente… Y me dije a mi mismo esto va ser que el esquema del cubo se peto…

Pues no!! Me había olvidado de incluir las traducciones para el lenguaje español e ingles. Añado todas las traducciones al nuevo cubo, pruebo la conexión y se vuelve a reproducir el problema de conexión… Pues no me quedó otra que ir poco a poco añadiendo y quitando traducciones.

Al final el problema viene porque tenía una medida que no tenia traducciones, sin embargo en su propiedad Display Folder si que tenía traducción y entiendo que el excel acababa liado con esta situación un tanto anómala…

Quitando la traducción de su propiedad DisplayFolder de las medidas o objetos que no tengan traducción asociada e implementando el cubo de nuevo la conexión desde el excel volvió a funcionar correctamente.

Algunos enlaces de interés sobre este problema:

Espero que les sirva de ayuda si alguien se encuentra con un problema similar.

Saludos, espero volver pronto!!!

SSIS – Lookup con búsquedas por fechas

Buenas a todos, hace unas semanas he tratado de hacer búsquedas por fechas mediante el componente lookup de SSIS, obteniendo en un principio muy malos tiempos de rendimiento.

Planteamiento del problema.-

Estas pruebas fueron llevadas a cabo en un SQLServer 2008 R2. En un punto de mi proceso de carga realizo una búsqueda sobre una tabla que contiene muy pocos registros, sin embargo son muchos los datos que se cargan alrededor de 5 millones en la carga inicial. Esta búsqueda se realiza mediante campos de fecha, la fecha que me viene en el flujo la busco sobre dos fechas que tiene la tabla de referencia. La tabla de referencia no es más que una tabla con Fecha Inicio y una fecha de fin, además de un valor asignado a ese registro. La idea no es más que traerme el valor correspondiente a ese periodo. Estas situaciones se pueden dar de múltiples formas y en este caso se trata de un escenario de ventas de productos que se le aplica un porcentaje en base a la fecha de la venta. Para realizar la búsqueda, como tengo que buscar el valor que le corresponde en base a la fecha de alta y fecha de baja modifico la consulta del componente lookup de la siguiente forma:

select * from (select * from [Tabla]) [refTable]
where (? between [refTable].[FechaAlta] and [refTable].[FechaBaja]) or [refTable].FechaBaja is null

En la tabla de ventas el último periodo se entiende como el actual, por lo tanto no tiene una fecha de baja, sino que es nula.

Al modificar la consulta se pone en parcial cache.

El rendimiento de este componente es muy malo. Actualmente tiene 25Mb de cache configurada y he probado otras «técnicas» como incluir el nuevo componente Balanced Data Distribuidor (del cual les recomiendo el siguiente post de Víctor Sánchez http://blogs.solidq.com/BICorner/Post.aspx?ID=74&title=SSIS+2008+Paralelismo+con+Balanced+Data+Distributor), que ha mejorado mucho el rendimiento, pero aun así para 5 millones de filas tarda en torno a 2 horas y pico.

Este tiempo me parece excesivo porque el ETL aumenta de 10 minutos de ejecución sin esa búsqueda a 2 horas y pico con la misma.

Solución.-

Después de buscar y probar diversas formas abrí una consulta en el foro de SSIS de la msdn: http://social.msdn.microsoft.com/Forums/es-ES/ssises/thread/644de7d2-f670-441a-bcaf-784fe24fc510, donde Miguel Egea, un auténtico crack me propuso la utilización de las Common Table Expressions (CTE) para darle un mejor rendimiento a este componente ante esta problemática.

De esa forma plantee la consulta del componente lookup de la siguiente forma:

Consulta:

with miconsulta as
(
select fechaalta, fechabaja, Porcentaje, fechaalta fecha from TablaReferencia
union all
select fechaalta, fechabaja, Porcentaje, fecha+1 from miconsulta where fecha<fechabaja
)
select * from miconsulta order by 4 option(maxrecursion 0)

No olvidar que hay que poner el full cache en el componente lookup.

De esta forma se consigue aumentar el número de registros sobre los que buscar, para así hacer una búsqueda sin utilizar la consulta modificada (que puse en la explicación) si no por el contrario hacerla directamente y al tener todos los días podré buscar directamente, consiguiendo una gran mejora en el rendimiento del componente.

Conclusión.-

Antes utilizando la consulta con el between: 02:02:59, es decir alrededor de 2 horas…

Ahora utilizando la técnica propuesta: 00:01:50, minuto y medio… Hay que tener en cuenta que el ETL también hace uso del Balanced Data Distribuidor.

 

Espero que les pueda ayudar… 🙂

Mis agradecimientos y bienvenida!

En primer lugar me gustaría agradecer a Alberto Díaz y a Rodrigo Corral la posibilidad que me han dado de tener un blog en Geeks.ms, para mí es un auténtico placer!

En segundo lugar,  trataré de personalizar mi blog entendiendo como va todo esto… 😉

A partir de aquí trataré de comentar todas aquellas experiencias que vivo a diario desarrollando con las tecnologías en .NET. Estas experiencias se basan en tecnologías de Business Intelligence de Microsoft abarcando desde los servicios que nos proporcionan productos como SQL Server 2008 R2, hasta SharePoint 2010 como plataforma de análisis y visualización.

A penas llevo tres años trabajando de lleno con estas tecnologías y es mucho lo que he aprendido de otros blogs, foros, etc, así que ahora he pensado que igual pueda aportar. Sin más espero que les parezcan interesantes mis próximos artículos y que les gusten!