• Author / Uploaded
• rodotifo

• Categories
• Tabla (base de datos)
• Microsoft Excel
• Fórmula
• Bases de datos
• Base de datos relacional

Citation preview

Power BI

(borrador)

Excel como instrumento de Business Intelligence. Transformar datos en conocimientos Jose Ignacio González Gómez

Departamento de Economía Contabilidad y Finanzas - Universidad de La Laguna

www.jggomez.eu

INDICE 1

2

Introducción a Power BI (Business Intelligence) .......................................................................... 1 1.1

Concepto de Business Intelligence ................................................................................................ 1

1.2

Como los estamos haciendo ............................................................................................................ 1

1.3

Como mejorar. Haciendo uso de Power BI, elementos que lo integran .......................... 2

Power Query, conexión a fuentes de datos. Buscando y organizando los datos ............... 3 2.1

Objetivo .................................................................................................................................................. 3

2.2

Esquema general de trabajo con Power Querry ..................................................................... 3

2.3

Lenguaje M............................................................................................................................................ 4

2.4

Principales razones para usar Power Query ............................................................................ 5

3 Power Pivot, creando nuestro modelo de datos, administrando una colección de tablas y relaciones desde dentro de Excel. ................................................................................................. 6 3.1

Objetivo .................................................................................................................................................. 6

3.2

Diferencias entre Power Pivot vs Access / Excel ..................................................................... 7

3.2.1

Power Pivot vs Base de datos relacionales (Access) ................................................. 7

3.2.2

Diferencia entre el modelo de datos con Power Pivot vs Excel ............................ 8

3.2.3

Power Pivot vs Tablas Dinámicas ..................................................................................... 9

3.3

Esquema general de funcionamiento ........................................................................................ 11

3.4

Creación del modelo de datos ...................................................................................................... 12

3.4.1

Obteniendo datos ................................................................................................................... 12

3.4.2

Relación entre tablas, obsolescencia de la función Buscar de Excel ................. 12

3.4.3

Crear una jerarquía de datos............................................................................................. 12

3.5

Cómo se almacenan los datos ...................................................................................................... 13

3.6

Guardar en SharePoint ................................................................................................................... 14

3.7

Cálculos en Power Pivot (DAX) .................................................................................................... 14

3.7.1

¿Qué es DAX?............................................................................................................................ 14

3.7.2

Tipos de cálculos. Medidas y columnas calculadas .................................................. 15

3.7.3

Crear fórmulas usando la barra de fórmulas.............................................................. 16

3.7.4

Usar varias funciones en una fórmula ........................................................................... 16

3.7.5

Comparación de funciones de DAX y funciones de Excel ...................................... 16

3.7.6

Indicadores clave de rendimiento (KPI) en Power Pivot ...................................... 17

3.8 4

5

6

En resumen ......................................................................................................................................... 18

Power View, creación de informes..................................................................................................... 20 4.1

Presentación y análisis de datos ................................................................................................. 20

4.2

Se necesita de Power Pivot para crear reportes en Power View ..................................... 20

4.3

En resumen ......................................................................................................................................... 20

Power Map (borrador)............................................................................................................................ 21 5.1

Presentación ....................................................................................................................................... 21

5.2

En resumen ......................................................................................................................................... 21

Referencias y recursos ............................................................................................................................ 21

Página |1

www.jggomez.eu

1

Introducción a Power BI (Business Intelligence) 1.1

Concepto de Business Intelligence

El Power BI consiste en la integración de diferentes componentes o elementos orientados a potenciar el tratamiento de datos y la generación de información desde nuestra hoja de cálculo Excel incrementando así el potencial de esta herramienta, en definitiva Power BI permite analizar e interactuar con una cantidad masiva de datos dentro de Excel. De esta forma se pretende dar respuesta desde Excel a la demanda empresarial de Business Intelligence, y que conceptualmente podemos resumir en los siguientes términos: “Es el conjunto de estrategias, tecnologías y metodologías que nos van a ayudar a convertir los datos en información de calidad, y dicha información en conocimiento que nos permita una toma de decisiones más acertadas y nos ayude así a mejorar nuestra competitividad 1.2

Como los estamos haciendo

Obtener los datos – Exportar a Excel o “copiar y pegar” – Repetir “N” Elaborar manualmente mediante fórmula

Ilustración 1

Y, a veces, agregamos tablas y gráficos dinámicos

Ilustración 2

¿Y si necesitamos hacer ese mismo informe periódicamente? – “Repetimos todo el proceso de forma manual” • Problema: – Trabajo mecánico y tedioso – Tiempo consumido – Posibilidad de cometer errores cada vez Hay solución…. Power BI

www.jggomez.eu

1.3

Página |2

Como mejorar. Haciendo uso de Power BI, elementos que lo integran

Por tanto los complementos de Excel que permiten alcanzar el objetivo planteado del Business Intelligence son:    

Power Query: Facilita el tratamiento y manipulación de grandes cantidades de datos de diferentes fuentes, tanto del propio Excel como de otras fuentes externas. Power Pivot: Complemento que permite procesar gran cantidad de información (datos) compleja en poco tiempo y de manera sencilla generando un modelo de datos (conjunto de tablas relacionadas). Power View: Herramienta que sirve para confeccionar presentaciones interactivas y visualmente intuitivas (a modo de Dashboard o Cuadro de mando). Power Map: Permite representar mapas georreferenciados o mapas adpatados.

Estos cuatro componentes están perfectamente integrados aunque puede ser usados independientemente. Así el Power BI amplia el uso de la hoja de cálculo Excel a una herramienta orientada al Business Intelligence y que en definitiva es la aplicación de conocimientos derivados del análisis de datos para obtener mejores resultados en la empresa,…. “ Transformar datos en conocimiento” Los pasos a seguir puede ser resumidos básicamente en: 

Crear un modelo de datos: o Importando datos a Excel o Ampliando las relaciones del modelo de datos usando Power Pivot y Dax  Creando informes o Con Power View o Con Power Map

Página |3

