Integrar Sharepoint 2010 con Sql Server Integration Services

Últimamente me estoy encontrando muchos proyectos en los que tengo que conectarme a un portal Sharepoint 2010 para leer información e integrarla en un Data Warehouse. En Sharepoint 2007 existía un componente de Codeplex que hacía la integración realmente fácil, pero este componente ha dejado de funcionar si lo conectas a Sharepoint 2010. Espero que en algún momento alguien le eche un vistazo y arregle el error que da porque en principio los servicios Web de ambas plataformas continúan siendo iguales. Mientras tanto tendremos que programar la integración mediante el modelo de objetos de Sharepoint, lo cual implica:

- Por un lado es un fastidio porque tendremos que programary definir las columnas manualmente.
- Por otro lado, podremos personalizar y optimizar mejor la consulta a Sharepoint, controlar errores y hacer un logging más intenso que nos ayude en el desarrollo.

Sin más, nos ponemos manos a la obra. Primero de todo deberemos insertar en el flujo de datos un componente de tipo "Script Component" que sea origen de datos. A continuación editamos el componente y definimos una a una las columnas que nos traeremos de Sharepoint en la pestaña "Input and Output":

A continuación editamos el código del script, pero antes de escribir nada deberemos añadir las 2 librerías necesarias para trabajar con el modelo de objetos de cliente de Sharepoint (Microsoft.SharePoint.Client.dll y Microsoft.SharePoint.Client.Runtime.dll). Éstas se encuentran en el directorio "%ProgramFiles%\Common Files\Microsoft Shared\web server extensions\14\ISAPI".

Ahora ya podemos ir al método CreateNewOutputRows que el proxy genera por nosotros, y copiar el siguiente código:

using (ClientContext context = new ClientContext(Variables.iSiteURL))
{
context.Credentials = new NetworkCredential(Variables.vSPUsername, Variables.vSPPassword, Variables.vSPDomain);
List list = context.Web.Lists.GetByTitle(Variables.vMyListName);
CamlQuery camlQuery = new CamlQuery();
camlQuery.DatesInUtc = false;
camlQuery.ViewXml = "";
ListItemCollection listItems = list.GetItems(camlQuery);
context.Load(list);
context.Load(listItems);
context.ExecuteQuery();
foreach (ListItem listItem in listItems)
{
Output0Buffer.AddRow();
Output0Buffer.Id = listItem.Id;
Output0Buffer.Title = (listItem["Title"] == null ? null : listItem["Title"].ToString());
DateTime meetingDate;
string myDateStr = (listItem["Data_x0020_convocat_x00f2_ria"] == null ? null : listItem["Data_x0020_convocat_x00f2_ria"].ToString());
DateTime.TryParse(myDateStr, out myDate);
if (myDate != DateTime.MinValue)
{
Output0Buffer.MyDateColumn = myDate;
}
}
}

Para evitar problemas con los credenciales, utilizo variables para guardar el usuario y password que utilizaré para la conexión. En caso contrario debería añadir la cuenta que ejecuta Visual Studio, SQL Server Integration Services o SQL Server Agent a Sharepoint para que el proceso pudiera conectar correctamente.

A continuación declaro a qué lista tengo que contectar y la query CAML que utilizaré, la lista la guardo igualmente en una variable y la vista la mantengo al mínimo y en caso de querer optimizarla ya la tocaré. Si tengo fechas que leer y necesito que me las devuelva en hora local (no UTC) tengo que sobrescribir el parámetro DatesInUtc de la consulta. Finalmente ejecuto la consulta contra el servidor y proceso el resultado escribiendo una fila por cada elemento que la lista me devuelve. Como DateTime es un tipo de datos nativo, Sharepoint me devuelve MinValue cuando el campo en Sharepoint no tiene valor, así que es necesaria una conversión.

Y eso es todo, al ejecutar este componente veremos como nuestro componente recupera las filas de Sharepoint y podemos tratarlas en nuestra pipeline de la manera que queramos.

Visio Services con Microsoft SharePoint 2010

