Un DataWarehouse es un sistema vivo, y es rara la vez en la que la ejecución de un proceso ETL no implica modificaciones de la información persistida en el mismo; esto es un proceso rutinario al que estamos totalmente acostumbrados. Pero al igual que se producen cambios en el contenido de las tablas, a veces son también necesarios cambios en la estructura de estas. En este articulo vamos a ver cómo podemos gestionar estos cambios estructurales de forma totalmente “desatendida”, es decir, que el proceso ETL que carga dichas tablas tenga la capacidad de gestionar también los cambios estructurales de forma que no sea necesario hacer modificaciones en el proceso de carga para adaptarlo a las nuevas estructuras.

Por dar un poco de contexto, esta “problemática” surgió con un cliente que se dedica el sector del Retail, y cuya gente de negocio realiza análisis de la información teniendo en cuenta los artículos, pero con una particularidad, y es que para cada temporada de venta (Primavera/Verano y Otoño/Invierno), crean atributos ficticios para realizar dicho análisis. Estos atributos pueden no persistir en el tiempo, con lo cual, ante cada cambio de temporada, se suelen producir cambios estructurales en la tabla (en nuestro caso una dimensión Artículo).

Es necesario aclarar que, en este artículo, nos vamos a centrar exclusivamente en la parte de modelado y carga de la base de datos, por lo que no haremos referencia alguna a la explotación de la información (modelos tabulares, multidimensionales, reporting…). Hay que comentar también que, aunque aquí nos centramos en un DataWarehouse, esto es aplicable a cualquier base de datos relacional.

Tomemos como punto de partida la siguiente tabla (la cual se encarga de cargar negocio, por lo que para nosotros es un simple origen de datos)

Gestion desatendida tablas 1

Por otro lado, esta será nuestra tabla destino (dimensión “Article”):

Gestion desatendida tablas 2

* Nota: las estructuras de las tablas se han reducido para no hacer demasiado denso el ejemplo.

Lo primero que debemos hacer es crear una tabla estructuralmente igual a la tabla aux.ArticleReferenceAttributes. Usaremos esta tabla para comparar si hay cambios estructurales en nuestro origen de datos. Creamos, por ejemplo, la tabla aux.ArticleReferenceAttributes_Model, quedando de la siguiente manera:

Gestión desatendida de tablas estructuralmente cambiantes

Esta tabla la podremos dejar vacía, pues únicamente la queremos para comprobar contra la tabla de origen si ha habido algún cambio estructural.

Finalmente, generamos también una tabla para controlar las columnas que cambian en cada ejecución (esta tabla se limpiará y recargará en cada ejecución)

Gestion desatendida tablas 4

Una vez que tenemos las estructuras ya creadas y la tabla original cargada, pasamos a continuación a enumerar los pasos a seguir para poder cargar la dimensión (en este caso vamos a hacer todo el proceso a través de código T-SQL, el cual podrá, para su ejecución, agendarse en un job, incluirse en un paquete SSIS…):

1. Comprobar posibles cambios estructurales

Lo primero que debemos hacer es comprobar si ha habido algún cambio estructural en la tabla, ya que, de haberlo, si no actuamos en consecuencia, el procesó no se ejecutará de forma correcta. Comprobaremos por lo tanto la estructura de la tabla aux.ArticleReferenceAttributes contra la de la tabla aux.ArticleReferenceAttributes_Model.

 Vamos a obtener la metadata de las dos tablas y compararla. El resultado de dicha comparación lo vamos a guardar en una tabla para posteriormente recorrerla y ver si tenemos que hacer algún proceso de Drop Column o de Add Column.

Este proceso lo hacemos tanto contra la dimensión como contra la tabla de Model, pues siempre la debemos tener alineada estructuralmente con el origen de datos para poder hacer las comparaciones correctas. El proceso se detalla en el siguiente script:

Declare @execution_id bigint = 456; --Aqui va el id de ejecucion del proceso
 
-- Comprobamos si se han agregado columnas nuevas
-- Tabla sera la tabla original, la cual es susceptible de cambios
With Tabla as
(
Select COLUMN_NAME
From dw_AUX.INFORMATION_SCHEMA.COLUMNS
Where TABLE_CATALOG = 'dw_AUX' and TABLE_SCHEMA = 'aux' and TABLE_NAME = 'ArticleReferenceAttributes'
),
-- Modelo es la tabla "gemela" que se usa para ir comparando a nivel de metadata si hubo cambios o no
Modelo as
(
Select COLUMN_NAME
From dw_AUX.INFORMATION_SCHEMA.COLUMNS
Where TABLE_CATALOG = 'dw_AUX' and TABLE_SCHEMA = 'aux' and TABLE_NAME = 'ArticleReferenceAttributes'
)
 
 
-- 1. Comprobamos si existen diferencias (columnas nuevas o borradas)
-- 2. Si las hay:
-- 2.1 Insertamos las diferencias (columnas nuevas o borradas) en la tabla de control
-- 2.2 Creamos o borramos las columnas nuevas en la tabla "Modelo" para que en las siguientes ejecuciones esté alineada con la tabla "original"
Insert Into [aux].[Metadata_Columns]
([process],[action],[columnName],[$sq_execution_id],[actionDone])
Select 'ArticleReferenceAttributes', Case When a.COLUMN_NAME is null Then 'Drop Column' Else 'Add column' End,
Case When a.COLUMN_NAME is null Then b.COLUMN_NAME Else a.COLUMN_NAME End, @execution_id,0
From Tabla a
Full Join Modelo b
on a.COLUMN_NAME = b.COLUMN_NAME
Where a.COLUMN_NAME is null or b.COLUMN_NAME is null

