Las relaciones entre elementos de PowerPivot

Una de las ventajas que nos ofrece PowerPivot es la posibilidad de añadir a un mismo modelo elementos con distintos orígenes. Aunque la afirmación anterior es  completamente cierta, para que dicho modelo funcione de manera correcta, es necesario que existan relaciones bien definidas entre los orígenes incorporados.

Relaciones automáticas

Cuando añadimos en PowerPivot tablas de una misma base de datos, Excel importará las relaciones definidas previamente. Vamos a comprobarlo con un modelo en el que hemos importado varias de un objeto de Access.

Para ver qué relaciones existen  en la actualidad, podemos actuar de dos formas distintas. Por un lado, tras acceder a Administrar, es posible emplear el botón Vista de diagrama de la cinta de Inicio. Entonces, Excel mostrará una representación gráfica muy parecida a la que nos ofrece Access. Por otro, con la cinta Diseño, y la opción Administrar relaciones, para obtener una tabla detalla entre los campos afectados.

Resultado de Administrar relaciones en PowerPivo

Como puedes comprobar en la imagen, desde este cuadro puedes Crear nuevas relaciones, así como EditarEliminar las actuales.

Relaciones entre elementos independientes

Ahora, partimos de un supuesto diferente, un modelo en el que hemos incorporado una pestaña con el contenido de una hoja de cálculo denominada Stores.xlsx y otra Geography.xlsx. Al tratarse de libros individuales, no existe ninguna relación, pero como nosotros conocemos el carácter de los datos, vamos a crear una manualmente.

Nos situamos, por ejemplo, en el encabezado del campo GeographyKey de la tabla Stores. Después, pulsamos el botón derecho del ratón y elegimos Crear relación. En el cuadro de diálogo que aparece sólo será necesario, escoger la tabla de destino en la parte inferior. Si ésta contiene un campo con el mismo nombre, Excel lo detectará automáticamente. De lo contrario, lo haremos nosotros.

Una vez establecida, los campos relacionados mostrarán un icono en la zona derecha del encabezado.

Vista de diagrama

La operación anterior también se puede realizar directamente en el acceso gráfico. Allí operaremos de manera similar. Es decir, hay que colocarse en uno de los campos a relacionar y en el menú contextual acceder a Crear relación. A continuación, proceder de la misma forma que en el apartado anterior.

Conclusiones

Para obtener resultados eficaces y/o evitar errores en el análisis de los datos, antes de comenzar a operar es necesario revisar que las relaciones están efectuadas y bien definidas.

Toma los datos para PowerPivot directamente de Excel

PowerPivot, lo decimos una y otra vez, es una herramienta muy potente para trabajar con gran cantidad de datos. Su potencia radica, entre otros factores, en la posibilidad de trabajar con más de 1.000.000 de registros.

PowerPivot para ExcelAsí, en los artículos Utiliza el asistente para Importar tablas de PowerPivot y Agregar datos en PowerPivot mediante SQL vimos cómo podíamos seleccionar los registros a tratar desde bases de datos. Sin embargo, la operativa puede ser en ocasiones más sencilla. Por eso, hoy vamos a ver alternativas directas con Excel.

Como siempre, si quieres poner en práctica las explicaciones, puedes utilizar el paquete de ejemplo Contoso de Microsoft.

Emplea el portapapeles

Sin duda alguna, la opción más simple y sencilla para añadir datos a tu PowerPivot. Empieza por abrir, por ejemplo, el libro Stores y seleccionar los registros que quieras tratar. A continuación, entra en Administrar y en el Portapapeles escoge Pegar.

Excel mostrará un cuadro de diálogo similar al siguiente en el que podrás asignar un nombre a la tabla. En nuestro caso, Tiendas.


Fácil ¿verdad? Sin embargo, este método presenta un gran problema: en caso de cambios, los datos no se actualizan.

Utiliza una tabla vinculada

Para solucionar el problema que acabamos de  describir, existe un método alternativo. Al igual que en el anterior, el primer paso será abrir el archivo Stores. Luego, convertimos los datos de la hoja en un objeto de tipo Tabla. Este proceso lo puedes llevar a cabo con la combinación de teclado Control+T o mediante el botón Tabla de la cinta Insertar. En cualquier caso, recuerda marcar la opción Mi tabla tiene encabezados. No olvides asignar un nombre personalizado, en el campo Nombre de tabla situado a la izquierda en la cinta Diseño; por ejemplo, Tiendas.

En este punto tendrás que enlazar el archivo actual con PowerPivot. Sin tienes Excel 2010 utiliza la opción Crear tabla vinculada de la cinta. Para Excel 2013 o superior, emplea Agregar a modelo de datos

La ventaja es que, al establecer un vínculo, los datos se pueden actualizar en cualquier momento. Por ejemplo, ve al archivo original y en la celda D2 sustituye Store por CornerShop.

