SQL Server, SQL Server 2012, Trucos

Tablas numéricas auxiliares (Tally Tables) en acción. Tablas de dimensión para cubos OLAP

Después de los artículos dedicados a la creación de tablas numéricas auxiliares publicados con anterioridad (parte1 y parte2), en esta ocasión vamos a aplicar de forma práctica esta útil herramienta en algunos escenarios con los que podemos encontrarnos en nuestra labor como desarrolladores de bases de datos.

En el presente artículo vamos a abordar un caso al que nos enfrentaríamos si estuviéramos desarrollando el modelo de datos para un cubo OLAP, cuya finalidad sea analizar la cantidad de población de una determinada región o país, y entre cuyos requerimientos se encuentren la elaboración de sendas dimensiones (con sus correspondientes tablas de datos) para mostrar la edad y fecha de nacimiento de los individuos que componen la población, teniendo en cuenta que los valores de edad deberán estar comprendidos en un rango de 0 a 120 años, mientras que las fechas de nacimiento oscilarán en un intervalo que va desde el 01/01/1900 hasta el 30/09/2014.

Para la estructura de la tabla de edades, además de la propia edad del individuo, añadiremos dos columnas adicionales que servirán para representar los atributos de edad decenal y quinquenal, utilizados habitualmente en sistemas de información demográfica. Por otra parte, en la tabla de fechas de nacimiento, además de las columnas que identifican las partículas de una fecha: año, mes y día, añadiremos columnas para los nombres del mes y día de la semana.


CREATE TABLE Edad
(
EdadID int NOT NULL,
EdadDecenal varchar(15) NULL,
EdadQuinquenal varchar(15) NULL,
CONSTRAINT PK_Edad PRIMARY KEY CLUSTERED (EdadID)
);

CREATE TABLE Fecha
(
FechaID date NOT NULL,
Anualidad int NULL,
Mes int NULL,
MesNombre varchar(20) NULL,
Dia int NULL,
DiaSemana varchar(20) NULL,
CONSTRAINT PK_Fecha PRIMARY KEY CLUSTERED (FechaID)
);

Para rellenar cada una de estas tablas mediante el método tradicional utilizaríamos un bucle WHILE, dentro del cual ejecutaríamos una sentencia INSERT en cada una de sus iteraciones.


DECLARE @nContador AS int;

SET @nContador = 0;

WHILE (@nContador <= 120)
BEGIN
  INSERT INTO Edad (EdadID,EdadDecenal,EdadQuinquenal)
  VALUES (@nContador,
  --...
  --...
  --valores calculados para las columnas EdadDecenal y EdadQuinquenal
  --...
  --...
  );

  SET @nContador = @nContador + 1;
END

--////////////////////////////////////////////////////
DECLARE @nContador AS int;
DECLARE @dtInicio AS date;
DECLARE @dtFin AS date;
DECLARE @dtFechaInsertar as date;

SET LANGUAGE Spanish;
SET @nContador = 0;
SET @dtInicio = '19000101';
SET @dtFin = '20140930';

WHILE (@nContador <= DATEDIFF(day,@dtInicio,@dtFin))
BEGIN
  SET @dtFechaInsertar = DATEADD(day,@nContador,@dtInicio);

  INSERT INTO Fecha (FechaID,Anualidad,Mes,MesNombre,Dia,DiaSemana)
  VALUES (@dtFechaInsertar,
  YEAR(@dtFechaInsertar),
  MONTH(@dtFechaInsertar),
  DATENAME(month,@dtFechaInsertar),
  DAY(@dtFechaInsertar),
  DATENAME(weekday,@dtFechaInsertar)
  );

  SET @nContador = @nContador + 1;
END

Pero si queremos aplicar un método más acorde a un entorno de programación basado en conjuntos (set based programming) como SQL Server, podemos optar por utilizar la técnica que da origen a este artículo: tablas numéricas auxiliares.

En primer lugar crearemos la tabla auxiliar a la que llamaremos Numeros, con base cero (valor cero como primer registro) y conteniendo 50000 filas (ver su modo de creación en este enlace). Dicha cantidad de registros será necesaria, evidentemente, no para la operación de carga de la tabla de edades, sino para la tabla de fechas.

A continuación rellenaremos la tabla Edad a partir de una consulta contra la tabla numérica, que devuelva los registros cuyo valor para la columna NumeroID esté comprendido en el intervalo de 0 a 120. La columna NumeroID se asignará directamente a la columna EdadID de la tabla Edad, mientras que para las otras dos columnas de esta tabla crearemos sendos valores calculados mediante expresiones CASE.


INSERT INTO Edad (EdadID,EdadDecenal,EdadQuinquenal)
SELECT NumeroID,
CASE
  WHEN NumeroID = 0 THEN '0'
  WHEN NumeroID BETWEEN 1 AND 4 THEN '1-4'
  WHEN NumeroID BETWEEN 5 AND 14 THEN '5-14'
  WHEN NumeroID BETWEEN 15 AND 24 THEN '15-24'
  WHEN NumeroID BETWEEN 25 AND 34 THEN '25-34'
  WHEN NumeroID BETWEEN 35 AND 44 THEN '35-44'
  WHEN NumeroID BETWEEN 45 AND 54 THEN '45-54'
  WHEN NumeroID BETWEEN 55 AND 64 THEN '55-64'
  WHEN NumeroID BETWEEN 65 AND 74 THEN '65-74'
  WHEN NumeroID BETWEEN 75 AND 84 THEN '75-84'
  WHEN NumeroID >= 85 THEN '85+'