www.jggomez.eu

2

Power Query, conexión a fuentes de datos. Buscando y organizando los datos 2.1

Objetivo

A continuación presentamos el primer componente y dónde encaja en el concepto Power BI y como primera aproximación señalar que el componente Power Query nos permite enlazar a diferentes orígenes de datos, internos o externos e incluso conectarse a fuentes públicas de datos, trabajar con ellos adaptándolos a las necesidades que tengamos y llevarlos a Excel para seguir trabajando, creando así un modelo de datos (Power Pivot). Si usamos varios orígenes de datos, podemos filtrar los datos, darles forma, combinarlos y anexarlos con Power Query... sin tener que incluirlos en Excel y así posteriormente enviarlos directamente al modelo de datos de Power Pivot e incluso después de hacer eso, podemos crear cálculos y campos nuevos en Power Pivot para obtener solo los conjuntos de datos deseados y trabajar directamente con Excel. 2.2

Esquema general de trabajo con Power Querry

Con Power Query, podemos buscar datos en toda la organización y a través de Internet. Después de encontrar los datos que deseamos, podemos dar forma a diferentes conjuntos de datos de distintos orígenes, filtrarlos, combinarlos y anexarlos, todo ello sin tener que incorporar los datos en Excel. Después de darle a la consulta la forma que queremos y filtrarla, podemos descargarla en una hoja de cálculo de Excel, en el modelo de datos o en ambos. Hay todo tipo de orígenes de datos a los que se puede acceder con Power Query, como bases de datos de distintos formatos, texto plano, csv, etc. En el siguiente esquema presentamos un resumen de lo ¿qué es lo que hace? y ¿para qué sirve? este componente.

1 Extrae (o importa)

Extrae los datos desde prácticamente cualquier fuente de datos como una base de datos relacional, un archivo plano, json, xml, Azure, Hadoop y mucho más

2 Transforma

Transforma los datos - y al transformar nos referimos un MUCHAS de cosas, desde fusionar, combinar, limpieza, añadir o simplemente cambiar de alguna manera y enriquecerla para su uso posterior.

3 Carga (Excel o Power Pivot)

Carga los datos transformados a Excel o Power Pivot para que podamos analizarlo

Fuente: http://www.poweredsolutions.co

Por tanto del esquema anterior se desprende que Power Query es un complemento de Excel que permite al usuario conectarse a cualquier origen de datos; transformar, combinar y adaptar los datos según la necesidad, y cargarlos a una hoja Excel o a un modelo de datos (Power Pivot). Además, todo el proceso de conexión, transformación, combinado y adaptación de los datos queda grabado para poder ser reutilizado posteriormente a través del lengiaje .

www.jggomez.eu

Página |4

Otra forma de ver el esquema de funcionamiento es el siguiente:

En algún momento de la vida de un usuario de Excel, todos nos hemos enfrentado a un dilema similar. ¿Cómo puedo combinar múltiples hojas, tablas, csv o txt? (puedo combinarlos juntos??) Como solíamos resolver este escenario Tiempo atrás (antes Power Query) de hecho, tuvimos algunas formas de hacerlo pero no eran tan fáciles de usar y dependíamos en gran medida de codificación o de alguna manera tediosa de hacerlo. Las formas más comunes fueron: 1. Utilizando sentencias SQL para unir múltiples archivos 2. Crear un código VBA que haga el trabajo por mi 3. Ir con la forma tediosa de combinar los archivos manualmente (tal vez con Excel o con Access) Pero ahora contamos con una manera más fácil y optimizada de hacer esto a través de PowerQuery . Antes de empezar, permítanme decir esto en voz alta - Power Query es la mejor herramienta que Microsoft ha creado para Excel en los últimos 5 años.Es como tener una interfaz fácil de usar que crea algún ultra código VBA optimizado malvado y fresco para mí sin necesidad de aprender cualquier VBA en absoluto El público objetivo que Power Query tiene es mucho más amplio que el de Power Pivot. Principalmente porque Power Query se centra en la limpieza, enriquecimiento y la preparación de sus datos - lo cual es algo que la mayoría de los usuarios de Excel hacen a diario - y debido a su facilidad de uso.

2.3

Lenguaje M

Otro punto a tomar en consideración es que Power Query utiliza una interfaz de usuario distintivo con el fin de trabajar en función de cada paso. Esto significa que usted crea pasos de transformación. Un poco la forma como primero añade los datos (paso 1), a continuación, quita algunas columnas (paso 2) y finalmente renombra algunas otras columnas (paso 3). Además, todos estos pasos se convierten a su propio código o lenguaje llamado lenguaje M que no es similar a DAX o Excel, pero bastante fácil de entender una vez que entienda los conceptos básicos. Presentamos en la siguiente ilustración un ejemplo en el que se muestra el interfaz de usuario de Power Query y cómo se crean los pasos y también cómo el código M se muestra a la derecha.

www.jggomez.eu

Página |5

Si ha intentado ejecutar un código VBA en la nube o en cualquier lugar cerca PowerBI o SharePoint, obtendrá nada porque VBA no es realmente compatible con la web. Por el contrario, el idioma M y Power Query están totalmente soportados en Power BI y están liberando activamente nuevas actualizaciones sobre una base mensual, ya sea corrigiendo errores o añadiendo nuevas características y sobre todo fuentes de datos. Echa un vistazo a esta captura de pantalla para ver algunas de las fuentes de datos agregadas más recientes donde destaca SAP y SalesForce están listadas como fuentes de datos soportadas para Power Query así como muchas otras fuentes de datos) 2.4

Principales razones para usar Power Query

Entre las principales razones para usar este componente destacamos las siguientes:

  



