detección de cambios para el desarrollo de warehouse

Detección de Cambios DWH

Freddy Leandro Angarita Castellanos

SQL Server MVP

@flacMVP

@sqlpassmed

http://geeks.ms/blogs/fangarita/

SQL Saturday Sponsors

Diamond Sponsors

Bronze Sponsors

Proceso

SSIS Stage WareHouse

Problema

4 |

Origen Destino

Dato 1 A 1 A

Dato 2 B 2 K

Dato 3 C 5 E

Dato 4 D

Acción Dato Original Nuevo Dato

Actualización 2 K 2 B

Inserción 3 C

Inserción 4 D

Borrado 5 E

Acción Datos Fecha Efectiva Fecha Expiración

Actualización 2 B

Obsoleto 2 K

Inserción

Inserción

Obsoleto 5 E

Detección de Cambios

Es uno de los problemas a resolver al crear un WareHouse

Existen varias formas de resolver éste problema

Eliminación y recarga

Transformación SCD

Lookup

Transformada Kimball SCD

Método MergeJoin (Ideado por Freddy Angarita)

Método T-Sql Merge

CDC

Tipos de Dimensión y Administración

Tipo 0 No mantiene historia

Geografía

Debe considerarse integridad referencial

Tipo I Sobrescribe datos antiguos con los nuevos

Usado para aplicar cambios sobre tablas de hechos (Idealmente)

Es importante considerar la integridad referencial

Tipo II Una nueva fila para cada cambio que se genere para cada registros

Se crea una fecha efectiva y una fecha de expiración

Tipo III Se crea historia sólo para algunas columnas

Normalmente sólo contienen el último valor de la columna

Eliminación y Carga

Es un método simple de implementar

Se implementa rápidamente

Cantidad de Datos

Complejidad de la tabla

Ventana de tiempo

Requerimientos del sistema

Si la tabla se usa como una Fuente ROLAP ó Direct Query

(Depende del tipo cubo)

No es una opción

Puede tomar mucho tiempo

Demo – Eliminación y Carga

Transformación SCD

Ventajas

Es un método simple de implementar

Desventajas

Realiza las actualizaciones en la tabla destino registro a registro

No recuerda las opciones de diseño realizadas

No se respetan relaciones hechas anteriormente

Útil para detección de cambios para dimensiones simples

Demo – Transformación SCD

Transformación LookUp

Ventajas

Ofrece buen rendimiento

Desventajas

No es tan simple de implementar

La comparación se realiza registro a registro

Demo – Transformación LookUp

Transformación Kimball

Ventajas

Ofrece buen rendimiento

La comparación se realiza por conjuntos

Desventajas

No es tan simple de implementar

Es necesario instalar el complemento

No usa transformaciones estándar

http://dimensionmergescd.codeplex.com/

Demo – Transformación LookUp

Transformación MergeJOIN

Ventajas

Ofrece buen rendimiento

La comparación se realiza por conjuntos

Usa transformaciones estándar

No es necesario instalar ningún complemento

Desventajas

No es tan simple de implementar

Transformación MergeJOIN

Llave Dato

A 1

B 56

C 2

Llave Dato

A 1

B 23

D 34

Llave Actual Llave Nueva Dato Actual Dato Nuevo

A A 1 1

B B 56 23

C NULL 2 NULL

NULL D NULL 34

Demo – Método MergeJOIN

Transformación t-Sql MERGE

Ventajas

Ofrece buen rendimiento

Es simple de implementar

La detección de cambios vive en la base de datos

Desventajas

No se recomienda su uso para escenarios OLTP (Locks)

Demo – Método t-Sql MERGE

Ventana de Tiempo

Ventajas

Reduce la cantidad de registros a procesar

Desventajas

Es necesario tener campos de fecha en las tablas a analizar

Demo – Ventana de Tiempo

CDC

Ventajas

Reduce la cantidad de registros a procesar

Fácil implementación

Desventajas

No funciona con todos los orígenes de datos

CDC - Script

USE InternetSalesGO

--Habilitar CDC para base de datosEXEC sys.sp_cdc_enable_dbGO

--Habilitar CDC para tablaEXEC sys.sp_cdc_enable_table@source_schema = N'dbo',@source_name = N'Customers',@role_name= NULL,@supports_net_changes = 1GO

--Mapear inicio y finalización a números de secuenciaDECLARE @from_lsn binary(10), @to_lsn binary(10);SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @StartDate) -- >SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @EndDate) -- <=

--Manejar correctamente NulosIF (@from_lsn IS NULL) OR (@to_lsn IS NULL)-- No hubo transacciones en éste marco de tiempo

--Extraer CambiosSELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Customers(@from_lsn, @to_lsn, 'all')

Demo – CDC

Preguntas

GRACIAS!

@flacMVP@sqlpassmedhttp://geeks.ms/blogs/fangarita/