TECHNOLOGÍA: Data Warehouse

Acelerar el Depósito de Datos

por Dan Vlamis

Oracle OLAP 11g incorpora características de alto desempeño para el depósito de datos en Oracle Database 11g .

La opción de Oracle Database 11g Enterprise Edition, Oracle OLAP 11g, es un motor con características completas para el procesamiento analítico online. Debido a que Oracle OLAP 11g está incluido en Oracle Database, se beneficia con las características para la administración, seguridad y escalabilidad de la base de datos.

Las nuevas características clave de Oracle OLAP 11g incluyen visualizaciones relacionales de un cubo administradas por la base de datos, una fuente de filas para exploración de cubos que es utilizada por el optimizador SQL, y visualizaciones materializadas organizadas por cubos. Este artículo se focaliza en mejoras de Oracle OLAP 11g, cómo los cubos OLAP se adecuan a Oracle Data Warehouse, y cómo se utilizan dentro de aplicaciones basadas en SQL para la inteligencia de negocios a fin de ofrecer contenido enriquecido con alto desempeño.

¿Por qué OLAP?

Las consultas de usuarios son, a menudo, impredecibles. En distintos días, los mismos usuarios realizarán análisis de tendencias, desgloses de líneas de productos específicos, y comparaciones de las ventas semanales frente a aquellas de la misma semana del año anterior. Con sistemas relacionales estándar, es difícil optimizar las estructuras de datos que brindan un buen desempeño de consultas para este patrón de consultas tan impredecible.

Para abordar esta necesidad, los DBA y diseñadores con frecuencia crean un sistema de tablas de resumen o visualizaciones materializadas. Los cubos OLAP, que proporcionan un desempeño de consultas consistentemente rápido en todo el modelo de datos, a menudo ofrece una mejor alternativa para la administración de resúmenes. Los cálculos sofisticados pueden incorporarse fácilmente dentro del cubo a fin de mejorar el contenido analítico de las aplicaciones.

Estos cálculos a menudo dependen de los datos de muchas filas y cálculos entre filas. Por ejemplo, un cubo OLAP podría incluir un cálculo que compare las ventas del año actual para cada región y línea de producto con aquellas del mismo período del año pasado y de hace dos años. La estructura de cubo se optimiza para manejar este tipo de consultas y análisis.

¿Por qué Oracle OLAP?

Oracle OLAP utiliza un espacio de trabajo analítico en la base de datos para realizar un análisis OLAP. Oracle OLAP almacena los datos en la base de datos como cubos multidimensionales, que están diseñados para consultas y actualizaciones cada vez más rápidas. Los cubos están organizados por dimensiones, que actúan como claves para los datos de la realidad y definen la estructura básica del cubo. De muchas formas, un cubo es similar a un esquema star. El cubo desempeña el papel de la tabla de datos, y una dimensión OLAP desempeña el papel de una tabla de dimensiones. Las dimensiones pueden ser listas simples de miembros, o pueden estar organizadas en niveles y jerarquías. Las dimensiones jerárquicas permiten que los datos se agrupen desde niveles más bajos a niveles más altos de resumen. Respaldan la navegación, como los desgloses y ciertos tipos de cálculos como Share to Parent, Share within Ancestor y Rank within Parent. También respaldan muchos cálculos de serie de tiempo como Acumulado Anual. Estos tipos de cálculos son fáciles de definir dentro del administrador de espacio de trabajo analítico (la herramienta administrativa de Oracle OLAP 11g) y son eficientemente computados dentro del cubo en tiempo de ejecución.

Oracle OLAP 11g puede acortar significativamente los tiempos de procesamiento de consultas para los usuarios de herramientas de inteligencia de negocios basadas en SQL, como Oracle Business Intelligence Suite Enterprise Edition y otras herramientas externas. Oracle OLAP 11g también facilita la inclusión de cómputos en un cubo OLAP. Las capacidades de Oracle OLAP 11g son provistas dentro de Oracle Database, lo cual permite un control centralizado de los datos, reglas de negocio y seguridad.

