Categoría: Uncategorized

Self Service BI using Excel 2010 + PowerPivot

Always Excel has been used as a tool by end users for BI analysis in a self-sufficient fashion. The biggest problems with Excel are its limitations in size of the data that can be stored and the complexity of BI analysis that can be performed. For those reasons, when the end user is facing such limitation, he/she needs to get help from the IT department. This ultimately leads to the typical problem in which IT must to assign IT staff to analyze the requirements with the user, and then continue with technical specifications and finally build an application.

The good news is that if you use Excel 2010 you can download the free PowerPivot add-in and the Excel limitations previously mentioned will be solved. In that way you can start to enjoy an excellent tool to do Self-Service Business Intelligence.

What is PowerPivot?

PowerPivot for Excel has the ability to take the Self-Service BI to an unprecedented level. PowerPivot exploits the familiarity of Excel while adding a powerful BI analysis engine, along with new data compression algorithms that let you load large data sets in memory. PowerPivot technically is the implementation of Vertipaq, now with the new name xVelocity, which is defined as In-Memory Analytic Engine.

With PowerPivot users can process huge amounts of data at an incredible speed. Using Data Analysis Expression (DAX new language) in addition to the standard features of Excel, advanced users can easily create more complex applications based on relationships between tables in a database from different origins and other sources such as Excel spreadsheets maintained by you at your computer. In many cases, PowerPivot for Excel can establish relationships between tables automatically, and when it is not possible you can easily set up the relations.

Praxis

To illustrate all this, the following is a case studio tested with a database that includes a table of 10,000,000 transactions (TxMaster), and three related tables: Product Categories (ProdCat) Products (Prods ) and Locations (Stores). All these tables are maintained in a SQL Server database.

Let us start with the end result achieved after establishing a connection to a SQL Sever database to load the data, setup the table’s relationships, and finally assemble the analysis report, graphics and filters (Slicers).

Final Result : Excel Dashboard

If we look at new things in the Excel Ribbon section, we can see the PowerPivot tab and as first option on the left side the PowerPivot Window item. If you click on the latter you are switched to the new section of PowerPivot, which is really where is performed all the handling of data to be used by the Excel spreadsheet via a Pivot Table.

In the next picture we have selected Data View, in particular TXMaster table with 10 million transactions (loaded into the computer memory, in this case a modest Notebook with Intel Core Duo and 4Gb memory). In the tabs above you can navigate through the rest of the related tables.

PowerPivot Window – TxMaster  Table

Note that you can add columns to any table loaded. Just point under Add Column and then enter the formula. The formulas are similar to Excel formulas, however the variables are referencing the fields names in the tables and not cells in particular. Additional you can use the new language DAX for more specific calculation. We can see more details of the DAX in a future post.

The table below shows the Product.

PowerPivot Window – Products Table

If you click on Diagram View you will see the diagram with the relationships between the tables we are using.

Power Pivot Window – Diagram View

It is interesting to note that in this diagram you can do a lot of things, for example, create hierarchies as in the case Category / Product. Also, among other things, you could define KPIs, and most importantly in the Get External Data section, you can add data from virtually anywhere (SQL Server, Oracle, DB2, etc.., Etc..).

If we return to the Dashboard, this was created using Pivot Table, applied to the data defined in the PowerPivot window.

In this dashboard there are four important elements to highlight. The first one is the PowerPivot Field List, i.e., the set of measures and dimensions for the analysis of our cube, which is displayed automatically when you create the PivotTable. We will not go into details of how to assemble the table and graph analysis, but this is achieved by simple drag and drop fields from the tables on the sections of Row Labels, Column Labels and Σ values.

Regarding the Excel file, this can take a lot of space, since all data in your tables will be stored in the file. However, PowerPivot has very good data compression ratio, in this case the final size of the Excel file was approximately 62% of the size occupied by the tables in SQL Server.

An important item is to indicate that everything that was defined in PowerPivot is not lost when you need to update your information, that is, re-loads the data sources to incorporate either new records or data updates in the tables. PowerPivot will apply the already defined data relationships and then re-calculate all the aggregated columns and will update all the information for a fresh analysis.

Well, to finish just add that this is a sample of what you can do today with PowerPivot for Excel, without going into further details. As you can see, PowerPivot is a real opportunity to enter the world of Self-Service Business Intelligence (SSBI).

Self Service BI Usando Excel 2010 + PowerPivot

Por mucho tiempo Excel ha sido una herramienta utilizada por los usuarios finales para hacer análisis BI en forma auto-suficiente. El mayor problema de Excel han sido sus limitaciones en cuanto a tamaño de los datos que puede almacenar y a la complejidad de los análisis BI que se pueden realizar. Así, cuando se topa con esta limitantes el usuario final no le queda otra alternativa que recurrir al departamento TI para que le soluciones el problema. Esto al final conduce al tema de siempre, hay que asignar personal TI para que analice con el usuario sus requerimientos, para después seguir con especificaciones técnicas y finalmente construirle una aplicación.

La buena noticia es que si usted usa Excel 2010 pude bajar sin costo el add-in PowerPivot con el que las típicas limitantes recién mencionadas pasan al olvido, y usted pueden entrar a disfrutar de una excelente herramienta de Self-Service Business Intelligence.

Que es o que realmente hace PowerPivot?

