Navegación Dimensional y cubos OLAP en Microstrategy 9.

Navegación Dimensional y cubos OLAP en Microstrategy 9. respinosamilla 25 Febrero, 2010 - 09:50

En la correspondiente entrada del Blog ( 2.2. Cubos OLAP (On-Line  Analytic Processing)), vimos las características que deberían de tener las herramientas OLAP, diferenciando por un lado la plataforma OLAP y por otro lado los visores OLAP.

El termino OLAP fue introducido en 1993 por el Dr. E.F. Codd. OLAP son las siglas de On-line Analytical Processing. Es un software que nos permite la manipulación multidimensional de la información provenidente de varios origenes de datos y que ha sido almacenada en un data warehouse. El software nos permite crear varias vistas o representaciones de los datos, ademas de un acceso rapido, consistente e interactivo sobre los datos multidimensionales.

Os recomiendo revisar la entrada del Blog antes de continuar viendo como implementa Microstrategy los conceptos OLAP.

Plataforma OLAP de Microstrategy

La plataforma de Microstrategy es una plataforma ROLAP (Relational OLAP), que utiliza una base de datos relacional como soporte de datos. El Intelligence Server traduce nuestros análisis dimensionales a consultas SQL sobre esta base de datos, y construye unos cubos ROLAP virtuales que sera de donde se extraera la información con las consideraciones de Dimensiones e Indicadores seleccionados en las consultas de información. Estamos trabajando con una especie de cubos ROLAP virtuales. Ademas, el Intelligence server puede ser capaz de encontrar los cubos que se utilizan con mas frecuencia y guardarlos en memoria para la próxima vez, con lo que se mejora el rendimiento del sistema.

En la versión 9 esta presente la tecnología de Cubos Inteligentes. Estos cubos tienen una caracteristica MOLAP y funcionan de la siguiente manera. Se definen como si fuera un informe. En esta definición seleccionamos los atributos e indicadores que formaran nuestro cubo (esto será la definición del cubo, lo que determinara las dimensiones por las que podremos luego analizar la información).

Una vez el cubo esta definido, se ha de ejecutar  (en ese momento se llena con información de la base de datos). La información almacenada en el cubo esta preagregada conforme a los atributos que hayamos seleccionado y por tanto preparada y preelaborada para el análisis. Y a partir de ese momento el cubo se publica y ya esta disponible para poder ser utilizado creando informes sobre él. El cubo se podrá ir actualizando automaticamente de forma planificada, por lo que nos olvidaremos de su mantenimiento o de si los datos estan actualizados o no (lo gestionará todo la plataforma).

Funcionamiento de los cubos inteligentes

Los informes que creemos sobre el cubo intelligente solo podrán utilizar los elementos que definamos cuando construyamos el cubo. La información de los cubos esta guardada en el sistema en un sitio aparte y al ejecutar los informes, no se volvera a acceder al Data Warehouse, sino solo a la información almacenada en los cubos inteligentes (veremos mas adelante un ejemplo de construcción, planificación y explotación de un cubo inteligente). Cualquier cambio que vayamos realizando en el informe no necesita volver a acceder a la base de datos, siempre trabajara con los datos del cubo ( a no ser que nos vayamos fuera de las dimensiones establecidas, aspecto que se puede configurar para permitirlo o no). Los cubos se pueden compartir entre diferentes usuarios e informes.

Cliente OLAP de Microstrategy

A nivel de usuario, Microstrategy ofrece las características OLAP mas habituales. La mayoría de ellas ya las hemos visto al detallar los elementos de reporting de Microstrategy (en las dos entradas anteriores del Blog). En Microstrategy, los aspectos de reporting y olap estan mezclados y en un poco dificil saber cuando estamos utilizando características de reporting puro y duro y cuando características Olap. En teoría, cuando trabajamos con Desktop, en la parte inferior derecha nos aparecera el rótulo Estandar u Olap según si utilizamos o no las características Olap de Microstrategy. Realmente estariamos hablando de una distinción entre características Olap estandar y lo que llaman Olap Services.