Facilidad de uso – se siente intuitivo! Microsoft ha hecho un trabajo increíble en hacerlo muy fácil de utilizar Utilidad práctica – por supuesto que no estaría utilizando esto si no fuera práctico o útil para usted. Dale una oportunidad! Capacidad de código personalizado – siempre puedes crear tu propio código M en vez de ir a través de la interfaz de usuario, pero el 90% de las veces ni siquiera tendrás que preocuparte por esto, ya que todo está en la interfaz de usuario Lenguaje fácil de aprender – el idioma M podría ser difícil al principio, pero es igual que una persona que pasa por incomprendido, porque es la nueva persona en la vecindad. Una vez que llegas a conocerlo / ella te darás cuenta de que es una persona bastante buena =)

www.jggomez.eu

3

Página |6

Power Pivot, creando nuestro modelo de datos, administrando una colección de tablas y relaciones desde dentro de Excel. 3.1

Objetivo

Si eres uno de los miles de usuarios de Excel que lucha con la creación de nuevos códigos VBA o tratar de llegar a soluciones que son semi-dinámicas debido a las limitaciones del tradicional Excel, Power Pivot entonces hará tu vida más fácil en casi todos los sentidos. Pero ¿Por qué te debe de interesar Power Pivot?, entre las    

Puede ayudarnos a ejecutar un mejor proceso para llegar a la misma o incluso una idea mejor (consumiendo menos tiempo) Si utilizamos tablas dinámicas entonces considere esto como las tablas dinámicas que se ejecutan en turbo o supercargador (herramientas familiares y la misma interfaz) Si sucede que ejecutamos grandes volúmenes de datos (datos grandes para de Excel son superiores a 1 millón de filas) entonces debemos considerar el uso de Power Pivot . Si nos quedamos sin memoria RAM o CPU por ver tantas funciones como hasta index / match, BUSCARV, BUSCARH, operaciones de búsqueda y una cantidad muy alta de las columnas a continuación, debemos considerar el uso de Power Pivot en la que vamos a ahorrar el consumo de memoria mediante el uso de relaciones en lugar de las funciones de búsqueda.

Por tanto Power Pivot es un complemento eficaz para procesar y analizar datos, transformarlos en información significativa creando modelos de datos (entendidos estos como conjunto de tablas y sus relaciones entre diferentes procesos y funciones empresariales). Permite realizar cálculos con las funciones en lenguaje DAX. Con la posibilidad de que se puede permitir colaborar y compartir al resto del equipo de trabajo. Es el núcleo de toda la experiencia Power BI. Es el motor que funciona todos los cálculos, acoge todos los datos y crea el modelo analítico que se puede explotar más tarde con otras herramientas como tablas dinámicas, gráficos o más.

Por tanto da la posibilidad de crear un Modelo de datos, un conjunto de tablas con relaciones. El modelo de datos que se ve en un libro de Excel es el mismo que aparece en la ventana de Power Pivot. Los datos importados a Excel están disponibles en Power Pivot y viceversa.

www.jggomez.eu

3.2

Página |7

Diferencias entre Power Pivot vs Access / Excel

3.2.1 Power Pivot vs Base de datos relacionales (Access) Fuente: http://www.poweredsolutions.co/es/2013/11/11/power-pivot-vs-bases-de-datos-relacionales-tradicionales/

Lo primero: Definamos las principales funciones de cada una Hay que tomar en cuenta que cada herramienta tiene su propósito y es necesario utilizar la herramienta adecuada para cada trabajo/tarea. Ahora, cuál es la función principal de Power Pivot y de una base de datos como Access?  

Power Pivot = Analizar Datos (es necesario que los mismos sean importados a Power Pivot) Access = Registrar, modificar y remover datos (gestión completa de tablas y datos dentro de ellas)

Luego de plantearnos esto, conocemos que dentro de Access podemos crear algunos reportes y analizar los datos de ciertas formas pero al compararlo con el motor que utiliza Power Pivot la verdad es que deja muy rezagado al motor que utiliza Access. Pero, qué diferencias existen entre ambas? Veamos la siguiente tabla para tener una idea:

Además de los aspectos arriba mencionados, hay muchos que tal vez ya conozcas como el motor de análisis que utiliza Power Pivot y como Power Pivot está integrado con Excel lo que hace a Excel una versión expandida/supe cargada de Excel – Excel 2.0? Excel trae consigo las tablas dinámicas que también existen en Access pero esta vez tenemos los llamados segmentadores de datos (slicers) y el lenguaje DAX que puede ser combinado con las formulas tradicionales de Excel (Cube Formulas y otras). Conclusiones 1. Power Pivot es algo diferente. Power Pivot es algo diferente a una base de datos relacional tradicional. Algo como una manzana y una base de datos tradicional es una naranja…pero ambas son frutas 2. En tal caso, podríamos decir que Power Pivot es la versión lite o hermana menor de SQL Server Analysis Services 3. Power Pivot presenta un motor de análisis y de almacenamiento de datos , mas no creación de datos. Podemos analizar los datos mas no crearlos (solo cargarlos/importarlos)

Página |8

www.jggomez.eu

4. Puedes pensar en Power Pivot como un asistente para el modelado de datos (creación de un cubo con almacenamiento de datos en memoria utilizando el motor xVelocity anteriormente conocido como VertiPaq) 5. Necesitamos una muy buena base de datos tradicional (o nueva) como fuente de datos para sacarle el mayor provecho a Power Pivot 3.2.2 Diferencia entre el modelo de datos con Power Pivot vs Excel

La diferencia básica entre Power Pivot y Excel es que se puede crear un modelo de datos mucho más sofisticado con el primero. Comparemos algunas tareas. Tarea

En Excel

En Power Pivot

Importar datos de orígenes diferentes, como bases de datos grandes, fuentes de distribución de datos públicas, hojas de cálculo y archivos de texto en el equipo.

Importar todos los datos de un origen de datos.

Filtrar los datos y cambiar las columnas y las tablas mientras se importan.

Crear tablas

Las tablas pueden estar en cualquier hoja de cálculo del libro Las hojas de cálculo pueden tener más de una tabla.

Las tablas están organizadas por páginas con pestañas individuales en la ventana de Power Pivot.

Editar datos en una tabla

Puede modificar los valores de celdas individuales de una tabla.

No puede modificar celdas individuales.

