El aprendiz de brujo : DAX, PowerPivot

Totales parciales con SUMMARIZE. Profundizando en el uso de DAX como lenguaje de consulta (y 9)

Luis Miguel Blanco — Wed, 15 May 2013 17:16:00 GMT

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.

Columnas de agrupación en SUMMARIZE con tablas no relacionadas. Profundizando en el uso de DAX como lenguaje de consulta (8)

Luis Miguel Blanco — Tue, 07 May 2013 18:45:00 GMT

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]

Combinación de múltiples tablas con SUMMARIZE. Profundizando en el uso de DAX como lenguaje de consulta (7)

Luis Miguel Blanco — Wed, 24 Apr 2013 17:59:00 GMT

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.

Agrupación y agregación de columnas con la función SUMMARIZE. Profundizando en el uso de DAX como lenguaje de consulta (6)

Luis Miguel Blanco — Wed, 17 Apr 2013 17:29:00 GMT

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.

Obtención de N filas con TOPN. Profundizando en el uso de DAX como lenguaje de consulta (5)

Luis Miguel Blanco — Wed, 10 Apr 2013 20:53:00 GMT

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.

La función CROSSJOIN. Profundizando en el uso de DAX como lenguaje de consulta (4)

Luis Miguel Blanco — Tue, 02 Apr 2013 20:39:00 GMT

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]

La función GENERATE. Profundizando en el uso de DAX como lenguaje de consulta (3)

Luis Miguel Blanco — Tue, 19 Mar 2013 20:00:00 GMT

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.

La función VALUES y la creación de medidas calculadas. Profundizando en el uso de DAX como lenguaje de consulta (2)

Luis Miguel Blanco — Tue, 12 Mar 2013 08:59:00 GMT

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.

La función ADDCOLUMNS. Profundizando en el uso de DAX como lenguaje de consulta (1)

Luis Miguel Blanco — Mon, 04 Mar 2013 19:56:00 GMT

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.

Creación de consultas para modelos tabulares en SQL Server 2012 (y 2)

Luis Miguel Blanco — Thu, 08 Nov 2012 18:02:00 GMT

En la primera parte de este artículo explicábamos la forma de crear un modelo tabular en SQL Server 2012 para su posterior consulta utilizando algunas de las técnicas que ilustraremos en esta segunda entrega.

Consultas DAX

Todos aquellos lectores que hayan tenido la oportunidad de trabajar con PowerPivot conocerán en mayor o menor medida el lenguaje DAX y la capacidad que otorga al usuario de construir, en un modelo de datos, expresiones en forma de columnas y medidas calculadas para obtener los resultados analíticos requeridos.

En lo que respecta al desarrollo de modelos tabulares, a las funcionalidades que acabamos de mencionar se suma ahora la posibilidad de escribir consultas con las que obtener conjuntos de resultados a partir de las tablas del modelo.

Para ello haremos uso de la instrucción EVALUATE que representa la pieza fundamental en la construcción de consultas DAX contra un modelo tabular, acompañando a la misma el nombre de la tabla a obtener, tal y como vemos en el siguiente ejemplo.

EVALUATE DimStore;

Para poder apreciar el resultado de la ejecución de esta sentencia iniciaremos SSMS y nos conectaremos a Analysis Server. A continuación haremos clic en el modelo ContosoTabular recién desplegado en el servidor, que aparece como nodo dependiente de la instancia de Analysis Server, y seleccionaremos la opción de menú "File | New | Query with Current Connection", que abrirá una ventana de consultas MDX que en este caso utilizaremos para escribir sentencias DAX. Antes de proseguir aclararemos al lector que cuando nos encontramos trabajando con modelos tabulares, el editor de consultas de SSMS, a pesar de guardar los archivos de sentencias con la extensión MDX, soporta tanto consultas DAX como MDX.

Si ya hemos consultado la documentación oficial que sobre la instrucción EVALUATE existe, veremos que es posible ordenar el conjunto de resultados obtenido mediante la cláusula ORDER BY, de tal forma que si modificamos nuestra consulta de la manera mostrada en la siguiente sentencia, los datos aparecerán ordenados por la columna StoreManager.

EVALUATE DimStore
ORDER BY [StoreManager];

Podemos combinar varias columnas a la hora de ordenar, así como alterar el sentido de la ordenación con los modificadores ASC y DESC.

EVALUATE DimStore
ORDER BY [StoreManager] ASC, [StoreKey] DESC;

Pero aún hay más respecto a la ordenación, ya que usando la subcláusula START AT conseguiremos que los datos comiencen a mostrarse a partir de un determinado valor, perteneciente a la columna(s) que participa en el orden.

EVALUATE DimStore
ORDER BY [StoreManager] START AT 75;

Dedicaremos un próximo artículo a profundizar en el uso de EVALUATE y el resto de instrucciones DAX especialmente desarrolladas para el trabajo con modelos tabulares.

Elementos calculados. Medidas y columnas

En un modelo de datos tabular, además de la información que obtenemos de las columnas de una tabla, podemos encontrarnos con columnas y medidas calculadas que no forman parte de la estructura original de dicha tabla, sino que son producto de una expresión/fórmula DAX. En este apartado haremos una primera aproximación a este tipo de elementos de un modelo.

Pongamos por ejemplo que para la tabla FactSales queremos saber cuánto ha supuesto el total de ventas, por lo que tendremos que aplicar una operación de suma sobre la columna SalesAmount.

En primer lugar nos situaremos en dicha tabla dentro del diseñador. Como podemos comprobar, la cuadrícula de datos se divide en dos zonas: la superior, correspondiente a los propios registros de la tabla; y la inferior, reservada a las medidas calculadas, y que actualmente se encuentra vacía.

Para crear la medida que sume las filas de la columna SalesAmount haremos clic en una celda vacía de la zona inferior de dicha columna, y a continuación escribiremos la siguiente expresión en la barra de fórmulas del diseñador.

TotalVentas:=SUM([SalesAmount])

De esta forma obtendremos una medida con el nombre TotalVentas, que mostrará el resultado de la operación de suma sobre la columna.

También conseguiremos el mismo resultado mediante la opción de menú "Column | AutoSum | Sum", con la diferencia de que el nombre de la medida (Sum of SalesAmount) será asignado automáticamente, no obstante, siempre podemos recurrir a la ventana de propiedades de la medida y cambiarle el nombre en su propiedad Measure Name.

Pasemos seguidamente a la creación de una columna calculada, donde vamos a suponer que necesitamos una nueva columna para la tabla FactSales, en la que se realice una operación sobre la columna SalesAmount, que arroje como resultado el valor de dicha columna para la fila actual menos el 10%. Esto lo conseguiremos haciendo clic en una celda de la primera columna vacía disponible en la tabla y escribiendo la siguiente sentencia en la barra de fórmulas.

=[SalesAmount] - ([SalesAmount] * 0,1)

A continuación asignaremos el nombre a esta nueva columna haciendo doble clic en su cabecera y escribiendo el valor VentasConDescuento.

Tras la creación de estos miembros calculados proseguiremos con otro de los modos de consulta a nuestra disposición: las consultas MDX.

Consultas MDX

Aunque MDX fue desarrollado como lenguaje de consulta para cubos OLAP (modelos multidimensionales), en SQL Server 2012 ha sido adaptado para poder interaccionar también con modelos tabulares, de manera que cuando lo empleamos dentro de este contexto, el nombre del modelo (archivo bim del proyecto en SSDT) representa al cubo de datos, mientras que las tablas identifican a las dimensiones. Las medidas que hayamos calculado en las diferentes tablas quedarán agrupadas en un nodo aparte, funcionando como una dimensión especial, lo que vemos al abrir el modelo en SSMS.

Para hacer una consulta MDX contra el modelo emplearemos la instrucción SELECT, donde al igual que si consultáramos un cubo OLAP, especificaremos, mediante la cláusula ON COLUMNS, la dimensión que ubicaremos en el eje de las columnas, y que en este caso será la medida TotalVentas, ya que como hemos mencionado, las medidas del modelo también pueden comportarse como una dimensión al crear la consulta.

Con la cláusula ON ROWS indicamos que la dimensión utilizada para las filas será DimGeography, visualizando el nombre de las provincias o estados de los países con la función Children de MDX aplicada al atributo StateProvinceName.

SELECT

{[Measures].[TotalVentas]} ON COLUMNS,

{[DimGeography].[StateProvinceName].Children} ON ROWS

FROM [Model];

Agregando la cláusula WHERE a la consulta podremos aplicar filtros al resultado. Por ejemplo, si queremos que las cifras de ventas se circunscriban solamente a las ventas de productos de color azul (tabla DimProduct, atributo ColorName), emplearemos la siguiente consulta.

SELECT

{[Measures].[TotalVentas]} ON COLUMNS,

{[DimGeography].[RegionCountryName].Children} ON ROWS

FROM [Model]

WHERE ([DimProduct].[ColorName].&[Azul]);

Podemos desplazar la medida TotalVentas a la cláusula WHERE y poner en las columnas una selección de los años en los que se realizaron las ventas. Para especificar dichos años como un rango, simplemente hemos de poner el primer y último elemento del mismo separados por dos puntos, tal y como vemos a continuación.

SELECT

{[DimDate].[CalendarYear].&[2007] : [DimDate].[CalendarYear].&[2010]} ON COLUMNS,

{[DimGeography].[RegionCountryName].Children} ON ROWS

FROM [Model]

WHERE ([Measures].[TotalVentas]);

Excel como herramienta de consulta

Y llegamos a Excel, el tercero de los tipos de consulta contra modelos tabulares que estamos presentando en este artículo.

Cuenta como gran ventaja con una potente interfaz de usuario, lo que nos permite construir informes de gran riqueza ya que pone a nuestra disposición un amplio abanico de elementos visuales tales como tablas dinámicas, gráficos, estilos, formatos, etc.

Una vez iniciado Excel, para acceder al contenido de un modelo tabular haremos clic en la pestaña Datos de la cinta de opciones, y a continuación, dentro del grupo "Obtener datos externos", haremos clic en la opción "De otras fuentes" seleccionando en la lista desplegable el valor "Desde Analysis Services".

Como resultado se abrirá un asistente en el que especificaremos el servidor\instancia a la que queremos conectarnos.

En el siguiente paso seleccionaremos la base de datos que contiene el modelo tabular (ContosoTabular) junto al propio modelo, que en el asistente estará identificado como un cubo.

En el último paso guardamos la información de conexión en un archivo.

Como resultado de la ejecución del asistente se establecerá una conexión contra el modelo tabular, y en la hoja de cálculo se creará una tabla dinámica que nos servirá como vehículo para presentar los datos del modelo. En el panel de campos de la tabla dinámica tendremos a nuestra disposición las tablas y medidas que hayamos definido en el modelo, que arrastraremos, bien directamente a la tabla o a los bloques denominados Valores, Etiquetas de fila/columna y Filtro de informe, hasta componer un informe similar al que podemos ver a continuación, donde analizamos las ventas de productos de color azul en función de los años y el país en que se ha producido la venta.

Además podemos añadir un gráfico sincronizado con los valores numéricos desde la opción Gráfico dinámico, perteneciente a la pestaña Opciones de la pestaña de nivel superior Herramientas de tabla dinámica. En dicho gráfico también es posible aplicar filtros al igual que desde la tabla dinámica.

Y llegados a este punto damos por concluida esta introducción dedicada a la elaboración de consultas contra los nuevos modelos tabulares de SQL Server 2012, espero que resulte de ayuda a todos los que quieran empezar a dar sus primeros pasos con esta interesante tecnología.

Creación de consultas para modelos tabulares en SQL Server 2012 (1)

Luis Miguel Blanco — Tue, 06 Nov 2012 19:53:00 GMT

Los Servicios de Análisis de SQL Server 2012 (SQL Server Analysis Services, SSAS) han experimentado un importante cambio con la introducción del Modelo Semántico de Inteligencia de Negocio (Business Intelligence Semantic Model, BISM), un nuevo paradigma en la construcción de sistemas analíticos, que aúna el tradicional modelo multidimensional (Unified Dimensional Model, UDM), propio de los tradicionales cubos OLAP, con el novedoso modelo tabular, basado en un motor de datos relacional, el cual propugna una filosofía de trabajo más simple (aunque no menos potente en cuanto al volumen de datos a manejar) que nos permita resolver determinados problemas de una forma más sencilla y ágil que la requerida habitualmente para desarrollar un modelo multidimensional.

