Excel, PowerPivot, SQL Server

Pirámides de población acumuladas con PowerPivot

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

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

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

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

PiramidesPoblacionAcumuladasPowerPivot_01

 

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

PiramidesPoblacionAcumuladasPowerPivot_02

 

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

PiramidesPoblacionAcumuladasPowerPivot_03

 

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

PiramidesPoblacionAcumuladasPowerPivot_04

 

La información a representar en cada segmento

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

PiramidesPoblacionAcumuladasPowerPivot_05

 

Ajustes sobre la estructura de la base de datos

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

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


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

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


ALTER TABLE Poblacion
ADD EstadoCivil_ID int NULL
GO

Actualizando la población con los datos de estado civil

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


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

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

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


UPDATE Poblacion
SET EstadoCivil_ID = 1
WHERE Edad_ID <= 20

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

PiramidesPoblacionAcumuladasPowerPivot_05B

 

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


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

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

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

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

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

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

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

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

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

 

El modelo de datos en PowerPivot

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

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

PiramidesPoblacionAcumuladasPowerPivot_06

 

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

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

PiramidesPoblacionAcumuladasPowerPivot_07

 

PiramidesPoblacionAcumuladasPowerPivot_07B

 

El gráfico con la pirámide acumulada

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

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

PiramidesPoblacionAcumuladasPowerPivot_08

 

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

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

PiramidesPoblacionAcumuladasPowerPivot_09

 

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

PiramidesPoblacionAcumuladasPowerPivot_10

 

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

PiramidesPoblacionAcumuladasPowerPivot_11

 

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

PiramidesPoblacionAcumuladasPowerPivot_12

 

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

PiramidesPoblacionAcumuladasPowerPivot_13

 

PiramidesPoblacionAcumuladasPowerPivot_14

 

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

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

PiramidesPoblacionAcumuladasPowerPivot_15

 

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

PiramidesPoblacionAcumuladasPowerPivot_16

 

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

PiramidesPoblacionAcumuladasPowerPivot_17

 

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

PiramidesPoblacionAcumuladasPowerPivot_18

 

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

PiramidesPoblacionAcumuladasPowerPivot_19

 

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

PiramidesPoblacionAcumuladasPowerPivot_20

 

Obtener diferentes perspectivas de la pirámide mediante segmentaciones

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

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

PiramidesPoblacionAcumuladasPowerPivot_21

 

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

PiramidesPoblacionAcumuladasPowerPivot_22

 

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

PiramidesPoblacionAcumuladasPowerPivot_23

 

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

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

PiramidesPoblacionAcumuladasPowerPivot_24

 

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

PiramidesPoblacionAcumuladasPowerPivot_25

 

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

PiramidesPoblacionAcumuladasPowerPivot_26

 

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

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

4 Comentarios

  1. kiquenet

    Grandioso !!

  2. lmblanco

    Hola Enrique

    Muchas gracias por tu opinión e interés en el artículo 8-D 😉

    Un saludo,
    Luismi

  3. MNG

    Imágenes caídas 🙁

    • lmblanco

      Hola Marc

      Disculpa el problema con las imágenes del artículo, hace unas semanas que se realizó una migración de la plataforma de publicación de geeks.ms a WordPress y en la mayoría de mis artículos se perdieron las imágenes. Actualmente estoy restaurando las que no se visualizan. En este artículo en concreto, ya deberían visualizarse correctamente.

      Muchas gracias por tu interés en el blog

      Un saludo,
      Luismi

Leave a Reply

Tema creado por Anders Norén