Crear Visualizaciones Materializadas Organizadas por Cubo para la Administración de Resumen

Las visualizaciones materializadas organizadas en cubo constituyen una nueva capacidad en Oracle OLAP 11g, lo cual permite que los cubos sean utilizados como solución para la administración de resumen, a menudo reemplazando tablas de resumen relacionales y visualizaciones materializadas basadas en tablas. En este contexto, una aplicación consulta la tabla de datos utilizando funciones de agrupación (como SUM) y una cláusula GROUP BY (AGRUPAR POR), y la característica de reescritura de consulta de la base de datos automáticamente redirecciona la consulta a la vista materializada organizada por cubo. El cubo administra los datos de resumen en forma transparente para la aplicación de consulta, y los usuarios se benefician con un mejor desempeño de consultas.

Para crear una visualización materializada organizada por cubo, los DBA crean un cubo a partir de las tablas de datos y dimensiones que las aplicaciones consultan en ese momento. Luego logran que el cubo permita la reescritura de consultas y la renovación de visualizaciones materializadas. Las visualizaciones materializadas pueden crearse mediante una invocación API o, más comúnmente, mediante ciertas opciones del administrador de espacio de trabajo analítico, como se muestra en la Figura 1. El cubo cuenta con indexación propia y administra todas las agrupaciones.

Crear el cubo de muestra GLOBAL. Para crear y utilizar el cubo OLAP de muestra GLOBAL y repasar las capacidades de Oracle OLAP 11g que se muestran en este artículo, instale y configure los productos, esquemas y espacios de trabajo de la siguiente manera :

1. Instale el cliente y el servidor de base de datos Oracle Database 11g. Tenga en cuenta que el servidor y el cliente son descargas separadas, ambas disponibles en Oracle Technology Network dentro de otn.oracle.com. En esta página, seleccione Descargas-> Base de Datos, y luego haga click en Ver Todo, al lado del nombre de su sistema operativo en la sección de descargas de Oracle Database 11g. Cuando instale el servidor de base de datos, puede utilizar opciones por defecto. Cuando instale el cliente, asegúrese de seleccionar la opción Administrador para instalar todo el conjunto de herramientas, que incluye al administrador de espacio de trabajo analítico.

2. Descargue el esquema GLOBAL (global_11g_schema.zip) desde otn.oracle.com/products/bi/olap . En esta página, haga click en el enlace 11.1 al lado de Esquema Global en la sección Descargas de esta página. Extraiga los contenidos del archivo en un directorio.

3. Descargue otn.oracle.com/oramag/oracle/08-may/o38olap.zip , y extraiga los contenidos, el archivo global_oramag.xml, en un directorio.

4. Siga las instrucciones especificadas en el archivo global_11g_readme.html, dentro del archivo .zip de esquema GLOBAL descargado en el paso 2. Estas instrucciones permiten crear el esquema GLOBAL con las tablas necesarias para crear un espacio de trabajo analítico.

5. Active el administrador de espacio de trabajo analítico. Desde el menú de Inicio Windows, puede encontrarlo en {Página de Inicio del Cliente Oracle11g} -> Herramientas Integradas de Administración -> Hoja de Trabajo y Administrador de Espacio de Trabajo Analítico de Oracle OLAP.

6. Cree una conexión con su instancia, al hacer click sobre el botón derecho del mouse en Bases de Datos y seleccionar Agregar Base de Datos al árbol. . . . Ingrese una descripción para la conexión y la cadena de conexión adecuada para su instancia (como localhost:1521:orcl11). Luego haga click en Crear.

7. Expanda el árbol de Base de Datos hasta que aparezca su nueva conexión, y luego haga click en + (signo más) al lado de su conexión para conectarse a la instancia. Ingrese GLOBAL en nombre de usuario, e ingrese la contraseña que se asignó en el paso 4.

