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.

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.

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í.

Mapa con Report Builder 3.0 Maps y SQL Server 2008

Os adjunto un video de una demo que realicé en mis últimos eventos sobre Business Intelligence. Se trata de la creación de un mapa de ventas por territorio hecho con Report Builder 3.0 R2 CTP y una fuente de datos SQL Server 2008. En unos sencillos pasos podemos tener un informe para visualizar sobre un mapa los resultados de ventas de nuestra empresa. Más fácil imposible!!!

SQL Server 2008 R2

Hola a todos.
Hace unos días se liberó la CTP de Noviembre de SQL Server 2008 R2. Podéis acceder directamente mediante este link:
http://www.microsoft.com/sqlserver/2008/en/us/R2Downloads.aspx
Adicionalmente podéis descargar el complemento Report Builder 3 con nuevas y espectaculares funcionalidades. El acceso directo a la descarga es:
http://download.microsoft.com/download/5/4/D/54D3A5E3-71EF-45B7-BA9D-02F0A7C4ABB7/ReportBuilder3.msi
Os recomiendo instalaros Office Excel 2010 con Powerpivot para probar a fondo las nuevas capacidades de esta versión de Excel. Powerpivot es capaz de conectar a casi cualquier fuente de datos e integrar la información en la hoja de cálculo. Con los datos cargados en memoria, el usuario es capaz de definir relaciones entre los datos recuperados y construir estructuras de cálculo complejas como si de un cubo se tratara.
Puedes descargar la beta 2 de Office Excel en el mismo link que la CTP de SQL Server 2008 R2.

Crear una dimensión de tiempo (Time Dimension)

Hola a todos.

Recientemente me he visto en la necesidad de crear una dimensión de tiempo en un nuevo Data Warehouse en una empresa financiera, por lo que tuve que utilizar un script de SQL Server para crear la tabla y rellenarla. Os lo dejo por si os véis en mi misma situación, que tengáis como mínimo por dónde empezar:

CREATE TABLE TIME (
TIME_KEY smalldatetime,
[DAY] int,
[MONTH] int,
[QUARTER] int,
[SEMESTER] int,
[HALF] int,
[YEAR] int,
[WEEKDAY] int,
[WEEK_OF_YEAR] int,
[MONTH_NAME] varchar(20),
[QUARTER_NAME] varchar(2),
[SEMESTER_NAME] varchar(2),
[HALF_NAME] varchar(2),
[WEEKDAY_NAME] varchar(10),
[DATE] [smalldatetime],
PRIMARY KEY CLUSTERED (TIME_KEY)
)

SET DATEFORMAT dmy

DECLARE @startDate SMALLDATETIME
DECLARE @currentDate AS SMALLDATETIME
DECLARE @maxDate AS DATETIME
DECLARE @halfNbr AS INT
DECLARE @semesterNbr AS INT
DECLARE @monthName AS VARCHAR(50)
DECLARE @quarterName AS VARCHAR(2)
DECLARE @semesterName AS VARCHAR(2)
DECLARE @halfName AS VARCHAR(2)
DECLARE @weekDayName AS VARCHAR(10)

SET @startDate = '20040101'
SET @maxDate = DATEADD(YEAR, 25, GETDATE())
SET @currentDate = @startDate

WHILE (@currentDate < @maxDate)
BEGIN
SET @semesterNbr = CASE WHEN MONTH(@currentDate) < 5 THEN 1 WHEN MONTH(@currentDate) > 8 THEN 3 ELSE 2 END
SET @halfNbr = CASE WHEN MONTH(@currentDate) < 7 THEN 1 ELSE 2 END
SET @monthName = DATENAME(MONTH, @currentDate)
SET @quarterName = 'Q' + CONVERT(char(1), DATEPART(QUARTER, @currentDate))
SET @semesterName = 'S' + CONVERT(char(1), @semesterNbr)
SET @halfName = 'H' + CONVERT(char(1), @halfNbr)
SET @weekDayName = DATENAME(WEEKDAY, @currentDate)