Y es en estos últimos, los modelos de datos tabulares, sobre los que vamos a concentrar nuestra atención a lo largo del presente artículo, más concretamente en la capacidad de elaborar consultas y métricas contra dichos modelos, las cuales nos permitan recabar la información necesaria acerca de su estado.

Para la confección de estas consultas emplearemos DAX (Data Analysis eXpressions), el lenguaje desarrollado con el objetivo de crear expresiones analíticas tanto para modelos de datos en PowerPivot como para modelos tabulares en BISM. También usaremos MDX para acceder a una fuente de datos tabular como si de un cubo OLAP se tratara. Finalizaremos nuestro periplo con Excel, como ejemplo de aplicación de usuario final dotada igualmente de la posibilidad de obtener información del modelo, presentándola con la conocida potencia gráfica de esta herramienta.

Creando el modelo tabular

Lo primero que necesitamos para poner en práctica lo explicado en el párrafo anterior es un modelo de datos tabular que podamos utilizar como banco de pruebas; así que comenzaremos arrancando SQL Server Data Tools (SSDT), el entorno de desarrollo para SQL Server 2012 basado en Visual Studio, que se encuentra en el mismo grupo de programas de SQL Server, y empleando el tipo de proyecto Analysis Services Tabular Project, crearemos un nuevo proyecto con el nombre ContosoTabular que usará ContosoRetailDW como fuente de datos.

El siguiente paso que daremos a continuación consistirá en la importación de las tablas de la base de datos ContosoRetailDW, que posteriormente utilizaremos en nuestras operaciones de análisis contra el modelo tabular, por lo que mediante la opción de menú "Model | Import From Data Source" de SSDT, ejecutaremos el asistente de importación de datos Table Import Wizard, en cuyo primer paso elegiremos el tipo de origen al que deseamos conectarnos.

En el siguiente paso introduciremos el servidor e instancia de SQL Server al que nos conectaremos (en este caso EVENDIM como servidor y SQLTABULAR como instancia), así como el nombre de la base de datos desde la que importaremos las tablas. También elegiremos Autenticación de Windows como método de conexión.

A continuación deberemos facilitar las credenciales de conexión que Analysis Services utilizará para conectarse a la base de datos al realizar el proceso de importación. En este caso emplearemos la cuenta del usuario que ha iniciado sesión en el equipo desde el que se desea conectar, aunque también podríamos haber usado la cuenta del servicio de Analysis Services, tal y como se explica en el siguiente enlace de este mismo blog.

Seguidamente podemos elegir el modo en que se realizará la importación de los datos: mediante una selección de las tablas/vistas de la base de datos o usando una consulta.

Para este ejemplo nos decantaremos por la primera opción, seleccionando las siguientes tablas: DimStore, DimGeography, DimDate, DimProduct, DimProductSubcategory y FactSales.

Completado este paso haremos clic en el botón Finish, que dará comienzo al proceso de importación, a cuyo término veremos la ventana de resultados, en la que si todo ha marchado bien, observaremos el detalle de las tablas y registros importados.

Una vez terminado el proceso de importación, el asistente nos devolverá a la ventana del diseñador del modelo tabular, donde tendremos a nuestra disposición las tablas recién importadas, organizadas en diversas pestañas.

También es posible visualizar estas tablas en forma de diagrama mediante la opción de menú "Model | Model View | Diagram View", lo que nos permitirá seleccionar la visualización de sus columnas, medidas, jerarquías, etc., así como las relaciones existentes entre estas.

Para retornar a la anterior vista del diseñador usaremos la opción "Model | Model View | Data View". Podemos conmutar entre estas dos vistas empleando los botones situados en la parte inferior derecha del diseñador.

Despliegue del modelo

Finalizada por el momento la etapa de desarrollo del modelo, procederemos a efectuar el despliegue del mismo en el servidor de análisis, de modo que pueda ser consultado por aquellas aplicaciones cliente que lo requieran. Para ello seleccionaremos la opción de menú "Build | Deploy ContosoTabular" o haremos clic derecho en el icono correspondiente al proyecto en el Explorador de Soluciones, eligiendo la opción Deploy. Ambas acciones conducirán a la ventana de despliegue, que nos irá informando acerca del progreso de la operación.

Si el despliegue se realiza con éxito, cerraremos su ventana de progreso y abriremos SQL Server Management Studio (SSMS), conectándonos a Analysis Services, donde encontraremos el modelo recién subido al servidor. Expandiendo su nodo principal veremos las tablas que lo componen.

A partir de este punto podemos comenzar a enviar consultas al modelo utilizando cualquiera de los medios a nuestra disposición: consultas DAX, consultas MDX o Excel. Todo ello será tratado en la segunda parte de este artículo.

PowerPivot, DAX and Excel. Business Intelligence for all audiences

Luis Miguel Blanco — Mon, 17 Sep 2012 19:25:00 GMT

(The spanish version of this article was previously published in dNM+, issue 83)

PowerPivot is a technology for information analysis whose peculiarity lies in the ability of working with massive amounts of data using Excel as user interface, so it becomes an attractive offer, given the popularity of this tool belonging to the Office suite. This article introduce the reader to PowerPivot as well as DAX (Data Analysis eXpressions), the analytical expression language that accompanies it, reeling off the most important features of this technology, which coupled with traditional Analysis Services and MDX (MultiDimensional eXpressions) language, make SQL Server 2012 one of the most robust and powerful solutions in the Business Intelligence arena.

With increasing frequency, organizations must perform diverse analysis about their status in the most varied perspectives (business, financial, HR, etc.) and on an increasingly vast amount of data, for which require suitable tools to enable them to obtain a reliable and valid information of such state.

As explained in this blog's article, OLAP data cubes are one of the key elements in this data analysis ecosystem called Business Intelligence (BI).

However, a BI system, as occurs in other areas such as Web applications, desktop, etc., requires a development team and time to its creation, this latter factor sometimes extends for longer than initially estimated.

Given the problems posed above, we can propose as a solution to improve development tools of the information system, which accelerate its creation time, and allow handling a greater data volume, given the widespread increase of this aspect in all corporate areas.

It would also be necessary to promote the development team, incorporating new players into the system development cycle, which so far have played a role of mere validators, but they can bring great value to it, given his extensive knowledge about the structure of the various data sources available within the organization.

PowerPivot comes with the aim of providing solutions to the challenges posed above, covering a number of specific areas in the development of an information system based on BI.

Self-service BI

When we talk about self-service, usually framed this concept within the product sales activity, where a person, the client, moves to a place, the supermarket, where products are exposed, and once there the client picks up the products, all generally without requiring the intervention of any intermediary.

Within the BI context we can make an analogy with the self-service model, using a concept called self-service BI, which advocates that end users (advanced or other profile) of an information system can elaborate their own analysis and reports on the system contents, to meet the specific needs emerging in several company departments, without having on these situations to rely from the enterprise BI development team.

As requirements are the access need by these users to those data sources that require analysis and it is also crucial that self-service BI oriented applications have a learning curve as small as possible, so users can start being productive with them almost immediately.

PowerPivot meet these requirements, since being a technology built into Excel has a wide user base already working with both, the functionality of the spreadsheet and access to external data sources, thus reducing or eliminating training in the use of user interface, and focuses on learning specific features about additional support for data analysis.

Incorporating new user profiles to the development cycle

In addition to release development teams a significant workload, providing self-service BI tools to end users; we get as a bonus the possibility that advanced users from various departments of the organization, through the use of such tools, to assist in the development cycle of the information system, providing ideas and suggestions.

VertiPaq. High performance data processing engine

PowerPivot introduces a new data processing engine: VertiPaq, which through a column-based storage, implements a series of algorithms for data compression, by which is capable of loading millions of records in memory.

However, due to the execution in memory nature of VertiPaq is recommended, whenever possible, work on a 64-bit environment, with adequate software version to this architecture: operating system, SQL Server, Office 2010, PowerPivot, etc., since it does not suffer the memory address limitations found in the 32-bit systems.

Architectural elements

PowerPivot, as seen in next image, consists of an assembly that is loaded into Excel process; VertiPaq engine, which handles data loading, query management and DAX expressions execution against the data warehouse, as well as PowerPivot's pivot tables and charts, and finally, the OLAP provider, the Analysis Management Objects (AMO) objects and ADOMD.NET provider, allowing communication with analysis services, for gathering information from data cubes if applicable.

Collaboration environment. PowerPivot for SharePoint

Using PowerPivot is expected that the number of analysis models created by users grows noticeably, and it is therefore very important to have a management mechanism, with respect to the publishing and access security within the organization.

This is the purpose of PowerPivot for SharePoint, a plug-in extending Excel Services for SharePoint, in the above paragraphs about management and collaboration.

Additionally, PowerPivot for SharePoint allows users to view PowerPivot models published in the organization simply using a web browser, so you only need to install Excel and PowerPivot for Excel on the machines of users who will develop analytical models using this tool.

PowerPivot. BISM cornerstone

(Note: for a more accurate description of BISM than below, we recommend read in first place the SSAS Tabular Models article which includes updated information about this technology).

SQL Server 2012 ships with a new BI model called Business Intelligence Semantic Model (BISM). This model doesn't replace or displace Unified Dimensional Model (UDM), the existing BI model from SQL Server 2005 but coexist with it, complementing and enriching the SQL Server offer in BI area, allowing us to have both models to work with the one that best suits our needs.

SQL Server has been traditionally blamed, referring to UDM, that its implementation in BI area has a difficult learning curve with complex concepts: cubes, dimensions, measures, etc., and that the time needed to develop a solution extends too much when it comes to creating a simple analysis system.

From our point of view, this learning curve isn't much more complicated than we find in other aspects of software development, although we admit that from the perspective of developers used to working with a relational data model, getting familiar with the UDM-OLAP multidimensional model to build a business analytics solution may involve some initial complexity until the developer master the concepts and features of the technology.

BISM comes to solve this problem, providing a simpler way of work, distinguished by its relational philosophy supported by PowerPivot and VertiPaq. While BISM is primarily oriented to the development of self-service analytical solutions or for small teams, UDM is focused in the development of corporate BI solutions, which require more planning.

BISM is a model with a distributed architecture in three layers: data access, business logic and data model.

The data access layer is responsible for connecting to data sources to load the data they contain, existing two ways to operate with them: cached or real time. In the case of cached mode, data is loaded using VertiPaq, whereas if we use the real-time mode, this task is left to the original data source from which data are extracted.

The business logic layer will implement the managing and processing of data that allow us to make them relevant information. We will use for this any expression languages at our disposal: DAX or MDX.

DAX is the expression language for data analysis built-in PowerPivot. We'll use it to build queries against the data store located in VertiPaq, while MDX is the language usually employed in UDM to query OLAP cubes.

DAX is not as powerful as MDX, but it is much easier to use, and aims to query data located in a self-service BI model, which theoretically should not require the use of complex queries, reserved to be made against the corporate BI system, which under normal conditions will have been developed by UDM.

Finally, the data model layer will be used by client applications (Excel, SharePoint, Reporting Services, etc.) gathering information from the proper model: relational or multidimensional. Among the reporting tools that use this model is Power View, a project whose objective is to provide a better user experience for data visualization and reporting against BISM based models.

Some prominent voices in the BI-related technical community have expressed concern about the UDM-OLAP position with the BISM arrival in SQL Server 2012, although from Analysis Services development team wanted to send a reassuring message, insisting that BISM is not a replacement for UDM but a complementary technology.

PowerPivot in practice

After architectural features review made in previous paragraphs, it's time to get down to working to develop a PowerPivot model, which help us to get our first impressions about the scope of this technology.

First of all we need to have Office 2010 installed, and download and install PowerPivot for Excel plug-in. In the event that our system is 32 bit, we'll download the file PowerPivot_for_Excel_x86.msi, but if we work on a 64 bit we must download the file PowerPivot_for_Excel_amd64.msi. For the sake of testing, in this article we use a Windows 7 operating system virtual machine with 1.5 GB of RAM and Intel Core 2 Duo processor.

Although the name of the download page referred to SQL Server 2008 R2, it is not necessary to have it installed if we are going to work with other data formats such as Access, text files, etc. However, in our case, we will use SQL Server as data engine and the database ContosoRetailDW. Through the latter link we'll download the ContosoBIdemoBAK.exe file containing the database in format backup.