En el cuadro Relaciones.

En la vista de diagrama o el cuadro de diálogo Crear relaciones.

Crear relaciones entre tablas Crear cálculos

de

diálogo

Use las fórmulas de Excel.

Escriba fórmulas avanzadas con el lenguaje de expresiones de análisis de datos (DAX).

Crear jerarquías

Defina Jerarquías para usar en cualquier parte de un libro, incluido Power View.

Crear indicador clave de rendimiento (KPI)

Crear KPI para usar en informes de Power View y tablas dinámicas.

Crear perspectiva

Crear Perspectivas para limitar el número de columnas y tablas que los usuarios ven.

Crear tablas dinámicas y gráficos dinámicos

Crear informes de tabla dinámica en Excel. Crear un gráfico dinámico

Haga clic en el botón Tabla dinámica de la ventana de Power Pivot.

Mejorar un modelo para Power View

Crear un modelo de datos básico.

Realizar mejoras como identificar los campos predeterminados, las imágenes y los valores únicos.

Usar VBA en Excel.

No use VBA en la ventana de Power Pivot.

Agrupar en una tabla dinámica de Excel

Usar DAX en columnas calculadas y campos calculados

Usar Visual Basic para Aplicaciones (VBA) Agrupar datos

https://support.office.com/es-es/article/Power-Pivot-an%C3%A1lisis-de-datos-eficaz-y-modelado-de-datos-en-Excel-d7b119ed-1b3b-4f23b634-445ab141b59b?CorrelationId=526aeec7-f2cd-42d2-ade6-82d7acbe2e76&ui=es-ES&rs=es-ES&ad=ES&fromAR=1

www.jggomez.eu

Página |9

3.2.3 Power Pivot vs Tablas Dinámicas Fuente: http://www.poweredsolutions.co/es/2013/07/22/power-pivot-sobre-tablas-dinmicas-tradicionales-por-qu/ Usuario Común: Ya utilizo Tablas Dinámicas. ¿Por qué debo “complicarme” con Power Pivot?

Una de las primeras preguntas que te podrías hacer es ¿Por qué utilizar Power Pivot? y la razón es muy simple. Power Pivot fue diseñado para ayudarnos en cosas que las tablas dinámicas y el Excel tradicional no pueden llegar, por ejemplo:   

Nos ayuda en rendimiento (+ veloz) Compresión de Datos (- espacio) y Mejores funciones para análisis

Déjame plantearte un ejemplo muy común que posiblemente parezca muy familiar. Algo muy sencillo que siempre hemos querido, pero que nunca hemos podido plantear de una buena manera en el Excel Tradicional. Conteos Distintivos con MILLONES de Filas - DISTINCTCOUNT() Tomemos el siguiente ejemplo. Tengo un archivo de texto de +1.6 millones de filas que voy a cargar a Power Pivot. Tomando en cuenta las limitantes de Excel, esto simplemente no es posible por las razones anteriormente mencionadas.

Algo que muy comúnmente necesitamos es un conteo distintivo de productos, eventos, clientes o cualquier otra dimensión que necesite de este tipo de análisis. Imaginemos que en esta tabla tenemos una columna de Clientes representada por la columna ‘Cliente’ y necesitamos saber cuantos clientes han comprado cada uno de los productos representados por la columna ‘Producto’. Un simple CONTEO de una tabla dinámica no nos ayuda en este caso y mucho menos si luego trato de filtrarlo en un periodo especifico. (Tengo una columna de fechas que planeo utilizar luego) Una vez tengo la tabla lista y tengo definido lo que quiero hacer, paso a darle clic a botón de Tabla Dinámica dentro de la ventana de Power Pivot.

www.jggomez.eu

P á g i n a | 10

Una vez creemos la tabla dinámica, vamos a desplazar el campo de Producto en filas y el campo de Cliente en valores y lucirá de la siguiente forma:

Debido a que no queremos una suma, necesitamos cambiar esto dándole clic izquierdo en donde dice ‘Sum of Cliente’ en la sección de valores y luego seleccionamos:

Lo que al final nos dará un resultado como el siguiente:

www.jggomez.eu

3.3

P á g i n a | 11

Esquema general de funcionamiento

Fuente: http://www.poweredsolutions.co/es/2013/11/11/power-pivot-vs-bases-de-datos-relacionales-tradicionales/ Presentamos a continuación un diagrama para entender cómo trabaja Power Pivot

Power Pivot trabaja como una base de datos relacional almacenada en memoria. Es de tipo tabular (de ahí el nombre de Modelo Tabular) y el lenguaje para interactuar con dicho modelo se llama DAX. El modelo tabular se rige por los mismos conceptos básicos de bases de datos relacionales en donde tenemos: 

La tabla primaria (tabla de hechos) = un ejemplo muy común es aquella tabla de órdenes de ventas que tiene miles o millones de registros



Las tablas de Dimensiones = las cuales presentan atributos y llaves de tabla que están relacionadas con la tabla primaria. Un ejemplo muy común es la tabla de productos la cual contiene una lista única de los productos que vendemos.

Ahora, puede que dicha tabla de dimensión tenga otra relación con una tabla de Subcategorías de productos y dicha tabla, a su vez, tenga una tabla de Categorías de productos como se muestra en la figura de abajo:

www.jggomez.eu

3.4

P á g i n a | 12

Creación del modelo de datos

3.4.1 Obteniendo datos

Importar datos relacionales con el complemento Power Pivot a menudo es una alternativa más rápida y más eficaz que importarlos con Excel. La forma general para ello es a través del menú Inicio > Obtener datos externos:  

De base de datos si los datos son relacionales o dimensionales. Importar datos de otros orígenes de datos como Microsoft Azure Marketplace o

una fuente de distribución de datos de OData. Las ventajas de importar con Power Pivot incluyen la posibilidad de:   

Filtrar los datos innecesarios para importar únicamente un subconjunto. Cambiar los nombres de las tablas las y columnas a medida que importa los datos. Pegue una consulta predefinida para seleccionar los datos que se devolverán. 3.4.2 Relación entre tablas, obsolescencia de la función Buscar de Excel

