SSIS Usando Data Profiling Task

Uno de los problemas más comunes a los que nos enfrentamos a la hora de integrar datos es conocer que es lo que tenemos que integrar. Podemos tener la estructura de los datos, las reglas de negocio, etc.. pero ¿que hay en las tablas? (tablas o cualquier otro origen).
Supongamos que tenemos que evaluar la usabilidad de los campos de una tabla para generar un documento de propuesta de atributos para una dimensión. ¿De que forma sabríamos si realmente sería un atributo útil?
Existen mil y un motivos para que los datos no sean los que esperemos, o lo que la organización espera. La calidad final de la información puede verse devaluada por inconsistencias en los contenidos.
¿Y por qué se degrada la calidad de los datos?

• Deficiencias en las validaciones de datos algunos sistemas (ERP, etc.)
  
• No se realizan chequeos contra datos maestros
  
• Los usuarios introducen cualquier cosa
  
• Etc.

En fin… el objetivo de este post es hacer perfiles de datos utilizando SSIS. A partir de la versión 2008, SQL Server provee de un nuevo componente para el flujo de control del diseñador de paquetes de Integration Services, el Data Profiling Task o tarea de perfilado de datos en castellano.

Esta tarea genera las siguientes métricas sobre los datos:

• Ratio de valores nulos por columna
  
• Distribución de longitud de la columna, disponible para las columnas de tipo char, vachar, nchar, nvarchar.
  
• Perfilado de patrón de expresión, devuelve patrones de expresiones regulares que sigue un porcentaje especifico de filas.
  
• Perfil de estadísticas, para los tipos numéricos y fecha. Devuelve datos como el mínimo y máximo, medio y desviación para las columnas de tipo numérico.
  
• Distribución de valores, que devuelve todos los valores distintos junto con el porcentaje que representa cada valor en el conjunto.
  
• Claves candidatas. Detecta que columnas son candidatas a ser clave de la tabla por su unicidad en el conjunto.
  
• Grado de dependencia funcional, proporciona un ratio con el grado de dependencia de una columna sobre otra.
  
• Inclusión de valores,

Utilizando este componente tendremos la capacidad de generar un archivo XML que nos facilita un análisis del contenido de una o varias tablas. Vamos a ver cómo hacemos eso.
Configurando Data Profiling Task
Creamos un nuevo paquete y añadimos el componente Data Profiling Task al flujo de control. Accedemos a la configuración y disponemos de tres secciones General, Profile Request (Solicitud de perfilado) y Expression.
En la sección general configuramos el fichero XML dónde volcará los datos. En esta sección aparece el botón Quick Profile o Perfil Rápido

Configuramos una conexión ADO.NET y seleccionamos la tabla o vista sobre la que se requiera la solicitud de perfilados. Posteriormente seleccionamos los tipos de perfilado que deseemos generar.
Si accedemos a la sección de configuración de Solicitud de Perfiles podremos revisar las solicitudes que hemos generado con el asistente.

Para optimizar la configuración de las solicitudes de perfiles, revisa la documentación de la MSDN http://msdn.microsoft.com/es-es/library/bb934043(v=SQL.100).aspx
La velocidad de ejecución de la tarea dependerá de la cantidad de datos existentes en la fuente, el número de solicitudes y la configuración de los perfilados que haya seleccionado.

Una vez generado el archivo XML podremos analizar los resultados con la herramienta Data Profile Viewer.

Recuerda que los archivos generados son XML y que pueden incorporarse a bases de datos fácilmente utilizando SSIS. ¿Te imaginas tener informes e indicadores en tu solución de BI que te informe sobre la variación de la calidad de los datos?
Pues a trabajar!
Microsoft tiene un video didáctico sobre el uso de estas herramientas: http://msdn.microsoft.com/en-us/library/cc952923(SQL.100).aspx

** Actualización
Navengando un poco he encontrado esta página http://informationqualitysolutions.com/page2/page10/page10.html

Un saludo!

SSIS Básico: Ajustando la longitud de una columna.

 

