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.

Publicado por Luis Miguel Blanco | con no comments

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.

 

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

Una tabla numérica auxiliar (denominada también tabla de conteo, tally table o simplemente tabla de números) es una tabla compuesta por una única columna, que almacena una cantidad variable de registros con valores numéricos consecutivos, y que se emplea como herramienta de soporte en operaciones tales como la generación de registros en otras tablas y la manipulación de cadenas en escenarios en los que tradicionalmente se recurre a algún tipo de estructura repetitiva, como pueda ser un cursor o bucle WHILE.

A lo largo de las dos entregas de que consta este artículo abordaremos algunas de las principales técnicas utilizadas en la creación de este tipo de tabla, dejando para un próximo artículo su aplicación práctica en diversos escenarios de uso.

 

Bucle WHILE

Si se trata de rellenar una tabla con una serie de números consecutivos, es probable que lo primero en lo que pensemos sea en utilizar un bucle WHILE, que se ejecute tantas veces como números necesitemos añadir a la tabla, de una forma parecida a la que mostramos a continuación (nótese que a lo largo de los ejemplos incluimos también diversas sentencias para evaluar los tiempos de ejecución de las técnicas presentadas y comparar así su rendimiento).

 

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

CREATE TABLE Numeros
(
            NumeroID int NOT NULL
);

DECLARE @nContador AS INT;
DECLARE @dtInicio AS DATETIME;
DECLARE @dtFin AS DATETIME;

SET @dtInicio = CURRENT_TIMESTAMP;
SET @nContador = 1;

WHILE(@nContador <= 30000)
BEGIN
            INSERT INTO Numeros (NumeroID)
            VALUES (@nContador);

            SET @nContador += 1; 
END

SET @dtFin = CURRENT_TIMESTAMP;

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

ALTER TABLE Numeros
ADD CONSTRAINT PK_Numeros
PRIMARY KEY (NumeroID)

 

El anterior bloque de código utiliza el operador compuesto +=  para incrementar la variable @nContador. Si nuestra versión de SQL Server no soporta dicho operador, emplearemos la construcción clásica: 

SET @nContador = @nContador + 1;

Aunque el código del ejemplo funciona correctamente y cumple su cometido de rellenar la tabla Numeros con un conjunto de registros cuyos valores están comprendidos entre 1 y 30000, no estamos empleando la potencia de la programación basada en conjuntos, tal y como Jeff Moden, uno de los grandes especialistas en SQL Server, explica en "The 'Numbers' or 'Tally' Table: What it is and how it replaces a loop" [1]. Es por ello que en los siguientes apartados veremos cómo podemos corregir dicha situación.

 

Expresión Común de Tabla (Common Table Expression - CTE) recursiva

Una expresión común de tabla (CTE a partir de ahora) según detalla Itzik Ben-Gan en su obra "Microsoft SQL Server 2012 T-SQL Fundamentals" [2], es una construcción del lenguaje SQL, que según la consulta a abordar, nos permite crear expresiones de tabla organizando el código de la consulta de una forma más limpia y legible que mediante el uso de tablas derivadas o subconsultas.

Para crear una CTE utilizaremos la palabra clave WITH, seguida del nombre que damos a la CTE, la partícula AS, y entre paréntesis, el código de la consulta propio de la CTE o consulta interna (inner query). A continuación escribiremos el código de la consulta externa (outer query) que se ejecuta contra la CTE. Todo ello lo vemos seguidamente en un ejemplo que hace un cruce entre el resultado de una CTE basada en la tabla DimCustomer y la tabla FactInternetSales, de la base de datos AdventureWorksDW2012.

 

 

Esta explicación introductoria sobre CTEs viene motivada por el hecho de que es un elemento del lenguaje SQL dotado de una funcionalidad recursiva, que vamos a aplicar como otro de los medios para la creación de tablas numéricas; de ahí que para aquellos lectores que todavía no han trabajado con CTEs resulte un requisito imprescindible conocer su funcionamiento aunque sea de un modo somero.

Para construir una CTE recursiva, que nos evite el uso de un bucle WHILE, debemos utilizar al menos dos consultas. La primera, denominada elemento ancla, que se ejecuta una sola vez, devolverá un resultado simple que será utilizado como valor inicial por la segunda consulta, denominada elemento recursivo, que hace que la CTE se llame a sí misma, consiguiendo de esta manera la capacidad recursiva

El límite de llamadas recursivas lo establecemos en el elemento recursivo mediante una cláusula WHERE, como muestra el siguiente código, que genera un conjunto de números del 1 al 50.

 

En el anterior ejemplo, al llamar a la CTE desde la consulta externa (1) se ejecuta      una única vez el elemento ancla (2) de la CTE, que devuelve el valor 1 con el alias de columna Num.

A continuación se produce, dentro de la CTE, una llamada a sí misma desde el elemento recursivo (3). Como la columna Num ya tiene el valor 1, fruto de la primera ejecución contra el elemento ancla, se suma 1 a dicha columna, por lo que ahora devuelve el valor 2.

Las sucesivas llamadas continúan ejecutando el elemento recursivo (3), incrementando la columna Num, hasta que se cumple la condición de la cláusula WHERE.

La cantidad de llamadas recursivas que pueden hacerse por defecto es de 100. No obstante, podemos cambiar este valor mediante el modificador OPTION (MAXRECURSION NumLlamadas), donde NumLlamadas representa la cantidad de ejecuciones recursivas que puede realizar la CTE, estando su valor comprendido entre 0 y 32767.

En el siguiente código, gracias al uso de dicho modificador, obtenemos una lista de números comprendida entre los valores 1 y 5000.

 

WITH cteContador AS
(
            SELECT 1 AS Num

            UNION ALL

            SELECT Num + 1
            FROM cteContador
            WHERE Num < 5000
)
SELECT Num
FROM cteContador
OPTION (MAXRECURSION 5000);

 

En caso de necesitar un número de llamadas recursivas mayor que el límite de 32767, utilizaremos cero como parámetro de MAXRECURSION.

 

WITH cteContador AS
(
            SELECT 1 AS Num

            UNION ALL

            SELECT Num + 1
            FROM cteContador
            WHERE Num < 39500
)
SELECT Num
FROM cteContador
OPTION (MAXRECURSION 0);

 

Una vez visto el funcionamiento de las CTEs recursivas volvemos a retomar nuestro objetivo inicial sobre creación de tablas numéricas, utilizando la técnica que acabamos de explicar, para lo cual emplearemos el siguiente código.

 

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 cteContador AS
(
            SELECT 1 AS Num

            UNION ALL     

            SELECT Num + 1
            FROM cteContador
            WHERE Num < 30000
)
INSERT INTO Numeros (NumeroID)
SELECT Num
FROM cteContador
OPTION (MAXRECURSION 30000);

SET @dtFin = CURRENT_TIMESTAMP;

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

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

 

Si comparamos los tiempos de ejecución, comprobaremos que este último método para cargar la tabla numérica es, con diferencia, mucho más rápido que el utilizado con el bucle WHILE, por lo que aparentemente parece una solución más recomendable, ya que esta mayor velocidad en su funcionamiento puede llevarnos a pensar que también proporciona un mejor rendimiento en ejecución.

Sin embargo, como acabamos de decir, esta ventaja es sólo aparente, ya que el análisis de la mecánica interna utilizada por una CTE recursiva revela que su funcionamiento no es tan óptimo como podría pensarse en un principio, tal y como explica detalladamente Jeff Moden en su interesante artículo "Hidden RBAR: Counting with Recursive CTE's" [3].

Ha llegado, por lo tanto, la hora de mostrar una técnica para crear una tabla numérica, que ofrezca una velocidad todavía mayor que los ejemplos anteriormente explicados, y un rendimiento en ejecución que sea realmente bueno.

 

Pseudo Cursor

Según se expone en [3], un pseudo cursor es un bucle basado en un conjunto de resultados obtenidos a partir de una consulta contra una tabla. También podría describirse como el producto de una consulta contra una tabla o combinación de tablas, pero utilizando solamente la presencia de filas de la tabla(s) involucrada(s) en la consulta. Puesto que es probable que estas explicaciones no resulten muy reveladoras para el lector, veamos lo que es un pseudo cursor a través de un ejemplo.

Si ejecutamos la siguiente consulta en alguna de nuestras bases de datos.

 

SELECT name FROM sys.all_columns;

 

Nos devolverá un conjunto de resultados con los nombres de las columnas de las tablas que componen la base de datos, en un número que variará dependiendo de la cantidad de tablas existentes. En mi caso obtengo 8453 filas como puede apreciarse en la siguiente imagen.

 

Supongamos ahora que aprovechamos este conjunto de resultados para generar, además, una nueva columna con numeración secuencial mediante la función ROW_NUMBER().

 

SELECT
name,
ROW_NUMBER() OVER(ORDER BY name) AS NumeroFila
FROM sys.all_columns;

 

 

Como el objetivo que realmente perseguimos es la creación, única y exclusivamente, del intervalo de números, en el anterior bloque de código sobraría la columna name de la tabla sys.all_columns, pero si quitamos de la consulta dicha columna nos encontraremos con un error en ROW_NUMBER(), ya que la cláusula OVER(ORDER BY <cláusula>) es obligatoria.

 

Sin embargo, si en la cláusula ORDER BY empleamos la expresión SELECT NULL, conseguiremos un doble propósito: por un lado solucionaremos el error que se producía en ROW_NUMBER(); y por otro, lograremos nuestro objetivo de crear el rango de valores numéricos.

 

SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NumeroFila 
FROM sys.all_columns;

 

La clave reside en la presencia de filas de la tabla sys.all_columns (obsérvese que no se hace uso de ninguna de las columnas de la tabla) que forma parte de la consulta, y que aquí actúa como fuente de datos en la generación de las filas para el intervalo, mientras que ROW_NUMBER() proporciona la lógica de la asignación del número correspondiente a cada fila. 

Este funcionamiento combinado de ambos elementos: tabla y ROW_NUMBER(), para obtener un conjunto secuencial de números, que tradicionalmente requeriría el uso de un cursor o bucle WHILE, es lo que recibe el nombre de pseudo cursor. Si aplicamos esta técnica a la creación de la tabla numérica, nuestro código quedaría como vemos a continuación.

 

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

CREATE TABLE Numeros
(
            NumeroID int NOT NULL
);

INSERT INTO Numeros (NumeroID)
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM sys.all_columns;

 

Aquí nos encontramos con una pequeña contrariedad: dado que la lista de números está limitada al número de filas de la tabla fuente (sys.all_columns en este caso) ¿qué podemos hacer si necesitamos crear una tabla numérica mayor? Una táctica sería recurrir al empleo de una tabla fuente con un mayor número de registros, pero si la tabla numérica a crear necesita un rango de valores muy elevado, puede que no dispongamos de una tabla fuente lo bastante grande, por lo que una técnica más recomendable consiste en hacer una CROSS JOIN de la tabla fuente consigo misma de la siguiente manera.

 

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;

INSERT INTO Numeros (NumeroID)
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Num
FROM sys.all_columns AS tbl1
CROSS JOIN sys.all_columns AS tbl2
ORDER BY Num
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);

 

Dado que el producto cartesiano de la tabla sys.all_columns al hacer la CROSS JOIN proporciona un número de filas abundante (71.453.209 en mi caso), y que gracias a los modificadores OFFSET...FETCH de la cláusula ORDER BY controlamos la cantidad de registros generados para la tabla numérica, se podría decir que esta técnica abarca prácticamente todas las situaciones que podamos necesitar.

Si la versión de SQL Server con la que trabajamos no soporta el uso de OFFSET...FETCH, emplearemos la cláusula TOP para limitar el número de filas devueltas por la consulta.


..........
..........
INSERT INTO Numeros (NumeroID)
SELECT TOP (30000)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM sys.all_columns AS tbl1
CROSS JOIN sys.all_columns AS tbl2;
..........
..........

Y llegados a este punto finalizamos la primera parte de este artículo en donde hemos realizado una introducción a las tablas numéricas auxiliares tanto en el aspecto conceptual como en su forma de creación. En la segunda entrega continuaremos profundizando en las técnicas de construcción de este tipo de tabla, las cuales pueden resultar de gran ayuda al desarrollador de bases de datos para resolver problemas de muy diverso tipo.

 

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/

 

Durante la instalación de SQL Server 2014, si nuestro sistema operativo es Windows 8/8.1, y no tenemos activado .NET Framework 3.5, al llegar al paso del asistente en el que se realiza la comprobación de las características seleccionadas para instalar, es probable que nos encontremos con un error que nos avisa de la falta de .NET Framework 3.5 en nuestro sistema, y que debemos instalarlo si queremos completar con éxito la instalación de esta versión del gestor de bases de datos.

  

 

Podemos resolver este problema de distinta forma en función de nuestra disponibilidad de conexión a Internet. Si esta es viable, accederemos al Panel de control de Windows, y dentro del apartado Programas, sección Programas y características, haremos clic en la opción Activar o desactivar las características de Windows.

 

Esta acción nos mostrará el cuadro de diálogo Características de Windows, en el que seleccionaremos la opción .NET Framework 3.5 (incluye .NET 2.0 y 3.0) y haremos clic en su botón Aceptar.

 

Sin embargo, como me sucedió recientemente, puede ocurrir que en el momento de realizar esta operación no dispongamos de acceso a Internet, ya que en mi caso necesitaba instalar SQL Server 2014 en una máquina virtual, y por diversas circunstancias no contaba en ese momento con conexión a la Red.

Si nos encontramos en dicha situación no debemos preocuparnos, ya que también es posible activar .NET Framework 3.5. Se trata de un proceso un poco más laborioso, pero que no reviste mayor complejidad.

En primer lugar deberemos introducir el DVD de instalación de Windows 8/8.1 en el lector de nuestra máquina, o en el caso de contar con una imagen ISO de dicho DVD, montaremos el archivo ISO correspondiente.

A continuación, en la unidad C: del equipo, crearemos una carpeta (a la que en este ejemplo hemos llamado SourcesNetFx) en la que copiaremos el contenido de la ruta \sources\sxs del disco de Windows 8/8.1, desde una ventana de símbolo de sistema abierta con privilegios de administrador.

  

Seguidamente, desde la misma ventana de símbolo de sistema, utilizaremos la herramienta DISM (Deployment Image Servicing and Management) para realizar la activación de .NET Framework 3.5 empleando el siguiente comando (sustituya el lector las letras de unidad y rutas por las que esté utilizando en su caso).

C:\>DISM /Online /Enable-Feature /FeatureName:NetFX3 /All /LimitAccess /Source:C:SourcesNetFx

 

  

Finalizada la ejecución del comando DISM, .NET Framework 3.5 ya estará activado en nuestra máquina, lo que nos permitirá continuar con la instalación de SQL Server 2014 hasta completarla adecuadamente.

 

A continuación se adjuntan varios enlaces como referencia de la operación explicada en el presente artículo. Esperamos que resulte de utilidad para todos aquellos lectores que puedan encontrarse con esta misma situación.

Instalar .NET Framework 3.5 en Windows 8.1 offline

MSDN Instalar .NET Framework 3.5 en Windows 8 u 8.1

SQL Server 2014. Descarga de evaluación

Publicado por Luis Miguel Blanco | con no comments
Archivado en: ,

Dentro de las características de Inteligencia de Negocio (BI) incluidas en Excel para el análisis de información, nos encontramos con la posibilidad de tener varias tablas dinámicas conectadas a diferentes cubos de datos, tanto en una misma hoja como repartidas en diversas hojas de un mismo archivo, permitiéndonos de esta forma disponer de múltiples vistas sobre nuestro sistema de información de forma centralizada.

Cuando los cubos que están conectados a dichas tablas dinámicas se procesan, existe la posibilidad de actualizar de forma independiente una de estas tablas mediante la opción Actualizar, perteneciente al grupo de opciones Datos, de la pestaña Analizar en la cinta de opciones de Excel; o bien usar la opción Actualizar todo, en el mismo grupo, que actualizará todas las tablas dinámicas existentes en el archivo Excel.

Sin embargo puede haber situaciones en las que solamente queramos actualizar algunas de las tablas dinámicas que componen nuestro archivo Excel, por lo que resultaría de gran ayuda disponer de un mecanismo que nos permitiera seleccionar las tablas y actualizarlas en un solo paso, evitándonos el tedioso trabajo de tener que recorrer el archivo hoja por hoja para actualizar independientemente cada tabla.

