sql-server - job - integration services sql server 2012
¿Cómo abordar una misión ETL? (5)
Se supone que debo realizar ETL donde la fuente es una base de datos sql 2k grande y mal diseñada y una base de datos sql 2k5 mejor diseñada. Creo que SSIS es el camino a seguir. ¿Alguien puede sugerir una lista de cosas para hacer o una lista de verificación o cosas para tener en cuenta para no olvidarme de nada? ¿Cómo debería abordar esto para que no me muerda en la retaguardia más adelante?
Bueno, estoy desarrollando un ETL para la compañía donde estoy.
Estamos trabajando con SSIS. Usando la API para generar y construir nuestros propios paquetes dtsx.
SSIS no es amigable para la administración de errores. A veces obtienes un "Error OleDb" que podría tener muchos significados diferentes dependiendo del contexto.
Lea la documentación de la API (no dicen mucho).
Algunos enlaces para ayudarlo desde allí: http://technet.microsoft.com/de-de/library/ms135932(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms345167.aspx
http://msdn.microsoft.com/en-us/library/ms403356.aspx
http://www.codeproject.com/KB/database/foreachadossis.aspx
http://wiki.sqlis.com/default.aspx/SQLISWiki/ComponentErrorCodes.html
http://www.new.facebook.com/inbox/readmessage.php?t=1041904880323#/home.php?ref=logo
http://technet.microsoft.com/en-us/library/ms187670.aspx
http://www.sqlis.com/post/Handling-different-row-types-in-the-same-file.aspx
http://technet.microsoft.com/en-us/library/ms135967(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms137709(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms345164(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms141232.aspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx
http://www.ivolva.com/ssis_code_generator.html
http://www.ivolva.com/ssis_wizards.html
http://www.codeplex.com/MSFTISProdSamples
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_23972361.html
http://forums.microsoft.com/MSDN/MigratedForum.aspx?siteid=1&PostID=1404157
http://msdn.microsoft.com/en-us/library/aa719592(VS.71).aspx
http://forums.microsoft.com/MSDN/MigratedForum.aspx?siteid=1&ForumID=80
http://blogs.conchango.com/jamiethomson/archive/2007/03/13/SSIS_3A00_-Property-Paths-syntax.aspx
http://search.live.com/results.aspx?q=%s&go=Buscar&form=QBJK&q1=macro%3Ajamiet.ssis
http://toddmcdermid.blogspot.com/2008/09/using-performupgrade.html?showComment=1224715020000
http://msdn.microsoft.com/en-us/library/ms136082.aspx
http://support.microsoft.com/kb/839279/en-us
Perdón por el "spam", pero me son muy útiles.
Estamos haciendo un ETL enorme (trasladando un cliente de aplicaciones heredadas de AS400 a Oracle EBS), y en realidad tenemos un proceso que (con modificaciones) puedo recomendar:
- Identifique las tablas / campos de objetivos críticos.
- Identifique las tablas / campos de fuentes críticas.
- Trabaje con los usuarios comerciales para asignar la fuente al objetivo.
- Analiza los datos de origen para problemas de calidad.
- Determine quién es responsable de los problemas de calidad de los datos identificados.
- Haga que las partes responsables limpien los datos en la fuente.
- Desarrolle el ETL real basado en la información de los pasos 1 - 3.
Los pasos más complicados son 2 & 3 en mi experiencia: a veces es difícil hacer que los usuarios de negocios identifiquen correctamente todos los bits que necesitan en una sola pasada, y puede ser aún más difícil identificar exactamente de dónde provienen los datos (aunque eso puede tener algo que ver con archivos crípticos y nombres de campo que estoy viendo!). Sin embargo, este proceso debería ayudarlo a evitar errores importantes.
Tengo experiencia con procesos ETL que extraen datos de más de 200 bases de datos distribuidas a una base de datos central diariamente, semanalmente, mensualmente y anualmente. Es una cantidad masiva de datos y hay muchos problemas que hemos tenido específicos para nuestra situación. Pero a mi modo de ver, hay varios elementos para pensar independientemente de la situación:
Asegúrese de tener en cuenta los bloqueos de archivos, tanto en el origen como en el destino. Asegurarse de que otros procesos no tengan los archivos bloqueados (y eliminar esos bloqueos si es necesario y tiene sentido) es importante.
bloqueando los archivos por ti mismo Asegúrese, especialmente en la fuente, de que bloquea los archivos mientras extrae los datos para que no obtenga datos a medio actualizar.
si es posible, extraiga deltas, no todos los datos. Obtenga una copia de los datos y luego seleccione solo las filas que han cambiado en lugar de todo. Cuanto mayor sea su conjunto de datos, más importante se vuelve. Si es necesario, revise las publicaciones periódicas y los factores desencadenantes, pero a medida que se vuelve más importante tener estos datos en cierta medida, este es probablemente el consejo número uno que le daré. Incluso si agrega una cantidad significativa de tiempo al proyecto.
registro de ejecución asegúrese de saber cuándo funcionó y cuándo no, y arrojar errores específicos en el proceso puede ayudar a la depuración.
documento, documento, documento. Si construyes este derecho, lo construirás y luego no pensarás en ello durante mucho tiempo. Pero puede estar garantizado, usted o alguien más tendrá que volver a él en algún momento para mejorarlo o corregir un error. La documentación es clave en estas situaciones.
HTH, actualizaré esto si pienso en otra cosa.
Este hilo es antiguo, pero quiero llamar su atención sobre la respuesta de ConcernedOfTunbridgeWells. Es un consejo increíblemente bueno, en todos los puntos. Podría reiterar algunos, pero eso disminuiría el resto, y todos merecen un estudio minucioso.
Algunos consejos generales de ETL
Considere organizarlo por destino (por ejemplo, todo el código para producir la dimensión Cliente vive en el mismo módulo, independientemente de la fuente). Esto a veces se conoce como ETL orientada a temas. Hace que encontrar cosas sea mucho más fácil y aumentará el mantenimiento de su código.
Si la base de datos SQL2000 es un desastre, probablemente encontrará que los flujos de datos SSIS son una forma torpe de manejar los datos. Como regla general, las herramientas ETL escalan escasamente con complejidad; algo así como la mitad de todos los proyectos de data warehouse en las compañías financieras se realizan con código de procedimiento almacenado como una decisión arquitectónica explícita, precisamente por esta razón. Si tiene que poner una gran cantidad de código en sprocs, considere poner todo el código en sprocs.
Para un sistema que involucra muchas depuraciones o transformaciones complejas, un enfoque 100% sproc es mucho más fácil de mantener ya que es la única forma factible de poner todas las transformaciones y la lógica de negocios en un solo lugar. Con sistemas ETL / sproc mixtos, debe buscar en múltiples lugares para rastrear, solucionar problemas, depurar o cambiar toda la transformación.El punto óptimo de las herramientas de ETL se encuentra en los sistemas donde tiene una mayor cantidad de fuentes de datos con transformaciones relativamente simples.
Haga que el código sea comprobable, de modo que pueda separar los componentes y realizar la prueba de forma aislada. El código que solo se puede ejecutar desde el medio de un flujo de datos complejo en una herramienta ETL es mucho más difícil de probar.
Haga que la extracción de datos sea tonta sin lógica comercial y cópiela en un área de ensayo. Si tiene la lógica de negocios distribuida en las capas de extracción y transformación, tendrá transformaciones que no se pueden probar de forma aislada y dificultar el rastreo de errores. Si la transformación se está ejecutando desde un área de ensayo, se reduce la dependencia del sistema fuente, lo que mejora la capacidad de prueba. Esta es una victoria particular en las arquitecturas basadas en sproc, ya que permite una base de código casi completamente homogénea.
Cree un manipulador genérico de dimensiones de cambio lento o utilice uno estándar si está disponible. Esto hace que sea más fácil probar esta funcionalidad por separado. Si esto se puede probar unitario, la prueba del sistema no tiene que probar todos los casos de esquina, simplemente si los datos presentados son correctos. Esto no es tan complejo como suena: el último que escribí fue sobre 600 o 700 líneas de código T-SQL. Lo mismo ocurre con cualquier función de depuración genérica.
Cargue incrementalmente si es posible.
Instrumente su código: haga que ingrese entradas de registro, posiblemente grabando diagnósticos como verificar totales o conteos. Sin esto, la resolución de problemas es casi imposible. Además, la comprobación de aserción es una buena forma de pensar en el manejo de errores para esto (¿el recuento de filas en un conteo de filas iguales en b, es la relación A: B realmente 1: 1).
Use claves sintéticas. El uso de claves naturales de los sistemas de origen vincula su sistema a las fuentes de datos y dificulta agregar fuentes adicionales. Las claves y las relaciones en el sistema siempre deben alinearse, sin nulos. Para los errores, ''no registrado'', realice entradas específicas de ''error'' o ''no grabadas'' en la tabla de dimensiones y haga coincidirlas.
Si construyes un Almacén de Datos Operacionales (el tema de muchas guerras religiosas) no recicles las claves ODS en los esquemas estelares. Por supuesto, únanse a las teclas ODS para construir las dimensiones, pero combinen en una clave natural. Esto le permite dejar caer y recrear arbitrariamente el ODS, posiblemente cambiando su estructura, sin alterar los esquemas de estrella. Tener esta capacidad es una ganancia de mantenimiento real, ya que puede cambiar la estructura de ODS o realizar un despliegue de fuerza bruta del ODS en cualquier punto.
Los puntos 1-2 y 4-5 significan que puede construir un sistema donde todo el código para cualquier subsistema dado (por ejemplo, una dimensión única o tabla de hechos) vive en un solo lugar en el sistema. Este tipo de arquitectura también es mejor para un mayor número de fuentes de datos.
El punto 3 es un contrapunto al punto 2. Básicamente, la elección entre herramientas SQL y ETL es una función de la complejidad de la transformación y del número de sistemas fuente. Mientras más simple sea la información y mayor sea la cantidad de fuentes de datos, más sólido será el enfoque basado en herramientas. Cuanto más complejos sean los datos, más sólido será el caso para pasar a una arquitectura basada en procedimientos almacenados. En general, es mejor utilizar de manera exclusiva o exclusiva uno o el otro, pero no ambos.
El punto 6 hace que tu sistema sea más fácil de probar. Probar las SCD o cualquier funcionalidad basada en cambios es complicada, ya que tiene que poder presentar más de una versión de los datos de origen al sistema. Si mueve la funcionalidad de administración de cambios al código de infraestructura, puede probarla aisladamente con conjuntos de datos de prueba. Este es un triunfo en las pruebas, ya que reduce la complejidad de los requisitos de prueba de su sistema.
El punto 7 es un consejo general de rendimiento que deberá observar para grandes volúmenes de datos. Tenga en cuenta que es posible que solo necesite carga incremental para algunas partes de un sistema; para tablas y dimensiones de referencia más pequeñas, puede que no lo necesite.
El punto 8 está relacionado con cualquier proceso sin cabeza. Si le salen las tetas durante la noche, quiere algunas posibilidades de luchar para ver qué salió mal al día siguiente. Si el código no registra correctamente lo que está sucediendo y detecta errores, tendrá un trabajo mucho más difícil para solucionarlo.
El punto 9 le da al almacén de datos una vida propia. Puede agregar y soltar fácilmente sistemas de origen cuando el almacén tiene sus propias claves. Las claves de almacén también son necesarias para implementar dimensiones que cambian lentamente.
El Punto 10 es una victoria de mantenimiento e implementación, ya que el ODS se puede reestructurar si necesita agregar sistemas nuevos o cambiar la cardinalidad de un registro. También significa que una dimensión se puede cargar desde más de un lugar en el ODS (piense: agregando ajustes de contabilidad manuales) sin una dependencia en las claves ODS.