Nuestras-experiencias-con-Excel-en-procesos-ETL-SolidQ-1–Excel-0

thesolidqjournalbusiness intelligence 20

Introducción

Excel es una herramienta ampliamente utilizada en las empresas, es más, es una de las herramientas con las que los usuarios se sienten más cómodos a la hora de crear y visualizar sus datos. Es muy habitual que cualquier persona vaya generando a lo largo del tiem-po gran cantidad de libros de Excel con información exportada de diversas aplicaciones con las que trabaja en el día a día, dado que es muy común que cualquier aplicación tenga un botón que permita exportar las consultas e informes a Excel. También es frecuente que cuando dichas aplicaciones no le ofrecen la posi-bilidad de gestionar los datos que necesita, el usuario recurra a Excel para ello. Por ejemplo, si quiere clasifi-car al personal de la empresa en tres grupos (tiendas, administración y dirección) y la aplicación actualmente no tiene una columna que nos permita almacenar di-cha clasificación, es fácil que se decida crear esa cla-sificación en Excel, agregar una lista de empleados y asignar allí el valor que corresponda a cada uno. En otras ocasiones, en lugar de pedir al departamento de TI la creación de una pequeña aplicación para ges-tionar ciertos datos, son los propios usuarios los que crean libros Excel y se encargan de almacenar allí los

datos. Por ejemplo, para consultar las diferentes ta-rifas de proveedores recibidas en formato Excel, se crea un nuevo libro que accede a las tarifas y muestra las diferencias de precios para un mismo producto y el proveedor que lo ofrece a menor coste.

También es habitual que el usuario se conecte desde Excel a diferentes fuentes de datos, tanto relacionales como multidimensionales, para realizar consultas. Dichas consultas las almacenan en libros Excel y las actualizan y visualizan periódicamente.

Como puede comprobar, Excel viene a comple-mentar las aplicaciones existentes en la empresa de

The SolidQ™ Journal, Septiembre 2011– www.solidq.com/sqj

Por Salvador Ramos

Nuestras experiencias con Excel en procesos ETL. SolidQ 1 – Excel 0

Excel es un formato ampliamente utilizado por los usuarios, y que nos encontramos habitual-mente cuando realizamos procesos ETL. Comentaremos sus peculiaridades, así como proble-mas habituales con los que nos hemos encontrado al tratar con este formato y las soluciones que hemos ido aportando. ¿Has tenido problemas con los drivers?, ¿con truncamientos de columnas?, ¿con tipos de datos?, ¿con cambios en los nombres de libros y hojas? Expondre-mos soluciones para estos y otros problemas.

Figura 1: Excel como almacén de datos

sqj 13 pág. 20-28 esp.indd 20 06/10/11 12:37

21


muy diversas formas, y por tanto pasa a ser tanto una fuente adicional para los datos como un destino. Véase la Figura 1.

Cuando abordamos la realización de tareas ETL, es muy habitual que nos encontremos con archivos en formato Excel entre nuestros orígenes y destinos Véase la Figura 2.

Es más, recomendamos que siempre que se aborde un proyecto de BI se revisen con detalle to-dos los archivos Excel de la empresa, ya que es muy habitual encontrar en ellos información importante para el negocio que, por diversas causas, no está almacenada en las aplicaciones, y que solamente se encuentra en dichos archivos Excel, por lo que debe-remos de tratarla en nuestros procesos ETL.

Dada la importancia y frecuencia con la que nos encontramos el formato Excel, hemos considerado oportuno dedicar un capítulo a recopilar las casuísti-cas, problemáticas, ejemplos de uso y nuestras ex-periencias con este formato. A continuación expon-dremos una serie de informaciones que le ayudarán

a abordar con mayor rapidez y eficacia la gestión de archivos en este formato desde Integration Services.

Problemática habitual

A partir de este momento nos centraremos en la problemática habitual que nos encontramos cuando estamos trabajando con paquetes de SQL Server In-tegration Services (SSIS) y en los procesos que es-tamos desarrollando donde tenemos archivos Excel como origen o destino.

Limitaciones en Integration Services

Una de las limitaciones que nos encontramos es que los componentes Lookup y Fuzzy Lookup cuyo origen sea un archivo Excel no los podremos enlazar si tenemos columnas numéricas como clave, ya que éstas son tratadas como float y por tanto no se pue-den utilizar para asociarlas. Imaginaos que tenemos una lista con los códigos (numéricos) y nombres de

Figura 2: Excel como origen y destino

sqj 13 pág. 20-28 esp.indd 21 06/10/11 12:37