En lo referente a la navegación multidimensional por los datos, Microstrategy incluye como hemos visto en los ejemplos las funcionalidades habituales:

  • Rotar (Swap): alterar las filas por columnas (permutar dos dimensiones de análisis)
  • Bajar (Down): bajar el nivel de visualización en las filas a una jerarquía inferior.
  • Detallar (Drilldown): informar para una fila en concreto, de datos a un nivel inferior.
  • Expandir (Expand): id. anterior sin perder la información a nivel superior para éste y el resto de los valores.
  • Colapsar (Collapse): operación inversa de la anterior.
  • Pivotación (Pivoting): movimiento de objetos de filas a columnas y de columnas a filas, asi como cambio del orden de los objetos tanto en filas como en columnas.

Cuando navegamos por los datos (y no estamos en los cubos inteligentes), las consultas se recalculan contra la base de datos. Solo puede haber algunos casos en los que se trabaja con el cubo que tenemos en memoría y no se haria un recalculo (por ejemplo, cuando quitamos un atributo del informe y nos quedamos en otro nivel de los que ya estan incluidos).

Navegacion - menu contextual

La gente de Microstrategy habla de las capacidades de Navegación llamandolas Drill-Anywhere. Se entiende que se permite la navegación en cualquier lugar y hacia cualquier dimensión del modelo de análisis (siempre que no lo hayamos limitado quitando la navegación en un informe o restringiendola a unos determinados atributos o dimensiones con los Mapas de Navegación personalizados).

Ademas de la navegación, podemos mencionar otras características, algunas de las cuales ya vimos (y que podeis revisar pulsando el correspondiente link de cada elemento):

  • Grupos de datos: cuando queremos mostrar la información en una estructura de atributos que no esta definida dentro de nuestro modelo de negocio, Microstrategy nos proporciona los Grupos Personalizados y Consolidaciones.
  • Paginación: como ya vimos, con esta funcionalidad podemos sacar un atributo de la cuadricula y crear paginas de datos para cada uno de los valores de dicho atributo (como veis en la imagen anterior, donde en la sección de paginación tenemos el atributo año).
  • Subtotales: también pasamos por ellos cuando vimos las funcionalidades de reporting de Microstrategy. Es la funcionalidad que nos permite incluir totales de diferentes tipos en los niveles seleccionados de los informes.
  • Aliasing: posibilidad de definir nombres de alias para cualquier elementro dentro los informes ( para los indicadores, atributos, grupos personalizados o consolidaciones).
  • Banding: metodo para definir el aspecto de segmentos del informe aplicandos bandas de colores. Podemos aplicar las bandas de colores a filas y columnas según el numero de ellas.
  • Ordenación avanzada: nos permite establecer criterios de ordenación compleja sobre los datos, con el correspondiente editor, indicando por niveles que tipo de ordenación deseamos, considerando tantos los valores de atributos como de indicadores.
  • Visualización jerárquica: aunque en un informe tengamos varios niveles de detalle con la visualización jerarquica o resultados indentados, podemos con un simple click selección el nivel al que queremos ver la información.
  • Umbrales: formatos personalizados sobre los valores de los indicadores para remarcar aquellos que cumplan unas determinadas condiciones (también se pueden sustituir los valores por simbolos, imagenes o texto).

A partir de los elementos siguientes es cuando Microstrategy considera que estamos utilizando funcionalidades Olap Services:

  • Filtros de visualizacion: restricciones de visualización que se aplican al informe calculado y que nos van modificando los resultados en pantalla sin volver a reejecutar la consulta sobre la base de datos (trabaja con el cubo en memoria).
  • Filtros de indicadores: filtrados sobre los valores de los indicadores que indicamos en los resultados del informe. Las condiciones podian ser sobre los valores del indicador o sobre valores de ranking. Igualmente, no se reejecuta la consulta sobre la base de datos al aplicar los filtrados.
  • Columnas calculadas: vimos que en los resultados de ejecución del informe podiamos añadir nuestras propias columnas de indicadores que incluyesen diferentes tipos de calculos (con el editor de expresiones), o bien porcentajes sobre Total, campos de Ordenación (Ranking) y Transformaciones. De la misma forma, los calculos se realizan en memoria.
  • Agregación dinámica: es la funcionalidad que nos permite cambiar de nivel de agregación en los resultados de un informe sin necesidad de volver a recalcular la consulta sobre la base de datos. La funcionalidad actua cuando estamos modificando un informe ejecutado ocultando atributos o indicadores, llevando atributos de la cuadricula a la sección de paginación, etc. Solo cuando los ocultamos, porque en el momento un objeto se borra del informe, entonces este siempre es recalculado.
  • Cubos Intelligentes.