El objetivo de este post es orientar a los nuevos desarrolladores que comienzan a diseñar paquetes SSIS sobre la resolución de algunos problemas básicos que se nos pueden presentar en el trabajo del día a día. Recientemente alguien planteó la siguiente duda: Mi paquete carga datos a una tabla y en algunas ocasiones genera un error debido a la diferencia de longitud de la columna del flujo con la columna de destino. ¿Cómo soluciono este problema?

El problema se genera al mapear columnas del flujo de datos a columnas con un tamaño menor, ya sea en el flujo de datos o en un destino (tabla, fichero, etc..)

Existen dos formas (o más) para solventar este problema:

· Ajustar el tamaño de la columna en el flujo

· Ajustar el tamaño de la columna en destino

 

Como determinar cuál es el tamaño correcto para una columna

El tamaño de la columna debe ser igual o superior al valor más alto que esperemos obtener como contenido, si es una cadena de texto el número de caracteres, si es un campo numérico el valor más alto.

Si podemos analizar el origen de datos, es conveniente utilizar el Data Profiling Task para generar un informe de estadísticas sobre el contenido de los campos, que nos facilita datos como el porcentaje de nulos contenido en la columna, el valor más alto, el menor, etc..

Ver Post de Data Profiling Task.

Es posible que el contenido de la columna se construya dinámicamente, concatenando varios campos por ejemplo. En estos casos debemos tener una idea de cuál es el tamaño máximo esperado y configurar la columna de destino con esa longitud.

Para revisar el tamaño de las columnas en el flujo haga doble clic sobre algún conector (pipeline) en el flujo de datos y acceda a la pestaña Metadatos

Ajustando el tamaño de columna en destino

Si necesitas guardar toda la información que se genera en el flujo de datos para la columna, está claro que no tienes otra opción que modificar la columna de destino. Si es un fichero de texto, habrá que modificar la longitud de la columna en el administrador de conexión. Si es una tabla, modifica el campo mediante una consulta, etc…

Ajustando el tamaño de la columna en el Flujo (DataFlow)

Si no puedes o no te interesa modificar la longitud de la columna de destino tendrás que hacerlo en el flujo de datos, para lo que tienes dos (o más) opciones:

· Utilizar un data conversión component

· Utilizar un derived column component

1. Data conversion component (Conversión de datos)

http://msdn.microsoft.com/en-us/library/ms141706.aspx

Este componente, provisto en todas las ediciones de SSIS, tiene como fin exclusivo la creación de nuevas columnas basadas en las existentes modificando el tipo de datos, intentando convertir al tipo destino el contenido de la columna. Esto puede generar un error si el valor contenido no es convertible al tipo de destino. Por ejemplo, intentar convertir la cadena ‘@’ a un tipo numérico (I4), generará un error.

Para ajustar la longitud de la columna sencillamente acceda al campo longitud y modifíquelo convenientemente.

Pero, ¿y que pasa si el contenido del campo origen excede el tamaño que le estamos asignando a la nueva columna? El componente generará un error por Truncamiento, por lo que es muy recomendable configurar la salida de errores para estos casos. Para estas filas que exceden el tamaño de la columna debemos redirigirlas a un componente columna derivada y se recortar su contenido para ajustarlo al tamaño.

El diseño del paquete tiene que mostrar algo similar a la siguiente imagen:

En la columna dervidada configuramos la creación de una nueva columna, que llamaremos igual que la columna generada en el Data Conversion:

Finalmente y como se muestra en la imagen anterior, hay que unir los resultados desde la conersion de datos al Union All y desde la columna derivada al unión all.

Solo queda mapear la nueva columna al destino.

2. Derived column component (Columna derivada)

Préstele atención a este componente, es muy flexible y potente. Es capaz de modificar contenido de columnas o crear otras utilizando expresiones, valores de columnas, valores de variables, posee funciones de conversión de tipos de datos.

Puede resolver el problema sin utilizar el Data Conversion component. Puede realizar conversiones de tipos y modificaciones en el contenido en un solo paso.

Para ello utilice una expresión similar a la siguiente:

FixedColumn <= (DT_STR,200,1252) Substring(Column0,1,200)

SSIS Utilizando el RowCount

Uno no para de aprender cada día.