La finalidad del presente artículo será precisamente esa: el desarrollo mediante VBA y el sistema de macros de Excel de una pequeña utilidad, que ofrezca al usuario una lista de las tablas dinámicas existentes en el archivo Excel, permitiéndole seleccionar las que quiere actualizar, y realizando dicha operación de actualización en un único paso.

Para ello necesitamos en primer lugar algunos cubos de datos con los que realizar esta prueba, así que emplearemos las bases de datos AdventureWorksDW2012 y ContosoRetailDW, disponibles en CodePlex y el centro de descargas de Microsoft, creando con cada una de ellas un proyecto en SQL Server Data Tools de tipo Analysis Services Multidimensional, a los que daremos los nombres AdventureWorks y ContosoRetail respectivamente. Cada uno de estos proyectos contendrá un par de sencillos cubos de datos, tal y como muestra la siguiente figura.

 

A continuación crearemos un nuevo archivo Excel, al que añadiremos varias tablas dinámicas repartidas en diferentes hojas, conectadas a los cubos que hemos desarrollado. Este archivo, al que daremos el nombre ActualizarTablasDinamicas.xlsm, lo guardaremos con formato de Libro de Excel habilitado para macros.

Para identificar adecuadamente las tablas dinámicas que necesitemos actualizar en el proceso que vamos a desarrollar, asignaremos a cada una de ellas un nombre de la siguiente manera: posicionados en una tabla dinámica, en la pestaña de nivel superior Herramientas de tabla dinámica de la cinta de opciones, seleccionaremos la pestaña Analizar y desplegaremos la opción Tabla dinámica (primera opción a la izquierda en esta pestaña) que mostrará un cuadro de texto en el que escribiremos el nombre para dicha tabla, como vemos en la siguiente figura.

 

Seguidamente, en la pestaña Desarrollador de la cinta de opciones, dentro del grupo Código, haremos clic en la opción Visual Basic, entrando en el entorno de desarrollo de VBA.

 

Si la pestaña Desarrollador no estuviera visible tendremos que ir a la pestaña Archivo y hacer clic en Opciones. En la ventana Opciones de Excel haremos clic en Personalizar cinta de opciones, y pasaremos la pestaña Desarrollador desde la columna Comandos disponibles en hasta la columna Personalizar la cinta de opciones, lo que hará visible a esta pestaña.

 

Volviendo a la ventana principal de VBA, seleccionaremos la opción de menú Insertar | UserForm, para añadir a nuestro archivo Excel un formulario al que daremos el nombre frmActualizarTablasDinamicas, y en el que agregaremos los controles que vemos en la siguiente figura.

 

Para iniciar este formulario añadiremos ahora un módulo mediante la opción de menú Insertar | Módulo, en cuyo editor de código escribiremos el siguiente procedimiento.

 

Sub ActualizarTablasDinamicas()

Dim ofrmActualizarTablasDinamicas As frmActualizarTablasDinamicas
Set ofrmActualizarTablasDinamicas = New frmActualizarTablasDinamicas
Load ofrmActualizarTablasDinamicas
ofrmActualizarTablasDinamicas.Show

End Sub

 

Este procedimiento quedará guardado como una macro del archivo Excel, que ejecutaremos haciendo clic en la opción Macros, disponible tanto en la pestaña Desarrollador como Vista de la cinta de opciones.

 

La siguiente figura muestra el formulario en ejecución. 

 

Aunque nuestro formulario ya dispone de interfaz de usuario, todavía carece de la funcionalidad necesaria para realizar las tareas que necesitamos, por lo que entraremos en su editor de código y nos situaremos en el evento Initialize del objeto UserForm, que representa al formulario, y que se produce al iniciarse su ejecución, escribiendo el siguiente bloque de código.

 

Private Sub UserForm_Initialize()

Dim oSheet As Object
Dim oPivotTable As PivotTable

Me.lstTablasDinamicas.SpecialEffect = fmSpecialEffectSunken
Me.lstTablasDinamicas.MultiSelect = fmMultiSelectMulti
Me.lstTablasDinamicas.ListStyle = fmListStyleOption

For Each oSheet In ActiveWorkbook.Sheets
    oSheet.Select

    For Each oPivotTable In ActiveSheet.PivotTables
        Me.lstTablasDinamicas.AddItem (oSheet.Name & "-" & oPivotTable.Name)
    Next
Next

End Sub

 

Como vemos en el código anterior, después de configurar algunas de las propiedades del control ListBox utilizamos un bucle For Each para recorrer las hojas del archivo, y dentro de este, otro bucle del mismo tipo para obtener los nombres de las tablas dinámicas que pueda haber, con la finalidad de rellenar el ListBox con el nombre de la hoja y tabla dinámica.

 

Al hacer clic en el botón Actualizar, ejecutaremos el siguiente bloque de código para el evento Click de dicho botón, en el que recorreremos los elementos contenidos en el ListBox, y en aquellos en los que su casilla esté marcada, accederemos, a través de la colección PivotTables del objeto ActiveSheet, a la tabla dinámica correspondiente para actualizarla mediante el método Refresh, perteneciente a la propiedad PivotCache de la tabla dinámica.

 

Private Sub cmdActualizar_Click()

Dim nIndice As Integer
Dim sElementoLista As String
Dim aHojaTabla() As String
Dim sHoja As String
Dim sTabla As String

For nIndice = 0 To Me.lstTablasDinamicas.ListCount - 1
    If Me.lstTablasDinamicas.Selected(nIndice) Then
        sElementoLista = Me.lstTablasDinamicas.List(nIndice)
        aHojaTabla = Split(sElementoLista, "-")
        sHoja = aHojaTabla(0)
        sTabla = aHojaTabla(1)        

        ActiveWorkbook.Sheets(sHoja).Select
        ActiveSheet.PivotTables(sTabla).PivotCache.Refresh
    End If
Next

MsgBox "Actualización completada"

Unload Me

End Sub

 

Si realizamos cambios en las tablas de hechos de los cubos desarrollados para este ejemplo, como pueda ser la inserción de nuevas filas, y después de procesarlos ejecutamos el formulario, marcando algunas de las casillas del ListBox; al hacer clic en el botón Actualizar observaremos cómo dichas tablas seleccionadas actualizan sus valores.

 

En el caso de que no queramos realizar la actualización, haciendo clic en el botón Cancelar cerraremos el formulario.

 

Private Sub cmdCancelar_Click()

Unload Me

End Sub

 

Y de esta forma concluimos este pequeño truco, que esperamos pueda resultar de utilidad para todos aquellos que necesiten trabajar con diferentes vistas de cubos de datos dentro en un mismo archivo Excel.

La obtención de detalles (drillthrough) es una característica de los cubos de datos multidimensionales consistente en proporcionar a un usuario acceso a datos de la tabla de hechos que no se encuentran visibles a través de los canales habituales utilizados en la consulta del cubo: dimensiones y medidas. De este modo, cierta información del cubo que pueda considerarse reservada, solamente estará disponible para un grupo de usuarios limitado.

En el presente artículo vamos a explicar los pasos necesarios para crear una acción de obtención de detalles (drillthrough action) en un cubo desarrollado con SQL Server 2012, utilizando como base de datos AdventureWorksDW2012, la cual modificaremos según las indicaciones del siguiente apartado. 

 

Preparación de la base de datos

En primer lugar crearemos una nueva tabla de hechos con el nombre FactInternetSalesDetalles a partir de la tabla FactInternetSales.

 

SELECT *
INTO FactInternetSalesDetalles
FROM FactInternetSales; 

 

La tabla FactInternetSales original tiene una clave primaria formada por las columnas SalesOrderNumber y SalesOrderLineNumber, pero al objeto de simplificar nuestro ejemplo, en la nueva tabla FactInternetSalesDetalles añadiremos una columna con el nombre OrderNumberID, a la que asignaremos la concatenación de las dos columnas antes mencionadas, convirtiéndola en clave primaria de la tabla. 

 

ALTER TABLE FactInternetSalesDetalles
ADD OrderNumberID nvarchar(25) NULL;

UPDATE FactInternetSalesDetalles
SET OrderNumberID = SalesOrderNumber + CAST(SalesOrderLineNumber AS nvarchar(1));

ALTER TABLE FactInternetSalesDetalles
ALTER COLUMN OrderNumberID nvarchar(25) NOT NULL;

ALTER TABLE FactInternetSalesDetalles
ADD CONSTRAINT PK_FactInternetSalesDetalles PRIMARY KEY CLUSTERED (OrderNumberID); 

 

Supongamos que la tabla de hechos del cubo que vamos a construir necesita contener ciertos datos del comprador (nombre, apellido y fecha de nacimiento), considerándose éstos información sensible que no debe estar al alcance de todos los usuarios que consulten el cubo.

Por este motivo añadiremos a continuación tres nuevas columnas a esta tabla, que contendrán información relativa al cliente que ha realizado la compra. 

 

ALTER TABLE FactInternetSalesDetalles
ADD
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
BirthDate date NULL;           

                  

Mediante la siguiente sentencia asignaremos valores a estas columnas cruzando con la tabla DimCustomer, terminando por el momento con las operaciones sobre la base de datos relacional y pasando a la etapa de creación del cubo.

 

UPDATE FactInternetSalesDetalles
SET
FirstName = DimCustomer.FirstName,
LastName = DimCustomer.LastName,
BirthDate = DimCustomer.BirthDate
FROM DimCustomer
WHERE FactInternetSalesDetalles.CustomerKey = DimCustomer.CustomerKey;

 

Creación del cubo

La siguiente fase de nuestro ejemplo consistirá en crear un cubo con un modelo de datos (Data Source View) compuesto por la tabla FactInternetSalesDetalles como tabla de hechos, y las tablas DimDate, DimCurrency y DimPromotion como tablas de dimensiones. Sugerimos al lector la consulta del artículo Cubos de datos en SQL Server 2008 Analysis Services, para cualquier duda acerca de los aspectos básicos relacionados con la creación de un cubo de datos.

Iniciaremos SQL Server Data Tools y crearemos un nuevo proyecto de tipo Analysis Services Multidimensional con el nombre ObtencionDetalles, en el que definiremos el modelo de datos que acabamos de describir.

 

 

Las dimensiones tomarán los nombres FechaVenta (tabla DimDate), Moneda (DimCurrency) y Promo (DimPromotion).

 

Tanto la dimensión Moneda como Promo serán dimensiones sencillas de un único atributo, mientras que FechaVenta tendrá una jerarquía del tipo Año-Mes-Día, construida utilizando las columnas CalendarYear, MonthNumberOfYear y DayNumberOfMonth.

 

 

 

El cubo de datos, al que daremos el nombre VentasInternet, tendrá una medida llamada ImporteVentas, que realizará la suma de la columna SalesAmount de la tabla de hechos.

 

Una vez procesado el cubo y sus dimensiones, nos conectaremos desde Excel para comprobar que su funcionamiento sea correcto.

 

Como vemos en la anterior imagen, la celda seleccionada en la tabla dinámica muestra el importe de las ventas por la fecha en que se realizaron y la moneda de pago utilizada, todo ello filtrado por una campaña o promoción comercial.

Pero en algunos casos nos gustaría saber, además, a qué clientes corresponden dichas ventas, y aunque bien es cierto que podríamos resolver esta cuestión con una dimensión de clientes, como ya hemos explicado al principio del artículo, en este escenario se trata de un tipo de información especial, por lo que aquí es donde entra en liza la obtención de detalles, elemento mediante el que averiguaremos los compradores relacionados con una determinada cifra de ventas. 

 

Configurar la obtención de detalles

Para crear una acción de obtención de detalles necesitamos una dimensión que devuelva, de la tabla de hechos del cubo, aquellas columnas que deban formar parte del detalle, y que en nuestro ejemplo serían OrderNumberID, SalesAmount, FirstName, LastName y BirthDate.

Podríamos emplear directamente la tabla de hechos para crear dicha dimensión, pero en su lugar vamos a utilizar la siguiente vista sobre la tabla FactInternetSalesDetalles, que devuelve las mencionadas columnas.

 

CREATE VIEW vFactInternetSalesDetalles
AS
SELECT OrderNumberID, SalesAmount, FirstName, LastName, BirthDate
FROM FactInternetSalesDetalles;

 

La utilización de una vista para crear una dimensión orientada a servir como base del detalle de un cubo de datos resulta de gran utilidad, ya que simplifica su mantenimiento puesto que en teoría solamente contiene aquellas columnas que formarán parte del detalle.

A continuación añadiremos al modelo de datos de nuestro proyecto de Analysis Services esta vista, y la utilizaremos en la creación de una dimensión con el nombre DetalleVentas, cuyos atributos se corresponderán con sus columnas.

 

 

Como siguiente paso, abriremos el diseñador del cubo y agregaremos la dimensión recién creada. También asignaremos a su propiedad Visible el valor False, puesto que no es una dimensión orientada al usuario sino para uso interno del cubo.

 

Continuaremos haciendo clic en la pestaña Actions del diseñador del cubo para crear una nueva acción de obtención de detalle que configuraremos de la siguiente manera:

Haciendo clic en el icono New Drillthrough Action crearemos la acción de detalle. En el campo Name le asignaremos el nombre DetalleVentasClientes.

 

En el apartado Action Target, dentro de la lista desplegable Measure group members seleccionaremos el grupo de medida que asociaremos al detalle que estamos definiendo. Esto quiere decir que los registros de detalle estarán accesibles a las medidas incluidas en el grupo seleccionado, que en nuestro ejemplo será ImporteVentas, ya que es la única medida del grupo MedidasVentasInternet. 

 

Finalmente, en el apartado Drillthrough Columns, seleccionaremos la dimensión DetalleVentas, que proporcionará acceso a los registros de detalle de la tabla de hechos a través de la vista vFactInternetSalesDetalles, creada anteriormente en la base de datos; y también marcaremos aquellos atributos de la dimensión que se mostrarán al solicitar el detalle.

 

Consulta del detalle

Volviendo nuevamente a Excel, nos conectaremos otra vez al cubo mediante una tabla dinámica, situando la dimensión Moneda en columnas y FechaVenta en filas (expandiendo hasta el nivel Día).

Centrándonos en las ventas realizadas en dólares australianos correspondientes al día 10/01/2007, vemos que arrojan una cifra de 8.978,64. Para averiguar los clientes que han efectuado las compras resultantes de dicho importe, haremos clic derecho en esa celda y seleccionaremos la opción Acciones adicionales, que mostrará un submenú con las acciones definidas en el cubo para la medida ImporteValores con la que estamos trabajando, en este caso DetalleVentasClientes.

 

Como resultado de la selección de dicha opción, se creará una nueva hoja en el archivo Excel, formada por los registros de la vista vFactInternetSalesDetalles que nos proporciona la dimensión DetalleVentas, y que componen la cifra que estábamos visualizando en la tabla dinámica conectada al cubo de datos.

 

Todos los registros de detalle de esta nueva hoja se almacenan como tipo de dato texto, por lo que al intentar comprobar si la suma de la columna SalesAmount concuerda con la celda de la tabla dinámica seleccionada para detalle, no obtendremos el resultado esperado.

Para realizar esta comprobación, seleccionaremos los valores de la columna SalesAmount y los pegaremos en una nueva hoja del archivo Excel. Como además, el separador decimal usado es el punto, aplicaremos la siguiente fórmula a las celdas recién añadidas, para sustituir el punto por la coma y al mismo tiempo convertir el valor de texto a numérico.

=VALOR(SUSTITUIR(Celda;".";","))

Una vez realizada la conversión, aplicaremos la función SUMA() a la columna, obteniendo el mismo valor que el seleccionado en la tabla dinámica.

=SUMA(B1:B6)

 

 

 

Establecer los permisos de acceso al detalle

Puesto que con toda probabilidad somos administradores de la máquina en la que estamos desarrollando este ejemplo, no habremos tenido impedimentos al acceder al detalle del cubo explicado en el apartado anterior. Sin embargo, un usuario sin los adecuados privilegios no tendrá disponible de forma predeterminada esta posibilidad.

 

La forma de conceder este permiso pasa por añadir un rol al proyecto de Analysis Services del modo explicado en el artículo Establecer el acceso a cubos de datos para usuarios y grupos de usuarios. Una vez creado el rol y agregado al mismo los usuarios necesarios, en la pestaña Cubes desplegaremos la lista Local Cube/Drillthrough Access seleccionando el valor Drillthrough.

 

