Tratamiento de datos duplicados en SQL Server (y 2)

Tal y como apuntábamos al finalizar la primera parte del
artículo, en esta segunda entrega expondremos algunas técnicas adicionales para
la manipulación de datos duplicados, consistentes en el acceso a valores situados
en la primera y última fila de las particiones que constituyen nuestros
conjuntos de resultados.

Pero antes de proseguir, puesto que en el último ejemplo de
la anterior entrega realizábamos un borrado de filas en la tabla Ventas,
tendremos que volver a ejecutar la sentencia de creación e inserción de datos
en dicha tabla, que podemos encontrar al comienzo de la primera
parte
de este artículo.

 

Encontrar la primera fila en un conjunto de
datos duplicados

Si necesitamos
obtener de un conjunto de resultados con datos repetidos el valor de una
columna situada en la primera fila podemos emplear la función FIRST_VALUE. Aunque es cierto que
lograríamos el mismo efecto mediante el uso de TOP 1, FIRST_VALUE, al ser una
función de ventana, presenta como ventaja adicional la definición de
particiones mediante la cláusula OVER (al igual que ROW_NUMBER), con lo cual
obtendremos el primer valor de cada partición. Veamos este aspecto de
forma práctica.

 

SELECT *,
FIRST_VALUE(LineTotal)
                OVER(PARTITION BY
SalesOrderNumber ORDER BY ProductID) AS PrimerValor
FROM Ventas
WHERE SalesOrderNumber IN (‘SO58536′,’SO68514’)
ORDER BY SalesOrderNumber, ProductID

 

Lo que
hace la anterior consulta es añadir una nueva columna al conjunto de resultados
con el primer valor de cada partición, pero lo que nosotros perseguimos es la
obtención de la primera fila de cada una de dichas particiones. Para conseguir
dicho objetivo situaremos dentro de una CTE (expresión común de tabla) una
consulta que nos devuelva la información necesaria de cada primera fila.
Cruzando este resultado con la tabla Ventas mediante una combinación de tipo
INNER JOIN, obtendremos única y exclusivamente las primeras filas objeto de
nuestra búsqueda.

WITH ctePrimeraFila AS
(
                SELECT DISTINCT
                SalesOrderNumber,
                FIRST_VALUE(LineTotal)
                               OVER(PARTITION
BY SalesOrderNumber ORDER BY ProductID) AS PrimerValor
                FROM Ventas
                WHERE SalesOrderNumber IN
(‘SO58536′,’SO68514’)
)
SELECT Ventas.*
FROM Ventas
INNER JOIN ctePrimeraFila
ON Ventas.SalesOrderNumber = ctePrimeraFila.SalesOrderNumber
AND Ventas.LineTotal = ctePrimeraFila.PrimerValor

 

 

En el
caso de que nuestra versión de SQL Server (por ejemplo 2005) no disponga de
FIRST_VALUE, y queramos crear una columna calculada conteniendo el valor de la
primera fila de cada partición, también acudiremos al uso de expresiones de
tabla (CTE), pero combinándolas esta vez con la función ROW_NUMBER.

 

WITH
cteCalcularPrimerValor AS
(
                SELECT
                SalesOrderNumber,
                LineTotal,
                ROW_NUMBER()
                    OVER(PARTITION BY
SalesOrderNumber ORDER BY ProductID) AS NumeroFila
                FROM Ventas
                WHERE SalesOrderNumber IN
(‘SO58536′,’SO68514’)
)
, ctePrimerValor AS
(
                SELECT SalesOrderNumber,
LineTotal AS PrimerValor
                FROM
cteCalcularPrimerValor
                WHERE NumeroFila = 1
)
SELECT v.*, p.PrimerValor
FROM Ventas AS v
INNER JOIN ctePrimerValor AS p
ON v.SalesOrderNumber = p.SalesOrderNumber
ORDER BY v.SalesOrderNumber, v.ProductID

 

En el anterior bloque de código necesitamos dos CTEs: la primera nos
ayuda a generar un número de orden para cada una de las filas del conjunto de
resultados, mientras que en la segunda filtramos las filas de la primera CTE
que en la columna del número de orden tengan el valor 1. Finalmente, en la
consulta externa a las CTEs combinamos la tabla Ventas con la segunda CTE para
obtener la columna correspondiente a los primeros valores, es decir, la columna
que obtendríamos en caso de disponer de la función FIRST_VALUE.

 

 

Por otro
lado, y basándonos en la anterior consulta, quizá nos interese recuperar
solamente las filas de cada partición que correspondan al primer valor, por lo
que la consulta se simplificaría, filtrando en el resultado de la CTE aquellas
filas en las que la columna calculada NumeroFila sea igual a 1.

 

WITH
cteCalcularPrimerValor AS
(
                SELECT
                *,
                ROW_NUMBER()
                     OVER(PARTITION BY
SalesOrderNumber ORDER BY ProductID) AS NumeroFila
                FROM Ventas
                WHERE SalesOrderNumber IN
(‘SO58536′,’SO68514’)
)
SELECT *
FROM cteCalcularPrimerValor
WHERE NumeroFila = 1

 

Encontrar la última fila en un conjunto de
datos duplicados

De
igual forma nos hallaremos en algún momento ante la necesidad de obtener la
última fila de un conjunto de resultados, con la dificultad añadida de que si hemos
definido particiones, en un buen número de ocasiones la última fila de cada
bloque de datos repetidos no estará en la misma posición, es decir, en una
partición puede que la encontremos en la cuarta fila, en otra la sexta y así
sucesivamente.

 

 

Para
abordar este escenario disponemos de la función LAST_VALUE, que representa la versión
opuesta de la función explicada en el apartado anterior, ya que su finalidad
consiste en devolver un valor correspondiente a la última fila de un conjunto
de resultados. Si dicho conjunto tuviera varias particiones creadas con la
cláusula obligatoria OVER, obtendríamos el último valor de cada una de
ellas. No obstante, comencemos por un ejemplo simple, sin particiones.

 

SELECT *, LAST_VALUE(LineTotal)
OVER(ORDER BY Name) AS UltimoValor
FROM Ventas
WHERE SalesOrderNumber IN (‘SO58536′,’SO68514’)
ORDER BY Name

 

¿Qué ha
ocurrido aquí? Aparentemente el uso que hacemos de LAST_VALUE es correcto:
pasamos como parámetro la columna LineTotal, de la cual queremos obtener el
valor de la última fila, y mediante la cláusula OVER establecemos el orden que
tendrán las filas a efectos de esta función. Sin embargo, la columna calculada UltimoValor
no devuelve en todos los casos 4.990000, que es el valor que debería tener si
observamos los datos resultantes en la columna LineTotal de la última fila, por lo que podemos deducir que el uso de LAST_VALUE no resulta, a priori, tan intuitivo
como el de FIRST_VALUE.

El
problema radica en que dentro de OVER no hemos especificado el criterio que
establece los límites de la ventana/partición (si no declaramos una partición,
SQL Server toma implícitamente todas las filas de la consulta como una especie
de partición predeterminada), por lo que una función de ventana como LAST_VALUE
no sabe “orientarse” a la hora de calcular el último valor, puesto
que no tiene los puntos de referencia necesarios para determinarlo.

Consultando
la documentación relativa a OVER veremos que existe un conjunto de palabras
clave (ROWS, CURRENT ROW, PRECEDING, FOLLOWING, UNBOUNDED, etc.) que serán las encargadas
de dotar a las filas de este comportamiento, por lo que si escribimos la
sentencia de la siguiente manera obtendremos el resultado deseado.

SELECT *,
                LAST_VALUE(LineTotal)
                OVER(
                               ORDER BY
Name
                               ROWS
BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
                )
                AS UltimoValor
FROM Ventas
WHERE SalesOrderNumber IN (‘SO58536′,’SO68514’)
ORDER BY Name

 

Lo que hemos indicado ahora
en la función de ventana se podría traducir como: “Obtener el valor de la
última fila (LAST_VALUE) ordenando (ORDER BY) por la columna Name las filas
(ROWS) que se encuentran entre (BETWEEN) la actual (CURRENT ROW) y el final de
la ventana/partición (UNBOUNDED FOLLOWING)”.

De la
sentencia y explicación del párrafo anterior lo que quizá pueda ocasionar una
mayor confusión al lector sea la mención al concepto de fila actual (CURRENT
ROW), por lo que esta cuestión merece que le dediquemos un tratamiento más
detallado.

En la
consulta sobre la que estamos trabajando, una vez que se realiza la extracción
principal de registros de la tabla, la cláusula OVER de la función LAST_VALUE
toma el control para ordenar internamente las filas por la columna Name. Hecho esto,
comienza el cálculo del valor para cada fila de la columna UltimoValor,
siguiendo la pauta explicada a continuación.

La
primera fila del conjunto de resultados pasa a ser la fila actual (CURRENT ROW),
LAST_VALUE se desplaza hasta la última fila del conjunto de resultados, toma el
valor de la columna LineTotal, vuelve a la fila actual y deposita dicho valor
en la columna calculada UltimoValor.

A
continuación, la segunda fila del conjunto se convierte en fila actual,
realizando idéntica operación que en el caso anterior. Esta acción continuará
repitiéndose hasta haber completado la asignación de valor en la columna UltimoValor
para todas las filas.

 

 

Volviendo
a nuestro objetivo original: encontrar la última fila en un conjunto compuesto
por grupos de datos duplicados; el único cambio que tendríamos que añadir a
nuestra consulta sería la cláusula PARTITION BY, especificando el modo de
particionamiento.

 

SELECT *,
                LAST_VALUE(LineTotal)
                OVER(
                               PARTITION
BY SalesOrderNumber
                               ORDER BY
Name
                               ROWS
BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
                )
                AS UltimoValor
FROM Ventas
WHERE SalesOrderNumber IN (‘SO58536′,’SO68514’)
ORDER BY SalesOrderNumber, Name

 

 

Con la
anterior sentencia, como habrá comprobado el lector, lo que
realmente obtenemos es la información acerca de un valor en la última fila de
cada partición en forma de columna calculada. Si al igual que ocurría al
utilizar FIRST_VALUE, lo que ahora queremos 
conseguir con LAST_VALUE son las últimas filas de cada partición del
conjunto de datos, volveremos a utilizar una CTE que devuelva esas filas, que
cruzaremos en la consulta externa a la CTE con la tabla Ventas mediante INNER
JOIN, logrando de esta manera las últimas filas de cada partición.

 

WITH
cteUltimaFila AS
(
                SELECT DISTINCT
                SalesOrderNumber,
                LAST_VALUE(LineTotal)
                OVER(
                               PARTITION
BY SalesOrderNumber
                               ORDER BY
Name
                               ROWS
BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
                ) AS UltimoValor
                FROM Ventas
                WHERE SalesOrderNumber IN
(‘SO58536′,’SO68514’)
)
SELECT Ventas.*
FROM Ventas
INNER JOIN cteUltimaFila
ON Ventas.SalesOrderNumber = cteUltimaFila.SalesOrderNumber
AND Ventas.LineTotal = cteUltimaFila.UltimoValor

 

 

Supongamos
ahora que necesitamos implementar esta funcionalidad en una versión de SQL
Server que no soporta la función LAST_VALUE, en cuyo caso tendremos que optar
por una solución al problema un poco más artificiosa a través de ROW_NUMBER.

 

SELECT *,
ROW_NUMBER()
                OVER(PARTITION BY
SalesOrderNumber ORDER BY Name) AS NumeroFila
FROM Ventas
WHERE SalesOrderNumber IN (‘SO58536′,’SO68514’)

Aplicando
ROW_NUMBER a la consulta en la forma que acabamos de mostrar obtendremos dos
particiones, en una de ellas la última fila será la cuarta, y en la otra la
quinta.

 

 

Como hemos comentado anteriormente, si trabajamos con un conjunto
de resultados compuesto por muchas particiones, la variación en la posición de
la última fila para cada partición será muy grande, por lo que para acceder a dicha
fila de un modo uniforme podemos recurrir a un sencillo truco, consistente en
ordenar de forma descendente la partición usando la partícula DESC en la
cláusula ORDER BY de OVER. De este modo, en la columna calculada NumeroFila, a
las últimas filas se les asignará el número 1.

SELECT *,
ROW_NUMBER()
                OVER(PARTITION BY
SalesOrderNumber ORDER BY Name DESC) AS NumeroFila
FROM Ventas
WHERE SalesOrderNumber IN (‘SO58536′,’SO68514’)

 

Si ahora
queremos conseguir el mismo efecto que la función LAST_VALUE
utilizaremos una combinación de dos CTEs: en la primera de ellas, cteCalcularUltimoValor,
calcularemos el número de posición para la última fila tal y como acabamos de
ver, mientras que en la siguiente, cteUltimoValor, filtraremos las filas
obtenidas de cteCalcularUltimoValor, quedándonos sólo con las que tienen el
valor 1 en la columna NumeroFila, o lo que es lo mismo, las últimas filas de
cada partición. Por último, en la consulta externa a las CTEs combinaremos la
tabla Ventas y cteUltimoValor para obtener todos los datos, incluyendo la
columna calculada
del último valor.

WITH
cteCalcularUltimoValor AS
(
                SELECT
                SalesOrderNumber,
                LineTotal,
                ROW_NUMBER()
                               OVER(PARTITION
BY SalesOrderNumber ORDER BY Name DESC) AS NumeroFila
                FROM Ventas
                WHERE SalesOrderNumber IN
(‘SO58536′,’SO68514’)
)
, cteUltimoValor AS
(
                SELECT
SalesOrderNumber,LineTotal AS UltimoValor
                FROM
cteCalcularUltimoValor
                WHERE NumeroFila = 1
)
SELECT v.*, u.UltimoValor
FROM Ventas AS v
INNER JOIN cteUltimoValor AS u
ON v.SalesOrderNumber = u.SalesOrderNumber
ORDER BY v.SalesOrderNumber, v.Name

 

En el caso de que solamente necesitemos los últimos
registros de cada grupo, retocaremos la anterior sentencia, filtrando aquellos
en que la columna NumeroFila tenga el valor 1.

WITH
cteCalcularUltimoValor AS
(
                SELECT
                *,
                ROW_NUMBER()
                               OVER(PARTITION
BY SalesOrderNumber ORDER BY Name DESC) AS NumeroFila
                FROM Ventas
                WHERE SalesOrderNumber IN
(‘SO58536′,’SO68514’)
)
SELECT *
FROM cteCalcularUltimoValor
WHERE NumeroFila = 1

 

 

Conclusión

La incorporación progresiva de las
funciones de ventana al motor de datos de SQL Server constituye una potente
herramienta, aplicable a multitud de operaciones tales como la gestión de
duplicados presentada en el presente artículo. Confiamos en que los ejemplos
aquí expuestos ayuden al lector a la hora de resolver los problemas que pueda
encontrar en su trabajo de tratamiento de datos duplicados.

Tratamiento de datos duplicados en SQL Server (1)

Una de las operaciones a las que con mayor frecuencia se enfrenta cualquier desarrollador de bases de datos es la detección y tratamiento de datos duplicados, ya sea para encontrar varios registros exactamente iguales en una tabla, debido a problemas en el diseño y consistencia de la propia base de datos, o bien para localizar determinados subconjuntos de datos con condiciones que se repitan dentro de una misma tabla.

En el presente artículo vamos a explorar diversas técnicas para enfrentarnos a este tipo de escenario, que nos permitan localizar casos de información duplicada en tablas, para proceder a su análisis, o bien para poder eliminar los datos sobrantes en el caso de repeticiones innecesarias.

En primer lugar crearemos una nueva base de datos con el nombre PruebasDuplicados, y dentro de esta, una tabla que llamaremos Ventas, cuyas filas procederán de la siguiente sentencia sobre la base de datos AdventureWorks, que devuelve datos duplicados con los que podremos realizar nuestras pruebas.

 

IF (OBJECT_ID(‘Ventas’,’U’) IS NOT NULL)
BEGIN
                DROP TABLE Ventas
END

SELECT
h.SalesOrderID,
h.OrderDate,
h.SalesOrderNumber,
h.TotalDue,
d.ProductID,
d.LineTotal,
p.Name
INTO Ventas
FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
INNER JOIN AdventureWorks.Production.Product AS p
ON d.ProductID = p.ProductID

 

 

GROUP BY…HAVING…COUNT

La combinación de las cláusulas GROUP BY y HAVING de la sentencia SELECT, junto con la función COUNT representa la forma más sencilla de detectar si en una tabla existen valores duplicados para una columna o combinación de columnas, como podemos apreciar en la siguiente consulta sobre nuestra tabla de ejemplo Ventas, donde obtendremos aquellos valores de la columna SalesOrderNumber en los que se está produciendo una duplicidad, y la cantidad de filas en las que se produce para cada ocurrencia de la mencionada columna.

 

SELECT SalesOrderNumber, COUNT(*) AS RecuentoFilas
FROM Ventas
GROUP BY SalesOrderNumber
HAVING COUNT(*) > 1
ORDER BY SalesOrderNumber

 

 

Si además queremos ver el detalle de los registros que cumplen esta condición de duplicidad, situaremos la anterior sentencia (con ligeros retoques) como una subconsulta sobre la tabla Ventas, cruzando por el campo SalesOrderNumber.

 

SELECT * FROM Ventas
WHERE SalesOrderNumber IN (
                SELECT SalesOrderNumber
                FROM Ventas
                GROUP BY SalesOrderNumber
                HAVING COUNT(*) > 1
)
ORDER BY SalesOrderNumber

 

 

Empleando en la consulta externa la instrucción DELETE junto a una condición adicional, podemos borrar selectivamente ciertas filas de cada grupo de datos duplicados, como vemos en el siguiente ejemplo, donde eliminamos aquellos registros con el valor 711 en la columna ProductID.

 

 

ROW_NUMBER

Supongamos ahora que del conjunto de resultados con valores duplicados queremos operar sobre una fila muy concreta de las que componen la repetición. Mediante la siguiente consulta tomaremos en primer lugar una muestra de registros para ilustrar mejor nuestro propósito.

 

SELECT *
FROM Ventas
WHERE SalesOrderNumber IN (‘SO58536′,’SO68514’)
ORDER BY SalesOrderNumber, ProductID

 

 