¿Alguna vez ha usado BUSCARV para traer una columna de una tabla a otra tabla? Ahora con Excel contamos con un modelo de datos integrado, BUSCARV ha quedado obsoleta. Se puede crear una relación entre dos tablas de datos basada en los datos que coincidan entre ellas. Después, se pueden crear hojas de Power View y generar tablas dinámicas y otros informes con campos de cada tabla, incluso cuando las tablas sean de orígenes diferentes. Por ejemplo, si tiene datos de ventas de clientes, puede que quiera importar y relacionar datos de inteligencia de tiempo para analizar patrones de venta por año y mes. Todas las tablas de un libro se muestran en la tabla dinámica y las listas de campos de Power View. Al importar tablas relacionadas desde una base de datos relacional, Excel casi siempre puede crear esas relaciones en el modelo de datos que genera en segundo plano. En todos los demás casos, se deberán crear las relaciones manualmente. 3.4.3 Crear una jerarquía de datos

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. Orta forma de entender las jerarquías es como agrupaciones de elementos de datos que comparten una relación lógica vertical. Por ejemplo, una jerarquía geográfica podría ser comunidad autónoma, provincia y localidad; la comunidad autónoma está por encima de la provincia en la jerarquía (y contiene una o varias provincias), y la provincia se encuentra por encima de la localidad (y contiene muchas localidades). Usar jerarquías en Power Pivot permite crear informes para explorar los datos en profundidad. 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

www.jggomez.eu

P á g i n a | 13

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.

Tomando como referencia el caso anterior tenemos que para crear una jerarquía Deporte tendremos que proceder de la siguiente forma: 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 al de la ilustración. 3.5

Cómo se almacenan los datos

Los datos con los que se trabaja en la ventana de Power Pivot y en Excel se almacenan en una base de datos analítica dentro del libro de Excel; un eficaz motor local carga consulta y actualiza los datos de dicha base de datos. Dado que los datos están en Excel, están inmediatamente disponibles para las tablas dinámicas, los gráficos dinámicos, Power View y otras características de Excel usadas para agregar datos e interactuar con ellos. Excel 2013 proporciona todas las funciones de presentación e interactividad con los datos; los datos y los objetos de presentación de Excel se encuentran en el mismo archivo de libro. Power Pivot admite archivos de hasta 2 GB de tamaño y permite trabajar con hasta 4 GB de datos en memoria.

www.jggomez.eu

3.6

P á g i n a | 14

Guardar en SharePoint

Los libros que se modifican con Power Pivot se pueden compartir con otras personas de la misma forma que se comparten otros archivos; aunque se obtienen más ventajas si el libro se publica en un entorno de SharePoint que tenga habilitados los Servicios de Excel. En el servidor de SharePoint, Servicios de Excel procesa y presenta los datos en una ventana de explorador donde otras personas pueden analizarlos. SharePoint 2013 permite agregar Power Pivot para SharePoint 2013 con el fin de obtener más soporte de colaboración y de administración de documentos, que incluye la Galería de Power Pivot, el panel de administración de Power Pivot en Administración central, actualización de datos programada y la posibilidad de usar un libro publicado como origen de datos externo desde su ubicación en SharePoint. 3.7

Cálculos en Power Pivot (DAX)

3.7.1 ¿Qué es DAX?

DAX es una recopilación de funciones, operadores y constantes que se pueden usar en una fórmula o expresión para calcular y devolver uno o varios valores. Dicho más fácilmente, DAX ayuda a crear información de datos nueva que ya está en un modelo. De esta forma podemos afirmar que el lenguaje DAX (Expresiones de análisis de datos) es un lenguaje de fórmulas que permite a los usuarios definir cálculos personalizados en tablas de PowerPivot (columnas calculadas) y en tablas dinámicas de Excel (medidas). DAX incluye algunas de las funciones que se usan en fórmulas de Excel y funciones adicionales que están diseñadas para trabajar con datos relacionales y realizar agregaciones dinámicas. ¿Por qué DAX es tan importante? Es fácil crear un libro e importar algunos datos en él. Puede incluso crear tablas dinámicas o gráficos dinámicos que muestran información importante sin utilizar las fórmulas de DAX. Pero, ¿qué ocurre si necesita analizar datos críticos de ventas en varias categorías de productos y para distintos intervalos de fechas? ¿O si necesita combinar datos importantes del inventario de varias tablas de orígenes de datos diferentes? Las fórmulas de DAX proporcionan esta capacidad y muchas otras funciones importantes. Aprender a crear fórmulas DAX eficaces le ayudará a sacar el máximo partido de los datos. Las fórmulas DAX son muy similares a las fórmulas de Excel. Para crear una, debe escribir un signo igual seguido de una expresión o nombre de función y los argumentos o valores obligatorios. Como en Excel, DAX proporciona una variedad de funciones que se pueden usar para trabajar con cadenas, realizar cálculos mediante fechas y horas o crear valores condicionales. Sin embargo, las fórmulas de DAX son diferentes en los siguientes puntos relevantes: 





Una función de DAX siempre hace referencia a una columna completa o una tabla. Si solo desea usar valores concretos de una tabla o columna, puede agregar filtros a la fórmula. Si desea personalizar los cálculos fila a fila, PowerPivot proporciona funciones que permiten usar el valor de la fila actual o un valor relacionado para realizar cálculos que varíen según el contexto. DAX incorpora un tipo de función que devuelve una tabla como resultado, en lugar de un valor único. Estas funciones se pueden usar para proporcionar la entrada a otras funciones, calculando así valores para tablas o columnas completas.

www.jggomez.eu



P á g i n a | 15

Algunas funciones de DAX proporcionan inteligencia de tiempo, que le permite crear cálculos usando intervalos de fechas significativos y comparar los resultados por períodos paralelos. 3.7.2 Tipos de cálculos. Medidas y columnas calculadas