INSERT INTO [TIME]
VALUES(@currentDate, DAY(@currentDate), MONTH(@currentDate), DATEPART(QUARTER,@currentDate),
@semesterNbr, @halfNbr, YEAR(@currentDate), DATEPART(WEEKDAY, @currentDate),
DATEPART(WEEK,@currentDate), @monthName, @quarterName, @semesterName, @halfName,
DATENAME(WEEKDAY, @currentDate), @currentDate)
SET @currentDate = DATEADD(DAY, 1, @currentDate)
END

Optimización del procesamiento de dimensiones (SCD o Slowly Changing Dimension) en SSIS

En un almacén de datos, es muy común tener que tratar con más de una dimensión, a menudo originadas desde varias fuentes de datos, y posiblemente todas ellas cambiando constantemente en el tiempo. Las necesidades del negocio hacen que muchas veces se quieran guardar todos los cambios que ha habido para consultas históricas.
Todas estas dimensiones cambiantes (SCDs) pueden complicar el proceso ETL, especialmente para las de tipo histórico (SCD tipo 2) donde se debe mantener un registro histórico de los datos cambiantes. En SSIS por suerte, tenemos un componente para procesar este tipo de dimensiones, incluido en las tareas del flujo de datos: el componente Slowly Changing Dimension Component (componente SCD en adelante).
Sin embargo, uno de los principales problemas que este componente presenta ha sido siempre su rendimiento. Para entender este problema vamos a echar un vistazo a su comportamiento:
Para cada fila de la pipeline de entrada, el componente lanza una consulta SQL para recuperar la fila existente en destino y buscar valores que cambien respecto de los que vienen en la pipeline. Si existe algún valor señalado como no-histórico, el componente simplemente actualizará la fila en destino con los nuevos valores. Si existe algún valor que cambie del tipo histórico, entonces se realiza una actualización del registro de destino existente y se marca como no válido, para después realizar una inserción de una nueva fila con la nueva versión de la fila.
Estamos hablando de una consulta de selección, de una consulta de actualización y de una inserción (ésta sólo para las filas con cambios históricos) para cada una de las filas de destino.
En este artículo describiré las diferentes aproximaciones que podemos tomar para optimizar el rendimiento de nuestros paquetes con procesamiento de dimensiones y así, optimizar la carga de nuestro almacén de datos.

Para ilustrar el ejemplo utilizaré las siguientes bases de datos:
• Una base de datos origen ‘SourceDB’, con una tabla ‘Product’.
• Una base de datos destino ‘DataWarehouseDB’, con una tabla ‘DimProduct’

Type 1 Slowly Changing Dimensions (no históricas)

Cuando trabajamos con dimensiones cambiantes de tipo 1, lo único que debemos hacer es actualizar las filas en las que detectemos algún cambio, sin necesidad de mantener ninguna información histórica. Si detectamos el cambio, la fila se actualizará y perderemos el valor que existía antes.

Usando el componente SCD de SSIS

Podemos construir un flujo de datos que implemente el escenario que hemos descrito con un componente SCD de SSIS. Podemos verlo en la figura 1:

Para cada fila que recupere el componente OLEDB que lee de la tabla ‘Products’ de la base de datos origen, el componente SCD ejecutará una consulta SQL contra la tabla ‘DimProduct’ para ver si ese producto ya existe. En el caso de que no exista redirigirá la fila hacia la salida ‘New Output’ y acabará con una inserción mediante el componente OLEDB de destino .En el caso de que exista, comprobará si la fila leída contiene valores diferentes a los de la fila en la pipeline. Si hay cambios redirigirá la fila hacia el componente comando OLEDB que actualizará la fila en destino con los nuevos valores.
Dado el comportamiento del componente podemos esperar un rendimiento pobre. A continuación veremos cómo podemos optimizar este flujo de datos sin excesiva complejidad añadida.

La solución con dimensiones cambiantes de tipo 1

