por: Koen Verbeeck | actualizado: 2018-08-09 | Comentarios (4) / relacionado: 1 | 2 | 3 | 4 | Más > desarrollo de servicios de integración
Webinar gratuito de MSSQLTips: primeros pasos con SSIS
para comenzar con los servicios de integración de SQL Server y cómo construir un proyecto SSIS con este seminario web centrado en la demostración.,
Problema
Nos gustaría mantener la historia en nuestro almacén de datos para varias dimensiones.Utilizamos SQL Server Integration Services (SSIS) para implementar el ETL (ExtractTransform and Load). Probamos el Asistente de dimensión incorporado que cambia lentamente, pero el rendimiento parece pobre. ¿Cómo podemos implementar la funcionalidad deseada con componentes SSIS regulares?
solución
Introducción a las dimensiones que cambian lentamente
una dimensión que cambia lentamente (SCD) realiza un seguimiento de la historia de sus miembros individuales., Hay varios métodos propuestos por Ralph Kimball en su libro The DatawarehouseToolkit:
- Tipo 1 – sobrescribir los campos cuando el valor cambia. No hay historia guardada.
- Tipo 2-Crear una nueva línea con los nuevos valores para los campos. Los extracolumnos indican cuándo en el tiempo una fila era válida.
- Type 3-Mantener el valor antiguo de una columna en una columna separada.
- hay más tipos de SCD, pero en su mayoría son una combinación híbrida de los anteriores.
en este consejo, nos centraremos en la situación de tipo 2. Vamos a ilustrar con un ejemplo., Tenemos una tabla simple que almacena los datos de los clientes.
la columna SK_Customer es una columna con una identityproperty que generará un nuevo valor para cada fila. Nos gustaría mantener la historia para el atributo de ubicación. Cuando la ubicación cambia de Amberes a Bruselas, no actualizamos la fila, pero insertamos un nuevo registro:
Usando los campos ValidFrom y ValidTo, indicamos cuándo un registro era válido en el tiempo. Se genera una nueva clave sustituta, pero la clave de negocio-CustomerName-sigue siendo la misma., Cuando se carga una tabla fact, se realiza una búsqueda en la tabla customer. Dependiendo de la marca de tiempo del registro fact, se devolverá una de las dos filas. Por ejemplo:
Todos los hechos son para el mismo cliente. Cuando usted preguntaría la cantidad total de ventas para CustomerA, el resultado es 31. Las ventas totales por ubicación son de 12,75 para Amberes y 18,25 para Bruselas, aunque los datos son para el mismo cliente. Usando SCDType 2, podemos analizar nuestros datos con atributos históricos.,
Métodos de implementación
Hay varios métodos para cargar una dimensión que cambia lentamente del almacén de datos INA de tipo 2. Puede optar por un enfoque T-SQL puro, ya sea con sentencias multipleT-SQL o mediante el uso de la sentencia MERGE. Esto último se explica en eltipusing the SQL Server MERGE Statement to Process Type 2 Slowly Changing Dimensions.
con las SSIS, puede utilizar el asistente integrado para cambiar lentamente las dimensiones, que puede gestionar varios escenarios., Este asistente se describe en tipsLoading Historical Data into a SQL Server Data Warehouse andHandle Slowly Changing Dimensions in SQL Server Integration Services. La desventaja de este asistente es el rendimiento: utiliza el comando OLE DB para cada actualización, lo que puede resultar en un rendimiento deficiente para conjuntos de datos más grandes. Si realiza cambios en el flujo de datos para resolver estos problemas, no puede ejecutar el Asistente de nuevo, ya que perdería todos los cambios.
la última opción – aparte de usar componentes 3rdparty – es construir la lógica SCD tipo 2 usted mismo en el flujo de datos,que describiremos en la siguiente sección.,
implementación en SSIS
la solución propuesta en este consejo funciona para cualquier versión de SSIS. Sorprenderemos el ejemplo de la dimensión cliente, pero se ha añadido un campo adicional: el atributo email. No guardamos el historial de las direcciones de correo electrónico, por lo que cualquier valor nuevo sobrescribirá todos los demás valores.
primero, leemos los datos de una fuente, muy probablemente un entorno de estadificación. Al utilizar un origen relacional, puede utilizar el componente de origen OLE DB con una consulta SQL para leer los datos. Solo seleccione las columnas que realmente necesita para su dimensión. El campo validfrom también se calcula aquí., Dado que normalmente carga datos del día anterior, ValidFrom se establece en la fecha de ayer. Si no tiene una fuente relacional, puede agregar esta columna utilizando una transformación de columnas derivada.
En el siguiente paso, estamos haciendo una búsqueda con la dimensión.Aquí verificaremos si las filas entrantes son una inserción o una actualización.Si no se encuentra ninguna coincidencia, la fila es un nuevo miembro de dimensión y debe insertarse.Si se encuentra una coincidencia, el miembro de dimensión ya existe y necesitaremos tocheck para los cambios de tipo SCD 2., A menos que tenga una dimensión muy grande, puede usar la caché completa:
Configure la transformación de búsqueda para enviar filas no coincidentes a la salida sin coincidencia. En SSIS 2005 esta opción aún no existe, por lo que puede usar la salida de error, o establecer la transformación para ignorar errores y dividir inserciones y actualizaciones usando una división condicional.
en el Panel de conexión, la siguiente consulta SQL obtiene las columnas surrogatekey, business key (CustomerName) y SCD Type 2., Para cada miembro,solo se recupera la fila más reciente, filtrando en el campo ValidTo.
El campo de Ubicación se cambia el nombre a Location_OLD para mayor claridad. En el panel columnas, haga coincidir la clave de negocio y seleccione Todas las demás columnas.
Ahora podemos añadir un Destino de OLE DB en el lienzo. Este destino escribirá todas las filas nuevas a la dimensión. Conecte la salida de búsqueda sin Coincidenciade la transformación de búsqueda al destino.,
en el panel asignación, asigne las columnas del flujo de datos con las columnas de la tabla Dimension.
la columna SK_Customer se deja sin asignar, ya que es una columna de identidad y sus valores son generados por el motor de base de datos. La columna valido también se deja en blanco. Las filas nuevas no tienen valor para esta columna.
en la parte 2 de este consejo continuaremos nuestra configuración del flujo de datos,donde comprobaremos si una fila es una actualización de tipo 2 o no.,
próximos pasos
- Si desea obtener más información sobre la implementación de dimensiones que cambian lentamente en inSSIS, puede consultar los siguientes consejos:
- usando la instrucción SQL Server MERGE para procesar el tipo 2 que cambia lentamente las dimensiones
- Cargando Datos Históricos en un almacén de datos de SQL Server
- maneje las dimensiones que cambian lentamente en los servicios de integración de SQL Server
- Puede encontrar más consejos de desarrollo de SSIS en esta descripción general.,
Última actualización: 2018-08-09
Sobre el autor
Koen Verbeeck es un BI profesional, que se especializa en la pila de Microsoft BI con un amor particular de SSIS.ver todos mis consejos