Tutorial: Ampliar relaciones del modelo de datos usando Excel, Power Pivot y DAX

Las secciones de este tutorial son los siguientes:

• Agregar una relación mediante la vista de diagrama de Power Pivot

• Extender el modelo de datos con columnas calculadas

• Crear una jerarquía

• Usar jerarquías en tablas dinámicas

• Control y evaluación

Al final de este tutorial hay una evaluación que puede realizar para probar su aprendizaje.

Esta serie utiliza datos que describen medallas olímpicas, países anfitriones y los diversos eventos olímpicos deportivos. Los tutoriales de esta serie son los siguientes:

1. Importar datos a Excel y Crear un modelo de datos

2. Ampliar relaciones del modelo de datos con Excel, Power Pivot y DAX

3. Crear informes de Power View basados en mapas

4. Incorporar datos de Internet y establecer valores predeterminados para los informes de Power View

5. Ayuda de Power Pivot

6. Crear informes de Power View sorprendentes - Parte 2

Le recomendamos que siga por ellas en orden.

Estos tutoriales usan Excel 2013 con Power Pivot habilitado. Para obtener más información sobre Excel 2013, haga clic aquí. Para obtener instrucciones sobre cómo activar Power Pivot, haga clic aquí.

Agregar una relación mediante la vista de diagrama de Power Pivot

En esta sección, usará el complemento Power Pivot en Excel 2013 de Microsoft Office para extender el modelo. El uso de la Vista de diagrama de Microsoft SQL Server Power Pivot para Excel facilita la creación de relaciones. Primero, tiene que asegurarse de que tiene el complemento Power Pivot habilitado.

Nota: El complemento PowerPivot en Microsoft Excel 2013 forma parte de Office Profesional Plus. Consulte Iniciar el complemento Power Pivot in Microsoft Excel 2013 para obtener más información.

Agregar Power Pivot a la cinta de opciones de Excel habilitando el complemento Power Pivot

Cuando Power Pivot esté habilitado, verá una pestaña de la cinta de opciones de Excel 2013 llamada POWER PIVOT. Para habilitar Power Pivot, siga estos pasos.

1. Vaya a ARCHIVO > Opciones > Complementos.

2. En el cuadro Administrar cerca de la parte inferior, haga clic en Complementos COM > Ir.

3. Active la casilla Microsoft Office PowerPivot en Microsoft Excel 2013 para Excel 2013 y haga clic en Aceptar.

La cinta de opciones ahora contiene la pestaña POWER PIVOT.

Agregar una relación mediante la vista de diagrama de Power Pivot

El libro de Excel incluye una tabla denominada Anfitriones. Hemos importado Anfitriones copiándolos y pegándolos en Excel y, a continuación, formateamos los datos como una tabla. Para agregar la tabla Anfitriones al modelo de datos, debemos establecer una relación. Vamos a usar Power Pivot para representar visualmente las relaciones en el modelo de datos y, a continuación, crear la relación.

1. En Excel, haga clic en la pestaña Anfitriones para hacer que sea la hoja activa.

2. En la cinta de opciones, seleccione POWER PIVOT > Tablas > Agregar al modelo de datos. Con este paso se agrega la tabla Anfitriones al modelo de datos. También se abre el complemento Power Pivot que usará para realizar los pasos restantes de esta tarea.

3. Observe que la ventana Power Pivot muestra todas las tablas del modelo, incluidos Anfitriones. Haga clic en un par de tablas. En Power Pivot puede ver todos los datos que contiene el modelo, incluso si no se muestran en ninguna hoja de cálculo de Excel, como los datos de Disciplinas, Eventos y Medallas a continuación, así como S_Teams,W_Teamsy Deportes.
   
   

4. En la ventana de Power Pivot, en la sección Ver, haga clic en Vista de diagrama.

5. Use la barra de desplazamiento para cambiar el tamaño del diagrama para poder ver todos los objetos. Reorganice las tablas arrastrando la barra de título para que estén visibles y colocadas una junto a la otra. Observe que cuatro tablas no están relacionadas con el resto de las tablas: Anfitriones, Eventos, W_Teams y S_Teams.
   
   