provincias en Excel, que estamos leyendo de una tabla de Clientes en SQL Server en la que os viene una columna con el código de provincia, y necesita-mos obtener del archivo Excel, a través del código de provincia, el nombre de la provincia. Lo habitual es que nos planteemos utilizar el componente Lookup en nuestro DataFlow, pero esto no es posible por las limitaciones del propio componente. Entonces tendremos que buscar una alternativa. Mi recomen-dación es que se importen los datos del Excel a una tabla en una base de datos de SQL Server, y a con-tinuación se haga el Lookup utilizando los datos de dicha tabla. Habitualmente son tablas de pocas filas cuya importación prácticamente no nos ocupará es-pacio ni tiempo.

Otra limitación que nos encontramos es que no tendremos acceso a las hojas marcadas como ocul-tas en el libro. Habrá que verificar que ninguna de las hojas que vamos a utilizar esté oculta.

Tenga en cuenta que las columnas ocultas sí que serán accesibles, y no será necesario hacerlas visi-bles para poder ser utilizadas en nuestros procesos.

Problemas frecuentes en entornos de 32/64 bitsActualmente, lo más habitual es encontrarnos con procesadores de 64 bits, mientras que sigue siendo frecuente encontrarnos con la ausencia de drivers de 64 bits para algunos formatos de ficheros. Por suerte esto es cada vez menos habitual, y van apareciendo mayor cantidad de drivers de 64 bits. Concretamente en el caso de Excel tenemos drivers de 32 bits.

Es conveniente saber que, si disponemos de un servidor de 64 bits, los paquetes pueden ser ejecu-tados tanto en modo 32 bits como en modo 64 bits. Desde Business Intelligence Developement Studio (BIDS) tenemos la posibilidad de hacerlo configu-rando la propiedad ‘Run64BitRuntime’ en las pro-piedades del proyecto. Eso sí, tenga en cuenta en que afectará a la ejecución de todos los paquetes del proyecto.

Cuando creamos un job que debe ejecutar un paquete SSIS, tenemos la posibilidad de indicarle también si queremos que se ejecute en 32 o 64 bits; para ello en el paso que ejecuta el paquete, vamos a la pestaña ‘Execution Options’ y allí podemos mar-car o desmarcar la casilla de verificación ‘Use 32 bit runtime’.

Figura 3: BIDS, configuración de ejecución en 32 o 64 bits

Figura 4: Paso de un job: elegir 32 o 64 bits

Es conveniente saber que, si disponemos de un servidor de 64 bits, los paquetes pueden ser ejecutados tanto en modo 32 bits como en modo 64 bits.

sqj 13 pág. 20-28 esp.indd 22 06/10/11 12:37

23


Hay ocasiones en que nos encontramos con archi-vos Excel simplemente porque el usuario se encuentra cómodo con esta herramienta, y realmente no tiene importancia el formato en el que están almacenados dada la simplicidad de estos. Supongamos, por ejem-plo, un caso como el anterior, en el que el usuario crea en Excel una tabla con los códigos y descripciones de provincias. En estos casos, y dado que Excel trabaja sin ningún problema y de forma transparente para el usuario con el formato .CSV (Comma Separated Va-lues), debemos plantearnos si nos merece la pena evitar los problemas y restricciones de Excel y alma-cenar la información en formato .CSV. Muchas veces es simplemente cuestión de dialogar con el usuario y establecer este formato de almacenamiento.

Valoremos, siempre que sea posible, los pros y contras del formato Excel frente al formato .CSV.

Tipos de datos y longitudes

Excel es una herramienta orientada a cálculos, su gestión de tipos de datos es muy básica y mucho menos rica que en un gestor de bases de datos re-lacionales o en herramientas ETL como SSIS. Este es el subconjunto de tipos de datos que tenemos disponibles desde SSIS cuando utilizamos Excel:

• DT_R8: para datos numéricos, sin más distin-ciones como enteros o tipos de mayor o me-nor tamaño en bytes.

• DT_WSTR: para cadenas de hasta 4000 bytes.• DT_NTEXT: para cadenas de mayor longitud.• DT_DATE: para fechas y horas.

Asignación de tipos de datos

Pero, si en Excel no tenemos la posibilidad de in-dicar el tipo de datos, y simplemente escribimos en

las celdas y no podemos definirlos en ningún sitio, ¿cómo se asignan los tipos de datos de SSIS al leer de un origen Excel?