Después de guardar los cambios procesaremos el proyecto al completo, es decir, la base de datos multidimensional incluyendo dimensiones, cubo, etc., con lo cual, los usuarios incluidos en el rol ya tendrán acceso al detalle del cubo.

Y en este punto damos por concluido el presente artículo, en el que hemos abordado el modo de activar, en un cubo de datos multidimensional, el acceso al detalle de los registros que conforman el valor seleccionado de una medida. Espero que os resulte de utilidad.

En el anterior artículo sobre pirámides de población con PowerPivot, emplazábamos al lector a una próxima entrega dedicada a pirámides acumuladas, una variedad de pirámide demográfica, en la que cada segmento, además de indicar el sexo y rango de edad de la población a la que pertenece, permite aportar información adicional, como pueda ser el estado civil, nacionalidad, etc.

Al igual que en el resto de artículos dedicados a esta serie sobre pirámides de población, quisiera expresar mi agradecimiento a Ricard Gènova, demógrafo especializado en análisis estadístico sanitario, cuya ayuda y asistencia en todos aquellos conceptos sobre demografía en general y pirámides de población particular, han resultado claves en la elaboración de este artículo.

El objetivo del presente artículo, como acabamos de mencionar, será la construcción de una pirámide de población acumulada en Excel a partir de un modelo de datos de PowerPivot, para cuya creación partiremos, al igual que en el resto de artículos que giran alrededor de esta temática, de la base de datos PiramidePoblacion, cuyo proceso de creación se describe en el artículo sobre generación de datos demográficos, publicado anteriormente en este blog. No obstante, en esta ocasión utilizaremos SQL Server 2012 como motor de datos y Excel 2013 (Office 2013) para la capa de presentación, lo que nos permitirá disponer de las versiones más recientes de las herramientas para la construcción de la pirámide.

Entre las ventajas de utilizar Excel 2013 se encuentra el hecho de que PowerPivot ya viene integrado en el producto, aunque es probable que no esté habilitado. Para habilitarlo haremos primeramente clic en la pestaña Archivo, y a continuación en el botón Opciones de la columna izquierda.

  

En la ventana Opciones de Excel haremos clic en la opción Complementos, mostrándose la configuración de complementos en la parte derecha de la ventana.

 

En la lista desplegable Administrar seleccionaremos Complementos COM y haremos clic en el botón Ir. Una vez abierta la ventana de complementos, marcaremos la casilla Microsoft Office PowerPivot for Excel 2013.

 

Aceptando esta ventana se agregará la pestaña PowerPivot a la cinta de opciones de Excel.

 

 

La información a representar en cada segmento

El ejemplo que vamos a desarrollar consistirá en añadir a cada segmento-barra de la pirámide, la información sobre el estado civil de la población (soltero, casado, separado, etc.), de manera que el resultado sea similar al mostrado en la siguiente figura.

 

 

Ajustes sobre la estructura de la base de datos

Antes de comenzar la preparación del modelo de datos con PowerPivot debemos realizar algunas modificaciones en la base de datos PiramidePoblacion, encaminadas a incorporar los nuevos datos que usaremos para representar el estado civil de la población.

En primer lugar añadiremos la tabla EstadoCivil, que representará el catálogo de los distintos tipos de estado civil que puede tomar la población.

 

CREATE TABLE EstadoCivil
(
            EstadoCivil_ID int NOT NULL,
            EstadoCivil_DS varchar(50) NULL,
            CONSTRAINT PK_EstadoCivil PRIMARY KEY CLUSTERED (EstadoCivil_ID ASC)
)
GO

 

A continuación modificaremos la tabla Poblacion añadiendo el campo EstadoCivil_ID, que emplearemos para asignar el identificador de estado civil de cada registro de la población, y cuyo valor literal mostraremos a través de una relación con la tabla EstadoCivil en el modelo de PowerPivot.

 

ALTER TABLE Poblacion
ADD EstadoCivil_ID int NULL
GO

 

Actualizando la población con los datos de estado civil

La tabla que actuará como catálogo de los tipos de estado de civil contendrá los siguientes registros. Nótese que en la descripción del estado civil (campo EstadoCivil_DS) incluimos en primer lugar un número identificador, a efectos de conseguir más adelante una ordenación de estos valores en PowerPivot adaptada a nuestras necesidades.

 

INSERT INTO EstadoCivil
VALUES
(1,'1-Soltero'),
(2,'2-Casado'),
(3,'3-Separado/Divorciado'),
(4,'4-Viudo')

 

Puesto que no contamos con una fuente de datos que nos proporcione las cifras reales del estado civil de los individuos que forman la población, vamos a generar dicha información manualmente realizando una estimación, la cual refleje en la medida de lo  posible un escenario real.

De tal forma, a todos los registros de individuos cuya edad sea menor o igual a 20 años, les asignaremos el identificador de estado civil 1 (soltero).

 

UPDATE Poblacion
SET EstadoCivil_ID = 1
WHERE Edad_ID <= 20

 

Mientras que para el resto de registros de la tabla Poblacion, el valor del campo EstadoCivil_ID se asignará según las siguientes especificaciones. 

 

Traducido lo anterior a sentencias SQL, el código a ejecutar contra la tabla Poblacion será el siguiente. 

--///////////////////////////
--edad 25 a 70
WITH tblPoblacion AS
(
            SELECT TOP 1083719
            FILA_ID
            FROM Poblacion
            WHERE Edad_ID BETWEEN 25 AND 70
            AND EstadoCivil_ID IS NULL
            ORDER BY NEWID()
)
UPDATE Poblacion
SET EstadoCivil_ID = 1
FROM tblPoblacion
WHERE Poblacion.Fila_ID = tblPoblacion.Fila_ID;

WITH tblPoblacion AS
(
            SELECT TOP 2384182
            FILA_ID
            FROM Poblacion
            WHERE Edad_ID BETWEEN 25 AND 70
            AND EstadoCivil_ID IS NULL
            ORDER BY NEWID()
)
UPDATE Poblacion
SET EstadoCivil_ID = 2
FROM tblPoblacion
WHERE Poblacion.Fila_ID = tblPoblacion.Fila_ID;

WITH tblPoblacion AS
(
            SELECT TOP 606883
            FILA_ID
            FROM Poblacion
            WHERE Edad_ID BETWEEN 25 AND 70
            AND EstadoCivil_ID IS NULL
            ORDER BY NEWID()
)
UPDATE Poblacion
SET EstadoCivil_ID = 3
FROM tblPoblacion
WHERE Poblacion.Fila_ID = tblPoblacion.Fila_ID;

WITH tblPoblacion AS
(
            SELECT TOP 260093
            FILA_ID
            FROM Poblacion
            WHERE Edad_ID BETWEEN 25 AND 70
            AND EstadoCivil_ID IS NULL
            ORDER BY NEWID()
)
UPDATE Poblacion
SET EstadoCivil_ID = 4
FROM tblPoblacion
WHERE Poblacion.Fila_ID = tblPoblacion.Fila_ID;


--///////////////////////////
--edad > 70
WITH tblPoblacion AS
(
            SELECT TOP 9452
            FILA_ID
            FROM Poblacion
            WHERE Edad_ID > 70
            AND EstadoCivil_ID IS NULL
            ORDER BY NEWID()
)
UPDATE Poblacion
SET EstadoCivil_ID = 1
FROM tblPoblacion
WHERE Poblacion.Fila_ID = tblPoblacion.Fila_ID;

WITH tblPoblacion AS
(
            SELECT TOP 94519
            FILA_ID
            FROM Poblacion
            WHERE Edad_ID > 70
            AND EstadoCivil_ID IS NULL
            ORDER BY NEWID()
)
UPDATE Poblacion
SET EstadoCivil_ID = 2
FROM tblPoblacion
WHERE Poblacion.Fila_ID = tblPoblacion.Fila_ID;

WITH tblPoblacion AS
(
            SELECT TOP 37808
            FILA_ID
            FROM Poblacion
            WHERE Edad_ID > 70
            AND EstadoCivil_ID IS NULL
            ORDER BY NEWID()
)
UPDATE Poblacion
SET EstadoCivil_ID = 3
FROM tblPoblacion
WHERE Poblacion.Fila_ID = tblPoblacion.Fila_ID;

WITH tblPoblacion AS
(
            SELECT TOP 330817
            FILA_ID
            FROM Poblacion
            WHERE Edad_ID > 70
            AND EstadoCivil_ID IS NULL
            ORDER BY NEWID()
)
UPDATE Poblacion
SET EstadoCivil_ID = 4
FROM tblPoblacion
WHERE Poblacion.Fila_ID = tblPoblacion.Fila_ID;

 

Como acabamos de comprobar, la técnica empleada para asignar el estado civil a cada grupo de población consiste en crear una expresión de tabla común o CTE (Common Table Expression), que contenga los registros de un determinado rango de edad elegidos aleatoriamente. Para lograr esto último hacemos uso de la función NEWID. Por último, cruzamos la expresión de tabla con la tabla Poblacion por el campo Fila_ID; de esa manera asignaremos el identificador de estado civil correspondiente en el campo EstadoCivil_ID de la tabla Poblacion.

 

 

El modelo de datos en PowerPivot

Terminados los ajustes sobre la base de datos, el siguiente paso consistirá en crear un archivo Excel con el nombre PiramideAcumulada.xlsx e importar las tablas Edad, EstadoCivil, Poblacion, Sexo y Zona a un modelo de datos en PowerPivot, tal y como se explica en el artículo inicial sobre creación de pirámides con PowerPivot.

En líneas generales, el proceso que tenemos que realizar es idéntico, con la excepción de que en esta ocasión añadiremos la tabla EstadoCivil al grupo de tablas a importar. Respecto a las relaciones necesarias entre las tablas del modelo, tendremos que añadir una nueva entre las tablas Poblacion y EstadoCivil.

 

 

La tabla dinámica con la estructura de población

Al igual que en el artículo inicial sobre creación de pirámides, el siguiente paso consistirá en añadir la tabla dinámica a la hoja de Excel, crear las medidas y formatear las celdas; con la salvedad de que para este ejemplo agregaremos el campo EstadoCivil_DS, de la tabla EstadoCivil, al bloque de columnas, situándolo debajo del campo Sexo_DS.

 

 

 

El gráfico con la pirámide acumulada

Para crear el gráfico a partir de la tabla dinámica seguiremos los pasos que se explican aquí, aunque existen algunas diferencias en dicho proceso de creación que describiremos seguidamente.

En primer lugar, el gráfico a utilizar será de tipo Barra apilada.

 

Una vez añadido el gráfico, notaremos que Excel aplica automáticamente al mismo una composición de colores predeterminada, utilizando un color distinto para cada combinación de rango de edad, sexo y estado civil. Sin embargo, nuestros requerimientos en este aspecto pasan por emplear el mismo color para cada combinación de rango de edad y sexo, cambiándolo en función del estado civil.

Para conseguir este resultado haremos clic derecho en un grupo de segmentos de la pirámide pertenecientes a un tipo de estado civil, seleccionando la opción Dar formato a serie de datos.

 

Acto seguido, se desplegará el panel de formato para serie de datos. Aquí haremos clic en el icono de relleno, eligiendo el tipo Relleno sólido y el color a aplicar para este grupo de segmentos de la pirámide.

  

También remarcaremos los bordes de los segmentos desde este mismo panel, empleando la opción Línea sólida del apartado Borde y aplicando el color negro.

 

Repetiremos esta operación para cada grupo de segmentos de estado civil, hasta dejar el gráfico de la pirámide con un aspecto similar al que vemos en la siguiente figura.

 

El cambio de posición de la leyenda supone una labor más artesanal, ya que si hacemos clic derecho en la misma y elegimos la opción Formato de leyenda, al seleccionar la opción Superior como nueva posición, se moverán a dicha ubicación los indicadores de color de la combinación sexo/estado civil.

 

 

El objetivo a conseguir en este caso consiste en mostrar las etiquetas "Hombres" y "Mujeres" encima de la pirámide, mientras que las etiquetas correspondientes al estado civil deberán visualizarse a la derecha.

En primer lugar, combinando la utilidad Recortes de Windows, y otra aplicación que nos permita manipular imágenes (Paint, PowerPoint, etc.), compondremos una imagen con las combinaciones de color/estado civil.

 

A continuación, en la pestaña Analizar, perteneciente al grupo de pestañas Herramientas del Gráfico Dinámico, dentro del apartado Mostrar u ocultar, desplegaremos la lista Botones de campo seleccionando la opción Ocultar todos.

 

De igual modo haremos clic sobre la imagen de la leyenda y la eliminaremos, dejando solamente el gráfico con la pirámide de población.

 

Como siguiente paso, insertaremos la imagen con la leyenda de estado civil que hemos creado manualmente.

 

Para las etiquetas con los literales de sexo insertaremos en la parte superior del gráfico un cuadro de texto donde añadiremos la cadena de caracteres "Hombres Mujeres"; con alineación centrada y una anchura que abarque aproximadamente los extremos más sobresalientes de la pirámide.

 

Situándonos entre ambas palabras del cuadro de texto, insertaremos espacios en blanco hasta que los literales queden en dispuestos en ambos extremos, finalizando de esta manera la confección manual de la leyenda en nuestra pirámide.

 

 

Obtener diferentes perspectivas de la pirámide mediante segmentaciones

Al igual que hacíamos en el anterior artículo sobre creación de gráficos de pirámides poblacionales, en el ejemplo que estamos desarrollando vamos a insertar una segmentación basada en la tabla Zona, que nos permita observar, también en forma de pirámide, un subconjunto de la población total con la que estamos trabajando.

Para ello nos situaremos en la tabla dinámica y seleccionaremos la pestaña Analizar, perteneciente al grupo de pestañas Herramientas de Tabla Dinámica.

 

Aquí haremos clic en la opción Insertar Segmentación de datos, que abrirá una ventana con el mismo nombre, en la que seleccionaremos el campo Zona_DS de la tabla Zona. Si no encontráramos el campo en la pestaña Activo de esta ventana, lo seleccionaremos en la pestaña Todos.

 

Una vez añadida la segmentación, la situaremos junto al gráfico.

 

Manteniendo el foco sobre la segmentación observaremos que en la cinta de opciones de Excel aparece un grupo de pestañas con el nombre Herramientas de Segmentación de Datos, compuesto por una única pestaña llamada Opciones, que contiene todas las opciones para configurar la segmentación.

Para este ejemplo que estamos desarrollando, en el apartado Segmentación de datos haremos clic en la opción Configuración de Segmentación de datos, en cuya ventana de configuración asignaremos un nuevo título a la segmentación.

 

Seguidamente elegiremos un nuevo estilo en el apartado Estilos de Segmentación de datos y añadiremos una columna adicional a la segmentación en la opción Columnas, del apartado Botones, con lo que terminaremos su configuración.

 

A partir de este punto podemos seleccionar una o varias de las zonas incluidas en la segmentación, obteniendo de esta forma diferentes perspectivas de la población, tal y como podemos apreciar en la siguiente figura, en la que se muestran dos vistas de la pirámide, una con la población total y otra filtrada por una de las zonas.

 

Como podemos apreciar, la incorporación de segmentaciones en un modelo de PowerPivot constituye una potente herramienta de análisis, así como una variante del tradicional filtro sobre los datos existente en la tabla dinámica, que actuando tanto por separado como de forma combinada, proporcionan un amplio abanico de posibilidades a la hora de analizar un modelo de datos incluido en una hoja de Excel.

Y llegados a este punto damos por concluido el presente artículo, en el que hemos expuesto el proceso de creación de una pirámide de población acumulada utilizando un modelo de datos de PowerPivot como punto de partida de la información. Esperamos que os resulte de utilidad. 

Durante el desarrollo de un cubo de datos multidimensional con SQL Server 2012 (o bien a su finalización), resulta necesario establecer una adecuada política de permisos para determinar qué usuarios podrán acceder a la información que contiene.

Con tal fin tenemos a nuestra disposición los roles, elementos integrantes de una base de datos multidimensional, mediante los cuales podremos gestionar los diferentes modos de acceso a la misma, según el usuario o grupo de usuarios que necesiten ejecutar consultas contra el cubo.