Al igual que en los anteriores ejemplos, por cada valor distinto en la columna SalesOrderNumber existe un número variable de registros con dicho valor repetido. Si tuviéramos que seleccionar de cada grupo la fila situada en una determinada posición podríamos conseguirlo mediante la función ROW_NUMBER, como demostraremos posteriormente.

ROW_NUMBER, como su nombre indica, asigna un número de fila a cada registro del conjunto de resultados obtenidos a partir de una consulta.

En su forma de uso más básica, junto al nombre de la función hemos de utilizar la cláusula OVER, y dentro de esta la partícula ORDER BY, para que la asignación del número de fila se haga en un orden concreto. En el siguiente ejemplo generamos un número de fila para la tabla Ventas, ordenando el resultado por la columna LineTotal.

 

SELECT *, ROW_NUMBER() OVER(ORDER BY LineTotal) AS NumeroFila
FROM Ventas
WHERE SalesOrderNumber IN (‘SO58536′,’SO68514’)

 

 

Resulta importante resaltar que la columna utilizada dentro de ROW_NUMBER para ordenar las filas no tiene que ser obligatoriamente  la misma que empleemos en la sentencia SELECT. Variando el ejemplo anterior, a continuación vemos que los números de fila asignados por la función seguirán siendo los mismos, pero la ordenación del conjunto de resultados obtenido se hará por la columna Name.

 

SELECT *, ROW_NUMBER() OVER(ORDER BY LineTotal) AS NumeroFila
FROM Ventas
WHERE SalesOrderNumber IN (‘SO58536′,’SO68514’)
ORDER BY Name

 

 

ROW_NUMBER se encuentra entre el grupo de funciones englobadas en la categoría conocida como funciones de ventana (Window functions), debido a que aparte del funcionamiento que acabamos de ver, nos permiten definir particiones (ventanas) de filas dentro de un conjunto de resultados sobre los que actuar de forma independiente, generando un número para cada fila de la partición, la cual especificaremos en la cláusula OVER de esta función mediante la partícula PARTITION BY.

Al aplicar, por tanto, ROW_NUMBER sobre nuestra consulta, particionaremos por la columna SalesOrderNumber de la siguiente manera.

 

SELECT *,
ROW_NUMBER() OVER(PARTITION BY SalesOrderNumber ORDER BY ProductID) AS NumeroFila
FROM Ventas
WHERE SalesOrderNumber IN (‘SO58536′,’SO68514’)

 

 

Obsérvese que, tal y como hemos mencionado anteriormente, la columna mediante la que establecemos la partición es independiente de la columna que utilizamos para ordenar las filas dentro de la partición, es decir, que mientras que definimos la partición sobre SalesOrderNumber, dentro de cada partición las filas se ordenarán por ProductID.

Como vemos en el actual apartado, y como tendremos ocasión de comprobar en los siguientes, la cláusula OVER es el denominador común a todas las funciones de ventana, ya que representa el elemento que determina la forma en la que el conjunto de filas de la consulta se particiona y ordena antes de que la función de ventana sea aplicada. Una vez establecidas las particiones y el orden en las filas, se aplica el cálculo correspondiente a cada una de ellas según la función de ventana utilizada: asignación de un número de fila, obtención del primer o último valor, etc.

 

Eliminar una fila con posición intermedia dentro de un grupo de datos duplicados

Como continuación de lo expuesto en el apartado anterior, supongamos que del siguiente conjunto de datos particionado mediante ROW_NUMBER, queremos eliminar la fila de cada partición que ocupa la tercera posición.

 

SELECT *,
ROW_NUMBER() OVER(PARTITION BY SalesOrderNumber ORDER BY ProductID) AS NumeroFila
FROM Ventas
WHERE SalesOrderNumber IN (‘SO43670′,’SO43676′,’SO43693′,’SO43853’)
ORDER BY SalesOrderNumber, NumeroFila

 

 

La solución pasa por situar la anterior consulta dentro de una expresión común de tabla (CTE, common table expression) y realizar el borrado de registros desde la sentencia externa a la CTE como vemos a continuación, especificando en la cláusula WHERE el número de fila a borrar.

WITH
cteFilas AS
(
                SELECT *,
                ROW_NUMBER() OVER(PARTITION BY SalesOrderNumber ORDER BY ProductID) AS NumeroFila
                FROM Ventas
                WHERE SalesOrderNumber IN (‘SO43670′,’SO43676′,’SO43693′,’SO43853’)
)
DELETE FROM cteFilas
WHERE NumeroFila = 3

 

Llegados a este punto concluimos la primera parte del presente artículo. En la siguiente entrega veremos la forma de seleccionar valores en la primera y última fila de las particiones de datos duplicados.

Ordenación de fechas en PowerPivot

(Artículo
publicado previamente en el número 88 de dNM+, enero 2012)

El
tratamiento de fechas es un aspecto del desarrollo de un sistema de información
que puede provocarnos más de un quebradero de cabeza. PowerPivot, como
herramienta orientada al análisis de datos, permite, entre todos los tipos de
datos de que dispone, el manejo de fechas. En el presente artículo abordaremos
el uso de fechas desde PowerPivot, focalizando nuestros esfuerzos en el apartado
dedicado a la ordenación de las mismas.

Todo proceso
de desarrollo de un sistema de información implica, en mayor o menor grado,
el trabajo con elementos
temporales. Tomando como ejemplo un sistema Business Intelligence, si
observamos los cubos de datos de que está compuesto, nos percataremos de que la
mayoría dispone de dimensiones de fecha, destinadas al análisis de la
información desde una perspectiva temporal.

PowerPivot,
como ya explicamos en [1], es una herramienta self-service BI, que puede
utilizarse tanto en las fases iniciales del desarrollo de un sistema de inteligencia
de negocio, como para realizar un análisis rápido, a partir de un conjunto de
resultados obtenidos del propio sistema de información corporativo, o de
fuentes externas a éste.

 

Creación de la fuente de datos

Situemos
nuestro escenario de trabajo en este último supuesto que acabamos de mencionar:
nos entregan una tabla con datos de ventas, perteneciente a una base de datos
externa a nuestro sistema de información, sobre la que tenemos que realizar
diversos análisis; algunos de ellos implican el uso de uno de los campos de
fecha disponibles en la tabla.

Como motor
de datos emplearemos SQL Server 2008 R2, mientras que como fuente de datos
origen utilizaremos AdventureWorksDW2008 [2], copiando la tabla FactInternetSales a una nueva base
de datos con el nombre FechasPowerPivot.

 

USE master
GO

CREATE DATABASE FechasPowerPivot
GO

 

USE FechasPowerPivot
GO

SELECT * INTO FactInternetSales FROM
AdventureWorksDW2008.dbo.FactInternetSales

GO

 

Aunque
acabamos de indicar que los análisis que necesitaremos realizar sobre la tabla
FactInternetSales requerirán el uso de un campo de fecha, al observar la
estructura de la tabla FactInternetSales, advertiremos que los campos cuyos nombres
sugieren que su contenido puede contener tal información (OrderDateKey, DueDateKey
y ShipDateKey), son realmente de tipo int, teniendo sus valores un formato que
podemos identificar con las diferentes partes de una fecha, ya que los cuatro
primeros dígitos representan el año, los dos siguientes el mes, y los dos
últimos el día.

El motivo de
este diseño de tabla se debe a que en AdventureWorksDW2008, la base de datos
origen, estos campos de la tabla FactInternetSales, así como del resto de
tablas, se relacionan con el campo DateKey de la tabla DimDate, la cual constituye
el catálogo de fechas de la base de datos; donde los campos de cada registro,
que representa una fecha, identifican los diferentes elementos de la misma: año,
mes, nombre de mes, día de la semana, etc.

Podríamos
añadir la tabla DimDate a la base de datos FechasPowerPivot, lo que facilitaría
enormemente nuestra labor. Sin embargo, hay ocasiones en las que por
determinadas restricciones, sólo disponemos de un único conjunto de resultados al
que debemos acomodarnos, así que intentando emular dicho escenario, emplearemos
solamente la tabla FactInternetSales.

 

Preparación del modelo de datos en
PowerPivot

A
continuación iniciaremos Excel, creando un archivo con el nombre OrdenarFechas.xlsx.
Seguidamente abriremos la ventana de PowerPivot, donde crearemos un modelo de
datos conteniendo la tabla FactInternetSales de la base de datos FechasPowerPivot.
El proceso de creación de un modelo de datos se explica en [1].

 

La columna de fecha

Puesto que
en el modelo de PowerPivot el tipo de dato de la columna OrderDateKey también
es numérico, crearemos,
partiendo de dicha columna, una nueva columna calculada cuyo tipo de dato sea
fecha.

 

Para ello
nos situaremos en la primera columna vacía existente en la ventana de
PowerPivot, introduciendo la siguiente expresión DAX en la barra de fórmulas.

 

=DATE(LEFT(FactInternetSales[OrderDateKey],4),
MID(FactInternetSales[OrderDateKey],5,2),
RIGHT(FactInternetSales[OrderDateKey],2))

 

La función
DATE devuelve un valor de tipo fecha a partir de
los tres números que recibe como parámetro y que corresponden al año, mes y día de la fecha a crear.
Estos números son obtenidos de la columna OrderDateKey mediante el uso de las
funciones de cadena LEFT, MID y RIGHT. Aunque el tipo de dato de dicha columna
es numérico, las mencionadas funciones realizan una conversión de tipo automática
a cadena para
extraer la subcadena correspondiente en cada caso; siendo
transformados   nuevamente en números al
ser empleados en la función DATE. De esta forma habremos creado una nueva columna calculada, a
la que daremos el nombre FechaPedido editando el título
de su cabecera.

 

La jerarquía de fecha

El siguiente
paso consistirá en crear tres nuevas columnas calculadas, que representen las diferentes
partes de las fechas incluidas en la columna FechaPedido. Para ello
utilizaremos las funciones de manipulación de fechas incluidas
en el lenguaje DAX de PowerPivot que vemos a continuación.

 

=YEAR([FechaPedido])

=MONTH([FechaPedido])

=DAY([FechaPedido])

 

 

A
continuación, en la cinta de opciones de la ventana de PowerPivot,
haremos clic en la
opción “PivotTable” del grupo “Informes”, creándose de esta manera una tabla
dinámica en la ventana de Excel, que nos permitirá
analizar la información del modelo de datos de PowerPivot.

Dentro del panel “Lista de campos de PowerPivot” arrastraremos,
desde el bloque de selección de campos hasta el bloque “Etiquetas de fila”, los
campos que acabamos de crear, formando una jerarquía de fecha de tres niveles (año,
mes y día), por la que podremos navegar, expandiendo y replegando sus elementos,
para obtener diferentes grados de detalle de los datos, en base a las fechas de
venta.

 

Obteniendo
el nombre del mes

A pesar de ser completamente operativa, nuestra
jerarquía adolece de un detalle para ser más funcional: mostrar el mes como
nombre en lugar de número.

Solucionaremos este problema desplazándonos a la
ventana de PowerPivot, y creando una nueva columna calculada llamada MesNombre,
que contenga la expresión =FORMAT([FechaPedido],”mmmm”), en la que
como podemos ver, aplicaremos un formato para obtener el nombre del mes de la
columna de fecha. Posteriormente sustituiremos, en la tabla dinámica, el campo
Mes por MesNombre.

 

Observemos, no obstante, que en la tabla dinámica,
las etiquetas con los nombres de mes han quedado ordenadas alfabéticamente, en
lugar de mantener el orden de fecha, como sería más apropiado.

Para resolver este problema haremos clic en la
opción “Ordenar de A a Z” de la cinta de opciones de Excel, perteneciente al
grupo “Ordenar y filtrar” de la ficha “Opciones”, que a su vez está contenida
en la pestaña de nivel superior “Herramientas de tabla dinámica”.
Como resultado, el orden de los nombres de mes cambiará para adaptarse a la
disposición del calendario.

 

¿Dónde reside la magia que hace posible esta alteración
en el comportamiento de la ordenación? La respuesta se halla en las listas
personalizadas de Excel, una característica que, como su nombre indica, nos
permite la creación de una lista de literales en un orden particular, que no
tiene por qué ajustarse al orden predeterminado utilizado por el motor de la
hoja de cálculo.

Para acceder a estas listas personalizadas haremos
clic en el elemento “Opciones”, perteneciente a la pestaña “Archivo” de la
cinta de opciones de Excel. En esta ventana de opciones, dentro del apartado “Avanzadas”,
haremos clic en el botón “Modificar listas personalizadas”, que abrirá su
ventana de mantenimiento, donde comprobaremos que ya se encuentra presente la
lista que acabamos de utilizar con los nombres de meses.

 

Para comprobar su funcionamiento, vamos a añadir en
la ventana de PowerPivot, una columna calculada que genere los nombres de los
meses en un idioma distinto del local, utilizando de forma combinada las
funciones IF y MONTH del lenguaje DAX. Llamaremos a esta columna MesNombre2.

 

=IF(MONTH([FechaPedido])=1, “Janvier”,

IF(MONTH([FechaPedido])=2, “Février”,

IF(MONTH([FechaPedido])=3, “Mars”,

IF(MONTH([FechaPedido])=4, “Avril”,

IF(MONTH([FechaPedido])=5, “Mai”,

IF(MONTH([FechaPedido])=6, “Juin”,

IF(MONTH([FechaPedido])=7, “Juillet”,

IF(MONTH([FechaPedido])=8, “Août”,

IF(MONTH([FechaPedido])=9, “Septembre”,

IF(MONTH([FechaPedido])=10, “Octobre”,

IF(MONTH([FechaPedido])=11,
“Novembre”,

“Décembre”)

))))))))))

Si utilizamos esta nueva columna como nombre de mes
en la jerarquía de fechas, los nombres se ordenaran alfabéticamente, y en esta
ocasión permanecerán así aunque seleccionemos la opción “Ordenar de A a Z”.

Agreguemos pues una nueva lista personalizada a Excel,
compuesta por los nombres de la columna MesNombre2, y volvamos a ordenar los
meses.

 

Comprobaremos que ahora ya sí se ordenan tal y como
necesitamos, lo que nos proporciona una idea acerca del funcionamiento de estas
listas y su aplicación en tablas dinámicas.

 

Las listas
personalizadas no funcionan en segmentadores

En el caso de que en la tabla dinámica necesitemos
utilizar un segmentador basado en el campo del nombre del mes, volveremos a
encontrarnos nuevamente con el problema de la ordenación, ya que los
segmentadores no soportan la ordenación en base a las listas personalizadas.

 

En esta ocasión podemos solucionar el problema
creando en PowerPivot otra columna calculada, que llamaremos MesNumeroNombre, con
una cadena de formato que en primer lugar contenga los dígitos del mes,
seguidos del nombre: =FORMAT([FechaPedido],”mm – mmmm”).

Al utilizar esta columna como campo de la tabla
dinámica en un segmentador, el orden mostrado por sus elementos corresponderá
al que se espera de un tipo de dato fecha. Adicionalmente podemos retocar la
expresión DAX de esta columna para hacer que la primera letra del nombre del
mes se muestre en mayúscula.

 

=REPLACE(FORMAT([FechaPedido],”mm –
mmmm”), 6, 1,
UPPER(MID(FORMAT([FechaPedido],”mm
– mmmm”),6,1)))

 

 

SQL Server
2012. La solución

En el caso de que trabajemos con SQL Server 2012
(Denali) [3],
no será necesario que utilicemos las técnicas relatadas anteriormente, ya que
la versión de PowerPivot para SQL Server 2012 [4]
permite solucionar este problema de una forma mucho más sencilla y elegante,
tal y como explicamos a continuación.

Para empezar, volvamos al momento del desarrollo de
nuestro ejemplo en el que añadíamos la columna calculada MesNombre con la
función FORMAT. Continuando en la ventana de PowerPivot, en su cinta de
opciones seleccionaremos, dentro del grupo “Ordenar y filtrar”, la opción “Sort
by Column”. En la ventana del mismo nombre elegiremos, en sendos desplegables,
la columna que queremos ordenar, MesNombre, y la columna por la que se
realizará la ordenación de la primera, Mes.

 

Al actualizar estos cambios en la tabla dinámica, el
campo MesNombre ya se ordenará correctamente tanto en la propia tabla como en
el segmentador empleando únicamente el nombre del mes, lo que nos evita el uso
de las técnicas artificiosas explicadas anteriormente.

 

Conclusiones

En la
primera versión de PowerPivot, la ordenación de ciertas partes de una fecha,
tales como el nombre del mes, precisa de la utilización de ciertos trucos para
poder llevarla a cabo correctamente. La próxima aparición de SQL Server 2012, ya
disponible en Release Candidate [3], conlleva aparejada una nueva
versión de PowerPivot, que simplifica en gran medida este aspecto, facilitando
el trabajo a todos aquellos usuarios de esta potente herramienta de análisis de
información.

REFERENCIAS

[1] Blanco,
Luis Miguel. “PowerPivot, DAX y Excel. Business Intelligence para todos los
públicos”.

http://geeks.ms/blogs/lmblanco/archive/2015/04/22/powerpivot-dax-y-excel-business-intelligence-para-todos-los-p-250-blicos.aspx

[2]
AdventureWorks. Base de datos de ejemplo para Microsoft SQL Server 2008 en
CodePlex.

http://msftdbprodsamples.codeplex.com/releases/view/55926

[3] SQL Server 2012 RC

http://www.microsoft.com/sqlserver/en/us/future-editions.aspx

 [4] SQL Server 2012 PowerPivot para
Excel 2010.

http://www.microsoft.com/downloads/es-es/details.aspx?FamilyID=C61EE610-B63E-4033-A934-01D2AB567A45

 

Modelos tabulares en SQL Server 2012 Analysis Services

(Artículo
publicado previamente en el número 93 de dNM+, junio 2012)