En un post de los foros de MSDN, en concreto el de SSIS en inglés, alguien pregunto lo siguiente:

“Estoy usando un componente RowCount para contar las filas del flujo y guardar el dato en una variable, posteriormente utilizo un Derived Column para crear una columna con el valor de la variable. La variable está siendo resetada a 0 cuando la asigno a la columna. ¿Existe algún motivo?”

Tal y como respondieron Todd C y Todd McDermid, expertos en SSIS, existen dos motivos:

1) El componente RowCount es de tipo síncrono: fila leida, fila expulsada es por esto que hasta que no pasen todas las filas no se establecerá la variable. El flujo habrá continuado y antes de que finalice el RowCount es posible que lleguen filas al Derived Column.

2) El componente Columna Derivada bloquea las variables en el evento Pre-Execute y no las desbloquea hasta el Post-Execute, por tanto las leerá con el valor anterior a la ejecución del data flow (obtenida en el control flow o en un data flow anterior).

Tuve que comprobarlo por mí mismo, que insolencia! Estaban claros los motivos, pero nunca me había topado con esta situación. Había usado RowCount y Aggregates para obtener conteos de filas, pero en ningún caso devolver el valor a todas las filas del flujo.

Creamos una variable del ámbito del flujo de datos, le ponemos el valor 99:

Diseñamos el flujo de datos añadiendo un origen con algunos datos, añadimos un componente RowCount al que asignamos la variable creada. Conectamos una columna dervidada a la salida del RowCount y creamos la columna RowCount a la que asignamos el valor de la variable. Conectamos un destino cualquiera a la salida y en el pipeline configuramos un visor de datos para revisar los valores de los campos

Pues tal como decían en el foro, el valor que contiene la variable es el establecido en el evento pre-execute del flujo de datos, que en este caso son los valores de diseño al no existir ningún componente que preceda al flujo de datos.

Para hacerle unas cosquillas al SSIS introduje un Script componente para mostrar un MessageBox el valor de la variable….

¿0? Y nuestro 99? Curioso al menos….

Me puse un poco nervioso e intenté otra aproximación para el problema. La propuesta de Todd C era romper el proceso en dos, obteniendo primero la variable en un data flow, pero sin guardarlo en destino y posteriormente hacer un update mediante un Execute SQL (en el control flow)

Pero ¿y si el destino no admite updates, como un archivo de texto o similar?

Haciendo algunas modificaciones al flujo de datos y cambiando el RowCount por un Aggregate, utilizando Sorts y un Merge Join… se puede!

Como se hizo:

1) En la columna derivada añadimos una nueva columna [JoinKey] con la expresión (DT_I1)1

2) Ordenamos el flujo por la columna [JoinKey]

3) Añadimos un Multicast

4) Una de las salidas las dirigimos a un Aggregate y lo configuramos para que cuente todas las filas agrupando por [JoinKey]. Tras el Aggregate volvemos a ordenar por [JoinKey]

5) Añadimos un Merge Join y a la entrada izquierda conectamos una la salida del multicast. A la entrada derecha conectamos la salida del Sort tras el Aggregate. Seleccionamos los datos de la izquierda y el número de filas de la izquierda.

Y así, perdiendo rendimiento en el paquete con los dos Sort, conseguimos añadir una columna al destino con el número total de filas procesadas.

---

Opinión sobre: Lider de opinión.

Pienso que es una buena cuestión con una fácil respuesta: se hace. Existe una serie de habilidades y capacidades personales que alguien puede tener de forma innata: sensatez y sentido común, facilidad de comunicación, percepción, empatía, reflexión, raciocinio, etc... Valores humanos. En principio es algo a lo que todos tenemos derecho pero

A) No todo el mundo quiere ser líder
B) No todos los que quieren, saben.

Que hace que alguien se erija como líder, nada. Es una decisión que se toma de forma natural y no explicita en muchas ocasiones, no hay que hacer nada. Sencillamente desarrolla tu actividad, ten en cuenta las opiniones de tu equipo, trata bien a las personas, toma decisiones sensatas...