Como funcionalidades avanzadas Olap, y que solo estan disponibles en los informes sobre cubos inteligentes y en los documentos de Report Services, tenemos las siguientes:

  • Definición de elementos derivados: es la definición de grupos de atributos creados al vuelo. Seria algo complementario a los grupos personalizados y las consolidaciones. El hecho de que se puedan construir sobre la marcha nos da posibilidades adicionales de formateo y de agrupación de la información para análisis. Se pueden definir de 3 maneras:
    • Grupos de elementos: lista de elementos, de los que tenemos en pantalla, que forman cada grupo.
    • Elementos que cumplan una condición de filtro: filtrado de los elementos de cada grupo por criterios de filtrado.
    • Operaciones sobre elementos: calculos para combinar los valores de varios atributos.

Trabajando con cubos inteligentes.

Como hemos visto, los cubos inteligentes son un conjunto de datos, generados a partir de la información existente en nuestro DW, y que podremos analizar o explotar a traves de los informes. Los cubos solo almacenan información de los datos, y la gestión de su visualización y formateo quedaran en manos de los informes que construyamos sobre ellos.

Hay algunos elementos que no se pueden incluir en el informe de definición de los cubos inteligentes, que son:

  • Grupos Personalizados y consolidaciones.
  • Elementos olap (filtros de visualización, columnas calculadas y elementos derivados).
  • Selecciones dinámicas o filtros que tengan asociados selecciones dinámicas.

Vamos a trabajar con un ejemplo de Cubo Inteligente para ver todos los pasos de su creación y explotación.

Creación de cubos inteligentes.

Para crear un cubo, crearemos un nuevo objeto en el lugar deseado del tipo Cubo Inteligente. En ese momento se nos abrira el editor de cubos (que es practicamente igual al editor de informes) y añadiremos en el los elementos deseados, siempre teniendo en cuenta las restricciones de elementos que no podemos utilizar.

Editor Cubos Inteligentes

Observad como hemos incluido en el informe un filtro estatico seleccionando la información de los años 2007 y 2008. En la cuadricula hemos seleccionado varios atributos de diferentes dimensiones y los indicadores: importe neto, unidades, margen total e importe de descuentos.

Una vez el cubo es correcto, lo guardamos y tendriamos creada su definición. Pero el cubo todavía no sería accesible, pues no esta ni calculado ni publicados sus resultados.

Publicación.

La publicación de un cubo es tan sencilla como realizar la ejecución de su definición. Cuando se realiza la ejecución, esta no tiene como resultado ningún resultado visible (no se ve una cuadricula como en el caso de los informes). Simplemente, nos aparecerá un log indicandonos que la ejecución ha sido completada.

Podemos ver las características del cubo con la opción del menú contextual Mostar información del cubo. Nos aparecera una pantalla con información general de este:

Propiedades del cubo creado

Ahí podremos ver cuando se creo el cubo, cuando se realizo la ultima actualización de sus datos, el numero de registros obtenidos de la base de datos para llenarlo. Incluso podriamos visualizar la sentencia SQL utilizada para su creación.

select    a14.ano_id  ano_id, a14.mes_id  mes_id, max(a14.mes_desc)  mes_desc, a11.fecha_id  fecha_id,
 max(a14.fecha_desc)  fecha_desc, a13.region_id  region_id, a13.provincia_id  provincia_id, a12.linprod_id  linprod_id,
 max(a12.linprod_desc)  linprod_desc, a12.denom_id  denom_id, sum(a11.importe_neto)  WJXBFS1, sum(a11.unidades)  WJXBFS2,
 sum(a11.margen_total)  WJXBFS3, (sum(a11.Importe_dtos) + sum(a11.importe_promos))  WJXBFS4