La reciente
aparición oficial de SQL Server 2012 (también conocido por el nombre clave
Denali) viene acompañada, como es habitual en toda nueva versión, por una serie
de interesantes mejoras, entre las cuales, en el presente artículo,
destacaremos aquellas relacionadas con el proceso de creación y explotación de
un Modelo Tabular de análisis, una de las piezas integrantes del Modelo Semántico
de Inteligencia de Negocio (BISM, Business Intelligence Semantic Model), el
nuevo paradigma para el desarrollo de soluciones de Inteligencia de Negocio (BI,
Business Intelligence), basadas en los Servicios de Análisis pertenecientes a
SQL Server 2012 (SSAS, SQL Server Analysis Services).

Como ya
adelantábamos en [1], la incorporación de BISM a los servicios de análisis
de SQL Server, convierte a estos últimos en una de las soluciones más potentes
dentro del panorama actual de BI. No obstante, desde los primeros anuncios
hechos públicos acerca de BISM hasta ahora, se han desvelado nuevos detalles
acerca de la arquitectura de dicha tecnología, que nos permiten ser más
precisos al hacer la descripción de la misma.

 

BISM. Un modelo único para todas las
necesidades analíticas

El nuevo
BISM se establece como el modelo único para el desarrollo de soluciones de
Inteligencia de Negocio, con el que se atenderán los requerimientos de los
diversos tipos de usuarios y aplicaciones para el análisis de información.

BISM
representa una evolución del anterior Modelo Dimensional Unificado (UDM, Unified
Dimensional Model), hacia un modelo combinado, que ofrece todas las
funcionalidades del desarrollo multidimensional que ya tenía UDM, más nuevas
características basadas en un diseño y motor de análisis relacional; lo
que enriquece la actual oferta de SQL Server dentro del campo de los servicios
de análisis. Por este
motivo, a partir de ahora, el término UDM queda reemplazado en favor de BISM, cuando
hagamos mención al modelo de desarrollo utilizado por Analysis Services. De
igual manera, cuando actualicemos un proyecto UDM a SQL Server 2012, será
considerado a todos los efectos como un proyecto BISM.

Esta
integración en las filosofías de trabajo, y por
ende, en las tecnologías subyacentes  (multidimensional y
relacional), da como resultado una arquitectura distribuida en tres capas, con
capacidad para dar respuesta a usuarios con necesidades muy dispares en todo lo
referente a las tareas relacionadas con el análisis de información.

 

Si observamos el flujo de funcionamiento de esta
arquitectura, partiendo de las fuentes de datos nos encontramos, en primer
lugar, con la capa de Acceso a datos,
que es la encargada de realizar la extracción de la información residente en
los orígenes de datos a los que nos conectamos, existiendo dos mecanismos para
llevarla a cabo: por caché y con acceso directo (passthrough) a la fuente
de datos.

El mecanismo de caché obtiene los datos de la fuente
original, y los almacena en una estructura de datos basada en un algoritmo de
compresión optimizado para su acceso a gran velocidad. A su vez, dentro de este
modo en caché tenemos que optar por dos motores distintos de almacenamiento:
MOLAP o xVelocity.

MOLAP es la vía utilizada tradicionalmente en Analysis
Services como sistema de almacenamiento intermedio, y como su nombre indica,
está optimizado para su uso en el desarrollo de modelos multidimensionales (cubos
OLAP).

xVelocity es un novedoso sistema introducido con BISM
para su empleo en modelos tabulares, consistente en un motor de almacenamiento de
datos en memoria basado en columna, que aúna sofisticados algoritmos de compresión
y búsqueda para ofrecer, sin necesidad de utilizar índices o agregaciones, un
rendimiento excelente en la velocidad de respuesta a nuestras consultas.

Con respecto a la modalidad de acceso directo, tal y
como su nombre indica, lo que hace es enviar la consulta directamente al motor
de la fuente de datos origen, para que ambas operaciones: procesamiento de datos
y lógica de negocio, sean realizadas     allí. En este caso también existen dos
modalidades de ejecución: ROLAP y DirectQuery.

ROLAP es el modo habitualmente empleado por Analysis
Services, que utiliza la   propia fuente
de datos origen, para procesar las consultas efectuadas contra cubos
pertenecientes a modelos multidimensionales.

DirectQuery se utiliza en modelos tabulares para
procesar igualmente las consultas en el origen de datos.

En la capa Lógica
de negocio
incluiremos las consultas en los lenguajes  MDX o DAX, según trabajemos respectivamente
contra un modelo multidimensional o tabular, con las que implementaremos la
lógica de nuestra solución.

Para terminar, en el Modelo de datos encontramos la capa conceptual, en la que
utilizando SQL Server Data Tools (antiguo Business Intelligence Development
Studio y actual entorno de desarrollo basado en Visual Studio 2010)
construiremos nuestro modelo empleando alguna de las plantillas de proyecto  disponibles a tal efecto: Analysis Services Multidimensional and Data
Mining Project
o Analysis Services
Tabular Project
.

Cabe mencionar que también resulta posible utilizar PowerPivot
como herramienta de desarrollo de un modelo tabular, aunque para llevar a cabo
la implementación de éste en el servidor tendremos necesariamente que usar SQL
Server Data Tools.

 

Instalación
de Analysis Services. Un modelo semántico, dos modos de ejecución

Dadas las particulares características de cada uno
de los modelos de datos pertenecientes a BISM, en el programa de instalación de
SQL Server 2012, al llegar al paso correspondiente a Analysis Services, debemos
elegir qué modo de ejecución del servidor de análisis queremos instalar: Multidimensional
o Tabular, ya que no es posible la instalación simultánea de ambos  en la misma instancia. Por este motivo, el
procedimiento recomendado consiste en instalar cada modo en instancias
separadas. No obstante, en nuestro caso, bastará con instalar únicamente el
modo tabular para el desarrollo del ejemplo.

 

Manos a la
obra. Creación de un modelo tabular

Una vez expuestos todos aquellos aspectos necesarios
para que el lector pueda ubicarse conceptualmente dentro del nuevo modelo
semántico, pasemos a la parte práctica del artículo, en la que nos ocuparemos
de desarrollar un proyecto de análisis de tipo tabular.

La base de datos que emplearemos para el ejemplo
será AdventureWorksDWDenali [2],
mientras que el objetivo del modelo a crear consistirá en analizar los gastos
de envío de los pedidos realizados por los clientes a través de Internet, así
como la cantidad de pedidos emitida, todo ello en función del territorio de
residencia del cliente y los detalles de oferta o promoción en que estuvieran
los artículos comprados.

En primer lugar iniciaremos SQL Server Data Tools, seleccionando un nuevo proyecto basado en la
plantilla Analysis Services Tabular
Project
, al que daremos el nombre PruebaModeloTabular. Según veremos en el Explorador de Soluciones, este proyecto
estará formado por un archivo con el nombre Model.bim, que representa al
diseñador del modelo tabular.

 

El siguiente paso consistirá en añadir al modelo las
tablas que formarán parte del mismo, para lo cual seleccionaremos la opción de
menú “Model | Import From Data Source”, que iniciará el asistente de
importación de datos. Tras elegir el tipo de origen de datos, la instancia de
SQL Server correspondiente y la base de datos, llegaremos al paso del asistente
en el que deberemos especificar las credenciales para poder conectarnos a la
fuente de datos y proceder a su extracción.

En este punto utilizaremos nuestra cuenta de Windows
(en caso de que tenga los permisos de acceso suficientes) o bien elegiremos la
opción Service Account, que utilizará
la cuenta NT SERVICEMSOLAP$SQL2012TAB, asociada al servicio de Analysis
Services. Si empleamos esta última posibilidad, necesitaremos otorgar permiso
de acceso y lectura a dicha cuenta sobre la base de datos
AdventureWorksDWDenali.

 

Para asignar el mencionado permiso, en SQL Server Management Studio, haremos
clic derecho en el nodo Logins,
perteneciente a su vez al nodo Security,
eligiendo la opción New Login, que
abrirá el cuadro de diálogo para añadir un nuevo login al servidor, donde
introduciremos el nombre de la cuenta de Analysis Services. En el apartado User Mapping marcaremos la base de datos
a la que daremos acceso.

 

Continuando con el asistente de importación
llegaremos a la selección de tablas, donde marcaremos DimSalesTerritory, DimPromotion
y FactInternetSales, procediendo a iniciar el proceso de importación.

 

Finalizado el proceso de importación de datos, el
diseñador mostrará las tablas en forma de cuadrícula, organizándolas en
pestañas.

 

Este modo de visualización del modelo puede
alternarse con un modo en diagrama, que será más aconsejable si queremos
observar determinadas características, tales como las relaciones entre tablas.
Para cambiar este modo de vista utilizaremos los botones situados a tal efecto
en la parte inferior derecha de la ventana del diseñador.

 

Creación de
medidas

A pesar de haber importado las tablas al modelo,
todavía no podemos realizar un análisis adecuado sobre el mismo, ya que
carecemos de los cálculos (medidas o métricas en el contexto de BI)
encargados de proporcionar los resultados numéricos, imprescindibles en
cualquier sistema de estas características [3].

Para crear una medida dentro del modelo, en primer
lugar, la visualización del diseñador deberá estar establecida en cuadrícula (modo
predeterminado). Una vez hecho este ajuste, observaremos que cada tabla muestra
dos secciones: la superior, que contiene las filas de la propia tabla; y la
inferior, reservada para las medidas calculadas que incorpore el desarrollador
al modelo.

Seguidamente, elegiremos la tabla del modelo que
contiene aquellas columnas susceptibles de ser utilizadas en la obtención de
resultados numéricos (también denominada tabla
de hechos
dentro del contexto del diseño de modelos multidimensionales), y
que en este proyecto será FactInternetSales.

A continuación utilizaremos la columna Freight para
crear la primera de nuestras medidas: una suma de los valores de dicha columna
para todas las filas de la tabla. En la zona inferior de la cuadrícula
seleccionaremos una celda libre debajo de la columna mencionada, y después
haremos clic en el botón Sum de la barra de herramientas, que aplicará a esta
columna la fórmula “SUM([Freight])”, perteneciente al lenguaje DAX.

Fruto de esta operación será la obtención de la
medida calculada Sum of Freight, cuyo
nombre, asignado automáticamente por el entorno de desarrollo, cambiaremos en
su ventana propiedades a GastosTransporte.

 

Observando
la cifra resultante nos percataremos de que existe un problema de formato con
el valor obtenido (7.339.696.091,00 €), ya que el resultado correcto es 733969,6091, lo
cual podemos comprobar ejecutando desde SQL Server Management Studio la consulta
del siguiente código. No obstante, este escollo solamente lo encontraremos en el ámbito del diseñador del
modelo, ya que como veremos en el siguiente apartado, al analizarlo desde una
herramienta externa, los valores se visualizarán correctamente.

 

SELECT SUM(Freight) FROM FactInternetSales

 

Seguidamente
crearemos la medida que calculará el número pedidos emitidos
por la empresa, para lo
cual necesitaremos aplicar sobre la columna SalesOrderNumber una operación de
recuento de valores distintos (Distinct Count), ya que la tabla FactInternetSales
puede tener más de un registro para el mismo pedido.

El botón Sum
de la barra de herramientas de Visual Studio puede desplegarse para crear
algunos de los cálculos más habituales, Distinct Count entre ellos, aunque en
esta ocasión crearemos manualmente la medida FacturasEmitidas, escribiendo la
expresión DAX “FacturasEmitidas:=DISTINCTCOUNT([SalesOrderNumber])” en la
barra de fórmulas.

 

Analizando el modelo desde Excel

Si hemos instalado Excel 2010 en nuestra máquina,
podremos emplearlo como herramienta de análisis para el modelo que estamos
desarrollando. Todo lo que tenemos que hacer es seleccionar la opción de menú
“Model | Analyze in Excel” de Visual Studio, que abrirá Excel y cargará el
modelo en una tabla dinámica.

Una vez situados en Excel, en el panel “Lista de
campos de tabla dinámica” marcaremos la medida GastosTransporte, que deberá
quedar situada en el bloque Valores.
De igual forma procederemos con el campo SpanishPromotionCategory de la tabla
DimPromotion, pero en esta ocasión lo situaremos en el bloque Etiquetas de columna. Para terminar, los
campos SalesTerritoryGroup y SalesTerritoryCountry serán colocados en el bloque
Etiquetas de fila, de forma que
podamos observar esta información en orden jerárquico.

 

Al mismo tiempo, podemos analizar la cantidad de
facturas que la empresa ha emitido por los artículos vendidos, añadiendo la
medida FacturasEmitidas a la tabla dinámica, de modo que también obtendremos
esta información por los campos actualmente situados en las filas y columnas o
bien por otros pertenecientes a las tablas del modelo.

Aquellos lectores que hayan tenido la oportunidad de
utilizar PowerPivot, encontrarán interesantes similitudes con este modo de
trabajo, ya que en ambos casos, la tecnología subyacente es la misma.

 

Conclusiones

Los modelos de análisis tabular, pertenecientes al
nuevo paradigma en la construcción de soluciones de Inteligencia de Negocio,
BISM, introducido en el recién aparecido SQL Server 2012, representan una
estupenda herramienta con la que desarrollar potentes sistemas de información,
utilizando los Servicios de Análisis de SQL Server. En el presente artículo hemos
realizado una introducción a esta interesante tecnología, mostrando al lector
cómo puede empezar a obtener partido de la misma.

 

REFERENCIAS

[1] Blanco,
Luis Miguel. “PowerPivot, DAX y Excel. Business Intelligence para todos los
públicos”.

http://geeks.ms/blogs/lmblanco/archive/2015/04/22/powerpivot-dax-y-excel-business-intelligence-para-todos-los-p-250-blicos.aspx

[2] AdventureWorksDWDenali.

http://msftdbprodsamples.codeplex.com/releases/view/55330

[3] Blanco,
Luis Miguel. “Cubos de datos en SQL Server 2008 Analysis Services”.

http://geeks.ms/blogs/lmblanco/archive/2013/09/11/cubos-de-datos-en-sql-server-2008-analysis-services.aspx

 

PowerPivot, DAX y Excel. Business Intelligence para todos los públicos

(Artículo
publicado previamente en el número 83 de dNM+, Julio-Agosto 2011)

PowerPivot
(proyecto también conocido con el nombre clave Gemini) es una tecnología para
el análisis de información, cuya particularidad radica en la posibilidad de
trabajar con cantidades masivas de datos utilizando Excel como interfaz de
usuario, por lo que se convierte en una atractiva oferta, dada la popularidad
de esta herramienta perteneciente al paquete de Office. En este artículo
realizaremos una introducción a PowerPivot así como a DAX (Data Analysis
eXpressions), el lenguaje de expresiones analíticas que lo acompaña,
desgranando aquellos aspectos más importantes de esta tecnología, que unida a
los tradicionales Servicios de Análisis y MDX (MultiDimensional eXpressions),
convertirán a la próxima versión de SQL Server (Denali) en una de las más
robustas y potentes soluciones en el campo de la Inteligencia de Negocio o BI (Business Intelligence).

Cada vez con
mayor frecuencia, las organizaciones deben realizar diversos análisis acerca de
su estado desde las más variadas perspectivas (comercial, financiera, RR.HH.,
etc.) y sobre una cantidad cada vez más ingente de datos, para lo cual precisan
de herramientas adecuadas, que les permitan obtener una información veraz y
fiable de dicho estado.

Como ya
explicábamos en [1], los cubos de datos constituyen uno de los elementos
clave dentro de este ecosistema de análisis de información denominado Business
Intelligence (BI).

Sin embargo,
un sistema BI, al igual que ocurre en otros ámbitos tales como las aplicaciones
Web, de escritorio, etc., precisa de un equipo de desarrollo y de un tiempo
para su creación, factor este último que, en ocasiones, se dilata por un
periodo mayor del que inicialmente se había estimado.

Ante los
problemas que acabamos de plantear, cabría proponer como solución una mejora en
las herramientas de desarrollo del sistema de información, las cuales aceleren
los tiempos de elaboración del mismo, y permitan manejar un mayor volumen de
datos, dado el aumento generalizado de este aspecto en todos los ámbitos corporativos.

También
sería necesario potenciar al equipo de desarrollo, incorporando nuevos actores
al ciclo de elaboración del sistema, que hasta la fecha habrían desempeñado un
papel de meros validadores, pero que pueden aportar un gran valor al mismo, dado
su amplio conocimiento acerca de la estructura de las diversas fuentes de datos
disponibles en la organización.

PowerPivot
llega con el ánimo de aportar soluciones a los retos que acabamos de plantear,
cubriendo una serie de áreas específicas en el desarrollo de un sistema de
información basado en BI.

 

Autoservicio de inteligencia de negocio
(self-service BI)

Cuando
hablamos acerca de autoservicio, habitualmente enmarcamos dicho concepto dentro
del ámbito de la venta de productos, una actividad en la que una persona, el
cliente, se desplaza a un lugar, el supermercado, donde los productos se
encuentran expuestos, y una vez allí, el cliente va tomando los productos que
necesita, todo ello, generalmente, sin precisar la intervención de
intermediario alguno.

Dentro del
contexto de la inteligencia de negocio podemos establecer una analogía con el
modelo de autoservicio, empleando un concepto denominado autoservicio de inteligencia de negocio o self-service BI, el cual propugna que los usuarios finales (avanzados
o de otro tipo de perfil) de un sistema de información, puedan elaborar sus
propios análisis e informes sobre los contenidos de dicho sistema, para
resolver las necesidades puntuales que emergen en los diversos departamentos de
la organización, sin tener que depender en estos casos del equipo de desarrollo
del sistema BI corporativo.

Como
requisitos encontramos la necesidad de acceso por parte de estos usuarios a
aquellas fuentes de datos que precisen para realizar los análisis, siendo
igualmente crucial el hecho de que las aplicaciones orientadas al self-service
BI tengan una curva de aprendizaje lo más reducida posible, de manera que los
usuarios de las mismas puedan comenzar a ser productivos con ellas de forma
prácticamente inmediata.

PowerPivot
cumple con estos requisitos, ya que al ser una tecnología integrada en Excel,
dispone de una amplísima base de usuarios, que ya trabajan con las
funcionalidades de la hoja de cálculo y el acceso a fuentes de datos externas,
por lo que reduce o elimina el entrenamiento en el uso de la interfaz de
usuario, y focaliza el aprendizaje en los aspectos puntuales del nuevo
complemento para análisis de datos.

 