Como ejemplo introductorio en el uso de roles en una base de datos multidimensional, supongamos que hemos creado desde SQL Server Data Tools (SSDT) un proyecto de tipo Analysis Services Multidimensional, conteniendo un cubo con el nombre VentasInternet, que emplea la base de datos AdventureWorksDW2012. Este cubo está formado por dos dimensiones: Fecha (tabla DimDate) y Moneda (tabla DimCurrency), y una medida basada en la suma de la columna SalesAmount de la tabla FactInternetSales.

 

Por otro lado tenemos una serie de usuarios en nuestro sistema que necesitarán acceder a este cubo.

 

Pero si alguno de estos usuarios intenta actualmente conectarse al cubo desde Excel utilizando la opción Obtener datos externos | De otras fuentes | Desde Analysis Services, de la pestaña Datos, se encontrará con un aviso que le impide acceder al cubo, debido a que no cuenta con los permisos necesarios.

 

 

Para solucionar este inconveniente, en el proyecto de análisis de SSDT en el que hemos creado el cubo, haremos clic derecho en el nodo Roles, seleccionando la opción New Role.

 

 

Una vez creado el rol, en la pestaña Membership haremos clic en el botón Add para añadir un usuario, al que concederemos permiso a través del cuadro de diálogo de selección de usuarios.

 

A continuación, en la pestaña Cubes abriremos la lista desplegable Access seleccionando el valor Read, lo que proporcionará acceso de lectura al cubo.

  

Después de volver a desplegar la solución en el servidor de Analysis Server, el usuario que acabamos de añadir al rol ya podrá conectarse al cubo.

En el caso de contar con un elevado número de usuarios, podemos gestionar más eficazmente las políticas de acceso empleando grupos de usuarios del sistema operativo, lo que facilitará las operaciones de mantenimiento de los permisos sobre los cubos de las diferentes bases de datos de análisis que compongan nuestro sistema de información.

Como muestra hemos creado el grupo ConsultaCubosVentas, integrado por los dos usuarios restantes que hemos utilizado en este ejemplo.

 

 

Ahora bien, cuando vayamos a agregar este grupo al rol de nuestra solución de análisis en la forma explicada anteriormente podemos llevarnos una pequeña sorpresa, ya que el cuadro de diálogo de selección de usuarios es posible que nos avise de que el grupo de usuarios que intentamos añadir no existe.

 

 

Para solucionar este problema, en el cuadro de diálogo haremos clic en el botón Tipos de objeto, y en la ventana de selección que se abrirá a continuación comprobaremos que el motivo de este comportamiento reside en que el objeto Grupos no está seleccionado.

 

 

Marcando la casilla Grupos ya podremos añadir el grupo de usuarios al rol, de forma que los permisos que establezcamos en el rol se aplicarán a todos los usuarios que compongan dicho grupo.

 

 

Esperamos que este pequeño truco resulte de ayuda a todos los lectores que necesiten establecer los permisos de acceso a los cubos desarrollados a través del modelo multidimensional de Analysis Services. Aunque los ejemplos expuestos en el presente artículo se han elaborado utilizando SQL Server 2012, resultan de igual aplicación en versiones anteriores de esta plataforma. 

(Artículo publicado previamente en el número 77 de dNM+)

Resulta un hecho innegable que en los últimos tiempos, el volumen de datos que las organizaciones deben manejar ha aumentado desmesuradamente. Analizar tal cantidad de datos, con el objetivo de tomar decisiones estratégicas se ha convertido en un auténtico problema. En el presente artículo realizaremos una introducción a los cubos de datos en SQL Server 2008 Analysis Services, una potente herramienta con la que podemos transformar ingentes cantidades de datos en información de utilidad.

La masificación de datos en los sistemas de información de una compañía, sin la adecuada organización ni estructuración, puede acarrear efectos negativos tales como lentitud en el análisis de su estado, o lo que es peor, la toma de decisiones estratégicas inadecuadas, ya que el hecho de disponer de millones de registros repartidos en múltiples orígenes de datos heterogéneos (bases de datos SQL Server, Access, archivos de texto plano, Excel, etc.), no tiene porque ser en todos los casos sinónimo de un sistema que proporcione información de calidad.

Para solventar este tipo de problemas, en SQL Server contamos, desde hace ya algunas versiones, con los Servicios de Análisis (SQL Server Analysis Services o SSAS) o herramientas de Inteligencia de Negocio (Business Intelligence o BI), cuyo exponente principal, el cubo de datos, permite generar información para analizar el estado de la empresa a partir del conjunto de sus fuentes de datos.

 

Aspectos conceptuales

Desde una perspectiva conceptual, un cubo de datos es una pieza más en el engranaje de un sistema de información denominado almacén de datos (data warehouse). El cubo está dotado de una maquinaria interna que le permite procesar elevados volúmenes de datos en un periodo relativamente corto de tiempo, y cuyo objetivo es siempre la obtención de un resultado numérico (importes de ventas, gastos, cantidad de productos vendidos, etc.). Estos resultados pueden cambiar en función de uno o varios filtros que apliquemos sobre el cubo. El tiempo de respuesta es mínimo gracias a que el motor de procesamiento del cubo realiza un cálculo previo de las posibles combinaciones de resultados que el usuario puede solicitar. A los diferentes resultados numéricos obtenidos se les denomina medidas, mientras que los elementos utilizados para organizar/filtrar la información reciben el nombre de dimensiones.

Representado gráficamente, un cubo de datos se mostraría como la forma geométrica de la cual toma su nombre, particionado horizontal y verticalmente en una serie de divisiones que dan lugar a múltiples celdas o casillas, las cuales identifican cada uno de los posibles resultados de las medidas, obtenidos por la intersección en cada celda de las dimensiones que conforman el cubo. La siguiente figura muestra dicha representación gráfica de un cubo, con información de ventas por productos, empleados y monedas. En los lados del cubo se sitúan las dimensiones, cuyo cruce produce los resultados numéricos en las celdas.

 

  

Observando la figura anterior, el lector puede pensar que el número de dimensiones en un cubo está limitado a las que podemos representar a través de dicha forma geométrica. Nada más lejos de la realidad, ya que un cubo puede soportar una elevada cantidad de dimensiones, que permiten cubrir sobradamente los requisitos de la información a obtener.

 

Elementos principales en un almacén de datos

Como hemos mencionado anteriormente, un cubo de datos es una de las piezas de una arquitectura más compleja: el almacén de datos, en cuyo proceso de creación están involucrados diversos componentes, que serán los encargados de tomar el dato original en bruto y pulirlo hasta convertirlo en información lista para su análisis. A continuación vemos un diagrama con las fases de este proceso de transformación.

 

Descrito este proceso a grandes rasgos, en primer lugar se realiza una operación de extracción, transformación y carga (Extract, Transform & Load o ETL) desde las fuentes de datos origen, situadas en el área operacional, a una base de datos que se encuentra en el área de integración, utilizando para ello paquetes de los Servicios de Integración (SQL Server Integration Services o SSIS), los cuales realizarán también tareas de depuración de datos.

A continuación pasaríamos a la fase de construcción del cubo, que desarrollaremos utilizando las herramientas de los servicios de análisis (SSAS). Finalmente, llegaremos a la fase de acceso a los cubos por parte de los usuarios finales, para lo que existen diversos productos tales como Reporting Services (SSRS), Excel, etc.

 

Elementos físicos. Tablas de hechos y dimensiones

A nivel físico, para construir un cubo de datos necesitamos una base de datos que contenga una tabla denominada tabla de hechos, cuya estructura estará formada por una serie de campos, denominados campos de medida, a partir de los cuales obtendremos los resultados numéricos del cubo; y por otro lado, un conjunto de campos, denominados campos de dimensión, que utilizaremos para unir con las tablas de dimensiones, a fin de poder obtener resultados filtrados por las diversas dimensiones de que conste el cubo.

El otro pilar fundamental para la creación de un cubo lo componen las tablas de dimensiones. Para cada dimensión o categoría de consulta/filtro que incorporemos a nuestro cubo necesitaremos una tabla, que uniremos con la tabla de hechos por un campo clave. Esta tabla de dimensiones actuará como catálogo de valores, también denominados atributos, que usaremos de forma independiente o combinados con otras dimensiones, para obtener resultados con un mayor grado de precisión.

 

Desarrollando un cubo de datos

Una vez explicadas las nociones básicas necesarias, entramos en la parte práctica del artículo, donde desarrollaremos nuestro propio cubo de datos. Centraremos todos nuestros esfuerzos exclusivamente en la creación del cubo, sin abordar aquí las operaciones de extracción, transformación y carga, que serían realizadas mediante paquetes SSIS, ya que estos últimos son aspectos que quedan fuera del ámbito de este artículo, quedando pendientes para una futura entrega.

En primer lugar, desde el menú de Windows iniciaremos SQL Server Business Intelligence Development Studio, cuyo acceso directo se encuentra en el grupo de programas Microsoft SQL Server 2008 R2. Se trata de una versión especial de Visual Studio preparada para desarrollar proyectos de BI, en cuyo diálogo inicial seleccionaremos como tipo de proyecto Analysis Services Project, al que daremos el nombre CuboDatosAdvWorks.

A continuación, haciendo clic derecho en el nodo Data Sources del Explorador de Soluciones, seleccionaremos la opción New Data Source, que abrirá el asistente para crear la fuente de datos del cubo, y que en nuestro caso será la base de datos de prueba AdventureWorksDW2008, cuya estructura ya se encuentra preparada para ser utilizada en el diseño de cubos de datos.

Dejaremos las opciones por defecto en el asistente hasta llegar al paso correspondiente a la conexión contra la fuente de datos, donde seleccionaremos AdventureWorksDW2008. Al llegar al paso final de este asistente veremos un resumen de la fuente de datos que hemos creado.

 

Nuestro siguiente paso consistirá en crear una vista de la fuente de datos, que nos permitirá, como su nombre indica, definir una visualización personalizada sobre la base de datos, incluyendo aquellas tablas que necesitemos para crear el cubo.

Lo que en este cubo de ejemplo vamos a medir es el importe de las ventas que los distribuidores de la compañía AdventureWorks han facturado, pudiendo consultar/filtrar los resultados por el tipo de moneda en que se ha realizado la venta, y el área geográfica a la que se ha enviado el pedido. Para ello utilizaremos la tabla FactResellerSales como tabla de hechos, siendo DimCurrency y DimSalesTerritory las tablas de dimensión.

Haciendo clic derecho en el nodo Data Source Views del Explorador de Soluciones seleccionaremos la opción New Data Source View, que abrirá un asistente en cuyo primer paso elegiremos la fuente de datos recién creada, y en el segundo las tablas que acabamos de mencionar.

 

 

Finalizado este asistente aparecerá su ventana de diseño, en la que vemos un diagrama de las tablas seleccionadas, con las relaciones existentes entre las mismas.

 

Creación de dimensiones. Dimensión básica

El siguiente paso consistirá en crear la dimensión que nos permitirá consultar/filtrar la información del cubo por el tipo de moneda con el que se realizó el pago del pedido. Haciendo clic derecho en el nodo Dimensions del Explorador de Soluciones seleccionaremos la opción New Dimension, iniciándose el consabido asistente, cuyo primer paso, Select Creation Method, dejaremos con la opción predeterminada Use an existing table. Al entrar en el paso Specify Source Information, tres listas desplegables nos permitirán configurar la información a obtener para la dimensión: con Main table elegiremos la tabla que utilizaremos en la dimensión: DimCurrency; con Key columns indicaremos la clave primaria; y finalmente, con Name column seleccionaremos el campo CurrencyName, que identificará el atributo a visualizar.

 

En el paso Select Dimension Attributes, el asistente nos ofrecerá Currency Key como atributo de la dimensión, el cual utilizaremos, pero cambiando su nombre a Currency. Un atributo es un campo, normal o calculado, perteneciente a la tabla de dimensión, que se mostrará como una etiqueta en cualquier lugar en el que la dimensión participe como parte de una consulta contra el cubo de datos.

 

Al llegar al último paso daremos el nombre Currency a la dimensión y finalizaremos el asistente, mostrándose el diseñador de dimensiones con la estructura que acabamos de crear. Observando las propiedades del atributo Currency, las más importantes son Name, que contiene el nombre que aparecerá en las consultas contra el cubo; KeyColumns, que contiene el campo clave de la tabla que se relacionará con la tabla de hechos; y NameColumn, que contiene el campo de la tabla que mostrará el valor del atributo.

 

En el caso de que necesitemos añadir más atributos a la dimensión, simplemente tendremos que arrastrar y soltar los campos desde la tabla del panel Data Source View hasta el panel Attributes de este diseñador.

Una vez creados todos los atributos procesaremos la dimensión haciendo clic en el botón Process de la barra de herramientas del diseñador, o mediante el menú de Visual Studio Build | Process. Completado el proceso de la dimensión, haremos clic en la pestaña Browser del diseñador, donde podremos examinar cómo ha quedado construida.

 

Creación de dimensiones. Dimensión jerárquica

Además de las dimensiones de un único nivel, como la que acabamos de ver en el apartado anterior, es posible crear dimensiones que agrupen los datos en varios niveles, lo que proporcionará una mayor capacidad de desagregación sobre la información del cubo cuando éste sea consultado a través de una dimensión de este tipo. A este elemento de una dimensión se le denomina jerarquía.

Pongamos como ejemplo la tabla DimSalesTerritory, incluida en el Data Source View de nuestro proyecto de ejemplo. En la misma podemos ver que la combinación de los campos SalesTerritoryGroup, SalesTerritoryCountry y SalesTerritoryRegion permite establecer varios niveles de agrupamiento para los datos.

 

Supongamos que necesitamos crear una dimensión basada en esta tabla que permita, partiendo del campo SalesTerritoryGroup, un efecto similar de "despliege" jerárquico por niveles.

Para ello, crearemos en primer lugar la dimensión utilizando el asistente en la forma explicada en el anterior apartado. El atributo seleccionado por defecto por el asistente será el correspondiente al campo SalesTerritoryKey, clave primaria de la tabla.

Una vez situados en el diseñador de dimensiones, arrastraremos desde la tabla del panel Data Source View los campos SalesTerritoryGroup, SalesTerritoryCountry y SalesTerritoryRegion, y los soltaremos en el panel Attributes de este mismo diseñador.

A continuación arrastraremos el atributo Sales Territory Group hasta el panel Hierarchies, lo cual creará una nueva jerarquía a la que cambiaremos el nombre predeterminado por Sales Territory. También depositaremos en esta jerarquía los atributos Sales Territory Country y Sales Territory Region, observando que junto al nombre de la jerarquía aparece un icono de advertencia que nos informa de que las relaciones entre los atributos de la jerarquía no están adecuadamente creadas, lo que puede afectar negativamente al proceso de la dimensión.

 

Para solucionar este inconveniente haremos clic en la pestaña Attribute Relationships, donde veremos las relaciones entre los atributos que automáticamente ha creado el diseñador.

 

Estas relaciones, sin embargo, no son válidas para nuestros propósitos, por lo que seleccionaremos las flechas del diagrama que las representan y las eliminaremos. Para crear las nuevas relaciones arrastraremos desde el atributo origen hasta el destino, hasta dejarlas tal y como apreciamos en la siguiente figura.

 

 

Antes de procesar la dimensión volveremos a la pestaña Dimension Structure para comprobar que la advertencia ha desaparecido. Por otro lado seleccionaremos todos los atributos del panel Attributes, asignando el valor False en su propiedad AttributeHierarchyVisible, con lo que conseguiremos que los atributos independientes no se muestren, ya que lo que nos interesa en este caso es explorar solamente la jerarquía. A continuación vemos el resultado de esta dimensión, con todos los elementos de la jerarquía expandidos.

 

Creación del cubo

Llegamos a la fase final en el desarrollo de nuestro proyecto de ejemplo: la construcción del cubo de datos. Empezaremos haciendo clic derecho en el nodo Cubes del Explorador de Soluciones, y seleccionando la opción Add Cube, lo que abrirá el asistente de creación, en el que dejaremos sus valores predeterminados hasta llegar al paso Select Measure Group Tables, que como su nombre indica, nos pide seleccionar la tabla que contendrá los campos que usaremos como medidas para el cubo, es decir, la tabla de hechos, que en este caso será FactResellerSales.

 

