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

Salvador Ramos Jorge Sánchez

BIN-311

Mentor – BI DPA – BI SQL Server MVP, MCTS, MCITP MCTS

sramos@solidq.com jsanchezdiaz@solidq.com

Arquitectura de BI ETL Area

Integration Services Data Flow

Excel El formato por excelencia para el usuario

α Limitaciones en SSIS

α 32/64 bits β BIDS

β Ejecución

α Tipos de datos y longitudes

α Separadores (problemas con el punto y la coma)

α Lo que quiere ver el usuario vs lo ideal para ETL

α Excel creados por el usuario

α Número variable de hojas y nombres diferentes

Problemática habitual

α ¿No se puede utilizar en Lookup ni en Fuzzy Lookup? β Sólo si la columna por la que enlazo no es numérica (float)

α Las hojas ocultas no están accesibles β Las columnas ocultas sí están accesibles

Limitaciones de uso en SSIS

Problemática 32/64 bits En BIDS

α Ejecución en modo 32 bits

α El formato .CSV puede ser una alternativa

Problemática 32/64 bits Ejecución SSIS

α Excel lee las 8 primeras filas y de ahí asigna tipos de datos y longitudes

α Filas escaneadas para obtener el tipo de datos β [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Exce

l] located registry REG_DWORD "TypeGuessRows".

β Valor por defecto 8 (8 filas)

β Si indicamos 0 escanea todas (afecta al rendimiento)

Tipos de datos y longitudes Asignación automática

α Texto β DT_WSTR (hasta 255 caracteres)

β DT_NTEXT

α Números β DT_R8 (floats)

α Fechas y horas β DT_DATE

Tipos de datos y longitudes Asignación automática

α Cuando tenemos datos de explotación, vemos que los datos no se ajustan a las longitudes establecidas

β Genera truncamientos

α Necesitamos una muestra lo más completa posible

Tipos de datos y longitudes Casuísticas habituales

α Separadores de miles y de decimales β Uso de punto o coma en función de la configuración

Tipos de datos y longitudes Casuísticas habituales

α Crear una muestra personalizada de pocas filas que implique la generación de tipos de datos y longitudes apropiados

Tipos de datos y longitudes Personalizando

α Entrar en propiedades avanzadas del origen Excel y definir los tipos de datos y longitudes apropiadas γ Evitar cambios posteriores, que afecten otras partes del DataFlow

γ DT_WSTR (hasta 4000)

γ Cambios de DT_WSTR a DT_NTEXT

δ Permite cambiar en Excel Source Output, pero da error en Excel Source Error Output

γ Cambios de DT_R8 a DT_I1, DT_I2, …

α En el origen no podemos hacer cualquier conversión de datos

β Para estos casos utilizaremos Data Conversion o Derived Column

Tipos de datos y longitudes Personalizando

Configuración orígenes Excel

α Excel con diferentes formatos de líneas β Líneas en blanco

β Títulos

β Encabezados

β Totales

β Otros

Informes Lo que quiere ver el usuario vs lo ideal para el ETL

Importar informe diseñado por usuario

α No siempre los Excel se generan de forma automatizada β Multitud de aplicaciones exportan a Excel

β El usuario crea sus propios Excel

α Cuando es el usuario quien los genera β No siempre son iguales

γ Cambios en los nombres de los ficheros

γ Cambios en los nombres de las hojas del libro

γ Cambios en el orden de las columnas

γ Cambios en los encabezados

γ Inclusión de líneas en blanco

Excel creados por el usuario

α Podemos leer los archivos de una carpeta cuyo nombre cumpla un patrón

β Ventas_??_*.xls

α Podemos recorrer todas hojas de un libro β Evitamos errores por cambios de nombre

β La inclusión o eliminación de hojas no afecta a la ejecución

α Necesitamos escribir código .Net para ello

Automatizando lecturas

Leyendo «todas» las hojas de «todos» los libros que cumplen el patrón Ventas_??_*.xls

α Analizar los problemas con los tipos de datos β Modificar los tipos de datos asignados por defecto

γ (Show advanced editor)

β Usar Data Conversion / Derived Column

β Tener una muestra completa

γ Eliminar la restricción por defecto de escaneo de 8 filas

γ Evitar que los datos de explotación sean diferentes a los que nos mostraron para el desarrollo

β Crear nuestra propia muestra resumida

α Automatizar tareas que nos eviten errores de ejecución y posteriores modificaciones sobre el paquete

Buenas prácticas

α Compromiso de no cambiar la estructura del fichero

α Pongamos en común la estructura a utilizar β Que perjudique lo menos posible a la visualización y al ETL

γ Ambas partes han de ceder

Buenas prácticas

No olvideis rellenar las evaluaciones en el Portal

del Summit!

Nos encontrareis en la zona de exposición en los

siguientes horarios

Salvador Ramos Jorge Sánchez

SQL Server MVP, MCTS, MCITP MCTS

α Esta tarde a la hora del café

α En cualquier descanso

sramos@solidq.com jsanchezdiaz@solidq.com

Mentor – BI DPA – BI

Salvador Ramos Jorge Sánchez

SQL Server MVP, MCTS, MCITP MCTS

sramos@solidq.com jsanchezdiaz@solidq.com

Mentor – BI DPA – BI