Incorporando nuevos perfiles de
usuario al ciclo de desarrollo

Además de
liberar de una importante carga de trabajo a los equipos de desarrollo, al
proporcionar a los usuarios finales herramientas de autoservicio de
inteligencia de negocio, obtenemos como beneficio adicional, la posibilidad de
que los usuarios avanzados de los diferentes departamentos de la organización, a
través del uso de dichas herramientas, colaboren en el ciclo de desarrollo del
sistema de información, aportando ideas y sugerencias.

 

VertiPaq. Motor de proceso de datos
de alto rendimiento

PowerPivot
introduce un nuevo motor de procesamiento de datos: VertiPaq, que a través de un
sistema de almacenamiento basado en columnas, implementa una serie de
algoritmos de compresión de los datos, mediante los cuales es capaz de cargar
millones de registros en memoria.

No obstante,
debemos tener en cuenta que dadas las características de ejecución en memoria
de VertiPaq, se recomienda, siempre que sea posible, trabajar en un entorno de
64 bits, con el software correspondiente a esta arquitectura: sistema operativo,
SQL Server, Office 2010, PowerPivot, etc., puesto que no sufre las limitaciones
de direccionamiento de memoria que encontramos en los sistemas de 32 bits.

 

Elementos arquitectónicos

PowerPivot,
como podemos apreciar en la siguiente figura, se compone de un ensamblado que
es cargado en el proceso de Excel; el motor de VertiPaq, que se ocupa de la
carga de datos, la gestión de las consultas y la ejecución de expresiones DAX
contra el almacén de datos, así como de las tablas y gráficos dinámicos de
PowerPivot; y finalmente, el proveedor OLAP, los objetos de análisis AMO
(Analysis Management Objects) y el proveedor ADOMD.NET, que permiten la
comunicación con los servicios de análisis, para obtener información a partir de
cubos de datos si fuera pertinente.

 

Entorno de colaboración. PowerPivot
para SharePoint

Utilizando
PowerPivot es previsible que el número de modelos de análisis creados por los
usuarios crezca notablemente, por lo que resulta muy importante disponer de un
mecanismo que permita su administración, en lo que respecta a las tareas de
publicación y seguridad de acceso dentro de la organización.

Tal es el
objetivo de PowerPivot para SharePoint,
un complemento que amplía los servicios de Excel para SharePoint, en los
mencionados apartados de administración y colaboración.

Adicionalmente,
PowerPivot para SharePoint permite a
los usuarios consultar los modelos de PowerPivot publicados en la organización
utilizando simplemente un navegador Web, por lo que sólo es necesario instalar
Excel y PowerPivot para Excel en las
máquinas de los usuarios que vayan a desarrollar modelos de análisis utilizando
esta herramienta.

 

PowerPivot. Piedra angular de BISM

Denali, nombre clave de la próxima versión de SQL
Server, vendrá acompañada por un nuevo modelo de inteligencia de negocio
denominado Modelo Semántico de
Inteligencia de Negocio
o BISM (Business Intelligence Semantic Model). Este
modelo no llega para sustituir ni desplazar a UDM (Unified Dimensional Model), el
modelo de inteligencia de negocio existente desde la versión 2005 de SQL
Server, sino para coexistir junto a éste, complementando y enriqueciendo la
oferta de SQL Server en el apartado BI; permitiéndonos disponer de ambos
modelos para trabajar con el que mejor se ajuste a nuestras necesidades, según
sea la solución a desarrollar.

Se ha achacado tradicionalmente a SQL Server, refiriéndose
a UDM, que su implementación en el apartado BI tiene una difícil curva de
aprendizaje, con conceptos complejos: cubos, dimensiones, medidas, etc., y que el
tiempo a dedicar para desarrollar una solución, se prolonga en demasía cuando
se trata de crear un sencillo sistema de análisis.

En nuestra opinión, dicha curva de aprendizaje no
resulta mucho más complicada que la que podamos encontrarnos en otros aspectos
del desarrollo de software, aunque sí admitimos, que desde la perspectiva de
los desarrolladores acostumbrados a trabajar con un modelo relacional de datos,
familiarizarse con el modelo multidimensional propugnado por UDM-OLAP, para
construir una solución de análisis de negocio, puede implicar una cierta
complejidad inicial hasta que se dominan los conceptos y características principales
de dicha tecnología.

BISM llega para solucionar esta problemática,
aportando una forma de trabajo más simple, caracterizada por su filosofía
relacional sustentada en PowerPivot y VertiPaq. Mientras que BISM se orienta
principalmente al desarrollo de soluciones de análisis de ámbito personal
(también denominadas Personal BI) o
para pequeños equipos de trabajo, que se enmarcan dentro del self-service BI;
el modelo UDM se dirige al desarrollo de soluciones BI corporativas de más
envergadura, que precisen una mayor planificación.

BISM es un modelo con una arquitectura distribuida
en tres capas: acceso a datos, lógica de negocio y modelo de datos.

 

La capa de acceso a datos se ocupa de conectar con
las fuentes de datos para proceder a su carga, existiendo dos formas de operar
con los mismos, en caché o en tiempo real. En el caso de optar por el modo en
caché, los datos se cargarán utilizando VertiPaq, mientras que si utilizamos el
modo en tiempo real, se dejará esta tarea a la fuente de datos original de la
que se extraen.

En la capa de lógica de negocio implementaremos las
operaciones de manipulación y tratamiento de los datos que nos permitirán convertirlos
en información relevante. Para ello utilizaremos alguno de los lenguajes de expresiones
a nuestra disposición: DAX o MDX.

DAX es el lenguaje de expresiones para análisis de
datos incorporado en PowerPivot, que utilizaremos para construir consultas
contra los datos situados en un almacén de VertiPaq, mientras que MDX es el
lenguaje habitualmente empleado en UDM para consultar cubos OLAP.

DAX no es tan potente como MDX, pero es mucho más
sencillo de utilizar, siendo su objetivo atacar los datos situados en un modelo
de análisis personal o de tipo self-service BI, que teóricamente no debería
necesitar el uso de consultas complejas, las cuales quedan reservadas para ser
realizadas contra el sistema BI corporativo, que en condiciones normales, se
habrá desarrollado mediante UDM.

Finalmente, la capa de modelo de datos será la
utilizada por las aplicaciones cliente (Excel, SharePoint, Reporting Services,
etc.) para obtener información del modelo que corresponda en cada ocasión:
relacional o multidimensional. Entre las herramientas de informes que
utilizarán este modelo se encuentra Crescent, que es el nombre clave de un
proyecto cuyo objetivo consiste en proporcionar una mejor experiencia de
usuario en la visualización de datos y generación de informes contra modelos
basados en BISM, y que también verá la luz con Denali.

Algunas
voces importantes en la comunidad técnica relacionada con BI han expresado su
preocupación [2] acerca del lugar en el que quedará UDM-OLAP, cuando
el nuevo modelo BISM haga su presentación oficial en la próxima versión de SQL
Server; aunque desde el equipo de desarrollo de Analysis Services han querido
enviar un mensaje de tranquilidad [3], insistiendo en que BISM no supone una sustitución
para UDM sino una tecnología complementaria.

 

PowerPivot en la práctica

Después de
la revisión de aspectos arquitectónicos realizada en los anteriores apartados,
llega la hora de poner manos a la obra para desarrollar un modelo de PowerPivot,
que nos sirva para obtener nuestras primeras impresiones acerca del alcance de
esta tecnología.

En primer
lugar necesitamos tener instalado Office 2010 y descargar e instalar el
complemento de PowerPivot para Excel [4]. En el caso de que nuestro sistema sea de 32 bits descargaremos
el archivo PowerPivot_for_Excel_x86.msi; si trabajamos en un entorno de 64
bits, el archivo a descargar será PowerPivot_for_Excel_amd64.msi. Para las
pruebas de este artículo se ha empleado una máquina virtual con Windows 7 como
sistema operativo, 1,5 GB de RAM y procesador Intel Core 2 Duo.

A pesar de
que en el nombre de la página de descarga se hace mención a SQL Server 2008 R2,
no es necesario tenerlo instalado si vamos a trabajar con otros formatos de
datos tales como Access, archivos de texto, etc. No obstante, en nuestro caso sí
que usaremos SQL Server como motor de datos, así como la base de datos ContosoRetailDW,
disponible en [5], de donde descargaremos el archivo
ContosoBIdemoBAK_es.exe, que contiene la base de datos en formato de archivo de
copia de seguridad.

El motivo
por el cual usaremos esta base de datos se debe a que dispone de algunas tablas
con varios millones de registros, lo que nos permitirá poner a prueba una de
las características más destacadas de PowerPivot: la capacidad de manejar
grandes volúmenes de datos.

 

Creación de un modelo

Después de restaurar la base de datos abriremos
Excel, haciendo clic a continuación en la pestaña PowerPivot de la cinta de opciones. Entre las opciones de esta
pestaña haremos clic en Ventana de
PowerPivot
, perteneciente al grupo Iniciar,
que como su nombre indica, abrirá la ventana de trabajo de PowerPivot.

La primera tarea en la creación del modelo
consistirá en conectarnos a una fuente de datos, para hacer una importación de
su contenido, por lo que desde la pestaña Página
principal
de la ventana de PowerPivot, nos situaremos en el grupo Obtener datos externos, y haremos clic
en la opción Desde base de datos, que
desplegará una lista de elementos, en la que seleccionaremos De SQL Server.

 

Como resultado se abrirá el Asistente para la importación de tablas, en el que incluiremos la
información para conectarnos a la base de datos ContosoRetailDW. En el paso
correspondiente a la selección de tablas marcaremos FactSales, DimDate,
DimStore y DimProduct.

 

Tras este paso comenzará el proceso de importación,
que una vez concluido, mostrará cada una de las tablas importadas en diferentes
pestañas de la ventana de PowerPivot.

 

Consultando
el modelo

El conjunto de tablas que acabamos de importar, nos
permitirá realizar un análisis de las ventas de la compañía, en función de los
datos relacionados con la tabla de ventas: fechas, productos, almacenes, etc.

Para ello, haremos clic en la opción PivotTable del grupo Informes, creando una tabla dinámica de
PowerPivot con la que realizar nuestras operaciones de análisis. Esta acción
nos trasladará a la ventana de Excel, donde un cuadro de diálogo nos pedirá las
coordenadas para situar la tabla dinámica en la hoja de cálculo. Aceptando los
valores por defecto, la nueva tabla dinámica será creada.

 

A la derecha de la tabla dinámica encontramos el
panel Lista de campos de PowerPivot,
en el que seleccionaremos los campos a utilizar para nuestro análisis, ya sea
como etiquetas en los ejes de filas y columnas, valores numéricos, filtros, y
segmentaciones.

Todos aquellos lectores con experiencia en la
creación de cubos OLAP comprobarán, que consultar un modelo de PowerPivot es muy
similar a la consulta contra un cubo de datos, pero sin la existencia de un
cubo real, ya que en el panel de lista de campos, los elementos del bloque
Valores representarían las medidas o métricas del cubo, mientras que los campos
del resto de bloques, representarían los atributos de las dimensiones, ya sean
visualizados en las filas, columnas o como filtros de la tabla dinámica.

Comenzaremos la creación de nuestra consulta
marcando la casilla del campo SalesAmount, de la tabla FactSales. Al ser un
campo numérico, automáticamente será colocado como una medida en el bloque
Valores del panel de campos. PowerPivot le aplicará entonces, una operación de
suma sobre todos los registros de la tabla a la que pertenece.

El estado actual de la tabla dinámica no ofrece, sin
embargo, unas grandes posibilidades analíticas, ya que solamente contamos con
el total del campo SalesAmount. Necesitamos añadir elementos adicionales a la
consulta, relacionados con la tabla FactSales, como por ejemplo la tabla
DimProduct.

La tabla DimProduct, además del nombre de cada uno
de los productos de la compañía, contiene información adicional sobre los
mismos, como puede ser el fabricante, clase, color, tamaño, etc.

Supongamos que queremos averiguar la cifra de ventas
por producto, pero en lugar de usar su nombre, necesitamos hacer un análisis
por fabricante. Lo que debemos hacer en tal caso es marcar la casilla
Manufacturer, perteneciente a la lista de campos de la tabla DimProduct. Esta
acción situará dicho campo en el bloque Etiquetas
de fila
del panel de campos, y sus valores en el eje de filas de la tabla
dinámica. De esta forma sabremos cuánto han supuesto las ventas por cada uno de
los fabricantes de productos.

Ya que los valores del campo SalesAmount no se
muestran formateados, como labor adicional, haremos clic derecho en cualquiera
de las celdas de este campo, eligiendo la opción Formato de número… En el cuadro de diálogo de formato seleccionaremos:
Moneda, con dos decimales y símbolo de euro; quedando aplicado a todas las
celdas de este campo en la tabla dinámica.

 

Escribiendo expresiones DAX

Como ya
dijimos anteriormente, DAX es el lenguaje de expresiones a través del cual
construiremos la lógica de negocio en PowerPivot, tanto a nivel de tabla
dinámica como de modelo de datos.

Antes de
comenzar a escribir nuestras primeras sentencias en este lenguaje, debemos
saber que, sin habernos percatado de ello, ¡ya hemos escrito una expresión en DAX!
Si en el panel de campos de la tabla dinámica, dentro del bloque Valores,
hacemos clic sobre la medida SalesAmount, y elegimos la opción Editar medida, se mostrará un cuadro de
diálogo con la expresión que PowerPivot ha creado automáticamente para este
elemento de la tabla dinámica.