6. Observe que tanto la tabla Medallas como la tabla Eventos tienen un campo llamado DisciplinaEvento. Después de una inspección más exhaustiva, determina que el campo DisciplinaEvento de la tabla Eventos consta de valores únicos no repetidos.

1. Cree una relación entre la tabla Medallas y la tabla Eventos. Mientras esté en la vista de diagrama, arrastre el campo DisciplinaEvento desde la tabla Eventos hasta el campo DisciplinaEvento de Medallas. Aparecerá una línea entre ellos que indica que se ha establecido una relación.

2. Haga clic en la línea que conecta Eventos y Medallas. Los campos resaltados definen la relación, tal como se muestra en la siguiente pantalla.
   
   
    

3. Para conectar Anfitriones al modelo de datos, es necesario un campo con valores únicos que identifiquen cada fila de la tabla Anfitriones. Después, podemos realizar una búsqueda en nuestro modelo de datos para ver si existen los mismos datos en otra tabla. La búsqueda en la vista de diagrama no nos permite hacer esto. Con Anfitriones seleccionada, vuelva a la vista de datos.

4. Después de examinar las columnas, nos damos cuenta de que Anfitriones no tiene una columna de valores únicos. Tendremos que crearla usando una columna calculada y Expresiones de análisis de datos (DAX).

Es interesante cuando los datos del modelo de datos tienen todos los campos necesarios para crear relaciones y mezclar datos para visualizar en Power View o en tablas dinámicas. Sin embargo, las tablas no siempre cooperan tanto, por lo que en la siguiente sección se describe cómo crear una columna nueva usando DAX, que sirve para crear una relación entre las tablas.

Extender el modelo de datos con columnas calculadas

Para establecer una relación entre la tabla Anfitriones y el modelo de datos, y así extender nuestro modelo de datos para incluir la tabla Anfitriones, Anfitriones debe tener un campo único que identifique cada fila. Además, ese campo debe corresponder a un campo del modelo de datos. Esos campos correspondientes, uno en cada tabla, son lo que permite asociar los datos de las tablas.

Ya que la tabla Anfitriones no tiene este tipo de campo, deberá crearlo. Si queremos mantener la integridad del modelo de datos, no podemos usar Power Pivot para editar o eliminar los datos existentes. Sin embargo, puede crear nuevas columnas mediante campos calculados basados en los datos existentes.

Si busca en la tabla Anfitriones y después en otras tablas del modelo de datos, encontrará un buen candidato para campo único que podrá crear en Anfitriones y asociar a una tabla en el modelo de datos. Ambas tablas requerirán una columna calculada nueva para cumplir con los requisitos para establecer una relación.

En Anfitriones, podemos crear una columna calculada única combinando el campo Edición (el año del evento olímpico) y el campo Temporada (verano o invierno). En la tabla Medallas también hay un campo Edición y un campo Temporada, por lo que, si creamos una columna calculada en cada una de esas tablas que combine los campos Edición y Temporada, podremos establecer una relación entre Anfitriones y Medallas. La siguiente pantalla muestra la tabla Anfitriones, con los campos Edición y Temporada seleccionados.

Crear columnas calculadas con DAX

Empecemos con la tabla Anfitriones . El objetivo es crear una columna calculada en la tabla Anfitriones y, después, en la tabla Medallas , que se puede usar para establecer una relación entre ellas.

En Power Pivot, puede usar Expresiones de análisis de datos (DAX) para agregar cálculos. DAX es un lenguaje de fórmulas para Power Pivot y tablas dinámicas, diseñado para los datos relacionales y el análisis contextual disponibles en Power Pivot. Puede crear fórmulas de DAX en una nueva columna de Power Pivot y en el área de cálculo de Power Pivot.

1. En Power Pivot, seleccione INICIO > Ver > Vista de datos para asegurarse de que esté seleccionada la vista de datos, en lugar de la vista de diagrama.