The reason why we use this database is that it has some tables with millions of records, allowing us to test one of the salient features of PowerPivot: the ability to handle large volumes of data.

Creating a model

After restoring the database we'll open Excel, then clicking on the ribbon tab PowerPivot. Among the options on this tab we will click PowerPivot window, belonging to Launch group, which as its name suggests, will open the PowerPivot working window.

The first task in creating the model will be to connect to a data source to import its content, so that from the Home tab of the PowerPivot window, in the Get External Data group, we'll click From database option, selecting From SQL Server among the available items.

As a result will open the Table Import Wizard, where we'll include information to connect to the database ContosoRetailDW. In the Select Tables and Views step we will check FactSales, DimDate, DimStore and DimProduct.

After this step will begin the import process, once completed will show each of the imported tables in different tabs in PowerPivot window.

Querying the model

The set of tables we have just import will conduct an analysis of company sales, according to data related to the sales table: dates, products, stores, etc.

To do this we will click on PivotTable option in Reports group reports, creating a PowerPivot's PivotTable with which to perform our analysis operations. This action will move the focus to Excel window, where a dialog box will ask you the coordinates to place the PivotTable in the spreadsheet. Accepting the default values the new PivotTable will be created.

On the pivot table's right side we find the PowerPivot Field List panel where we select the fields to use for our analysis, either as axis labels of rows and columns, numeric values, filters, and slicers.

Those readers with expertise in creating OLAP cubes will verify that querying a PowerPivot model is very similar to the query against a data cube, but without the existence of a real cube, as in the field list panel, the items in block Values represent the cube measures or metrics, while the rest of the block fields, represent the dimension attributes, displayed either in rows, columns or filter the PivotTable.

We will begin creating our query by checking the field SalesAmount, from FactSales table. Due this is a numeric field, it will automatically be placed as a measure in the Values block. PowerPivot will apply then an addition operation on all records in the table to which it belongs.

The current state of the PivotTable does not offer, however, great analytical possibilities, since we only have total by SalesAmount field. We need to add additional elements to the query relating to the table FactSales such as DimProduct table. This table contains each product's name and additional information as manufacturer, type, color, size, etc.

Suppose we want to find out the number of sales by product, but instead of using his name, we need to analyze by manufacturer. What we do in this case is check the field Manufacturer, belonging to the field list of DimProduct table. This action will place the field in the Row Labels block and their values in the PivotTable row axis. In this way we will know how sales have meant by each of the manufacturers.

Since the SalesAmount field values are not shown formatted, as additional work we right-click any cell in this field, choosing Number Format... option. In the format dialog box select type Currency with two decimal places and euro symbol, being applied to all cells in this field in the pivot table.

Writing DAX expressions

As noted earlier, DAX is the expression language through which we'll build the business logic in PowerPivot, both as PivotTable and data model.

Before starting to write our first sentences in this language, we must know that without us aware of this, we have already written a DAX expression! If we right-click Sum of SalesAmount in Values block and select Edit Measure..., a dialog box will open with the expression automatically created by PowerPivot

=SUM('FactSales'[SalesAmount])

The creation of a DAX expression is very similar to writing a formula in Excel, since then the equal sign, write one or more functions with their corresponding parameters, forming the whole expression. In the expression currently under review, the SUM function will make a sum of SalesAmount field for all records in the table FactSales. If the table name has spaces or other special characters we enclose it in quotes, in other cases not necessary. With respect to the field name should always be enclosed in brackets.

Now let's approach to writing our own expressions, through two major elements PowerPivot: computed columns and measures.

First we will create a computed column in the table FactSales, to obtain the sales amount without discounting. To this end, in PowerPivot window we should be on the last available empty column, writing in the formula bar as follows:

=FactSales[UnitPrice] * FactSales[SalesQuantity]

To assist in the writing of expressions, the AutoComplete feature will suggest at all times a list of functions, tables and fields, depending on what we go on writing.

After writing the expression, the new column will be filled with the resulting values. Double-clicking the column header we'll assign the name ImporteSinDescuento, ending the creation of computed column.

Another way to create a computed column is to click on Add option from Columns group, belonging to Design tab.

Because the fields used in the formula belong to the same table on which we are creating the calculated column, in the expression syntax we can circumvent the name of the table, being as follows:

=[UnitPrice] * [SalesQuantity]

To use the new column in the pivot table we'll back to the Excel window, finding a warning in PowerPivot Field List which informs us that data was modified. Clicking the Refresh button of the warning the field list will update, adding the new field ImporteSinDescuento, which we'll check to include in the PivotTable.

Our next step is creating a measure to calculate, for each manufacturer, the percentage of his sales with respect to total sales of the company.

In PowerPivot tab, within Measures group, we click the New Measure option, opening the Measure Settings window, where we'll type PorcentajeVentas as measure's name and the following expression in formula field:

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

The first part of this expression, as we have seen, is responsible for adding the field SalesAmount. This sum will be made for each manufacturer, because this field is used in PivotTable labels row.

The second part of the expression returns, for all rows, the total sum of SalesAmount field, which we accomplish combining the functions CALCULATE, SUM and ALL. CALCULATE function evaluates the expression passed as the first parameter, which is a sum of SalesAmount field, and requires that the sum is over all FactSales table records by using the ALL function, regardless of the filters that are applied in the PivotTable.

Finally, add the new measure to the pivot table by applying the percentage format.

Filters and slicers

Our PivotTable now shows arranged by product manufacturers, a series of measures obtained from fields and calculations of FactSales table belonging to PowerPivot data model.

The results of these measurements are made from all records of that table, but at some point we may also be interested to have the ability to apply filters on the model's information, in order to obtain different perspectives of analysis.

PowerPivot pivot tables provide the user with filters and slicers as data filtering tools. Next, we'll explain to the reader to use them as a means to narrow the results of the report.

Let's suppose we want to filter information in the pivot table depending on the type of store where are the products. We get this data from field StoreType in DimStore table.

To create a filter of this type, in the field list pane we'll open out the field list of table DimStore and drag the StoreType field to the Report Filter block. As a result this filter is inserted in the top of the pivot table.

By clicking on the filter selecting values button, we'll select "Almacén". This action will activate the filter, causing all numeric cells in the pivot table refreshing sales information depending on the filter set.

Slicers, moreover, they behave as filters, although its handling by the user is slightly different.

Let's create a slicer that allows us to filter the results by sales year, for which we will drag the CalendarYear field from DimDate table and drop in Slicers Horizontal block. This action will create the slicer with all filter values placed above the pivot table.

Slicer values that appear in a darker color tone are those with which we can make effective filters, while the use of those with a lighter shade will not produce results as there are no sales in those years.

If we want to use more than one value in the slicer, we must keep down the CTRL key while clicking on the different values. The result of the filter is not effective until we release the CTRL key. To remove all filters from the segmentation will click the funnel-shaped icon situated at the top right.

Creating a PivotChart

In certain circumstances, the numerical data provided by a PivotTable it may not be sufficient to analyze the content of a PowerPivot model, so we also have pivot charts at our disposal, as an alternative in the information display.

We can create a pivot chart from scratch or use an existing pivot table as chart's foundation. In this case we'll choose the latter method, for which, once positioned in the pivot table, we'll click on Options tab belonging to PivotTable Tools category, and then select PivotChart option, located in the group Tools. This opens the Insert Chart dialog box, offering us a wide range of chart types to insert into the worksheet. Having made our choice, we will accept this dialog inserting the chart next to the PivotTable.

From this point, both the table and chart will be synchronized, so that the modifications of filters, measures axes labels, etc. performed in one will be reflected immediately in the other. Next image shows the appearance of our report including pivot table and pivot chart.

Conclusions

PowerPivot represents a technology with great potential to become an important part of the toolkit available for BI development with SQL Server. If we add capacity in handling large volumes of data, the power of its query expressions language, and the fact of using Excel as user interface, we obtain a product that allows users to create their own analysis models to address specific information needs. At the same time, these models can be of assistance to BI development teams in building corporate information systems.

Population pyramids with PowerPivot. Chart development (and 2)

Luis Miguel Blanco — Sun, 01 Apr 2012 11:06:00 GMT

In the first part of this article we developed a data model in PowerPivot representing population figures by age and sex. In this second installment will shape these figures on a chart shaped population pyramid.

Pyramid chart. First approach

In its current state, the pivot table already have enough information (figures by population, age and sex) to try to create a chart that represents a population pyramid, but we advance the reader that in this first approach we will not get the desired effect.

Positioned in the pivot table, from the Excel ribbon, select the option "PivotChart" belonging to "Tools" group from "Options" tab, which in turn is contained in the top-level tab "PivotTable Tools".

This selection opens "Insert Chart" window, which contains all available chart types. Here we will realize that there is no specific template for creating a pyramid chart, therefore, of all bid at our disposal we choose, within "Bar" category, the "Clustered Bar" type, which as we shall see later, it will be the best adapted to the result we want achieve.

Accepting this window, the chart will be created from the PivotTable data, and as we anticipated, the result won't look like the image presented in the first part of the article.

However, the main difference lies in the orientation of the men bar population, which should be left. All other aspects are basically visual configuration issues, which explain how to solve in short.

Solving the trajectory of the population bars

Focusing on the male population bar, the solution for achieve to draw itself in the opposite direction to the current, is to put in negative the values of the cells in the pivot table for this segment of the population.

If we were in a simple spreadsheet with no connection to PowerPivot, the solution is as simple as editing the cells in column Hombre, changing their values to negative, but in this scenario data is being obtained from the PowerPivot data model, so that it's not possible directly edit the values in the PivotTable.

To solve this kind of problems we have to resort to the creation of columns and / or calculated measures, which through DAX expressions provide the results we need. In the transition to negative the values of the Hombre column, we will open the PowerPivot window, and placing ourselves in the first available empty column in the Poblacion table, write the following expression in the formula bar:

=IF([Sexo_ID] = "M", 1, -1)

We just created a calculated column that will be evaluated for each row of the Poblacion table, checking if Sexo_ID field value is equal to the letter "M" (Mujer), if so, the column value in that row will be 1, otherwise, when the field contains "H" (Hombre), the returned value will be -1.

Then we double-click its header to assign Sexo_Codigo as the column's name it. We can also give name by right clicking on the header and choosing "Rename Column".

Turning again to the Excel window, we'll remove the chart we created in the worksheet and uncheck the RecuentoPoblacion measure, leaving empty the area of the PivotTable values.

The next step will be to create a new measure named SumaPoblacion, based on the following DAX expression:

=SUM([Sexo_Codigo])

By applying this measure to the PivotTable, the SUM function takes the sum of column values passed as parameter, so the population figures of men now appear negative. This means that when re-creating the chart in the manner explained above, the indicator bars of the population values by sex are now drawn in opposite directions. As an additional feature, on "PivotTable Tools" tab, into "Design" tab, at the "Layout" group, we'll drop "Grand Totals", selecting "On for Columns Only", that hides the row totals column, because its presence in this context is irrelevant.

Visual configuration of population bars

Although the chart bars are now shown with the effect we wanted, it would be desirable a few tweaks in its visual appearance to improve the presentation quality.

First we right click on the age ranges labels, selecting "Format axis". In the window of the same name, under "Axis Options", assign the value "Low" to "Axis Labels" property, which align the labels column to the left of the chart.

Then we right click on any of the bars in the graph, choosing "Format Data Series". In the configuration series window, into "Series Options," on property "Series Overlap" will move the position marker to the right end position (fully overlapped), while in property "Gap Width" will move the position marker to the left end (no gap at all). In this way we will accomplish the bars increase their thickness and eliminate the space between them, being completely joined to form the population pyramid.

Calculating the population percentages

So far, the data representation obtained, both in the pivot table as in the chart pyramid is based on absolute population numbers. However, the usual practice is that such representation is made as a proportion of each age group and sex on total population.

For example, in our pivot table, the women population aged between 55 and 59 is 184,888 people; to get the percentage that this population group is in relation to all individuals with whom we are working (6,458,684), we divide the group by the total, and format the result as a percentage, gaining 2.86%.

If we want the PivotTable to perform this operation for all population groups, we'll have to add additional calculations in the form of measures, but before that we'll remove the current population chart, because we will build it again from one of the new measures.

We start out the existence of a measure, SumaPoblacion, which as we know, it returns the number of people adding the field Sexo_Codigo. The next step is to create a new measure, which included in the pivot table, provide the total population in all cells.