Haciendo clic en Next entraremos en el paso Select Measures, donde tendremos que seleccionar los campos que actuarán como medidas del cubo. El objetivo de este cubo consiste en averiguar el importe de las ventas realizadas por los distribuidores, por lo tanto, seleccionaremos solamente el campo SalesAmount.

 

El siguiente paso nos solicita la selección de las dimensiones que van a formar parte del cubo. Automáticamente se han detectado las dimensiones creadas por nosotros con anterioridad, las cuales ya se ofrecen seleccionadas por defecto.

 

A continuación, el asistente realiza una búsqueda en la tabla de hechos, por si algún campo pudiera ser susceptible de ser también tratado como una dimensión. Dado que no necesitamos esta característica, desmarcaremos la selección de la tabla de hechos como fuente de origen para la creación de dimensiones.

 

Y llegamos al paso final, donde daremos al cubo el nombre de VentasDistribuidores, finalizando así el asistente.

 

Como resultado obtendremos la pantalla correspondiente al diseñador de cubos, que muestra diversos elementos de importancia, tales como el panel de dimensiones, diagrama de tablas, medidas (Measures), etc.

 

Es precisamente en el panel de medidas donde aparece la medida que hemos seleccionado en el asistente, pero a la cual cambiaremos su nombre por Importe Ventas desde la ventana de propiedades. En esta misma ventana podemos observar el nombre, la función de agregado que se usa para calcular la medida, el campo de la tabla utilizado, cadena de formato, etc.

 

Para que la medida aparezca correctamente formateada, además de asignar la cadena de formato a su propiedad FormatString, en las propiedades del cubo tenemos que asignar el valor Spanish (Spain) a la propiedad Language.

Finalmente, antes de poder consultar el cubo, al igual que hicimos con las dimensiones, debemos procesarlo haciendo clic en el botón Process, que abrirá el cuadro de diálogo de procesamiento del cubo, en el que haremos clic en su botón Run.

 

Una vez que el cubo ha sido procesado, podemos consultar su contenido haciendo clic en la pestaña Browser. En el panel Measure Group expandiremos el nodo Measures hasta llegar a la medida Importe Ventas, que arrastraremos hasta la zona central del visualizador. A continuación arrastraremos la dimensión SalesTerritory hasta el margen izquierdo del visualizador. Podemos hacer clic derecho sobre esta dimensión, seleccionando la opción Expand Items, lo que producirá un despliegue de los elementos de la dimensión. Para terminar arrastraremos la dimensión Currency hasta el margen superior. Como resultado obtendremos una cuadrícula de datos en la que cada celda mostrará el valor de la medida para la intersección de las dimensiones situadas en las columnas y filas del visualizador de datos.

 

Conclusiones

En el presente artículo hemos realizado una introducción al desarrollo de cubos de datos con SQL Server 2008 Analysis Services, un componente de la familia SQL Server destinado a proporcionar soluciones de inteligencia de negocio con las que explotar el potencial de análisis que reside en los datos de las organizaciones. Las posibilidades y potencia de esta herramienta son enormes, y confiamos en que este artículo anime al lector a llevarlas a la práctica. 

Aquellos desarrolladores que migren a SQL Server 2012 desde una versión anterior de esta misma plataforma de administración de bases de datos, al comenzar a trabajar con el editor de consultas de SQL Server Management Studio (SSMS), pueden encontrarse con la desagradable sorpresa de que algunas de sus combinaciones o atajos de teclado favoritas, utilizadas para la ejecución de acciones habituales, no funcionan igual que en versiones previas del producto.

Este comportamiento, a priori extraño, del editor de consultas, viene motivado por una serie de cambios orientados hacia la integración entre SSMS y SQL Server Data Tools (SSDT), el entorno de desarrollo basado en Visual Studio 2010, que acompaña a SQL Server 2012.

A continuación, como ejemplo ilustrativo, vamos a mostrar dos de las acciones que se han visto afectadas por estos cambios, así como el modo de reasignar la combinación de teclado que originalmente tenían en versiones anteriores. Las dos acciones en cuestión corresponden a Ocultar (y mostrar) el panel de resultados de la consulta, y a Poner en minúsculas el código seleccionado en el editor.

 

Mi combinación de teclado ahora no funciona

Si después de ejecutar una consulta intentamos ocultar el panel de resultados pulsando la combinación de teclas Ctrl+R, nos encontraremos con que SSMS no reacciona a nuestra orden, mostrando además un mensaje en la esquina inferior izquierda, en el que podemos intuir que dicha combinación no es reconocida por esta aplicación, ya que se queda esperando una siguiente pulsación de teclado para ejecutar una posible acción.

  

Si por otra parte, queremos convertir el código seleccionado a minúsculas pulsando Ctrl+Mayús+L, nos llevaremos un buen susto, ya que esta combinación de teclas ahora borra el texto seleccionado en el editor. Por suerte, Ctrl+Z sigue funcionando como siempre y nos permitirá deshacer dicho borrado.

Este último cambio de funcionalidad en la combinación de teclas puede resultar especialmente incómodo, si para este caso concreto nos hemos acostumbrado a asociar la última tecla pulsada con la operación a realizar, es decir, Ctrl+Mayús+L(owercase) y Ctrl+Mayús+U(ppercase).

 

Devolviendo la combinación de teclas a su funcionalidad original

No debemos preocuparnos, sin embargo, por este nuevo funcionamiento en nuestros atajos de teclado preferidos, ya que es posible personalizarlos para que se ejecuten de la forma en que lo hacían en la versión con la que anteriormente trabajábamos.

Para ello, en primer lugar, localizaremos en el sistema de menús de SSMS la opción de menú correspondiente a la acción sobre la que queremos reasignar la combinación de teclado; que en el caso del panel de resultados es Ventana | Ocultar panel de resultados (o también Mostrar panel de resultados, según esté o no visible); y en el cambio a minúsculas es Editar | Avanzadas | Poner en minúsculas.

 

 

 

A continuación seleccionaremos la opción de menú Herramientas | Opciones, y en el cuadro de diálogo Opciones desplegaremos el nodo Entorno y haremos clic en Teclado.

En la lista de comandos que aparece en el lado derecho de esta ventana necesitamos encontrar aquellos que vamos a modificar. Para facilitar la búsqueda, en el cuadro de texto Mostrar los comandos que contengan, podemos introducir un valor aproximado  que realice las funciones de filtro. Si escribimos "ventana." se filtrará el contenido de  la lista a las opciones que dependen del menú Ventana. Seguidamente nos desplazaremos por la misma hasta encontrar el comando Ventana.Mostrarpanelderesultados. Una vez localizado, en la lista desplegable Usar nuevo método abreviado seleccionaremos Editor de consultas de SQL, y en el cuadro de texto Presionar teclas de método abreviado pulsaremos la combinación Ctrl+R.

Podemos ver la asignación de teclas actual en la lista desplegable El método abreviado lo utiliza actualmente, y una vez que estemos seguros del cambio que vamos a realizar haremos clic en el botón Asignar, de forma que Ctrl+R quedará asignada a partir de ese momento a la acción de mostrar/ocultar el panel de resultados.

 

Respecto a la nueva asignación de teclas para el cambio a minúsculas, procederemos de la misma forma que acabamos de explicar, pero usando el comando Editar.Ponerenminúsculas y la combinación de teclas Ctrl+Mayús+L.

 

Creación de una barra de herramientas personalizada

Otra técnica para acceder a las acciones que necesitemos ejecutar frecuentemente consiste en crear una nueva barra de herramientas y añadirle los botones para dichas acciones.

Empezaremos seleccionando la opción de menú Herramientas | Personalizar, y en la ventana Personalizar, pestaña Barras de herramientas, haremos clic en el botón Nuevo, creando así una nueva barra de herramientas vacía a la que daremos el nombre MisAcciones.

 

A continuación haremos clic en la pestaña Comandos de esta misma ventana, para proceder a asignar los comandos a nuestra nueva barra de herramientas. Después de hacer clic en el botón de opción Barra de herramientas, abriremos la lista desplegable situada a la derecha seleccionando MisAcciones.

Seguidamente haremos clic en el botón Agregar comando, y en la ventana del mismo nombre que se abrirá a continuación buscaremos el comando Mostrar panel de resultados. Una vez localizado haremos clic en Aceptar para añadirlo a nuestra barra de herramientas personalizada.

 

 

Haciendo clic en el botón Cerrar de la ventana Personalizar, la barra de herramientas quedará añadida junto al resto de barras de SSMS, de manera que a partir de ahora dispondremos de una forma adicional de ejecutar esta acción.

 

Podemos repetir este procedimiento tantas veces como necesitemos para componer una barra de herramientas que agrupe las acciones que más frecuentemente ejecutamos en una sesión de trabajo, evitando de esta forma tener que navegar reiteradamente por las opciones de menú de SSMS.

Y con esto damos por concluido el presente artículo, confiando en que los trucos que acabamos de exponer sirvan al lector para facilitar su trabajo diario con SSMS, permitiéndole ser más productivo con esta herramienta integrante de SQL Server 2012. Como información complementaria recomendamos al lector visitar el siguiente post del blog de Aaron Bertrand. 

La cláusula ROLLUP perteneciente a la función SUMMARIZE genera, en las columnas de agregación que especifiquemos, filas adicionales de resultados acumulados (totales parciales) para las columnas numéricas utilizadas en la consulta.

A modo de ejemplo, en la siguiente consulta utilizamos ROLLUP para la columna StoreType de la tabla DimStore, por lo que obtendremos, además de las cifras de venta por tipo de almacén, una última fila adicional con el total de las ventas.

 

EVALUATE
SUMMARIZE(
            'DimStore',
            ROLLUP('DimStore'[StoreType]),
            "Ventas", SUM('FactSales'[SalesAmount])
)

 

 

Partiendo de este resultado, supongamos ahora que necesitamos que los tipos de almacén aparezcan ordenados, pero manteniendo la fila de total al final. Si usamos la cláusula ORDER BY en la consulta, el resultado no será totalmente satisfactorio, ya que  se ordenarán los nombres, pero la fila de total quedará al principio.

 

EVALUATE
SUMMARIZE(
            'DimStore',
            ROLLUP('DimStore'[StoreType]),
            "Ventas", SUM('FactSales'[SalesAmount])
)
ORDER BY [StoreType]

 

 

Aplicando el modificador DESC a la cláusula ORDER BY solucionamos el problema solamente en parte, ya que conseguimos que el total vuelva a colocarse al final del conjunto de resultados, pero como es lógico, los nombres quedan ordenados en sentido descendente.

 

EVALUATE
SUMMARIZE(
            'DimStore',
            ROLLUP('DimStore'[StoreType]),
            "Ventas", SUM('FactSales'[SalesAmount])
)
ORDER BY [StoreType] DESC

 

 

Controlando la ubicación de los acumulados mediante ISSUBTOTAL

Si queremos ordenar los nombres en sentido ascendente, pero manteniendo la ubicación del total al final, debemos recurrir a ISSUBTOTAL, otra de las cláusulas de SUMMARIZE, la cual crea una columna de tipo lógico, en la que cada valor nos indica si la fila actual del conjunto de resultados corresponde a un acumulado (total parcial) o bien se trata de una fila normal de datos. El truco en este caso reside en aplicar una doble ordenación: primero por la columna de ISSUBTOTAL y a continuación por la de datos.

 

EVALUATE
SUMMARIZE(
            'DimStore',
            ROLLUP('DimStore'[StoreType]),
            "Ventas", SUM('FactSales'[SalesAmount]),  
             "Subtotal_StoreType", ISSUBTOTAL('DimStore'[StoreType])
)
ORDER BY [Subtotal_StoreType],[StoreType]

 

  

Ahora vamos a añadir la columna ContinentName, de la tabla DimGeography, como nueva columna de agrupación a la consulta. De esta manera, cada fila de acumulado resultante se corresponderá con el total de ventas de un continente para todos los tipos de almacén.

 

EVALUATE
SUMMARIZE(
            'DimStore',
            ROLLUP('DimStore'[StoreType]),
            'DimGeography'[ContinentName],
            "Ventas", SUM('FactSales'[SalesAmount])
)

 

  

La interpretación de los datos que obtenemos, no obstante, es poco amigable, por lo que nuevamente usaremos ISSUBTOTAL y ordenaremos las columnas tal y como muestra el siguiente bloque de código, consiguiendo que las filas de acumulado se sitúen proporcionando la información de un modo más legible.

 

EVALUATE
SUMMARIZE(
            'DimStore',
            ROLLUP('DimStore'[StoreType]),
            'DimGeography'[ContinentName],
            "Ventas", SUM('FactSales'[SalesAmount]),
            "Subtotal_StoreType", ISSUBTOTAL('DimStore'[StoreType])
)
ORDER BY [ContinentName], [Subtotal_StoreType], [StoreType]

 

 

Ya que ROLLUP admite más de una columna de agregación como parámetro, vamos a añadirle la columna ContinentName para obtener en esta ocasión, además de los acumulados anteriormente mencionados, una nueva fila de total con el importe de las ventas para todos los tipos de almacén y continentes. Al mismo tiempo crearemos con ISSUBTOTAL sendas columnas para StoreType y ContinentName, que junto a un adecuado orden facilitarán la lectura de los datos.

 

EVALUATE
SUMMARIZE(
            'DimStore',
            ROLLUP('DimStore'[StoreType], 'DimGeography'[ContinentName]),         "Ventas", SUM('FactSales'[SalesAmount]),
            "Subtotal_StoreType", ISSUBTOTAL('DimStore'[StoreType]),          "Subtotal_ContinentName", ISSUBTOTAL('DimGeography'[ContinentName])
)
ORDER BY [Subtotal_StoreType], [StoreType], [Subtotal_ContinentName], [ContinentName]

 

 

Agrupar los acumulados con ROLLUPGROUP

A pesar de existir la posibilidad de utilizar varias columnas con ROLLUP, en determinadas situaciones quizá nos interese tener una única fila de acumulado para todas las columnas de agregación incluidas en esta cláusula, lo que podemos lograr  mediante ROLLUPGROUP, otra de las cláusulas de SUMMARIZE.

Esta cláusula se utiliza como parámetro de ROLLUP, colocando las columnas de agregación como parámetros de la llamada a la función ROLLUPGROUP.

 

EVALUATE
SUMMARIZE(
            'DimStore',
            ROLLUP(ROLLUPGROUP('DimStore'[StoreType], 'DimGeography'[ContinentName])),
            "Ventas", SUM('FactSales'[SalesAmount]),
            "Subtotal_StoreType", ISSUBTOTAL('DimStore'[StoreType])
)
ORDER BY [Subtotal_StoreType], [StoreType], [ContinentName]

 

 

Cabe destacar que la información sobre ROLLUPGROUP disponible en la documentación online de la función SUMMARIZE es una aportación de Marco Russo, puesto que dicha entrada, correspondiente a la referencia de las funciones del lenguaje DAX, no disponía inicialmente de reseña alguna acerca de esta cláusula.

Para lograr un comportamiento similar en Transact-SQL haremos uso del operador ROLLUP cuando especifiquemos las columnas a agrupar, así como de la función GROUPING en la lista de columnas a mostrar de la instrucción SELECT.

 

SELECT
CASE
            WHEN (GROUPING(StoreType)=1) THEN '--Total General--'
            ELSE StoreType
END AS StoreType,
CASE
            WHEN (GROUPING(ContinentName)=1) THEN '--Acumulado por tipo almacén--'
            ELSE ContinentName
END AS ContinentName,
SUM(SalesAmount) AS Ventas
FROM DimStore
INNER JOIN DimGeography
ON DimStore.GeographyKey = DimGeography.GeographyKey
INNER JOIN FactSales
ON DimStore.StoreKey = FactSales.StoreKey
GROUP BY StoreType, ContinentName WITH ROLLUP
ORDER BY StoreType, GROUPING(ContinentName), ContinentName

 

Y llegados a este punto damos por concluida esta serie de artículos en los que hemos expuesto diversas técnicas para la creación de consultas contra modelos tabulares empleando el lenguaje DAX, espero que os resulten de utilidad. 

Si las tablas de las columnas empleadas en la consulta con SUMMARIZE no están relacionadas se producirá un error, como vemos en el siguiente bloque de código, donde combinamos las columnas StoreType y BrandName de las tablas DimStore y DimProduct, para obtener las ventas realizadas.

 

EVALUATE
SUMMARIZE(
            'DimStore',
            'DimStore'[StoreType],
            'DimProduct'[BrandName],
            "Ventas", SUM('FactSales'[SalesAmount])
)

 

 

