28 septiembre 2009

Excel 2010 Technical Preview – Parte 6: Sparklines

Esta es la sexta entrega de la serie de las nuevas características para Business Intelligence en Excel 2010. Lo que nos toca hoy es escribir acerca de los Sparklines, una excelente nueva característica que incluye Excel 2010 para darnos mayores y poderosas capacidades para reflejar información gráfica e histórica que sirva como una referencia rápida a una información principal que se esté revisando.

El concepto ode los Sparklines no es inventado por Microsoft, sino fue propuesto por Edward Tufte para “pequeños gráficos de alta resolución, incrustados en un contexto de palabras, números o imágines.” Además Tufte define a los sparklines como “intensos de datos, diseño simple, gráficos del tamaño del texto”. Más detalles en Wikipedia (en inglés). Los sparklines también existen y han existido como Add-Ins de terceros como el de XLCubed pero en este caso, ya podremos disfrutarlos desde el mismo Excel.

La limitación actual en el Technology Preview de Julio, es que no son parte de los Pivot Tables y por tanto no se sincronizan automáticamente con los datos que estemos mostrando en dicho Pivot Table. No he encontrado alguna fuente que indique para para la versión final de Excel 2010 los Pivot Tables soporten esta integración de manera automática (crucen los dedos). Bueno, pero esto no nos debe detener ya que hay formas creativas de hacer que nuestros sparklines sean de alguna manera “dinámicos”.

Lo primero es mostrarles cómo crear un sparklines, y esto es bastante sencillo, sólo basta tener una serie de datos y mapearla con el sparline. Ojo, que no es un pre-requisito tener los datos en un Pivot Table para poder mapearlo, se puede hacer directamente desde los datos en las celdas de Excel.

image 
El ribbon “Insert” de Excel 2010 nos muestra 3 formas distintas de sparklines:

image 

A continuación agregamos los sparklines a nuestro pivot table de resultados del estado de Pérdidas y Ganancias del 2003:

image 
Adicionalmente tenemos varias opciones de personalizar o mejorar la presentación de nuestros sparlines:

image

image

image

Después de aplicarle alguna de las propiedades a nuestros sparklines, tenemos un efecto visual interesante, mostrando por cada fila de datos, la serie completa de los 12 meses de evolución, en color rojo el punto más bajo alcanzado y en color verde el punto más alto de la serie. Para el caso de contrastar valores positivos y negativos podemos cambiar el tipo de gráfica:

image

Como dijimos al inicio, la idea de uso de los sparklines, es que se muestren como información complementaria pero poderosa de la representación de un valor. De acuerdo a eso sería ideal poder ocultar los 12 meses de valores y dejar sólamente por ejemplo el valor total de todo el año para la cuenta. Actualmente si ocultamos las columnas con las series (meses), los sparklines desaparecen tambíen (tal vez se esto se trata de un bug), así que se requiere hacer algunos trucos para lograr este efecto.

En mi caso mi idea va por copiar el Pivot Table en una segunda hoja de Excel y referenciarla en los sparklines de la primera. Así en la primera logramos el efecto deseado:

image 