Our first reaction might be to reuse the RecuentoPoblacion measure, created in the early stages of our example, but soon we realize that it is useless for this purpose, because although this measure counts the Poblacion table rows, the cells result are affected by the fields used in rows and columns, as well as other active filters in the PivotTable.

For a measure always count all rows in a table, regardless of the filters may be active, we'll use the CALCULATE function, which as the first parameter pass the operation to perform, in this case the count of rows in the Poblacion table using COUNTROWS function. Then pass as many parameters as filters we want to remove, using the ALL(TableName) function for each table that is, somehow, acting as a filter.

Under such assumptions create a new measure called TotalGlobalPoblacion, with the following DAX expression:

=CALCULATE (COUNTROWS (Poblacion), ALL (Edad), ALL (Sexo))

When applied this measure to the PivotTable, all the cells show the same value: the total population.

Now we need a third measure that makes the division between the two previous and display the result in percentage format. This new measure will have the name PorcentajePoblacion and use the following formula:

=[SumaPoblacion] / [TotalGlobalPoblacion]

As we saw in the first installment of the article, to apply the formatting to this measure will right click on one of its cells choosing "Number Format". In the format window this time select the "Custom" category, through which we introduce in the "Type" field the following format string.

0,00%;0,00%

This string will format the number as a percentage, and show the male population columns values without the negative sign, but internally, these values will remain negative.

Next deactivate all PivotTable measures except PorcentajePoblación, which will be the only one that remains visible. Then again add a clustered bar chart, using the configuration steps outlined above, and adding new format features to improve their presentation.

Apply percentage format to the horizontal axis

Firstly we will right click on the horizontal axis labels by selecting "Format axis", which will open the format window. In "Number" section, select the format category "Custom", and in the "Format Code" field, write the following format string:

0%;0%

By clicking the "Add" button, the string is added to the list of custom strings. Accepting the window, the format will apply to the horizontal axis labels.

Emphasizing the population bar edges

Then we will right click on one of the bars in the chart, choosing again "Format Data Series". This time, in "Border Color" section, we will click "Solid Line", selecting black color; while in "Border Styles" assign the value "2 pt." in property "Width". This operation will make for both groups of bars in the chart.

Then we right click on the labels of the age ranges, selecting "Format axis". In this format window assign the same values for the color and border style properties that just used to the chart bars.

As a result of these actions, the chart will show the edges with an outline clearly highlighted.

Relocating the legend position

At the time of chart creation, Excel puts the legend (Sexo_DS field) on the right side by default. However, it is possible to change the location of this element if we want to provide more space to the drawing of population bars. To do this, we right-click the legend and select "Format Legend" in the format window, under "Legend Options", we will click "Top".

As we can see, the chart has won drawing surface, but the legend indicators have been placed in reverse order with respect to the bars. To solve this problem we will click on the legend (Sexo_DS), dropping a menu of filter options, which will select "Sort Z to A".

With this action, the legend indicators shall be placed properly, but now we will find that the colors of the bars have been reversed, and lost the edge of the chart bars.

We will restore the edges of the bars in the manner explained above, whereas in terms of colors, for each side of the pyramid do a right click on one bar and select "Shape Fill", changing the current color which originally had the chart.

To complete the adjustments we are making about the legend, drag it until it is situated at the same level of the upper element of the pyramid, and will increase its width, so that the indicators are further apart.

Clear the fields and add title

Then we right click on any of the fields of the chart buttons and select the option "Hide All Field Buttons on Chart". In this way will prevent the user to apply filters on the horizontal and / or vertical pyramid axis, keeping a solid structure thus avoiding the possibility, for example, to hide age ranges either sex. However, this filter feature will still exist from the PivotTable.

Moreover, in the tab group "PivotChart Tools" we will click on "Layout", and within the group "Labels" will click on "Chart Title", which will drop several items where we will choose "Above Chart", adding a text box to the chart, we will edit to give it a title. At this point we have completed the development of our pyramid.

Energizing the data pyramid data through slicers

Although we have achieved the stated goal of creating a population pyramid, it would be interesting to enrich the information that actually offers as we do in this section.

Looking at the data model in the PowerPivot window, we shall realize that we haven't use health zoning information yet, so we can use these data to construct a filter that displays the pyramid based on the population concerning one or more of these health areas.

PowerPivot pivot tables, in addition to traditional filter, incorporating a new filter type called "slicer", in addition to the usual filtering functionality provides a more flexible user interface for handle the values to work with.

Let's create one slicer on the pyramid based on zone information. To this end, on the field list panel, drag the field Zona_DS from Zona table to "Slicers Horizontal" block, resulting in a slice located above both the pivot table and pyramid chart.

To filter data by slicer simply have to select the name of the health area we want to use as a filter. It is also possible to filter several areas simultaneously holding down the Ctrl key while clicking the areas composing the filter (as shown in the figure below).To remove all active filters will click on the icon located in the upper right corner of the slicer.

And at this point we concluded the article, in which its two parts we have shown how to construct a population pyramid in Excel 2010, using PowerPivot as a management tool for population data. However, the power of this technology goes beyond the mere treatment of demographic information, covering its scope to any environment where we have to make an analysis with large volumes of data.

Population pyramids with PowerPivot. Preparing the data (1)

Luis Miguel Blanco — Mon, 26 Mar 2012 16:56:00 GMT

A population pyramid is a tool that allows to analyze the status and evolution of a population based on age and sex. This is a feature in demography and statistics, but also cover areas such as health, education, business, etc. For this reason, its integration in an computing information system belonging to one of the areas just mentioned, involves a substantial enrichment in the quality of the results obtained by the users of such systems.

This article will discuss the construction of population pyramids using PowerPivot, an add-on to Excel 2010 that enables access to large volume data sources and its subsequent management and analysis.

As we mentioned in the article Generation of demographic data from SQL Server, also published in this blog, our current goal (using the PiramidePoblacion database created in that article) is to construct a chart representing a population pyramid similar to what we see in the figure below.

The first part of the article is devoted to the preparation of PowerPivot data model: connection to a data source, data load and pivot table creation to analyze the population figures. The second part will devote to the construction of the chart that represents the population pyramid, starting from the data on which we worked on the first delivery.

As in the previously mentioned article, I want to thank again the members of the Health and Studies Reports Service (Directorate of Health Promotion and Prevention, DG Primary Care, Health Ministry CM): Jenaro Astray Mochales, María Felicitas Dominguez Berjón, María Dolores Esteban Vasallo, Beatriz Elvira Rodriguez and especially to Ricard Gènova Maleras, for the support and guidance received on demographic concepts necessary to be able to properly develop a population pyramid using Excel 2010 in combination with PowerPivot. Besides all this, Ricard has kindly agreed to do a great job of reviewing this article, so the gratitude goes twice.

I would also like to thank to Enrique Barceló for all his great tricks about OLAP development, shared with the team, making a little easier our development work on all these business intelligence tasks.

A little theory

But before to jump into its creation process, we will provide some brief theoretical notes on population pyramids that allow us to better understand their main features and the information we can collect and analyze from them.

In essence, a population pyramid is a double histogram that shows the distribution by age and sex of the individuals from a population, either in absolute figures or as percentage of the total population.

If we are interested in obtaining more detailed information about the conceptual aspects surrounding population pyramids, Epidat's 4.0 documentation can be useful. This is an software application developed by the Ministry of Health of the Xunta de Galicia and the Pan American Health Organization (OPS, Washington), from whose documentation, we quoted below some of the most important points concerning the issue at hand. First start with some ideas related to the construction of the pyramid.

"The best way to visualize the distribution by sex and age of a population is, without doubt, the population pyramid, a true icon of demography. A pyramid is a double histogram that allows, at a single glance, to draw up a clear idea not only about the general characteristics of the population (young, aged, with some imbalance by gender) but also specific features which refer to a particular event relative to the population under study.

Usually the population pyramids are constructed following several conventions and rules:

- To represent men at left and women at right of the central axis.

- Locating ages so as to be lower, the closer are the base and vice versa.

- Maintain a certain proportionality between the base and height (3 widths by 2 levels, or 4 by 3, approximately).

- Respect the same scale on both sides of the central axis (to facilitate comparison between sexes).

- Represent the weight of each age interval by the area of each histogram bar, not by its length, which is especially important when working with mixed age groups.

- Can be built with absolute values, but preferably with the proportions of each age and sex of the total population.

The last point is important and differentiates the pyramid structure indicators [proportion of young and old, aging index, etc.], which are calculated separately on the total for each respective sex. In the case of the population pyramid, using the total population of both sexes gathered, as the denominator of the proportions, not only ensures the comparability of age distribution, but also by sex."

And we will conclude with those items relating to the pyramid's interpretation.

"A pyramid by sex and age summarizes the demographic history of a population of at least a hundred years before the reference date (the time it takes roughly one generation to move from the base to the top of the pyramid). Sometimes, the indirect effect of some demographic events is visible beyond the leap of a century (for example, the impact of the sharp decline in fertility in recent decades experienced by many populations will be appreciated, no doubt, in the pyramids of the first half of the XXII). A simple ages pyramid [age by age: 0, 1, 2, 3 ... 99, 100 ...] allows a more accurate analysis than other age groups on aggregate (five-year, ten-year) but also suffers the risk of being affected by quality problems in the records, or be vulnerable to instability of the distributions in small populations.

The first perception of a pyramid allows to identify the general features of the represented population: a broad-based pyramid that narrows quickly gives an idea of a young population with a high proportion of children and adolescents, adults and elderly low, resulting of high birth and death. Conversely, a pyramid with a narrow profile at the bottom and wide at the top and the middle represents a mature or aged structure. The higher the life expectancy of a population is, most often the inequality by gender is reflected at the top of the pyramid (more full on the side of women, due to their highest level of survival).

The pyramid does not provide answers for itself, but conducive to the relevant questions arise. The explanations for their profile must be found in historical, social, political and economical events and tendencies that generate consequences in demographic future -that is, in fertility, mortality and migration, which are the phenomena that shape the contours and set the size of a population- and have an interpretation in terms of the triple time perspective: age, period and cohort."

Incorporating data to PowerPivot

Thanks to VertiPaq, the PowerPivot's data processing engine, we can achieve unprecedented power and speed in handling, filtering, creating expressions of business logic, and ultimately, in all the analytical operations that we perform on a data model created with this tool.

The following link provides access to PowerPivot's web site, where we can download it, and then install it on our machine, so that we can follow the examples from this article.

When PowerPivot installation concludes, we will start Excel 2010, creating a new worksheet with the name PiramidePoblacion.xlsx. In the ribbon will have now a new tab with the name "PowerPivot". Clicking on it will select "PowerPivot Window " belonging to the "Launch" group, which opens the aforementioned PowerPivot window, where define the table structure we will use, or "data model" as referred in PowerPivot development context.

Then in PowerPivot's window "Home" tab, we'll connect to the database using "From Database | From SQL Server" option, from "Get External Data" group.

This will start the "Table Import Wizard", where indicate the data source we want to connect.

A valid data source to develop a population pyramid must have at least information about sex and age of the population, as is the case in PiramidePoblacion database. Additionally, as a way to enrich the analysis to be performed, the database may have additional information as may be the health zoning, population nationality and so on.

After selecting the PiramidePoblacion database, the next step will provide a list of tables and views that we could import, of which will mark the following: Edad, Poblacion, Sexo y Zona, beginning the import by clicking on "Finish".

If the import process runs correctly, the wizard will display a summary of the process window.

After the data entry concludes, we will click "Close" to return to the PowerPivot window, where we find the imported tables arranged in several tabs.

Analysis using PivotTable

Before go back to the construction phase of the population pyramid, we will use a PivotTable to analyze the figures from the PowerPivot data model, so placed in its working window, we will click on "PivotTable" option, from "Reports" group.

This will position us in the Excel window, which opens a dialog where we choose the worksheet that will accomodate the PivotTable.

In our case, selecting the first option and accepting the dialog box, will create the PivotTable on a new worksheet.

From the "PowerPivot Field List" panel we have at our disposal the fields from the data model tables, which will locate in different areas of the PivotTable (row labels, columns, values, etc.) to carry out our analysis operations. We will use for this a very similar style of work to that we would employ if we were querying an OLAP cube, since the management of the PowerPivot fields here is similar to what we can do with the dimensions and measures contained in a data cube when consulted from Excel.