Tal y como se explica en el mensaje de error, la columna BrandName no existe en la tabla de entrada DimStore, utilizada como primer parámetro de la función SUMMARIZE; aunque en este caso sería más correcto decir que la tabla DimProduct, que contiene la columna BrandName, no está relacionada con la tabla de entrada usada en la consulta.

Si el primer parámetro de SUMMARIZE representa una tabla con las columnas a visualizar (directa o indirectamente a través de relaciones), en el caso de que no existan relaciones con las tablas necesarias podemos crear una tabla al vuelo con las columnas que tenemos que mostrar empleando la función GENERATE.

 

EVALUATE
SUMMARIZE(
            GENERATE(
                        VALUES('DimStore'[StoreType]),
                        VALUES('DimProduct'[BrandName])
            ),
            [StoreType],
            [BrandName],
            "Ventas tipo almacén marca producto", SUM('FactSales'[SalesAmount])
)
ORDER BY [StoreType],[BrandName]

 

 

En el caso de que el número de columnas sin relacionar que tengamos que presentar sea superior a dos, podemos optar por la función CROSSJOIN para crear la tabla de entrada utilizada por SUMMARIZE, como vemos en la siguiente consulta, donde creamos una medida calculada para realizar la suma de las ventas, a la que aplicaremos un formato monetario, y que emplearemos en una condición de filtro para evitar mostrar importes vacíos.

 

DEFINE
MEASURE 'FactSales'[Ventas] = SUM('FactSales'[SalesAmount])

EVALUATE
FILTER(
            SUMMARIZE(
                        CROSSJOIN(
                                   VALUES('DimStore'[StoreType]),
                                   VALUES('DimProduct'[BrandName]),
                                   VALUES('DimDate'[CalendarYear])
                        ),
                        [StoreType],
                        [BrandName],
                        [CalendarYear],
                        "Ventas tipo almacén-marca producto-año venta", FORMAT('FactSales'[Ventas], "Currency")
            ),
            NOT(ISBLANK([Ventas]))
)
ORDER BY [StoreType],[BrandName],[CalendarYear]

 

 

Partiendo del anterior resultado, supongamos ahora que necesitamos calcular el porcentaje que la combinación de ventas por tipo de almacén, marca de producto y año de venta ha supuesto con respecto al total de las ventas realizadas.

Para obtener estos resultados precisamos añadir al código de la consulta dos nuevas medidas calculadas: la primera, a la que llamaremos VentasTotales, realizará mediante la función SUMX la suma de la columna SalesAmount para todas las filas de la tabla FactSales; empleando la función ALL para ignorar cualquier filtro que pudiera encontrarse activo.

La segunda medida, PorcentajeVentas, efectuará la operación encargada de calcular el porcentaje de las ventas realizadas. Dicho valor será convenientemente formateado cuando lo mostremos junto al resto de columnas de la consulta.

 

DEFINE
MEASURE 'FactSales'[Ventas] = SUM('FactSales'[SalesAmount])
MEASURE 'FactSales'[VentasTotales] = SUMX(ALL('FactSales'), 'FactSales'[SalesAmount])
MEASURE 'FactSales'[PorcentajeVentas] = ('FactSales'[Ventas] / 'FactSales'[VentasTotales])

EVALUATE
FILTER(
            SUMMARIZE(
                        CROSSJOIN(
                                   VALUES('DimStore'[StoreType]),
                                   VALUES('DimProduct'[BrandName]),
                                   VALUES('DimDate'[CalendarYear])
                        ),
                        [StoreType],
                        [BrandName],
                        [CalendarYear],
                        "Ventas tipo almacén-marca producto-año venta", FORMAT('FactSales'[Ventas], "Currency"),
                        "Ventas totales", 'FactSales'[VentasTotales],
                        "Porcentaje ventas", FORMAT('FactSales'[PorcentajeVentas], "Percent")
            ),
            NOT(ISBLANK([Ventas]))
)
ORDER BY [StoreType],[BrandName],[CalendarYear]

 

 

Aunque la medida calculada VentasTotales se visualiza junto al resto de las columnas, podría perfectamente permanecer oculta, ya que su finalidad consiste en actuar como operando para hallar el porcentaje de las ventas.

Para obtener este mismo resultado mediante SQL, tendremos que establecer, al igual que en anteriores ejemplos, las combinaciones necesarias entre las tablas implicadas en la consulta.

 

SELECT StoreType, BrandName, CalendarYear,
SUM(SalesAmount) AS [Ventas tipo almacén-marca producto-año venta],
(SELECT SUM(SalesAmount) FROM FactSales) AS [Ventas totales],
FORMAT( ( SUM(SalesAmount) / (SELECT SUM(SalesAmount) FROM FactSales) ) , 'P', 'ES-ES' )  AS [Porcentaje ventas]
FROM DimStore
INNER JOIN FactSales
ON DimStore.StoreKey = FactSales.StoreKey
INNER JOIN DimProduct
ON FactSales.ProductKey = DimProduct.ProductKey
INNER JOIN DimDate
ON FactSales.DateKey = DimDate.Datekey
GROUP BY StoreType, BrandName, CalendarYear
ORDER BY StoreType, BrandName, CalendarYear

 

Empleando la tabla de resultados numéricos como tabla de entrada

En los últimos ejemplos de SUMMARIZE hemos planteado la manera de abordar la creación de una consulta introduciendo un problema consistente en la inexistencia de relaciones entre la tabla de entrada y el resto de tablas de las columnas de agregación implicadas en dicha consulta, lo cual nos llevaba a recurrir a soluciones un tanto artificiosas, tales como el empleo de las funciones GENERATE o CROSSJOIN en el código a escribir.

Observando las características de estas consultas nos percataremos de que la tabla FactSales, empleada para calcular la suma de ventas, se encuentra relacionada con las tablas utilizadas para las columnas de agregación. Si a esto unimos la capacidad de utilizar FactSales como tabla de entrada en SUMMARIZE, podremos construir una consulta más simple que evite el uso de trucos rebuscados. Como ventaja añadida, esta consulta no devuelve filas con la columna de ventas vacía, lo que también nos libera de aplicar el correspondiente filtro.

 

EVALUATE
SUMMARIZE(
            'FactSales',
            'DimStore'[StoreType],
            'DimProduct'[BrandName],
            'DimDate'[CalendarYear],
            "Ventas", FORMAT(SUM('FactSales'[SalesAmount]), "Currency")
)
ORDER BY [StoreType],[BrandName],[CalendarYear]

 

 

Las columnas de agrupación de los ejemplos con SUMMARIZE mostrados en la entrega anterior pertenecían a una misma tabla, lo cual puede resultar conveniente en determinados casos, pero con toda seguridad, en algún momento nos encontraremos ante situaciones en las que tengamos que crear una consulta para visualizar columnas pertenecientes a diversas tablas del modelo.

Gracias a la potencia que ofrece el mecanismo de relaciones del motor tabular, esta labor resulta tan simple como añadir tales columnas en la lista de parámetros de la función, siempre y cuando existan las oportunas relaciones entre las tablas integrantes de la consulta.

Observando el diagrama de nuestro modelo de datos de ejemplo vemos que la tabla DimStore se relaciona con las tablas DimGeography, DimEmployee y FactSales, por lo que podemos construir una consulta con SUMMARIZE en la que ofrezcamos al usuario la suma de los descuentos sobre las ventas, agregada por el tipo de almacén, país y nombre del gerente del almacén que ha realizado la venta.

 

EVALUATE
SUMMARIZE(
            'DimStore',
            'DimStore'[StoreType],
            'DimGeography'[RegionCountryName],
            'DimEmployee'[FirstName],
            'DimEmployee'[LastName],
            "Total descuentos", SUM('FactSales'[DiscountAmount])
)
ORDER BY [StoreType],[RegionCountryName],[FirstName],[LastName]

 

 

En SQL esta consulta es muy similar a la anterior que hemos escrito en Transact, con alguna variación en las tablas a combinar.

 

SELECT StoreType, RegionCountryName, FirstName, LastName,
SUM(DiscountAmount) AS [Total descuentos]
FROM DimStore
INNER JOIN DimGeography
ON DimStore.GeographyKey = DimGeography.GeographyKey
INNER JOIN DimEmployee
ON DimStore.StoreManager = DimEmployee.EmployeeKey
INNER JOIN FactSales
ON DimStore.StoreKey = FactSales.StoreKey
GROUP BY StoreType, RegionCountryName, FirstName, LastName
ORDER BY StoreType, RegionCountryName, FirstName, LastName

 

En la tabla DimStore, la columna StoreManager representa al empleado de la organización que realiza las funciones de gerente o director del almacén, existiendo la posibilidad de que un mismo gerente se encuentre al cargo de varios almacenes. Esto nos permite complicar un poco la consulta DAX anterior, de manera que los resultados visualizados se correspondan con los directores que administran más de un almacén.

A continuación mostramos nuevamente esta consulta con los retoques que acabamos de mencionar. Por un lado quitamos la columna StoreType de la tabla DimStore, ya que uno de los objetivos consiste en contar el número de almacenes a cargo de cada director, independientemente del tipo de almacén. Dicho recuento lo realizaremos empleando una medida calculada (cláusula DEFINE) con el nombre NumeroAlmacenes, que reutilizaremos en más de un lugar de la consulta. La función FILTER, por otra parte, nos permitirá establecer la condición para obtener los gerentes a cargo de varios almacenes.

 

DEFINE
MEASURE 'DimStore'[NumeroAlmacenes] = COUNT('DimStore'[StoreKey])

EVALUATE
FILTER(
            SUMMARIZE(
                        'DimStore',
                        'DimGeography'[RegionCountryName],
                        'DimEmployee'[FirstName],
                        'DimEmployee'[LastName],
                        "Total descuentos", SUM('FactSales'[DiscountAmount]),
                        "Número almacenes gestionados", 'DimStore'[NumeroAlmacenes]          ),
            'DimStore'[NumeroAlmacenes] > 1
)
ORDER BY [RegionCountryName],[FirstName],[LastName]

 

 

Utilizando SQL resulta necesario escribir una mayor cantidad de código para conseguir este mismo resultado, como vemos a continuación.

 

WITH
tblGerentes AS
(
            SELECT StoreManager, COUNT(*) AS NumeroAlmacenes
            FROM DimStore
            GROUP BY StoreManager
            HAVING COUNT(*) > 1
),
tblStore AS
(
            SELECT StoreKey, GeographyKey, DimStore.StoreManager, tblGerentes.NumeroAlmacenes
            FROM DimStore
            INNER JOIN tblGerentes
            ON DimStore.StoreManager = tblGerentes.StoreManager
)
SELECT RegionCountryName, StoreManager, FirstName, LastName,
SUM(DiscountAmount) AS [Total descuentos],
NumeroAlmacenes AS [Número almacenes gestionados]
FROM tblStore
INNER JOIN DimGeography
ON tblStore.GeographyKey = DimGeography.GeographyKey
INNER JOIN DimEmployee
ON tblStore.StoreManager = DimEmployee.EmployeeKey
INNER JOIN FactSales
ON tblStore.StoreKey = FactSales.StoreKey
GROUP BY RegionCountryName, StoreManager, FirstName, LastName, NumeroAlmacenes
ORDER BY RegionCountryName, StoreManager, FirstName, LastName, NumeroAlmacenes

 

En la siguiente entrega daremos otra vuelta de tuerca al uso de la función SUMMARIZE, mostrando el modo de resolver consultas en las que se encuentren involucradas tablas no relacionadas. 

La función SUMMARIZE muestra los distintos valores de una o varias columnas (denominadas columnas de agrupación) de una tabla, permitiendo además incluir expresiones que generen columnas calculadas, encargadas de devolver valores numéricos en base a las columnas de agrupación. Con ello logramos un comportamiento similar al obtenido mediante la cláusula GROUP BY de Transact-SQL.

Comenzaremos nuestro periplo acerca del uso de esta función con un ejemplo muy básico: mostrar los distintos valores de la columna RegionCountryName, perteneciente a la tabla DimGeography.

 

EVALUATE
SUMMARIZE(
            'DimGeography',
            'DimGeography'[RegionCountryName]
)
ORDER BY [RegionCountryName]

 

 

Como es lógico, podemos utilizar varias de las columnas correspondientes a la tabla pasada en el primer parámetro, también denominada tabla de entrada.

 

EVALUATE
SUMMARIZE(
            'DimGeography',
            'DimGeography'[ContinentName],
            'DimGeography'[RegionCountryName]
)
ORDER BY [ContinentName],[RegionCountryName]

 

Para conseguir un resultado equivalente en SQL podemos emplear GROUP BY, tal y como hemos mencionado anteriormente, o también la cláusula DISTINCT, ya que hasta el momento no estamos usando una operación de agregado como COUNT, SUM, etc., sobre el conjunto de resultados.

 

SELECT ContinentName, RegionCountryName
FROM DimGeography
GROUP BY ContinentName, RegionCountryName
ORDER BY ContinentName, RegionCountryName

--//////////////////////////////////////////////

SELECT DISTINCT ContinentName, RegionCountryName
FROM DimGeography
ORDER BY ContinentName, RegionCountryName

 

Supongamos ahora que de este resultado nos interesaría saber la cantidad de unidades vendidas para cada una de las combinaciones de continente y país. Para ello escribiremos una expresión que utilice la función SUM aplicada a la columna SalesQuantity de la tabla FactSales, y que añadiremos a SUMMARIZE como último parámetro.

 

EVALUATE
SUMMARIZE(
            'DimGeography',
            'DimGeography'[ContinentName],
            'DimGeography'[RegionCountryName],
            "Cantidades vendidas", SUM('FactSales'[SalesQuantity])
)
ORDER BY [ContinentName],[RegionCountryName]

 

 

En el caso de que necesitemos más columnas calculadas iremos añadiendo las expresiones correspondientes a las mismas como parámetros adicionales al final de la función, tal y como vemos a continuación, donde agregamos dos medidas para obtener el importe de venta y las ventas menos el descuento aplicado.

 

EVALUATE
SUMMARIZE(
            'DimGeography',
            'DimGeography'[ContinentName],
            'DimGeography'[RegionCountryName],
            "Cantidades vendidas", SUM('FactSales'[SalesQuantity]),
            "Importe ventas", SUM('FactSales'[SalesAmount]),
            "Venta con descuento", SUMX('FactSales', 'FactSales'[SalesAmount] - 'FactSales'[DiscountAmount])
)
ORDER BY [ContinentName],[RegionCountryName]

 

Al emplear una sentencia SQL para obtener un resultado equivalente ahora sí será necesario el uso de GROUP BY, dado que aplicamos diversas operaciones de agregado (suma) sobre varias columnas, así como la combinación de la tabla DimGeography con DimStore y FactSales, para que las columnas numéricas reflejen los valores correctos para cada continente y país.

 

SELECT ContinentName, RegionCountryName,
SUM(SalesQuantity) AS [Cantidades vendidas],
SUM(SalesAmount) AS [Importe ventas],
(SUM(SalesAmount) - SUM(DiscountAmount)) AS [Venta con descuento]
FROM DimGeography
INNER JOIN DimStore
ON DimGeography.GeographyKey = DimStore.GeographyKey
INNER JOIN FactSales
ON DimStore.StoreKey = FactSales.StoreKey
GROUP BY ContinentName, RegionCountryName
ORDER BY ContinentName, RegionCountryName

 

En la siguiente parte de esta serie abordaremos la forma de mostrar columnas de agrupación procedentes de múltiples tablas empleando SUMMARIZE. 

La función TOPN devuelve un subconjunto de una cantidad determinada de las primeras filas pertenecientes a una tabla, en base a una expresión utilizada para ordenar dicha tabla.

Como primer parámetro pasaremos el número de filas a recuperar; en segundo lugar indicaremos la tabla de la que se obtendrán las filas; el tercer parámetro corresponderá a la expresión que realizará la ordenación de las filas; y opcionalmente, en el cuarto parámetro, pasaremos un número (0 ó 1) indicativo del sentido (ascendente o descendente) en que se realizará la ordenación especificada en el tercer parámetro.

Supongamos que de la tabla DimProduct queremos averiguar las siete marcas de productos (columna BrandName) que han generado más ventas (tabla FactSales, columna SalesAmount). Comenzaremos nuestro análisis del problema desde el motor relacional, ejecutando la siguiente consulta SQL, que nos devuelve todas las marcas de producto con sus totales de venta correspondientes.

 