Si pulsas el botón Administrar de la cinta PowerPivot para volver al modelo, verás que no hay cambios. Ésto es así porque la actualización automática está desactivada en esta herramienta al igual que en las tablas dinámicas. Sin embargo, para que  aparezcan las modificaciones, sólo será necesario pulsar el botón Actualizar de la cinta Inicio.

Como ves, este procedimiento es mucho más interesante que el anterior y también resulta sencillo de ejecutar.

Práctica exportando datos a PowerPivot con el Portapapeles y el objeto Tabla

Gráfico circular combinado con subgráfico en Excel

A veces, la dispersión de cifras entre distintas categorías de datos, hace que la exposición de un gráfico circular en Excel pierda eficacia. Sin embargo, esa circunstancia puede propiciar el uso de un subgráfico para aportar mayor claridad.

Punto de partida para crear un gráfico circular combinado

Para realizar la explicación y exponer algunos ejemplos, vamos a utilizar datos relativos a la prima de riesgo. En concreto, la tabla de datos que podemos encontrar en la web Datos macro. Si no sabes cómo importar dicha información a tu hoja de cálculo, repasa el artículo Cómo utilizar en Excel datos publicados en la web.

Datos para efectuar un gráfico con subgráfico

Tras la importación, hemos depurado un poco los datos para quedarnos sólo con las denominaciones de los países y los datos de la prima. Ahora, ya está todo listo para empezar con la práctica.

Hora de crear un subgráfico

Comenzamos seleccionando las celdas con los nombres de los países y las primas asociadas. Recuerda que la selección es la clave para realizar un buen gráfico.  A continuación, en la cinta Insertar, desplegamos las alternativas para gráfico Circular.  Para esta primera prueba, nuestra opción será Grafíco circular con subgráfico circular.

Gráfico circular con subgráfico circular sin adaptación

Si te fijas en la imagen, el gráfico de la izquierda muestra un área celeste que enlaza con el subgráfico circular. Ahora bien, la pregunta es clave es ¿qué datos está tomando Excel para el subgráfico?. Para responder, en el menú contextual de la zona grande celeste, haz clic en Formato de punto de datos

En el cuadro de diálogo que aparece, puedes comprobar que Excel ha dividido los  datos por posición y en el subgráfico muestra las 8 últimas categorías. Sin embargo, esa cantidad puede variar en función del número de categorías a representar.

Personaliza para que sea más eficiente

Con el aspecto actual, la información no es suficientemente clara. Por ejemplo, en ambos gráficos podemos apreciar quesitos demasiado pequeños que cuesta trabajo identificar. Así que vamos a mejorar el resultado para que en el subgráfico aparezcan las categorías que tengan un valor por debajo del número indicado.

En primer lugar, dentro del campo Dividir serie por escogemos Valor. En Segundo trazado contiene… señalamos como referencia el valor 50 para este ejemplo. Además, como nuestra intención es centrar el foco en los países con menor prima, el Tamaño del segundo trazado se ha fijado en 150.

También hemos activado las Etiquetas de datos con Valor, Nombre de la categoría y Ajuste perfecto. Por último, hemos ordenado los datos originales por Prima de mayor a menor y se ha ocultado la leyenda. Tras todos estos cambios el resultado debe ser similar al siguiente:

Gráfico circular combinado con subgráfico circular personalizado

Otro modelo de subgráfico

Es cierto que quizás al contar en esta ocasión con tantas categorías, el resultado no es del todo claro. No obstante, podemos probar el otro modelo de gráfico combinado circular. Y, para no comenzar desde el principio, nada más fácil que utilizar el botón Cambiar tipo de gráfico de la cinta Diseño.

Seleccionamos Gráfico circular con subgráfico de barras. Ampliamos el Ancho del intervalo400 y reordenamos por países. El resultado visual parece ahora más compacto que en el caso anterior. De todas formas, la decisión final depende de quién realice el gráfico pensando siempre en las características de la audiencia.

Gráfico circular con subgráfico de barras personalizado

¡Practica con distintos ejemplos y encuentra tu modelo de gráfico circular combinado con subgráfico!

 

 

Agregar datos en PowerPivot mediante SQL

PowerPivot para ExcelTras ver cómo podíamos trabajar en PowerPivot con datos añadidos mediante el asistente, es el momento de avanzar.

Si el último artículo de Access estaba dedicado a iniciarse en SQL, hoy vamos a ver que también se puede utilizar este lenguaje en Excel.

Importar datos mediante SQL

Al trabajar con SQL desde PowerPivot también se presentan varias alternativas: importar una consulta ya creada o escribirla desde cero. En cualquier caso, el comienzo es el mismo, utilizar el asistente. Sigue estos pasos para practicar:

  1. Ve a Ventana de PowerPivot
  2. En Inicio, escoge como origen De base de datos y luego De Access.
  3. Para nombre de la conexión escribeCategorías. Con el botón Examinar selecciona la base ProductCategories del paquete de ejemplo Contoso, que Microsoft pone a nuestro alcance.
  4. Haz clic en Siguiente.

