Ediciones ENI Editorial | líder en informática
La creación de un modelo de datos con Power Pivot
Objetivo
La segunda parte de este libro propone, en primer lugar, descubrir y familiarizarse con la elaboración de un modelo de datos utilizando Power Pivot en Excel.
A continuación se propondrá un caso práctico: la creación de una herramienta de seguimiento presupuestario, con objeto de aplicar en términos concretos los elementos abordados.
¿Por qué crear un modelo de datos en Excel?
Power Pivot era, en su primera versión, un simple complemento de Excel 2010. Aunque es cierto que esta versión ofrecía menos posibilidades que hoy, por primera vez los «usuarios avanzados» podían usar Excel como si se tratase de una base de datos relacional; podían crear relaciones de tablas dentro de un archivo de Excel sin tener que usar una gran cantidad de funciones de búsqueda, como BUSCARV o INDICE/ COINCIDIR.
Los anglosajones disponen de un término bastante explícito para designar un archivo de Excel que intenta replicar la funcionalidad de una base de datos relacional utilizando fórmulas: EXCEL HELL
Desafortunadamente, durante mi experiencia profesional, me encontré en esta situación muchas veces. A medida que un archivo «crece», su mantenimiento se complica hasta hacerse imposible; después de un tiempo, lisa y llanamente, nadie comprende para qué sirven los cientos de fórmulas complejas que pueblan cada una de las pestañas.
La capacidad de crear un modelo de datos para fusionar fuentes de datos dispares que contienen cientos de miles de filas en un motor analítico tan avanzado y accesible como Excel constituyó una auténtica revolución.
Con el lanzamiento de Excel 2016, Microsoft decidió incorporar Power Pivot directamente en la cinta de opciones...
Los fundamentos de un modelo de datos
El modelo de datos le permite organizar datos como si trabajase con una base de datos relacional directamente en Excel. Se trata de un componente de la herramienta Excel Power Pivot.
Así, será posible:
-
administrar y analizar un conjunto voluminoso de datos que no podría estar contenido en una hoja de cálculo Excel tradicional;
-
crear relaciones de tabla para mostrar y agregar datos bajo demanda;
-
crear tablas dinámicas no desde una sola tabla, sino desde un conjunto de tablas organizadas y vinculadas entre sí.
La normalización
En términos generales, la normalización consiste en organizar tablas y columnas en un modelo de datos estructurado para reducir las redundancias y preservar la integridad de los datos.
Los objetivos de la normalización son:
-
eliminar datos redundantes para reducir el tamaño de las tablas y mejorar la velocidad y la eficiencia del procesamiento;
-
minimizar errores y anomalías debidos a modificaciones de datos (inserción, actualización o eliminación de registros);
-
simplificar la implementación de consultas y estructurar la base de datos para un análisis significativo.
En un modelo de datos estandarizado, cada tabla debe tener un objetivo distinto y específico (información sobre clientes o proveedores, registros de transacciones, etc.).
Ejemplo
Encontrará los datos de este ejemplo en el archivo modelo_datos.xlsx; la solución de este ejemplo se encuentra en el archivo modelo_datos_resuelto.xlsx.
En la pestaña tabla, la tabla siguiente recoge los préstamos de libros a los lectores de una biblioteca:
Las celdas en gris representan las redundancias que se hallan en la tabla.
En este momento, estas redundancias pueden parecer insignificantes, pero ineficiencias menores pueden convertirse en problemas mayores a medida que aumenta el tamaño de la base de datos.
La normalización consiste en separar los datos en varias tablas que incluyen elementos de la misma naturaleza.
La tabla inicial se dividirá en tres tablas:
-
La tabla T_préstamos
-
La tabla T_socios
-
La tabla T_libros
Encontrará estas tres tablas en la pestaña tablas normalizadas.
Importar tablas al modelo de datos
Si la pestaña Power Pivot no se muestra en la cinta de opciones, efectúe la siguiente operación:...
Tabla dinámica, modelo de datos y contexto de filtro
El concepto de propagación de filtro
En una tabla dinámica, los datos se sintetizan en función de un contexto de filtro.
El contexto de filtro es el conjunto de filtros (o «coordenadas») de determinadas por los elementos de la tabla dinámica (filtros, segmentos, etiquetas de filas y etiquetas de columnas).
En un informe de tabla dinámica basado en un modelo de datos, si filtra una tabla, ello tendrá un impacto no solo en la tabla en sí, sino también en las tablas relacionadas y situadas por debajo de ella.
Para ilustrar este principio, vamos a trabajar de nuevo con el archivo modelo_datos.xlsx; la solución de este ejemplo se encuentra en el archivo modelo_datos_resuelto.xlsx, en la pestaña td.
Ejemplo: Vamos a construir una tabla dinámica para recoger el número de préstamos por título del libro.
En el menú Inicio de la cinta de opciones de Power Pivot, haga clic en el botón Tabla dinámica.
Aparece en pantalla el cuadro de diálogo Crear tabla dinámica.
En cuanto a la ubicación, active la opción Nueva hoja de cálculo.
Haga clic en el botón Aceptar.
Aparece el panel Campos de tabla dinámica en el lado derecho de la pantalla; muestra las tres tablas importadas con anterioridad en el modelo de datos:
Haga clic en la tabla T_libros y seleccione el campo Libro.
Excel ha colocado...
Conectar Power Pivot a datos externos
Hasta ahora hemos trabajado con tablas existentes en un archivo de Excel. Sin embargo, al igual que Power Query (que estudiamos en el capítulo anterior), Power Pivot puede conectarse a una gran cantidad de fuentes de datos.
No obstante, conviene hacer una distinción entre dos escenarios diferentes:
-
Los datos se han preparado previamente con Power Query: basta entonces con agregarlos al modelo de datos.
-
Los datos externos ya están listos para su uso: en este caso, se debe crear una conexión entre la fuente de datos y Power Pivot.
Muy a menudo, en este segundo escenario, los datos provienen de una base de datos relacional y, por lo tanto, ya están limpios y normalizados.
Conectar datos externos preparados previamente con Power Query
Los datos externos preparados con Power Query pueden importarse directamente al modelo de datos de Power Pivot.
En el editor de Power Query, pestaña Inicio - grupo Cerrar, haga clic en el menú desplegable del botón Cerrar y cargar - Cerrar y cargar en.
Aparece en pantalla el cuadro de diálogo Importar datos.
Seleccione Crear solo conexión.
Marque la opción Agregar estos datos al modelo de datos.
Haga clic en el botón Aceptar.
Los datos agregados al modelo de datos se encuentran directamente en Power Pivot.
Conexión a una base de datos relacional
Power Pivot permite conectarse a una amplia gama de software...
La actualización de los datos
Cuando carga datos de una fuente de datos externa en Power Pivot, crea una copia «estática» de la fuente de datos. Si esta fuente va a evolucionar, puede ser apropiado actualizar los datos cargados en Power Pivot.
Existen varias opciones para actualizar los datos.
Para ilustrar los diferentes métodos de actualización de datos, continuaremos con la base de datos de Access modelo_datos_access.accdb que importamos en Power Pivot en la sección anterior.
Actualización manual
Para actualizar manualmente los datos, en la pestaña Inicio de la cinta de opciones de Power Pivot, haga clic en la lista desplegable del botón Actualizar.
Son posibles dos opciones:
-
Actualizar: actualiza únicamente los datos de la tabla activa.
-
Actualizar todo: actualizar todas las tablas cargadas.
Actualización automática
Puede configurar la actualización de los datos para que esta se lleve a cabo automáticamente cuando se abre un archivo o a intervalos regulares.
La configuración de una actualización automática se realiza a través de la cinta de opciones de Excel.
En la cinta de opciones de Excel, seleccione la pestaña Datos y, a continuación, haga clic en el botón Consultas y conexiones.
El panel Consultas y conexiones aparece en la parte derecha de la ventana de Excel.
En el panel Consultas...
Caso práctico: seguimiento presupuestario
Presentación del caso práctico
Una biblioteca municipal desea implementar un sistema de seguimiento presupuestario. Cada año, se le asigna un presupuesto provisional para la adquisición de diferentes tipos de productos editoriales.
A lo largo del año, los bibliotecarios se reúnen, debaten sobre las novedades y realizan pedidos. Estos pedidos se detallan por tipo de producto: novela, novela policiaca, cómic, etc.
Los datos necesarios para llevar a cabo este caso práctico se encuentran en el archivo Presupuesto.xlsx. La solución se encuentra en el archivo Presupuesto_resuelto.xlsx.
A priori, podemos definir cuatro tablas:
-
La tabla T_Presupuesto, con el presupuesto anual distribuido por sección o por tipo de producto.
Se trata de una tabla de dimensión. La clave primaria es el campo Sección.
-
La tabla T_Bibliotecario, con el apellido y el nombre de cada uno de los bibliotecarios. La clave primaria es N° Bibliotecario. Se trata de una tabla de dimensión.
-
La tabla T_Pedido contiene la Fecha de pedido, el número del bibliotecario (N° Bibliotecario) que ha realizado el pedido (se trata de una clave extranjera heredada de la tabla T_Bibliotecario). La clave primaria de esta tabla es N° Pedido. Se trata de una tabla de hechos.
-
La tablaT_Detalle pedido detalla cada Título pedido, así como su precio (Importe). Contiene dos claves extranjeras:
-
Sección, heredada de la tabla T_Presupuesto.
-
N° Pedido, heredada de la tabla T_Pedido.
-
La clave primaria es id detalle. Se trata de una tabla de hechos.
Creación de las relaciones
Seleccione una celda de la tabla T_Presupuesto y en la pestaña Power Pivot, haga clic en el botón Agregar a modelo de datos.
Se abre la ventana Power Pivot para Excel. Contiene los datos de la tabla T_Presupuesto.
Repita esta operación con las tablas T_Bibliotecario, T_Pedido y T_Detalle_pedido.
Las cuatro tablas se han importado a Power Pivot.
Aparecen en forma de pestañas en la parte inferior de Power Pivot:
En Power Pivot, en la pestaña Inicio de la cinta de opciones - grupo Vista, haga clic en el botón Vista de diagrama.
Aparecen las cuatro tablas.
Reorganice ligeramente...