WITH tblMarcas AS
(
            SELECT DP.BrandName,
            SUM(FS.SalesAmount) AS VentasMarca
            FROM FactSales AS FS, DimProduct AS DP
            WHERE FS.ProductKey = DP.ProductKey
            GROUP BY DP.BrandName
)
SELECT BrandName, FORMAT(VentasMarca, 'C', 'es-es') AS Ventas
FROM tblMarcas
ORDER BY VentasMarca DESC

 

 

Para obtener aquí las siete marcas con más ventas, simplemente añadiremos la partícula TOP junto a la cantidad de filas que necesitamos obtener.

 

WITH tblMarcas AS
(
            SELECT DP.BrandName,
            SUM(FS.SalesAmount) AS VentasMarca
            FROM FactSales AS FS, DimProduct AS DP
            WHERE FS.ProductKey = DP.ProductKey
            GROUP BY DP.BrandName
)
SELECT TOP 7 BrandName, FORMAT(VentasMarca, 'C', 'es-es') AS Ventas
FROM tblMarcas
ORDER BY VentasMarca DESC

 

Vamos a realizar un primer intento para conseguir este mismo resultado en DAX, utilizando la función TOPN en la siguiente consulta.

 

EVALUATE
TOPN(
            7,
            VALUES('DimProduct'[BrandName]),
            SUM('FactSales'[SalesAmount]),
            0
)

                      

Los resultados, no obstante, no serán nada satisfactorios porque, por un lado, obtenemos todos los nombres de marcas de producto, y por otro, no aparecen los importes de ventas por marca. Observando detenidamente la documentación de la función veremos que esto es completamente lógico, ya que TOPN devuelve las filas de la tabla pasada en el segundo parámetro según el resultado de aplicar la expresión del tercero.

Como primer paso para acercarnos a la solución añadiremos a la consulta una medida calculada, que será la encargada de sumar la columna SalesAmount; esta medida la emplearemos a continuación como tercer parámetro de TOPN.

 

DEFINE
MEASURE DimProduct[VentasPorMarca] = SUM(FactSales[SalesAmount])

EVALUATE
TOPN(
                       7,
                       VALUES('DimProduct'[BrandName]),
                       DimProduct[VentasPorMarca],
                       0
)

 

 

Con esto casi hemos cumplido con nuestro objetivo, tan sólo nos falta visualizar la columna con los importes de ventas, para lo cual recurriremos a la función ADDCOLUMNS, a la que pasaremos como parámetro la función TOPN y la expresión con la columna calculada que realiza la suma de las ventas.

 

DEFINE
MEASURE 'DimProduct'[VentasPorMarca] = SUM('FactSales' [SalesAmount])

EVALUATE
ADDCOLUMNS(
                       TOPN(
                                   7,
                                   VALUES('DimProduct'[BrandName]),
                                    'DimProduct'[VentasPorMarca],
                                    0
                       ),
                       "Ventas por nombre marca", FORMAT('DimProduct'[VentasPorMarca], "Currency")
)
ORDER BY 'DimProduct'[VentasPorMarca] DESC

 

 

Aunque podamos estar acostumbrados al uso de la partícula TOP en Transact-SQL, en el contexto de las consultas y expresiones con DAX es probable que el empleo de TOPN no resulte tan intuitivo como a priori cabría esperar. En este enlace se proporciona una explicación más detallada sobre dicha función, y en este post se muestra un interesante caso de uso.

A raíz de las consultas desarrolladas en la entrega anterior, supongamos que ahora debemos visualizar, además de la marca del producto y el año de venta, el nombre del país y tipo de almacén en el que ésta se ha producido; datos que encontramos en las tablas DimGeography y DimStore respectivamente.

 

CROSSJOIN. Aumentando la cantidad de tablas a combinar

Puesto que la función GENERATE admite solamente dos tablas en su lista de parámetros debemos modificar nuestra consulta, de forma que soporte la inclusión de nuevas columnas, por lo que vamos a recurrir a la función CROSSJOIN, que al igual que GENERATE, realiza un producto cartesiano de las tablas que recibe como parámetro, pero con la diferencia de poder utilizar más de dos tablas, como vemos en la siguiente consulta.

 

EVALUATE
CROSSJOIN(
            VALUES('DimGeography'[ContinentName]),
            VALUES('DimStore'[StoreType]),
            VALUES('DimProduct'[BrandName]),
            VALUES('DimDate'[CalendarYear])
)
ORDER BY [ContinentName], [StoreType], [BrandName], [CalendarYear]

 

 

Para obtener el mismo resultado en Transact-SQL podemos utilizar la cláusula CROSS JOIN, pero con el inconveniente de que el tiempo de ejecución de la consulta será notablemente mayor que en DAX.

 

SELECT DISTINCT g.ContinentName,s.StoreType,p.BrandName,d.CalendarYear
FROM DimGeography AS g
CROSS JOIN DimStore AS s
CROSS JOIN DimProduct AS p
CROSS JOIN DimDate AS d
ORDER BY g.ContinentName,s.StoreType,p.BrandName,d.CalendarYear

 

Para añadir a la consulta DAX la columna con las cifras de ventas emplearemos ADDCOLUMNS como uno más de los parámetros de CROSSJOIN.

 

EVALUATE
CROSSJOIN(
            VALUES('DimGeography'[ContinentName]),
            VALUES('DimStore'[StoreType]),
            VALUES('DimProduct'[BrandName]),
            ADDCOLUMNS(
                        VALUES('DimDate'[CalendarYear]),
                        "Ventas",FORMAT(SUMX(RELATEDTABLE(FactSales), 'FactSales'[SalesAmount]), "Currency")
            )
)
ORDER BY [ContinentName], [StoreType], [BrandName], [CalendarYear]

 

 

Tras ejecutar la consulta, con toda seguridad habremos advertido que el cálculo resultante en la columna de ventas es incorrecto, ya que para cada fila únicamente se está teniendo en cuenta la columna 'DimDate'[CalendarYear] al realizar la suma de 'FactSales'[SalesAmount]. Este resultado para cada grupo de años es el mismo que si utilizáramos la siguiente consulta SQL.

 

SELECT YEAR(DateKey) AS AñoVenta,
FORMAT(SUM(SalesAmount), 'C', 'es-es') AS ImporteVentas
FROM FactSales
GROUP BY YEAR(DateKey)
ORDER BY YEAR(DateKey)

 

  

Sin embargo, nuestro objetivo consiste en efectuar la suma de 'FactSales'[SalesAmount] para cada combinación de 'DimGeography'[ContinentName], 'DimStore'[StoreType], 'DimProduct'[BrandName] y 'DimDate'[CalendarYear]. Supongamos que tuviéramos que construir esta consulta para ejecutarla en el motor relacional. La sentencia SQL a emplear, utilizando CTEs, sería parecida a la siguiente.

 

WITH
tblGeography AS
(
            SELECT DISTINCT GeographyKey, ContinentName
            FROM DimGeography
),
tblStore AS
(
            SELECT StoreKey, StoreType, tblGeography.ContinentName
            FROM DimStore
            INNER JOIN tblGeography
            ON DimStore.GeographyKey = tblGeography.GeographyKey
),
tblProduct AS
(
            SELECT ProductKey, BrandName
            FROM DimProduct
),
tblDate AS
(
            SELECT Datekey, CalendarYear
            FROM DimDate
)
SELECT ContinentName, StoreType, BrandName, CalendarYear, FORMAT(SUM(SalesAmount), 'C', 'es-es') AS Ventas
FROM FactSales
INNER JOIN tblStore
ON FactSales.StoreKey = tblStore.StoreKey
INNER JOIN tblProduct
ON FactSales.ProductKey = tblProduct.ProductKey
INNER JOIN tblDate
ON FactSales.DateKey = tblDate.Datekey
GROUP BY ContinentName, StoreType, BrandName, CalendarYear
ORDER BY ContinentName, StoreType, BrandName, CalendarYear


 

Para obtener un resultado equivalente en DAX debemos cambiar la posición de las funciones CROSSJOIN y ADDCOLUMNS dentro de la consulta.

Como primer parámetro de ADDCOLUMNS pasaremos la llamada a CROSSJOIN, y como segundo la expresión que crea la columna calculada que realiza la suma de 'FactSales'[SalesAmount]. Adicionalmente situaremos todo este código dentro de la función FILTER, añadiendo una condición para quitar las filas que carezcan de importe de venta.

 

EVALUATE
FILTER(
            ADDCOLUMNS(
                        CROSSJOIN(
                                   VALUES('DimGeography'[ContinentName]),
                                   VALUES('DimStore'[StoreType]),
                                   VALUES('DimProduct'[BrandName]),
                                   VALUES('DimDate'[CalendarYear])
                        ),
                        "Ventas",FORMAT(SUMX(RELATEDTABLE(FactSales), 'FactSales'[SalesAmount]), "Currency")
            ),
            LEN([Ventas]) > 0
)
ORDER BY [ContinentName], [StoreType], [BrandName], [CalendarYear]

 

 

Como alternativa a lo anterior podemos volver a usar GENERATE, pasando en el primer parámetro la llamada a CROSSJOIN y en el segundo la función ADDCOLUMNS.

 

EVALUATE
FILTER(
            GENERATE(
                        CROSSJOIN(
                                   VALUES('DimGeography'[ContinentName]),
                                   VALUES('DimStore'[StoreType]),
                                   VALUES('DimProduct'[BrandName])
                        ),
                        ADDCOLUMNS(
                                   VALUES('DimDate'[CalendarYear]),
                                   "Ventas",FORMAT(SUMX(RELATEDTABLE(FactSales), 'FactSales'[SalesAmount]), "Currency")
                        )
            ),
            LEN([Ventas]) > 0
)
ORDER BY [ContinentName], [StoreType], [BrandName], [CalendarYear]

  

En la segunda parte de esta serie ilustrábamos cómo mediante el trabajo en conjunto de VALUES más ADDCOLUMNS, lográbamos crear consultas cuyos resultados ofrecieran al mismo tiempo la información de una tabla más una operación de agregado sobre otra tabla relacionada.

Pero habrá ocasiones en que esto no sea suficiente, siendo necesario añadir a la consulta datos de otras tablas del modelo, para ampliar y mejorar la información proporcionada al usuario.

Por ejemplo, tomando como punto de partida la consulta con la función VALUES de la entrega anterior, supongamos que además de la columna BrandName, necesitamos añadir al resultado la columna CalendarYear de la tabla DimDate y un cálculo basado en la suma de las ventas realizadas por la combinación de las dos columnas.

Si con ADDCOLUMNS intentáramos ejecutar una consulta como la siguiente se produciría un error, ya que de los parámetros admitidos por esta función, sólo el primero puede ser una tabla o expresión de tabla, siendo el resto expresiones que generan columnas calculadas.

 

EVALUATE
ADDCOLUMNS(
            VALUES('DimProduct'[BrandName]),
            VALUES('DimDate'[CalendarYear]),
            "Ventas por marca y año", CALCULATE(SUM('FactSales'[SalesAmount]))
)

 

GENERATE. Enriqueciendo el resultado mediante la combinación de tablas

Para resolver este problema recurriremos a la función GENERATE, que recibe como parámetro dos tablas o expresiones de tabla, generando como resultado el producto cartesiano de ambas. A continuación vemos una consulta de ejemplo que emplea esta función para combinar las tablas DimStore y DimGeography.

 

EVALUATE
GENERATE('DimStore','DimGeography')

 

Observando el resultado, apreciaremos ocurrencias de filas en la tabla DimStore que tienen una correspondencia geográfica incorrecta al combinarse con la tabla DimGeography, debido precisamente al modo en el que se realiza el cruce entre los registros de ambas tablas. En breve explicaremos cómo arreglar este aspecto en particular.

La forma de aplicar GENERATE para resolver nuestro problema consiste en pasarle como primer parámetro la función VALUES, creando así la columna con las marcas de productos; y como segundo parámetro la función ADDCOLUMNS, para crear tanto la columna de años como la columna calculada con la suma de ventas. Todo ello lo vemos en la siguiente consulta.

 

EVALUATE
GENERATE(
            VALUES('DimProduct'[BrandName]),
            ADDCOLUMNS(
                        VALUES('DimDate'[CalendarYear]),
                        "Ventas por marca y año", SUMX(RELATEDTABLE('FactSales'), 'FactSales'[SalesAmount])
            )
)
ORDER BY [BrandName], [CalendarYear]

 

En el conjunto de resultados obtenido, como ya hemos mencionado, habrá filas con el valor de la columna "Ventas por marca y año" vacías, que corresponderán a registros de la tabla FactSales para los que no se han producido ventas en la fecha con la que se están cruzando.

 

Para evitar la visualización de las filas que no tienen valor en la columna de importe de venta agregaremos a la consulta la función FILTER, en la que situaremos como primer parámetro la expresión anterior, mientras que en el segundo parámetro escribiremos una expresión que actúe como filtro, eliminando las filas sin valor de venta.

 

EVALUATE
FILTER(
            GENERATE(
                        VALUES('DimProduct'[BrandName]),
                        ADDCOLUMNS(
                                   VALUES('DimDate'[CalendarYear]),
                                   "Ventas por marca y año", SUMX(RELATEDTABLE('FactSales'), 'FactSales'[SalesAmount])
                        )
            ),
            NOT(ISBLANK([Ventas por marca y año]))
)
ORDER BY [BrandName], [CalendarYear]

 

Otra variante de la expresión de filtro podría ser la siguiente:

 

[Ventas por marca y año] > 0

 

Con cualquiera de estas formas de aplicar el filtro, el conjunto de resultados ya no mostrará importes vacíos. 

 

Con Transact-SQL podemos obtener este mismo resultado utilizando una sentencia muy similar a la última empleada, pero añadiendo la combinación con la tabla DimDate y su campo DateKey, así como el campo CalendarYear en la cláusula GROUP BY.

 

WITH tblFactSales AS
(
            SELECT DateKey, ProductKey, SalesAmount
            FROM FactSales
)
SELECT BrandName, CalendarYear, SUM(tblFactSales.SalesAmount) AS [Ventas por marca y año]
FROM DimProduct
INNER JOIN tblFactSales
ON DimProduct.ProductKey = tblFactSales.ProductKey
INNER JOIN DimDate
ON DimDate.Datekey = tblFactSales.DateKey
GROUP BY BrandName, CalendarYear
ORDER BY BrandName, CalendarYear

 

Corrección de efectos no deseados en la ordenación de números con formato

Si en la anterior consulta DAX aplicamos un formato (moneda, separador de millar, etc.) sobre la columna "Ventas por marca y año", creada dinámicamente, y ordenamos por dicha columna de la siguiente manera.

 

EVALUATE
FILTER(
            GENERATE(
                        VALUES('DimProduct'[BrandName]),
                        ADDCOLUMNS(
                                   VALUES('DimDate'[CalendarYear]),
                                   "Ventas por marca y año", FORMAT(SUMX(RELATEDTABLE('FactSales'), 'FactSales'[SalesAmount]), "Currency")
                           )
            ),
            LEN([Ventas por marca y año]) > 0
)
ORDER BY [Ventas por marca y año]

 

El resultado no se mostrará ordenado tal y como esperamos.

 

Esto es debido a que la columna "Ventas por marca y año", por causa del formateo aplicado, contiene datos de tipo carácter en lugar de numéricos, lo que produce el aparentemente extraño comportamiento en la ordenación, que en realidad no es tal, ya que se trata del modo correcto de ordenar una columna de tipo string (obsérvese también que para la condición de filtro hemos utilizado la función LEN, que se emplea en el manejo de cadenas).

Vamos a solucionar este inconveniente recurriendo de nuevo a la creación de medidas mediante la instrucción DEFINE, con la que crearemos la medida que realiza la suma de las ventas, la cual emplearemos en los siguientes puntos de la consulta: dentro de ADDCOLUMNS, como expresión de filtro, y como criterio de ordenación.

 

DEFINE                   
MEASURE 'FactSales'[Ventas] = SUMX(RELATEDTABLE('FactSales'), 'FactSales'[SalesAmount])