8. Expanda el árbol Esquemas hasta que vea los Espacios de Trabajo Analíticos. Haga click sobre el botón derecho del mouse en Espacios de Trabajo Analíticos, y seleccione Crear Espacio de Trabajo Analítico desde la Plantilla. . . .

9. Navegue por la selección de archivos para encontrar la plantilla global_oramag.xml que descargó en el paso 3. Seleccione este archivo, y haga click en Crear. Espere durante unos minutos mientras se crea el espacio de trabajo analítico.

10. Haga click sobre el botón derecho del mouse en el nuevo espacio de trabajo analítico GLOBAL, y seleccione Conservar Espacio de Trabajo Analítico GLOBAL.

11. Haga click en >> (Agregar Todo), y luego en Finalizar. Este paso carga los datos en el cubo y los agrupa de acuerdo con las configuraciones especificadas en la plantilla. Puede demorar algunos minutos en finalizarse. Una vez finalizado, haga click en Cerrar para cerrar el archivo de creación.

Ejecute la consulta OLAP. Con el cubo GLOBAL cargado y los datos agrupados, ahora ingrese lo siguiente- 

SELECT t.calendar_year_id time,
          p.class_id product,
          c.region_id region,
          SUM(f.sales) sales
FROM time_dim t,
        product_dim p,
        customer_dim c,
        units_fact f
WHERE t.month_id = f.month_id
   AND p.item_id = f.item_id
   AND c.ship_to_id = f.ship_to_id
   AND c.region_id = 'AMER'
GROUP BY t.calendar_year_id,
              p.class_id,
              c.region_id
ORDER BY t.calendar_year_id,
              p.class_id;


-utilizando Oracle SQL Developer (u otra herramienta SQL elegida). Esta consulta OLAP típica resume la información en la tabla de datos UNITS_FACT por año, clase y región solo para aquellas filas que tienen un valor REGION_ID de AMER.

Si no hubiese tablas de resumen, esta consulta debería buscar en la tabla CUSTOMER_DIM (dimensión) para encontrar todas las filas con REGION_ID='AMER' y luego encontrar las filas de la tabla de datos con esos valores SHIP_TO_ID. Finalmente, debería resumir la columna VENTAS en la tabla de datos, agrupando los datos por año, clase y región.

Con Oracle OLAP 11g, utilizando visualizaciones materializadas, estos totales son inmediatamente accesibles. Simplemente toma las filas de los datos resumidos y los devuelve al procesador SQL. El resultado es una respuesta de consultas mucho más rápida, con un sistema simple para la administración de resumen fácil de mantener.

Si ejecuta un plan de explicación sobre la consulta, verá que el optimizador Oracle utiliza la operación CUBE_SCAN en lugar de la operación HASH JOIN, que es mucho menos eficiente y que es requerida cuando usted no cuenta con las visualizaciones materializadas. Para ver la diferencia, ejecute los comandos 

alter materialized view cb$units_cube disable query rewrite
y
alter materialized view cb$units_cube enable query rewrite 


y vea los planes explicativos resultantes en cada caso. Usted también verá una gran diferencia en desempeño. En mis pruebas sobre la pequeña tabla de datos GLOBAL (300.000 filas del nivel más bajo), he descubierto que con la activación de la característica para la reescritura de consultas (mediante el uso del cubo), la consulta arrojaba resultados 2-5 veces tan rápido que cuando no se utiliza el cubo. Encontrará más diferencias en el desempeño al utilizar cubos más grandes, con tamaños más realistas.

El sistema para la administración de resumen también es fácil de mantener. Cuenta con una sola visualización materializada, en lugar de una visualización materializada separada para cada combinación de niveles en cada dimensión. Usted puede controlar el punto hasta el cual se precomputan los datos, pero el cubo siempre se presenta como totalmente resuelto para cada combinación de los niveles de las dimensiones involucradas. Un registro de las visualizaciones materializadas guarda un historial de las filas que cambian en la tabla base, y controla qué parte del cubo se actualiza y agrupa, y cuándo.

Cálculos Adicionales Expuestos Mediante Visualizaciones Automáticas