Afortunadamente, podemos aumentar considerablemente el rendimiento de nuestra pipeline en el flujo de datos, sustituyendo el componente SCD por un componente de Lookup más otro de tipo Split.
Para conseguir esta optimización, borraremos el componente SCD del flujo de datos, dejando los componentes OLEDB generados debajo por el propio SCD. A continuación añadiremos un componente de Lookup para recuperar las filas de DimProduct, tal y como lo haría el componente SCD, es decir, recuperando únicamente las columnas de los valores cambiantes y las que compongan la clave de negocio.
Después añadiremos un componente de tipo Conditional Split. En este componente añadiremos una condición llamada ‘Actualizar’ con todas las comparaciones necesarias entre los valores cambiantes.
El nuevo flujo de datos debería quedar como el siguiente diagrama:

Hay que tener en cuenta que no se puede utilizar una única comparación entre los valores cambiantes para averiguar si ha cambiado, ya que nos pueden llegar valores nulos. Por ejemplo, si queremos hacer la comparación entre el campo ‘ProductCategory’ fuente y destino, la expresión que usaremos será la siguiente:

!((ISNULL(SRCPRODUCTCATEGORY) && ISNULL(DWDIMPRODUCTCATEGORY)) || (!ISNULL(SRCPRODUCTCATEGORY) && !ISNULL(DWDIMPRODUCTCATEGORY) && (SRCPRODUCTCATEGORY == DWDIMPRODUCTCATEGORY)))

Con esta expresión evitaremos que los valores nulos provoquen cambios no deseados, y sólo actualizaremos si el cambio se produce por dos valores diferentes. Podemos repetir esta comparación en la expresión de condición del componente Split.
Lo único que queda es conectar la salida de error del componente Lookup al componente OLEDB de inserción, y la salida de ‘Actualizar’ al componente OLEDB de actualización.
El resultado final en nuestro ejemplo es que el componente Lookup recuperará en una sola operación las filas de destino y las mantendrá en memoria durante la ejecución del flujo de datos. Después de comparar cada fila con el Dataset que mantiene en memoria, realizará de la misma forma la inserción o la actualización en función de si se encuentra la fila en destino o no. La diferencia radica en el número de transacciones, que se reduce enormemente ya que no es necesario recuperar fila por fila para hacer la comparación.

Type 2 SCD (Históricas)

This was the SCD 1 type optimization, but we should use the SCD 2 type every time that a dimension has an historical attribute. If an historical attribute has been changed then rather than just u0pdate the destination row with the new values, the OLEDB component must mark the existing row as out-of-date, and then insert a new row with the new values. For example, if we want to track all the pricing changes in time, we'll have to insert a new row every time that we detect that the prize has been changed in the source system.

Type 2 SCD with the SCD Component

En el caso de que queramos implementar algún campo histórico, deberemos utilizar la aproximación de las dimensiones cambiantes de tipo 2 (SCD 2). En este caso, si un atributo histórico ha cambiado entonces en lugar de tan sólo actualizar la fila de destino con el valor nuevo, el componente deberá marcar la fila actual como inválida, e insertar una nueva fila con los nuevos valores. Por ejemplo si queremos registrar todos los cambios de precio de un producto, deberemos insertar una nueva fila cada vez que detectemos que un producto ha cambiado su precio en la fuente de datos.
Podemos diseñar un flujo de datos con este comportamiento de forma muy sencilla gracias al componente SCD de SSIS. Si seleccionamos el atributo precio como un atributo histórico en el asistente del componente SCD, el flujo de datos resultante debería parecerse al siguiente:

La solución con dimensiones cambiantes de tipo 2

Los pasos para optimizar este componente en este caso son muy similares al proceso que hemos seguido en el caso del SCD de tipo 1. Deberemos borrar el componente SCD una vez completado el asistente, y añadir un componente Lookup que devuelva los registros de la tabla DimProduct y un componente de Split. El componente de Lookup deberá recuperar tanto los atributos cambiantes como los históricos.
Para controlar los dos tipos de cambio utilizaremos el component Split. Tendremos una condición para los campos históricos y otra para los campos cambiantes. El comportamiento esperado será que si existe un campo histórico que ha cambiado no será necesario continuar mirando si existe algún cambio de tipo 1 ya que irá igualmente a la rama del histórico. Para conseguir este comportamiento lo único que tenemos que hacer es colocar la condición del histórico en primer lugar, ya que el componente procesa las condiciones en orden. En la siguiente figura podemos ver cómo queda configurado el componente Split:

Como último paso, conectaremos las salidas convenientes a los componentes que había generado el SCD de SSIS, y el flujo de datos quedará listo. El nuevo flujo deberá tener un aspecto similar a este:

Comparación del rendimiento

Para comparar el rendimiento de los 2 flujos, vamos a ejecutar el paquete sobre la tabla Product de la base de datos AdventureWorks.
En la carga de datos, se obtienen los siguientes tiempos de ejecución:
Primera carga:
- Con componente SCD de SSIS: 73 segundos
- Con Lookup+Split: 65 segundos
Debemos tener en cuenta que los tiempos de validación del paquete son idénticos para ambos flujos, de ahí la escasa diferencia entre ambas ejecuciones.
Ahora cambiaremos algunos atributos en la categoría y precio de algunos productos de la tabla Product. El siguiente script se encargará de ello:

Los tiempos del proceso de actualización son:
- Con componente SCD de SSIS: 74 seconds
- Con Lookup+Split: 65 seconds
Los resultados no son tan apasionantes como esperábamos, pero debemos tener en cuenta que una dimensión de 500 filas está muy lejos de ser apasionante. Simularemos una dimensión de 100.000 filas cambiando la sentencia SQL de origen por la siguiente:

SELECT PRODUCTID, NAME, '1'+ PRODUCTNUMBER AS PRODUCTNUMBER, LISTPRICE, PRODUCTSUBCATEGORYID, MODIFIEDDATE FROM PRODUCTION.PRODUCT UNION ALL
SELECT PRODUCTID, NAME, '2'+ PRODUCTNUMBER AS PRODUCTNUMBER, LISTPRICE, PRODUCTSUBCATEGORYID, MODIFIEDDATE FROM PRODUCTION.PRODUCT UNION ALL
[...]
SELECT PRODUCTID, NAME, '200'+ PRODUCTNUMBER AS PRODUCTNUMBER, LISTPRICE, PRODUCTSUBCATEGORYID, MODIFIEDDATE FROM PRODUCTION.PRODUCT

Después de truncar la tabla DimProduct y repetir los tests de nuevo. Los resultados los podemos ver a continuación:
Primera carga:
- Con componente de SCD de SSIS: 11 minutos 8 segundos
- Con Lookup+Split: 2 minutos 10 segundos
Proceso de actualización:
- Con componente de SCD de SSIS: 178 minutos 05 segundos
- Con Lookup+Split: 121 minutos 23 minutos
Los resultados son mucho más significantes, y ahora sí que podemos ver la enorme diferencia entre un diseño y otro. Debemos tener en cuenta además que si tuviéramos realmente una dimensión de 100.000 filas el servidor debería mover muchas más páginas de disco y los resultados seguramente serían más significativos todavía.
Hay que fijarse en la diferencia abismal entre la primera carga y la actualización posterior. Esto es debido básicamente a la diferencia de coste que tienen ambas instrucciones para el servidor SQL Server. Realizar una actualización requiere localizar la página de disco donde se encuentra ubicada la fila, modificar su valor y escribir de nuevo la página.
Con esta premisa sería posible crear una optimización adicional, consistente en modificar el componente OLEDB de actualización y combinar un componente OLEDB de inserción en una tabla temporal con una instrucción JOINED UPDATE en otro componente OLEDB de actualización. Como ejercicio individual, os dejo que probéis los beneficios de esta aproximación.

Conclusión

Podemos conseguir una importante mejora del rendimiento en paquetes de procesamiento de dimensiones si implementamos estas optimizaciones en nuestros flujos de datos. El componente SCD de SSIS realmente supone una sobrecarga de trabajo para el proceso que puede ser mejorada de forma sencilla y rápida.
Por el contrario es justo destacar que esta mejora conlleva un aumento de la complejidad del paquete, y por tanto perjudica su desarrollo y mantenimiento.

Logging personalizado (Custom Logging) y mensajes de debug (Output List Messages) con scripts de SSIS