from    dwh_ventas    a11
 join    dwd_producto    a12
 on     (a11.material_id = a12.material_id)
 join    dwd_cliente    a13
 on     (a11.cliente_id = a13.cliente_id)
 join    dwd_tiempo    a14
 on     (a11.fecha_id = a14.fecha_id)
where    a14.ano_id in (2007, 2008)
group by    a14.ano_id, a14.mes_id, a11.fecha_id, a13.region_id, a13.provincia_id, a12.linprod_id, a12.denom_id

El cubo esta calculado y ya podriamos crear informes sobre el. Tener en cuenta que sino planificamos o reejecutamos manualmente la actualización del cubo, este quedara como una foto de los datos en un momento puntual, totalmente desvinculada de las actualizaciones que se vayan realizando sobre el Data Warehouse.

Igualmente, tenemos la posibilidad de crear cubos inteligentes a partir de la definición de un informe. Es tan sencillo como, en la edición del informe, seleccionar la opción Datos –> Opciones de cubo inteligente –> Convertir a cubo inteligente. Siempre habrá que tener en mente las restricciones de los tipos de objetos que no podemos utilizar.

Creación de informes sobre cubos inteligentes.

Para crear un informe sobre un cubo inteligente, seleccionaremos el cubo sobre el que queremos trabajar,  y desde el menú contextual, seleccionaremos la opción Crear Informe. Nos aparecerá el editor de informes, tal y como habiamos visto anteriormente, aunque con muchas opciones restringidas. No podremos seleccionar objetos de los definidos en nuestro proyecto (ni objetos del esquema, ni objetos publicos, ni atributos o indicadores). Solo podremos utilizar elementos de los definidos en la construcción del cubo (es decir, atributos e indicadores y calculos sobre ellos).

Por ejemplo, a nivel de atributos, solo podremos seleccionar por Año, Mes, Dia, Provincia, Región, Linea de Producto o Denominación de Origen. A nivel de indicadores, podemos utilizar los indicados o bien campos calculados sobre ellos (del tipo formula, porcentaje sobre el total u orden). En este caso no nos deja utilizar las transformaciones (lo cual es lógico, pues seguramente el ambito temporal de esta podría estar fuera de los datos del cubo).

Tampoco podremos añadir filtros de la forma usual ni selecciones dinámicas para filtrar la información (aunque si selecciones dinámicas del tipo objeto para permitir que se puedan seleccionar en tiempo de ejecución que atributos o indicadores se muestran en el informe). Tampoco podremos utilizar consolidaciones ni grupos personalizados en los informes que conectan con cubos inteligentes. Solo podremos establecer restricciones con el uso de los filtros de visualización. Otra forma de establecer restricciones será a traves de los filtros de seguridad. Podemos establecer limitaciones en los valores de atributos con los que puede trabajar un usuario con este tipo de filtros, que van asociados a la definición de usuario o grupo(pensar el tipico ejemplo de responsables de delegación que solo tienen que tener acceso a visualizar los datos de su ambito geográfico). Son filtros ocultos.

Creacion de informe sobre el cubo inteligente

Podeis ver en la imagen que en los objetos del informe estan todos los utilizados en la definición del cubo. También podeís ver que hemos añadido una columna calculada del tipo % sobre el total. Igualmente hemos añadido un filtro de visualización para restringir los datos a los del año 2007 (podiamos haber restringido por cualquier otro atributo o indicador). El informe esta listo para ejecutar.

Ejecucion de informes sobre cubos inteligentes.

La ejecución de los informes se realiza de la forma habitual. En este caso, no tenemos ningun filtro ni selección dinámica asociada en la ejecución (solo la selecciones dinámicas de objetos, como ya indicamos). Ejecutamos el informe y tenemos el siguiente resultado:

Ejecucion informe sobre el cubo

La ejecución del informe ha leido la información existente en el cubo sin realizar ninguna acción sobre la base de datos y los resultados se han mostrado de forma inmediata. En los resultados del informe podremos trabajar con los elementos habituales, tales como totales, umbrales, ordenación avanzada, insertar campos calculados, formato, aplicar filtros sobre indicadores, filtros de visualización, paginación, etc.

