¿A qué se dedican miles de financieros y oficinistas durante las largas jornadas en su sitio de trabajo? Efectivamente a consultar la página de algún periódico que sintonice con su ideal político ¿Y el resto del tiempo que dedican a trabajar? A introducir y/o leer datos que afectan a la actividad de su empresa. Como bien preconiza Microsoft en estos casos, la herramienta estrella de esta demanda es esencialmente Excel. Excel con ventas, productividad, gastos; Excel con vídeos, gráficas, Excel como base de datos, como orden de pedido; excel, excel, excel hasta en la sopa. Por ello para facilitar el trabajo y permitir adaptarse a la demanda de uso de grandes cantidades de información y poder explotarla convenientemente aparece PowerPivot.
PowerPivot es la manera de explotar todas las posibilidades que los usuarios han demostrado que tiene Excel, para hacer «inteligencia de negocio» de bolsillo. Podemos cruzar datos de diferentes fuentes, limpiarlos mediante un analisis pormenorizado, filtrarlos y presentarlos en una interfaz visual rica en detalle e impactante que permita sacar conclusiones, estadisticas y previsiones desde nuestro escritorio. Cabe destacar que una de las características que resalta Microsoft es que convierte a Office en una máquina de procesar datos por millones con esta herramienta. Además hay que tener en cuenta que se ha facilitado la integración con SharePoint en una Galería específica para compartir los informes y almacenarlos.
Entrando en materia, PowerPivot técnicamente es un Add-in para Excel que instalamos en nuestra máquina y agrega una nueva ventana con la que poder realizar la operativa. Los requisitos previos que se han de cumplir son el de tener Excel 2010 instalado, .Net Framework 3.5 SP1 como mínimo y cualquier sistema operativo desde Windows XP SP3 en adelante.
Un ejemplo de uso
Voy a realizar un ejercicio práctico para introduciros en la materia que cubre power pivot. Es un ejemplo largo, por tanto lo he dividido en secciones para poder acometerlo poco a poco por etapas; de modo que no haya necesidad de hacerlo todo en el mismo momento. Esto hará amena la lectura del artículo y permite un acercamiento a la materia gradual. Espero que os guste.
Preparando el entorno
En el sitio de microsoft podemos descargar el instalador para tener PowerPivot en nuestro Office. Hacemos doble click sobre el mismo y aparecerá el clásico Wizard que nos guiará en la instalación paso a paso.
Sin pasar por el trauma del reinicio podemos abrir Office y crear un nuevo libro Excel de la manera habitual para probarlo. El plugin nos ofrece una nueva ventana accesible a través del botón de la cinta o Fluent Ribbon.
Dicha ventana es la que nos permitirá la carga intensiva de datos y el modelado de la información que vamos a utilizar. Pero sigamos un ejemplo práctico. Éste está basado en la archiconocida base de datos Adventure Works descargable en el sitio de Code Plex de bases de datos de ejemplo sobre SQL Server 2008 R2. Ni que decir tiene que para nuestro propósito podemos utilizar cualquiera de las ediciones existentes, tanto de pago como gratuitas.
Para la prueba que vamos a realizar he preparado sendos scripts para generar dos vistas sencilla sobre las que trabajar cuyo código puedes copiar de aquí:
USE [AdventureWorks2008R2] GO /****** Object: View [Sales].[vOnlineSalesOrder]******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [Sales].[vOnlineSalesOrder] AS SELECT TOP (100) PERCENT SOH.SalesOrderID, SOH.DueDate, SOH.OrderDate, SOH.Status , SOH.TerritoryID, SOH.SubTotal , SOD.SalesOrderDetailID, SOD.OrderQty, SOD.LineTotal , SOD.ProductID, SOD.UnitPrice, SOD.UnitPriceDiscount , ST.Name AS TerritoryName,PRO.StandardCost AS [Cost] FROM Sales.SalesOrderHeader AS SOH INNER JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN Production.Product AS PRO ON SOD.ProductID = PRO.ProductID INNER JOIN Sales.SalesTerritory AS ST ON SOH.TerritoryID = ST.TerritoryID WHERE (SOH.OnlineOrderFlag = 1) ORDER BY SOH.TerritoryID, SOH.SalesOrderID GO /****** Object: View [Production].[vProductCatalog] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [Production].[vProductCatalog] AS SELECT TOP (100) PERCENT P.ProductID, P.ProductModelID, P.FinishedGoodsFlag, P.ProductNumber, P.StandardCost, P.Name AS Product ,PC.ProductCategoryID, PC.Name AS CategoryName ,PS.ProductSubcategoryID, PS.Name AS SubcategoryName FROM Production.Product AS P LEFT JOIN Production.ProductModel AS PM ON P.ProductModelID = PM.ProductModelID LEFT JOIN Production.ProductSubcategory AS PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID LEFT JOIN Production.ProductCategory AS PC ON PS.ProductCategoryID = PC.ProductCategoryID ORDER BY P.ProductID GO
Otra fuente de datos que vamos a utilizar es un fichero de tipo csv para hacer una demostración de cómo podemos mezclar diferentes fuentes de datos al más puro estilo de la extracción y análisis de datos profesional. Descarga del fichero.
Importando los datos a nuestro Excel
Abramos la ventana de PowerPivot y sobre el Ribbon que contiene pulsemos en fuentes de datos SQL Server. Nuestra intención es realizar un informe de ventas por producto y así poder sacar conclusiones acerca del futuro de nuestro surtido. Debemos indicar de qué servidor queremos extraer la información, qué base de datos, y las tablas que vamos a usar.
Un inciso. Debo decir que por supuesto, el uso de cubos y estructuras multidimensionales, es el modelo ideal del que deberíamos extraer la información, aunque el usuario de PowerPivot pueda realizar con la herramienta la tarea de análisis para adaptar las tablas al diseño conceptual de su negocio.
Configurar la fuente de datos es muy sencillo. En este ejemplo que usamos una base de datos SQL Server debemos indicar el servidor la base de datos y un nombre amigable para denominar la conexión en el libro de Excel
Seleccionamos localhost para conectar con el servidor de nuestro ordenador, le damos el nombre AdventureWorks y finalmente elegimos la base de datos AdventureWorks2008R2, que es donde hemos ejecutado los scripts anteriores.
Podemos probar la conexión antes de seguir para ver si se realiza correctamente. Después pulsando siguiente se nos pide el modo de importación. Elegiremos «a partir de tablas…»
Y en la vista siguiente elegimos marcando los checkboxes de las tablas o las vistas que vayamos a utilizar. En este caso queremos utilizar vProductCatalog y vOnlineSales. De entre las opciones que nos presenta la ventana una de ellas nos permite elegir las columnas de la vista y establecer filtros para hacer la importación. Eligiendo «Vista previa y filtros» con la vista de ventas online, de las columnas que nos presenta vamos a elegir el SalesOrderID, OrderDate, LineTotal, ProductID, TerritoryName y Cost. Una vez seleccionadas podemos poner un nombre a la tabla que nos resulte más amigable como «Ventas online» (veanse imágenes más abajo). Realizamos la misma operación para vProductCatalog. Elegimos las columnas ProductID, CategoryName, SubCategoryName y Product; y establecemos un filtro sobre FinishedGoodsFlag. Para este caso vamos a elegir todos los productos cuyo valor sea TRUE. Una vez terminado le damos el nombre «Catálogo de productos«.
Proceso de filtrado y selección de columnas previo a la importación
La última imagen muestra el proceso de carga de los datos que durará más o menos timepo dependiendo de las filas a leer. Con las tablas elegidas el proceso debería durar pocos segundos ya que la tabla más pesada tiene 60 y pico mil registros nada más.
Cuando finalice veremos que en la parte central se muestran las tablas seleccionadas y en la inferior las pestañas con el nombre de las mismas para alternarlas.
Ahora a modo de ejemplo vamos a importar un fichero csv para ver cómo se pueden usar fuentes de datos diferentes. También es posible usar feeds de datos en atom, bases de datos Access, Oracle, Informes y un largo etc.
Elijo como fuente de datos obtener fuente de datos externa de texto para seleccionar el fichero que nos descargamos en pasos anteriores llamado Time.csv.
La importación se debe ajustar a la fuente de datos seleccionada. En este caso sabemos que es un csv separado por semicolon (;) así que elegimos esa opción del desplegable. También tenemos que navegar a la carpeta donde tengamos el fichero y veremos que una vez elegido se completa el nombre que se va a utilizar para esta conexión de datos. También debemos indicar que la primera fila son los encabezados de las columnas.
Desde esta preview lo que podemos hacer es des chequear en la primera columna que no contiene nada para que todos los encabezados queden como «no seleccionados», e ir marcando las columnas indicadas como se ve en la imagen a continuación.
Pulsamos Finish y veremos una ventana de proceso de carga que tras unos segundos nos mostrará que ha finalizado la importación de 1000 filas de calendario. Tras pulsar aceptar veremos la ventana de PowerPivot con 3 tablas cargadas con los datos que hemos importado.
Definición de los datos importados
Una vez que tenemos la información que necesitamos, puede que necesite algún retoque más. Sobre todo cuando las fuentes de datos son heterogéneas como pasa con las tablas de ventas y la del calendario. Primero estableceremos relaciones entre ellas y luego definiremos qué datos son relevantes para un informe y cuales solo son de uso interno.
Para ello vamos a relacionar las ventas con las fechas del calendario. Colocandonos en la pestaña de Ventas online pulsamos botón derecho sobre el título de la columna y elegimos crear relación. En la ventana emergente debemos seleccionar la tabla con que vincular la relación y el campo que establece dicha relación.
Para completar esta parte debemos ir a la tabla de ventas y establecer una relación esta vez del campo ProductID con el mismo campo de la tabla Catálogo de productos. Volviendo a la tabla de ventas vamos a crear una columna calculada para conocer el beneficio. Para ello vamos a utilizar una expresión DAX. Marcamos la última columna add column y en la barra justo debajo del Ribbon escribimos los siguiente:
=[LineTotal]-[Cost]
y pulsamos enter para que quede introducida. Vemos que se cargan los datos y se genera un nombre automático. Si pulsamos sobre el nombre con el botón derecho podemos renombrar la columna por Profit.
Vamos a realizar lo mismo en la tabla Time. En ella vamos a dar un formato más amigable a la lectura de meses. Creamos una nueva columna que llamamos Month con la formula DAX:
=IF([DayNumberOfMonth] <10,"0","") & [DayNumberOfMonth] & ", " & [MonthOfYearLabel]
Ahora vamos a ocultar columnas que no sean necesarias para nuestro informe de datos que queremos realizar. Para ello el procedimiento se puede hacer de varios modos. Seleccionada la pestaña del conjunto de datos deseada, podemos acudir a la opción del Ribbon Hide and Unhide. Pulsando la el botón debemos elegir qué columna o columnas queremos ocultar y en qué contexto. Las opciones son ocultarlas de la ventana de PowerPivot actual y ocultarlas de la Pivot Table que usaremos para el informe.
Vamos a ocultar de la tabla de Catalogo de productos el ProductID, de la tabla Time Date, DayNumberOfMonth y MonthOfYearLabel; y de Ventas online SalesOrderID, ProductID y TerritoryID . Para rematar la faena vamos a renombrar CalendarQuarterLabel por Quarter y CalendarYearLabel por Year.
Preparación para una Pivot Table
Algo muy importante en que no hemos reparado hasta ahora es que, contra la costumbre acertada de realizar autoguardados cada cierto tiempo, la ventana de PowerPivot y por extensión el add-in y sus ramificaciones, no realiza dichas tareas ¿Qué quiere decir esto? Guardar, guardar, guardar. Si hay algún momento, por descontado, es ahora que ya hemos dejado la tarea de extracción de datos y acondicionamiento del modelo fino y listo. Así que pulsamos el consabido icono de guardado en la ventana de PowerPivot.
Realizando informes con PowerPivot
En el ribbon tenemos la opción PivotTable o si desplegamos el menu que aparece al pulsar en la flecha de debajo todos los tipos de graficos de presentación de datos disponibles para Excel. Pulsamos pivotTable y acto seguido nos preguntará en qué hoja deseamos poner la tabla. Elegimos la que hemos creado al principio. Aparecerá en la hoja de Excel el diseñador para manipular la información cargada.
Empecemos por sumarizar las cifras de ventas y beneficios. Marcamos en la columna de campos a la derecha los checkboxes correspondientes a LineTotal y Profit.
Ahora vamos a agregar una par de slicers que nos permitan ver los datos sumarizados por tiempo o lugar. Para ello arrastramos en el lado derecho el campo TerritoryName hacia el recuadro de Slicer Vertical. Lo mismo hacemos con los campos de la tabla Time arrastrando, Month, Year y Quarter a la zona de slicers horizontal quedando como la imagen siguiente:
En este punto convendría poder guardar los datos por si acaso tenemos algún problema. Vemos que si pulsamos sobre alguno de los territorios o sobre los años o meses, la información de la tabla sobre ventas y beneficios va variando para presentar los diferentes totales en los cortes tempora-espaciales que elijamos.
El siguiente paso a realizar sería incluir una visión de los datos con mas granularidad. Vamos a comprobar cómo las ventas se distribuyen en categorías, subcategorías y producto. Para lo cual sólo hay que arrastrar en ese orden los campos CategoryName, SubcategoryName y Product correspondientes a la tabla de Catálogo de productos a la zona de RowLabel. Nada más hacerlo vemos como la tabla se amplía generando la información en árbol y repartiendo los datos de ventas y beneficios por cada elemento indicado mostrando los totales de cada escalón. Para afinar un poco más en la presentación y realzar mejor los datos vamos a incorporar barras de color a las cifras. Marcamos en las celdas de la columna Profit a la altura de uno de los productos. Por ejemplo MountainBikes. En el Ribbon buscamos la opción Conditional Formatting dependiente de la pestaña Home. Esta opción nos ofrece un desplegable de opciones para dar formato. Elegimos Data Bar y de los colores, el verde. Vemos cómo se han generado barras de color para cada uno de los productos permitiendo ver de un vistazo por la cantidad de relleno de color la relación de cantidades entre unos datos y otros.
Para finalizar vamos a aplicar las mismas reglas en la columna de ventas totales. Esta vez elijamos el color azul.
Esta es una de las muchas maneras de dar formato a los datos y relacionarlos entre sí de manera que revelen cifras significativas para tomar decisiones y comprobar el estado de los negocios. Como características más avanzadas que exploraré en siguientes artículos, está la posibilidad de agregar medidas y realizar muestras porcentuales de la relación coste/beneficio, por ejemplo.
Próximamente
Más adelante escribiré una segunda parte donde podemos ver cómo explotar nuestros informes de PowerPivot en un entorno colaborativo como Sharepoint. Pero eso ya es otra historia.
Así pues, os animo a que instaléis el plugin y lo probéis con los repositorios que estéis usando en vuestra tarea actual, y tratéis de comprobar todo el potencial y el conocimiento que de vuestras fuentes de información os pueda sorprender.
JLA