2. Seleccione la tabla Anfitriones en Power Pivot. Junto a las columnas existentes hay una columna vacía titulada Agregar columna. Power Pivot ofrece esa columna como un marcador de posición. Existen muchas formas de agregar una nueva columna a una tabla en Power Pivot, una de las cuales es simplemente seleccionar la columna vacía que contiene el título Agregar columna.
   
   
   

3. En la barra de fórmulas, escriba la siguiente fórmula de DAX. La función CONCATENAR combina dos o más campos en uno solo. Cuando escriba, AutoCompletar lo ayudará a escribir los nombres cualificados completos de las columnas y las tablas, y enumera las funciones que están disponibles. Use TAB para seleccionar las sugerencias de AutoCompletar. También puede hacer clic en la columna mientras escribe la fórmula, y Power Pivot insertará el nombre de columna en la fórmula.
   
   =CONCATENATE([Edition],[Season])

4. Cuando termine de crear la fórmula, presione Entrar para aceptarla.

5. Los valores se rellenan para todas las filas de la columna calculada. Si se desplaza hacia abajo por la tabla, verá que cada fila es única, por lo que hemos creado correctamente un campo que identifica exclusivamente cada fila de la tabla Anfitriones . Estos campos se denominan clave principal.

6. Vamos a cambiar el nombre de la columna calculada a IDEdición. Puede cambiar el nombre de cualquier columna haciendo doble clic en ella o clic con el botón secundario y eligiendo Cambiar nombre de columna. Cuando termine, la tabla Anfitriones en PowerPoint se verá como en la pantalla siguiente.
   
   

La tabla Anfitriones está lista. Ahora vamos a crear una columna calculada en Medallas que coincida con el formato de la columna IDEdición que creamos en Anfitriones, de modo que podamos crear una relación entre las dos tablas.

1. Comience creando una columna nueva en la tabla Medallas, como lo hizo en Anfitriones. En Power Pivot, seleccione la tabla Medallas y haga clic en Diseño > Columnas > Agregar. Observe que Agregar columna está seleccionada. Esto tiene el mismo efecto que cuando se selecciona Agregar columna.

2. La columna Edición de Medallas tiene un formato distinto al de la columna Edición de Anfitriones. Antes de combinar o concatenar la columna Edición con la columna Temporada para crear la columna IDEdición, debemos crear un campo intermedio que haga que Edición tenga el formato correcto. En la barra de fórmulas situada encima de la tabla, escriba la siguiente fórmula de DAX.

   = YEAR([Edition])
   

3. Cuando termine de crear la fórmula, presione Entrar. Todas las filas de la columna calculada se rellenan con valores, basándose en la fórmula especificada. Si compara esta columna con la columna Edición de Anfitriones, verá que estas columnas tienen el mismo formato.

4. Para cambiar el nombre de la columna, haga clic con el botón derecho del mouse en CalculatedColumn1 y seleccione Cambiar nombre de columna. Escriba Año y, después, presione Entrar.

5. Al crear una columna nueva, Power Pivot agregará otra columna como marcador de posición con el nombre Agregar columna. A continuación, queremos crear la columna calculada IDEdición, por lo que debe seleccionar Agregar columna. En la barra de fórmulas, escriba la siguiente fórmula de DAX y presione Entrar.
   
   =CONCATENATE([Year],[Season])

6. Cambie el nombre de la columna. Para ello, haga doble clic en ColumnaCalculada1 y escriba IDEdición.

7. Ordene la columna en orden ascendente. La tabla Medallas de Power Pivot ahora es similar a la de la pantalla siguiente.
   
   
    

Observe que muchos valores se repiten en el campo IDEdición de la tabla Medallas. Eso está bien y es lo esperado, ya que en todas las ediciones de los Juegos Olímpicos (ahora representados con el valor IDEdición) se otorgaron muchas medallas. Lo que es único en la tabla Medallas es cada medalla otorgada. El campo ClaveMedalla es el identificador único de cada registro de la tabla Medallas y su clave principal designada.

El siguiente paso consiste en crear una relación entre Anfitriones y Medallas.

Crear una relación con columnas calculadas

Ahora vamos a usar las columnas calculadas que creamos para establecer una relación entre Anfitriones y Medallas.

