01 julio 2008

Data Profiling Task en Integration Services 2008

Hola a todos, estoy de vuelta. En esta ocasión vamos a revisar las posibilidades que nos da Integration Services 2008 para hacer data profiling o analizar perfil de nuestros datos. La respuesta la encontramos en el componente del mismo nombre Data Profiling Task que se encuentra dentro de los componentes del Control Flow. Como vamos a ver más adelante, este nuevo componente nos permite de una manera fácil hacer un análisis completo de nuestros datos resumiéndose en su distribución y calidad.

El funcionamiento del Data Profiling Task es bastante sencillo:

1. Definimos la conexión, tabla y campo(s) de donde vamos a alimentar los datos que queremos analizar,
2. Definimos la conexión de salida en donde queremos que se guarden los resultados del análisis (un archivo XML en el sistema de archivos ó una variable),
3. Seleccionamos el tipo de análisis que queremos realizar y configuramos los parámetros requeridos (si alguno), y
4. Inspeccionamos los resultados con el Data Profiling Viewer ó leemos los resultados y tomamos alguna opción haciendo una interpretación (parsing) de los resultados de la variable XML.

El Data Profiling Task nos permite realizar los siguientes tipos de análisis (profile requests):
• Candidate Key Profile Request: Para determinar si un campo puede servir como identificador (key) mostrando el porcentaje de valores únicos.
• Column Lenght Distribution Profile Request: El mínimo y máximo tamaño de variables de tipo cadena. Muestra también la distribución de los valores según el tamaño de los mismos.
• Column Null Ratio Profile Request: Muestra el porcentaje de valores nulos de un campo.
• Column Pattern Profile Request: Para identificar la distribución de nuestro datos según patrones en su contenido.
• Column Statistics Profile Request:
• Column Value Distribution Profile Request: Para identificar la distribución de nuestros datos, número de valores únicos, cantidad y porcentaje de los valores.
• Functional Dependency Profile Request: Para identificar relaciones de dependencia entre distintos campos y detectar posibles violaciones. Por ejemplo la relación entre los campos País y Capital: Perú  Lima, España  Madrid, Puerto Rico  San Juan, si se detecta un campo que no siga la dependencia, por ejemplo Perú  Cusco, esto se mostrará como una violación y disminuirá el índice de dependencia entre estos 2 campos.
• Value Inclusion Profile Request: Para verificar si los valores del campo en una primera tabla (hija), están contenidos en una segunda tabla (padre). Esto también nos sirve para validar la calidad de nuestros datos como la existencia de cada producto vendido en la tabla de [Ventas] dentro de nuestra tabla de [Productos].

Vamos a ver paso a paso y gráficamente la configuración y resultados de cada uno de los tipos de análisis. Para realizar los ejemplos que se muestran a continuación he usado la base de datos AdventureWorksDW que viene con la instalación de SQL 2008 y principalmente de la vista llamada [vNewCustomers].
Cree un nuevo proyecto de SSIS:



Agregamos un DataProfiling Task desde el Toolbox al Control Flow:




Editamos las propiedades de conexión para la salida o output de los resultados del data profiling:




Indique la ruta a donde quiere guardar los resultados:



Ahora vamos a cerrar las propiedades del Data Profiling Task para crear un nuevo objeto conexión ADO .NET para indicar cuál será la fuente de donde obtendremos los datos para el análisis:




Ingrese los detalles de la conexión, en este caso apuntamos al servidor local y a la base de datos AdventureWorksDW:




Ahora que tenemos lista nuestra conexión de entrada y salida para nuestro profiling abrimos nuevamente el componente y en la pestaña de Profile Request veremos los tipos de análisis disponibles:



Candidate Key Profile Request: Vamos a comenzar por analizar cuál sería la posibilidad de usar el campo [Phone] como una clave que permita identificar a nuestros nuevos clientes. La configuración utilizada sería:





La propiedad ThresholdSetting nos permite indicarle al componente que queremos alcanzar un índice mínimo con respecto al potencial del campo seleccionado para ser un identificador único o clave primaria (donde 100% indicaría que es todos los registros son únicos). En este caso le estamos indicando que SÓLO queremos que escriba los resultados en el archivo XML de salida si el índice (threshold) es mayor o igual que 95%. Otros valores para la propiedad ThresholdSetting son “None” (no queremos restringir el output del análisis) ó “Exact” (sólo queremos output si es un key único). Finalmente, el MaxNumberOfViolations le dice al componente cuántos registros con violaciones como máximo queremos incluir en el archivo de salida de manera que podamos restringir esta cantidad si en caso tenemos cientos o miles de registros inválidos.




Ejecutamos el paquete:




Ahora veamos con el DataProfileViewer los resultados:



Una vez en el Data Profile Viewer abrimos el archivo XML que indicamos al componente como output. En este caso como se ve a continuación no tenemos resultados lo cual no es un error sino que el profiling no cumplió el índice de 95% o más y por tanto no escribió resultados:





Probemos ahora bajando el threshold a 60%:



Ejecutamos el paquete nuevamente y verificamos los resultados:







Como se ve, el Data Profile Viewer nos muestra la información de conexión en el lado izquierdo incluyendo el tipo de análisis que hemos aplicado a la tabla o campo, eso nos ayuda mucha en la navegación cuando tengamos más de un análisis aplicado a la misma tabla o campo.




Con respecto a los resultados en la imagen anterior vean que el Key Strength del campo Phone es sólo de 61.20%, lo cual quiere decir que tenemos números de teléfonos repetidos en nuestra tabla. Eso lo podemos constatar con los resultados en el panel Key Violations incluyendo información la cantidad de violaciones incurridas y un porcentaje con respecto al total de los datos.


Adicionalmente podríamos presionar el botón DrillDown o darle doble click a uno de los resultados para automáticamente conectarnos a los datos de la tabla y ver cuáles son los que produjeron la violación seleccionada como se ve a continuación:





Algo bastante interesante del archivo de output en XML es que podríamos utilizar su contenido para automatizar algún proceso leyendo directamente sus resultados, ya sea desde el archivo físico directamente o sino desde una variable. En la imagen que se muestra a continuación se muestra parte del XML del archivo con los resultados del Candidate Key Profile:


Ahora que ya tenemos las bases de cómo funcionan las cosas con el Data Profiling Task veremos los demás tipos de análisis usando el mismo componente.





Column Leght Distribution Profile: Aplicamos este análisis a la columna Phone:








En los resultados podrán notar que tenemos 2 tipos de teléfono, de 12 caracteres y de 19 caracteres.







Según se muestra los teléfonos de 19 caracteres son números internacionales:





Column Null Ratio Profile: Aplicamos este análisis a la columna MiddleName.










Como se muestra en la siguiente imagen, el 43.77% de los valores son nulos:







Column Pattern Profile: Este tipo de data profiling nos permite conocer los patrones de nuestros datos expresados en forma de Regular Expressions (RegEx) de manera que de una serie de datos podamos identificar si siguen o no determinado esquema (la expresión regular). Por ejemplo si tenemos un campo que guarde los números de tarjetas de créditos que se compone de 4 grupos de 4 dígitos separados o no por un guión (-), tendríamos un RegEx como el siguiente: \d{4}-?\d{4}-?\d{4}-?\d{4}. Si luego de aplicar el data profiling obtenemos RegEx distintos al esperado tenemos problemas con la calidad de nuestros datos.


Para más detalles con respecto a las propiedades de configuración de este tipo de profiling se pueden referir a http://technet.microsoft.com/en-us/library/bb934022(SQL.100).aspx. Para ver una librería de Regular Expressions se pueden referir a http://regexlib.com.




En nuestro demostrativo aplicaremos el análisis al campo teléfono Phone.









Los resultados que se muestran a continuación nos muestran los 2 RegEx encontrados, uno para números locales y otro para números internacionales.






Column Statistics Profile: Este análisis se puede aplicar sólo a variables numéricas y obtendremos algunas estadísticas como se muestra en la siguiente imagen:








Column Value Distribution Profile Request: Para conocer más acerca de los valores dentro del campo Educación aplicamos el siguiente análisis. Noten que también podemos configurar la propiedad FrequentValueThreshold para filtrar los valores resultantes que se repitan un porcentaje mayor o igual al ingresado. Para efectos demostrativos dejamos este valor por defecto.







Aquí los resultados. Además de la distribución de los valores obtenemos también la cantidad de valores distintos.







Functional Dependency Profile Request: Como mencioné en la parte inicial con este tipo de análisis podemos ver como se relaciona un campo con otro y detectar posibles violaciones. Un ejemplo adicional podría ser la relación entre el país de residencia de una persona y el código telefónico del país de su número telefónico fijo. En este caso si mi país de residencia es Perú mi número telefónico debería de comenzar con el número 51, si mi país es Argentina con el 54 y así. En el ejemplo que se muestra a continuación mostramos un análisis de dependencia entre la línea de un producto (ProductLine) y la SubCategoría (ProductSubCategoryKey). En un escenario hipotético, la relación entre ProductLine y el SubCategory debe ser de 1 a 1.









Según nuestro escenario hipotético vemos que hay algunas violaciones a la relación entre los campos indicados. Aún así podemos notar que la dependencia del campo ProductLine hacia SubCategory es de más de 92%.





Value Inclusion Profile Request: En este caso podemos referenciar a una tabla maestra (superset) para verificar si todos los valores de mi tabla de transacciones (subset) tiene una correspondencia válida en la primera, esto es perfecto para validar problemas de integridad referencial. Para la demostración se usó como tabla superset a tabla DimProductSubCategory y como subset a la tabla DimProduct; como campo de comparación se seleccionó ProductSubCategoryKey de ambas tablas:





En este caso vemos que todas las referencias en el subset se incluyen en el superset a excepción de los valores nulos. Dependiendo de mis reglas de negocios diríamos que este es o no un problema.





Ahora tenemos ya una idea de las potencialidades del DataProfilingTask de SSIS 2008.


Pueden descargar el proyecto completo usado para este demo desde este link SSISProfilingDemo.zip. El proyecto fue creado con el CTP de Febrero de SQL 2008, pero no deben tener problemas de ejecutarlo en el RC0.


Hasta la próxima,

Alan