Ahora el tema es que si cambiamos de año usando el filtro de nuestro Pivot Table (por ejemplo al año 2002 o 2004, entonces perderemos la sincronización corracta de los datos con la segunda hoja de Excel que está alimentando nuestro datos ya que estos están apuntando al año 2003 y eso no es nada bueno. Por suerte ya que estamos usando la versión 2010 de Excel y si tuvieron la oportunidad de leer la parte 2 de esta serie, sabrán que los Slicers vienen al rescate ya que estos nos permiten conectar a más de un Pivot Table con el mismo filtro. En este caso reemplazamos nuestro parámetro individual del año de cada Pivot Table por un Slicer conectado a ambos Pivot Tables:

image

Ahora logramos el efecto deseado, podemos cambiar de año, y automáticamente los 2 Pivot Tables se actualizan y por tanto los sparklines también. De acuerdo a esto, ya saben que pueden agregar más slicers a su Pivot y mantener la sincronización de sus sparklines automáticamente.

image

Esto es todo por ahora, espero que hayan disfrutado este post.

Alan.

27 septiembre 2009

Excel 2010 Technical Preview - Parte 5: Filtros a los parámetros

Bienvenidos a la quinta entrega de la serie acerca de las nuevas características de Excel 2010 Technical Preview para Business Intelligence. La facilidad de filtrar nuestros parámetros en un Pivot Table o mejor dicho, filtrar los miembros de una dimensión referenciada por el mismo ha sido una de las características “simples” y a la vez “poderosas” deseada por los usuarios que ahora la tendremos disponibles en la nueva versión de Excel.

Lo nuevo es que ahora podemos filtrar los miembros de la dimensión directamente escribiendo en la caja de texto de la dimensión en nuestro Pivot Table:

image

image Cabe resaltar que el filtro es similar a un %LIKE% de SQL, es decir busca la palabra en todo el nombre del miembro y NO es sensitivo a las mayúsculas o minúsculas (key-sensitve).

image

 

 

 

image Si queremos agregar otro miembro al filtro sólo basta con buscarlo nuevamente en la lista y seleccionarlo ya que los miembros seleccionados inicialmente no se pierden:

image

 

 

 

Finalmente, si tenemos más de un atributo en el eje (columna o fila), o una jerarquía, entonces podemos seleccionar el atributo sobre el cual queremos hacer el filtro:

image

Aquí termina este post. Hasta la próxima.

Alan

PR.Net Presentation

Este lunes, la comunidad de .NET de Puerto Rico a través del Doctor Eduardo Sobrino, Microsoft MVP, estará presentanto una muy interesante sesión mostrando un avance de las novedades que incluirá Visual Studio 2010 que se espera sea lanzado durante la primera mitad del 2010. A continuación los detalles de la presentación, no se lo pueden perder!

Announcing PR.Net Community Presentation
Polytechnic University of Puerto Rico
PR.Net Community Presentation
Lambda Expressions and Parallel Computing (in VS-2010)
September 28, 2009
From 4:30 – 6:30 pm
Electrical Engineering Building ROOM L-301

 

Alan

22 septiembre 2009

Excel 2010 Technical Preview – Parte 4: La misma medida más de una vez

Siguiendo con la serie de las nuevas características de BI en Excel 2010 Technical Preview, me toca contarles o recordarles, que en la versión 2007 de Excel, sólo podemos agregar más que una vez la misma medida a un Pivot Table. Claro, la pregunta que se deben estar haciendo es: pero para qué querría yo agregar la misma medida más de una vez? Una de las respuestas podría ser, el querer mostrar en el mismo Pivot Table, el total de las ventas por categoría de producto y el porcentaje de contribución de las mismas al total de las ventas.

Una solución alternativa para lograr tener ambos valores en el mismo Pivot Table, podría ser que en nuestro cubo de Analysis Services tengamos una medida calculada adicional que realice el cálculo del porcentaje de contribución. Pero si queremos agregar otros cálculos en nuestro Pivot Table para distintos análisis, entonces tendríamos un cubo con muchas medidas calculadas lo cual no es muy elegante y puede confundir mucho al usuario a la hora de utilizar nuestro cubo: Excel 2010 al rescate!

Incluir la misma medida más de una vez es bastante sencillo, para este ejemplo empezaremos con creando un nuevo Pivot Table para mostrar la cantidad de órdenes de los resellers según tipo de negocio:

image

El pivot table panel se ve así:
image

Para agregar la misma medida una segunda vez, sólo basta hacer drag-and-drop de la misma medida hacia la sección Values  de nuestro Pivot Table panel:

image

image

Ahora para nuestra segunda medida cambiamos el tipo de cálculo en la opción “Show Value As”… “% of Column Total”:

image

image

Asi mismo podemos agregar una tercera versión de nuestra medida con un ranking usando “Rank Largest to Smallest”:

image

De curiosidad por el MDX que genera este Pivot Table, ejecuté el SQL Profiler y encontré el siguiente query:

SELECT {[Measures].[Reseller Order Count],[Measures].[Reseller Order Count],[Measures].[Reseller Order Count]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[Reseller].[Business Type].[All Resellers]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS  FROM [Adventure Works]

Como ven, la medida en el query de MDX se muestra tres veces, por tanto deducimos que lo mas  interesante de todo esto, es que los cálculos los está procesando Excel y no Analysis Services, lo cual mejora mucho el tiempo de respuesta.

Como vieron, ya no será necesario llenar nuestro cubo de medidas calculadas si tenemos Excel 2010.

Alan

Excel 2010 Technical Preview – Parte 3: SETs

Hola, estoy de vuelta para continuar nuestra serie de las novedades en Excel 2010 Technical Preview para BI. De acuerdo a la primera entrega, ahora nos vamos a encargar de cómo podemos crear SETs o mejor conocidos como Named Sets en el lenguaje MDX que usa Analysis Services. Lo interesante de esta nueva opción en los Pivot Tables de Excel, es que ya no tenemos que limitarnos a utilizar los SETs que hayan sido definidos en el Cubo, sino que podemos crear nuestros propios SETs. En este caso los SETs creados con Excel son a nivel de la sesión del usuario (no pueden ser compartidos con otros usuarios u otra hoja de Excel del mismo usuario).

Excel 2010 permite crear al usuario común sus propios sets básicos. Para ello una de las formas más simples es crear un nuevo Pivot Table y agregar la dimensión de la cual queremos crear un set para reuso posterior. En la imagen a continuación he creado un nuevo Pivot Table a partir del cubo de Adventure Works de Analysis Services 2008 con la medida Internet Sales Amount y el atributo State/Province de la dimensión Customer para crear nuestro primer set:

image 

En esa misma imagen se muestra la opción “Create Set Based on Row Items” dentro de Pivot Table Tools, bajo “Fields, Items & Sets”. En la ventana para la creación del nuevo set, tenemos la oportunidad de escoger gráficamente, a partir de los valores existentes en el Pivot Table, los miembros que queremos incluir en el set. En mi caso escojo los Estados y Provicias que quiero monitorear siempre (Main States/Provinces):

image

Quedándome finalmente con los siguientes miembros:

image

Al pulsar el botón OK, mi Pivot Table se actualiza mostrándome el uso de mi nuevo y flamante set:

image

Adicionalmente podemos crear sets más sofisticados, que en este caso requerirían conocimiento del lenguaje MDX. Por ejemplo podemos crear un nuevo set para seleccionar las Top 6 Subcategorías de productos según las ventas. Para ellos seleccionamos la opción “Manage Sets”:

image

Pulsamos “New” para crear nuestro nuevo set:

image

Y ahora ingresamos el query en MDX que nos permitirá obtener los primeros 6 productos según ventas:

image

Por si no se ve correctamente en la imagen, el MDX ingresado es el siguiente:

TOPCOUNT([Product].[Subcategory].[All].Children, 6,[Measures].[Sales Amount])

image

Ahora procedemos a usar nuestro nuevo set en nuestro Pivot Table:

image  image

Y el resultado se muestra según lo esperado:

image

Veamos si funciona también cuando filtramos el Pivot Table, en este caso para el año 2001:

 image

Mmm, aquí nos encontramos con un problema, ya que las 4 Subcategorias que se muestran en la imagen, no son las correctas para el 2001, adicionalmente desaparecieron 2 por no tener datos en ese año. Si cambio el filtro a cualquier otro año, obtengo la misma lista de Subcategorías en la selección.

En este punto es donde me pongo a pensar en qué tipo de NAME SET está creando Excel en Analysis Services. Para resolver mi duda habilito el SQL Server Profiler para capturar el MDX completo del nuevo set que Excel está creando y me encuentro con lo siguiente:

CREATE SESSION STATIC SET [Adventure Works].[Top 6 Products] AS TOPCOUNT([Product].[Subcategory].[All].Children, 6,[Measures].[Sales Amount])

Como se ve en el MDX, Excel está creando el set de tipo estático, esta es la razón de porqué mi set no cambia como esperaba al cambiar el filtro. Por tanto, lo que necesitamos para solucionar el problema es un DYNAMIC SET, concepto introducido a partir de SQL 2008 y no voy a profundizar ahora para no perder el hilo, pero si les doy una excelente referencia que explica los detalles: Dynamic Sets in SQL Server Analysis Services 2008.

Para cambiar el comportamiento de nuestro set a dinámico, en la ventana de creación de sets, debemos seleccionar el checkbox que dice “Recalculate set with every update” (lo que sería igual a “Crear un Dynamic Set”):

image

Si capturamos el MDX que ahora se envía al SQL Server Profiler, tenemos lo siguiente:

CREATE SESSION DYNAMIC SET [Adventure Works].[Top 6 Products] AS TOPCOUNT([Product].[Subcategory].[All].Children, 6,[Measures].[Sales Amount])

Como ven ya tenemos creado nuestro Dynamic Set. Probemos ahora los resultados en el Pivot Table, primero para todos los periodos (All Periods):

image

Ahora para el año 2001:

image

Excelente! ahora si tenemos los resultados correctos.

Como pudieron ver, Excel 2010 nos permite de una manera relativamente fácil poder tener más control y personalización de nuestros Pivot Tables, esta vez mediante la creación de SETs, que pueden ser estáticos o dinámicos.

Hasta la próxima,

Alan

21 septiembre 2009

Microsoft Community Quest 2009

Aquí una comunicación muy interesante de parte de Karina Leal de Microsoft:

“Ya comenzó el Community Quest 2009 del Microsoft Media Center, Puedes ganar una de las HP-One Netbooks con Windows 7 RTM ANTES que esté disponible en el mercado.

El Community Quest 2009 es un juego de búsqueda del tesoro para la comunidad técnica de Microsoft, a través del portal de Contenidos de Videos, Webcasts y Eventos Presenciales en América Latina. El portal se denomina como Community Mediacenter o CMC, ubicado en http://www.LatamCMC.com, Registrate!”

17 septiembre 2009

Mostrar el MDX de un Pivot Table

Por cortesía de mi compañero, el gran José Rivera (más conocido como Joe en el bajo mundo), comparto con ustedes una forma interesante de mostrar la consulta MDX que genera un Pivot Table desde Excel, lo cual puede usar para validar sus datos o saber cómo ejecuta el query el cliente de Excel.

Para esto necesitan agregar un pequeño script dentro del botón de Visual Basic en el ribbon de Developer en su Excel 2007:

image

Luego de eso incluyen el siguiente script:

Sub CheckMDX()
  Dim strMDX As String
  strMDX = ActiveSheet.PivotTables(1).MDX
  MsgBox strMDX
End Sub

Finalmente, el MDX resultante se mostrará en el Message Box:

image

Alan