Puede usar las fórmulas DAX en tablas de PowerPivot o en tablas dinámicas de Excel:  Puede usar fórmulas en columnas calculadas, agregando una columna y escribiendo a continuación una expresión en la barra de fórmulas. Estas fórmulas se crean en la ventana de PowerPivot.  Puede usar fórmulas en medidas. Cree estas fórmulas en Excel, haciendo clic en Agregar medida en una tabla dinámica o gráfico dinámico existente de PowerPivot. Para obtener más información, vea Medidas en PowerPivot. La misma fórmula puede comportarse de forma diferente dependiendo de si se usa en una columna calculada o una medida. En una columna calculada, la fórmula siempre se aplica a todas las filas de la columna en toda la tabla. Dependiendo del contexto de la fila, el valor podría cambiar. En una medida, sin embargo, el cálculo de resultados depende en gran parte del contexto. Es decir, el diseño de la tabla dinámica y la opción de encabezado de fila y columna afectan a los valores que se emplean en los cálculos. Columnas calculadas Una columna calculada es una columna que se agrega a una tabla PowerPivot existente. En lugar de pegar o importar los valores de la columna, se crea una fórmula de DAX que los define. Si incluye la tabla PowerPivot en una tabla dinámica (o gráfico dinámico), se puede utilizar la columna calculada tal como lo haría con cualquier otra columna de datos. Las fórmulas de columnas calculadas son muy similares a las fórmulas creadas en Excel. A diferencia de Excel, sin embargo, no se puede crear una fórmula diferente para las diferentes filas de una tabla; la fórmula de DAX se aplica automáticamente a toda la columna. Cuando una columna contiene una fórmula, el valor se calcula para cada fila. Los resultados se calculan para la columna en cuanto crea la fórmula. Solo se recalculan los valores de columna si los datos subyacentes están actualizados o si se utiliza el recálculo manual. Medidas Una medida es una fórmula que se crea específicamente para su uso en una tabla dinámica (o gráfico dinámico) que use datos PowerPivot. Las medidas pueden estar basadas en funciones de agregación estándar, como COUNT o SUM, o puede definir su propia fórmula utilizando DAX. Una medida se utiliza en el áreaValores de una tabla dinámica. Si desea colocar los resultados calculados en una área diferente de una tabla dinámica, utilice en su lugar una columna calculada. Para crear una medida, debe agregar primero una tabla dinámica o gráfico dinámico a su libro PowerPivot. Cuando se define una fórmula para una medida, no ocurre nada hasta que la medida se coloca en una tabla dinámica. Al agregar la medida, la fórmula se evalúa para cada celda del área Valores de la tabla dinámica. Puesto que se crea un resultado para cada combinación de encabezados de fila y columna, el resultado para la medida puede ser diferente en cada celda de la tabla dinámica. La definición de la medida que crea se guarda con su tabla de dato de origen. Aparece en la Lista de campos de PowerPivot y está disponible para todos los usuarios del libro.

www.jggomez.eu

P á g i n a | 16

3.7.3 Crear fórmulas usando la barra de fórmulas

PowerPivot, al igual que Excel, proporciona una barra de fórmulas para facilitar la creación y modificación de fórmulas, y una función Autocompletar para minimizar los errores tipográficos y sintácticos. 



Para escribir un nombre de una tabla. Empiece a escribir el nombre de la tabla. La función Autocompletar fórmula proporciona una lista desplegable que contiene nombres válidos que comienzan con esas letras. Para escribir el nombre de una columna. Escriba un paréntesis y elija la columna en la lista de columnas de la tabla actual. Para una columna de otra tabla, empiece a escribir las primeras letras del nombre de la tabla y, a continuación, elija la columna en la lista desplegable Autocompletar. 3.7.4 Usar varias funciones en una fórmula

Las funciones se pueden anidar, es decir, puede usar los resultados de una función como argumento de otra función. Puede anidar hasta 64 niveles de funciones en columnas calculadas. Sin embargo, el anidamiento puede dificultar la creación de fórmulas o la solución de sus problemas. Muchas funciones de PowerPivot están diseñadas para usarse exclusivamente como funciones anidadas. Estas funciones devuelven una tabla, que no se puede guardar directamente como resultado en el libro de PowerPivot, pero que se debe proporcionar como entrada para una función de tabla. Por ejemplo, las funciones SUMX, AVERAGEX y MINX requieren una tabla como primer argumento. 3.7.5 Comparación de funciones de DAX y funciones de Excel

Aunque la biblioteca de funciones de DAX está basada en la biblioteca de funciones de Excel, existen muchas diferencias entre ellas. En esta sección se resumen las diferencias y similitudes entre las funciones de Excel y las funciones de DAX. 

Muchas funciones de DAX tienen el mismo nombre y el mismo comportamiento general que las funciones de Excel, pero se han modificado para aceptar tipos diferentes de entradas y, en algunos casos, podrían devolver un tipo de datos diferente. Generalmente, las fórmulas de DAX no se pueden usar en un libro de Excel, y las fórmulas de Excel no se pueden emplear en un libro de PowerPivot sin realizar alguna modificación.



Las funciones de DAX nunca usan un rango de celdas o un rango como referencia; en su lugar, usan una columna o una tabla como referencia.



Las funciones de fecha y hora de DAX devuelven un tipo de datos datetime. En contraste, las funciones de fecha y hora de Excel devuelven un entero que representa una fecha como un número de serie.



Muchas de las nuevas funciones de DAX devuelven una tabla de valores o realizan cálculos basados en una tabla de valores como entrada. En cambio, Excel no tiene ninguna función que devuelva una tabla, pero algunas funciones pueden trabajar con matrices. La capacidad de hacer referencia con facilidad a tablas y columnas completas es una nueva característica de PowerPivot.



DAX proporciona unas funciones de búsqueda nuevas parecidas a las funciones de búsqueda basada en vectores y matrices de Excel. Sin embargo, las funciones de DAX requieren que se establezca una relación entre las tablas.

www.jggomez.eu



P á g i n a | 17

