Un problema típico cuando trabajamos con SSAS es la necesidad de hacer un drillthrough sobre una medida calculada. SSAS no nos permite realizar drillthrough de forma automática sobre estas medidas y utilizando acciones de este tipo tampoco podemos hacerlo, ya que no podemos determinar el valor para las medidas involucradas en el calculo. Sin embargo, dado un calculo sencillo como puede ser el de la siguiente imagen, ¿por qué no tener la opción de ver el detalle que muestre el calculo y los factores del producto?
En este post intentaremos dar solución a este problema utilizando un informe en SSRS (SQL Server Reporting Services) para mostrar el detalle que queremos, aunque existen otras alternativas como la que podéis encontrar en el primer enlace relacionado. En primer lugar debemos definir un informe que reciba como parámetros los elementos del contexto que deseamos explotar en el drillthrough, en mi caso el contexto que quiero analizar es el producto, el tiempo y una medida, ya que el análisis que estoy haciendo en Excel es el siguiente:
De esta manera, creamos un informe con los siguientes parámetros de tipo texto:
En paralelo, en nuestro cubo debemos definir una acción de tipo Report como la mostrada en la siguiente imagen, podéis encontrar material muy útil en los enlaces relacionados sobre este tipo de acciones [2,3].
Si introducimos tres objetos text box en el informe para ver el valor de los parámetros recibidos podemos acceder al informe desde Excel utilizando la acción:
Ahora solo nos queda jugar con nuestro informe para mostrar la información que queremos. Para empezar lo que tenemos que hacer es obtener el listado de métricas que queremos mostrar en función de la medida pasada como parámetro, esto lo podemos hacer de muchas maneras pero en mi caso voy a utilizar una DMV (Dynamic Management Views) de SSAS para obtener la definición de la métrica, y en el caso de que esta sea calculada aplicare una pequeña función de limpieza para que el MDX quede como un listado de métricas (debemos ser conscientes que está limpieza no servirá para todos las medidas calculadas). Para leer la DMV desde nuestro informe, creamos un origen de datos contra nuestro servidor SSAS y un dataset utilizando la siguiente consulta:
select measure_unique_name,expression from $SYSTEM.MDSCHEMA_MEASURES
Para aplicar la limpieza podemos crear una función propia dentro de SSRS en Report > Report Properties > Code (adjunto el informe donde se puede ver el código utilizado para la limpieza). Yo he creado una función de limpieza muy sencilla que consiste en convertir la formula MDX en un listado de [Measures].[……],[…..].[……],[Measures].[……], pero la podríamos complicar todo lo que quisiéramos para tener en cuenta otra casuísticas.
En el dataset que lanza la DMV podemos incluir una columna calculada que lance está función sobre la expresión MDX y así obtener el listado de métricas. Así quedaría el informe añadiendo el listado de métricas en nuevo text box:
Por otro lado, creamos una consulta MDX que nos muestre la información al máximo nivel de granularidad con la información que nosotros queremos ver en el detalle. En mi caso la consulta que utilizare es la siguiente:
select non empty {[Measures].[Numero de ventas],[Measures].[Unidades vendidas],[Measures].[Total ventas],[Measures].[Precio Unidad]} on columns,
non empty {[DimProducto].[Por categoria en castellano].[Nombre ES].allmembers*[DimTiempo].[Por tiempo].[Date Key].allmembers} on rows
from (
select {[DimTiempo].[Por tiempo].[Date Key].&[20110228]} on 0 from (
select {[DimProducto].[Por categoria en castellano].[Nombre ES].&[Teléfono marca X]} on 0 from [Demo PFM-DW]))
[box type=”info”] Nota: Esto será equivalente a un drillthrough cuando un hecho sea identificado por la combinación de sus dimensiones, en otro caso deberemos utilizar la sentencia drillthrough de SSAS en lugar de una consulta MDX.[/box]
Los resultados de esta consulta los volcamos a un región de datos de tipo tablix y en cada columna de las medidas especificamos el color del text box con la siguiente formula en función de la medida:
=iif(Parameters!ListaMetricas.Value.ToString().Contains("Numero de ventas"),"Tomato","Teal")
- El parámetro ListaMetricas se rellena desde el dataset definido con DSV de manera que en el tenemos la cadena con las medidas directamente relacionadas con la medida sobre la que se hizo drillthrough. Por ejemplo, si la medida sobre la que se lanzo la acción es Total Ventas el valor de este parámetro será {[Measures].[Total ventas],[Measures].[Unidades Vendidas],[Measures].[Precio Unidad]}.
- El parámetro de la función Contains (en este caso “Numero de ventas”) es el nombre de la columna.
El informe quedaría finalmente con el siguiente aspecto, mostrando siempre todas las medidas de nuestro detalle y coloreando en rojo aquellas medidas relacionadas con la medida sobre la que lanzamos la acción:
- Ejemplo medida no calculada:
- Ejemplo medida calculada:
Enlaces relacionados interesantes:
http://blogs.netconnex.com/2007/08/ssas-report-server-action.html
http://bennyaustin.wordpress.com/2009/07/19/reporting-action/
Un saludo
Ilde
2 comments