Let's start with a simple query, consisting in a record count of the Poblacion table, grouping the information by age group, which will place in the PivotTable rows.

We'll make the record count through a DAX expression (the PowerPivot query language) located in a calculated measure, which will create by selecting "New Measure", belonging to the "Measures" group in the "PowerPivot" tab. We'll apply this measure on the Poblacion table, so we must be previously positioned on it, within the field list panel.

Selecting this option will open up the "Measure Settings" dialog, and in the formula text box we will write the following expression:

=COUNTROWS(Poblacion)

COUNTROWS, as its name suggests, is a function that counts the rows of the table passed as a parameter. To finish creating our measure, give it the name "RecuentoPoblacion" and click "OK".

Right after creating the measure, it is automatically added to Poblacion table field list and in the "Values" block, showing the table total number of rows in the pivot table.

Then select in the field list pane, the Edad_Grupo field from Edad table, which automatically placed it in the block "Row Labels". If this is not the behavior obtained, we'll manually drag the field and drop in that block.

As a result of the previous action the pivot table will be updated, showing the Edad_Grupo field values in the rows axis. Regarding figures from RecuentoPoblacion measure, they should be distributed among the sections of each age, to reflect the number of records (population) corresponding for each of these sections. However, as shown in the figure below, this is is not happening, because the measure shows the same value for all rows, which is incorrect.

Relationships between tables in the model

Observing the field panel we see that PowerPivot already noticed the problem just discussed, because on top of the panel displays a notice, which informs us that it may be necessary relationship between the tables that are being used to compose the pivot table query.

This problem would not have occurred if there had been the necessary relationships between tables in the database, but as we said in the article on the creation of the database population, such relations were not created intentionally in order to demonstrate that it is also possible do it so from PowerPivot, as discussed below.

To let PowerPivot automatically detect the needed connection, we will click the "Create" button next to the notice displayed on the panel fields. As a result, a window will open to create the appropriate relationship, offering additional information on it through the "Details" links.

After relationship creation, it will be applied immediately on the pivot table without any user intervention, so it will properly display measure values grouped by age.

It's also possible to manually create relationships between tables in the data model using the PowerPivot working window. To do this we will click the "Create Relationship" option, that belongs to the "Relationships" group, which is located in the "Design" tab of that window.

This will open a window which will select the table and column representing the source and destination of the relationship.

This way we will establish two new relationships, which have the Poblacion table as the source and Sexo and Zona tables as a destination. Additionally, using "Manage Relationships" we can see a summary of the relationships created and manage them (create, edit, delete, etc.).

These changes just make in the PowerPivot window may affect directly or indirectly the data we are presenting in the pivot table; for that reason, when we return to the Excel window, will see a notice on the field panel, where we click the "Refresh" button to update the data we're working with.

Adding data to the column axis

Fixed the relationships problem, now is time to add new data in the pivot table columns through Sexo_DS field from Sexo table.

When selected, this field is placed by default on the "Row Labels" blocks so we'll have to manually move it to "Column Labels" block, or directly drag it to the label column block.

Additionally, we will format the numeric cells either by right-clicking any of them and selecting "Number Format" in the Format dialog box, where we'll define it without decimal and with thousands separator.

After these operations, the PivotTable will already display population data according to the requirements posed.

Now we conclude the first part of this article. In the next installment we will reach our goal of creating a chart that represents the population pyramid using the data we have just prepared with PowerPivot.

Superposed population pyramids with PowerPivot

Luis Miguel Blanco — Thu, 15 Mar 2012 22:22:00 GMT

In the articles that were dedicated to the creation of a population pyramid using PowerPivot ( part1 and part2 ), we saw the power that this add-on for Excel provides to users responsible for conducting information analysis on a database of demographic content.

Continuing the line of work depicted in those articles, this time we will address the development of superposed population pyramids, one aspect of the work with population pyramids, which is to observe and analyze the differences by sex and age structure between two populations by direct comparison of their respective pyramids.

Before proceeding I want to reiterate my thanks to Ricard Gènova Maleras, a demographer specializing in population health analysis, belonging to the Reports and Studies Health Service (Directorate of Health Promotion and Prevention, DG Primary Care, Health Ministry CM) for their invaluable review work and guidance on all those concepts about creating population pyramids, which have proved so valuable in the development of this and previous articles we have published about this topic in the blog.

Brief introduction to the superposed population pyramids

If we want to graphically describe this working scenario, firstly, we take the population pyramids to compare ...

... and then place a pyramid on the other, so that we can measure and analyze population differences between them, ending the operation.

If we are managing two static populations, the task is more complex than solely fill the cells in the Excel sheet with the corresponding population figures, and prepare the pyramid-shaped chart that combines the values. However, suppose we have a population base or reference, belonging to all the individuals from a region, and we want to make comparisons between this amount and a classification of the subgroups in which this population can be divided, for example, the geographic or health care areas.

If the number of zones is very high, this will increase the amount of calculations and subgroups pyramids that we will need developing to compare with the main reference pyramid, increasing the complexity of demographic analysis to be performed.

At this point of the problem, PowerPivot enters the arena, because if we use to manage population data we must work with, greatly simplify the development of population pyramids and subsequent analysis phase.

Taking advantage of the pyramid developed in the items listed above, and reflected in the PiramidePoblacion.xlsx file, we will use that work setting as a starting point to develop, in first place, the population pyramids of different health areas, and continue with the reference pyramid creation, that depicts the Community of Madrid population.

The zoning pyramids

At first glance, this task may seem the most complicated, because its purpose is to create a pyramid for each one of the health area of the population we are dealing with.

Nothing is further from reality however, because if we followed the early commented articles about pyramids creation, we will have realized that we already have that work done in the PiramidePoblacion.xlsx file, created as an exercise of such of those articles, due to the use of a slicer based on the table Zona, from the PowerPivot data model.

However, in order to reorganize the names we use in this article, we will remove the measures and pyramid chart we had originally created for the PivotTable. Also change in the window PowertPivot, the table name Poblacion to PoblacionZonas, by right-clicking on the tab for the table, and choosing the menu option "Rename".

Next we will create again the measures for the PoblacionZonas table with the following names and expressions in DAX language.

/ / PoblacionZonasSuma

= SUM ([Sexo_Codigo])

/ / PoblacionZonasTotal

= CALCULATE (COUNTROWS (PoblacionZonas), ALL (Edad), ALL (Sexo))

/ / PoblacionZonasPorcentaje

= [PoblacionZonasSuma] / [PoblacionZonasTotal]

The way to create and configure the pyramid chart for these populations will be the same as in the articles already mentioned, except the edge of the bars, which in this case be assigned the color white, with a width of 2 points between thereof. Let's recall that to format the bars will right click on any of them, selecting the menu option "Format Data Series".

In the figure below we can see the chart with the changes just discussed (including the slicer by the field Zona_DS from Zona table), showing the pyramid from one of the available areas.

The reference population pyramid

The next step is to add to the current chart the pyramid representing the total population, so we will place on the PowerPivot window, where we will repeat the process of adding the Poblacion table from source database to the PowerPivot data model, but this time giving the name PoblacionReferencia to the PowerPivot table, and also creating the calculated column Sexo_Codigo using the following DAX expression: "= IF ([Sexo_ID] =" M ", 1, -1)" cause we need to sum up the population according to sex of individuals.

After this operation we will create the necessary relationships between both population tables and the rest, obtaining the result shown in the figure below.

Then return to the Excel window, where we click the "Refresh" button in the "PowerPivot Field List" window to refresh its content, and incorporate the PoblacionReferencia table that we just added to the PowerPivot window.

With respect to the necessary measures to generate the pyramid corresponding to this reference population, the following code block shows the DAX expressions used for its creation.

/ / PoblacionReferenciaSuma

CALCULATE = (SUM ([Sexo_Codigo]), ALL (Area))

/ / PoblacionReferenciaTotal

= CALCULATE (COUNTROWS (PoblacionReferencia), ALL (Edad), ALL (Sexo), ALL (Area))

/ / PoblacionReferenciaPorcentaje

= [PoblacionReferenciaSuma] / [PoblacionReferenciaTotal]

Comparing the measures from the tables PoblacionReferencia and PoblacionZonas, we find in those a significant difference in the operations involved in sum and calculate the total population, consisting in the use of the ALL function applied to the Zona table, which is passed as parameter. Into the measure PoblacionReferenciaSuma we will use, furthermore, the CALCULATE function, so that the addition operation on the reference population table is carried out correctly.

The ALL function, when operating in a DAX expression used to construct a calculated measure that we use, for example, in a PivotTable, cancels those filters applied to the PivotTable, which are related to table passed as a parameter to the ALL function, and in our case, as mentioned above, affects the Zona table.

To verify this behavior, that will result in the pyramid of reference at the same time, in the PowerPivot fields window, drag the PoblacionReferenciaPorcentaje field to the Values pane. This will result in new columns insertion into the pivot table for the measure, and bars into the pyramid chart to represent the new population figures.

As a next step apply the percentage format to the new columns of figures from the PivotTable, and then do right click on one of the new bars from the population chart, selecting the "Format Data Series"; this action will open the window of the same name, where we modify the following properties / values.

- Filling: No Fill.

- Border Color: solid line, color black.

- Border Styles: width of 1 point.

Repeating the same operation on the other bar chart data will complete the visual configuration of the new reference population pyramid, which will stay superposed on the zone pyramid, thus achieving our goal. This result allows us to appreciate the differences between the two types of people, facilitating their analysis.

At this point we conclude this article about construction of population pyramids superposed using PowerPivot as a tool to model our data. In a future article will discuss the development of accumulated pyramids, a variety of population pyramid in which the pyramid bars provide more information, because they allow the inclusion of an additional category of classification.

Pirámides de población superpuestas con PowerPivot

Luis Miguel Blanco — Thu, 19 Jan 2012 22:20:00 GMT

En los artículos que dedicábamos a la creación de una pirámide de población empleando PowerPivot (parte1 y parte2), pudimos comprobar la potencia que este complemento para Excel proporciona a aquellos usuarios encargados de realizar determinados análisis de información sobre una base de datos de contenido demográfico.

Continuando la línea de trabajo abierta en aquellos artículos, en esta ocasión abordaremos el desarrollo de pirámides de población superpuestas, un aspecto del trabajo con pirámides demográficas, que consiste en observar y analizar las diferencias de estructura por sexo y edad existentes entre dos poblaciones, mediante la comparación directa de sus respectivas pirámides.

Antes de proseguir quisiera reiterar mi agradecimiento a Ricard Gènova Maleras, demógrafo especializado en análisis poblacionales sanitarios, perteneciente al Servicio de Informes de Salud y Estudios (Subdirección de Promoción de la Salud y Prevención, DG Atención Primaria, Consejería Sanidad CM), por su inestimable labor de revisión y orientación en todos aquellos conceptos sobre creación de pirámides demográficas, que tan valiosos han resultado en la elaboración del presente y los anteriores artículos que hemos publicado acerca de este mismo tema en el blog.

Breve introducción a las pirámides de población superpuestas

Si queremos describir gráficamente este escenario de trabajo, en primer lugar tomaremos las pirámides de las poblaciones a comparar...

...y a continuación colocaremos una pirámide sobre la otra, de forma que podamos apreciar y analizar las diferencias de población existentes entre ambas, finalizando así la operación.

Si se trata de dos poblaciones estáticas, la tarea no reviste mayor complejidad que la de rellenar las celdas de la hoja Excel con las cifras de las poblaciones correspondientes, y preparar el gráfico con forma de pirámide que combine los valores. Sin embargo, supongamos que tenemos una población base o de referencia, perteneciente al total de individuos de una región, y queremos establecer comparaciones entre ese total y una clasificación de los subgrupos en que dicha población puede ser dividida, como por ejemplo, las zonas geográficas o de atención sanitaria.

Si el número de zonas es muy elevado, aumentará la cantidad de cálculos y pirámides de subgrupos que necesitaremos elaborar para comparar con la pirámide principal de referencia, incrementándose la complejidad del análisis demográfico a realizar.

Es en este punto del problema donde entra en liza PowerPivot, ya que si lo utilizamos para gestionar los datos de las poblaciones con las que debemos trabajar, simplificaremos en gran medida la elaboración de las pirámides de población, así como su posterior fase de análisis.