Pues muy sencillo, se leen las 8 primeras filas y en función del tamaño y valores que hay en ellas se asigna uno de los tipos de datos anteriores. Por lo que hemos podido ir comprobando en nuestras experiencias, ya que no hemos encontrado ningún documento oficial que hable de los tipos disponi-bles y asignaciones, si todo son números se asig-na DT_R8, si todo son fechas y/o horas se asigna DT_DATE, si hay alguna cadena de caracteres de hasta 255 se asigna DT_WSTR, y si dicha cade-na es de más caracteres se asigna DT_NTEXT. En cuanto a las cadenas de caracteres hay que tener en cuenta que esa es la asignación automática, y que aunque tenga, por ejemplo, 600 caracteres en una fila y se podría asignar el tipo DT_WSTR (que soporta hasta 4.000 caracteres) se asigna el tipo DT_NTEXT.

Hay que tener en cuenta que se puede configu-rar indicando si la primera fila son datos o son enca-bezados, y en el segundo caso asigna esos valores como nombre de columnas y no las tiene en cuenta como filas para decidir el tipo de datos a asignar.

El principal problema con el que nos encontra-mos es con el tratamiento de cadenas, ya que en función de la asignación automática, en muchos ca-sos pueden producir truncamientos y por lo tanto pérdida de información y posibles errores en nues-tros procesos ETL.

A continuación vamos a ver qué posibilidades te-nemos de mejorar ese funcionamiento por defecto que hemos descrito anteriormente.

En cuanto a la asignación automática de los tipos de datos, podemos modificar el comportamiento en cuanto al número de filas escaneadas que se tendrán en cuenta a la hora de asignar los tipos de datos. Para

Figura 5: Clave de registro para indicar el número de filas a leer

sqj 13 pág. 20-28 esp.indd 23 06/10/11 12:37