En caso de que no existan cambios estructurales, podemos ir directamente al paso 3, si los hubiera iremos al paso siguiente.

 

2. Igualación estructural de las tablas

Recorremos la tabla aux.Metadata_Columns y vamos generando y ejecutando por cada registros los scripts necesarios (borrar o agregar columna) sobre las dos tablas de forma que dejemos todo alineado. El proceso se detalla en el siguiente script:

-- Columnas nuevas
Declare @inserts int = 0
Declare @insertCount int = 0
 
Select @inserts = count(*)
From [aux].[Metadata_Columns]
Where process = 'ArticleReferenceAttributes' and [action] = 'Add Column' and actionDone = 0
 
While @insertCount < @inserts
Begin
Declare @sql varchar(200) = ''
Declare @sqlDim varchar(200) = ''
Declare @col varchar (50) = ''
 
Set @sql = 'Alter Table dw_aux.aux.ArticleReferenceAttributes_Model Add '
Set @sql = 'Alter Table dw_dwh.dim.Article Add '
 
Select @col = columnName
From [aux].[Metadata_Columns]
Where process = 'ArticleReferenceAttributes' and [action] = 'Add Column' and actionDone = 0
 
Set @sql = @sql + @col + ' nvarchar(255)' -- en este caso son todas de este tipo, si hubiera de otros, tendriamos que inferir tambien los tipos
Set @sqlDim = @sqlDim + @col + ' nvarchar(255)'
exec(@sql)
exec(@sqlDim)
 
Update [aux].[Metadata_Columns]
Set actionDone = 1
Where process = 'ArticleReferenceAttributes' and [action] = 'Add Column' and columnName = @col
 
set @insertCount = @insertCount + 1
End
 
-- Columnas a eliminar
Declare @deletes int = 0
Declare @deleteCount int = 0
 
Select @deletes = count(*)
From [aux].[Metadata_Columns]
Where process = 'ArticleReferenceAttributes' and [action] = 'Drop Column' and actionDone = 0
 
While @deleteCount < @deletes
Begin
Set @sql = 'Alter Table dw_aux.aux.ArticleReferenceAttributes_Model Drop Column '
Set @sqlDim = 'Alter Table dw_dwh.Article Drop Column '
 
Select @col = columnName
From [aux].[Metadata_Columns]
Where process = 'ArticleReferenceAttributes' and [action] = 'Drop Column' and actionDone = 0
 
Set @sql = @sql + @col
Set @sqlDim = @sqlDim + @col
exec(@sql)
exec(@sqlDim)
 
Update [aux].[Metadata_Columns]
Set actionDone = 1
Where process = 'ArticleReferenceAttributes' and [action] = 'Drop Column' and columnName = @col
 
set @deleteCount = @deleteCount + 1
End

 

 

3. Carga de la dimensión

Llegados a este punto, las tablas están alineadas estructuralmente, por lo cual el proceso de carga de la dimensión podemos decir que es trivial y en consecuencia no entraremos a analizarlo: podremos usar SSIS, T-SQL o el proceso que más nos guste y del que más rendimiento saquemos.

Estos pasos que acabamos de ver nos permitirán que nuestros procesos de carga de este tipo de tablas sean totalmente “autónomos”, y que los posibles cambios que se realicen desde negocio en sus tablas no necesiten de alguna intervención manual para que nuestro flujo de carga del DataWarehouse siga funcionando correctamente. Obviamente, nuestro proceso puede ser mucho más complejo; aquí lo hemos reducido porque el objetivo principal es entender lo que hay que hacer y el por qué. A partir de aquí, podremos escalar el proceso con casos tales como:

  • Uso de índices en las tablas
  • Manejar tipos de datos distintos
  • Que las tablas sean referenciadas por otros procesos o estructuras (procedimientos almacenados, vistas…)
0 Shares:
Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

You May Also Like
Reporte financiero con cuentas aplanadas en Power BI
Leer más

Reporte financiero con cuentas aplanadas en Power BI

Hoy, nuestro experto Dani Gil, nos ayuda a crear un report financiero con cuentas aplanadas con Power BI y nos explica la solución a algunos problemas que pueden surgir basándose en su experiencia con el proyecto real de un cliente.
postgresql
Leer más

Indexación con Trigrams en PostgreSQL

En cualquier motor de base de datos es crítico entender las posibilidades que nos ofrece a nivel de indexación para maximizar su rendimiento. Hoy veremos la indexación con trigrams en PostgreSQL.