=SUM(‘FactSales'[SalesAmount])

 

 

Como podemos
ver en la figura anterior, la creación de una expresión DAX es muy similar a la
escritura de una fórmula en Excel, ya que a continuación del signo igual,
escribiremos una o más funciones con sus correspondientes parámetros, formando
con todo ello la expresión. En la expresión que actualmente nos ocupa, la
función SUM realizará una suma del campo SalesAmount para todos los registros de
la tabla FactSales. Si el nombre de la tabla tiene espacios en blanco u otros
caracteres especiales, deberemos encerrarlo entre comillas, en el resto de
casos no será necesario. Respecto al nombre del campo, siempre deberá ir
encerrado entre corchetes.

Seguidamente
abordaremos la escritura de nuestras propias expresiones, a través de dos de
los principales elementos de PowerPivot: columnas calculadas y medidas.

En primer
lugar crearemos una columna calculada en la tabla FactSales, que obtenga el
importe de venta sin descuento. Para ello, en la ventana de PowerPivot nos
situaremos en la última columna vacía disponible, escribiendo en la barra de
fórmulas lo siguiente:

=FactSales[UnitPrice]
* FactSales[SalesQuantity]

Como ayuda
en la escritura de expresiones, la característica Autocompletar nos sugerirá en
todo momento una lista de funciones, tablas y campos, dependiendo de lo que
vayamos escribiendo.

Después de
escribir la expresión, la nueva columna se rellenará con los valores
resultantes. Haciendo doble clic en la cabecera de la columna asignaremos el
nombre ImporteSinDescuento, finalizando así la creación de la columna calculada.

 

Otra forma
de crear una columna calculada consiste en hacer clic en la opción Agregar, del grupo Columnas, perteneciente a la pestaña Diseño.

Debido a que
los campos utilizados en la fórmula, pertenecen a la misma tabla sobre la que
estamos creando la columna calculada, en la sintaxis de la expresión podemos
obviar el nombre de la tabla, quedando de la siguiente manera:

=[UnitPrice]
* [SalesQuantity]

Para poder usar la nueva columna en la tabla
dinámica volveremos a la ventana de Excel, encontrándonos con un aviso en el
panel Lista de campos de PowerPivot, que
nos informa de que se han producido cambios en los campos. Haciendo clic en el
botón Actualizar de dicho aviso, se refrescará la lista de campos de la tabla
FactSales, agregándose el nuevo campo ImporteSinDescuento, cuya casilla
marcaremos  para incluirlo en la tabla
dinámica.

 

Nuestro siguiente
paso consistirá en la creación de una medida para calcular, de cada fabricante,
el porcentaje de ventas que han supuesto sus productos con respecto al total de
ventas de la compañía.

En la
pestaña PowerPivot, dentro del grupo Medidas, haremos clic en la opción Nueva medida, abriéndose la ventana Configuración de medida, en la que
asignaremos el literal PorcentajeVentas como nombre de la medida, y la
siguiente expresión en el campo de fórmula:

=SUM(FactSales[SalesAmount]) / CALCULATE(SUM(FactSales[SalesAmount])
,ALL(FactSales))

 

 

La primera
parte de esta expresión, como ya hemos visto anteriormente, se encarga de sumar
el campo SalesAmount. Esta suma se realizará para cada fabricante, al ser
Manufacturer el campo utilizado en las etiquetas de fila de la tabla dinámica.

La segunda
parte de la expresión devuelve, para todas las filas, la suma total del campo
SalesAmount, lo cual conseguimos combinando las funciones CALCULATE, SUM y ALL.
La función CALCULATE evalúa la expresión pasada como primer parámetro, que
consiste en una suma del campo SalesAmount, y obliga a que dicha suma sea sobre
todos los registros de la tabla FactSales mediante el uso de la función ALL,
independientemente de los filtros que estén aplicados en la tabla dinámica.

Para
terminar, añadiremos la nueva medida a la tabla dinámica, aplicándole el formato
de porcentaje.

 

Filtros y segmentaciones

Nuestra
tabla dinámica muestra, organizadas por fabricantes de productos, una serie de
medidas obtenidas a partir de campos y cálculos de la tabla FactSales,
perteneciente al modelo de datos de PowerPivot.

Los
resultados de estas medidas se elaboran a partir de todos los registros de la
mencionada tabla, pero también nos interesará en algún momento, disponer de la
posibilidad de aplicar filtros sobre la información que estamos consultando,
con el fin de obtener diferentes perspectivas de análisis.

Las tablas
dinámicas de PowerPivot, proporcionan al usuario los filtros y segmentaciones
como herramientas de filtrado de los datos. A continuación explicaremos al
lector su utilización, como medio de acotar los resultados del informe.

Supongamos
que queremos filtrar la información de la tabla dinámica, según el tipo de
almacén en el que se encuentran ubicados los productos; dato que obtenemos a
partir del campo StoreType perteneciente a la tabla DimStore.

Para crear
un filtro de estas características, en el panel lista de campos desplegaremos
los campos de la tabla DimStore, y arrastraremos el campo StoreType hasta el
bloque Filtro de informe. Como
resultado, se añadirá este filtro en la parte superior de la tabla dinámica.

Haciendo
clic en su botón de despliegue y seleccionando el valor Almacén, se activará el
filtro, provocando que todas las celdas numéricas de la tabla dinámica muestren
la información de ventas, pero en función del filtro establecido.

 

Las
segmentaciones, por otra parte, se comportan como los filtros, aunque su manejo
por parte del usuario es ligeramente distinto.

Vamos a
crear una segmentación que nos permita filtrar los resultados por el año de
venta, para lo cual desplegaremos los campos de la tabla DimDate, arrastrando
el campo CalendarYear hasta el bloque Segmentaciones
de datos horizontales
. Esta operación creará la segmentación con todos los
valores de filtro, situándola encima de la tabla dinámica.

Los valores
de la segmentación que aparecen en un tono de color más oscuro, son aquellos
con los cuales podremos realizar filtros efectivos; mientras que el uso de los
que tienen un tono más claro no producirá resultados, ya que no existen ventas
realizadas en dichos años.

Si queremos
utilizar más de un valor de la segmentación, mantendremos pulsada la tecla CTRL
mientras hacemos clic en los diferentes valores. El resultado del filtro no
será efectivo hasta que no soltemos la tecla CTRL. Para eliminar todos los
filtros de la segmentación haremos clic en el icono con forma de embudo,
situado en su parte superior derecha.

 

Creación de un gráfico dinámico

En
determinadas circunstancias, la representación numérica de datos proporcionada
por una tabla dinámica, puede no ser suficiente para analizar el contenido del
modelo de PowerPivot, por lo que también contamos con los gráficos dinámicos,
como vía alternativa en la visualización de información.

Podemos
crear un gráfico dinámico desde cero, o bien usar una tabla dinámica ya
existente como base del gráfico. En nuestro caso optaremos por este último
método, para lo cual, una vez posicionados en la tabla dinámica, haremos clic
en la pestaña Opciones, perteneciente
a la categoría Herramientas de tabla
dinámica
, y seguidamente, seleccionaremos la opción Gráfico dinámico, situada en el grupo Herramientas. Se abrirá el cuadro de diálogo Insertar gráfico, que nos ofrecerá un amplio abanico de tipos de
gráfico para insertar en la hoja de cálculo. Una vez hecha nuestra elección,
aceptaremos este diálogo, insertándose el gráfico junto a la tabla dinámica.

A partir de
este momento, tanto la tabla como el gráfico estarán sincronizados, de forma
que las modificaciones de filtros, medidas, etiquetas en ejes, etc., que
realicemos en uno, tendrán su reflejo inmediato en el otro. Seguidamente vemos
el aspecto de nuestro informe, después de eliminar dos medidas del gráfico y de
haber modificado el filtro StoreType.

 

Conclusiones

PowerPivot
representa una tecnología con grandes posibilidades de convertirse en una pieza
importante del conjunto de herramientas disponibles para el desarrollo de
sistemas BI con SQL Server. Si unimos su capacidad en el manejo de grandes
volúmenes de datos, la potencia de consulta de su lenguaje de expresiones, y el
hecho de utilizar Excel como interfaz de usuario, obtenemos un producto que
permite crear a los usuarios sus propios modelos de análisis, para resolver
necesidades puntuales de información. Al mismo tiempo, dichos modelos pueden
servir de ayuda a los equipos de desarrollo de inteligencia de negocio, en la
construcción de los sistemas de información corporativos.

 

REFERENCIAS

[1] Blanco,
Luis Miguel. “Cubos de datos en SQL Server 2008 Analysis Services”. http://geeks.ms/blogs/lmblanco/archive/2013/09/11/cubos-de-datos-en-sql-server-2008-analysis-services.aspx

[2] Chris Webb’s BI Blog – PASS Summit Day 2.

http://cwebbbi.wordpress.com/2010/11/11/pass-summit-day-2/

[3] Analysis Services – Roadmap for SQL Server
“Denali” and Beyond.

http://blogs.technet.com/b/dataplatforminsider/archive/2010/11/12/analysis-services-roadmap-for-sql-server-denali-and-beyond.aspx

[4] Microsoft SQL Server 2008 R2 – PowerPivot para
Microsoft Excel 2010 – RTM .

http://www.microsoft.com/downloads/es-es/details.aspx?FamilyId=e081c894-e4ab-42df-8c87-4b99c1f3c49b&displaylang=es

[5] Base de
datos ContosoRetailDW.

http://www.microsoft.com/downloads/es-es/details.aspx?FamilyID=868662dc-187a-4a85-b611-b7df7dc909fc&displayLang=es

 

Tablas numéricas auxiliares (Tally Tables) en acción. Creación dinámica de sentencias con PIVOT

El presente artículo, que cierra nuestro monográfico
dedicado a la construcción y usos prácticos de las tablas numéricas auxiliares
o tally tables, se orienta a la utilización de esta técnica como elemento de
soporte en la construcción de sentencias que utilicen el operador PIVOT,
y en las cuales debamos generar un número considerable de columnas que sigan un
determinado patrón, ahorrando por consiguiente una importante cantidad de
escritura de código manual. Comencemos pues por una sencilla introducción que
nos proporcione una idea aproximada de la situación a resolver.

Partimos de la siguiente consulta contra la tabla
FactInternetSales, perteneciente a la base de datos AdventureWorksDW2012, que
muestra las cifras de ventas por código territorial y año.

 

SELECT
SalesTerritoryKey,
YEAR(OrderDate) AS Anualidad,
SalesAmount
FROM FactInternetSales

 

 

Necesitamos adaptar esta sentencia, de forma que
mediante una operación de pivotado, creemos tantas columnas como años existan
en OrderDate. Sumando para cada combinación de SalesTerritoryKey y año, el
valor de la columna SalesAmount, tal y como describe el esquema de la siguiente
figura.

 

Teniendo en cuenta estos requerimientos,
la consulta con PIVOT quedaría de la siguiente forma.

 

SELECT
SalesTerritoryKey,
[2005],
[2006],
[2007],
[2008]
FROM
(
            SELECT
            SalesTerritoryKey,
            SalesAmount,
            YEAR(OrderDate) AS Anualidad
            FROM FactInternetSales
) AS tblDatosOrigen
PIVOT
(
            SUM(SalesAmount)
            FOR Anualidad IN (
                        [2005],
                        [2006],
                        [2007],
                        [2008]
            )
) AS tblPivot

 

 

Si además quisiéramos agrupar las cifras de ventas
por los productos que pertenecieran a las subcategorías de bicicletas,
añadiríamos a la sentencia las tablas DimProduct y DimProductSubcategory,
combinándolas para obtener los nuevos datos a mostrar.

 

SELECT
EnglishProductSubcategoryName,
[2005],
[2006],
[2007],
[2008]
FROM
(
            SELECT
            FIS.SalesAmount,
            YEAR(FIS.OrderDate) AS
Anualidad,
            DPS.EnglishProductSubcategoryName,
            DPS.ProductSubcategoryKey
            FROM FactInternetSales AS FIS
            INNER JOIN DimProduct AS DP
            ON FIS.ProductKey =
DP.ProductKey
            INNER JOIN
DimProductSubcategory AS DPS
            ON DP.ProductSubcategoryKey =
DPS.ProductSubcategoryKey
            WHERE
DPS.ProductSubcategoryKey BETWEEN 1 AND 3
) AS tblDatosOrigen
PIVOT
(
            SUM(SalesAmount)
            FOR Anualidad IN (
                        [2005],
                        [2006],
                        [2007],
                        [2008]
            )
) AS tblPivot;

 

Hasta aquí todo bien, la cantidad de código a
escribir no es muy elevada, pero ahora se nos plantea un nuevo requisito, consistente
en mostrar los resultados de ventas por año y mes, lo que supone modificar la
sentencia para añadir al código toda la combinatoria de columnas de año y mes,
como vemos a continuación.

 

SELECT
EnglishProductSubcategoryName,
[2005_1],
[2005_2],
[2005_3],
[2005_4],
[2005_5],
[2005_6],
[2005_7],
[2005_8],
[2005_9],
[2005_10],
[2005_11],
[2005_12],
[2006_1],
[2006_2],
[2006_3],
[2006_4],
[2006_5],
[2006_6],
[2006_7],
[2006_8],
[2006_9],
[2006_10],
[2006_11],
[2006_12],
[2007_1],
[2007_2],
[2007_3],
[2007_4],
[2007_5],
[2007_6],
[2007_7],
[2007_8],
[2007_9],
[2007_10],
[2007_11],
[2007_12],
[2008_1],
[2008_2],
[2008_3],
[2008_4],
[2008_5],
[2008_6],
[2008_7],
[2008_8],
[2008_9],
[2008_10],
[2008_11],
[2008_12]
FROM
(
            SELECT
            FIS.SalesAmount,
            CONVERT(varchar(4),
YEAR(FIS.OrderDate)) + ‘_’ + CONVERT(varchar(2),
MONTH(FIS.OrderDate)) AS AnualidadMes,
            DPS.EnglishProductSubcategoryName
            FROM FactInternetSales AS FIS
            INNER JOIN DimProduct AS DP
            ON FIS.ProductKey =
DP.ProductKey
            INNER JOIN
DimProductSubcategory AS DPS
            ON DP.ProductSubcategoryKey =
DPS.ProductSubcategoryKey
            WHERE
DPS.ProductSubcategoryKey BETWEEN 1 AND 3
) AS tblDatosOrigen
PIVOT
(
            SUM(SalesAmount)
            FOR AnualidadMes IN (
                        [2005_1],
                        [2005_2],
                        [2005_3],
                        [2005_4],
                        [2005_5],
                        [2005_6],
                        [2005_7],
                        [2005_8],
                        [2005_9],
                        [2005_10],
                        [2005_11],
                        [2005_12],
                        [2006_1],
                        [2006_2],
                        [2006_3],
                        [2006_4],
                        [2006_5],
                        [2006_6],
                        [2006_7],
                        [2006_8],
                        [2006_9],
                        [2006_10],
                        [2006_11],
                        [2006_12],
                        [2007_1],
                        [2007_2],
                        [2007_3],
                        [2007_4],
                        [2007_5],
                        [2007_6],
                        [2007_7],
                        [2007_8],
                        [2007_9],
                        [2007_10],
                        [2007_11],
                        [2007_12],
                        [2008_1],
                        [2008_2],
                        [2008_3],
                        [2008_4],
                        [2008_5],
                        [2008_6],
                        [2008_7],
                        [2008_8],
                        [2008_9],
                        [2008_10],
                        [2008_11],
                        [2008_12]
            )
) AS tblPivot;

 

 

Como acabamos de comprobar, el volumen de código a
escribir se ha incrementado sustancialmente debido a la necesidad de
especificar las columnas que contienen las ventas por año y mes. En el caso de
que debamos desarrollar varios escenarios de similares características, su mantenimiento
puede convertirse en una tarea bastante molesta y propensa a incurrir en
errores. Por este motivo, el uso de una tabla numérica como herramienta de
ayuda en la elaboración del código puede resultar una opción a tener en cuenta.

En primer lugar necesitamos la mencionada tabla
numérica con base 1, a la que denominaremos Numeros (al igual que en el resto
de artículos que sobre este tema  hemos
dedicado en el blog), conteniendo al menos 2008 filas, ya que se trata del
máximo valor para el año en la columna OrderDate de la tabla FactInternetSales.

Seguidamente crearemos una  sentencia basada en dos expresiones de tabla
(CTE)     contra la tabla numérica. La
primera devolverá el rango de años existente en la columna OrderDate y la
segunda los números de mes. A continuación cruzaremos ambas expresiones mediante
CROSS
JOIN
, para comprobar que obtenemos correctamente las combinaciones de año y
mes.

 

WITH
cteAnualidades AS
(
            SELECT NumeroID AS Anualidad
            FROM Numeros
            WHERE NumeroID BETWEEN
                        (SELECT
MIN(YEAR(OrderDate)) FROM FactInternetSales)
                        AND
                        (SELECT
MAX(YEAR(OrderDate)) FROM FactInternetSales)
),
cteMeses AS
(
            SELECT NumeroID AS Mes
            FROM Numeros
            WHERE NumeroID BETWEEN 1 AND
12
)
SELECT Anualidad, Mes
FROM cteAnualidades
CROSS JOIN cteMeses
ORDER BY Anualidad,Mes

 

Esta consulta la integraremos posteriormente en otra
de tipo dinámico, siendo esta última la que contenga el operador PIVOT. Para
recoger y dar formato al resultado, en aquella parte de la sentencia que
devuelve el cruce de años y meses emplearemos una variable de cadena (@sAnualidadesMeses)
que reutilizaremos en dos ocasiones dentro de la construcción de la consulta
principal. Veamos en primer lugar el código necesario para obtener la cadena
con las definiciones de columna año-mes.

 

DECLARE @sSQL AS nvarchar(2000);
DECLARE @sAnualidadesMeses AS nvarchar(1000);

SET @sAnualidadesMeses = ”;

WITH
cteAnualidades AS
(
            SELECT NumeroID AS Anualidad
            FROM Numeros
            WHERE NumeroID BETWEEN
                        (SELECT
MIN(YEAR(OrderDate)) FROM FactInternetSales)
                        AND
                        (SELECT
MAX(YEAR(OrderDate)) FROM FactInternetSales)
),
cteMeses AS
(
            SELECT NumeroID AS Mes
            FROM Numeros
            WHERE NumeroID BETWEEN 1 AND
12
)
SELECT @sAnualidadesMeses += ‘[‘ + CONVERT(varchar(4), Anualidad) + ‘_’ +
CONVERT(varchar(2), Mes) + ‘],’
FROM cteAnualidades
CROSS JOIN cteMeses;

PRINT @sAnualidadesMeses;

 

 

Y ahora, la sentencia al completo.

 

DECLARE @sSQL AS nvarchar(2000);
DECLARE @sAnualidadesMeses AS nvarchar(1000);

SET @sAnualidadesMeses = ”;

WITH
cteAnualidades AS
(
            SELECT NumeroID AS Anualidad
            FROM Numeros
            WHERE NumeroID BETWEEN
                        (SELECT MIN(YEAR(OrderDate))
FROM FactInternetSales)
                        AND
                        (SELECT
MAX(YEAR(OrderDate)) FROM FactInternetSales)
),
cteMeses AS
(
            SELECT NumeroID AS Mes
            FROM Numeros
            WHERE NumeroID BETWEEN 1 AND
12
)
SELECT @sAnualidadesMeses += ‘[‘ + CONVERT(varchar(4), Anualidad) + ‘_’ +
CONVERT(varchar(2), Mes) + ‘],’
FROM cteAnualidades
CROSS JOIN cteMeses;

SET @sAnualidadesMeses = LEFT(@sAnualidadesMeses, LEN(@sAnualidadesMeses) – 1
);

SET @sSQL = ‘SELECT EnglishProductSubcategoryName,’ + @sAnualidadesMeses + ‘
FROM (SELECT FIS.SalesAmount,’ +
‘CONVERT(varchar(4), YEAR(FIS.OrderDate)) + ”_” + CONVERT(varchar(2),
MONTH(FIS.OrderDate)) AS AnualidadMes,’ +
‘DPS.EnglishProductSubcategoryName ‘ +
‘FROM FactInternetSales AS FIS ‘ +
‘INNER JOIN DimProduct AS DP ‘ +
‘ON FIS.ProductKey = DP.ProductKey ‘ +
‘INNER JOIN DimProductSubcategory AS DPS ‘ +
‘ON DP.ProductSubcategoryKey = DPS.ProductSubcategoryKey ‘ +
‘WHERE DPS.ProductSubcategoryKey BETWEEN 1 AND 3) AS tblDatosOrigen ‘ +
‘PIVOT (‘ +
‘SUM(SalesAmount) ‘ +
‘FOR AnualidadMes IN (‘ + @sAnualidadesMeses + ‘)) AS tblPivot’;

EXECUTE sp_executesql @sSQL;

 

Comprobaremos que hemos obtenido el mismo resultado
pero escribiendo una menor cantidad de código.

Podemos concluir, una vez revisadas las aplicaciones
prácticas que hemos ido describiendo tanto en el presente como en los
anteriores artículos sobre las tablas numéricas auxiliares, que representan una
poderosa herramienta con la que contar en nuestro arsenal de utilidades para SQL
Server. Espero que en alguna ocasión le resulten de ayuda al lector en su
trabajo.

Tablas numéricas auxiliares (Tally Tables) en acción. Depuración de caracteres delimitadores en columnas de tipo texto

Como continuación al conjunto de artículos dedicados
a la aplicación práctica de las tablas numéricas auxiliares, en la actual
entrega, al igual que en el anterior
artículo
, centraremos nuestro objetivo en el tratamiento de
los caracteres que actúan como delimitador dentro de un texto, pero en este
caso el objetivo consistirá en eliminar los delimitadores sobrantes del mismo, para
lo cual, en primer lugar expondremos el escenario de trabajo sobre el que
actuaremos.

 

El
escenario de trabajo

Supongamos que debemos cargar una tabla de una base
de datos con el contenido proveniente de un archivo de texto, en el que las
columnas vienen determinadas por un carácter delimitador, por ejemplo, el
carácter de barra vertical o pipe. La siguiente figura muestra algunas líneas
de este archivo, que incluye los nombres de las columnas en su primera fila.

 

Observando con detalle este archivo comprobaremos
que cada fila dispone de 6 caracteres delimitadores, que a su vez definen 7 campos o columnas, como vemos gráficamente en la siguiente imagen.

 

Preparación
del archivo de datos origen

Los datos de este archivo se han obtenido a partir
de varias columnas de la tabla DimReseller, perteneciente a la base de datos
AdventureWorksDW2012. Para preparar este archivo, desde SQL Server Management
Studio haremos clic derecho en dicha base de datos, eligiendo la opción Tasks | Export Data, lo que abrirá el
asistente de exportación de datos de SQL Server 2014.

 

Después de hacer clic en el botón Next para pasar la página de
presentación del asistente, en el primer paso del mismo seleccionaremos la
fuente de datos desde la que vamos a realizar la exportación.

 

A continuación seleccionaremos Flat File Destination como tipo de destino de la exportación, así
como el nombre del archivo en el que se realizará el volcado de los datos.
También habilitaremos la inclusión de los nombres de las columnas de la tabla
en la primera fila del archivo.

 

En el siguiente paso indicaremos que la exportación
se realizará mediante una consulta contra la base de datos.

 

Incluyendo dicha consulta a continuación.

 

En el próximo paso seleccionaremos el carácter pipe
como delimitador de columna.

 

A partir de este punto avanzaremos hasta el último
paso del asistente y haremos clic en el botón Finish para generar el archivo de texto con el resultado de la
sentencia.

 

La carga
del archivo en la base de datos

Una vez obtenido el archivo, en la base de datos que
estemos utilizando para realizar nuestras pruebas crearemos una tabla con la
siguiente estructura.

 

CREATE TABLE Reseller
(
ResellerKey int NOT NULL,
ResellerAlternateKey varchar(15) NULL,
BusinessType varchar(20) NOT NULL,
ResellerName varchar(50) NOT NULL,
NumberEmployees int NULL,
OrderFrequency char(1) NULL,
BankName varchar(50) NULL
)

Mientras que para traspasar el contenido del archivo
de texto (situado en una supuesta ruta C:Datos) a esta tabla utilizaremos la
instrucción BULK
INSERT
.

 

BULK INSERT Reseller
FROM ‘C:DatosDimReseller.txt’
WITH (FIELDTERMINATOR = ‘|’, CODEPAGE = ‘ACP’, FIRSTROW = 2)

 

¿Y si el archivo
de datos no está correctamente normalizado?

En condiciones normales el anterior bloque de código
se ejecutaría sin problemas, cargando el contenido del archivo en la tabla.
Pero supongamos que los datos que se exportan al archivo se recogen a partir de
un formulario en el que el campo correspondiente a la columna ResellerName
(columna número cuatro) de la tabla es de texto libre, pudiendo el usuario
introducir cualquier carácter, y existiendo, por tal motivo, algunos registros
en los que en el contenido de dicho campo podemos encontrarnos con los mismos caracteres
pipe que utilizamos como delimitador de columna al exportar los datos al
archivo.

 

Esto es algo que desvirtúa completamente nuestra
operación de importación de datos desde el archivo, ya que provoca errores como
los siguientes.

 

Ante una situación como esta resulta necesario
desarrollar un proceso que elimine, de los valores que han de traspasarse a la
columna ResellerName, los caracteres pipe que están provocando el conflicto en
la operación de importación. Por lo que en primer lugar, vamos a tomar el contenido
de una de las filas del archivo que producen el error (la fila 3, por ejemplo)
y trabajar sobre dicho texto hasta obtener el código que realice la depuración
de los caracteres sobrantes. A continuación se muestra la fila seleccionada.

 

3|AW00000003|Warehouse|Advan|ced Bi|ke Comp|one|nts|40|Q|Primary
International

 

Tabla
numérica auxiliar al rescate

Para comenzar necesitamos averiguar, dentro de la
cadena, las posiciones de inicio y fin del campo a depurar, por lo que aquí es
donde recurriremos al uso de una tabla numérica auxiliar con base 1 (primera
fila con valor 1), y compuesta por 100 filas. Como primera aproximación al
problema calcularemos las posiciones de todos los delimitadores de la manera
que vemos en el siguiente bloque de código.

 

DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);