A nivel de la navegación, en principio podremos navegar solamente por los atributos que incluimos en el cubo. Existe la posiblidad de permitir la navegación fuera del cubo (es un parametro que se configura a nivel de proyecto y que también se puede establecer a nivel de cubo). En este caso, al realizar la navegación puede darse el caso que nos “salgamos” del cubo y haya que realizar un recalculo fuera de el, accediendo a datos del DW.

Una vez definido el informe, se podrá cambiar a posteriori el cubo al que esta asociado, aunque con restricciones (el nuevo cubo tendrá que tener todos los elementos necesarios para que el informe se pueda asociar a el).

Actualización de cubos inteligentes. Programacion de actualizaciones.

La actualización de un cubo es tan sencilla como volver a realizar la ejecución de este. En este momento, conforme a la definición del cubo, se vuelve a leer la información de la base de datos y se refresca su contenido. Lo habitual sería que la actualización de los cubos estuviese automatizada para no depender de ningún usuario. Para ello, hemos de tener configuradas las planificaciones, de forma que podamos asignar a una de ellas la actualización del cubo.

Asistente de planificacion

En nuestro ejemplo, hemos creado una planificación que se ejecuta una vez al dia, a las 10 de la mañana. Y la repetición de la ejecución es recursiva. El siguiente paso será cargar la actualización del cubo en la planificación. Para ello, con el menu contextual sobre el cubo, en la opción Planificar entrega a –> Actualizar Cubo.

Subscripcion a la actualizacion del cubo

En la subscripción al cubo podemos indicar una fecha de caducidad, a partir de la cual el cubo ya no se volvera a actualizar. Igualmente, seleccionamos a que planificación asociamos el proceso. Esto determinara la frecuencia de ejecución del proceso. También podremos hacer que se realice inmediante dicha actualización, a parte de la planificación establecida.

Trabajando con elementos derivados en cubos inteligentes.

Los elementos derivados son una funcionalidad exclusiva de los informes construidos sobre cubos intelligentes y de los documentos del report services. Son grupos de elementos que definimos al vuelo interactuando con los resultados de un informe. Para entender mejor los conceptos, vamos a ver un ejemplo.

Informe sencillo sobre cubo

El informe nos nuestra el total de ventas por región. Interactuando sobre los datos del informe, podremos crear los elementos derivados al vuelo. En los informes que trabajan sobre los cubos inteligentes, aparecen varias opciones de menú contextual nuevas, que son Elementos derivados, Crear grupo y Crear calculo.Vamos a ver un ejemplo de cada uno de los tipos.

  • Grupos de elementos: lista de elementos, de los que tenemos en pantalla, que forman cada grupo. Podemos seleccionar en pantalla los atributos que queremos que formen un grupo y con la opción Crear Grupo creamos un grupo para ellos. Le asignamos un nombre, tal y como vemos en la imagen, y el grupo esta creado.

Creacion de grupo de elementos derivados

Repetimos el proceso para crear grupos para las diferentes Zonas Geográficas de España. Vamos seleccionando los diferentes valores de los grupos que queremos crear y hacemos la misma operativa. Tras crear todos los grupos, el informe quedará asi:

Informe con elementos derivado - grupo

Han ido despareciendo los valores individuales y se han sustituido por los nombres de los grupos. Todo de forma dinámica y sin recalcular nada, siempre trabajando en memoria. Podremos ver como ha quedado configurado el elemento derivado desde el editor, al que se accede desde el menú contextual.

Editor elementos derivados

Podemos observar como aparecen los diferentes grupos que hemos ido creando y la lista de valores de cada atributo que hemos incluido en cada uno de los grupos. Aparece un grupo predefinido, llamado Todo lo demas, que siempre aparecerá y sobre el que no podemos realizar ninguna acción (siempre tiene que existir). Si quisieramos realizar algun cambio en los grupos, modificar el orden, la forma de visualizar o como afectan a los subtotales, lo hariamos desde este lugar.

  • Elementos que cumplan una condición de filtro: filtrado de los elementos de cada grupo por criterios de filtrado. Sería similar al anterior. La definición de los grupos se realizaria desde el editor de Elementos derivados. Creariamos los grupos del tipo Filtro, y para cada grupo, estableceriamos las condiciones de filtrado que van a determinar los elementos que van a formar cada grupo.
  • Operaciones sobre elementos: calculos para combinar los valores de un atributo. Un calculo es una operación sobre varios elementos de atributo o sobre los grupos creados anteriormente. En nuestro ejemplo, ademas de los grupos definidos, vamos a crear dos nuevos grupos, del tipo cálculo, que restan, a los importes de un grupo, los valores de una región (en el grupo ZONA LEVANTE restamos los valores de la región VALENCIA y en los de la ZONA CENTRO restamos los valores de MADRID). El editor de elementos tendrá el siguiente aspecto:

Elementos derivados - calculo

En este tipo de elemento derivado, se nos abre el editor de expresiones. Nos aparecen los diferentes valores de atributo del nivel seleccionado que tengamos en el informe y los grupos que hayamos definido. Y podemos hacer operaciones sobre ellos (suma, resta o funciones). Luego, en el informe, se realizara el mismo cálculo a nivel de los indicadores en cada fila del informe. Los dos nuevos grupos que hemos añadido, los hemos configurado en la pestaña propiedad para que no se tengan en cuenta en los subtotales. Podeis observar como al grupo ZONA LEVANTE le hemos restado los valores de una región en concreto. El informe resultante despues de aplicar los elementos derivados de grupo y de calculo sería el siguiente:

Informe con elementos derivado - calculo

Podemos ver los dos nuevos grupos que aparecen, y como no son tenidos en cuenta en el total general. Para ver como se estan haciendo los cálculos, desglosamos el informe a nivel de provincia, y el informe resultante es el siguiente:

Informe con elementos derivado - calculo y desglose

Al desglosar la información, se mantiene en el nivel superior los grupos de elementos derivados. En el grupo donde a la zona de Levante restamos los datos de la region de Valencia, los valores de las provincias de dicha región presentan importes cero. Y el valor del elemento derivado de cálculo es la suma del resto de las provincias de las otras regiones.

Los elementos derivados se definen sobre cada atributo del informe y no se pueden combinar elementos de atributos diferentes (no puedo meter en los grupos de regiones operaciones o valores del atributo provincia, por ejemplo).

Conexión con otras bases de datos via MDX.

Microstrategy permite trabajar con cubos de otros sistemas que admitan MDX source tales como SAP BI, Hyperion Essbase y Microsoft Analysis Services. Por un lado, podemos realizar la integración de los cubos MDX en Microstrategy (importando los cubos, mapeandolos y realizando la creación de metricas a traves de los datos de el). Ademas, también podremos realizar reporting sobre ellos. No vamos a entrar en el análisis de esta funcionalidad.

Dynamic Sourcing

Como elemento destacable, el Dynamic Sourcing es una característica avanzada de la plataforma de Microstrategy que permite que los informes estandar accedan a los cubos inteligentes que puedan cubrir sus requerimientos de información (en lugar de hacerlo con la base de datos del DW). Con esta característica, se puede mejorar el rendimiento del sistema.

El Dynamic Sourcing esta deshabilitado por defecto y para habilitarlo se requiere configuración a nivel proyecto, cubos inteligentes, atributos, métricas e informes.

Conclusiones

Microstrategy cumple de sobra los requisitos de navegación dimensional y tipos de operaciones y elementos que podemos utilizar en el análisis dimensional. Muchos de los elementos OLAP han sido utilizados en las herramientas de reporting sin percatarnos de ello y viendolo como una funcionalidad mas de este. La funcionalidad de los cubos inteligentes es muy avanzada y presenta muchas ventajas, sobre todo en la rapidez de elaboración de los informes y en la facilidad para la creación y actualización de los cubos (aunque tiene algunas limitaciones respecto a los elementos que se pueden utilizar en ellos). El uso de la característica Dynamic Sourcing puede ser interesante para aumentar el rendimiento del sistema, aunque puede ser complicada su configuración y mantenimiento.

Finalmente, aunque esta funcionalidad no la hemos utilizado, puede ser interesante utilizar Microstrategy para atacar otras bases de datos a través de MDX.