Reporting en Microstrategy 9 (II).

Inserción de calculos. Porcentajes sobre Total y Transformaciones.

Microstrategy nos permite incluir en el informe cálculos que se realizan sobre los valores de las columnas devueltas al ejecutar el análisis. Es decir, se realizan después de la consulta sobre la base de datos, en el momento en el que el motor análitico va a visualizar los resultados. Esto nos puede ser util para incluir nuestros propios calculos que no tienen porque tener un indicador asociado y definido en el metadata.

Veamos un ejemplo:

Partiendo del informe de ventas por Año, Trimestre y Target (segmento destino del producto), en el cual se visualizan el importe neto de ventas, las unidades, el margen y el numero de pedidos contabilizados en el sistema, queremos obtener el numero de unidades medidas por pedido y el importe medio de cada pedido. El informe original es el siguiente:

Ventas por Año, Trimestre y Target de Producto

Para añadir una columna calculada, seleccionaremos la opción Insertar –> Calculo. Nos aparecera el editor de indicadores donde podremos definir la formula de calculo de la nueva columna. En el editor nos aparecen unica y exclusivamente los indicadores del informe y los atributos (también los podremos utilizar en el calculo, por ejemplo, para obtener un recuento de sus valores).

Editor columnas calculadas

En nuestro ejemplo, añadimos tres nuevas columnas. Una de ellas, la Media de Unidades por Pedido fruto de la división del total de unidades y el numero de pedidos. La otra, la Media de Ingresos por Pedido, que se calcula dividiendo el Importe Neto por el número de pedidos. Finalmente, añadimos el calculo del Margen Porcentual, con el objetivo de ver como se comportan los totalizadores de las columnas calculadas (el indicador Margen% ya lo tenemos definido en nuestro catalogo de indicadores). El informe con los nuevos calculos sería el siguiente:

Ventas por Año, Trimestre y Target con columnas calculadas

Podemos ver como aparecen las nuevas columnas en el informe. A diferencia de los indicadores, los campos de totalización para estas columnas son calculados también en la sección de totales y subtotales. Por ejemplo, para la columna %MargenB, se va calculado su resultado en cada fila. A nivel de subtotal, no se suman las diferentes cantidades de cada linea, sino que se recalcula con los importes de los subtotales y totales de los campos implicados en el cálculo. Esto nos puede ser útil en determinados casos.

Ademas de los campos calculados, Microstrategy nos permite insertar otros campos con utilidad miscelanea, que pueden ser:

• Porcentaje sobre total: para un indicador determinado, podemos añadir un nuevo indicador calculado donde aparezca el peso de su valor con respecto al total general o con respecto a los totales en un determinado nivel de desglose del informe. En nuestro ejemplo, podriamos haber incluido el Porcentaje sobre Total a nivel de Trimestre (en ese caso, para cada Target, nos dara el peso del Importe Neto sobre el total de cada Trimestre).
• Transformaciones: podemos seleccionar cualquier indicador de los existentes en el informe y aplicarles una de las transformaciones que tengamos definidas, creando un nuevo indicador calculado. Por ejemplo, a la columna Importe Neto, le podriamos haber aplicado la transformación Año Anterior, y estamos creando un nuevo indicador que incluye los importes netos del año anterior al de analisis actual. Con esto, podemos realizar, como ya vimos, análisis de series temporales.
• Orden: nos permite definir una columna donde aparece el número de orden de un valor respecto al total o respecto a los diferentes niveles de desglose del informe. El orden se puede referir a nivel ascendente (de menor o mayor) o descendente (de mayor a menor). En nuestro ejemplo, podriamos haber añadido una columna de Orden a nivel de Trimestre, para que nos indique el ranking de mayor a menor importe de ventas o de rentabilidad.

Como ejemplo, vamos a añadir algún campo calculado de este tipo a nuestro informe de ejemplo:

Informe con Indicadores % sobre Total y Orden

En nuestro informe ejemplo hemos añadido tres columnas mas:

• Ranking Trim: el valor que aparece en esta columna nos indica el lugar que ocupa el importe de ventas de cada Target de Producto a nivel de cada Trimestre, con ordenación descendente.
• %s/ Total: esta columna nos indica el porcentaje que representa el Importe Neto sobre el total general del informe.
• %s/ Total Trim: esta columna nos indica el porcentaje que representa el Importe Neto sobre el total general de cada Trimestre.