SET @sTexto = ‘3|AW00000003|Warehouse|Advan|ced Bi|ke Comp|one|nts|40|Q|Primary International’;
SET @sDelimitador = ‘|’;

SELECT
ROW_NUMBER() OVER(ORDER BY NumeroID) AS NumeroDelimitador,
NumeroID AS PosicionDelimitador
FROM Numeros
WHERE SUBSTRING(@sTexto,NumeroID,1) = @sDelimitador
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) ROWS ONLY;

 

El conjunto de resultados obtenido podemos verlo a
continuación.

 

Analizando la cadena asignada a la variable @sTexto
observaremos que el valor para el campo ResellerName contiene cuatro caracteres
pipe que necesitamos eliminar.

 

Los números de orden del delimitador de inicio y fin
para este campo son el 3 y 8, que corresponden a las posiciones 23 y 52 en el
cómputo total de caracteres de la cadena, como muestra el siguiente diagrama.

 

Selección
de delimitadores inicial y final

Lo que haremos a continuación será incluir el código
que calcula los delimitadores en una expresión
común de tabla
(cteDelimitadores), seleccionando desde la
consulta externa aquellos delimitadores que anteriormente hemos indicado que
representan el inicio y fin del campo a depurar. Para facilitar el desarrollo
del código asignaremos a variables los datos con los que vamos a trabajar:
cadena, carácter delimitador, número de campo sobre el que vamos a realizar la
depuración y número total de campos existente en la cadena.

 

DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);
DECLARE @nCampoDepurar AS int;
DECLARE @nTotalCampos AS int;

SET @sTexto = ‘3|AW00000003|Warehouse|Advan|ced Bi|ke Comp|one|nts|40|Q|Primary International’;
SET @sDelimitador = ‘|’;
SET @nCampoDepurar = 4;
SET @nTotalCampos = 7;

WITH
cteDelimitadores AS
(
            SELECT
            ROW_NUMBER() OVER(ORDER BY
NumeroID) AS NumeroDelimitador,
            NumeroID AS
PosicionDelimitador
            FROM Numeros
            WHERE
SUBSTRING(@sTexto,NumeroID,1) = @sDelimitador
            ORDER BY NumeroID
            OFFSET 0 ROWS
            FETCH FIRST LEN(@sTexto) ROWS
ONLY
)
SELECT
NumeroDelimitador,
PosicionDelimitador
FROM cteDelimitadores
WHERE NumeroDelimitador IN (
            (@nCampoDepurar – 1),
            (
                        ((SELECT
MAX(NumeroDelimitador) FROM cteDelimitadores) –
                        (@nTotalCampos –
@nCampoDepurar)) + 1
            )
)

 

Averiguadas las posiciones de la subcadena a
obtener, y al igual que hicimos en el paso anterior, situaremos el código para
calcular las posiciones de inicio y fin en una expresión de tabla (cteDelimitadoresInicioFin),
volviendo a generar el número de orden del delimitador, ya que llegados a este
punto, el conjunto de resultados con el que vamos a trabajar estará compuesto
únicamente por dos filas, facilitando así el inminente trabajo con SUBSTRING,
puesto que de antemano sabremos que la fila con el número de delimitador 1
contendrá la primera posición de la subcadena, y la fila con el delimitador 2
corresponderá a la última posición.

 

DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);
DECLARE @nCampoDepurar AS int;
DECLARE @nTotalCampos AS int;

SET @sTexto = ‘3|AW00000003|Warehouse|Advan|ced Bi|ke Comp|one|nts|40|Q|Primary International’;
SET @sDelimitador = ‘|’;
SET @nCampoDepurar = 4;
SET @nTotalCampos = 7;

WITH
cteDelimitadores AS
(
            SELECT
            ROW_NUMBER() OVER(ORDER BY
NumeroID) AS NumeroDelimitador,
            NumeroID AS
PosicionDelimitador
            FROM Numeros
            WHERE
SUBSTRING(@sTexto,NumeroID,1) = @sDelimitador
            ORDER BY NumeroID
            OFFSET 0 ROWS
            FETCH FIRST LEN(@sTexto) ROWS
ONLY
),
cteDelimitadoresInicioFin AS
(
            SELECT
            ROW_NUMBER() OVER(ORDER BY
NumeroDelimitador) AS NumeroDelimitador,
            PosicionDelimitador
            FROM cteDelimitadores
            WHERE NumeroDelimitador IN (
                        (@nCampoDepurar –
1),
                        (
                                   ((SELECT
MAX(NumeroDelimitador) FROM cteDelimitadores) –
                                   (@nTotalCampos
– @nCampoDepurar)) + 1
                        )
            )
)
SELECT *
FROM cteDelimitadoresInicioFin

 

 

Extracción
de la subcadena incorrecta

A continuación agregaremos a la consulta una nueva
expresión de tabla (cteCampoOriginal) en la que emplearemos la función
SUBSTRING para extraer la subcadena a depurar. Los parámetros start y length de SUBSTRING los obtendremos a partir de los valores de la
expresión de tabla anterior, encargada de calcular las posiciones inicial y
final de los delimitadores.

 

DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);
DECLARE @nCampoDepurar AS int;
DECLARE @nTotalCampos AS int;

SET @sTexto = ‘3|AW00000003|Warehouse|Advan|ced Bi|ke Comp|one|nts|40|Q|Primary International’;
SET @sDelimitador = ‘|’;
SET @nCampoDepurar = 4;
SET @nTotalCampos = 7;

WITH
cteDelimitadores AS
(
            SELECT
            ROW_NUMBER() OVER(ORDER BY
NumeroID) AS NumeroDelimitador,
            NumeroID AS
PosicionDelimitador
            FROM Numeros
            WHERE
SUBSTRING(@sTexto,NumeroID,1) = @sDelimitador
            ORDER BY NumeroID
            OFFSET 0 ROWS
            FETCH FIRST LEN(@sTexto) ROWS
ONLY
),
cteDelimitadoresInicioFin AS
(
            SELECT
            ROW_NUMBER() OVER(ORDER BY
NumeroDelimitador) AS NumeroDelimitador,
            PosicionDelimitador
            FROM cteDelimitadores
            WHERE NumeroDelimitador IN (
                        (@nCampoDepurar –
1),
                        (
                                   ((SELECT
MAX(NumeroDelimitador) FROM cteDelimitadores) –
                                   (@nTotalCampos
– @nCampoDepurar)) + 1
                        )
            )
),
cteCampoOriginal AS
(
            SELECT
            SUBSTRING(
                        @sTexto,
                        (SELECT PosicionDelimitador
+ 1 FROM cteDelimitadoresInicioFin WHERE NumeroDelimitador = 1),
                        (
                                   (SELECT
PosicionDelimitador FROM cteDelimitadoresInicioFin WHERE NumeroDelimitador = 2) –
                                   (SELECT
PosicionDelimitador FROM cteDelimitadoresInicioFin WHERE NumeroDelimitador = 1)
                        ) – 1
            ) AS CampoOriginal
)
SELECT *
FROM cteCampoOriginal

 

 

Depurando
el exceso de delimitadores

Para eliminar los caracteres pipe de la subcadena
que acabamos de obtener utilizaremos la función REPLACE dentro de una última
expresión de tabla (cteCampoDepurado), la cual devolverá tanto la subcadena
original como la ya depurada sin delimitadores. En la siguiente sentencia vemos
el código al completo, donde podemos observar la evolución de nuestro proceso,
desde el texto completo original al depurado, pasando por la subcadena original
y la depurada.

 

DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);
DECLARE @nCampoDepurar AS int;
DECLARE @nTotalCampos AS int;

SET @sTexto = ‘3|AW00000003|Warehouse|Advan|ced Bi|ke Comp|one|nts|40|Q|Primary International’;
SET @sDelimitador = ‘|’;
SET @nCampoDepurar = 4;
SET @nTotalCampos = 7;

WITH
cteDelimitadores AS
(
            SELECT
            ROW_NUMBER() OVER(ORDER BY
NumeroID) AS NumeroDelimitador,
            NumeroID AS
PosicionDelimitador
            FROM Numeros
            WHERE
SUBSTRING(@sTexto,NumeroID,1) = @sDelimitador
            ORDER BY NumeroID
            OFFSET 0 ROWS
            FETCH FIRST LEN(@sTexto) ROWS
ONLY
),
cteDelimitadoresInicioFin AS
(
            SELECT
            ROW_NUMBER() OVER(ORDER BY
NumeroDelimitador) AS NumeroDelimitador,
            PosicionDelimitador
            FROM cteDelimitadores
            WHERE NumeroDelimitador IN (
                        (@nCampoDepurar –
1),
                        (
                                   ((SELECT
MAX(NumeroDelimitador) FROM cteDelimitadores) –
                                   (@nTotalCampos
– @nCampoDepurar)) + 1
                        )
            )
),
cteCampoOriginal AS
(
            SELECT
            SUBSTRING(
                        @sTexto,
                        (SELECT
PosicionDelimitador + 1 FROM cteDelimitadoresInicioFin WHERE NumeroDelimitador = 1),
                        (
                                   (SELECT
PosicionDelimitador FROM cteDelimitadoresInicioFin WHERE NumeroDelimitador = 2) –
                                   (SELECT
PosicionDelimitador FROM cteDelimitadoresInicioFin WHERE NumeroDelimitador = 1)
                        ) – 1
            ) AS CampoOriginal
),
cteCampoDepurado AS
(
            SELECT
            CampoOriginal,
            REPLACE(CampoOriginal,@sDelimitador,”)
AS CampoDepurado
            FROM cteCampoOriginal
)
SELECT
@sTexto AS TextoOriginal,
CampoOriginal,
CampoDepurado,
REPLACE(@sTexto,CampoOriginal,CampoDepurado) AS TextoDepurado
FROM cteCampoDepurado

 

 

Organizar
el código para su reutilización

Para poder reutilizar todo este código en diferentes
escenarios de trabajo vamos a situarlo en una función de tipo escalar que
llamaremos DepurarDelimitadores. En los parámetros a pasar a esta función
encontramos la cadena que contiene el valor a depurar, el carácter delimitador del
texto, el número del campo a depurar y el número total de campos a tratar. El
valor de retorno de la función, cuyo código vemos a continuación, será la
cadena pasada en el primer parámetro una vez quitados los delimitadores
sobrantes de la subcadena correspondiente al campo a depurar.

 

CREATE FUNCTION DepurarDelimitadores(@sTexto AS
varchar(100),
            @sDelimitador AS char(1),
            @nCampoDepurar AS int,
            @nTotalCampos AS int
)
RETURNS varchar(100)
AS
BEGIN

DECLARE @sTextoDepurado AS varchar(100);

WITH
cteDelimitadores AS
(
            SELECT
            ROW_NUMBER() OVER(ORDER BY
NumeroID) AS NumeroDelimitador,
            NumeroID AS
PosicionDelimitador
            FROM Numeros
            WHERE
SUBSTRING(@sTexto,NumeroID,1) = @sDelimitador
            ORDER BY NumeroID
            OFFSET 0 ROWS
            FETCH FIRST LEN(@sTexto) ROWS
ONLY
),
cteDelimitadoresInicioFin AS
(
            SELECT
            ROW_NUMBER() OVER(ORDER BY
NumeroDelimitador) AS NumeroDelimitador,
            PosicionDelimitador
            FROM cteDelimitadores
            WHERE NumeroDelimitador IN (
                        (@nCampoDepurar –
1),
                        (
                                   ((SELECT
MAX(NumeroDelimitador) FROM cteDelimitadores) –
                                   (@nTotalCampos
– @nCampoDepurar)) + 1
                        )
            )
),
cteCampoOriginal AS
(
            SELECT
            SUBSTRING(
                        @sTexto,
                        (SELECT
PosicionDelimitador + 1 FROM cteDelimitadoresInicioFin WHERE NumeroDelimitador = 1),
                        (
                                   (SELECT
PosicionDelimitador FROM cteDelimitadoresInicioFin WHERE NumeroDelimitador = 2) –
                                   (SELECT
PosicionDelimitador FROM cteDelimitadoresInicioFin WHERE NumeroDelimitador = 1)
                        ) – 1
            ) AS CampoOriginal
),
cteCampoDepurado AS
(
            SELECT
            CampoOriginal,
            REPLACE(CampoOriginal,@sDelimitador,”)
AS CampoDepurado
            FROM cteCampoOriginal
)
SELECT
@sTextoDepurado = REPLACE(@sTexto,CampoOriginal,CampoDepurado)
FROM cteCampoDepurado;

RETURN @sTextoDepurado;

END

Una vez creada la función vamos a probar su funcionamiento utilizando la cadena
con la que hemos experimentado a lo largo del desarrollo de todo el proceso.

 

Limpieza de
los datos origen

Finalizado el desarrollo del código encargado de
eliminar los delimitadores sobrantes, la siguiente tarea consistirá en importar
el archivo de texto a una tabla formada por una sola columna, realizando la
depuración del excedente de caracteres delimitadores sobre esta nueva tabla. A
continuación vemos las sentencias encargadas de la creación y carga de datos
para dicha tabla.

 

CREATE TABLE ColumnaTexto
(
Columna varchar(200) NULL
)

BULK INSERT ColumnaTexto
FROM ‘C:DatosDimReseller.txt’
WITH (CODEPAGE = ‘ACP’, FIRSTROW = 2)

 

Analicemos ahora cómo aplicar la función DepurarDelimitadores
para eliminar los delimitadores innecesarios en la tabla ColumnaTexto. Recordemos
que cada fila de esta tabla tiene 6 caracteres delimitadores (exceptuando
aquellas con exceso de dicho carácter), que a su vez definen 7 campos.

 

Si queremos averiguar, por lo tanto, las filas de la
tabla a las que hay que aplicar la depuración, tenemos que encontrar aquellas
que contengan más de seis delimitadores; lo que conseguiremos calculando la
diferencia entre la longitud total de caracteres de cada fila menos dicha
longitud sin los caracteres delimitadores. Para realizar estos cálculos
emplearemos de forma combinada las funciones LEN y REPLACE, tal y como muestra
la siguiente sentencia.

 

SELECT
Columna,
(LEN(Columna) – LEN(REPLACE(Columna,’|’,”))) AS NumeroDelimitadores
FROM ColumnaTexto

 

 

Adaptando el código anterior dentro de una sentencia
de actualización que utilice una expresión de tabla para seleccionar aquellas
filas con exceso de delimitadores, la operación de modificación sobre la tabla
ColumnaTexto empleando la función DepurarDelimitadores quedaría de la siguiente
manera para depurar el cuarto campo de los siete existentes en cada fila.

 

