Trabajar con tablas versionadas (Fecha Desde y Fecha Hasta) en SQL Server, Slowly Changing Dimension (SCD) Tipo 2

En este Artículo pretendo describir con suficiente detalle, el funcionamiento de las tablas versionadas, es decir, aquellas tablas que utilizan campos Fecha Desde y Fecha Hasta para conseguir almacenar las diferentes versiones (histórico) de los datos a lo largo del tiempo. Este tipo de tablas, son conocidas en los entornos de Data Warehouse (DW) y Business Intelligence (BI), como Slowly Changing Dimensions (SCD) de tipo 2. Debido al nivel de profundidad con el que he decidido redactar este Artículo, he tenido que dividirlo en varios capítulos, para así conseguir alcanzar el nivel de detalle que tenía como objetivo (confío que también sea del gusto de aquellos que lo lean).

El problema de trabajar con versiones de datos, tiene dos principales alcances:

• Por un lado, el Análisis, Diseño y Desarrollo de Software de Gestión, debido a que ésta es una práctica muy habitual en aplicaciones de éste tipo (ERPs, CRMs, etc.), por la necesidad de almacenar y gestionar en bases de datos relacionales la información y su evolución a lo largo del tiempo (es decir, almacenar datos históricos). Aunque no toda la información es susceptible de ser versionada, gran parte de la información almacenada en las aplicaciones de gestión sufre cambios de estado a lo largo del tiempo, que pueden resultar de necesario seguimiento (y en consecuencia, son etiquetados con campos Fecha Desde y Fecha Hasta para conocer el periodo de validez del dato). En caso contrario, estaríamos perdiendo información.
• Por otro lado, la Consulta, Extracción y Transformación de tablas versionadas en entornos de Business Intelligence (BI) y Data Warehousing (DW), por la sencilla razón, de que si nuestros sistemas transaccionales almacenan los datos versionados, evidentemente nuestros sistemas analíticos deberán consumir (consultar, extraer o cargar, y transformar) los datos versionados (etiquetados con Fecha Desde y Fecha Hasta) para uná fácil interpretación por parte de sus usuarios (Directivos y Mandos Intermedios). En consecuencia, nuestros procesos de extracción, transformación y carga de datos (ETL) realizarán intensivas transformaciones sobre tablas versionadas, y nuestros sistemas analíticos (OLAP y Reporting) consumirán intensivamente este tipo de tablas.

Del mismo modo, antes de continuar es importante aclarar que éste artículo no explica cómo trabajar con fechas en SQL Server. Es decir, no se explican las funciones de Fecha y Hora de SQL Server, ni se pretende explicar los diferentes tipos de datos de Fecha y/o Hora de SQL Server, etc. Para consultar dudas de éste tipo, puede consultarse el artículo ¿Cómo trabajar con Fechas en SQL Server?.

A lo largo de los diferentes capítulos, se utilizará el ejemplo bancario de las Cuentas Corrientes y los Titulares (se presentará con más detalle más adelante), con el fin de que al utilizar un mismo ejemplo para los distintos aspectos tratados, se facilite la comprensión de estos contenidos (al menos, lo he hecho con esta intención).

Puestos en situación, llega el momento de entrar en Harina, y con ello empezamos con los diferentes capítulos del Artículo. Ale, a disfrutá !!

• Definición de un Modelo de Versionado.
  Este capítulo define un Modelo de Versionado que utilizaremos a lo largo de todo el Artículo, y que facilita el conocimiento y comprensión del funcionamiento de las tablas versionadas (tablas que almacenan históricos de cambios), en particular, de cómo se rellenan sus campos Fecha Desde y Fecha Hasta, tanto para es desarrollo de sistemas operacionales, como para una correcta extracción, transformación y carga de datos (ETL) en entornos de Data Warehouse (DW) y Business Intelligence (BI). También se aprovecha para indicar las diferencias comunes entre Modelos de Versionado diferentes, se hace hincapié en la dependencia que se tiene cara al desarrollo de consultas SQL sobre tablas versionadas, se comentan alternativas de diseño, etc.
  
  
• El Versionado (Fecha Desde y Fecha Hasta) y otras Fechas relacionadas (Fecha Alta, Fecha Baja, Fecha Creación o Timestamp, etc.)
  En este capítulo se describen otros campos de tipo Fecha también relacionados con el Versionado (Fecha Desde y Fecha Hasta) que muy habitualmente nos encontraremos en tablas versionadas: Fecha Hora de Creación (Timestamp), Fecha Hora de Actualización, Fecha Alta y Fecha Baja, son quizás los campos de tipo Fecha que más habitualmente nos podremos encontrar en tablas versionadas. Su conocimiento y comprensión son vitales, debido a que pueden afectar al desarrollo de consultas SQL sobre tablas versionadas, en función de cuál sea el dato que deseemos obtener, y qué campos posea la tabla versionada.
  
  