Mucho tiempo sin publicar un post lo sé, y no soy de poner excusas así que por faena. Acabo de editar un video que quería colgar hace tiempo pues es bastante espectacular por su sencillez y su facilidad de uso. Se trata de un nuevo servicio de Microsoft Sharepoint 2010 llamado Visio Services. Con Visio Services podemos publicar cualquier diagrama de Visio en la Web de forma muy sencilla, incluso con formas de Visio asociadas a una fuente de datos.

En este ejemplo voy a crear paso a paso un gráfico de un equipo de ventas con información de su estado de objetivos, desde cómo se crea el diagrama hasta su publicación en la home del departamento.

Espero que os guste y veáis el potencial de esta opción.

Artículo publicado en Channel Partner

Hola de nuevo.

Hace poco me pidieron que escribiera un artículo de opinión para una conocida web de expertos en distintos temas tecnológicos. El artículo trata sobre el futuro del Business Intelligence y las tendencias que marca el mercado. Lo podéis leer en el siguiente enlace:

http://www.channelpartner.es/Voz/201005040018/El-business-intelligence-te-ayuda-a-analizar-primero-y-decidir-despues.aspx

Espero que sea de vuestro interés.

Mejores prácticas en Analysis Services (SSAS Best Practices)

Hola de nuevo.

Hace poco tiempo estuve desarrollando una consultoría sobre una solución basada en Analysis Services para analizar cantidades bárbaras de información (del orden de miles de millones de filas en cada tabla). Como resultado de la consultoría hice un recopilatorio de buenas prácticas que toda solución completa de Analysis Services debería seguir. Os lo adjunto a continuación y espero que lo encontréis útil.

Diseño del Data Source View

• En el diseñador de cubos de Visual Studio, se recomienda en los DSVs separar en varios diagramas las tablas para una mayor claridad a la hora de diseñar. Una recomendación general es separar cada tabla de hechos con sus respectivas dimensiones relacionadas en un solo diagrama. Esto ayuda también a diseñar siempre el cubo con un esquema OLAP en mente.

Diseño de las Dimensiones

• Establecer la propiedad HierarchyVisible a false para esconder los atributos que participan en las diferentes jerarquías de las dimensiones. De esta forma se obliga a los usuarios a navegar con las jerarquías y se evitan posibles errores en los resultados de los informes.
• Asignar la propiedad AttributeHierarchyEnabled a false en aquellos atributos que no sean útiles para el análisis de información y permitir a los usuarios adquirir esta información mediante las propiedades del miembro o una acción de detalle, optimizando así el tiempo de procesado y el tamaño de los cubos.
• Los atributos con una cardinalidad cercana a la clave de la dimensión para asignar la propiedad AttributeHierarchyOptimizedState a false, y así evitar la creación de índices innecesarios. En estos casos es recomendable además, si no existe ninguna jerarquía de negocio con ese atributo, habilitar una jerarquía adicional utilizando una agrupación ad-hoc (por ejemplo para el atributo nombre, crear una jerarquía Inicial-Nombre con la inicial del nombre) para facilitar la navegación del usuario.
• Se recomienda además en las dimensiones con atributos con la misma cardinalidad que la clave primaria establecer la propiedad ProcessingGroup a ByTable para evitar consultas distinct en tiempo de procesado.

Relaciones con las tablas de hechos

• Repasar el diseño para encontrar posibles relaciones many-to-many existentes que puedan ser convertidas a relación indirecta, para optimizar el tiempo de procesado y tiempos de respuesta de consultas.
• Se recomienda también en el caso de tener claves foráneas nullables en el relacional utilizar una vista o una named query del DSV para enmascarar los Nulls y redirigirlos a un miembro desconocido “de negocio”, de forma que podamos diferenciar los hechos que se pierden por no tener informado este campo, y los errores de clave foránea que detecte la partición al procesar (2 miembros desconocidos, el nuestro y el de sistema).

Esquema de particionamiento de los grupos de medida:

• Se recomienda siempre particionar los grupos de medida. Las recomendaciones de Analysis Services sugieren particiones de entre 200Mb-3Gb con un máximo de 10-15 millones de filas, hasta un máximo de 2000 particiones.
• Se recomienda particionar las tablas relacionales de la misma forma que se particionan cada uno de los grupos de medida del cubo, para optimizar los accesos a disco en el procesado en paralelo de particiones.
• Se recomienda automatizar la creación y procesado de las particiones de las medidas, y asignar dinámicamente diseños distintos de agregaciones para particiones que varíen de uso con el tiempo (por ejemplo sobre la dimensión tiempo, asignar un esquema con más agregaciones a los últimos X meses mediante programación AMO).

