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:
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):
Quedándome finalmente con los siguientes miembros:
Al pulsar el botón OK, mi Pivot Table se actualiza mostrándome el uso de mi nuevo y flamante set:
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”:
Pulsamos “New” para crear nuestro nuevo set:
Y ahora ingresamos el query en MDX que nos permitirá obtener los primeros 6 productos según ventas:
Por si no se ve correctamente en la imagen, el MDX ingresado es el siguiente:
TOPCOUNT([Product].[Subcategory].[All].Children, 6,[Measures].[Sales Amount])
Ahora procedemos a usar nuestro nuevo set en nuestro Pivot Table:
Y el resultado se muestra según lo esperado:
Veamos si funciona también cuando filtramos el Pivot Table, en este caso para el año 2001:
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”):
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):
Ahora para el año 2001:
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
No hay comentarios:
Publicar un comentario