END
,
CASE
  WHEN NumeroID BETWEEN 0 AND 4 THEN '0-4'
  WHEN NumeroID BETWEEN 5 AND 9 THEN '5-9'
  WHEN NumeroID BETWEEN 10 AND 14 THEN '10-14'
  WHEN NumeroID BETWEEN 15 AND 19 THEN '15-19'
  WHEN NumeroID BETWEEN 20 AND 24 THEN '20-24'
  WHEN NumeroID BETWEEN 25 AND 29 THEN '25-29'
  WHEN NumeroID BETWEEN 30 AND 34 THEN '30-34'
  WHEN NumeroID BETWEEN 35 AND 39 THEN '35-39'
  WHEN NumeroID BETWEEN 40 AND 44 THEN '40-44'
  WHEN NumeroID BETWEEN 45 AND 49 THEN '45-49'
  WHEN NumeroID BETWEEN 50 AND 54 THEN '50-54'
  WHEN NumeroID BETWEEN 55 AND 59 THEN '55-59'
  WHEN NumeroID BETWEEN 60 AND 64 THEN '60-64'
  WHEN NumeroID BETWEEN 65 AND 69 THEN '65-69'
  WHEN NumeroID BETWEEN 70 AND 74 THEN '70-74'
  WHEN NumeroID BETWEEN 75 AND 79 THEN '75-79'
  WHEN NumeroID BETWEEN 80 AND 84 THEN '80-84'
  WHEN NumeroID BETWEEN 85 AND 89 THEN '85-89'
  WHEN NumeroID >= 90 THEN '90+'
END
FROM Numeros
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST 121 ROWS ONLY;

En el caso de que nuestra versión de SQL Server no soporte el uso de OFFSET…FETCH, emplearemos la cláusula TOP para limitar el número de filas a obtener de la tabla Numeros.


INSERT INTO Edad (EdadID,EdadDecenal,EdadQuinquenal)
SELECT TOP(121) NumeroID,
...
...

Seguidamente vemos una muestra del contenido de la tabla Edad tras la inserción de las filas.

TablasNumericasAuxiliaresTallyTableEnAccionTablasDimension_01

 

Respecto a la tabla Fecha, para generar cada una de las filas a insertar utilizaremos nuestra tabla numérica, pasando el valor del campo NumeroID a la función DATEADD, tomando como referencia del cálculo la fecha menor del rango. La cantidad de días a obtener será calculada desde OFFSET…FETCH mediante la función DATEDIFF, a la que pasaremos como parámetro las fechas inferior y superior del rango. Toda esta operación la agruparemos en una expresión de tabla (Common Table Expression o CTE) contra la que ejecutaremos una sentencia para insertar las filas en la tabla Fecha, aplicando funciones de tratamiento de fechas para obtener las diversas partículas comentadas anteriormente.


DECLARE @dtInicio AS date;
DECLARE @dtFin AS date;

SET LANGUAGE Spanish;

SET @dtInicio = '19000101';
SET @dtFin = '20140930';

WITH cteFechas AS
(
  SELECT
  DATEADD(day,numeroid,@dtInicio) AS Fecha
  FROM Numeros
  ORDER BY NumeroID
  OFFSET 0 ROWS
  FETCH FIRST DATEDIFF(day,@dtInicio,@dtFin) + 1 ROWS ONLY
)
INSERT INTO Fecha
SELECT Fecha,
YEAR(Fecha),
MONTH(Fecha),
DATENAME(month,Fecha),
DAY(Fecha),
DATENAME(weekday,Fecha)
FROM cteFechas;

Al igual que en el caso anterior, si no podemos usar OFFSET…FETCH volveremos a emplear TOP.


--...
--...
WITH cteFechas AS
(
  SELECT TOP(DATEDIFF(day,@dtInicio,@dtFin) + 1)
  DATEADD(day,numeroid,@dtInicio) AS Fecha
  FROM Numeros
--...
--...

TablasNumericasAuxiliaresTallyTableEnAccionTablasDimension_02

Como acabamos de comprobar, mientras que el uso de WHILE para rellenar una tabla  con las características presentadas en este artículo supone la ejecución de múltiples sentencias INSERT, con el consiguiente impacto en el rendimiento, la utilización de una tabla numérica permite realizar el mismo trabajo en una única sentencia de inserción, lo que redunda en un mejor aprovechamiento de los recursos del servidor de datos. Confiamos en que los ejemplos aquí presentados resulten de utilidad al lector.

1 Comentario

  1. anonymous

    Después de explicar en el anterior artículo el modo de creación de tablas de dimensión

Deja un comentario

Tema creado por Anders Norén