Vemos que estos campos calculados nos pueden ser utiles para añadir información estadística y ordenaciones que hagan mas claros y faciles de analizar los resultados.

Formatos de exportación de Informes: PDF, Excel, HTML, Word, Access, Texto. Envio por Email.

Una vez tenemos calculado un informe, podremos exportar sus resultados en diferentes formatos. Esto nos va a permitir, por ejemplo, pasar la información a terceras personas y en el formato preciso según el tipo de acción que se tenga que realizar sobre los datos. Para realizar la exportación, desde el menú Datos –> Exportar A, y a continuación seleccionaremos el tipo de formato de exportación.

Por ejemplo, podremos crear un documento estático del tipo PDF o HTML que contenga los resultados. Tambien podremos sacar los datos a un fichero de texto o a documentos modificables en formato Excel, Word, Access. Se pueden configurar parametros de la exportación (como si se abrirá la aplicación correspondiente despues de la exportación, si queremos guardarlo en un determinado archivo y carpeta, si queremos ejecutar macros en el documento antes o despues de la exportación, etc).

Exportacion a Excel de los resultados de un informe

Microstrategy tiene una gran integración con la suite Office y las exportaciones estan bastante conseguidas. Por ejemplo, al exportar el mismo resultado de informe del ejemplo a Word, me aparece un texto con las características de ejecución del informe y a continuación una tabla de Word con los resultados del informe (el crosstab del informe replicado en la tabla). Observad en el ejemplo como se han pasado todos los atributos del informe a Excel, incluyendo los formatos y umbrales.

Igualmente, Microstrategy nos permite enviar los resultados de un informe por Correo Electrónico, utilizando uno de los formatos indicados anteriormente. Se genera un correo electrónico que llevará anexo el correspondiente documento en el formato seleccionado (se requiere tener instalado el programa Outlook para que funcione correctamente).

Ejecución de Informes desde aplicaciones Office.

Microstrategy 9 incluye el componente Office que instala un complemento dentro la suite Microsoft Office. Este complemento nos permite interactuar directamente desde Word, Excel, Powerpoint, Access o incluso Outlook.El complemento se conecta a nuestro proyecto y nos muestra los diferentes informes a los que tenemos acceso.

Ejecucion de Informes desde Excel

Una vez seleccionado el informe que queremos ejecutar, apareceran los correspondientes filtros o selecciones dinámicas que lleve asociado el informe y se nos mostraran los resultados, una vez ejecutado el informe, dentro de la aplicación Office desde la que estemos trabajando. Desde Excel, podremos repetir la ejecución del informe con los mismos criterios de selección o repetir la selección para obtener otros rangos de información, todo de una forma integrada y automática.

En nuestra prueba hemos ejecutado un informe de Ventas por Año, Trimestre y Target desde Excel, y todos los formatos, colores, Umbrales, etc. se han mantenido y se han pasado exactamente igual en la hoja de calculo que se nos ha creado automáticamente.

Navegación en los informes. Opciones de navegación. Configuracion.

Aunque la navegación es una característica de los cubos OLAP, vamos a verla por encima pues es una característica muy util en el reporting, que nos va a permitir ir profundizando en el análisis de los datos que se obtienen en la ejecución de un informe o cambiar el ambito de análisis en tiempo de ejecución (cambiando la dimensión por la que estemos analizando la información).

La navegación se puede configurar a nivel de informe, de forma que podemos tener informes que sean estáticos y donde no este permitida la navegación (nos puede interesar por el perfil de usuario que lo utilice) o informes en donde se puede navegar, pero solo a nivel descendente. En la imagen, vemos algunas de las opciones de navegación.

Opciones de navegación en un informe

También se pueden configurar otros aspectos, como el mantener en la cuadricula el atributo padre desde el que estamos navegando, si mantenemos los umbrales y subtotales, etc. La navegación se puede realizar sobre todos los elementos de un atributo o sobre valores seleccionados. Para entender esto, vamos a ver un ejemplo. Tenemos un informe sencillo, donde se analizan las ventas por Año y Trimestre (tal y como vemos en la imagen).

Analisis Ventas por Año y Trimestre

Podriamos profundizar mas en los datos de dos maneras:

• Total: seleccionamos en la cabecera del atributo que queremos desglosar (por ejemplo, el Trimestre Año), y con el botón derecho, desde el menú contextual seleccionamos Navegación –> Abajo –> Mes Año. El informe se recalculara y se mostrara toda la información desglosada al nuevo nivel.
• Según valores seleccionados: seleccionamos uno o varios trimestres (pulsando Control), y de la misma manera con el menú contextual, procederemos a la navegación. En este caso, solo estaremos desglosando al nivel inferior los trimestres seleccionados.

Analisis año-trimestre con navegacion

En la imagen podeis ver como hemos desglosado por mes el primer y segundo trimestre de 2007. Igualmente, podeis ver el aspecto del menú contextual que nos permite realizar la navegación. La opción Arriba y Abajo nos permite subir y bajar en la jerarquía actual. Con la opción Otras direcciones, podriamos cambiarnos de jerarquía y pasar a analizar los datos y a desglosarlos por otro atributo.

Movilidad en los componentes de un informe. Filas, columnas, indicadores.

La herramienta de reporting nos permite en tiempo de ejecución modificar el aspecto del informe, pudiendo intercambiar filas y columnas, cambiar el orden de los elementos en la visualización, ocultar un indicador (aunque sigue estando asociado al informe). Tambien podremos llevar una fila a columnas o viceversa o seleccionar un determinado atributo y llevarlo a la ventana de paginación (momento en el que pasara a formar parte del filtro de paginación). Veamos un ejemplo sencillo de un informe de analisis de ventas por Linea de Producto y Mes (en la filas estan las lineas de producto y en las columnas los meses):

Ventas por Linea de Producto y Mes

En este informe queremos transponer las filas con las columnas. Sera tan facil como seleccionar la opción Mover –> Intercambiar filas y columnas, y el aspecto del informe pasará a ser el siguiente:

Ventas por Mes y Linea de Producto

En el caso de haber tenido mas indicadores, podriamos haber cambiado su posición simplemente seleccionandolos y arrastrandolos a la nueva ubicación. De la misma manera, en el caso de tener varios atributos en el mismo eje (por ejemplo Trimestre, Linea Producto), podriamos haberlos arrastrado cambiando su orden, orden que determinara la forma en que se hace el desglose de la información.

Limites a nivel de informe. Filtros de visualización.

Ademas de los filtros que vimos anteriormente que se pueden establecer a nivel del informe mediante los filtros y selecciones dinámicas, y también mediante el filtrado de valores de un indicador (que también vimos), Microstrategy nos permite dos filtrados adicionales de la información:

• Limites a nivel de informe: en las opciones de datos del informe, podemos indicar criterios adicionales de filtrado que produciran como resultado que no se visualicen los valores que no cumplan las condiciones. Por ejemplo, si en un informe de ventas indicamos la condición de la imagen (Importe Neto Mayor o Igual que 100), no se mostrara, cuando ejecutemos el informe, ninguna linea que no cumpla dicha condición. Pero una cosa importante, los valores filtrados si apareceran reflejados en totales y subtotales del informe. Es una forma útil de quitar información no relevante, aunque si siga considerandose para los analisis de importes totales.

• Filtro de visualización: este filtro se define en la sección del informe correspondiente y nos permite ir estableciendo filtros de visualización en caliente sobre los datos mostrados en pantalla. Conforme se van cambiando los filtros y aplicandose, dinamicamente cambian los valores visualizados, y también los importes de totales y subtotales. Puede ser util para trabajar dinamicamente sobre los datos y para ir mostrando diferentes escenarios de datos según se omite o no determinada información.

Filtro visualizacion

Cache de Informes.

Es una característica avanzada de Microstrategy, que permite que los informes que se ejecutan con mas frecuencia y en los que no cambian los criterios de ejecución, sean guardados en cache y recuperados en el momento en el que alguien vuelva a realizar la misma consulta. En este tipo de ejecución, cuando lanzamos un informe no se accede a la base de datos, sino que leemos los resultados del informe que quedaron guardados en un area de trabajo especial. Con esto, optimizamos los recursos del sistema y mejoramos los tiempos de respuesta y el rendimiento general del sistema. Puede tener algún inconveniente, como que los datos que se nos muestren no esten actualizados con los datos mas recientes, aunque todo esto es configurable.

