Ing. Germán Zelada (MCSD)
Gerente de COGNOS Soluciones
Sumario
En este artículo se presentan tres enfoques que se pueden seguir para crear una plataforma de análisis multidimensional que permita a usuarios Excel realizar análisis interactivo usando funcionalidad de tablas dinámicas con datos provenientes de sistemas OLTP que acceden a bases de datos relacionales. Para cada enfoque presentado se discuten sus ventajas, desventajas y situaciones de uso.
Introducción
Cuando se necesita diseñar una determinada solución de análisis multidimensional usando tablas dinámicas de Excel como herramienta para los usuarios finales que acceden a datos operacionales que residen en fuentes de datos transaccionales, se pueden seguir diversos enfoques de arquitecturas que permiten alcanzar los objetivos y metas buscados por la solución.
Dentro de la funcionalidad nativa de Excel se tienen las tablas dinámicas o pivot tables, que son componentes interactivos que permiten analizar en forma dinámica y muy flexible datos multidimensionales.
Además de otra funcionalidad de análisis multidimensional, Excel a través de las tablas dinámicas permite realizar las siguientes operaciones:
· Drill Down.- Consiste en ir a un nivel de detalle mayor a partir de un resumen. Por ejemplo ver el detalle de las ventas cuyo resumen es un monto que se está visualizando.
· Drill Up.- Es la operación contraria al Drill Down, es decir subir de nivel partiendo del detalle para llegar al resumen.
· Drill Through.- Es la operación con la que se puede acceder al mayor nivel de detalle disponible para un valor dado que este calculado con alguna función de agregación.
· Pivoteo.- Consiste en mover en forma interactiva los valores de fila en valores de columna o de datos o de filtro o desde cualquier ubicación a cualquier otra, siendo los datos analizados recalculados y presentados al usuario final.
· Filtrado.- Es la operación que consiste en poner datos en el área de filtros y poder elegir valores que servirán para establecer condiciones de búsqueda para los valores y datos que se están visualizando.
Los datos que se analizan con las tablas dinámicas pueden provenir de distintas fuentes, entre las fuentes disponibles se tienen datos del mismo Excel, datos proveniente de alguna fuente a la cual se pueda acceder vía un controlador ODBC u OLEDB y también datos residentes en motores multidimensionales como Analysis Services de SQL Server.
Al realizar arquitectura de la solución se tiene que tomar una serie de decisiones de diseño, como por ejemplo si los datos serán cargados primero a Excel y luego analizados desde las tablas dinámicas o si utilizaremos o no un motor multidimensional como fuente de datos.
En el presente artículo se analizan tres enfoques que se pueden seguir para resolver la problemática planteada de habilitar a los usuarios de Excel el análisis multidimensional interactivo utilizando tablas dinámicas y cuyos datos provienen de bases de datos relacionales gestionados por sistemas transaccionales conocidos como sistemas OLTP o sistemas operacionales.
Para cada enfoque se analizan sus ventajas y desventajas o limitaciones, de manera que se pueda orientar la aplicación a los requerimientos y limitaciones que se tengan a la hora de implementar este tipo de soluciones también conocidas como sistemas OLAP.
Enfoques
Los enfoques analizados en el presente documentos son los siguientes:
· Datos fuente OLTP que alimentan Data Marts y luego estos alimentan cubos OLAP a través de procesos ETL.- Es el enfoque más complejo y sigue el flujo completo de una aplicación OLAP. Incluye las fuentes de datos, los almacenamientos intermedios entre las fuentes y los cubos, los cubos que residen en una base de datos multidimensional y finalmente, Excel como herramienta de análisis para el usuario final.
· Datos fuente OLTP que alimentan cubos OLAP a través de vistas.- Es un enfoque más sencillo que el primero donde se dejan de usar los almacenamientos intermedios entre las fuentes de datos y los cubos en la base de datos multidimensional. En lugar de vistas se pueden usar los DSV (Data Source Views) de SQL Server 2005.
· Datos fuente OLTP cargados a Excel a través de vistas.- Ese enfoque es el más simplificado de todos en el que no solamente se deja de usar los repositorios intermedios entre la fuente y los cubos, sino que también se prescinde del motor dimensional y los datos son contenidos directamente en el mismo Excel.
La descripción y análisis de cada uno de estos enfoques es presentado a continuación.
Enfoque Datos Fuente que alimentan Data Marts y luego estos alimentan cubos OLAP a través de procesos ETL
Descripción
El siguiente gráfico esquematiza este enfoque:
Este es el enfoque tradicional para el diseño de una solución de data warehousing, ya sea como una solución corporativa a través de un Data Warehouse o de una solución departamental a través de un Data Mart.
En esta arquitectura se tiene diversas fuentes de datos heterogéneas (bajo distintos formatos, aplicaciones y plataformas) que son consolidada en una base de datos relacional llamada el Data Warehouse o el Data Mart.
Los procesos que se encargan de realizar la transferencia de los datos desde la fuente hasta el Data Wharehouse o Data Mart se llaman en forma genérica procesos ETL y se encargan de la extracción de los datos desde la fuente, su transferencia y transformación en caso de ser necesario hasta su carga en el destino.
Esta arquitectura considera también, en caso de ser necesario, una base de datos temporal llamada “Staging” cuyo propósito es servir de repositorio temporal para realizar la “limpieza” de los datos aplicando las validaciones y transformaciones requeridas. Una vez los datos han sido trabajados en la base de datos de Staging son recién pasados al Data Wharehouse o Data Mart.
Los datos en el Data Wharehouse o Data Mart son estructurados en forma desnormalizada bajo representaciones de tipo estrella, dando lugar a las tablas de hechos y las tablas de dimensiones.
La fuente de datos de las cuales se alimentan los cubos en una base de datos multidimensional son estas estructuras en forma de estrella que se construyen en los Data Warehouses o Data Marts.
Los cubos son estructuras especiales que permiten realizar entre otras cosas preagregaciones (cálculos que son almacenados y consultados directamente en la estructura), característica que permite tener los tiempos de respuesta requeridos para este tipo de soluciones donde se combinan dos características encontradas con fuerzas opuestas: grandes volúmenes de datos que deben ser consultados a gran velocidad.
Luego, y como punto final, están los usuarios finales de la solución que accede a los cubos OLAP desde Excel, utilizando la funcionalidad nativa de tablas dinámicas existentes en
Ventajas
Habilita el análisis multidimensional con tablas dinámicas en Excel
Hace uso de un motor multidimensional lo que brinda funcionalidad adicional y la velocidad asociada a los datos preagregados que soporta la tecnología
Puede soportar grandes volúmenes de datos en el tiempo manteniendo su desempeño, que es el requerimiento típico en este tipo de soluciones OLAP.
Habilita el uso del Addin de Excel para Analysis Services que solo trabajan con cubos externos a Excel.
Es el enfoque más flexible y mantiene un excelente desempeño, permitiendo consolidar y centralizar información para su análisis proveniente de múltiples fuentes de datos heterogéneas, incluyendo o no diversos formatos, con distintas estructuras, ejecutadas bajo distinta aplicaciones, en distintas plataformas (sistemas operativos), de un empresa, de varias empresas, de un gestión, de varias gestiones, etc.
Desventajas
Es el enfoque que requiere mayor trabajo para su desarrollo, pruebas, puesta en producción, soporte y mantenimiento debido a que considera todos los elementos y componentes siendo la arquitectura más completa.
Enfoque que tiene más puntos posibles de falla.
Enfoque que requiere mayor procesamiento intermedio y automatización de estos procesos para actualizar los cubos de datos a ser consumidos por los usuarios.
Este Enfoque requiere un motor multidimensional para su implementación, con todos los aspectos asociados de costo por licencias, instalación, configuración y operación del servidor. Por supuesto considerando el conocimiento y experiencia necesaria para manejar esta tecnología y todos sus elementos (bases de datos, particiones, cubos, medidas, dimensiones, jerarquías, etc.).
Enfoque Datos Fuente OLTP que alimentan cubos OLAP a través de vistas
Descripción
El siguiente gráfico esquematiza este enfoque:
Este enfoque a diferencia del primero no cuenta con el ambiente de consolidación (Data Wharehouse o Data Mart, base de datos de Staging y procesos ETL).
En esta arquitectura se crean un conjunto de vistas en la base de datos fuente, estas vistas lo que hacen es simular una o varias estructuras estrella como las que contienen los Data Warehouses o Data Marts. De manera que los cubos en la base de datos Multidimensional utilizan esta vistas para dar lugar a sus cubos y poder realizar su procesamiento (calculo y almacenamiento de pre agregados).
Ventajas
Habilita el análisis multidimensional con tablas dinámicas en Excel
Hace uso de un motor multidimensional lo que brinda funcionalidad adicional y la velocidad asociada a los datos preagregados que soporta la tecnología
Puede soportar grandes volúmenes de datos en el tiempo manteniendo su desempeño, que es el requerimiento típico en este tipo de soluciones OLAP.
Habilita el uso del Addin de Excel para Analysis Services que solo trabajan con cubos externos a Excel.
Este es un enfoque más sencillo que el anterior pues todo el almacenamiento intermedio entre la fuente de datos y los cubos es descartado, incluyendo los procesos requeridos para mantener estas estructuras. Al no existir estos almacenamientos y procesos intermedios, la complejidad de la aplicación y los puntos de falla de la solución se disminuyen en forma importante.
Desventajas
Solo permite tener una fuente de datos y dificulta la consolidación.
No es posible tener fuentes heterogéneas de datos, de múltiples empresas, de varias gestiones, además de dificultar el mantener cubos con datos históricos.
Enfoque Datos Fuente OLTP cargados a Excel a través de vistas
Descripción
El siguiente gráfico esquematiza este enfoque:
Ventajas
Habilita el análisis multidimensional con tablas dinámicas en Excel
Este es el enfoque más sencillo, pues además de descartar todo el almacenamiento y procesos intermedios entre la fuente de datos y los cubos también se descarta de la aplicación la base de datos multidimensional, por estas características, la complejidad de la aplicación y los puntos de falla de la solución se disminuyen aún más.
Habilita el uso de funcionalidad nativa de las tablas dinámicas de Excel que no está disponible para cubos externos (como Drill Through)
Desventajas
No hace uso de un motor multidimensional lo que restringe funcionalidad adicional y la velocidad asociada a los datos preagregados que soporta la tecnología
No soporta grandes volúmenes de datos pues la cantidad de registros que pueden ser manipulados está limitada al número de filas que soporta una hoja Excel.
No Habilita el uso del Addin de Excel para Analysis Services que solo trabajan con cubos externos a Excel.