Agregaciones

• Como ya se ha apuntado en el apartado anterior, se recomienda implementar más de un diseño de agregaciones para el conjunto de particiones de un grupo de medida que varíe con el uso a través del tiempo.
• Una vez desplegado habilitar el log durante un tiempo suficiente con un trabajo intensivo de los usuarios de negocio y revisar los diseños de las agregaciones para optimizar aquellas agregaciones más críticas.

Videos de Sharepoint Insights 2010

Hola a todos.

Últimamente en mi empresa, raona, hemos estado haciendo una serie de demos on-site con nuestros clientes de las nuevas características de análisis de información que traerán consigo los nuevos Microsoft Office 2010 y Microsoft Sharepoint Server 2010. He podido recopilar algunas de ellas en estos videos que ahora están disponibles vía Youtube. Os dejo el acceso directo a la playlist para que podáis verlos todos. Las demos son:

• Demo de Office Excel 2010 con Powerpivot, para el análisis de datos en memoria.
• Demo de Sharepoint Excel Services 2010, para publicación de contenido Excel en el portal.
• Demo de Sharepoint la Chart Web Part, para visualizar datos rápidamente en Sharepoint.
• Demo de PerformancePoint Services 2010, para el análisis avanzado y creación de cuadros de mando.

Las tenéis todas en el siguiente link:

Error en SSAS: The attribute key cannot be found when processing...

Este error es el más común que te encontrarás si te dedicas a crear cubos OLAP con SQL Server Analysis Services. Para mí el modo en que este error se corrige dará credibilidad o no a los datos presentados por el cubo.

Pero empezamos por el principio. Comienzas creando las dimensiones y grupos de medida del cubo, repasas las relaciones entre estas dimensiones y cada uno de estos grupos de medida, defines las jerarquías y relaciones entre atributos, incluso defines agregaciones y particiones para optimizar rendimiento y espacio en disco. En el momento en el que vas a procesar el cubo para ver los resultados el siguiente error se muestra:

The attribute key cannot be found when processing: Table: 'TableName', Column: 'ColumnName', Value: 'ErrorValue'. The attribute is 'AttributeName'.

El error en el fondo es muy sencillo. En la tabla de hechos TableName tienes una clave foranea ColumnName que apunta a un registro de la dimensión cuyo atributo es AttributeName, y este registro, seguramente una clave subrogada, no se ha encontrado en la dimensión. Hablando en términos relacionales, se ha roto la integridad referencial pues la clave foranea apunta hacia un valor de clave primaria que no existe.

En Analysis Services tenemos básicamente 4 modos de solventar esta situación:

Opción 1) Modificamos el valor no encontrado con un valor correcto correspondiente a un miembro de la dimensión. Reprocesamos la dimensión y posteriormente volvemos a procesar el cubo.
Opción 2) Modificamos la propiedad ErrorConfiguration del cubo o bien del grupo de medida, indicamos custom, y cambiamos la propiedad KeyErrorLimitAction a StopLogging y la propiedad KeyErrorAction a DiscardRecord. Finalmente procesamos el cubo.
Opción 3) Modificamos la propiedad ErrorConfiguration del cubo o bien del grupo de medida, indicamos custom, y cambiamos la propiedad KeyErrorLimitAction a StopLogging, la propiedad KeyErrorAction a ConvertToUnknown y la propiedad UnknownMember de la dimensión a Visible. Reprocesamos la dimensión y posteriormente volvemos a procesar el cubo.
Opción 4) Añadimos un miembro más a la dimensión llamado Unknown con una clave conocida (0 por ejemplo) y se la asignamos a este registro, de forma que se cumpla la integridad referencial. Reprocesamos la dimensión y posteriormente volvemos a procesar el cubo.