Para saber si un informe se ha ejecutado recuperando o no la cache, en la sección Detalles de Informe aparecera si se ha recurrido a ella o no. En cualquier momento se puede limpiar la cache de un informe antes de volverlo a ejecutar, y así asegurarnos que se realizará el recálculo completo.

Las opciones de cache se establecen a nivel de proyecto (a nivel general), tal y como vemos en la imagen.

Configuración de cache a nivel de proyecto

A nivel de informe, se pueden establecer opciones de cache personalizadas, que solo afectarán al informe en cuestion. Incluso se puede inhabilitar la cache para un informe en concreto y darle una caducidad.

Cache a nivel de informe

Las caches se pueden limpiar de forma global en cualquier momento para que todo se recalcule.

Creación de informes al vuelo.

Ademas de construir informes desde cero utilizando el editor de informes o el correspondiente asistente, tenemos otra forma de construir informes al vuelo. Partiendo de la ejecución de cualquier informe, nos podemos pasar al modo Diseño, y realizar los cambios que estimemos oportunos (añadir indicadores, modificar atributos, quitar o poner filas y columnas, cambios en  los formatos, umbrales, filtros, etc).

Una vez realizados todos los cambios, podremos guardar el informe con un nombre diferente y habremos creado de forma interactivo un nuevo informe. Esta característica nos permite reutilizar componentes ya definidos y personalizarlos según las diferentes necesidades que vayamos teniendo. Por eso, puede ser util cuando queramos construir un nuevo informe, buscar uno ya existente que tenga elementos en comun y sobre el realizar las modificaciones para dar lugar al nuevo informe.

Creación de grupos personalizados.

Los grupos personalizados son agrupaciones heterogeneas de atributos que nos permiten ver los resultados de los informes desde otro punto de vista. Es una característica avanzada de Microstrategy que yo no he visto en otras herramientas y que puede ser muy util para analizar la información por criterios que no estan registrados en el modelo de datos.

Antes de continuar, os recomiendo visualizar el video elaborado por Microstrategy que habla de la forma de crear los grupos personalizados (ver aquí ).

Para nuestro ejemplo, hemos creado un Grupo Personalizado que incluye varios grupos de atributos. El objetivo es realizar una clasificación de las regiones por Zonas. Por ejemplo, la zona Levante incluye la región de Valencia, Murcia y Cataluña. La region Sur incluye Andalucia, Extremadura, Ceuta y Melilla. Y así con el resto de las regiones. La definición del grupo personalizado sería la siguiente:

Creación de grupo personalizado Zonas Geográficas

Una vez definido un grupo personalizado, lo podemos utilizar en la creación de un informe como si fuera un atributo mas de una dimensión. En nuestro ejemplo, hemos definido un informe utilizando el Grupo personalizado en las filas y en las columnas los indicadores Importe Neto, Precio Neto y Unidades. El resultado de la ejecución del informe sería el siguiente:

Informe con grupo personalizado

Podemos ver como se ha realizado la clasificación de la información y como aparece desglosado todo según la jerarquía definida al crear el grupo personalizado. Cuando se define el grupo personalizado, se puede predefinir como queremos que se vea la representación jerarquica, los subtotales, si el elemento padre aparecera en la parte superior o inferior de los hijos, etc.

Ademas del grupo personalizado indicando valores de atributos, podemos crear grupos personalizados que califican sobre valores (por ejemplo los 10 primeros clientes por importe, los 20 primeros clientes por rentabilidad, los clientes que representan el 40% de las unidades vendidas). Para este caso, la definición del grupo personalizado sería la siguiente:

Grupo Personalizado calificando Indicadores

Observad como se pueden añadir tantos subgrupos dentro de un grupo personalizado como se deseen. En este caso, hemos añadido 3. Cada uno califica sobre un indicador diferente y con un criterio de agrupación diferente (Clasificación, Porcentaje). El grupo personalizado lo hemos incluido en un informe, y el resultado de la ejecución sería el siguiente:

Informe con grupo Personalizado sobre indicadores

Hemos podido ver con estos dos ejemplos las amplias posibilidades que nos proporciona esta funcionalidad de Microstrategy. Podremos crear nuestro propios grupos e incluirlos en el mismo informe, incluso aunque no esten relacionados de una forma homogenea.