WITH cteColumnaTexto AS
(
            SELECT
            Columna
            FROM ColumnaTexto
            WHERE (LEN(Columna) –
LEN(REPLACE(Columna,’|’,”))) > 6
)
UPDATE cteColumnaTexto
SET Columna = dbo.DepurarDelimitadores(Columna,’|’,4,7);

 

Tras la ejecución de la anterior sentencia, las
filas con la cantidad incorrecta de delimitadores habrán sido corregidas.

 

Traslado de
los datos depurados a la tabla definitiva

Hemos llegado prácticamente al final del proceso.
Tan sólo resta traspasar los valores separados por delimitadores de cada fila
de la tabla ColumnaTexto a la tabla Reseller, siendo esta una tarea que realizaremos
en dos fases.

En la primera fase, ayudándonos de la función
ParticionarCadena que creamos en el artículo
anterior
y de la instrucción CROSS APPLY, generaremos un
conjunto de resultados a partir de la tabla ColumnaTexto, donde cada fila de
dicha tabla se subdividirá en 7 filas. De cada una de estas nuevas filas,
la columna Subcadena contendrá el valor de uno de los campos de una fila de la
tabla ColumnaTexto, y la columna NumCampoEnFila servirá de identificador del
número de columna de la tabla Reseller, a la que más adelante traspasaremos el
valor. A continuación se muestra la sentencia que debemos utilizar.

 

SELECT
C.Columna,
SubC.Subcadena,
ROW_NUMBER() OVER(PARTITION BY C.Columna ORDER BY C.Columna) AS NumCampoEnFila
FROM ColumnaTexto AS C
CROSS APPLY dbo.ParticionarCadena(C.Columna,’|’) AS SubC

El siguiente diagrama ilustra esta parte del proceso.

 

En la segunda fase situaremos la sentencia anterior
en una expresión de tabla y pivotaremos el conjunto de resultados obtenido a
partir de la misma.

 

WITH cteColumnaTexto AS
(
            SELECT
            C.Columna,
            SubC.Subcadena,
            ROW_NUMBER() OVER(PARTITION BY
C.Columna ORDER BY C.Columna) AS NumCampoEnFila
            FROM ColumnaTexto AS C
            CROSS APPLY
dbo.ParticionarCadena(C.Columna,’|’) AS SubC
)
SELECT
[1] AS ResellerKey,
[2] AS ResellerAlternateKey,
[3] AS BusinessType,
[4] AS ResellerName,
[5] AS NumberEmployees,
[6] AS OrderFrequency,
[7] AS BankName
FROM
(
            SELECT
            Columna,
            Subcadena,
            NumCampoEnFila
            FROM cteColumnaTexto
) AS tblOrigen
PIVOT
(
            MAX(Subcadena)
            FOR NumCampoEnFila IN (
            [1],
            [2],
            [3],
            [4],
            [5],
            [6],
            [7]
            )
) AS tblPivot;

 

La siguiente figura muestra gráficamente la
operación de transposición de filas a columnas conseguida mediante la
instrucción PIVOT.

 

Añadiendo una instrucción INSERT
INTO
sobre la tabla Reseller a la sentencia anterior habremos logrado nuestro
objetivo.

 

WITH cteColumnaTexto AS
(
            SELECT
            C.Columna,
            SubC.Subcadena,
            ROW_NUMBER() OVER(PARTITION BY
C.Columna ORDER BY C.Columna) AS NumCampoEnFila
            FROM ColumnaTexto AS C
            CROSS APPLY
dbo.ParticionarCadena(C.Columna,’|’) AS SubC
)
INSERT INTO Reseller (
ResellerKey,
ResellerAlternateKey,
BusinessType,
ResellerName,
NumberEmployees,
OrderFrequency,
BankName
)
SELECT
[1],
[2],
[3],
[4],
[5],
[6],
[7]
FROM
(
            SELECT
            Columna,
            Subcadena,
            NumCampoEnFila
            FROM cteColumnaTexto
) AS tblOrigen
PIVOT
(
            MAX(Subcadena)
            FOR NumCampoEnFila IN (
            [1],
            [2],
            [3],
            [4],
            [5],
            [6],
            [7]
            )
) AS tblPivot;

 

Una vez ejecutada esta sentencia, las columnas de
cada una de las filas de la tabla Reseller contendrán los datos que
anteriormente se encontraban unidos en la columna única de la tabla
ColumnaTexto.

Resumiendo a modo de conclusión, en el presente
artículo hemos mostrado una más de las variadas aplicaciones que podemos dar a las
tablas numéricas auxiliares, consistente en este caso en permitirnos depurar un
archivo de datos con valores  separados
por un delimitador. Confío en que esta técnica pueda resultar de utilidad al
lector en alguna ocasión.

Tablas numéricas auxiliares (Tally Tables) en acción. Extracción de subcadenas mediante delimitadores

Después de explicar en el anterior artículo
el modo de creación de tablas de dimensión para cubos de datos empleando tablas
numéricas auxiliares, en esta ocasión continuaremos exponiendo situaciones de
aplicación práctica relacionadas con el empleo de dichas tablas, abordando un
problema frecuentemente resuelto mediante esta herramienta: la partición de una
cadena en varias subcadenas a partir de un delimitador utilizado en la cadena
principal.

Para lograr este objetivo el procedimiento a seguir
consistirá en combinar la tabla numérica con la cadena; con ello averiguaremos
respectivamente las posiciones de inicio y la longitud de cada subcadena, lo
que nos permitirá extraerlas de la cadena principal.

En primer lugar necesitaremos crear una tabla
numérica con base uno, es decir, que el valor del primer registro sea el número 1 (ver un ejemplo en este enlace),
conteniendo alrededor de 200 filas, cantidad suficiente para las pruebas a
realizar, ya que la cadena con la que trabajaremos tendrá una longitud menor de
dicha cifra.

Como primera aproximación al problema comenzaremos a
combinar la tabla con la cadena para obtener un conjunto de resultados
compuesto por sus caracteres independientes. Mediante la cláusula OFFSET…FETCH
calcularemos cuántas filas de la tabla numérica necesitaremos emplear para
realizar la extracción.

 

DECLARE @sTexto AS varchar(100);
SET @sTexto = ‘monitor,teclado,ratón,impresora,smartphone’;

SELECT NumeroID,
SUBSTRING(@sTexto, NumeroID, 1) AS Caracter
FROM Numeros
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) ROWS ONLY;

 

También podemos usar la cláusula TOP para averiguar la cantidad de filas a
utilizar.



SELECT TOP(LEN(@sTexto))

 

 

Pero como acabamos de mencionar en un párrafo
anterior, los datos esenciales que necesitamos para la extracción de las
subcadenas son su posición de inicio y longitud. Para el primer caso utilizaremos
una condición en la cláusula WHERE mediante la que conseguiremos que la
consulta sólo devuelva las posiciones de la cadena en las que se halle un
carácter delimitador, lo que nos servirá para calcular la posición de inicio de
cada subcadena.

 

DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);

SET @sTexto = ‘monitor,teclado,ratón,impresora,smartphone’;
SET @sDelimitador = ‘,’;

SELECT
NumeroID AS PosicionCaracterInicio,
SUBSTRING(@sTexto, NumeroID + 1, 1) AS CaracterInicio
FROM Numeros
WHERE SUBSTRING(@sTexto, NumeroID, 1) = @sDelimitador
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) ROWS ONLY;

 

 

El lector habrá comprobado que esta operación no
proporciona la posición inicial de la primera subcadena debido a que el inicio
(y el final) de la cadena principal carece del carácter delimitador.

No obstante, podemos solucionar este inconveniente desde
la cláusula WHERE, concatenando en la función SUBSTRING el delimitador y la
cadena, para obtener las posiciones de todos los delimitadores. Cuando esto
ocurra, la consulta devolverá el mismo número de posición también a través de
otra llamada a SUBTRING, pero aquí aplicaremos dicha posición solamente a la
cadena principal, con lo que obtendremos el carácter de inicio de cada
subcadena.

 

DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);

SET @sTexto = ‘monitor,teclado,ratón,impresora,smartphone’;
SET @sDelimitador = ‘,’;

SELECT
NumeroID AS PosicionCaracterInicio,
SUBSTRING(@sTexto, NumeroID, 1) AS CaracterInicio
FROM Numeros
WHERE SUBSTRING(@sDelimitador + @sTexto, NumeroID, 1) = @sDelimitador
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) ROWS ONLY;

 

 

El siguiente diagrama muestra gráficamente la
operativa de la consulta anterior.

 

En lo que respecta al cálculo para hallar la
longitud de la subcadena, mediante la función CHARINDEX y la
concatenación de las variables de la cadena y el delimitador lograremos la posición del delimitador
situado al final de cada subcadena. A este valor le restaremos la posición del
carácter de inicio obteniendo la longitud, y por último uniremos todos estos elementos
en la función SUBSTRING, obteniendo como resultado la subcadena que
necesitamos.

 

DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);

SET @sTexto = ‘monitor,teclado,ratón,impresora,smartphone’;
SET @sDelimitador = ‘,’;

SELECT
NumeroID AS PosicionCaracterInicio,
SUBSTRING(@sTexto, NumeroID, 1) AS CaracterInicio,
CHARINDEX(@sDelimitador, @sTexto + @sDelimitador, NumeroID) AS PosicionDelimitador,
CHARINDEX(@sDelimitador, @sTexto + @sDelimitador, NumeroID) – NumeroID AS LongitudSubcadena,
SUBSTRING(@sTexto, NumeroID, CHARINDEX(@sDelimitador, @sTexto + sDelimitador, NumeroID) – NumeroID) AS Subcadena
FROM Numeros
WHERE SUBSTRING(@sDelimitador + @sTexto, NumeroID, 1) = @sDelimitador
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) ROWS ONLY;

 

 

A continuación podemos ver también un diagrama
descriptivo del proceso.

 

Sin embargo, a pesar de su correcto funcionamiento,
el código anterior sufre un problema de rendimiento originado por las
concatenaciones realizadas entre las variables que contienen la cadena
principal y el delimitador, tal y como Jeff Moden describe en su excelente artículo
Tally
OH! An Improved SQL 8K “CSV Splitter” Function
, donde también se proponen
algunas técnicas alternativas para conseguir particionar la cadena sin usar las
mencionadas operaciones de concatenación

Una de estas técnicas consiste en utilizar una tabla
numérica con base cero
(primera fila con valor cero),
gracias a la cual evitaremos la concatenación. Esta práctica, por contra, nos
obligará a que en la cláusula WHERE, además de la condición para obtener las
posiciones que correspondan a un delimitador, tengamos que añadir otra que compruebe
los casos en que la columna NumeroID valga cero, ya que este será el criterio
para poder calcular y obtener la primera subcadena de la cadena principal. No
obstante, antes de realizar la extracción de subcadenas, utilicemos en primer
lugar la consulta para analizar los diferentes elementos que obtenemos para la
operación.

 

DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);
SET @sTexto = ‘monitor,teclado,ratón,impresora,smartphone’;
set @sDelimitador = ‘,’

SELECT
NumeroID + 1 AS PosicionPrimerCaracter,
CHARINDEX(@sDelimitador, @sTexto, NumeroID + 1) AS PosicionDelimitador,
(CHARINDEX(@sDelimitador,@sTexto, NumeroID + 1)) – (NumeroID + 1) AS LongitudSubcadena
FROM Numeros
WHERE SUBSTRING(@sTexto, NumeroID, 1) = @sDelimitador
OR NumeroID=0
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) + 1 ROWS ONLY;

 

 

Observando los resultados obtenidos nos percataremos
de que utilizando los cálculos realizados para averiguar las posiciones del
primer carácter de la subcadena, el delimitador y la longitud; si intentamos
extraer las subcadenas mediante las funciones SUBSTRING y CHARINDEX obtendremos
un error provocado por los valores de la última fila del conjunto de resultados.
Esto es debido a que el cálculo para obtener la longitud de la subcadena arroja
un valor negativo por la inexistencia de carácter delimitador al final de la
cadena principal, produciéndose un error en la función SUBSTRING al pasar este
valor a su parámetro length.

Seguidamente vemos el código a utilizar para
reproducir este error.

DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);
SET @sTexto = ‘monitor,teclado,ratón,impresora,smartphone’;
SET @sDelimitador = ‘,’

SELECT
SUBSTRING(@sTexto, (NumeroID + 1), CHARINDEX(@sDelimitador, @sTexto, NumeroID + 1) – (NumeroID + 1)) AS Subcadena
FROM Numeros
WHERE SUBSTRING(@sTexto, NumeroID, 1) = @sDelimitador
OR NumeroID=0
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) + 1 ROWS ONLY;

 

Como vemos en la siguiente figura, las cuatro
primeras subcadenas son obtenidas correctamente, produciéndose el error en la
quinta.

 

Una forma de evitar el error (que no solucionarlo)
consiste en aplicar la función ABS a la
expresión utilizada para el parámetro length
de la función SUBSTRING, convirtiendo a signo positivo el valor de la longitud
de la subcadena.

 

SUBSTRING(@sTexto, (NumeroID + 1), ABS(CHARINDEX(@sDelimitador,
@sTexto, NumeroID + 1) – (NumeroID + 1))) AS Subcadena

 

Y como acabamos de mencionar, este pequeño truco no
soluciona el problema, porque lo que hace no es calcular la longitud de la
última subcadena, sino devolver un valor, que en este caso es igual a la
posición de inicio de la misma, lo que en algunas ocasiones puede funcionar, pero
si la longitud de esa última subcadena es lo suficientemente grande aparecerá truncada,
como vemos en el siguiente ejemplo.

 

Es por ello que para averiguar la longitud de las
subcadenas debemos emplear una estrategia distinta que nos permita calcular la
última de ellas sin que ocurra este error, y hacerlo además de una forma que
suponga la menor penalización posible de rendimiento en la ejecución de la
consulta.

Aunque el error se produce en la función SUBSTRING
al utilizar un valor negativo en el parámetro length, la raíz del problema reside en el cálculo de la posición
del delimitador realizado mediante CHARINDEX, ya que la última subcadena carece
de dicho carácter, y por lo tanto obtenemos un cero como resultado.

Vamos a intentar “engañar” a nuestra operación de
cálculo del delimitador, de forma que cuando tenga que buscar dicho valor para
la última subcadena, al no existir realmente tal delimitador, devolvamos 8000
como posición ficticia, que es el valor máximo del tipo de dato con el que
estamos trabajando (varchar), y por consiguiente, la máxima longitud de cadena
que utilizaríamos.

Esta operación la realizaremos en dos pasos: en
primer lugar, cuando CHARINDEX devuelva el valor cero, mediante la función NULLIF
devolveremos NULL; a continuación comprobaremos esta situación mediante la
función ISNULL,
y en caso afirmativo devolveremos 8000; si todo lo anterior no se cumple se
ejecutará solamente CHARINDEX, lo cual ocurrirá para todas las subcadenas
excepto la última.

De esta manera, al calcular la longitud de la
subcadena nos aseguramos la obtención de un valor que no producirá error al ser
utilizado en SUBSTRING, ya que si la suma de los valores para los parámetros start y length es mayor que la longitud de la cadena que estamos manejando,
esta función devolverá la subcadena empezando desde el parámetro start hasta el final de la misma.

 

DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);
SET @sTexto = ‘monitor,teclado,ratón,impresora,smartphone’;
SET @sDelimitador = ‘,’;

SELECT
SUBSTRING(
            @sTexto,
            NumeroID + 1,
            ISNULL(NULLIF(CHARINDEX(@sDelimitador,
@sTexto, NumeroID + 1 ), 0), 8000) – (NumeroID + 1)
) AS Subcadena
FROM Numeros
WHERE SUBSTRING(@sTexto, NumeroID, 1) = @sDelimitador
OR NumeroID = 0
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) + 1 ROWS ONLY;

 

Tras aplicar esta corrección al código obtendremos
un conjunto de resultados compuesto por todas las subcadenas contenidas en la
cadena principal.

 

Aunque ya hemos logrado optimizar la consulta
eliminando las concatenaciones iniciales y solventando el error de cálculo para
la longitud de las subcadenas, el operador OR también podría afectar
negativamente al rendimiento, por lo que vamos a incorporar un nuevo ajuste a
nuestro código, que elimine la necesidad de usar dicho operador.

Puesto que la utilidad de OR dentro del contexto en el
que nos encontramos reside en la capacidad de permitirnos obtener la posición
de inicio de la primera subcadena, para
conseguir el mismo resultado evitando el uso de este operador vamos a crear una
expresión de tabla (CTE)
compuesta por dos consultas unidas mediante el operador UNION. La primera
devolverá el valor 1, mientras que la segunda será nuestra vieja conocida (sin OR)
encargada de devolver las primeras posiciones de las subcadenas, en este caso a
partir de la segunda subcadena en adelante.

 

DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);

SET @sTexto = ‘monitor,teclado,ratón,impresora,smartphone’;
SET @sDelimitador = ‘,’;

WITH ctePosicionesInicio AS
(
            SELECT 1 AS PosicionInicio

            UNION ALL

            SELECT NumeroID + 1 AS
PosicionInicio
            FROM Numeros
            WHERE SUBSTRING(@sTexto,
NumeroID, 1) = @sDelimitador
            ORDER BY NumeroID
            OFFSET 0 ROWS
            FETCH FIRST LEN(@sTexto) + 1
ROWS ONLY
)
SELECT
SUBSTRING(
            @sTexto,
            PosicionInicio,
            ISNULL(NULLIF(CHARINDEX(@sDelimitador,
@sTexto, PosicionInicio), 0), 8000) – PosicionInicio
) AS Subcadena
FROM ctePosicionesInicio;

 

 

El siguiente diagrama describe gráficamente la
consulta que acabamos de crear.

 

Completada esta nueva mejora sobre nuestro código
podemos dar por concluido su desarrollo, queda ahora por ver el modo de aplicarlo
de la manera más práctica posible, ya que el hecho de copiar y pegar esta
sentencia en aquellos lugares donde vayamos a necesitarla no parece el modo de
uso más adecuado ni más sencillo de cara a su mantenimiento.

