DAX, Excel, PowerPivot

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

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.

PiramidesPoblacionPowerPivot_01

 

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 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.

PiramidesPoblacionPowerPivot_02

 

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”.

PiramidesPoblacionPowerPivot_03

 

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

PiramidesPoblacionPowerPivot_04

 

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”.

PiramidesPoblacionPowerPivot_05

 

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

PiramidesPoblacionPowerPivot_06

 

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.

PiramidesPoblacionPowerPivot_07

 

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”.

PiramidesPoblacionPowerPivot_08

 

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.

PiramidesPoblacionPowerPivot_09

 

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

PiramidesPoblacionPowerPivot_10

 

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.

PiramidesPoblacionPowerPivot_11

 

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”.

PiramidesPoblacionPowerPivot_12

 

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.

PiramidesPoblacionPowerPivot_13

 

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.

PiramidesPoblacionPowerPivot_14

 

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”.

PiramidesPoblacionPowerPivot_15

 

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.

PiramidesPoblacionPowerPivot_16

 

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.

PiramidesPoblacionPowerPivot_17

 

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.).

PiramidesPoblacionPowerPivot_18

 

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.

PiramidesPoblacionPowerPivot_19

 

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.

PiramidesPoblacionPowerPivot_20

 

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

PiramidesPoblacionPowerPivot_21

 

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.

3 Comentarios

  1. lmba

    Hola

    Me ha parecido interesante, qué versión de Excel hay que usar

    Saludos

  2. anonymous

    En los artículos que dedicábamos a la creación de una pirámide de población

  3. anonymous

    In the articles that were dedicated to the creation of a population pyramid using PowerPivot ( part1

Leave a Reply

Tema creado por Anders Norén