PowerPivot para Excel tiene la capacidad de llevar el Self-Service BI a un nivel sin precedentes. PowerPivot aprovecha lo familiar que es Excel para los usuarios, agregando  un motor de análisis BI en memoria, junto a los nuevos algoritmos de compresión de datos que le permiten cargar grandes conjuntos de datos en memoria. PowerPivot técnicamente es la implementación de Vertipaq de MS, hoy con su nuevo nombre xVelocity, el cual se define como In-Memory Analytic Engine.

Con PowerPivot los usuarios pueden procesar enormes cantidades de datos a una velocidad increíble. Mediante el uso de las expresiones de análisis de datos (DAX nuevo lenguaje), además de las características estándar de Excel, los usuarios avanzados pueden crear fácilmente aplicaciones de mayor complejidad que se basan en relaciones entre tablas de una base de datos proveniente de diferentes orígenes, o bien tablas o planillas Excel mantenidas por usted en su computador.  En muchos casos, PowerPivot para Excel puede establecer las relaciones entre las tablas de forma automática, y cuando no es posible usted las podrá establecer fácilmente sin complejidad de formulas.

Praxis

Para ilustrar mejor lo antes dicho, a continuación les muestro un caso de estudio probado con una base de datos que incluye una tabla de 10.000.000 de transacciones (TxMaster), y tres tablas relacionadas: Categorías de Productos (ProdCat), Productos (Prods), y Tiendas (Stores). Todas estas tablas son mantenidas en una base de datos SQL Server.

Partamos con el resultado final conseguido después de haber conectado a una base de datos SQL Sever para cargar los datos, establecer las relaciones, y finalmente armar el cuadro de análisis, gráfico y filtros (Segmentación de Datos).

Dashboard - resultado final uso PowerPivot

Si miramos que cosas nuevas se ven en la seccion de menus de Excel, podemos ver una pestaña PowerPivot y como primera opcion al lado izquierdo el ítem Ventana de PowerPivot. Si hacemos click sobre esta última accedemos a la nueva sección de PowerPivot, que es realidad donde haremos todo el manejo de los datos que serán utilizados por la planilla Excel vía una Pivot Table (Tabla Dinámica).

En la siguiente imagen tenemos seleccionada  Vista de Datos, y en particular la tabla TXMaster con sus 10 millones de transacciones (cargadas en la memoria del computador, en este caso un modesto Notebook con Intel Core Duo y 4Gb memoria). En las pestañas de abajo puede navegar por las diferentes tablas relacionadas.

Ventana PowerPivot - Tabla TxMaster

Hay que hacer notar, que usted puede agregar columnas a cualquier tabla que haya cargado. Basta ubicarse bajo la columna Agregar Columna y luego ingresar la formula. Estas formulas son parecidas a las formulas de Excel pero no hacen referencia a celdas en particular, sino que a campos de la tabla, otras tablas, o funciones de calculo del nuevo lenguaje DAX. Por ahora, no entraremos en estos detalles, que realmente se merecen un nuevo post.

La siguiente imagen muestra la tabla de Productos.

Ventana PowerPivot - Tabla Productos

Si hacemos click sobre Vista de Diagrama veremos el diagrama con las relaciones entre las tablas que estamos utilizando.

Ventana Power Pivot - Vista de Diagrama

Lo interesante es que dentro de este diagrama usted puede hacer mucho, como por ejemplo, crear jerarquías como en este caso Categoría/Producto. También, entre otras cosas, pude definir sus KPI, y lo mas importante en la sección  Obtener Datos Externos, puede virtualmente agregar datos de donde quiera (SQL Server, Oracle, DB2, etc., etc.).

Si volvemos al Dashboard, este fue creado usando la opcion Tabla Dinamica o Pivot Table, aplicada sobre el diagrama de datos.

En este Dashboard hay cuatro elementos importantes para destacar. El primero es la Lista de Campos PowerPivot, es decir, el conjunto de medidas y dimensiones para el análisis de nuestro cubo, el cual es desplegado automáticamente al crear la Tabla Dinamica. No vamos a entrar en detalles de como armar la tabla de análisis y el gráfico, pero esto se consigue con la simple acción de arrastrar y soltar los campos de las tablas sobre las secciones de Etiquetas de Filas, Etiquetas de Columnas y ∑ Valores.

En cuanto al archivo Excel, este ocupa una gran cantidad de espacio, ya que todos los datos de sus tablas serán almacenados en el archivo. Ahora bien, PowerPivot  tiene muy buenas tasas de compresión de datos, en este caso el tamaño final del archivo Excel fue de aproximadamente un 62% del tamaño que ocupan las tablas en SQL Server.

Lo importante es indicar que todo lo usted ha definido en PowerPivot no se pierde cuando usted necesita Actualizar sus datos, es decir, volver a cargas los datos fuentes para incorporar ya sea nuevos registros o actualizaciones de datos de sus tablas. PowerPivot aplicara las relaciones de datos ya definidas, para luego re-calcular las columnas agregadas y así obtener los datos actualizado para sus cuadros de análisis.

Bueno, para terminar solo agregar que esta es una muestra de lo que puede hacer hoy con PowerPivot para Excel, sin entrar en mayores detalles. Como podrán haber visto, PowerPivot es una real oportunidad para entrar al mundo de lo que se llama Self-Service Business Intelligence (Autoservicio de Inteligencia de Negocios).