Uno de los problemas más comunes en el desarrollo de paquetes SSIS es que no se puede hace debug en cada paso que se realiza. Sólo en el flujo de control, en las tareas de script, se puede depurar con el debugger integrado del designer,pero en la mayoría de los casos no tendremos suficiente.
En un entorno de pruebas nos interesará, ya que no podremos hacer debug, como mínimo tener un buen sistema de logging para detectar posibles fallos. Veremos cómo se puede habilitar el logging en el paquete, registrar los mensajes de los componentes y conectarse con un proveedor de logging para guardar los mensajes en una base de datos SQL Server, un archivo de texto, etc, pero ¿y si queremos acceder a nuestros mensajes personalizados en el registro?

La respuesta es que podemos utilizar componentes y tareas de script para hacer un logging más personalizado y útil.
Opcionalmente, también podríamos visualizar de una forma diferente esta información mediante los eventos de Visual Studio. Así, los mensajes estarían disponibles en la vista de "Output List" de Visual Studio.

Para registrar estos mensajes no hay más que añadir estas declaraciones VB en nuestros scripts:

En los componentes 'Script Component' (Data Flow Tasks):

Me.Log("This is a logging message in a DFT", 0, Nothing)
Me.ComponentMetaData.FireInformation(0, "ComponentName", "This is an information message in a DFT", Nothing, Nothing, False)

En las tareas 'script tasks' (Control Flow):

Dts.Log("This is a log message in a Control Flow", 0, Nothing)
Dts.Events.FireInformation(0, "ComponentName", "This is an information message in a Control Flow", Nothing, Nothing, False)

El paso final es habilitar el logging en el paquete, mediante el menú SSIS -> Logging. Hay que añadir un 'Logging Provider' y comprobar que están habilitadas tanto los check ScriptComponentLogEntry para el 'Script Component' de DFT como la ScriptTaskLogEntry para la 'Script Task'.

Podemos ver los resultados en la siguiente captura de pantalla.

Let the Null Values pass through the Lookup transformation in SSIS

After the summer, here I am to communicate something that surprises me and it can be useful for someone (I hope).
When we use the lookup component to retrieve values from another OLEDB source, we always hope that all entries will match with a row in the lookup table. When this situation doesn't happen, we have the ability to let the row pass the lookup component with a null value in the lookup fields.
But what if we want to have a different behavior for the null values that come from the source pipeline? We can have the next situation:
We are translating some category codes from the products source table to a common set of new category codes. We have a translation table that will match every category source code with a new one. But we know that some rows from the source system come with a null value in the category field. For these rows, we want a 'Not Available' as translated value.
The only thing that we should do to get this behavior in the SSIS Lookup component is to perform this little trick into the lookup component statement:

select code, translation from CodeTable
union all
select null, 'Not Available'

As lookup component is able to match null values, the rows with a null value in the category field will be translated into the 'Not Available' value, just as we wanted.

SSIS, VSTS and Sensitive Information

Hello everybody.
I have been testing for a long time what is the best approach to share SSIS packages between developers in a wide developing team. The best solution for controlling versions of SSIS packages is, without a doubt, Visual Studio Team System. With this tool the developers have full control of versions, branches, documentation, etc.
But there's an issue when we share a SSIS package in a environment like this. SSIS encrypts all sensitive information about the package (passwords basically). By default, SSIS encrypts this sensitive information with a key that depends on the machine that contains the package. When the package is saved and another developer tries to edit it, SSIS tries to decrypt the package with the wrong key and fails.
There are two possible solutions to this issue:
1) Set the ProtectionLevel of the package to EncryptSensitiveWithPassword and manually enter a password and share it with all developers. This approach allows the programmers to validate the package in design time and save the sensitive information inside the package. This approach has the inconvenient that every time that a developer opens a package, it is mandatory to enter the correct password. If we have 10 packages opened in a SSIS project, the developer will have to enter the password 10 times before starting to program.

2) Set the ProtectionLevel of the package to DontSaveSensitive, use Package Configurations and set the DelayValidation property to True. With this approach the developers can save the sensitive information in XML configuration files, and there's no need to supply any password. Using package configurations is also considered a best practice by Microsoft experts.