1. En la ventana de Power Pivot, seleccione Inicio > Ver > Vista de diagrama en la cinta de opciones. También puede cambiar entre la vista de cuadrícula y la vista de diagrama utilizando los botones de la parte inferior de la ventana de PowerView, tal como se muestra en la siguiente pantalla.
   
   

2. Expanda Anfitriones para poder ver todos sus campos. Hemos creado la columna IDEdición para que actúe como la clave principal de la tabla Anfitriones (campo único no repetido) y una columna IDEdición en la tabla Medallas para habilitar una relación entre ambas tablas. Tenemos que encontrar ambas columnas y crear una relación. Power Pivot proporciona una función Buscar en la cinta de opciones para que pueda buscar el modelo de datos para los campos correspondientes. La siguiente pantalla muestra la ventana Buscar metadatos, con IDEdición incluida en el campo Buscar.
   

3. Coloque la tabla Anfitriones junto a Medallas.

4. Arrastre la columna IDEdición de Medallas a la columna IDEdición de Anfitriones. Power Pivot crea una relación entre las tablas en función de la columna IDEdición y dibuja una línea entre las dos columnas para indicar la relación.
   
   

En esta sección, aprendió una nueva técnica para agregar columnas nuevas, creó una columna calculada con DAX y usó dicha columna para establecer una relación nueva entre las tablas. Ahora, la tabla Anfitriones está integrada en el modelo de datos y sus datos están disponibles para la tabla dinámica de Hoja1. También puede utilizar los datos asociados para crear tablas dinámicas, gráficos dinámicos, informes de Power View adicionales, y mucho más.

Crear una jerarquía

La mayoría de los modelos de datos incluyen datos que son intrínsecamente jerárquicos. Por ejemplo, es habitual en los datos de calendario, datos geográficos y categorías de productos. La creación de jerarquías dentro de Power Pivot es útil porque se puede arrastrar un elemento a un informe (la jerarquía) en lugar de tener que ensamblar y ordenar los mismos campos una y otra vez.

Los datos de los Juegos Olímpicos también son jerárquicos. Resulta útil para comprender la jerarquía de los Juegos Olímpicos, en términos de deportes, disciplinas y eventos. Para cada deporte, hay una o más disciplinas asociadas (a veces hay muchas). Además, para cada disciplina, hay uno o más eventos (nuevamente, es posible que haya muchos eventos para cada disciplina). La siguiente imagen ilustra la jerarquía.

En esta sección, creará dos jerarquías en los datos de los Juegos Olímpicos que ha usado en este tutorial. Después, usará estas jerarquías para ver cómo facilitan la organización de datos en tablas dinámicas y, en un próximo tutorial, en Power View.

Crear una jerarquía Deporte

1. En Power Pivot, cambie a la vista de diagrama. Expanda la tabla Eventos para que pueda ver con más facilidad todos sus campos.

2. Mantenga presionado Ctrl y haga clic en los campos Deporte, Disciplina y Evento. Con esos tres campos seleccionados, haga clic con el botón secundario y seleccione Crear jerarquía. Se creará un nodo principal de la jerarquía, Jerarquía 1, en la parte inferior de la tabla, y las columnas seleccionadas se copiarán bajo la jerarquía como nodos secundarios. Compruebe que Deporte aparezca primero en la jerarquía, luego Disciplina y, por último, Evento.

3. Haga doble clic en el título, Jerarquía1, y escriba DDE para cambiar el nombre de la jerarquía nueva. Ahora tiene una jerarquía que incluye Deporte, Disciplina y Evento. Ahora, la tabla Eventos es similar a la de la pantalla siguiente.
   
   

Crear una jerarquía Ubicación

1. Sin salir de la vista de diagrama de Power Pivot, seleccione la tabla Anfitriones y haga clic en el botón Crear jerarquía del encabezado de tabla, como se muestra en la pantalla siguiente.
   
   
   Aparecerá un nodo principal de jerarquía vacío en la parte inferior de la tabla.

2. Escriba Ubicaciones como nombre de la nueva jerarquía.