DAX no admite el tipo de datos variant de Excel. Se espera que los datos de una columna de basen siempre en el mismo tipo de datos. Si los datos no son del mismo tipo, DAX cambia la columna completa al tipo de datos que mejor se acomode a todos los valores. 3.7.6 Indicadores clave de rendimiento (KPI) en Power Pivot

En terminología empresarial, un KPI es una medida cuantificable para medir los objetivos empresariales. Por ejemplo, el departamento de ventas de una organización puede usar un KPI para medir la ganancia bruta mensual con respecto a la ganancia bruta proyectada. El departamento de contabilidad puede medir los gastos mensuales con respecto a los ingresos para evaluar los costos, y un departamento de recursos humanos puede medir la facturación trimestral por empleado. Cada uno es un ejemplo de KPI. Los profesionales de negocios usan con más frecuencia KPI que se agrupan en un cuadro de mandos para obtener un resumen histórico rápido y preciso del éxito empresarial o para identificar tendencias. Un KPI contiene: 

Valor de Base. Un valor de Base se define como un campo calculado que se resuelve en un valor. Este valor, por ejemplo, se puede crear como un agregado de las ventas o se puede crear para definir los beneficios de un período determinado.



Valor de Objetivo. Un valor de Objetivo se define como un campo calculado que se resuelve en un valor, o bien un valor absoluto. Por ejemplo, se puede usar como valor de Objetivo un campo calculado con el que los directores empresariales de una organización desean comparar el rendimiento del departamento de ventas en relación con una cuota determinada, donde el campo calculado de presupuesto representaría el valor de Objetivo. Un ejemplo donde se usaría un valor absoluto como valor de Objetivo sería un caso en el que el director de recursos humanos deseara evaluar el número de días de ausencia de cada empleado en comparación con la media, donde el número medio de días de ausencia representaría el valor absoluto.



Umbrales de estado. Un umbral de estado se define como el intervalo entre el umbral inferior y superior. El umbral de estado se muestra con un gráfico para que los usuarios puedan consultar fácilmente el estado del valor de Base en comparación con el valor de Objetivo.

Ejemplo La directora de ventas de Adventure Works desea crear una tabla dinámica que pueda usar para mostrar rápidamente si los empleados de ventas cumplen o no su cuota de ventas en un determinado período (año). Quiere que la tabla dinámica muestre, de cada empleado de ventas, el importe real de ventas en dólares, el importe de la cuota de ventas en dólares y un gráfico sencillo que muestre el estado para saber si cada empleado de ventas se encuentra por debajo, al nivel o por encima de su cuota de ventas. Quiere poder segmentar los datos por año. Para ello, la directora de ventas opta por agregar un KPI de ventas al libro AdventureWorks. A continuación, creará una tabla dinámica con los campos (campos calculados y KPI) y los segmentadores, con el fin de analizar si los empleados de ventas cumplen sus cuotas o no. En Power Pivot, se crea un campo calculado en la columna SalesAmount de la tabla FactResellerSales, que proporciona el importe real de las ventas en dólares

www.jggomez.eu

P á g i n a | 18

correspondiente a cada empleado de ventas. Este campo calculado definirá el valor de base del KPI. El director de ventas puede seleccionar una columna y hacer clic enAutosuma, en la pestaña Inicio, o escribir una fórmula en la barra de fórmulas. Se creará el campo calculado Sales con la siguiente fórmula: Sales:=Sum(FactResellerSales[SalesAmount]) La columna SalesAmountQuota de la tabla FactSalesQuota tiene definida una cuota de importe de ventas para cada empleado. Los valores de esta columna servirán como campo calculado de Objetivo (valor) en el KPI. Se creará el campo calculado SalesAmountQuota con la siguiente fórmula: Target SalesAmountQuota:=Sum(FactSalesQuota[SalesAmountQuota]) Ahora que se han creado los campos calculados que servirán de valor de base y valor de objetivo del KPI, el campo calculado Ventas se extiende a un nuevo KPI de Ventas. En el KPI de Ventas, el campo calculado de objetivo SalesAmountQuota se define como el valor de objetivo. El umbral de estado se define como un intervalo por porcentajes cuyo objetivo es el 100 %, es decir, que las ventas reales definidas por el campo calculado Ventas coinciden con el importe de cuota definido en el campo calculado de objetivo SalesAmountQuota. Los porcentajes inferior y superior se definen en la barra de estado, y se selecciona un tipo de gráfico. Indicadores clave de rendimiento en Power Pivot Ahora, la directora de ventas puede crear una tabla dinámica agregando el valor de Base del KPI, el valor de Objetivo y el estado al campo Valores. La columna Employees se agrega al campo RowLabel, y la columna CalendarYear se agrega como segmentador. Ahora, la directora de ventas puede ver rápidamente el estado de las ventas del departamento de ventas, y segmentar por año el importe de ventas real, el importe de la cuota de ventas y el estado de cada empleado de ventas. Puede analizar las tendencias de ventas de un año a otro para determinar si necesita o no ajustar la cuota de ventas de un empleado de ventas. 3.8

En resumen

En Power Pivot, puede personalizar, ampliar con cálculos y jerarquías y administrar el eficaz modelo de datos que forma parte de Excel. Power Pivot funciona a la perfección y automáticamente con Power Query, y con otras características de Excel, por lo que le permite administrar y ampliar (con cálculos y jerarquías) su propia base de datos personalizada (el modelo de datos) en el entorno de Excel que ya conoce. Power Pivot incluye todos los datos que incorpore desde Power Query, más todos los datos que agregue al modelo. Además, todo el modelo de datos de Power Pivot (que incluye tablas,

www.jggomez.eu

P á g i n a | 19

columnas, cálculos, jerarquías y todas las demás personalizaciones) se expone como elementos listos para crear informes en Power View. 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. Cuestiones: ¿Por qué es importante convertir datos importados en tablas? Si los datos importados se convierten en tablas, se pueden incluir en el modelo de datos y puede dejarse disponibles para tablas dinámicas, Power Pivot y Power View.

