GuilleSQL :: Microsoft SQL Server, SSIS, y más !!

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


Este Artículo describe la utilización de tablas con versiones de filas (tablas versionadas con Fecha Desde y Fecha Hasta), muy habitual en bases de datos de sistemas transaccionales (OLTP) independentemente del Motor de Base de Datos utilizado (SQL Server, ORACLE, DB2, Informix, MySQL, etc.), y también en entornos de Data Warehouse (DW) y Business Intelligence (BI), donde se conocen como Slowly Changing Dimension (SCD) de Tipo 2, utilizándose tanto para Reporting como para OLAP. ¿Cómo trabajar con tablas Slowly Changing Dimensiones (SCD) de tipo 2? ¿Cómo diseñar un Modelo de Versionado? ¿Cómo funciona el Versionado en tablas SCD Tipo 2? ¿Cómo desarrollar en SQL Server consultas sobre tablas versionadas SCD de Tipo 2? ¿Cómo consultar con SQL una tabla Versionada SCD de Tipo 2? ¿Cómo comprobar la existencia o no de Versiones Solapadas?

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...).


[Fecha del Artículo (UTC): 28/09/2008]
[Autor: GuilleSQL]



Escribir un Comentario

Para poder escribir un comentario, debe Iniciar Sesión con un usuario.

Si no dispone de un usuario, puede Registrarse y hacerse miembro.

Si dispone de un usuario, pero no recuerda sus credenciales de acceso, puede Restablecer su Contraseña.

Miembros de
Miembros de GITCA (Global IT Community Association)

Menu de Usuario
  Iniciar Sesión
  Registrarse
  Restablecer Contraseña
  Ventajas de Registrarse

Acerca de
  Contigo desde Oct 2007
  771 usuarios registrados
  86146 pageloads/mes
  Ranking Alexa 498160

Social Networks
Sigue a Portal GuilleSQL en Linkedin !!
Sigue a Portal GuilleSQL en Twitter !!



Archivo

Junio de 2017 (3)
Mayo de 2017 (1)
Marzo de 2017 (3)
Enero de 2017 (4)
Junio de 2016 (1)
Mayo de 2016 (2)
Abril de 2016 (2)
Septiembre de 2015 (2)
Agosto de 2015 (2)
Junio de 2015 (10)
Mayo de 2015 (4)
Abril de 2015 (8)
Marzo de 2015 (11)
Octubre de 2014 (3)
Septiembre de 2014 (7)
Agosto de 2014 (5)
Julio de 2014 (2)
Mayo de 2014 (4)
Abril de 2014 (4)
Marzo de 2014 (4)
Febrero de 2014 (1)
Enero de 2014 (5)
Diciembre de 2013 (8)
Noviembre de 2013 (2)
Octubre de 2013 (7)
Septiembre de 2013 (6)
Agosto de 2013 (1)
Julio de 2013 (6)
Junio de 2013 (11)
Mayo de 2013 (7)
Abril de 2013 (6)
Febrero de 2013 (5)
Enero de 2013 (7)
Diciembre de 2012 (12)
Noviembre de 2012 (13)
Octubre de 2012 (5)
Septiembre de 2012 (3)
Agosto de 2012 (6)
Julio de 2012 (4)
Junio de 2012 (1)
Mayo de 2012 (2)
Abril de 2012 (7)
Marzo de 2012 (16)
Febrero de 2012 (9)
Enero de 2012 (5)
Diciembre de 2011 (10)
Noviembre de 2011 (10)
Octubre de 2011 (4)
Septiembre de 2011 (5)
Agosto de 2011 (2)
Julio de 2011 (2)
Junio de 2011 (4)
Mayo de 2011 (2)
Abril de 2011 (6)
Marzo de 2011 (4)
Febrero de 2011 (10)
Enero de 2011 (5)
Diciembre de 2010 (6)
Noviembre de 2010 (4)
Octubre de 2010 (8)
Septiembre de 2010 (4)
Agosto de 2010 (1)
Julio de 2010 (3)
Mayo de 2010 (5)
Abril de 2010 (6)
Marzo de 2010 (8)
Febrero de 2010 (3)
Enero de 2010 (1)
Diciembre de 2009 (9)
Noviembre de 2009 (14)
Octubre de 2009 (2)
Septiembre de 2009 (8)
Agosto de 2009 (2)
Julio de 2009 (10)
Junio de 2009 (9)
Mayo de 2009 (10)
Abril de 2009 (9)
Marzo de 2009 (3)
Febrero de 2009 (2)
Enero de 2009 (3)
Noviembre de 2008 (2)
Octubre de 2008 (2)
Septiembre de 2008 (2)
Agosto de 2008 (5)
Julio de 2008 (5)
Junio de 2008 (1)
Mayo de 2008 (3)
Abril de 2008 (2)
Marzo de 2008 (2)
Febrero de 2008 (2)
Enero de 2008 (5)
Noviembre de 2007 (2)
Octubre de 2007 (2)






Esta información se proporciona "como está" sin garantías de ninguna clase, y no otorga ningún derecho.
This information is provided "AS IS" with no warranties, and confers no rights.

Copyright © 2007 GuilleSQL, todos los derechos reservados.
GuilleSQL.com y GuilleSQL.net son también parte de Portal GuilleSQL.

Visitas recibidas (Page Loads) en GuilleSQL (fuente: StatCounter):

screen resolution stats
Visitas