3. Existen muchas formas de agregar columnas a una jerarquía. Arrastre los campos Temporada, Ciudad y NOC_PaísRegión al nombre de la jerarquía (en este caso, Ubicaciones), hasta que el nombre de la jerarquía quede resaltado, y después suelte los campos para agregarlos.

4. Haga clic con el botón secundario en IDEdición y seleccione Agregar a la jerarquía. Elija Ubicaciones.

5. Asegúrese de que los nodos secundarios de la jerarquía estén en orden. De arriba a abajo, el orden debería ser: Temporada, NOC, Ciudad, IDEdición. Si los nodos secundarios no están en orden, solo tiene que arrastrarlos al lugar correspondiente de la jerarquía. La tabla debe tener una apariencia similar a la de la siguiente pantalla.
   

Ahora, el modelo de datos tiene jerarquías que pueden ser útiles en los informes. En la sección siguiente, aprenderá cómo estas jerarquías pueden acelerar y hacer más coherente el proceso de creación de informes.

Usar jerarquías en tablas dinámicas

Ahora que tenemos una jerarquía Deportes y una jerarquía Ubicaciones, podemos agregarlas a las tablas dinámicas o de Power View para obtener rápidamente buenos resultados, como grupos de datos de gran utilidad. Antes de crear jerarquías, tuvo que agregar campos individuales a la tabla dinámica y organizar los campos de la forma en que quería que se muestren.

En esta sección podrá usar las jerarquías creadas en la sección anterior para dar, de forma rápida, mayor precisión a la tabla dinámica. Después, creará la misma vista de tabla dinámica con los campos individuales de la jerarquía para poder comparar el uso de jerarquías con el uso de campos individuales.

1. Vuelva a Excel.

2. En Hoja1, quite los campos del área FILAS de Campos de tabla dinámica y, a continuación, quite todos los campos del área COLUMNAS. Asegúrese de que la tabla dinámica esté seleccionada (ahora es bastante pequeña, por lo que puede elegir la celda A1 para asegurarse de que esté seleccionada). Los únicos campos que quedan en la tabla dinámica son Medalla, en el área FILTROS, y Recuento de medallas, en el área VALORES. La tabla dinámica, casi vacía, debe verse como en la pantalla siguiente.

   

3. En el área Campos de tabla dinámica, arrastre DDE desde la tabla Eventos hasta el área FILAS. Después, arrastre Ubicaciones desde la tabla Anfitriones hasta el área COLUMNAS. Con el simple hecho de arrastrar esas dos jerarquías, la tabla dinámica se rellena con muchos datos, todos organizados según la jerarquía que definió en los pasos anteriores. La pantalla debe tener una apariencia similar a la de la siguiente pantalla.
   

4. Vamos a filtrar los datos un poco y ver solo las primeras diez filas de eventos. En la tabla dinámica, haga clic en la flecha de Etiquetas de fila, haga clic en (Seleccionar todo) para eliminar todas las selecciones y haga clic en las casillas junto a los primeros diez deportes. Ahora, la tabla dinámica es similar a la de la pantalla siguiente.
   

5. Puede expandir cualquiera de los deportes en la tabla dinámica (nivel superior de la jerarquía DDE) y ver información en el siguiente nivel inferior de la jerarquía (disciplina). Si existe un nivel inferior en la jerarquía para esa disciplina, puede expandir la disciplina para ver esos eventos. Puede hacer lo mismo para la jerarquía Ubicación, de la cual el nivel superior es Temporada, que se muestra como Verano e Invierno en la tabla dinámica. Cuando se expande el deporte Acuáticos, vemos todos sus elementos de disciplina secundarios y sus datos. Al expandir la disciplina Buceo de Deportes acuáticos, vemos también sus eventos secundarios, como se muestra en la pantalla siguiente. Podemos hacer lo mismo para Waterpolo, y ver que tiene solo un evento.
   

Al arrastrar esas dos jerarquías, creó rápidamente una tabla dinámica con datos interesantes y estructurados que puede analizar, filtrar y organizar.

Ahora vamos a crear la misma tabla dinámica, sin el beneficio de las jerarquías.