Aprovechando la pirámide desarrollada en los artículos mencionados anteriormente, y plasmada en el archivo PiramidePoblacion.xlsx, utilizaremos dicho escenario de trabajo como punto de partida para elaborar, en primer lugar, las pirámides de población de las distintas zonas sanitarias; pasando a continuación a crear la pirámide de referencia, que representa a la población de la Comunidad de Madrid.

Las pirámides de zonificación

A simple vista, esta tarea puede parecer la más complicada, debido a que el objetivo de la misma consiste en crear una pirámide por cada una de las zonas sanitarias de la población que estamos manejando.

Nada más lejos de la realidad sin embargo, puesto que si hemos seguido los artículos ya comentados sobre creación de pirámides, nos habremos dado cuenta de que ese trabajo ya lo tenemos hecho en el archivo PiramidePoblacion.xlsx, creado como ejercicio de ejemplo de dichos artículos, gracias al empleo de una segmentación basada en la tabla Zona del modelo de datos de PowerPivot.

No obstante, con el fin de reorganizar los nombres que utilizaremos en este artículo, vamos a eliminar las medidas y el gráfico de la pirámide que inicialmente habíamos creado para la tabla dinámica. También cambiaremos, en la ventana de PowertPivot, el nombre de la tabla Poblacion por PoblacionZonas, haciendo clic derecho en la pestaña correspondiente a la tabla, y eligiendo la opción de menú "Cambiar nombre".

A continuación crearemos de nuevo las medidas de la tabla PoblacionZonas, con los siguientes nombres y expresiones en lenguaje DAX.

// PoblacionZonasSuma

=SUM([Sexo_Codigo])

// PoblacionZonasTotal

=CALCULATE(COUNTROWS(PoblacionZonas),ALL(Edad),ALL(Sexo))

// PoblacionZonasPorcentaje

=[PoblacionZonasSuma] / [PoblacionZonasTotal]

La forma de crear y configurar el gráfico de pirámide para estas poblaciones será la misma que en los artículos ya indicados, con la excepción del borde de las barras, a las que en esta ocasión asignaremos el color blanco, con un ancho de 2 puntos entre las mismas. Recordemos que para dar formato a las barras haremos clic derecho en cualquiera de ellas, seleccionando la opción de menú "Dar formato a serie de datos".

En la siguiente figura podemos observar el gráfico con las modificaciones que acabamos de comentar (incluyendo la segmentación por el campo Zona_DS de la tabla Zona), mostrando la pirámide de una de las zonas disponibles.

La pirámide de referencia

El siguiente paso consistirá en añadir al gráfico actual la pirámide que representará al total de la población, para lo cual nos situaremos en la ventana de PowerPivot, donde repetiremos el proceso de agregación de la tabla Poblacion desde la base de datos origen hasta el modelo de datos de PowerPivot; pero en esta ocasión daremos el nombre PoblacionReferencia a la tabla en PowerPivot, creando también la columna calculada Sexo_Codigo con la expresión DAX: "=IF([Sexo_ID] = "M", 1, -1)", ya que igualmente necesitaremos sumar la población en función del sexo de los individuos.

Tras esta operación crearemos las oportunas relaciones entre ambas tablas de población y el resto, quedando el resultado mostrado por la siguiente figura.

A continuación volveremos a la ventana de Excel, donde haremos clic en el botón "Actualizar" de la ventana "Lista de campos de PowerPivot", para que se refresque su contenido, y se incorpore la tabla PoblacionReferencia que acabamos de agregar a la ventana de PowerPivot.

En cuanto a las medidas necesarias para generar la pirámide correspondiente a esta población de referencia, en el siguiente bloque de código se muestran las expresiones DAX a utilizar para su creación.

// PoblacionReferenciaSuma

=CALCULATE(SUM([Sexo_Codigo]),ALL(Zona))

// PoblacionReferenciaTotal

=CALCULATE(COUNTROWS(PoblacionReferencia),ALL(Edad),ALL(Sexo),ALL(Zona))

// PoblacionReferenciaPorcentaje

=[PoblacionReferenciaSuma] / [PoblacionReferenciaTotal]

Comparadas las medidas de la tabla PoblacionZonas y PoblacionReferencia, encontramos en estas últimas una importante diferencia dentro de las operaciones encargadas de sumar la población y calcular su total, consistente en el uso de la función ALL aplicada a la tabla Zona, la cual es pasada como parámetro. En la medida PoblacionReferenciaSuma utilizaremos, además, la función CALCULATE, para que la operación de suma sobre la tabla de población de referencia se efectúe correctamente.

La función ALL, cuando interviene en una expresión DAX utilizada para construir una medida calculada que empleamos, por ejemplo, en una tabla dinámica, anula aquellos filtros aplicados en la tabla dinámica, que estén relacionados con la tabla pasada como parámetro a la función ALL, y que en nuestro caso, como ya hemos mencionado, afecta a la tabla Zona.

Para comprobar este comportamiento, que al mismo tiempo dará como resultado la pirámide de referencia, en la ventana de campos de PowerPivot arrastraremos el campo PoblacionReferenciaPorcentaje hasta el panel Valores. Esto producirá la inserción, en la tabla dinámica, de nuevas columnas para dicha medida; y en el gráfico de pirámide, de barras para representar las nuevas cifras de población.

Como siguiente paso aplicaremos el formato de porcentaje a las nuevas columnas de cifras de la tabla dinámica, y a continuación haremos clic derecho en una de las nuevas barras de población del gráfico, seleccionando la opción "Dar formato a serie de datos"; acción esta que abrirá la ventana del mismo nombre, donde modificaremos las siguientes propiedades/valores.

--Relleno: Sin Relleno.

--Color del borde: Línea sólida, color negro.

--Estilos de borde: Ancho de 1 punto.

Repitiendo esta misma operación sobre la otra barra de datos del gráfico completaremos la configuración visual de la nueva pirámide de población de referencia, que quedará superpuesta a la pirámide por zonas, logrando así nuestro objetivo. Este resultado nos permitirá apreciar las diferencias entre los dos tipos de población, facilitando su análisis.

Llegados a este punto damos por concluido el presente artículo sobre construcción de pirámides de población superpuestas, empleando PowerPivot como herramienta para modelar nuestros datos. En un próximo artículo abordaremos el desarrollo de pirámides acumuladas, una variedad de pirámide poblacional en la que las barras de la pirámide aportan mayor información, gracias a que permiten la inclusión de una categoría adicional de clasificación.

Pirámides de población con PowerPivot. Creación del gráfico (y 2)

Luis Miguel Blanco — Sun, 21 Aug 2011 17:58:00 GMT

En la primera parte de este artículo desarrollábamos un modelo de datos en PowerPivot que representaba las cifras de una población por edad y sexo. En esta segunda entrega plasmaremos dichas cifras en un gráfico con forma de pirámide de población.

Gráfico de pirámide. Primer acercamiento

En su estado actual, la tabla dinámica ya tendría la información suficiente (cifras de población, edad y sexo) como para intentar crear un gráfico que represente una pirámide de población; aunque adelantamos al lector que en esta primera aproximación no vamos a conseguir el efecto deseado.

Posicionados en la tabla dinámica, dentro de la cinta de opciones de Excel seleccionaremos la opción "Gráfico dinámico", perteneciente al grupo "Herramientas" de la ficha "Opciones", que a su vez está contenida en la ficha de nivel superior "Herramientas de tabla dinámica".

Esta selección abrirá la ventana "Insertar gráfico", que contiene el conjunto de tipos de gráfico disponibles. Aquí nos percataremos de que no existe una plantilla específica para crear un gráfico de pirámide; por lo tanto, entre toda la oferta a nuestra disposición elegiremos, dentro de la categoría "Barra", el tipo "Barra agrupada", que como veremos más adelante será el que mejor se adaptará al resultado que queremos conseguir.

Aceptando esta ventana, el gráfico será creado a partir de los datos de la tabla dinámica, y tal y como habíamos anticipado, el resultado no se parecerá a la imagen presentada al principio del artículo.

No obstante, la principal diferencia radica en el dibujo de la barra correspondiente a la población de hombres, cuyo sentido debería ser hacia la izquierda; el resto de aspectos son básicamente cuestiones de configuración visual, que posteriormente explicaremos cómo resolver.

Solucionando la trayectoria de las barras de población

Centrándonos en la barra de población masculina, la solución para conseguir que se dibuje en dirección opuesta a la que actualmente tiene, consiste en poner en negativo los valores de las celdas de la tabla dinámica correspondientes a este segmento de la población.

Si nos encontráramos en una hoja de cálculo simple, sin conexión a PowerPivot, la solución sería tan sencilla como editar las celdas de la columna Hombre, pasando sus valores a negativo, pero estamos en un escenario de trabajo en el que los datos están siendo obtenidos desde el modelo de datos de PowerPivot, por lo que no es posible editar directamente los valores de la tabla dinámica.

Para solucionar esta clase de problemas tendremos que recurrir a la creación de columnas y/o medidas calculadas, que a través de expresiones DAX proporcionen los resultados que necesitamos. En el caso del paso a negativo de los valores de la columna Hombre, abriremos la ventana de PowerPivot, y situándonos en la primera columna vacía disponible en la tabla Población, escribiremos la siguiente expresión en la barra de fórmulas:

=IF([Sexo_ID] = "M", 1, -1)

Acabamos de crear una columna calculada que se evaluará para cada fila de la tabla Población, comprobando si el valor del campo Sexo_ID es igual a la letra "M", en caso afirmativo, el valor de la columna en dicha fila será 1, en caso contrario (cuando el campo contenga "H") el valor devuelto será -1.

Seguidamente haremos doble clic sobre su cabecera para asignarle el nombre Sexo_Codigo. También podemos darle nombre haciendo clic derecho sobre la cabecera y eligiendo la opción "Cambiar nombre de columna".

Volviendo nuevamente a la ventana de Excel, eliminaremos el gráfico que habíamos creado en la hoja y desmarcaremos la medida RecuentoPoblacion, quedando vacía la zona de valores de la tabla dinámica.

El próximo paso a dar consistirá en crear una nueva medida con el nombre SumaPoblacion, basada en la siguiente expresión DAX:

=SUM( [Sexo_Codigo] )

Al aplicar esta medida a la tabla dinámica, la función SUM realiza la suma de los valores de la columna pasada como parámetro, por lo que las cifras de la población de hombres ya aparecerán en negativo. Esto significa que al volver a crear el gráfico en el modo antes explicado, las barras indicadoras de los valores de población por sexo se dibujarán ahora en direcciones opuestas. Como detalle adicional, en la ficha "Herramientas de tabla dinámica", dentro de la ficha "Diseño", en el grupo "Diseño" desplegaremos la opción "Totales generales", seleccionando el valor "Activado sólo para columnas", que ocultará la columna de totales de fila, ya que su presencia en este contexto resulta irrelevante.

Configuración visual de las barras de población

Aunque las barras del gráfico ya se muestran según el efecto que nos habíamos propuesto como objetivo, sería deseable que su aspecto visual recibiera algunos retoques para mejorar la calidad de su presentación.

En primer lugar haremos clic derecho sobre las etiquetas de los rangos de edad, seleccionando la opción "Dar formato a eje". En la ventana del mismo nombre, dentro del apartado "Opciones del eje", asignaremos el valor "Bajo" a la propiedad "Etiquetas del eje", lo que tendrá como resultado que esta columna de etiquetas quede alineada a la izquierda del gráfico.

A continuación haremos clic derecho sobre cualquiera de las barras del gráfico, eligiendo la opción "Dar formato a serie de datos". En la ventana de configuración de la serie, dentro del apartado "Opciones de la serie", en la propiedad "Superposición de series", desplazaremos el marcador de posición hasta el extremo derecho (totalmente superpuesta), mientras que en la propiedad "Ancho del intervalo" desplazaremos el marcador de posición hasta el extremo izquierdo (sin intervalo en absoluto). De esta forma conseguiremos que las barras aumenten su grosor y eliminen el espacio intermedio entre las mismas, quedando completamente unidas para formar la pirámide de población.

Calculando la población en porcentajes

La representación de los datos obtenida hasta este momento, tanto en la tabla dinámica como en el gráfico de pirámide, se basa en cifras absolutas de población. Sin embargo, lo habitual y recomendable es que dicha representación se realice como proporción de cada grupo de edad y sexo sobre el total de la población.

