Thursday, December 4, 2008

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


Tuesday, November 11, 2008

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.

Monday, October 27, 2008

Varios seminarios de Business Intelligence

Hola a todos.

El mes pasado, estuve realmente ocupado preparando dos seminarios de Business Intelligence para ampliar la competencia de BI dentro de la empresa donde trabajo. Estos seminarios tuvieron lugar en Barcelona y en Andorra la Vella.

En Barcelona estuve hablando alrededor de tres horas acerca de las mejoras de SQL Server 2008 y el Case Study de GMAC RFC, la división financiera perteneciente a General Motors dedicada a hipotecas en España. En Andorra sin embargo, sólo tuve 45 minutos, así que acabé hablando de la situación actual del mundillo del BI en el mercado y algunos case studies representativos.

Monday, September 15, 2008

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.

Thursday, August 28, 2008

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.

Sunday, June 29, 2008

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

Wednesday, May 7, 2008

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


Thursday, April 24, 2008

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.

Thursday, April 3, 2008

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.

Thursday, March 27, 2008

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:

Monday, March 17, 2008

Microsoft Business Intelligence IT Professional

Last Friday, I past the exam 70-445 “Business Intelligence – Implementation and Maintenance”, becoming a Certified IT Professional developing Business Intelligence solutions.

The exam was finally not as difficult as I thought. I had read that this exam was more difficult to pass than 70-446, but both are similar, and someone who has past one of them, surely will pass the other one with a little effort.

A great help to pass this exam was the Microsoft Training Kit, which includes a set of possible exam questions and allows you to simulate an exam test.

The next step, I hope, will be the “Microsoft Office PerformancePoint Server 2007 Applications” certification number 70-556.

Thursday, March 6, 2008

First shot

This Wednesday I have finally decided to create my blog, mainly to share my professional experiences in my daily work in "Raona Enginyers", a software engineering company with a high degree of technological knowledge, especially with Microsoft technologies.

I currently work as a technology specialist in Business Intelligence projects, and split myself to be an Account Manager at the same time. This blog is specially intended to people that has a truly passion about this kind of job, like I have.