Tengo la suerte de trabajar con un grupo de personas, de alguna manera soy responsable de la actividad que desarrollan, de facilitarles una organización de tareas y objetivos, para que puedan realizarlas de la forma que ellos determinen. No es el cómo, sino el qué debemos hacer.

Por tanto, puedo ser el Responsable de lo que realizan, pero quien dice que sea su Líder... ¿las encuestas de evaluación?
Hay un factor muy importante que va asociado a las "Jefaturas": Ser el jefe, no significa ser el líder.
No soy ningún especialista en sociología, pero mi manera de entenderlo implica una máxima "Las personas son un ente individual, pero existe el macro-organismo denominado Sociedad" Es un hecho, en algún momento todos hemos vivido o presenciado un fenómeno de masas (Eventos populares, sentimientos colectivos, etc.). Creo que el liderazgo es uno de estos fenómenos a pequeña escala. Es el reconocimiento por parte de la sociedad que te rodea (a nivel laboral, deportivo, etc...)

Un líder no se considerará como tal, él tuvo uno.

Según David Fichan: “El liderazgo personal se logra cuando el individuo emprende el camino trabajando su autoestima, creatividad, visión, equilibrio y capacidad de aprender.”

¿Qué bonito, no?
Entonces... ¿quién/cómo es un líder de opinión?
Existe un baremo con las 100 personas más influyentes del mundo en la clasificación de Social Networking, elaborado por la revista Times. Líderes de opinión que puede modificar la influenciar a las personas que siguen. Esta puntuación se ha determinado tomando como métricas la puntuación el número de seguidores en Twitter y conexiones en Facebook.

Entonces... ¿quién/cómo es un líder de opinión? Sinceramente, ni idea. Pero está claro que hay ciertos indicios que te pueden llevar a pensar lo contrario:

1) Si te llamas líder de opinión, no lo eres.
2) Si no eres transparente y no puedes validar la información que facilitas, no lo eres un líder de opinión.
3) Si el objetivo de publicar (blogs, artículos, etc...) tus conocimientos es, de manera descarada, promocionarte o promocionar algún producto ya te puedes imaginar.



Dejo abierto del debate para que hagáis vuestros comentarios

¿Y Dónde queda SSAS?

Leyendo los muchos comentarios que se extraen del keynote de SQL Pass (09-12 de noviembre)en el que Microsoft presentó su nueva versión de SQL Server, con nombre código Denali (ver post anterior). La gran sorpresa se llama Project Crescent y es en lo que van a invertir en esfuerzos y tiempo de desarrollo para implementarlo en SQL Server 2011. El caso es que esta origentación es un enfoque menos 'multidimensional' desde la perspectiva tradicional, en favor de una nueva técnología denominada BI Semantic Model (BISM) que se generan desde PowerPivot al servidor. La intención parece clara, atraer más profesionales al mundo ROLAP, ya que la prolongada curva de aprendizaje para SSAS desanima a mucha gente a implicarse en esta tecnología.

Esto significa que para la próxima versión no tendremos grandes mejoras en el motor SSAS, y en su lugar se concentrarán en PowerPivot y BISM (BI Semantic Model). Por tanto, queridos amis, habrá que empaparse del leguage DAX ¿e irle diciendo adiós al MDX?

En el próximo post hablaremos de como funcionará el BISM, arquitectura y demás hierbas, como dicen por ahí.


Un saludo!

Aprobado el exámen 70-448

Hola Amigos!
Llevaba algún tiempo preparandome el exámen con el Training Kit de Microsoft para esta certificación. La verdad que no esperaba obtener un 1000/1000.
Hoy es ciertamente un día de alegría para mi.

Aparte del exámen hemos conseguido un proyecto de BI importante, creo que no hay que darse pausa. Empiezo a preparame el 70-452 para conseguir el MCITP.
¿Cúantos MCITP de BI con SQL 2008 habrá en España?


Un saludo!





... no te vayas sin decir adios.

SQL Denali y Project Crescent