• Rangos de Fechas válidos y Tipos de Datos de Fecha y/o Hora
  Al trabajar con tablas versionadas (Fecha Desde y Fecha Hasta) y con distintos motores de base de datos, resulta especialmente importante conocer las características de los tipos de dato Fecha que disponemos: ¿Almacenan sólo Fecha o Fecha y Hora? ¿Qué Fechas mínima y máxima permite almacenar cada tipo de dato? ¿Con qué precisión se almacena la información horaria? ¿Qué calendario se utiliza: Calendario Juliano o Calendario Gregoriano? ¿Qué precisión y rangos de Fecha y Hora soportan SQL Server, Access, My SQL, ORACLE, o DB2 en sus tipos de datos de Fecha y/o Hora?
  
  
• El Gráfico de Línea de Tiempo (Time Line), el Sudoku de las Fechas
  Este capítulo describe el Gráfico de Línea de Tiempo, una herramienta de gran utilidad para facilitar la construcción de Consultas SQL sobre tablas versionadas, pudiendo comprobar los distintos casos que pueden producirse para así validar la fiabilidad de nuestro algoritmo (es decir, de nuestra consulta SQL, especialmente las cláusulas WHERE y JOIN). Resulta especialmente útil, cuando necesitamos denormalizar dos tablas versionadas (ambas con sus campos Fecha Desde y Fecha Hasta, caso habitual en entornos de Business Intelligence - BI - y Data Warehouse - DW) y en consultas SQL similares.
  
  
• Cómo definir una Clave Unica o Indice Unico sobre una tabla con Versionado (Fecha Desde y Fecha Hasta)
  Este Capítulo examina las diferentes alternativas posibles cara a la necesidad de definir de Claves Primarias o Índices Únicos sobre tablas versionadas (con Fecha Desde y Fecha Hasta) debido al potencial riesgo de duplicidad de valores (violación de claves) si utilizamos sólo la Clave de Negocio (Business Key). Las Claves Primarias o Indices Unicos permiten garantizar la integridad de los datos en lo referente a la unicidad de filas, facilitan tareas como comprobar versiones solapadas, etc. En entornos de Business Intelligence (BI) y Data Warehouse (DW) facilitan la creación de Claves Subrogadas en tablas de Dimensiones de Cubos OLAP, etc. ¿Puede interesarnos un valor autonumérico o secuencial? ¿Puede interesarnos un campo Fecha Hora de Creación o Timestamp?
  
  
• Como consultar la versión válida a una Fecha
  Este capítulo describe cómo realizar una consulta SQL sobre una tabla versionada (con Fecha Desde y Fecha Hasta) para obtener la versión válida a un Fecha, algo muy habitual al trabajar con tablas versionadas (ej: obtener la versión válida de un Titular a fecha de Movimiento; obtener la versión válida de un Cliente a Fecha de Factura), tanto en entornos transaccionales (OLTP) como en entornos de Business Intelligence (BI) y Data Warehouse (DW) especialmente para Reporting. Se incluyen ejemplos de consultas SQL (en Transact-SQL), se explica la lógica de estas consultas SQL, se considera el caso de tablas versionadas que incluyan los campos Fecha Alta y Fecha Baja, etc.
  
  
• Como Denormalizar dos Tablas Versionadas (con Fecha Desde y Fecha Hasta)
  Este capítulo detalla un método o algoritmo para denormalizar dos tablas versionadas (con Fecha Desde y Fecha Hasta), por supuesto, sin utilizar cursores. Esta técnica resulta de gran utilidad en entornos de Business Intelligence (BI) y Data Warehouse (DW), tanto para Reporting como para construir tablas de Dimensión en entornos OLAP. Se explica también, como para denormalizar varias tablas versionadas es posible aplicar este método o algoritmo varias veces consecutivas. Se incluyen consultas SQL de ejemplo, se detalla qué campos son estrictamente necesarios, y otras alternativas de diseño cara a la implementación de este método o algoritmo en entornos de producción de base de datos (a poder ser, con SQL Server, of course ;-).
  
  
• Como comprobar la existencia de versiones solapadas en Tablas Versionadas SCD Tipo 2 (con Fecha Desde y Fecha Hasta)
  Este capítulo explica una de las tareas de comprobación que suele resultar necesaria al trabajar con tablas versionadas: comprobar la existencia de versiones solapadas en una Tabla Versionada SCD Tipo 2 (con Fecha Desde y Fecha Hasta). Esta comprobación resulta especialmente útil en entornos de Data Warehouse (DW) y Business Intelligence (BI), como medio para garantizar los datos resultantes de los procesos de carga y transformación de datos (ETL), que en ocasiones realizan intensivas transformaciones sobre las tablas versionadas (Slowly Changing Dimensions) de origen. Este capítulo explica cómo realizar esta comprobación con consultas SQL (es decir, utilizando Transact-SQL), con el ejemplo bancario de las Cuentas Corrientes y los Titulares utilizado anteriormente.
  
  

Y con esto (ni más, ni menos ;-) doy por finalizado el presente Artículo, bajo mi opinión (susceptible de fallo) un Problema bastante común y de un gran interés técnico y funcional. Confío que estos contenidos estén lo suficientemente claros para su comprensión, y por supuesto, espero que resulte de gran utilidad al público (como siempre digo, espero que os sirva...).