1. En el área Campos de tabla dinámica, quite Ubicaciones del área COLUMNAS. A continuación, quite DDE del área FILAS. Vuelve a tener una tabla dinámica básica.

2. En la tabla Anfitriones, arrastre Temporada, Ciudad, NOC_PaísRegión e IDEdición al área COLUMNAS y organícelos en ese orden, de arriba a abajo.

3. En la tabla Eventos, arrastre Deporte, Disciplina y Evento al área FILAS, y organícelos en ese orden, de arriba a abajo.

4. En la tabla dinámica, filtre Etiquetas de fila en los diez deportes superiores.

5. Contraiga todas las filas y columnas y después expanda por este orden, Deportes acuáticos, Buceo y Waterpolo. El libro es similar al de la pantalla siguiente.
   

La pantalla es similar, con la excepción de que se han arrastrado siete campos individuales a las áreas Campos de tabla dinámica, en lugar de solo dos jerarquías. Si es la única persona que crea informes de tablas dinámicas o de Power View basados en estos datos, la creación de jerarquías solo es algo cómodo. Sin embargo, cuando muchas personas crean informes y deben determinar el orden correcto de los campos para que las vistas sean adecuadas, las jerarquías se convierten en una mejora de la productividad y permiten la coherencia de los datos.

En otro tutorial, aprenderá cómo usar jerarquías y otros campos en informes visualmente atractivos creados con Power View.

Control y evaluación

Revise lo que ha aprendido

El libro de Excel tiene ahora un modelo de datos que incluye datos de varias fuentes que se relacionan mediante campos y columnas calculadas que ya existen. También tiene jerarquías que reflejan la estructura de los datos dentro de las tablas y permiten crear informes atractivos de forma rápida, coherente y sencilla.

Ha aprendido que crear jerarquías le permite especificar la estructura inherente de los datos y usar rápidamente datos jerárquicos en los informes.

En el siguiente tutorial de esta serie, creará con Power View informes visualmente atractivos sobre las medallas olímpicas. También hará más cálculos, optimizará los datos para acelerar la creación de informes e importará datos adicionales para hacer que esos informes sean aún más interesantes. He aquí un vínculo:

Tutorial 3: Crear informes de Power View basados en mapas

Evaluación

¿Desea saber qué tan bien recuerda lo que aprendió? Aquí tiene la oportunidad de hacerlo. La siguiente evaluación resalta las características, las capacidades o los requisitos que ha aprendido en este tutorial. En la parte inferior de la página, encontrará las respuestas. ¡Buena suerte!

Pregunta 1: ¿Cuál de las siguientes vistas le permite crear relaciones entre dos tablas?

A: Crear relaciones entre tablas de Power View.

B: Crear relaciones entre tablas utilizando la vista de diseño de Power Pivot.

C: Crear relaciones entre tablas utilizando la vista de cuadrícula en Power Pivot

D: Todas las respuestas anteriores

Pregunta 2: VERDADERO o FALSO: Puede establecer relaciones entre tablas basadas en un identificador único que se crea mediante las fórmulas de DAX.

A: VERDADERO

B: FALSO

Pregunta 3: ¿En cuál de las siguientes opciones se puede crear una fórmula de DAX?

A: En el área de cálculo de Power Pivot.

B: En una nueva columna de Power Pivot.

C: En cualquier celda de Excel 2013.

D: A y B.

Pregunta 4: ¿Cuál de las siguientes afirmaciones es cierta sobre las jerarquías?

A: Cuando se crea una jerarquía, los campos incluidos dejan de estar disponibles de forma individual.

B: Al crear una jerarquía, los campos incluidos y su jerarquía pueden usarse en herramientas cliente si se arrastra la jerarquía a un área de Power View o de la tabla dinámica.

C: Cuando se crea una jerarquía, los datos subyacentes del modelo de datos se combinan en un solo campo.

D: No se pueden crear jerarquías en Power Pivot.

Respuestas de la evaluación

1. Respuesta correcta: D

2. Respuesta correcta: A

3. Respuesta correcta: D

4. Respuesta correcta: B