Y ahora las implicaciones. La opción 1 nos puede servir cuando siempre exista un valor correcto y conocido para la dimensión, aunque esto no siempre es válido. En cualquier caso será la opción predilecta si podemos aplicarla. La segunda opción es un suicidio profesional en toda regla. El registro será ignorado por el cubo lo cual quiere decir que no se incluirá en el cálculo de las medidas. En definitiva, el resultado que lanzará el cubo no coincidirá con el resultado calculado en el relacional y el proyecto (y el autor del mismo) perderá toda la credibilidad. La opción 3 es una buena opción a la vez que rápida, el registro se incluirá en un miembro especial de la dimensión llamado miembro desconocido y los resultados calculados por el cubo serán correctos. El único problema puede ser que los usuarios vean raro un miembro fantasma que englobe todos los valores perdidos en el procesado, aunque es cuestión de acostumbrarlos. Finalmente una de mis opciones preferidas, crear un miembro desconocido propio que nos permita tener controlados los resultados que asignamos a este fantasma. De esta manera podemos tener claro qué valores se agregan en el miembro desconocido y solventar los valores que realmente son errores.

En conclusión y por orden de preferencia utilizar la opción 1 siempre que se pueda, la opción 4 si tenemos valores desconocidos y queremos mantener el control total sobre los cálculos, la opción 3 si se quiere ir rápido (en fases de desarrollo y testing por ejemplo), y jamás la opción 2 a no ser que busquemos confundir al usuario o acabar nuestra carrera como ingenieros.

¿Cómo crear una pipeline de ventas? (Cross-Time Sales Pipeline Howto)

Desde que me dedico a la inteligencia de negocio y en especial a la integración de datos, me han preguntado muchas veces cómo crear un informe de pipeline a partir de un histórico de oportunidades de venta, o lo que es lo mismo, un informe dónde se cuenta cuántas filas de una tabla existen en un determinado estado en unos instantes de tiempo marcados (Cross-time), por ejemplo cada mes.

Después de buscar mucho y encontrar poco, la solución siempre ha sido crear una tabla de snapshots incrementales para cada instante del tiempo que contuviera la información básica con la que crear el informe. En ella utilizaría un procedimiento almacenado o bien un proceso SSIS para crear un bucle que me contase por ejemplo por cada mes y por cada estado, cuántas oportunidades de venta existían y guardar esta cifra en esta tabla.

Hace poco he vuelto a encontrarme con esta necesidad, y en este caso un conjunto de filas reducido y un buen motor y esquema relacional me ha permitido implementar una solución sencilla y directa sin pasar por una tabla temporal. La idea es la misma, se necesita un proceso intermedio que esta vez implemento con una sencilla función escalar en T-SQL, pero puede servir de base a cualquiera que se encuentre con este problema alguna vez.

La idea es utilizar la siguiente función en una consulta donde cruzamos la tabla de históricos con una tabla de dimensión de tiempo para recuperar los valores que buscamos para cada mes:

CREATE FUNCTION [dbo].[fnFINGetPipelineAmount](@date as datetime, @state as varchar(20))
RETURNS numeric(38,20)
AS
BEGIN
DECLARE @total numeric(38,20);
SET @total = (select sum(New_EstRevenueQ) from
(select rn=ROW_NUMBER() over (partition by ContractID, OpportunityId order by StartDate desc), *
from Pipeline
where ((month(@date) between month(StartDate) and MONTH(EndDate)) or (MONTH(StartDate) = month(@date) and EndDate is null))
and YEAR(StartDate) = year(@date)
AND (state = @state)
) as sq where sq.rn = 1)
RETURN @total;
END;

Ahora sólo necesitamos la dimension tiempo para invocar a esta función y recuperar la pipeline completa para un lapso de tiempo determinado, por ejemplo el año pasado.

SELECT month, dbo.fnFINGetPipelineAmount(Date, 'New') AS NewAmount, dbo.fnFINGetPipelineAmount(Date, 'Proof') AS ProofAmount, dbo.fnFINGetPipelineAmount(Date, 'Close') AS CloseAmount
FROM vwFIN_CubeDimTime
where year = 2009 and IsLastDayOfMonth = 1

Cuando el número de filas comienza a dispararse esta solución se vuelve demasiado costosa con facilidad. Para profundizar en este tema podemos leer una solución más compleja y efectiva en el whitepaper de Microsoft titulado "The many-to-many revolution" pág 57., de Marco Russo, el cual recomiendo a aquellos que les guste el mundo OLAP. Lo podéis encontrar aquí.