EVALUATE
FILTER(
    GENERATE(
        VALUES('DimProduct'[BrandName]),
        ADDCOLUMNS(
            VALUES('DimDate'[CalendarYear]),
            "Ventas por marca y año", FORMAT('FactSales'[Ventas], "Currency")
        )
    ),
    [Ventas] > 0
)
ORDER BY [Ventas] DESC

 

 

La obtención de resultados procedentes de dos tablas resulta una estupenda posibilidad de la que disponemos al escribir consultas DAX, aunque en escenarios en los que debamos manejar información de un mayor número de tablas resultará insuficiente. En la siguiente parte de esta serie abordaremos dicha cuestión. 

En la primera parte de esta serie comenzamos abordando la creación de columnas calculadas, que uníamos al resto de columnas de la tabla mediante la función ADDCOLUMNS. En esta segunda entrega veremos cómo construir consultas basadas en los valores de una única columna.

 

VALUES. Valores distintos de una columna

Aunque la función ADDCOLUMNS es adecuada para dotar de valores adicionales a una tabla, puede suceder que en determinados momentos estemos más interesados en obtener el contenido de una sola de sus columnas, y más concretamente, de los distintos valores que la componen, bien para visualizarlos o como parte de una expresión más compleja.

Para tal cometido disponemos de la función VALUES, que recibe como parámetro una columna de la que extrae sus distintos valores, retornándolos en forma de tabla de una sola columna, como vemos en el siguiente ejemplo, donde se muestran los nombres de las marcas de productos de la tabla DimProduct.

 

EVALUATE
VALUES('DimProduct'[BrandName])
ORDER BY [BrandName]

 

 

En una consulta SQL disponemos de la cláusula DISTINCT, con la que obtendremos los distintos valores de uno de los campos de la tabla.

 

SELECT DISTINCT BrandName
FROM DimProduct
ORDER BY BrandName

 

Puesto que el tipo de dato devuelto por VALUES es una tabla, y ADDCOLUMNS recibe un valor de este tipo en su primer parámetro, podemos combinar ambas funciones para crear una consulta en la que mostremos las marcas de productos de la tabla DimProduct, y una columna calculada que sume las ventas de la tabla FactSales, obteniendo como resultado las cifras de ventas por cada marca. Recordemos que para que la operación de suma se lleve a cabo correctamente emplearemos las funciones CALCULATE y SUM, o la combinación de SUMX y RELATEDTABLE.

 

EVALUATE
ADDCOLUMNS(
            VALUES('DimProduct'[BrandName]),
            "Ventas por marca", CALCULATE(SUM('FactSales'[SalesAmount]))
)
ORDER BY [BrandName]

 

 

La función DISTINCT también devuelve, al igual que VALUES, los valores únicos de una columna, por lo que en la mayoría de las situaciones los resultados serán iguales utilizando indistintamente una u otra. No obstante, en algunos casos, VALUES devolverá un valor vacío o elemento desconocido, cuando al combinar dos tablas no exista correspondencia entre alguno de sus miembros.

 

EVALUATE
ADDCOLUMNS(
            DISTINCT('DimProduct'[BrandName]),
            "Ventas por marca", CALCULATE(SUM('FactSales'[SalesAmount]))
)
ORDER BY [BrandName]

 

Desde Transact-SQL necesitamos en este caso emplear la cláusula GROUP BY, puesto que junto al campo BrandName vamos a usar la función de agregado SUM. Para la combinación entre las tablas DimProduct y FactSales definiremos sobre esta última una expresión común de tabla o CTE (Common Table Expression).

 

WITH tblFactSales AS
(
            SELECT ProductKey, SalesAmount
            FROM FactSales
)
SELECT BrandName, SUM(tblFactSales.SalesAmount) AS [Ventas por marca]
FROM DimProduct
INNER JOIN tblFactSales
ON DimProduct.ProductKey = tblFactSales.ProductKey
GROUP BY BrandName
ORDER BY BrandName

 

DEFINE y MEASURE. Creación y reutilización de medidas calculadas

Si en la anterior consulta con VALUES quisiéramos además ordenar el resultado por la columna "Ventas por marca", tendríamos que repetir la expresión que contiene la operación de suma en la cláusula de ordenación.

 

EVALUATE
ADDCOLUMNS(
            VALUES('DimProduct'[BrandName]),
            "Ventas por marca", CALCULATE(SUM('FactSales'[SalesAmount]))
)
ORDER BY CALCULATE(SUM('FactSales'[SalesAmount]))

 

Esta situación puede resultar bastante incómoda en el caso de consultas extensas, provocando además, que nuestro código se vuelva más complicado de leer y mantener.  

Una forma de evitar este tipo de reiteraciones pasa por utilizar las instrucciones DEFINE y MEASURE al comienzo de la consulta, las cuales, tal y como sus nombres indican, nos permiten definir una medida calculada que podemos reutilizar tantas veces como queramos a lo largo de la consulta. Como ventaja adicional, en el ejemplo que nos ocupa, al crear la medida con DEFINE ya no será necesario emplear la función CALCULATE.

 

DEFINE
MEASURE FactSales[Ventas] = SUM('FactSales'[SalesAmount])

EVALUATE
ADDCOLUMNS(
            VALUES('DimProduct'[BrandName]),
            "Ventas por marca", FORMAT(FactSales[Ventas], "Currency")
)
ORDER BY FactSales[Ventas] DESC

 

Para mejorar la visualización de los datos al crear esta nueva versión de la consulta incluimos la función FORMAT, aplicando un formato de moneda a la medida/columna calculada.

 

El siguiente paso consistirá en construir una consulta compuesta por columnas provenientes de varias tablas, cuestión esta que abordaremos en las próximas entregas. 

Como ya explicábamos en el artículo sobre creación de consultas para modelos tabulares (primera y segunda parte), el lenguaje DAX puede ser utilizado para emitir consultas contra un modelo tabular, obteniendo como respuesta un conjunto de resultados, dentro de una mecánica similar a la que estamos acostumbrados a emplear cuando trabajamos con el motor relacional, es decir, mediante sentencias SQL del estilo "SELECT ListaDeCampos FROM Tabla". Esta funcionalidad del lenguaje recibe el nombre de Consultas DAX (DAX Queries), siendo la instrucción EVALUATE la encargada de llevarla a cabo empleando sentencias del tipo "EVALUATE Expresión".

Aunque inicialmente, las consultas DAX no estaban disponibles en el lenguaje cuando PowerPivot hizo su primera presentación, la llegada de SQL Server 2012, que posiciona a Business Intelligence Semantic Model (BISM) como el nuevo paradigma en el desarrollo de soluciones de inteligencia de negocio, ha supuesto una ampliación de las características del lenguaje, que ahora nos va a permitir escribir consultas contra los nuevos modelos tabulares pertenecientes a BISM.

Con el presente artículo iniciamos una serie en la que continuaremos la línea de exploración iniciada en el artículo antes mencionado, por lo que son recomendables, al menos, unos conocimientos básicos sobre DAX, de forma que los ejemplos que vamos a exponer puedan ser seguidos adecuadamente. Adicionalmente, en aquellos casos en que se estime oportuno, mostraremos el código de la consulta tanto en lenguaje DAX como en Transact-SQL, lo que nos permitirá observar las diferencias entre ambos a la hora de abordar la resolución de un mismo problema.

 

El proyecto de ejemplo

Como base de los ejemplos que iremos desarrollando vamos a utilizar el modelo ContosoTabular, anteriormente creado aquí; aunque en esta ocasión volveremos a abrir el proyecto de dicho modelo en SQL Server Data Tools (SSDT) para añadir la tabla DimEmployee (necesaria en alguna de las diversas pruebas a realizar), tras lo cual desplegaremos nuevamente el modelo en el servidor de análisis de SQL Server, actualizando así los cambios realizados. A continuación ejecutaremos SQL Server Management Studio (SSMS) abriendo una ventana de consulta, que a partir de este momento utilizaremos para ilustrar las funciones empleadas en la construcción de consultas DAX.

  

ADDCOLUMNS. Creación dinámica de columnas

La función ADDCOLUMNS permite crear una o varias columnas calculadas a partir de una tabla o expresión de tabla. Como primer parámetro situaremos la tabla a la que se añadirá la columna(s), definiendo a continuación cada una de las columnas mediante una cadena para el título y una expresión DAX con la fórmula para el cálculo de la columna.

En la siguiente sentencia mostramos la tabla DimStore junto a una columna calculada que duplica el número de empleados de cada almacén.

 

EVALUATE
ADDCOLUMNS(
            'DimStore',
            "Empleados Duplicados", [EmployeeCount] * 2
)

 

  

Para obtener el mismo resultado utilizando la instrucción SELECT en Transact-SQL escribiremos la siguiente consulta.

 

SELECT *, EmployeeCount * 2 AS [Empleados Duplicados]
FROM DimStore

 

Continuando con la tabla DimStore, supongamos que de la columna OpenDate, que contiene la fecha de apertura del almacén, queremos obtener la parte correspondiente al mes como nombre. Una posibilidad, aunque no la más óptima, como veremos enseguida, sería utilizar la siguiente consulta, en la que combinando ADDCOLUMNS con las funciones MONTH y SWITCH, obtenemos una nueva columna con el nombre del mes correspondiente a OpenDate.

 

EVALUATE
ADDCOLUMNS(
            'DimStore',
            "Mes Apertura", SWITCH(MONTH([OpenDate]),
                        1,"Enero",
                        2,"Febrero",
                        3,"Marzo",
                        4,"Abril",
                        5,"Mayo",
                        6,"Junio",
                        7,"Julio",
                        8,"Agosto",
                        9,"Septiembre",
                        10,"Octubre",
                        11,"Noviembre",
                        12,"Diciembre"
            )
)

 

 

La función MONTH devuelve el número del mes de la fecha pasada como parámetro. Si este resultado lo pasamos a su vez como parámetro a la función SWITCH, para cada número retornamos una cadena con el nombre del mes correspondiente.

Pero como ya decíamos antes, existe una forma mucho más simple de obtener el mismo resultado, consistente en utilizar la función FORMAT, a la que pasaremos como primer parámetro la columna de fecha, y como segundo la cadena con el formato que deseamos aplicar. La siguiente expresión consigue el mismo resultado que la anterior pero con mucho menos código.

 

EVALUATE
ADDCOLUMNS(
            'DimStore',
            "Mes Apertura", FORMAT([OpenDate], "MMMM")
)

 

En Transact-SQL también disponemos de la función FORMAT que se utiliza como vemos a continuación.

 

SET LANGUAGE Spanish

SELECT *, FORMAT(OpenDate, 'MMMM') AS [Mes Apertura]
FROM DimStore

 

Compliquemos ahora un poco la expresión utilizada para crear la columna calculada: supongamos que necesitamos averiguar la cantidad de años transcurridos desde la fecha de apertura del almacén (columna OpenDate) hasta la fecha actual, teniendo en cuenta que si existe valor en la columna CloseDate significará que el almacén ya ha sido cerrado.

La expresión de columna para resolver esta situación implicará, pues, el uso de las siguientes funciones: YEAR, para extraer el año de las columnas de fecha; NOW, para obtener la fecha actual; ISBLANK, para comprobar la existencia de valor en la columna CloseDate; e IF, para determinar la operación que realiza el cálculo de años según la columna CloseDate tenga o no valor.

 

EVALUATE
ADDCOLUMNS(
            'DimStore',
            "Años actividad almacén", IF(ISBLANK([CloseDate]),
                        YEAR(NOW()) - YEAR([OpenDate]),
                        YEAR([CloseDate]) - YEAR([OpenDate])
            )
)

 

 

En SQL usaremos la función DATEDIFF para hallar la diferencia entre las fechas. La expresión CASE nos servirá para utilizar en el cálculo la fecha actual o la de cierre del almacén si la primera es nula.

 

SELECT *,
CASE
            WHEN CloseDate IS NULL THEN
                        DATEDIFF(year, OpenDate, GETDATE())
            ELSE
                        DATEDIFF(year, OpenDate, CloseDate)
END AS [Años actividad almacén]
FROM DimStore

 

Las columnas calculadas creadas hasta el momento con la función ADDCOLUMNS se basan en operaciones sobre las columnas de la propia tabla DimStore, pasada como primer parámetro a la función. No obstante, también es posible generar columnas dinámicas que pertenezcan a otras tablas del modelo tabular con las que DimStore guarda relación.

Observando el diagrama de tablas del modelo tabular en SSDT, vemos que DimStore mantiene relaciones con las tablas FactSales, DimGeography y DimEmployee.

 

Es por lo tanto perfectamente posible crear una columna calculada con ADDCOLUMNS que muestre la información de la columna RegionCountryName, perteneciente a la tabla DimGeography, ya que se relaciona con la tabla DimStore a través de la columna GeographyKey, común en ambas tablas. Es necesario tener en cuenta que en el momento de crear la expresión de la columna calculada debemos indicar explícitamente la existencia de dicha relación utilizando la función RELATED. Esta nueva columna tendrá como título "País ubicación".

 

EVALUATE
ADDCOLUMNS(
            'DimStore',
            "País ubicación", RELATED('DimGeography'[RegionCountryName])
)

 

Además, tal y como explicábamos al principio de este apartado, con ADDCOLUMNS podemos crear varias columnas calculadas al mismo tiempo, por lo que añadiremos a la consulta anterior otra expresión con el título "Ventas almacén", que realice, empleando la función SUM, la suma de la columna SalesAmount, perteneciente a la tabla FactSales, con la que también se relaciona DimStore a través del campo StoreKey.

 

EVALUATE
ADDCOLUMNS(
            'DimStore',
            "País ubicación", RELATED('DimGeography'[RegionCountryName]),
            "Ventas almacén", SUM('FactSales'[SalesAmount])
)

 

 

Pero observando el resultado obtenido vemos que hay algo que no funciona correctamente, ya que la finalidad de la anterior consulta consiste en obtener la suma del campo SalesAmount para cada uno de los almacenes, y lo que hemos conseguido es que el valor de la columna calculada "Ventas almacén" muestre en todos los casos la suma total de SalesAmount, lo cual no es el objetivo que perseguimos.

Podemos solucionar este problema de dos formas: la primera consiste en sustituir la función SUM por SUMX, que sobre una tabla pasada como primer parámetro realiza la suma de la columna pasada en el segundo parámetro. Aquí también debemos indicar que existe una relación entre DimStore y FactSales, para lo que usaremos la función RELATEDTABLE.

 

EVALUATE
ADDCOLUMNS(
            'DimStore',
            "País ubicación", RELATED('DimGeography'[RegionCountryName]),
            "Ventas almacén", SUMX(RELATEDTABLE('FactSales'), 'FactSales'[SalesAmount])
)

 

De esta forma la suma ya se realizará de forma separada para cada almacén. 

 

La otra vía de abordar la suma de las ventas por almacén consiste en utilizar la función CALCULATE, a la que pasaremos como parámetro la operación de suma. CALCULATE se encargará de evaluar dicha expresión para cada uno de los almacenes (valores distintos del campo StoreKey) existentes en la tabla DimStore, obteniendo de esta manera el resultado deseado.

 

EVALUATE
ADDCOLUMNS(
            'DimStore',
            "País ubicación", RELATED('DimGeography'[RegionCountryName]),        
            "Ventas almacén", CALCULATE(SUM('FactSales'[SalesAmount]))
)

La función CALCULATE puede revestir cierta complejidad inicial, por lo que recomendamos visitar el siguiente post del blog de Marco Russo, en el que realiza una detallada explicación acerca de su funcionamiento.

Para obtener este resultado con Transact-SQL usaremos una combinación de tipo LEFT JOIN entre las tablas DimStore y DimGeography, además de una subconsulta sobre la tabla FactSales, que realice la suma del campo SalesAmount con la función SUM.

 

SELECT S.*, G.RegionCountryName,
(SELECT SUM(F.SalesAmount) FROM FactSales AS F WHERE F.StoreKey = S.StoreKey) AS [Ventas almacén]
FROM DimStore AS S
LEFT JOIN DimGeography AS G
ON S.GeographyKey = G.GeographyKey

 

Como hemos podido comprobar, la función ADDCOLUMNS resulta de gran utilidad a la hora de añadir a nuestras consultas información adicional en forma de columnas calculadas. En las siguientes entregas explicaremos diversas técnicas y funciones del lenguaje, mediante las que conseguiremos seleccionar un subconjunto de columnas de la tabla, evitando la necesidad de obtener la totalidad de las mismas. 

Más artículos Página siguiente >