Graficos. Combinación tabla cruzada-gráfico.

Ademas de los informes en forma de crosstab o tabla cruzada que hemos visto hasta ahora, Microstrategy también nos permite incluir gráficos para la visualización de los resultados. Disponemos de un amplio abanico de posibilidades en lo que respecta a los gráficos: desde gráficos de área, de barras, de lineas, circular o de tarta, dispersión, polar, radial, burbuja, marcadores, diagramas de gant, gráficos combinados o gráficos personalizados.

Tipos de grafico en Microstrategy

A la hora de utilizar los gráficos, tendremos que tener los datos apropiados en la tabla cruzada para que su visualización en un gráfico tenga sentido (si tenemos 5 indicadores y la información desglosada por muchos atributos, no valdrá de nada construir un gráfico).

Ejemplo grafico de barras

Ademas, Microstrategy nos permite combinar en un mismo informe la tabla cruzada y su correspondiente informe. Por ejemplo, para el ejemplo que hemos visto, podriamos tener las ventas por mes y región-provincia. Conforme vayamos cambiando en la paginación la región, los valores en la tabla cruzada se irían actualizando, asi como los valores reflejados en el gráfico.

Ejemplo Tabla Cruzada con gráfico

Con los gráficos podremos complementar nuestro informes y darles un significado gráficos. También seran utiles para esquematizar información en los cuadros de mando. Cada tipo de informe tiene unos requisitos de atributos e indicadores (en la documentación hay una tabla donde se enumera cada gráfico y los requisitos mínimos para darle sentido).

Informes con selección de atributos y ratios en tiempo de ejecución.

Para terminar con el detalle de los elementos que podemos utilizar en nuestro sistema de reporting, vamos a ver la combinación de selecciones dinámicos que nos permiten seleccionar los atributos o indicadores de un informe, incluidos dentro de un crosstab. De esta forma, al ejecutar el informe, se ejecutaran también las selecciones, y estaremos construyendo, en tiempo de ejecución, lo que queremos que incluya nuestro informe.

En primer lugar, realizaremos la definición de las selecciones dinámicas. Definiremos esta como del tipo “Selección Dinámica de Objeto“, y crearemos una para los atributos, y otra para los indicadores (tal y como vemos en la imagen).

Seleccion dinamica de indicadores

A continuación, construiremos un informe. La seleccion dinámica de atributos la pondremos en las filas en nuestro ejemplo (como si fuera un atributo), y la selección dinámica de indicadores en las columnas(como si fuera un indicador). Al ejecutar el informe, nos aparecerá algo asi:

Selecciones de atributos e indicadores al ejecutar

Vemos como el informe nos muestra la lista de atributos e indicadores que hemos preconfigurado, y podremos elegir entre ellos para llevarlos al informe al lugar correspondiente donde se incluyeron en el diseño. Con la selecciónes realizadas, mas el filtrado propio de cada informe, estaremos dejando al criterio del usuario que información quiere ver en cada momento. Esto nos puede ser util para reducir el número de informes disponible o para que el mismo informe se utilice para funciones diferentes según nuestras necesidades.

Otros elementos

• Generación informes a partir de sentencias SQL personalizadas: podremos desde una sentecia SQL construida por nosotros, crear un informe. El el momento de crear un informe, seleccionamos el origen ODBC con el que vamos a trabajar. Utilizando el generador de consultas, podremos ir llevando las tablas que haya definidas en la base de datos seleccionada, al espacio de trabajo. De ahí, podremos ir seleccionando los campos, construyendo expresiones sobre ellos e indicando condiciones (como vemos en la imagen siguiente). Al grabar, volvemos al editor de informes con la información necesaria recuperada de la query. Con este procedimiento podremos construir informes sobre cualquier origen de datos y preparar consultas puntuales y no predefinidas sobre la información (podeis ver un video tutorial del proceso completo de creación de este tipo de informes aquí ).

Generador consultas SQL para construir informes

• Mapas de navegación: nos permiten establecer los patrones de como será la navegación en los informes. Es decir, podremos indicadar que atributos estarán disponibles para la navegación hacia arriba, hacia abajo o hacia otra dimensión. Sino se crea ningún mapa de navegación, se utiliza el de proyecto que incluye todas las jerarquias de usuario. Si deseamos personalizarlo, podemos hacerlo a nivel de proyecto (general para todos elementos), o bien a nivel de atributos, jerarquias o consolidaciones. Cuando el objeto se utilice en un informe, se reemplazara el mapa de navegación general por el propio. También se pueden asociar mapas de navegación a un informe o una plantilla. En la imagen posterior, podeis ver un ejemplo de mapa de navegación, que asociariamos al atributo mes (en el estamos restringiendo la navegación hacia arriba a solo los atributos Año, Semestre y Trimestre, y a nivel inferior a Fecha, no permitiendo navegación transversal).

Editor de Mapas de navegacion

• Creación de plantillas de informes: cualquiera de los informes que hemos creado, lo podremos encapsular en forma de plantilla. Esta plantilla se podrán reutilizar luego en el diseño de otros informes.
• Consolidaciones: es un concepto similar al de los grupos personalizados. Las consolidaciones nos permiten crear atributos “ficticios”, que pueden ser agrupaciones de estos y para los que luego realizaremos operaciones aritmeticas. Las consolidaciones son mas eficientes a nivel de SQL y los calculos se realizan a nivel del Motor análitico (en lugar de en la consulta al DW, como es el caso de los grupos personalizados). En el ejemplo podeis ver el editor de consolidaciones. En los tres primeros grupos, hemos indicado los valores de las lineas de producto que queremos incluir en ellos, solo de un determinado atributo. En el ultimo grupo, Cavas Rueda, hemos indicado dos condiciones sobre atributos diferentes, la linea de Producto CAVAS y la denominación de origen RUEDA (Cavas fabricados en la denominación de origen Rueda). Las consolidaciones se utilizan luego en el editor de informes como si fueran un atributo.

Editor de consolidaciones

• Filtrado utilizando resultados de otro informe.
• Vinculos entre informes y documentos: llamada desde un informe a otro en un lugar determinado con paso de parametros de contexto.
• Creación de Datamarts: resultados de informes guardados en base de datos.
• Etc, etc.

Conclusiones.

Hemos intentado pasar con bastante detalle por todas las funcionalidades de reporting que nos proporciona Microstregy. Partiendo de los objetos de esquema que habiamos preconfigurado (atributos, indicadores, filtros, selecciones dinámicas) y los elementos que podemos utilizar para reporting como las tablas cruzadas, gráficos, grupos personalizados y consolidaciones, columnas calculadas, formatos, umbrales, totales y subtotales, etc, podremos seguramente cubrir todas las necesidades de reporting de cualquier organización. También seguramente habra funcionalidades complejas que nunca utilizaremos, pero que estan ahí disponibles.

El feeling con la herramienta es bueno, aunque la primera impresión es que técnicamente puede ser compleja la comprensión y utilización de todos los elementos que ofrece (sobre todo el tema de los niveles de cálculo de los indicadores), y para hacer determinadas cosas seguramente no bastará con un usuario de tipo medio, sino mas bien avanzado.

Por otro lado, conociendo otras herramientas de reporting, creo que Microstrategy es bastante completo y nos deja muchas puertas abiertas para personalizar los informes y para hacer cosas que no son estandar (como por ejemplo, lo de los grupos personalizados y las consolidaciones, o los indicadores de nivel) o el tema de las transformaciones para la series temporales.

Tras la revisión de la herramienta de reporting, hemos profundizado en su conocimiento y ademas hemos construido un set de ejemplos de informes que incluyen la mayoria de elementos disponibles y que nos permitiran preparar todo el lote de informes para reporting dentro de nuestro proyecto. En los ejemplos explicados no lo hemos visto, pero los filtros y selecciones dinamicas tienen muchisimas funcionalidades que nos van a permitir definir todo tipo de filtrados de todas las formas que necesitemos, como podriamos hacer en cualquier lenguaje de programación, y ademas con funciones avanzadas, para, por ejemplo, el tratamiento de fechas (nos permite definir patrones para crear, a partir de la fecha actual, periodos predefinidos, como el mes anterior hasta la misma fecha, desde principio de año hasta la fecha actual, el primer lunes del año y todas las variantes que podamos imaginar).

En la proxima entrada del blog veremos las funcionalidades OLAP que proporciona Microstrategy y como nos permite trabajar con los cubos inteligentes y origenes de datos MDX.