In my opinion, the best solution in most cases will be the second one. In addition to the control over the sensitive information, we will have an easier way to move the package throughout environments (developing, testing, UAT, pre-production and production).

Remove repeating rows with SQL Server 2005

When we are performing Data Cleansing in a Data Warehouse environment, often we should take care of tables that has repeating rows (e.g. when there's no primary keys). There's no easy way to remove these repeating rows in a single query since SQL Server 2005, by the OVER statement.
I'll take a customers table to show how it works:

SELECT * FROM (
SELECT rn = row_number() OVER (PARTITION ON c.NIE ORDER BY c.DateModified), c.NIE, c.FirstName, c.Surname, ...
FROM Customers c)
WHERE rn = 1

This query will return to us only the last updated row of every customer, even if the source table had more than one row per customer and modified date.
We can also perform delete statements like this:

DELETE tbl FROM (
SELECT rn = row_number() OVER (PARTITION ON c.NIE ORDER BY c.DateModified) FROM Customers c) AS tbl
WHERE rn <> 1

PerformancePoint Server 2007 Training

The last week I had the opportunity to attend to a PerformancePoint Server 2007training, performed by the MVPs of Solid Quality Mentors Spain.

The experience (3 full days of theory and labs) was pretty exciting. I could learn the power of the Monitoring Dashboards, and the sophisticated engine implemented by MS in the Planning infrastructure.

But there is a quite large list of constraints that we should take into account when we're planning to implement a project with this software. Here we have some samples:

- It is not possible to reuse an existing Staging database, we're forced to use its own.
- It is not possible to reuse an existing Time Dimension, we'll have to build a new one via the business modeler.
- The tools provided by PerformancePoint Server to program the solution doesn't allow us to use any Source Code Control (neither VSS nor TFS).

Despite this several constraints, that I hope and supose that in the next versions of the software will disapear, the product have plenty of advantages. Thus, I'm excited to get involved into a PerformancePoint BI project. I hope this will happen soon.

Datetime transformation with SSIS

After doing a lot of failed transformation with some date columns in SSIS, with a very poor manually entered source data, I'll find the definitive solution to the datetime transformations from string.

I have included a script component into the dataflow to perform the transformation, with the following code:

Try
Row.ConvertedDate = DateTime.Parse(Row.Date.Substring(0, 2) + "/" + Row.Date.Substring(2, 2) + "/" + Row.Date.Substring(4, 4))
Catch
Row.ConvertedDates = DateTime.MinValue
End Try

With that approach you have the abbility of perform some advanced transformations in the Catch statement.

SSIS Error executing a package in a 64 bit server

Last week I get into trouble when I was developing a SQL Server Integration Services package to consolidate some information into a Data Warehouse. The package consists of taking some information from text files, performs some transformation with the data via a script task, and updates a SQL table. When I execute the package in the development environment, it does with no problems.
When I deploy the package into the production server, a four 64bit-processors machine, and executes it from a SQL Server Agent job, I got the following error:

- DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component ...

Digging and "googling", I discover, despite the confusing (or not?) message from SSIS, that the problem was that the script component of the package was pre-compiled for 32-bits and the server where I was trying to execute the package was a 64-bits one.

There is an option of the "Script Component" that allows to compile in execution time the script, but it's a requirement to pre-compile the script if you want to execute them in a 64-bits server. Amazing!

The workaround consists of executing the package with the dtexec utility instead of as a SSIS ordinary package. In all 64-bits SQL Integration Services servers, the SQL installer installs two versions of the dtexec utility, one for the 64-bits package and another one for 32-bits compatibility executions.

When defining the SQL Server Agent job that executes the process, we should change the job type to "Operating System" and type the command line arguments for the dtexec utility properly to execute the package.

The final command for the job was:

"D:\Program Files\Microsoft SQL Server (x86)\90\DTS\Binn\dtexec.exe" /DTS "\MSDB\TestSSIS32bits" /SERVER "." /CONFIGFILE "C:\Projects\TestSSIS32bits.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V

and when I press the "Execute" button the result was SUCCESSFUL: