Project REAL

Buenas a todos los miles de lectores de mi Blog (hay que ser optimista en la vida… jeje). Para todos aquellos que estén dando sus primeros pasos en el mundo de BI (Business Intelligence) les recomiendo que den un vistazo a Project Real. Project Real, como se deduce de su nombre, es el resultado de un esfuerzo conjunto entre Microsoft y numerosos e importantes partners (tales como Unisys y Scalability Experts entre otros) cuyo fin es el proporcionar a las empresas una serie de ejemplos cuasi-reales orientados a instruir sobre la utilización de “Best Practices” para la creación de este tipo de proyectos con MS SQL SERVER 2005.

Si te especializas en ETL o Reporting Services puedes encontrar conceptos y ejemplos muy interesantes al respecto. A continuación les dejo una serie de Links que pueden ser de ayuda.

• Project REAL - Home Page
• Project REAL - Best practices
• Project REAL - Analysis Services Technical Drilldown
• Project REAL - Business Intelligence ETL Design Practices

Espero les sean de utilidad estos links. Hasta el próximo post!!!

SSIS – Atributos Históricos tomando fecha desde nombre del Archivo

He regresado con mi 2º post sobre SSIS, espero el anterior les haya sido de utilidad. Este ejemplo es una continuación del anterior, por ello es importante que hayan respetado la nomenclatura y demás características del mismo. Aquí explicaré como a través de un SCD (Slowly Changing Dimension) podemos llevar un registro histórico de los datos de alumnos ingresados en el post anterior, tomando las fechas que poseen los nombres de los ficheros XLS. Para decirlo más claro, la TablaAlumnos va a contener un registro histórico de los cursos en los que han estado los alumnos, con su fecha de alta y baja respectiva para cada alumno y curso. En caso de que un alumno repita de curso, como los registros de los 2 ficheros XLS serán iguales, el SCD lo detectará y sólo se ingresará una vez.
Al igual que el post anterior, procederé a dividirlo en partes para simplificar su comprensión. Estas son:

1. Modificar los datos en último fichero XLS
2. Agregar componente Script Task para obtener la fecha desde el nombre del fichero
3. Agregar componente SCD
4. Ejecutar el proceso y analizar los resultados

1. Modificar los datos en último fichero XLS
Para que nuestro ejemplo funcione, debemos tomar el último fichero XLS (Alumnos 28022008.xls) y realizarle algunas modificaciones. En realidad debemos hacer sólo 2 modificaciones, a los alumnos con legajo 100 (Perez Mieguel Angel) y 102 (Juarez Martin Alejandro) y reemplazar en la columna “Curso” el valor 3 por el 2. Ello significa que esos alumnos repitieron de curso y las filas para esos 2 alumnos serán exactamente iguales en los archivos Alumnos 28022007.xls y Alumnos 28022008.xls


2. Agregar componente Script Task
El siguiente paso consiste en dirigirnos nuevamente al Package y en el Control Flow dentro del Foreach agregar un componente Script Task desde la ToolBox. Luego de agregado el componente, debemos unir su flecha verde con el Data Flow Task como muestra la siguiente figura.


Dentro de este Script, mediante código VB.Net obtendremos la fecha que figura en el nombre del fichero XLS que esté procesando en ese momento el Foreach.
Antes de mostrarles cómo configurar el Script, vamos a crear una variable llamada “varDate” de tipo DateTime, desde la solapa Variables del IDE.


Una vez creada la variable, hacemos click derecho sobre el Script Task para Editarlo. En la ventana de edición, seleccionamos la opción Script y y cliqueamos el botón Design Script…Esto abrirá una nueva ventana del VS en la cual sede quedar el código siguiente.

' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain Public Sub Main() ' Declaramos las variables que utilizaremos Dim vars As Variables Dim FileDate As String Dim varsDate As Variables 'asignamos a la variable local "vars" el contenido de la variable varFileName 'que obtiene el nombre del fichero XLS en el Foreach Dts.VariableDispenser.LockOneForRead("User::varFileName", vars) 'Procesamos la variable varFileName para obtener la fecha del nombre del fichero XLS 'con el formato dd/mm/aaaa FileDate = vars("varFileName").Value.ToString.Substring(vars("varFileName").Value.ToString.Length - 12, 2) + "/" + _ vars("varFileName").Value.ToString.Substring(vars("varFileName").Value.ToString.Length - 10, 2) + "/" + _ vars("varFileName").Value.ToString.Substring(vars("varFileName").Value.ToString.Length - 8, 4) 'instanciamos y asignamos a la variable varDate la fecha obtenida Dts.VariableDispenser.LockOneForWrite("varDate", varsDate) varsDate("varDate").Value = CDate(FileDate) Dts.TaskResult = Dts.Results.Success End Sub End Class

Nótese que el lenguaje utilizado es solamente VB.Net y se puede hacer Debug como cualquier aplicación en VS 2005.
Cerramos la ventana una vez que ingresamos el código, y damos Ok en la ventana de edición de Script Task. Con esto logramos que por cada vez que se recorra un fichero XLS del directorio correspondiente, la variable varDate tome el valor que figura en el nombre del archivo.

3. Agregar componente SCD
Llegó la hora de agregar el SCD (Slowly Changing Dimensión) que nos permitirá agregar los datos en la tabla TablaAlumnos conservando datos históricos. Para ello nos dirigiremos al Data Flow.
Antes de insertar el SCD será conveniente eliminar el OLE DB Destination existente, ya que el nuevo componente se encargará de agregar todos los componentes necesarios al finalizar de configurarlo.
Una vez eliminado el OLE DB Destination procederemos a insertar el SCD desde el ToolBox. Luego uniremos la flecha verde del componente Derived Column con el SCD. Haciendo click derecho, elegiremos la opción Edit…Allí nos aparecerá un asistente que nos permitirá configurar el SCD fácilmente.
En el primer paso daremos click en Next>, mientras que los demás pasos se detallan a continuación:

Paso Select a Dimension Table and keys
En este paso seleccionaremos la tabla, los campos a ser tenidos en cuenta para los cambios y la Key Business que en este caso será el campo Legajo. Damos Next > para ir al siguiente.


Paso Slowly Changing Dimensión Columns
Aquí es donde debemos especificar que columnas y qué tipo de cambios vamos a necesitar. Para nuestro ejemplo seleccionaremos todas las columnas disponibles y su Change Type como Historical Attribute. Damos Next > para ir al siguiente.



Paso Historical Attribute Options
Usaremos “Start and End Dates” para identificar los registros vigentes y expirados. Alli utilizaremos la variable varDate para setear los valores de los campos Registro_valido_desde y Registro_valido_hasta. Damos Next > para ir al siguiente.


Paso Infered Dimension Members
En este paso desmarcaremos el check box “Enable infered member support” y daremos click en Finish > para ir culminar con la configuración del SCD.
Para más información sobre SCD visite
http://msdn2.microsoft.com/en-us/library/ms141662.aspx

Una vez finalizado el asistente, se generarán una serie de componentes automáticamente, los que llevarán a cabo la tarea que nos propusimos al comienzo. El Package se verá de la siguiente manera.



4. Ejecutar el proceso y analizar los resultados
Ya estamos en condiciones de probar el Package, para ello les recomiendo borrar el contenido de la tabla TablaAlumnos para ver más claramente los resultados.
Ejecutemos el proceso presionando F5 y vayamos a ver el contenido de la tabla a la BD. El resultado debería ser el siguiente:


Analizando los datos de la tabla TablaAlumnos, podremos ver que contamos con 2 tipos de alumnos:
a) Los que pasaron todos sus cursos sin problemas año a año. Por ejemplo Legajo 101 Baresi Mateo, si analizamos sus datos veremos que en el año 2006 cursó 1º año, en el 2007 2º año y en el 2008 se encuentra cursando 3º año ya que el campo Registro_valido_hasta se encuentra vacío.


b) Los que repitieron el 2º año. Por ejemplo Legajo 100 Perez Miguel angel y analizando sus datos veremos que sólo tiene 2 registros y no tres como los que no repitieron de año. Veamos por qué. En el año 2006 cursó 1º y en el año 2007 cursó 2º año, el que todavía no ha concluido debido a que ha repetido el mismo.


Analizándolo desde el punto de vista técnico, podemos decir que el SCD detecta en este caso que existen registros exactamente iguales en los archivos Alumnos 28022007.xls y Alumnos 28022008.xls, y por ello no ingresa en la tabla el registro duplicado.
Aquí finaliza mi 2º post, espero les sea de utilidad y por cualquier duda o inquietud solo tienen que agregar comentarios. Saludos a todos!

SSIS – Ejemplo de Package con origen de datos Excel dinámicos

Estimados amigos lectores, en éste, mi primer post sobre SSIS, quiero presentarles un ejemplo que seguramente les será muy útil si están dando sus primeros pasos con esta herramienta de Microsoft. Intentaré explicar con un ejemplo sencillo como crear un Package de SSIS (SQL Server Integration Services) para importar los datos de todos los documentos XLS (MS Excel) que se encuentran en un directorio particular, procesarlos y luego enviar los datos obtenidos y procesados a una tabla de una Base de datos de SQL Server. En post posteriores iré ampliando este Package, agregando más transformaciones para complejizarlo y ajustarlo más a la realidad de la vida cotidiana, pero para empezar, creo que este ejemplo estará más que bien. Bueno, arranquemos…
El ejemplo constará de 3 partes con la finalidad de simplificar su comprensión. Estas son:

1. Crear Orígenes y destino de Datos
2. Creación del Package sin iteración
3. Creación de iteración para el Package

1. Crear Orígenes y destino de Datos
Lo primero que debemos hacer es contar con 3 archivos XLS (Excel) en una carpeta de nuestra PC. Les sugiero que utilicen los mismos nombres y path que yo para evitar problemas. Si miran la imagen de abajo, verán el path y los nombres de archivos. Por ejemplo:
C:\SSIS\Alumnos\Alumnos ddmmaaaa.xls
Respetar el formato del nombre será importante para futuros post de ampliación del ejemplo. Fíjense que lo único que difiere en ese nombre es el año.


La estructura (columnas) del los 3 archivos debe ser igual, no así los datos de los alumnos, los que sólo deben ser diferentes los valores de la columna Curso en cada uno de los archivos. Por ejemplo, el Alumno Baresi Mateo en el archivo Alumnos 28022006.xls tendrá en la Columna “Curso” el valor 1, en el archivo Alumnos 28022007.xls el valor 2, y en el archivo Alumnos 28022008.xls el valor 3. La estructura del XLS debe ser la siguiente:


La tabla donde se almacenarán los datos obtenidos y transformados para este ejemplo se encuentra en la base de datos Examples y se llama TablaAlumnos (Tener presente que tanto la BD como la tabla han sido creadas para este ejemplo). La estructura de la tabla sería la siguiente:


Donde RowID será la clave principal y autoincremetal.

2. Creación del Package sin iteración
Una vez que ya contamos con los archivos de “origen” de datos y la tabla de “destino” creada procedemos a abrir el SQL Server Business Intelligence Development Studio para comenzar a crear el proceso. Creamos un nuevo Proyecto de Integration Services, le ponemos de nombre IMPORT_XLS y lo guardamos dentro de la carpeta SSIS que creamos con anterioridad.


Luego de creado el proyecto, se nos abre en el IDE del VS el paquete por defecto, cuyo nombre es Package.dtsx. Allí es donde debemos apelar a nuestra creatividad y comenzar a “dibujar” nuestro proceso!!!
Lo primero que vamos a hacer es crear las conexiones de origen y destino para los datos. Para crear el origen de datos de archivos Excel nos situamos en el Administrador de Conexiones, allí hacemos lo siguiente:
- click derecho en la superficie, seleccionamos la opción New Connection…
- Seleccionamos la opción EXCEL, y hacemos clic en Add….
- Luego hacemos click en el botón Browse y seleccionamos el archivo deseado, en nuestro caso seleccionaremos el archivo Alumnos 28022006.xls ubicado en C:\SSIS\Alumnos
- Dejamos chequeada la casilla First row has column name, ya que nuestras columnas tienen encabezado.
- Hacemos click en el botón OK y ya habremos creado el “Origen” de datos. Se llamará Excel connection Manager.


Para crear el “Destino” de datos haremos lo siguiente:
- click derecho en la superficie, seleccionamos la opción New OLE DB Connection…
- en la ventana de configuración hacemos click en la opción New… para crear una nueva conexión a la BD.
- En la nueva ventana de Connection Manager debemos especificar los datos de nuestra conexión. Para nuestro ejemplo, los datos pueden observarse en la figura siguiente.

- Damos Ok en las dos ventanas y ya tendremos creada también la conexión de destino hacia la base de datos llamada localhost.Examples.

Ahora procederemos a la creación del Package propiamente. Lo primero que haremos es insertar en el Control Flow (Flujo de Control) un nuevo Data Flow task (Tarea de Flujo de Datos), arrastrando dicho objeto hacia la superficie que aún se encuentra vacía.
Posteriormente, hacemos doble click sobre el Data Flow task para poder agregarle la funcionalidad adecuada. Una vez dentro del Data Flow, agregamos una Excel Source, ubicada en la sección Data Flow Sources de la Toolbox arrastrándola a la superficie. Hacemos click derecho sobre el elemento agregado, elegimos la opción Edit… Allí nos aseguramos que la conexión seleccionada sea la que creamos con anterioridad y luego sólo seleccionamos la hoja del fichero XLS que contiene la información (en nuestro caso seleccionamos Hoja1$)


Lo siguiente que haremos es agregar el destino de datos, para ello arrastraremos el componente OLE DB Destination desde la Sección Data Flow destination. Una vez insertado en el Package, nos situamos sobre el Excel Source y arrastramos la flecha verde hasta el OLE DB Destination que acabamos de insertar.



Lo siguiente que debemos hacer es Editar el OLE DB Destination y donde debemos en la opción Connection Manager nos aseguramos que la conexión sea la que creamos con anterioridad (localhost.Examples) y seleccionamos la TablaAlumnos. Luego debemos ir a la opción Mappings para relacionar cada una de las columnas de Excel con sus respectivos campos en TablaAlumnos.



Es muy posible que les surjan errores de compilación debido a que no se pueden mapear campos con diferentes tipos de datos. En nuestro caso, los errores surgen en algunos campos. Lo que debemos hacer es fijarnos en el OLEDB Destination los tipos de datos que corresponden a cada campo (posicionando el cursor unos segundos encima del mismo) y luego agregar un nuevo componente para realizar el cambio en el tipo de datos.

El componente a agregar es Derived Column, con el cual podremos crear columnas derivadas con el nuevo tipo de dato. El Data Flow debe quedar como se muestra en la figura siguiente.



Luego debemos Editar el componente Derived Column insertado, agregando columnas derivadas para cada uno de los campos que requieren transformación, para ello vea la siguiente figura y observe que la propiedad Length de de la columna derivada “DerivedDivision” es 1. Eso es porque puede ir solo un carácter en este caso.



Una vez ingresadas estas las columnas, damos click en OK para confirmar estos cambios. Lo que resta en esta primera parte es volver a mapear los campos del destino con problemas de tipo de datos con las nuevas columnas derivadas que acabamos de crear. Para ello debemos editar el componente OLEDB Destination, ir hasta la opción Mappings y allí vincular las nuevas columnas con sus respectivos campos en el destino.


Si damos OK en la ventana, ya habremos concluido esta parte del ejercicio y podremos llevar a cabo la primera prueba. Presionamos F5 para que ejecutar el Package y una vez concluida la ejecución, si nos dirigimos a la tabla TablaAlumnos de la Base de Datos podremos ver los datos importados. Deberíamos tener los siguientes datos:




3. Creación de iteración para el Package

Vamos por buen camino, ya falta menos!!! Hasta ahora el proceso funciona correctamente pero con sólo un fichero XLS. Lo que haremos ahora es agregar las iteraciones para que recorra todo el directorio C:\SSIS\Alumnos, cargando de esa forma los datos de todos los ficheros en la tabla TablaAlumnos.

Debemos situarnos en el Control Flow donde agregaremos un Foreach Loop Container sobre la superficie donde ya se encuentra el Data Flow Task. Luego arrastramos éste último dentro del componente Foreach Loop Container.



Ahora debemos configurar el Foreach, para ello hacemos click derecho sobre el mismo, y elegimos Edit. En la ventana del editor nos situamos en la opción collections en donde debemos poner el path y el tipo de archivo que vamos a recorrer. Donde debemos poner “C:\SSIS\Alumnos” y “*.XLS” respectivamente.



En la opción Variable Mappings debemos agregar una nueva variable que se llamará varFileName, será de tipo String y su valor será el primero de los ficheros XLS que tenemos en el directorio “c:\SSIS\Alumnos\Alumnos 28022006.xls”


Lo último que resta es configurar la conexión para el origen de datos Excel Connection Manager que creamos con anterioridad. Para ello hacemos click derecho sobre él, Propiedades, y en Expressions asignamos la variable varFileName a las propiedades ExcelFilePath y ServerName.


Damos OK y listo! El proceso ya está concluido.

Si ejecutamos nuevamente el proceso (es conveniente eliminar el contenido de la tabla TablaAlumnos antes de cada importación para evitar duplicación de datos) veremos cómo han sido cargados los datos de los 3 ficheros XLS de la carpeta C:\SSIS\Alumnos.

Espero les haya sido útil este ejemplo y en sucesivos posts iré ampliando este ejemplo con más funcionalidades. Saludos a todos los lectores!