El pasado 09 de noviembre Microsoft presentó en el SQLPass la nueva versión de la familia SQL, codename Denali.
Entre alguna de las características más reseñables es la inclusión de Projet Crescent (no croissant http://blogs.msdn.com/b/bi/archive/2010/11/09/data-visualization-done-right-project-crescent.aspx). Es una ampliación del concepto Powerpivot: BI Self Services con grandes mejoras en visualización y analisis de datos ad-hoc. Según los videos que he visto... es para recogerse las babas.

EL SQL Denali CTP1 se encuentra disponible para descarga, pero no incluye Project Crescent.
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9

Otros links de interés:
SQLPass: http://www.sqlpass.org/summit/na2010/
SSRS Team Blog: http://blogs.msdn.com/b/sqlrsteamblog/archive/2010/11/09/a-glimpse-at-project-crescent.aspx


PD: Curioso... se pondrán de acuerdo GMC y Microsoft en los nombres de sus productos?
http://www.gmc.com/yukon/denali/index.jsp



Un saludo!



...no te vayas sin decir adios.

SSIS: Optimizando inserciones en SQL

El post anterior ya lo tenía preparado, y publicado en http://blogs.gsc.es, y mientras lo reproduje en este blog me vinieron a la mente algunos otros intentos de ganarle tiempo al tiempo. Alguna vez os habrá pasado, lanzas un proceso ETL que sabes es pesado de narices, y si tienes tiempo para prestarle atención a cómo pasan los segundos lo aprovechas para pensar cómo mejorarlo.

La idea inicial era hablar de cómo trabajar con destinos SQL y configurar los índices de una tabla de forma adecuada para usar BULK Insert, o Fast Load según el componente que se utilice, pero voy a poneros en situación.

El escenario es el siguiente:

• Servidor Origen SQL2008r2 con BD relacional como origen de datos, REL.
  
• Servidor Destino SQL2005Sp3 con BD relacional como destino de datos, DW. Mismo servidor para SSAS.
  
• Red interna de 100Mbps
  

Solicitan la modificación un datamart de un sistema de BI, recientemente migrado a SQL 2008R2, la inclusión de una nueva dimensión. Una vez realizada la consultoría pertinente sobre los requisitos de este nuevo objeto en la BD, se crea la tabla de dimensión en el datamart y se construye el paquete ETL necesario para mantenerla. Modificamos el diseño de la base de datos de Analysis Services creando la dimensión. Y hasta aquí todo va como la seda...

En tabla de hechos que se relaciona con la dimensión es necesario introducir un nuevo campo con valor por defecto... y aquí hubo que pararse pensar un poco más.
¿Introducir un campo en la tabla existente? Con más de 200 millones de registros (unos 200Gb) el proceso podía tomarse su tiempo y cómo siempre, no lo había. Además hay que decir que el campo existía en el modelo relacional, en los datos de origen, desde el principio de los tiempos así que el siguiente paso hubiera sido actualizar el campo mediante una consulta de update. No es factible.

¿Cuál es la forma más rápida de tener una tabla de 200M. de registros con un campo nuevo y actualizado?

Comando OLEDB? A no ser que sean 20 filas las que vayais a actualizar, no recomiendo la utilización de este componente que actualiza n campos fila a fila. 1 a 1.

Inserción en nueva tabla stage con el id de fila y el nuevo campo. Posterior update mediante consulta SQL utilizando un join por el id de fila (unique non-clustered).

No actualizar la tabla, sino crear una nueva con el nuevo campo y eliminar. Aqui volvimos a mirar si se había realizado el backup de la BD Destino.

Pues está claro. Nos quedamos con la tercera opción... Hay que recrear la tabla con el nuevo campo e insertar con la opción Fast-Load funcionando en las mejores condiciones. Se diseñó un paquete ETL que leyera el origen y el destino como fuentes, combinando los datos del modelo relacional y dimensional en memoria para, desde el campo de origen obtener mediante un lookup la clave surrograda de la dimensión correspondiente e insertando en la nueva tabla. Listo!
Pero.... las inserciones no se realizaban tan rápido como cabía esperar, dichosas filas perezosas!!! La tabla destino se había recreado tal cual, con índices clustered y non-clustered.

¿Cómo configurar la tabla para un rendimiento óptimo de un Bulk-Insert?
En la documentación del componente OLEDB Destination indican la utilización del Hit Order de las opciones de configuración, Alberto Ferrari tiene un buen post sobre el uso de estas opciones.
http://sqlblog.com/blogs/alberto_ferrari/archive/2007/04/08/making-fast-load-really-fast-on-clustered-indexed-tables-with-ssis.aspx
. Es cierto que si la tabla contiene índices agrupados (clustered) utilizar los hits de Fast-Load optimiza enormemente el rendimiento del componente.
¿Pero que hace el componente? Pues ni más ni menos que un bulk-insert desde memoria.

Siguiendo la documentación de Microsoft para optimizar operaciones de Bulk Insert (http://msdn.microsoft.com/en-us/library/ms177445.aspx) y observando los tiempos que publico Alberto en su blog, llegué a la conclusión de que la configuración optima para este caso era mantener el índice clustered de la tabla y eliminar el resto, utilizando el Hint ORDER del componente Destino OLEDB

El proceso diseñado ETL para hacer la carga se tomó poco más de 2 horas para llenar la nueva tabla.

Una vez lo sabes... a optimizar destinos!

Saludos.

SSIS: Optimizando inserciones OLEDB

Que los registros se graben en una tabla es (casi) el propósito principal de un paquete ETL. Tras combinar nuestras fuentes de datos y realizar las transformaciones correspondientes... sólo nos queda despejar de la ecuación ETL la L de LOAD.
Entre las tareas que pueden denominarse comunes en un proyecto de BI es la construcción de un datamart o datawarehouse, y las tablas hechos pueden contener un volumen considerable como para hacer inserciones de filas una por una.
¿De qué forma podemos optimizar las inserciones de estas filas sin evitar que se realice el chequeo de restricciones tales como una clave principal?... Si intentamos introducir en la tabla una clave duplicada genera una fila de error en el componente que podemos capturar y procesar.
Teniendo esto en cuenta, la solución pasa por añadir más destinos y anidarlos, conectando al siguiente componente destino la salida de error del anterior.

La siguiente imagen es una muestra de cómo conectar los destinos de mayor a menor tamaño de lotes:









De esta forma podemos tener inserciones de lotes de filas de distinto volumen y que se vaya trasladando cada lote en caso de no cumplir con las restricciones de la tabla al siguiente componente.
Mediante esta metodología intentamos introducir grandes lotes de filas y en caso de que alguna genere un error, pasa a un tratamiento más "fino", hasta llegar a la inserción de una fila por lote.
Lo único que hay que hacer es cambiar la configuración de nuestro componente OLEDB Destination modificando los siguientes parámetros:




Si el flujo de datos que tratamos siempre contiene un número considerable de duplicados, la optimización es mejor hacerla tras la carga y antes de la transformación. Esto lo veremos en el siguiente artículo.

En la siguientes capturas se puede apreciar la diferencia de tiempo que hay en la ejecución de un paquete con filas duplicadas en destino y las distintas formas de realizar la inserción:

Fila por fila:








Filas por lotes:











Una significativa diferencia de 2'5 minutos en el proceso de inserción en un destino donde existen el 25% de las filas.
Existen formas más efectivas de eliminar las filas del flujo de datos que ya existan en la tabla destino. El método a utilizar depende de la cantidad de datos que se vaya a manejar.

El principio

Bienvenidos, Welcome

Inicio este blog como primer intento oficial de publicar y compartir artículos relacionados con Business Intelligence y tecnología de Microsoft, si bien se me puede escapar alguna cosilla de caracter laboral o personal... en fin si es el caso no me lo tengais en cuenta. Poder compartir mis experiencias, vivencias, problemas, soluciones y otras cosillas haciendo BI con SQL Server y SharePoint es la principal motivación. Podeis decir lo que querais al respecto de lo que escriba, esta abierto a todo tipo de comentarios... que si alguno esta fuera de tono, creo que por aqui hay un botón eliminar :D
Mi nombre es Víctor Sánchez García y aprovecho para decir que me estoy preparando MCITP de BI. Si alguno esta pensando en presentarse les recomiendo que consigan los kit de preparación. Ya les diré el día 12 de noviembre cual es el resultado.