Por ejemplo, en nuestra tabla dinámica, la población de mujeres con edades comprendidas entre los 55 y 59 años es de 184.888 personas; para obtener el porcentaje que este grupo de población constituye en relación al total de individuos con el que estamos trabajando (6.458.684), dividiremos el grupo entre el total, y formatearemos el resultado como porcentaje, obteniendo 2,86 %.

Si queremos que la tabla dinámica realice esta operación para todos los grupos de población tendremos que añadir cálculos adicionales en forma de medidas, pero antes de eso eliminaremos el actual gráfico de población, ya que volveremos a construirlo a partir de una de las nuevas medidas.

Partimos de la existencia de una medida, SumaPoblacion, que como ya sabemos, devuelve la cifra de población sumando el campo Sexo_Codigo. El siguiente paso consistirá en crear una nueva medida, que al incluirse en la tabla dinámica, proporcione el total de población en todas las celdas.

Nuestra primera reacción podría ser volver a utilizar la medida RecuentoPoblacion, creada en las fases iniciales de nuestro ejemplo, pero pronto nos daremos cuenta de que no sirve para este propósito, ya que aunque esta medida cuenta las filas de la tabla Población, los resultados de las celdas se ven afectados por los campos utilizados en filas y columnas, así como otros filtros que pudiera tener activos la tabla dinámica.

Para que una medida cuente siempre todas las filas de una tabla, sin importar los filtros que pueda haber activos, utilizaremos la función CALCULATE, a la que pasaremos como primer parámetro la operación a realizar, en este caso el recuento de filas de la tabla Población mediante la función COUNTROWS. A continuación pasaremos tantos parámetros como filtros queramos eliminar, utilizando la función ALL(NombreTabla) por cada tabla que de alguna manera esté actuando como filtro.

Bajo tales premisas crearemos una nueva medida llamada TotalGlobalPoblacion, con la siguiente expresión DAX:

=CALCULATE( COUNTROWS( Poblacion ), ALL( Edad), ALL( Sexo ) )

Al aplicar sobre la tabla dinámica esta medida, todas sus celdas mostrarán el mismo valor: el total de la población.

Ahora necesitamos una tercera medida que haga la división entre las dos anteriores y muestre el resultado en formato porcentaje. Esta nueva medida tendrá el "original" nombre de PorcentajePoblacion y utilizara la siguiente fórmula:

=[SumaPoblacion] / [TotalGlobalPoblacion]

Como ya vimos en la primera entrega del artículo, para aplicar el formato a esta medida haremos clic derecho en una de sus celdas eligiendo la opción "Formato de número". En la ventana de formato seleccionaremos esta vez la categoría "Personalizada", a través de la cual introduciremos en el campo "Tipo" la siguiente cadena de formato propia.

0,00%;0,00%

Esta cadena, además de formatear el número como porcentaje, mostrará sin el signo negativo los valores de la columna de población masculina, aunque internamente, dichos valores seguirán siendo negativos.

A continuación desactivaremos todas las medidas de la tabla dinámica a excepción de PorcentajePoblación, que será la única que permanecerá visible. Seguidamente volveremos a añadir un gráfico de barras agrupadas, en el que además de utilizar los pasos de configuración explicados con anterioridad, agregaremos nuevas características de formato para mejorar su presentación.

Aplicar formato de porcentaje al eje horizontal

En primer lugar haremos clic derecho en el eje horizontal de etiquetas seleccionando la opción "Dar formato a eje", que abrirá la ventana de formato, en cuyo apartado "Número" seleccionaremos la categoría de formato "Personalizado" y en el campo "Código de formato" escribiremos la siguiente cadena de formato:

0%;0%

Haciendo clic en el botón "Agregar", la cadena se añadirá a la lista de cadenas personalizadas, de modo que al aceptar la ventana se aplicará el formato al eje horizontal de etiquetas.

Remarcando los bordes de las barras de población

Seguidamente haremos clic sobre alguna de las barras del gráfico, volviendo a elegir la opción "Dar formato a serie de datos". En esta ocasión, dentro del apartado "Color del borde" haremos clic en la opción "Línea sólida", seleccionando el color negro; mientras que en el apartado "Estilos del borde" asignaremos a la propiedad "Ancho" el valor "2 pto". Esta operación la realizaremos para ambos grupos de barras del gráfico.

Después haremos clic derecho sobre las etiquetas de los rangos de edad, seleccionando la opción "Dar formato a eje". En esta ventana de formato asignaremos los mismos valores para las propiedades de color y estilo de borde que acabamos de utilizar para las barras del gráfico.

Como resultado de estas acciones, el gráfico mostrará sus bordes con un contorno claramente remarcado.

Reubicando la posición de la leyenda

En el momento de crear el gráfico, Excel sitúa por defecto la leyenda (campo Sexo_DS) en el lado derecho. No obstante, es posible cambiar la ubicación de este elemento si queremos proporcionar más espacio al dibujo de las barras de población. Para ello, haremos clic derecho en la leyenda y seleccionaremos la opción "Formato de leyenda", en la ventana de formato, dentro del apartado "Opciones de leyenda", haremos clic en la opción "Superior".

Como podemos apreciar, el gráfico ha ganado en superficie de dibujo, pero los indicadores de la leyenda han quedado colocados en orden inverso a las barras. Para solucionar este problema haremos clic en el botón de campos de la leyenda (Sexo_DS), de forma que despleguemos sus opciones de filtro, donde seleccionaremos "Ordenar de Z a A".

Con esta acción, los indicadores de leyenda quedarán colocados adecuadamente, pero ahora nos encontraremos con el inconveniente de que los colores de las barras han quedado invertidos, y hemos perdido el borde de las barras del gráfico.

Restauraremos los bordes de las barras en la manera explicada anteriormente, mientras que en cuanto a los colores, para cada lado de la pirámide haremos clic derecho en una de las barras, y en la opción "Relleno de forma", cambiaremos el color actual por el que originalmente tenía el gráfico.

Para completar los ajustes que estamos realizando sobre la leyenda, arrastraremos ésta hasta que quede situada al nivel del elemento superior de la pirámide, y aumentaremos su anchura, para que los indicadores queden más separados.

Despejar el área de campos y agregar título

Seguidamente haremos clic derecho en cualquiera de los botones de campos del gráfico y seleccionaremos la opción "Ocultar todos los botones de campos en gráfico". Con este movimiento impediremos que el usuario aplique filtros sobre el eje horizontal y/o vertical de la pirámide, manteniendo su estructura consistente y evitando así la posibilidad, por ejemplo, de ocultar rangos de edad o alguno de los sexos. No obstante, seguirá existiendo tal posibilidad de filtro desde la tabla dinámica.

Por otra parte, en el grupo de fichas "Herramientas del gráfico dinámico" haremos clic en la ficha "Presentación", y dentro del grupo "Etiquetas" haremos clic en la opción "Título del gráfico", que desplegará una serie de elementos entre los que elegiremos "Encima del gráfico", añadiéndose un cuadro de texto al gráfico, que editaremos para asignarle un título. Llegados a este punto habremos completado el desarrollo de nuestra pirámide de población.

Dinamizando los datos de la pirámide mediante segmentaciones

Aunque hemos logrado el objetivo propuesto de crear una pirámide de población, resultaría interesante dotarla de cierto valor añadido, tal y como vamos a hacer en este apartado.

Si observamos el modelo de datos en la ventana de PowerPivot, caeremos en la cuenta de que todavía no hemos hecho uso de la información que sobre zonificación sanitaria existe en el mismo, por lo que podemos aprovechar estos datos para construir un filtro que muestre la pirámide en base a la población de una o varias de estas zonas sanitarias.

Las tablas dinámicas de PowerPivot, además del filtro tradicional, incorporan un nuevo tipo de filtro denominado "segmentación", que además de la funcionalidad habitual de filtrado ofrece una interfaz de usuario más flexible para la manipulación de los valores a tratar.

Vamos a crear en nuestra pirámide una segmentación basada en la información de zona. Para ello, en el panel de la lista de campos, arrastraremos el campo Zona_DS de la tabla Zona hasta el bloque "Segmentaciones de datos horizontales", obteniendo como resultado una segmentación situada encima de la tabla dinámica y el gráfico de pirámide.

Para filtrar los datos mediante la segmentación simplemente tenemos que seleccionar el nombre de la zona sanitaria que queramos emplear como filtro. También es posible filtrar simultáneamente varias zonas manteniendo pulsada la tecla Ctrl, mientras vamos haciendo clic en las zonas a filtrar (como muestra la siguiente figura). Si queremos eliminar todos los filtros activos haremos clic en el icono situado a tal efecto en la parte superior derecha de la segmentación.

Y llegados a este punto damos por finalizado el artículo, en el que a lo largo de sus dos entregas hemos mostrado el modo de construir una pirámide de población en Excel 2010, utilizando PowerPivot como herramienta de gestión de los datos poblacionales. No obstante, la potencia de esta tecnología va más allá del mero tratamiento de la información demográfica, abarcando su campo de acción a cualquier entorno en el que tengamos que realizar un análisis con elevados volúmenes de datos.

Pirámides de población con PowerPivot. Preparación del entorno de datos (1)

Luis Miguel Blanco — Thu, 18 Aug 2011 19:13:00 GMT

Una pirámide poblacional es una herramienta que nos permite analizar el estado y evolución de una población en función de su edad y sexo. Se trata de un elemento característico en demografía y estadística, aunque sus aplicaciones también abarcan campos tales como el sanitario, educativo, comercial, etc. Es por ello, que su integración en un sistema de información perteneciente a alguna de las áreas recién mencionadas, supone un enriquecimiento sustancial en la calidad de los resultados obtenidos por los usuarios de tales sistemas.

En el presente artículo abordaremos la construcción de pirámides de población utilizando PowerPivot, un complemento para Excel 2010, que permite el acceso a orígenes de datos de gran volumen, así como su posterior manejo y análisis.

Como ya apuntábamos en el artículo Generación de datos demográficos desde SQL Server, publicado en este mismo blog, nuestro objetivo actual (utilizando la base de datos PiramidePoblacion creada en el mencionado artículo) consistirá en construir un gráfico que represente una pirámide de población similar a la que vemos en la siguiente figura.

La primera parte del artículo estará dedicada a la preparación del modelo de datos de PowerPivot: conexión contra un origen de datos, carga y creación de una tabla dinámica para analizar las cifras de población. La segunda parte la destinaremos a la construcción del gráfico que represente a la pirámide de población, partiendo de los datos sobre los que hemos trabajado en la primera entrega.

Al igual que en el artículo mencionado anteriormente, quiero agradecer nuevamente a los integrantes del Servicio de Informes de Salud y Estudios (Subdirección de Promoción de la Salud y Prevención, DG Atención Primaria, Consejería Sanidad CM): Jenaro Astray Mochales, María Felicitas Domínguez Berjón, María Dolores Esteban Vasallo, Beatriz Elvira Rodríguez y en especial a Ricard Gènova Maleras, el soporte y orientación recibido sobre los conceptos demográficos necesarios con los que poder desarrollar apropiadamente una pirámide de población utilizando Excel 2010 en combinación con PowerPivot. Además de todo esto, Ricard ha accedido muy gustosamente a realizar una estupenda labor de revisión de este artículo, con lo que el agradecimiento va por partida doble.

Igualmente quisiera expresar mi agradecimiento a Enrique Barceló por todos sus estupendos trucos sobre desarrollo OLAP, que comparte con el equipo de trabajo, y que hacen un poco más fácil nuestra labor de desarrollo en estas tareas del Business Intelligence.

Un poco de teoría

Pero antes de entrar de lleno en su proceso de creación, vamos a proporcionar unos breves apuntes teóricos sobre las pirámides de población, que nos permitan entender mejor cuáles son sus principales características y la información que de ellas podemos obtener y analizar.

En esencia, una pirámide de población es un doble histograma que representa la distribución por edad y sexo de los efectivos de una población, bien en cifras absolutas, bien como porcentaje sobre la población total.

Si estamos interesados en obtener una información más detallada acerca de los aspectos conceptuales que rodean a las pirámides de población, la ayuda del programa Epidat 4.0 puede resultarnos de utilidad. Se trata de una aplicación desarrollada por la Consejería de Sanidad de la Xunta de Galicia y la Organización Panamericana de la Salud (OPS, Washington), de cuya documentación citamos a continuación algunos de los puntos más importantes en relación con el tema que nos ocupa. En primer lugar comenzaremos con algunas nociones relativas a la construcción de la pirámide de población.