Usted puede utilizar los cubos en Oracle OLAP 11g como solución para la administración de resumen, pero Oracle OLAP 11g realmente se destaca cuando es utilizado para realizar cálculos. Oracle OLAP puede realizar muchos cálculos que pueden ser difíciles de expresar en SQL y pueden requerir mucho tiempo de realización con el motor relacional estándar.

Los cálculos Share y Acumulado Anual son buenos ejemplos de cálculos que se desempeñan mejor con Oracle OLAP. Una vez definidas en el espacio de trabajo analítico como mediciones calculadas, estas mediciones se exponen como columnas extra en una visualización que es automáticamente creada para un cubo. Usted puede luego consultar estas mediciones de la misma manera que consultaría cualquier medición base desde la visualización del cubo.

Al trabajar con las visualizaciones del cubo, tenga en cuenta que la visualización contiene datos preagrupados en múltiples niveles. Como resultado, usted no necesita incluir ninguna función de agrupación, como SUM, y generalmente no necesita cláusulas GROUP BY. Pero también debe especificar el nivel de datos que quiere seleccionar mediante la cláusula WHERE adecuada.

Incorpore la consulta de la Lista 1 en Oracle SQL Developer (o su herramienta SQL preferida) para ver las ventas trimestrales y ver cómo esas ventas se comparan sobre una base de acumulado anual con los mismos períodos del año pasado. La consulta también muestra cómo proporcionar múltiples niveles de la dimensión de tiempo en una sola consulta.

Lista de Códigos 1: Ventas trimestrales y comparación 

SELECT t.long_description time,
          p.long_description product,
          cu.long_description region,
          ch.long_description channel,
          ROUND(u.sales) SALES,
          ROUND(u.sales_ytd) YTD,
          ROUND(u.sales_ytd_dif_yrago) DIFF,
          ROUND(u.sales_ytd_pct_dif_yrago, 6) PCT
FROM time_view t, product_view p, customer_view cu, channel_view ch,
units_cube_view u
WHERE t.dim_key = u.time
   AND p.dim_key = u.product
   AND cu.dim_key = u.customer
   AND ch.dim_key = u.channel
   AND (t.level_name IN('CALENDAR_YEAR', 'CALENDAR_QUARTER') 
     OR t.time_calendar_quarter_id = 'CY1999.Q1')
   AND p.level_name IN('CLASS', 'TOTAL')
   AND cu.level_name = 'TOTAL'
   AND ch.level_name = 'TOTAL'
   AND p.dim_key = 'CLASS_SFT'
ORDER BY t.end_date, t.time_span;


Ejecutar la consulta en Oracle SQL Developer arroja los resultados que se muestran en la Figura 2. Fíjese que las filas aparecen por años, trimestres y tres meses especificados—todo en una sola visualización.

 Este ejemplo muestra solo los cálculos más básicos que Oracle OLAP 11g puede realizar. Un wizard de medición recientemente calculado del administrador de espacio de trabajo analítico permite calcular la participación, los índices, jerarquías, totales en movimiento, totales acumulativos y expresiones definidas por usuarios. Usted incluso puede calcular los modelos, pronósticos y regresiones.

Actualización de Cubos, Usando Visualizaciones Materializadas

A medida que las tablas de datos adquieren nuevos datos, Oracle Database debe retener los cubos y recalcular los totales. Usted puede simplificar este procedimiento al aprovechar la capacidad de actualización de las visualizaciones materializadas en Oracle Database. Al marcar Activar Actualización de Visualizaciones Materializadas en el casillero del Cubo (que se muestra en la Figura 1), usted puede especificar la actualización de un espacio de trabajo analítico para que sea igual a la actualización de cualquier otra visualización materializada. Por ejemplo, para actualizar el espacio de trabajo analítico GLOBAL, use el siguiente comando: 

dbms_mview.refresh('CB$UNIT_CUBE', 'F')