Es en este punto cuando nuestro camino cambia respecto al anterior artículo, ya que ahora vamos a decantarnos por Escribir una consulta que especifique los datos a importar.

Tras pulsar de nuevo Siguiente, asignamos el nombre descriptivo; por ejemplo, Todo menos Audio. En la parte inferior tienes a tu alcance el botón Diseño, con el que puedes Importar una consulta ya creada. Sin embargo, esta vez nos vamos a lanzar a diseñar una consulta nueva, que extraiga los datos de todas las categorías existentes menos la de Audio. Así que toca escribir el siguiente código en el cuadro de diálogo:

SELECT DimProductCategory.ProductCategoryKey, DimProductCategory.ProductCategoryLabel, DimProductCategory.ProductCategoryName, DimProductCategory.ProductCategoryDescription
FROM DimProductCategory
WHERE (ProductCategoryName <> 'AUDIO')

Antes de Aceptar es recomendable pulsar Validar para comprobar que no existen errores de sintaxis. Si todo está correcto, será el momento de Finalizar.

Resultado en PowerPivot

En ese momento, PowerPivot conectará con la base de datos escogida y realizará la importación de todos los elementos que cumplan los criterios establecidos en la consulta. Al Cerrar podrás ver el resultado en la pantalla principal.

Datos extraidos para PowerPivot mediante SQL

PowerPivot genera los datos como una tabla nueva denominada Consulta. ¿No la ves? Entonces revisa la pestaña que aparece en la zona inferior de la pantalla 😉

Si en cualquier momento desearas modificar su nombre, sólo sería necesario acceder al menú contextual de la pestaña y utilizar la opción Cambiar nombre, así de fácil. Además, en dicho menú tienes a tu alcance otras opciones que también pueden ser útiles para Eliminar la consulta o asignarle una Descripción.

En cualquier caso, has podido comprobar mediante este ejemplo, que el uso de consultas SQL es un método sencillo y eficaz para extraer información.

Practica con SQL para importar distintos datos en PowerPivot

Utiliza el asistente para Importar tablas de PowerPivot

En artículos anteriores aprendimos qué es PowerPivot y los tipos de datos admitidos. Así que ya es hora de comenzar a trabajar, empezando por ver cómo podemos importar tablas.

Tablas de una base de datos de Access

Utiliza el asistente para importar tablas en PowerPivotPowerPivot nos puede ayudar fácilmente a importar tablas de una base de Access mediante un asistente. Sin embargo, para que ésto sea posible, es necesario que las relaciones estén previamente creadas.

Para contar con un ejemplo de calidad y datos suficientes que permitan obtener resultados representativos, vamos a utilizar como referencia los archivos de Contoso. Se trata de una base de datos que Microsoft pone al alcance de los usuarios para poder efectuar prácticas.

Si ya lo tienes todo preparado, en la cinta PowerPivot, pulsa el primer icono: Ventana de PowerPivot. Ahora, en  el icono Desde base de datos, haz clic sobre De Access para iniciar el asistente. En esta primera parte es suficiente con asignar un nombre distinto y escoger el archivo adecuado. En este caso, Contoso Sales.

Asistente de PowerPivot para importar tablas

Ahora puedes optar por seleccionar directamente los datos en las tablas/vistas o crear una consulta SQL. Nosotros como estamos empezando, vamos a decantarnos por la primera opción y pulsamos Siguiente.

A continuación hay que marcar todos los elementos que deseamos traernos a PowerPivot. Para nuestro ejemplo, supongamos que nos interesan todas las tablas menos la dedicada a las promociones DimPromotion.

Llegados a este punto, también es posible filtrar las tablas para escoger solamente registros y/o campos específicos. Por ejemplo, selecciona la tabla DimProduct y pulsa el botón Vista previa y filtro que está situado en la esquina inferior derecha del cuadro de diálogo.

Filtros y selección al importar tablas para PowerPivot

En la imagen puedes ver que se han desmarcado las columnas Manufacturer y Brandname; además, hemos aplicado un filtro en ClassName para importar sólo los registros de tipo Economy. Tras Aceptar el cuadro de diálogo mostrará el indicativo Filtros aplicados en la columna de la derecha.

Si no necesitas nada más, es el momento adecuado para pulsar Finalizar. Tras efectuar las distintas operaciones, Excel mostrará un cuadro de resultados del proceso y ya estaremos listos para continuar, pero eso será én futuros artículos.

Informe de resultados al importar tablas para PowerPivot

Con el Asistente de PowerPivot, puedes importar tablas fácilmente. ¡Empieza ya a practicar!