¿Qué posibilidades que nos brinda Power Query a la hora de Extraer, Transformar, y Cargar (ETL Extract, Transform, Load) datos para nuestro modelo de BI Personal? Abordaremos el análisis de la información utilizando Power Pivot desde Excel 2013.

Mapa Mental

Introducción a Power Pivot

Power Pivot es una tecnología que permite procesar y analizar información compleja de forma muy eficiente y rápida. Su principal característica y la que le aporta una enorme velocidad es que es una tecnología “In-Memory”, es decir que tiene todos los datos en memoria con una alta compresión, para disminuir el espacio que ocupa y aumentar la velocidad de análisis. Estos modelos de datos están basados en tablas y relaciones.

Tiene tres “sabores” o presentaciones:

• Power Pivot para Excel: es un complemento (add-in) para Excel que contiene toda esta tecnología y permite gestionar estos modelos desde unos nuevos menús integrados en el propio Excel. Su uso está orientado al BI Personal.

• Power Pivot para SharePoint: Permite gestionar hojas de trabajo Power Pivot en un servidor SharePoint. Tanto el procesamiento de datos como la visualización es gestionada en dicho servidor. Su uso está orientado al BI departamental.

• Modelo Tabular de SQL Server Analysis Services (SSAS): son bases de datos “In-Memory” gestionadas por Analysis Services a través de su motor analítico, un servicio orientado a servidores y a su administración por parte de personal de TI cualificado. Su objetivo es disponer de la información corporativa centralizada y servirla a todos los usuarios de la empresa de forma simultánea con unos tiempos de respuesta muy rápidos. Soporta mayores volúmenes de almacenamiento, la gestión está optimizada para uso simultáneo por parte de muchos usuarios. Por último, destacar que permite la automatización y agendado de la carga de información desde los orígenes de datos al modelo. Su uso está orientado al BI Corporativo.

A nivel un poquito más técnico, por si el lector desea investigar sobre este modelo de almacenamiento, Microsoft lo ha llamado “xVelocity” (anteriormente lo llamó “Vertipaq”); puede buscar en internet por estos dos términos y encontrar gran cantidad de información técnica.

En este momento nos vamos a centrar en el complemento (add-in)para Excel, que es el que permite a los usuarios de negocio crear sus propios modelos analíticos y poblarlos con información procedente de muy diversos orígenes de datos, con total independencia del departamento de TI. Además un modelo creado en Excel, se puede compartir en SharePoint o importar desde SSAS (con ayuda de personal cualificado del departamento de TI), reutilizando todo lo que el usuario de negocio ha desarrollado en Excel.

Power Pivot para Excel es un complemento (add-in) para Excel que nos permite integrar y estructurar en tablas la información procedente de diversos orígenes de datos, dicha información se almacena en los archivos “.xlsx” con un alto grado de compresión, y en el momento que se abren los lee y carga por completo en memoria, lo que permite una rapidez de cálculo no vista hasta ahora en Excel, así como la posibilidad de almacenar grandes cantidades de filas, muchos millones de filas, superando así las limitaciones (1 millón de filas aproximadamente) y las lentas velocidades que tenemos cuando hay un gran número de filas en las hojas Excel tradicionales.

Para que se haga una idea de la alta compresión que se puede llegar a alcanzar, vamos a poner un ejemplo con datos reales, realizado por SolidQ. Tenemos un modelo con cuatro tablas:

Ocupando un tamaño de tan sólo 26,3 MB. Tenga en cuenta que la compresión puede ser menor, ya que depende de la redundancia de valores de cada columna y en este caso esa redundancia es bastante alta.

Adicionalmente incluye un potente lenguaje de fórmulas, llamado DAX (Data Analysis Expressions) que incrementa enormemente la capacidad de cálculo analítico y la velocidad de respuesta.

Tanto si usted es un usuario de nivel intermedio de Excel y obtiene los datos de su empresa mediante exportación de informes y consultas desde sus aplicaciones y/o mediante operaciones de copiar y pegar, como si es un usuario avanzado que descubre y lucha día a día con el lenguaje VBA para tratar de obtener soluciones analíticas con cierto dinamismo y sin tener que hacer tareas manuales tediosas y repetitivas para obtener datos actualizados de su empresa y transformarlos para su análisis, Power Pivot es la herramienta que necesita para hacer su vida más fácil, dedicando menos tiempo a tareas tediosas y más tiempo a analizar su información y tomar decisiones más acertadas.

En resumen, mayor almacenamiento, facilidad para obtener datos externos, gran aumento de la velocidad de cálculo y nuevo lenguaje de fórmulas con mucha más potencia de cálculo.

Power Pivot es la nueva herramienta de almacenamiento y análisis de la información orientada al usuario final y que está totalmente integrada con Excel. Como puede comprobar en la siguiente imagen su interfaz es un nuevo menú en la “cinta” (Ribbon) de Excel.

El complemento Power Pivot no viene habilitado por defecto en Excel 2013. Para habilitarlo debe seguir una serie de pasos que se resumen en la siguiente imagen:

Uno de los principales elementos es la opción “Administrar” (Manage) que da acceso a una nueva ventana con todos los elementos necesarios para su gestión. En la siguiente imagen puede ver dicha ventana.

Fíjese en la parte inferior izquierda, donde indica que hay en esa tabla 6.341.790 registros, algo imposible en una hoja Excel tradicional.

Toda la información, tanto de Power Pivot como de Excel tradicional se almacena en el mismo fichero “.xlsx”. Tenga en cuenta que la función de autoguardado de Excel no aplica a la ventana de Power Pivot, por lo que deberá guardar los cambios de forma manual para evitar perderlos ante un cierre inesperado de la aplicación.

Por último vamos a ubicar Power Pivot junto con el resto de componentes de Power BI para Excel en la siguiente imagen, para seguir trabajando en la comprensión desde una perspectiva global. Observe que hemos marcado con un círculo rojo su ubicación:

Power Pivot es el núcleo de los componentes de Power BI para Excel, sin él no tiene sentido la existencia del resto de componentes, ya que todas las demás herramientas la necesitan como punto de partida o de destino:

1. Power Query: lee datos de los orígenes, los transforma y los almacena en Power Pivot.
2. Power View: es una herramienta para visualizar información almacenada en Power Pivot y en servidores con SQL Server Analysis Services.

Power Map: es una herramienta para visualizar y analizar información geolocalizada en diversos tipos de mapas, a partir de información almacenada en Power Pivot y en servidores con SQL Server Analysis Services.

¿Quieres convertirte en un experto con Power BI? Infórmate de todos nuestros cursos para todos los niveles y suscríbete al blog para estar al corriente de todos los contenidos y novedades de SolidQ.