ello tenemos la clave de registro [HKEY_LOCAL_MA-CHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD “TypeGuessRows”, cuyo valor por defecto es 8. De ahí que se lean las 8 primeras filas. Este valor puede ser modificado en el registro de Windows, y podemos indicar allí el núme-ro de filas que deseamos que sean escaneadas. Si in-dicamos el valor 0 se leerán todas las filas del archivo. Tenga en cuenta que cuanto más alto sea este valor (o bien cuando sea 0) penaliza el rendimiento.

Aun leyendo todas las filas, puede que no se asig-ne el tipo de datos que realmente va a corresponder en explotación, debido a que la muestra de ficheros no haya sido lo suficientemente amplia para cubrir toda la casuística. No nos confiemos y hagamos las pruebas y asignemos los tipos de datos a partir de uno o muy pocos ficheros de muestras si estos no son representativos de toda la casuística.

A todo ello hay que sumar que, tanto el com-ponente OleDb Source como Excel Source, aunque nos permiten cambiar desde el editor

avanzado los tipos de datos por defecto, tienen cier-tas limitaciones. Por ejemplo, no nos permite cam-biar entre cadenas Unicode y no Unicode. Y aunque sí permite cambiar el valor de la propiedad DataType entre DT_WSTR y DT_NTEXT en Source Output, en cambio da error cuando cambiamos para la misma columna en Source Error Output.

Hay otra serie de cambios que sí que podemos realizar sin problemas en el editor avanzado, como el cambio de DT_R8 a tipos de datos enteros (DT_I1, DT_I2, DT_I4,…) siempre que todas las filas de origen tengan valores que no den error al almacenarlos con esos tipos de datos. En definitiva, tenemos diversas posibilidades para cambiar el tipo de datos y longitud asignadas por defecto, pero con ciertas restricciones.

Buenas prácticas

Hay ocasiones en las que nos han facilitado va-rios ficheros y tenemos una muestra suficiente para asignar los tipos de datos correctos para cubrir toda la

Figura 06: Error al cambiar la propiedad DataType en Source Error Output.

sqj 13 pág. 20-28 esp.indd 24 06/10/11 12:37

25


casuística, pero si escogemos un solo fichero, no se contempla la casuística completa. Dado este caso, unido a que si cambiamos la clave de registro para que escanee todas las filas (valor 0) penaliza el ren-dimiento, nuestras recomendaciones son crear un nuevo archivo Excel, y pegar allí en la primera fila (tras los encabezados si existiesen) los valores represen-tativos para que la asignación automática del tipo de datos que necesitamos obtener sea la correcta.

Veamos un ejemplo: periódicamente recibimos un archivo Excel con la información de nuevos clien-tes, disponemos de una muestra de 5 archivos que cubre la casuística habitual, y hemos detectado que sólo en uno de ellos, en la columna ‘Observaciones-Medicas’, hay filas con más de 255 caracteres, y sólo en otro archivo diferente en la columna ‘Observacio-nesPsiquicas’ hay más de 255 caracteres, en cuyo caso ninguno de ellos es válido para obtener el tipo de datos adecuado para cada columna (necesitamos que sean DT_NTEXT ambas columnas). Podemos crear un archivo que contemple en una sola fila ambas ca-suísticas (ver imagen), y que será el que utilicemos a la hora de diseñar inicialmente nuestro paquete SSIS.

Nuestras recomendaciones a la hora de generar los tipos de datos que necesitamos son las siguien-tes y en este orden:

1. Definir un archivo Excel de muestra que nos fa-cilite la asignación automática lo más parecida posible al resultado que necesitamos.

2. Modificar las propiedades referentes a los tipos de datos mediante el editor avanzado.

3. Utilizar los componentes Data Conversion y Derived Column para obtener los tipos de datos que nece-sitemos y que no haya sido posible obtener por los métodos indicados en los puntos anteriores.

Separadores. Problemas con el punto y la coma

Otro problema habitual inherente a Excel y a su forma de trabajar con las configuraciones re-gionales, es la asignación de los caracteres punto “.” y coma “,”, bien punto como separador de mi-les y coma separador de decimales, o viceversa. Debemos tener en cuenta este punto para evitar conflictos en nuestros paquetes de SSIS y la in-

Figura 7: Archivo de muestra cubriendo toda la casuística de tipos de datos correcta

Figura 8: punto y coma, ¿separadores de miles o de decimales?

sqj 13 pág. 20-28 esp.indd 25 06/10/11 12:37



troducción y visualización de datos por parte del usuario.

Informes. Lo que quiere ver el usuario vs. lo ideal para el ETLY llegados a este punto, vamos a entrar en un tema complicado, no por complejidad técnica, sino por las relaciones entre personas y equipos, por discrepan-cias en la forma de ver los datos, y por diferencias de conocimientos técnicos.

Hay ocasiones en que un mismo archivo ha de ser tratado en procesos ETL y a su vez visualizado y actualizado por los usuarios. Aquí surge uno de los problemas, porque el formato óptimo para los proce-so ETL difiere del formato óptimo para visualización y actualización. Veamos un ejemplo muy simple, ba-sado en un informe de ventas diario cuya informa-ción debe ser consultada por diversos usuarios y a su vez importada al DataMart de la empresa.

¿Cómo quiere verlo el usuario? Pues formatea-do, sin datos redundantes, con totales, incluyendo nombres descriptivos y no sólo códigos, con líneas y columnas en blanco para que no aparezca todo demasiado apelotonado. En definitiva, y dejando al lado las mejoras de diseño visual, algo como lo que se muestra en la siguiente figura:

¿Cuál es el formato óptimo para su uso en nuestro proceso ETL? Teniendo en cuenta que en este caso no necesitamos importar la forma de pago, ni tampo-co los nombres de clientes y artículos porque están en otra tabla, que la fecha debe ir en cada fila y que no necesitamos los totales porque los vamos calcular

a partir del detalle, sería algo como lo mostrado en la siguiente figura:

Ante estos casos, la solución es trabajar en SSIS para la obtención del formato que tenemos en nues-tro destino. Lo que sí es recomendable es dialogar con el usuario para que si hay ciertos elementos de diseño que nos complican la creación del paquete y ve asumible su modificación, se hagan los cambios oportunos en el diseño, con el fin de ahorrar tiem-pos de desarrollo. Siguiendo con el ejemplo anterior, consideramos que no sería asumible por el usuario la eliminación del total que aparece en la parte infe-rior, pero sí que podría ser asumible que la fecha, en vez de aparecer una sola vez en la parte supe-rior, apareciese como una columna adicional en el detalle, y así evitarnos un diseño más complejo del paquete de SSIS.

Figura 09: Informe de ventas (perspectiva del usuario)

Figura 10: Informe de ventas (perspectiva del proceso ETL)

Hay ocasiones en que un mismo archivo ha de ser tratado en procesos ETL y a su vez visualizado y actualizado por los usuarios. Aquí surge uno de los problemas, porque el formato óptimo para los proceso ETL difiere del formato óptimo para visualización y actualización.

sqj 13 pág. 20-28 esp.indd 26 06/10/11 12:37

27


Excel creados por el usuario

El ejemplo que hemos visto anteriormente, puede haber sido creado por un usuario o por una aplica-ción sin la intervención del usuario en el diseño. O en una combinación de ambas formas, es decir, es generado por una aplicación y después retocado por el usuario.

Cuando nos encontramos en el caso de que sí que interviene el usuario en el diseño del archivo Ex-cel, tenemos que evitar, en la medida de lo posible, que se generen errores porque diferentes archivos del mismo tipo tienen diferentes formatos, es decir, no podemos encontrarnos con que en un archivo el código del cliente está en la columna B y en otro en la columna D, el nombre del fichero y de las hojas debe seguir ciertos patrones en algunos casos, no se pueden agregar o quitar celdas a su antojo, etc.

Esto para el usuario que trabaja con ese archivo Ex-cel no tiene ninguna importancia, pero para el proce-so ETL es un problema muy grave que hará que el proceso falle al ser ejecutado.

¿Qué solución tenemos en estos casos? Pues ninguna desde el punto de vista técnico. Se trata de dialogar con el usuario y hacerle ver que el formato ha de ser siempre el mismo y que será responsa-bilidad suya que así sea. Esto es muy fácil de decir aquí, pero la realidad es que los usuarios no suelen asumir estos casos como errores suyos e intentan responsabilizar al departamento de TI, por lo que

tendremos que poner todo nuestro empeño y habi-lidades negociadoras en dejar este punto aclarado y por escrito.

Automatizando lecturas de archivos y hojasHay ocasiones en la que nos encontramos con fi-cheros que contienen diferentes cantidades de ho-jas en su interior, e incluso a veces con diferentes nombres. En ocasiones será por simple despiste del usuario a la hora de asignar nombres y otras veces porque este dato puede ir variando. Por ejemplo si nuestro archivo Excel viene con una hoja por mes (Enero, Febrero, Marzo,…) y sólo trae los meses en curso, dependiendo del mes en que nos encontremos, el archivo tendrá un número diferen-te de hojas. Otro ejemplo sería un libro que viene con una hoja por familia de producto; imaginemos que actualmente tenemos 8 familias, pero alguna puede cambiar de nombre, o también puede apare-cer o desaparecer alguna de las existentes, en cuyo caso cambiarán las hojas del libro.

Para solucionar este problema es necesario apoyarnos en los Scripts Task y mediante código VB.NET o C# conseguir la flexibilidad necesaria para que nuestro paquete SSIS trate esas variantes de ficheros sin necesitar de hacer modificaciones en nuestros desarrollos.

A groso modo, nuestro proceso consistirá en dos bucles anidados, uno que lea cada archivo Excel de la carpeta donde se encuentran almacenados, y otro bucle al interior del primero que lea las diversas hojas de cada libro. Y como último paso se importarán los datos de cada hoja a nuestro destino (ver imagen).

Hay ocasiones en la que nos encontramos con ficheros que contienen diferentes cantidades de hojas en su interior, e incluso a veces con diferentes nombres.

Figura 11: usuarios y departamento de TI deben llegar a ciertos acuerdos.

sqj 13 pág. 20-28 esp.indd 27 06/10/11 12:37



Conclusiones

Finalmente queremos remarcar lo visto anterior-mente con una serie resumida de buenas prácticas que debemos revisar cada vez que tengamos que trabajar en nuestros procesos ETL con archivos Excel:

• Analizar con detalle la problemática con los tipos de datos:– Hacer una hoja de muestra que cubra la ca-

suística que hemos encontrado tras analizar los datos.

– Hacer los cambios que podamos en la defini-ción de los tipos de datos y longitudes en los componentes Source del DataFlow mediante el editor avanzado.

– Hacer el resto de cambios utilizando Data Con-version y Derived Column.

• Automatizar tareas de forma que nos eviten erro-res de ejecución y posteriores modificaciones so-bre el paquete.

• Dialogar con el usuario:

– Ambas partes (usuario y departamento de TI) deben ceder para consensuar un formato que sea factible para ambos, y no complique el tra-bajo a una de las partes.

– Que el usuario asuma que el formato que se establezca se debe respetar estrictamente para todos los archivos Excel generados.

Sobre el Autor

Salvador Ramos (blog), (twitter) es for-mador y mentor en SolidQ y posee experien-cia en una amplia variedad de aplicaciones de negocio. Su interés principal radica en los proyectos de BI y Data Warehouse, y en el diseño de Bases de Datos relacionales usando Microsoft SQL Server. También es un

experto en proyectos de migración a SQL Server..

Figura 12: Proceso que lee todas las hojas de cada libro Excel de una carpeta

sqj 13 pág. 20-28 esp.indd 28 06/10/11 12:37

Nuestras-experiencias-con-Excel-en-procesos-ETL-SolidQ-1–Excel-0

Documents

Transcript of Nuestras-experiencias-con-Excel-en-procesos-ETL-SolidQ-1–Excel-0