Este comando vuelve a cargar el espacio de trabajo analítico desde la tabla de datos y recalcula cualquier elemento que necesite actualización. Los mecanismos y la sintaxis son exactamente iguales a los de las visualizaciones materializadas sin cubos, con métodos para controlar aspectos como deterioros y cronogramas. Los registros de visualizaciones materializadas identifican qué parte del cubo necesita recargarse. Oracle OLAP luego utiliza esta información para recargar y recalcular las partes adecuadas del cubo.

Agrupación

Oracle OLAP siempre devuelve los datos como totalmente resueltos, pero usted tiene el control hasta el punto en que los datos se preagrupan. En Oracle OLAP 10g, usted podría especificar cuáles niveles del cubo preagrupar. Esa capacidad todavía es respaldada en Oracle OLAP 11g, pero ahora usted también puede especificar el porcentaje del cubo que quiere preagrupar. Oracle OLAP luego descifrará cuáles áreas del cubo preagrupar y cuáles áreas agrupar sobre la marcha. Oracle OLAP descifra cómo equilibrar el desempeño en tiempo de ejecución con el desempeño de la carga y el tiempo necesario para calcular ciertas agrupaciones. La Figura 3 muestra un casillero de diálogo para la creación de cubos que especifica una preagrupación del 20 por ciento.

Presentación de Datos

Al igual que con versiones anteriores de Oracle OLAP, usted puede acceder a los cubos de Espacio de Trabajo Analítico mediante Java OLAP API. No obstante, Oracle ha elegido SQL como el lenguaje preferido para acceder a los datos de cubos Oracle OLAP. Con visualizaciones automáticamente generadas que brindan datos de cubos mediante SQL estándar, cualquier entorno de aplicaciones con una interface SQL puede acceder a los datos desde los cubos. Como resultado, los datos Oracle OLAP pueden ser accedidos por aplicaciones Oracle como Oracle Business Intelligence Suite Enterprise Edition Plus, Oracle Discoverer y Oracle Application Express. Asimismo, cualquier otra herramienta para el desarrollo de aplicaciones que utilice SQL también puede acceder a los datos Oracle OLAP. Como ejemplo, la Figura 4 muestra un informe de Oracle Business Intelligence Suite Enterprise Edition ejecutándose sobre los mismos datos del cubo Oracle OLAP que se demuestran en este artículo. Oracle OLAP brinda todos los totales y computa todos los cálculos que se muestran.

 

Otras Nuevas Características

figura 1
Figura 1: Administrador de espacio de trabajo analítico

figura 2
Figura 2: Resultados trimestrales y de comparación
figura 3
Figura 3: Casillero de diálogo para la creación de un cubo que especifica una preagrupación
del 20 por ciento

figura 4
Figura 4: Oracle Business Intelligence Suite Enterprise Edition con acces a los datos
del cubo Oracle OLAP

Otras Nuevas Características

Otras características de Oracle OLAP 11g hacen que sea más fácil trabajar con espacios de trabajo analíticos e integrar OLAP en el motor de base de datos. Hay nuevos asesores para almacenamiento y particionamiento que simplifican el proceso de diseñar cubos para un almacenamiento y mantenimiento óptimos. Asimismo, las nuevas políticas para de seguridad de datos facilitan la autorización de acceso a diferentes partes de los cubos para varios usuarios. Un debate más detallado sobre estas capacidades excede el alcance de este artículo.

Conclusión

Con las capacidades mejoradas de Oracle OLAP 11g, resulta mucho más fácil incorporar OLAP en el marco de un depósito de datos. Algunas mejoras clave como las visualizaciones materializadas, la creación de visualizaciones automáticas y wizards de agrupación convierten a Oracle OLAP en una opción atractiva para acelerar el desempeño de las consultas.

 


Dan Vlamis( dvlamis@vlamis.com ) es presidente de Vlamis Software Solutions, Inc. Ha trabajado con productos de inteligencia de negocios Oracle desde 1986. Con regularidad, realiza presentaciones en importantes conferencias de Oracle, y es un experto reconocido en Oracle OLAP y Oracle Business Intelligence.