www.jggomez.eu

4

P á g i n a | 20

Power View, creación de informes 4.1

Presentación y análisis de datos

Power View usa el modelo de datos para dar vida a esos datos. Crea informes y deje que otros usuarios interactúen con ellos y los exploren en profundidad. Las visualizaciones cambiarán a medida que seleccionan los elementos del informe. Permite visualización realmente intuitiva y mejor análisis de los datos en la hoja de cálculo. 4.2

Se necesita de Power Pivot para crear reportes en Power View

Para poder hacer que todo esto trabaje, es necesario tener Power Pivot y configurar algunos campos para que trabajen de manera correcta. Power Pivot se encarga del motor para las calculaciones mientras que Power View es el motor de la visualización. Para crear un nuevo informe de Power View en Excel, debemos seleccionar el botón Power View, que se encuentra en la sección Informes de la cinta de opciones Insertar de Excel. Se crea una nueva pestaña con una hoja de cálculo.

La pestaña Power View solo aparece en la cinta de opciones cuando el informe de Power View es la hoja de cálculo activa. Con la nueva hoja de informe de Power View generada, aparece la pestaña Power View en la cinta de opciones.

Nota: si se activó la casilla Cargar en libro en Power Query, verá tablas adicionales en el panel Campos de Power View, una por cada consulta que se cargó en el libro. Power View incluye todas las tablas del modelo de datos y el libro en Campos de Power View. Puede volver a Power Query y desactivar la casilla Cargar en libro para quitar esa tabla del libro y de Campos de Power View. 4.3

En resumen

Con la colección de herramientas de visualización y funciones de filtrado disponibles en Power View, el único límite de sus posibilidades es la imaginación y la experimentación.

www.jggomez.eu

5

P á g i n a | 21

Power Map (borrador) 5.1

Presentación

Este complemento está disponible a partir la versión Excel 2013 y su principal característica está en que permite realizar mapas con datos muy visuales en 3D a partir de los datos de una hoja de cálculo Excel. Una de las últimas novedades de Excel es Power Map, una característica de Excel para la creación de mapas geocéntricos y temporales que convierte datos de ubicación en visualizaciones de mapas 3D eficaces e interesantes. Con Power Map, puede crear informes basados en la ubicación, que se visualizan a lo largo de una línea temporal y que permiten recorrer los datos visualmente y proporcionan perspectivas únicas. Power Map es una nueva visualización en 3D (Geoespacial), como complemento para Excel, para mapear, explorar e interactuar con los datos geográficos y temporales, lo que permite a las personas descubrir y compartir nuevos conocimientos 5.2

En resumen

Resumen de Power Map: con la característica de Excel Power Map, los mapas cobran un significado totalmente nuevo. Con la funcionalidad de modelado 3D y los paseos interactivos, Power Map puede hacer que los usuarios que visualizan los mapas vean los datos como en un vuelo (es como un paseo en helicóptero por los datos 3D, con usted al mando). Así, lleva la interactividad a un nivel completamente nuevo. Con los controles de zoom, desplazamiento lateral y altitud, Power Map es, aparte de los paseos interactivos, una forma impresionante de visualizar los datos. Seleccione puntos de datos, vea los datos desde el espacio o de cerca y seleccione cualquier punto de datos para obtener información. También puede crear notas que resalten la información directamente en el mapa y llevar los análisis y las perspectivas a un mundo visual en 3D.

6

Referencias y recursos

http://hojasdecalculo.about.com/od/Excel_avanzado/fl/Descubre-los-diferentes-Complementos-Power-a-antildeadir-a-tuExcel.htm http://es.slideshare.net/sramosl/power-bi-power-pivot-power-view-power-map-power-query http://www.unocero.com/2013/07/12/power-bi-una-nueva-herramienta-para-trabajar-con-datos-masivos/ https://support.office.com/es-es/article/Power-Pivot-an%C3%A1lisis-de-datos-eficaz-y-modelado-de-datos-en-Exceld7b119ed-1b3b-4f23-b634-445ab141b59b?CorrelationId=526aeec7-f2cd-42d2-ade6-82d7acbe2e76&ui=es-ES&rs=esES&ad=ES&fromAR=1 http://blogs.technet.com/b/jorge_aguinaga/archive/2014/05/03/191-qu-233-es-power-query.aspx https://support.office.com/es-ar/article/Power-BI---Gu%C3%ADa-de-introducci%C3%B3n-bd30711a-7ccf-49e8-aafa2e8f481e675d?CorrelationId=781f109a-49f2-4850-897d-cebc1b44033d&ui=es-ES&rs=es-AR&ad=AR#_Power_Query http://www.poweredsolutions.co/es/2015/01/28/que-es-power-bi/ http://www.poweredsolutions.co/es/2015/01/23/que-es-power-query/ https://support.office.com/es-es/article/Obtener-datos-mediante-el-complemento-Power-Pivot-f0431904-aab1-49c3b50c-c6f5d4517a66?ui=es-ES&rs=es-ES&ad=ES https://support.office.com/es-ES/article/Tutorial-Ampliar-relaciones-del-modelo-de-datos-usando-Excel-2013-PowerPivot-y-DAX-cf7197d3-1938-490e-93fb-20371e8dd67a?ui=es-ES&rs=es-ES&ad=ES#__toc358188413 https://support.office.com/es-es/article/Tutorial-r%C3%A1pido-aprenda-los-fundamentos-de-DAX-en-30-minutos51744643-c2a5-436a-bdf6-c895762bec1a?ui=es-ES&rs=es-ES&ad=ES https://msdn.microsoft.com/es-es/library/gg399181(v=sql.110).aspx https://support.office.com/es-es/article/Indicadores-clave-de-rendimiento-KPI-en-Power-Pivot-e653edef-8a21-40e49ece-83a6c8c306aa?ui=es-ES&rs=es-ES&ad=ES http://www.poweredsolutions.co/es/2015/02/02/combinando-multiples-archivos-y-tablas-con-power-query-para-excel/