"El mejor modo de visualizar la distribución por sexo y edad de una población es, sin duda, la pirámide de población, verdadero icono de la demografía. Una pirámide es un doble histograma que permite, con un solo golpe de vista, hacerse una idea clara no sólo acerca de las características generales de la población (joven, envejecida, con algún desequilibrio en función del sexo) sino también sobre particularidades específicas que remiten a algún acontecimiento concreto concerniente a la población objeto de estudio.

Usualmente las pirámides de población se construyen siguiendo varias convenciones y reglas:

- representar a los hombres a la izquierda y a las mujeres a la derecha del eje central,

- ubicar las edades de manera que cuanto más bajas sean, más cerca estén de la base y viceversa,

- mantener cierta proporcionalidad entre base y altura (3 anchuras por 2 alturas, o 4 por 3, aproximadamente),

- respetar la misma escala a ambos lados del eje central (para favorecer la comparación entre sexos),

- representar el peso de cada intervalo etario por la superficie de cada barra del histograma, no por su longitud, algo especialmente importante cuando se trabaja con grupos de edad desiguales,

- pueden construirse con valores absolutos, pero es preferible hacerlo con las proporciones de cada edad y sexo respecto de la población total.

El último punto es importante y diferencia a la pirámide de los indicadores de estructura [proporción de jóvenes o ancianos, índice de envejecimiento, etc.], que se calculan separadamente sobre el total de cada sexo respectivo. En el caso de la pirámide de población, usar como denominador de las proporciones a la población total de ambos sexos reunidos garantiza la comparabilidad no sólo de la distribución por edad, sino también por sexo".

Y finalizaremos con aquellas cuestiones relacionadas con la interpretación de la pirámide.

"Una pirámide por sexo y edad resume la historia demográfica de una población de por lo menos los cien años anteriores a la fecha de referencia (el tiempo que tarda, aproximadamente, una generación en pasar de la base a la cima de la pirámide). En ocasiones, el efecto indirecto de algunos acontecimientos demográficos es visible más allá de ese salto de un siglo (por ejemplo, el impacto del profundo descenso de la fecundidad de las últimas décadas experimentado por muchas poblaciones se apreciará, sin duda, en las pirámides de la primera mitad del siglo XXII). Una pirámide por edades simples [edad por edad: 0, 1, 2, 3 ... 99, 100 ...] permite un análisis más preciso que otra realizada por grupos de edades agregadas (quinquenales, decenales), pero corre también el riesgo de verse afectada por problemas de calidad de los registros, o ser vulnerable a la inestabilidad de las distribuciones en poblaciones pequeñas.

La primera percepción de una pirámide permite identificar los rasgos generales de la población representada: una pirámide de base ancha y que se estrecha rápidamente da idea de una población joven, con una alta proporción de niños y adolescentes, y baja de adultos y ancianos, resultado de una alta natalidad y mortalidad. Inversamente, una pirámide con perfil estrecho en la base y ancho en el centro y la cima representa una estructura madura o envejecida. Cuanto mayor es la esperanza de vida de una población, mayor suele ser la desigualdad por sexo en la cima de la pirámide (más llena por el lado de las mujeres, por su mayor nivel de supervivencia).

La pirámide no da respuestas por sí misma, sino que propicia que se planteen preguntas pertinentes. Las explicaciones para su perfil deben buscarse en la historia, en los avatares sociales, políticos, económicos, que generan consecuencias en el devenir demográfico -es decir, en la fecundidad, la mortalidad o la migración, que son los fenómenos que modelan el contorno y fijan el tamaño de una población- y que tienen una interpretación en función de la triple perspectiva temporal: edad, periodo y cohorte".

Incorporando los datos a PowerPivot

Gracias a VertiPaq, el motor de procesamiento de datos de PowerPivot, podemos conseguir una potencia y velocidad sin precedentes en la manipulación, filtrado, creación de expresiones de lógica de negocio, y en definitiva, en todas aquellas operaciones analíticas que debamos realizar sobre un modelo de datos creado con esta herramienta.

El siguiente enlace proporciona acceso a la página principal de PowerPivot, desde donde podremos descargarlo, para posteriormente instalarlo en nuestra máquina, de forma que podamos seguir los ejemplos del artículo.

Una vez que hayamos instalado PowerPivot iniciaremos Excel 2010, creándose una nueva hoja de cálculo a la que daremos el nombre PiramidePoblacion.xlsx. En la cinta de opciones dispondremos ahora de una nueva ficha con el nombre "PowerPivot". Al hacer clic en la misma seleccionaremos la opción "Ventana de PowerPivot", perteneciente al grupo "Iniciar", lo que abrirá la mencionada ventana de trabajo de PowerPivot, en la que definiremos la estructura de tablas que vamos a utilizar, o "modelo de datos", tal y cómo se denomina dentro del contexto de PowerPivot.

A continuación, en la pestaña "Página principal" de la ventana de PowerPivot, nos conectaremos a la base de datos mediante la opción "Desde base de datos | De SQL Server", situada en el grupo "Obtener datos externos".

Esta acción iniciará el asistente para importar tablas, en el que indicaremos el origen de datos al que nos queremos conectar.

Una fuente de datos válida para elaborar una pirámide de población ha de tener como mínimo información acerca del sexo y edad de la población, como es el caso de nuestra base de datos PiramidePoblacion. Adicionalmente, y como forma de enriquecer el análisis a realizar, la base de datos puede disponer de información complementaria como pudiera ser la zonificación sanitaria, nacionalidad de la población, etc.

Tras elegir la base de datos PiramidePoblacion, el siguiente paso nos ofrecerá una lista de las tablas y vistas que podremos importar, donde marcaremos las siguientes: Edad, Población, Sexo y Zona, comenzando la importación al hacer clic en el botón "Finalizar".

Si la importación se desarrolla correctamente, el asistente mostrara una ventana resumen del proceso.

Una vez terminada la incorporación de datos haremos clic en "Cerrar" para volver a la ventana de PowerPivot, donde hallaremos organizadas en diversas pestañas cada una de las tablas importadas.

Análisis mediante tabla dinámica

Antes de pasar a la fase de construcción de la pirámide de población, vamos a utilizar una tabla dinámica de PowerPivot para analizar las cifras del modelo de datos, por lo que situados en su ventana de trabajo haremos clic en la opción "PivotTable", perteneciente al grupo "Informes".

Esta acción nos posicionará en la ventana de Excel, donde se abrirá un diálogo en el que elegiremos la hoja de cálculo en la que se ubicará la tabla dinámica.

En nuestro caso seleccionaremos la primera opción y aceptaremos el diálogo, creándose la tabla dinámica en una nueva hoja cálculo

Desde el panel "Lista de campos de PowerPivot" tenemos a nuestra disposición los campos de las tablas del modelo de datos, que situaremos en las diferentes zonas de la tabla dinámica (etiquetas de filas, columnas, valores, etc.) para llevar a cabo nuestras operaciones de análisis. Utilizaremos para ello un estilo de trabajo muy semejante al que emplearíamos si estuviéramos consultando un cubo OLAP, ya que el manejo de los campos de PowerPivot en este sentido es similar al que podemos realizar con las dimensiones y medidas de un cubo de datos cuando es consultado desde Excel.

Vamos a comenzar por una consulta sencilla, consistente en contar los registros de la tabla Población, agrupando la información por rangos de edad, los cuales situaremos en las filas de la tabla dinámica.

El recuento de registros lo realizaremos mediante una expresión DAX (el lenguaje de consultas de PowerPivot) situada en una medida calculada, que crearemos seleccionando la opción "Nueva medida", perteneciente al grupo "Medidas" de la ficha "PowerPivot". Esta medida la aplicaremos sobre la tabla Población, por lo que deberemos estar previamente posicionados en la misma, dentro del panel de la lista de campos.

Al seleccionar esta opción se abrirá la ventana "Configuración de medida", en la que dentro del cuadro de texto reservado para la fórmula escribiremos la siguiente expresión:

=COUNTROWS(Poblacion)

La función COUNTROWS, como su nombre indica, cuenta las filas de la tabla pasada como parámetro. Para terminar la creación de nuestra medida le daremos el nombre "RecuentoPoblacion" y haremos clic en "Aceptar".

Nada más terminar de crear la medida, ésta se añadirá automáticamente a la lista de campos de la tabla Población y al bloque "Valores", visualizándose en la tabla dinámica el número total de filas de la tabla.

A continuación seleccionaremos, en el panel de la lista de campos, el campo Edad_Grupo de la tabla Edad, lo que situará automáticamente al mencionado campo en el bloque "Etiquetas de fila". Si este no fuera el comportamiento obtenido, arrastraremos manualmente el campo hasta colocarlo en dicho bloque.

Como consecuencia de la acción anterior la tabla dinámica se verá actualizada, mostrándose en el eje de las filas los valores del campo Edad_Grupo. Respecto a las cifras de la medida RecuentoPoblacion, éstas deberían repartirse entre los tramos de cada edad, para así reflejar el número de registros (población) correspondiente a cada uno de dichos tramos. Sin embargo, como vemos en la siguiente figura, es algo que no está ocurriendo, ya que la medida muestra el mismo valor para todas las filas, lo cual es incorrecto.

Relaciones entre las tablas del modelo

Observando el panel de campos vemos que PowerPivot ya se ha percatado del problema que acabamos de comentar, porque en la parte superior de dicho panel aparece un aviso, que nos informa acerca de que puede ser necesaria una relación entre las tablas que se están utilizando para componer la consulta de la tabla dinámica.

Este problema no habría tenido lugar si hubiesen existido las oportunas relaciones entre las tablas de la base de datos, aunque como ya dijimos en el artículo sobre la creación de la base de datos de población, dichas relaciones no fueron creadas intencionadamente, para así demostrar que también es posible hacerlo desde PowerPivot, como veremos a continuación.

Para dejar que PowerPivot detecte automáticamente la relación que necesita, haremos clic en el botón "Crear" que aparece junto al aviso mostrado en el panel de campos. Como resultado se abrirá una ventana encargada de crear la oportuna relación, ofreciéndonos información adicional sobre la misma a través de los enlaces "Detalles".

Una vez creada la relación, ésta será aplicada inmediatamente, sin intervención del usuario, sobre la tabla dinámica, que de esta forma ya mostrará correctamente los valores de la medida agrupados por edad.

También es posible crear manualmente las relaciones entre las tablas del modelo de datos utilizando la ventana de trabajo de PowerPivot. Para ello haremos clic en la opción "Crear relación", perteneciente al grupo "Relaciones", que está situado en la ficha "Diseño" de la mencionada ventana.

Esta opción abrirá una ventana con el mismo nombre, en la que seleccionaremos la tabla y columna que representarán el origen y destino de la relación.

De esta forma estableceremos dos nuevas relaciones, que tendrán a la tabla Población como origen y a las tablas Sexo y Zona como destino. Adicionalmente, desde la opción "Administrar relaciones" podemos ver un resumen de las relaciones creadas así como gestionarlas (crear, editar, eliminar, etc.).

Estos cambios que acabamos de realizar en la ventana de PowerPivot pueden afectar directa o indirectamente a los datos que estamos presentando en la tabla dinámica; por dicho motivo, al retornar a la ventana de Excel veremos un aviso al respecto en el panel de campos, donde haremos clic en el botón "Actualizar", para refrescar los datos con los que estamos trabajando.

Agregando datos en el eje de columnas

Solucionado el problema de las relaciones, ahora es el momento de añadir nuevos datos en las columnas de la tabla dinámica mediante el campo Sexo_DS de la tabla Sexo.

Al ser seleccionado, este campo se situará por defecto en el bloque "Etiquetas de fila", de modo que tendremos que moverlo manualmente hasta el bloque "Etiquetas de columna"; o bien al hacer clic en él lo arrastraremos directamente hasta el bloque de etiquetas de columna.

Adicionalmente, daremos formato a las celdas numéricas haciendo clic derecho en cualquiera de ellas y seleccionando la opción "Formato de número", en el cuadro de diálogo de formato definiremos éste sin decimales y con separador de miles.

Una vez realizadas estas operaciones, la tabla dinámica ya mostrará los datos de población de manera acorde a los requerimientos planteados.

Llegados a este punto concluimos la primera parte del artículo. En la siguiente entrega alcanzaremos nuestro objetivo de crear un gráfico que represente la pirámide de población utilizando los datos que hemos preparado con PowerPivot.