Una buena opción de cara a su reutilización sería
incluir este código en una función con valor de retorno de tipo tabla (Inline
Table-Valued Function
) como vemos a continuación, lo que nos permitiría
aplicarla sobre la columna de una tabla que contuviera cadenas con valores
separados por un delimitador, de forma que el resultado de la partición de las
cadenas podríamos emplearlo como entrada en la inserción de otra tabla.

 

CREATE FUNCTION ParticionarCadena(@sTexto AS
varchar(100), @sDelimitador AS
char(1))
RETURNS TABLE
AS
RETURN
(

WITH ctePosicionesInicio AS
(
            SELECT 1 AS PosicionInicio

            UNION ALL

            SELECT NumeroID + 1 AS
PosicionInicio
            FROM Numeros
            WHERE SUBSTRING(@sTexto,
NumeroID, 1) = @sDelimitador
            ORDER BY NumeroID
            OFFSET 0 ROWS
            FETCH FIRST LEN(@sTexto) + 1
ROWS ONLY
)
SELECT
SUBSTRING(
            @sTexto,
            PosicionInicio,
            ISNULL(NULLIF(CHARINDEX(@sDelimitador,
@sTexto, PosicionInicio), 0), 8000) – PosicionInicio
) AS Subcadena
FROM ctePosicionesInicio

)

 

Como demostración del comentario anterior, en la base
de datos que estemos utilizando para realizar estas pruebas crearemos una tabla
con el nombre Cadenas, que contenga una columna identificadora de fila y otra
con los valores concatenados y separados por coma de las columnas EmployeeKey, EmployeeNationalIDAlternateKey,
FirstName, LastName, Title y DepartmentName, pertenecientes a la tabla
DimEmployee de la base de datos AdventureWorksDW2012.

 

CREATE TABLE Cadenas
(
            CadenaID int NULL,
            Cadena varchar(200) NULL
)

–////
INSERT INTO Cadenas (CadenaID,Cadena)
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
CONVERT(varchar(4), EmployeeKey) + ‘,’ +
EmployeeNationalIDAlternateKey + ‘,’ +
FirstName + ‘,’ +
LastName + ‘,’ +
Title + ‘,’ +
DepartmentName
FROM AdventureWorksDW2012..DimEmployee

 

 

Si queremos comprobar la ejecución de la función ParticionarCadena
contra la tabla que acabamos de crear, combinaremos en una consulta dicha tabla
junto a la llamada a la función utilizando el operador CROSS
APPLY
. En el lado derecho de la consulta situaremos la
función pasándole como parámetro la columna Cadena de la tabla Cadenas y el
delimitador a usar, mientras que en el lado izquierdo colocaremos las columnas
de la tabla Cadenas, y la columna Subcadena devuelta por la función.

 

SELECT
C.CadenaID,
C.Cadena,
SubC.Subcadena
FROM Cadenas AS C
CROSS APPLY dbo.ParticionarCadena(C.Cadena,’,’) AS SubC

 

 

A continuación crearemos la tabla Subcadenas, que
usaremos como destino de la operación de partición aplicada a la tabla Cadenas
utilizando la función ParticionarCadena.

 

CREATE TABLE Subcadenas
(
            Subcadena varchar(50) NULL
)

INSERT INTO Subcadenas (Subcadena)
SELECT SubC.Subcadena
FROM Cadenas AS C
CROSS APPLY dbo.ParticionarCadena(C.Cadena, ‘,’) AS SubC

 

 

Y tras este ejemplo concluimos el presente artículo,
en el que hemos podido comprobar nuevamente la enorme versatilidad de las
tablas numéricas auxiliares o tally tables, desarrollando en esta ocasión un
proceso que nos ha permitido tomar una cadena compuesta por subcadenas
separadas por un carácter delimitador y extraer de forma independiente cada una
de dichas subcadenas, esperamos que los ejemplos  aquí expuestos resulten de utilidad al lector.

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.

 

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
            –…
            –…

 

 

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.

 

Creación de tablas numéricas auxiliares (Tally Tables) (y 2)

En la primera parte de este artículo explicábamos en qué consiste una tabla numérica auxiliar, así como varias técnicas para su creación. En esta segunda entrega continuaremos exponiendo formas adicionales de generar tablas numéricas, de modo que el lector disponga de un amplio abanico de opciones que pueda aplicar en las más variadas situaciones.

 

Pseudo Cursor + IDENTITY()

Una variante de la operación expuesta en el apartado sobre pseudo cursores de la primera parte consistiría en utilizar una sola sentencia para crear, al mismo tiempo, la tabla numérica y rellenarla de valores, utilizando para tal propósito la instrucción SELECT…INTO junto a la función IDENTITY(), que en este caso realizaría idéntico trabajo de generación de números que ROW_NUMBER() en el ejemplo anterior.

 

IF OBJECT_ID(‘Numeros’,’U’) IS NOT NULL
BEGIN
            DROP TABLE Numeros;
END

DECLARE @dtInicio AS datetime;
DECLARE @dtFin AS datetime;

SET @dtInicio = CURRENT_TIMESTAMP;

SELECT
IDENTITY(int,1,1) AS NumeroID
INTO Numeros
FROM sys.all_columns AS tbl1
CROSS JOIN SYS.all_columns AS tbl2
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST 30000 ROWS ONLY;

SET @dtFin = CURRENT_TIMESTAMP;

SELECT DATEDIFF(millisecond,@dtInicio,@dtFin);

ALTER TABLE Numeros
ADD CONSTRAINT PK_Numeros
PRIMARY KEY (NumeroID);

 

Al igual que en el apartado anterior, si nuestra versión de SQL Server no soporta el uso de OFFSET…FETCH, emplearemos TOP para especificar el número de filas a rellenar en la tabla numérica auxiliar.

 

……….
……….
SELECT TOP (30000)
IDENTITY(INT,1,1) AS NumeroID
INTO Numeros
FROM sys.all_columns AS tbl1
CROSS JOIN SYS.all_columns AS tbl2;
……….
……….

 

Cruce múltiple de CTEs

A continuación es el turno de un interesante método desarrollado por Itzik Ben-Gan en su obra “Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions” [4], que se basa en el empleo de una serie de CTEs combinadas mediante cláusulas CROSS JOIN, para obtener el rango secuencial de números con el que rellenaremos nuestra tabla numérica.

La primera CTE, a la que daremos el nombre cteGenNumsBase (de generador base de números), se encarga de crear un conjunto de resultados que devuelve dos filas con el valor 1 en cada una de ellas, empleando un constructor de valores para tabla (Table Value Constructor) en forma de tabla derivada dentro de una cláusula FROM.

 

WITH
cteGenNumsBase AS (
            SELECT NumBaseID
            FROM (
                        VALUES
                                   (1),
                                   (1)
            ) AS tblNums(NumBaseID)
)
SELECT NumBaseID
FROM cteGenNumsBase;

 

 

A continuación agregaremos otra CTE con el nombre cteGenNums1 (de primer generador de números con CROSS JOIN), conteniendo una consulta contra la CTE cteGenNumsBase, que realizará una CROSS JOIN sobre esa misma CTE, obteniendo esta vez cuatro filas con el valor 1.

 

WITH
cteGenNumsBase AS (
            SELECT NumBaseID
            FROM (
                        VALUES
                                   (1),
                                   (1)
            ) AS tblNums(NumBaseID)
),
cteGenNums1 AS (
            SELECT 1 AS NumID
            FROM cteGenNumsBase AS tblGenNumsA
            CROSS JOIN cteGenNumsBase AS tblGenNumsB
)
SELECT NumID
FROM cteGenNums1;

 

 

Aunque no hemos completado todavía el código de este ejemplo, observe el lector que estamos empezando a aplicar nuevamente la técnica del pseudo cursor, ya que en la CTE cteGenNums1 utilizamos la presencia de filas obtenida a partir de la CTE cteGenNumsBase para generar nuevas filas, doblando su número hasta cuatro. Para volver a doblar dicho número, tan sólo necesitamos añadir otra CTE que utilice la misma lógica en su código, y así sucesivamente hasta concluir con una CTE (que aquí llamamos cteGenNumsTotal), que como ya conocemos de anteriores ejemplos, mediante la función ROW_NUMBER() se encargaría de generar la secuencia de números. La unión de todos estos elementos es lo que Jeff Moden denomina en [3] CTEs cruzadas en cascada (CROSS JOINED Cascaded CTE).

Seguidamente vemos el código del ejemplo al completo, en el que utilizando cinco CTEs para generar filas a partir de la CTE base, podríamos obtener hasta 4.294.967.296 filas, cantidad que raramente nos encontraremos en situación de necesitar; de ahí que mediante la cláusula OFFSET…FETCH de ORDER BY, limitemos el número de registros obtenidos.

 

WITH
cteGenNumsBase AS ( 
             –devuelve 2 filas
            SELECT NumBaseID
            FROM (
                        VALUES
                                   (1),
                                   (1)
            ) AS tblNums(NumBaseID)
),
cteGenNums1 AS ( 
             –devuelve 4 filas
            SELECT 1 AS NumID
            FROM cteGenNumsBase AS tblGenNumsA
            CROSS JOIN cteGenNumsBase AS tblGenNumsB
),
cteGenNums2 AS ( 
             –devuelve 16 filas
            SELECT 1 AS NumID
            FROM cteGenNums1 AS tblGenNumsA
            CROSS JOIN cteGenNums1 AS tblGenNumsB
),
cteGenNums3 AS ( 
             –devuelve 256 filas
            SELECT 1 AS NumID
            FROM cteGenNums2 AS tblGenNumsA
            CROSS JOIN cteGenNums2 AS tblGenNumsB
),
cteGenNums4 AS ( 
             –devuelve 65.536 filas
            SELECT 1 AS NumID
            FROM cteGenNums3 AS tblGenNumsA
            CROSS JOIN cteGenNums3 AS tblGenNumsB
),
cteGenNums5 AS ( 
             –devuelve 4.294.967.296 filas
            SELECT 1 AS NumID
            FROM cteGenNums4 AS tblGenNumsA
            CROSS JOIN cteGenNums4 AS tblGenNumsB
),
cteGenNumsTotal AS (
            SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NumeroID
            FROM cteGenNums5
)
SELECT NumeroID
FROM cteGenNumsTotal
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST 30000 ROWS ONLY;

 

El bloque de código anterior, aun cumpliendo su cometido, puede resultar un tanto sobrecargado debido a la extensión de los nombres utilizados y a la disposición de las sentencias; ello se debe a que el propósito principal es que la lógica aplicada resulte lo más comprensible al lector. Por dicho motivo, a continuación presentamos una versión más compacta del mismo, donde tras reducir los nombres de los identificadores, la colocación de las sentencias y las líneas de código, obtenemos la misma funcionalidad. Aprovechamos también para incluir aquí las sentencias de creación de la tabla numérica auxiliar.

 

IF OBJECT_ID(‘Numeros’,’U’) IS NOT NULL
BEGIN
            DROP TABLE Numeros;
END

CREATE TABLE Numeros
(
            NumeroID int NOT NULL
);

DECLARE @dtInicio AS datetime;
DECLARE @dtFin AS datetime;

SET @dtInicio = CURRENT_TIMESTAMP;

WITH
GNBase AS (SELECT N FROM (VALUES (1),(1)) AS NBase(N)),
GN1 AS (SELECT 1 AS N FROM GNBase AS tblA CROSS JOIN GNBase AS tblB),
GN2 AS (SELECT 1 AS N FROM GN1 AS tblA CROSS JOIN GN1 AS tblB),
GN3 AS (SELECT 1 AS N FROM GN2 AS tblA CROSS JOIN GN2 AS tblB),
GN4 AS (SELECT 1 AS N FROM GN3 AS tblA CROSS JOIN GN3 AS tblB),
GN5 AS (SELECT 1 AS N FROM GN4 AS tblA CROSS JOIN GN4 AS tblB),
GNTotal AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NumeroID FROM GN5)
INSERT INTO Numeros (NumeroID)
SELECT NumeroID
FROM GNTotal
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST 30000 ROWS ONLY;

SET @dtFin = CURRENT_TIMESTAMP;

SELECT DATEDIFF(millisecond,@dtInicio,@dtFin);

ALTER TABLE Numeros
ADD CONSTRAINT PK_Numeros
PRIMARY KEY (NumeroID);

 

Uno de los puntos fuertes de esta técnica reside en su excelente aprovechamiento de recursos, ya que durante la generación de las filas, el procesador de consultas del motor de SQL Server se detiene justo en el momento de alcanzar el número de registros especificado en ORDER BY…OFFSET…FETCH.

Como ya hemos explicado en ejemplos previos, para establecer el número de registros que necesitamos obtener, también podemos conseguir el mismo resultado mediante la cláusula TOP en caso de usar una versión de SQL Server que no implemente OFFSET…FETCH.

 

……….
……….
GNTotal AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NumeroID FROM GN5)
INSERT INTO Numeros (NumeroID)
SELECT TOP (30000) NumeroID
FROM GNTotal;
……….
……….

 

De igual forma, si la versión de SQL Server con la que trabajamos no soporta constructores de valores para tabla, en la primera CTE utilizaremos dos consultas que devuelvan el valor 1, aplicando sobre ellas el operador UNION.

 

……….
……….
WITH
GNBase AS (SELECT 1 AS N UNION ALL SELECT 1 AS N),
……….
……….

 

Incluir el valor cero como primer registro

La tabla numérica auxiliar creada en todos los ejemplos mostrados hasta el momento contiene el valor 1 en su primera fila, pero podríamos encontrarnos con situaciones en las que necesitáramos que ese primer registro de la tabla fuera un cero.

Para conseguir tal propósito podemos añadir una consulta a la CTE final del grupo de CTEs cruzadas, en la que devolvamos un conjunto de resultados con el valor cero, que combinaremos mediante el operador UNION con la consulta ya existente, encargada de los cruces múltiples de las CTEs. Por último, para poder obtener el último número que necesitamos del intervalo, tenemos que incrementar en uno el valor indicado en el modificador FETCH de la cláusula ORDER BY.

Como ejemplo de lo que acabamos de explicar, si queremos crear una tabla numérica con el rango de números de 0 a 30000, el valor de FETCH deberá ser 30001.

 

 

Intervalo de números con valor de inicio mayor de 1

Otra situación con la que podríamos encontrarnos, relacionada con la primera fila de la tabla numérica, sería que su valor tuviera que ser superior a 1, debiendo especificar, adicionalmente, los límites inferior y superior de la tabla.

Este problema puede ser resuelto mediante el uso de un procedimiento almacenado, al que pasaremos dos parámetros con los mencionados límites de la tabla, los cuales utilizaremos dentro del código del procedimiento en los modificadores OFFSET…FETCH tal y como vemos a continuación.

 

IF OBJECT_ID(‘spGenerarTablaNumeros’,’P’) IS NOT NULL
BEGIN
            DROP PROCEDURE spGenerarTablaNumeros;
END
GO

CREATE PROCEDURE spGenerarTablaNumeros
            @nPrimerNumero AS int,
            @nUltimoNumero AS int
AS
BEGIN

IF OBJECT_ID(‘Numeros’,’U’) IS NOT NULL
BEGIN
            DROP TABLE Numeros;
END

CREATE TABLE Numeros
(
            NumeroID int NOT NULL
);

WITH
GNBase AS (SELECT N FROM (VALUES (1),(1)) AS NBase(N)),
GN1 AS (SELECT 1 AS N FROM GNBase AS tblA CROSS JOIN GNBase AS tblB),
GN2 AS (SELECT 1 AS N FROM GN1 AS tblA CROSS JOIN GN1 AS tblB),
GN3 AS (SELECT 1 AS N FROM GN2 AS tblA CROSS JOIN GN2 AS tblB),
GN4 AS (SELECT 1 AS N FROM GN3 AS tblA CROSS JOIN GN3 AS tblB),
GN5 AS (SELECT 1 AS N FROM GN4 AS tblA CROSS JOIN GN4 AS tblB),
GNTotal AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NumeroID
ROM GN5)
INSERT INTO Numeros (NumeroID)
SELECT NumeroID
FROM GNTotal
ORDER BY NumeroID
OFFSET (@nPrimerNumero – 1) ROWS
FETCH FIRST ((@nUltimoNumero + 1) – @nPrimerNumero) ROWS ONLY;

ALTER TABLE Numeros
ADD CONSTRAINT PK_Numeros
PRIMARY KEY (NumeroID)

END
GO

 

Si ahora queremos crear una tabla numérica cuya primera y última fila sean respectivamente los valores 12537 y 28754, ejecutaríamos la siguiente sentencia.

 

EXECUTE spGenerarTablaNumeros 12537,28754;

 

 

Para lograr mediante la cláusula TOP el mismo resultado realizaremos la siguiente modificación al código del procedimiento almacenado.

……….
……….
INSERT INTO Numeros (NumeroID)
SELECT TOP((@nUltimoNumero + 1) – @nPrimerNumero) (@nPrimerNumero – 1) +
NumeroID
FROM GNTotal
……….
……….

 

Una vez completado este ejemplo concluimos el presente artículo en el que a lo largo de sus dos entregas hemos realizado una introducción a las tablas numéricas auxiliares o tally tables, presentando algunas de las técnicas más destacadas en la creación de las mismas. En un próximo artículo expondremos ejemplos prácticos del uso de esta herramienta confiando en que resulte de utilidad al lector.

 

Referencias

[1] Jeff Moden. “The “Numbers” or “Tally” Table: What it is and how it replaces a loop”. http://www.sqlservercentral.com/articles/T-SQL/62867/

[2] Itzik Ben-Gan. “Microsoft SQL Server 2012 T-SQL Fundamentals”. http://blogs.msdn.com/b/microsoft_press/archive/2012/07/16/new-book-microsoft-sql-server-2012-t-sql-fundamentals.aspx

[3] Jeff Moden. “Hidden RBAR: Counting with Recursive CTE’s”. http://www.sqlservercentral.com/articles/T-SQL/74118/

[4] Itzik Ben-Gan. “Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions”. http://blogs.msdn.com/b/microsoft_press/archive/2012/04/26/new-book-microsoft-sql-server-2012-high-performance-t-sql-using-window-functions.aspx