O Oracle OLAP 11g fornece recursos de data warehouse de alta performance para o Banco de Dados Oracle 11g.

Sendo um opcional do Banco de Dados Oracle 11g Enterprise Edition, o Oracle OLAP 11g é um mecanismo de processamento analítico online com recursos completos. Como o Oracle OLAP 11g é incorporado ao Banco de Dados Oracle, ele se beneficia dos recursos de escalabilidade, segurança e capacidade de gerenciamento do banco de dados.

Entre os principais novos recursos do Oracle OLAP 11g estão visualizações relacionais de um cubo gerenciadas pelo banco de dados, uma fonte de linhas para pesquisa de cubos usada pelo otimizador SQL e visualizações materializadas organizadas por cubos. Este artigo trata principalmente das melhorias do Oracle OLAP 11g, de como os cubos OLAP se ajustam ao data warehouse Oracle e de como são usados nos aplicativos de business intelligence baseados em SQL para oferecer conteúdo rico com alta performance.

Por que OLAP?

Consultas de usuários em geral são imprevisíveis. Em dias diferentes, os mesmos usuários executam análise de tendências, fazem detalhamento de linhas de produtos específicas e comparam as vendas de uma semana com as da mesma semana do ano anterior. Com sistemas relacionais padrão, é difícil otimizar estruturas de dados que proporcionem desempenho constantemente bom em um padrão de consultas imprevisível como esse.

Para atender a essa necessidade, os DBAs e designers freqüentemente criam um sistema de tabelas de resumo ou visualizações materializadas. Os cubos OLAP, que oferecem desempenho constantemente alto em consultas em todo um modelo de dados, normalmente oferecem uma melhor alternativa ao gerenciamento de resumos. Cálculos sofisticados podem ser facilmente incorporados ao cubo para aprimorar o conteúdo analítico dos aplicativos.

Esses cálculos em geral dependem dos dados de muitas linhas e cálculos entre linhas. Por exemplo, um cubo OLAP pode incluir um cálculo que compara as vendas do ano vigente para cada região e linha de produtos com os dados do mesmo período de um ou dois anos atrás. A estrutura do cubo é otimizada para lidar com esse tipo de consulta e análise.

Por que Oracle OLAP?

O Oracle OLAP usa um workspace analítico no banco de dados para executar uma análise OLAP. Ele armazena dados no banco de dados como cubos multidimensionais, que são projetados para garantir rapidez na atualização e consulta incrementais. Os cubos são organizados por dimensões, que atuam como chaves para os dados dos fatos e definem a estrutura básica do cubo. Em muitas maneiras, um cubo é semelhante a um esquema de estrela. O cubo desempenha o papel da tabela de fatos, e uma dimensão OLAP desempenha o papel de uma tabela de dimensões. As dimensões podem ser simples listas de membros ou organizadas em níveis e hierarquias. As dimensões hierárquicas permitem que os dados sejam agregados desde os níveis inferiores até os superiores de resumos. Elas suportam navegação como detalhamento e certos tipos de cálculos como Share to Parent, Share within Ancestor e Rank within Parent. Também suportam muitos cálculos de séries de tempo como Year to Date. Esses tipos de cálculos são fáceis de definir no Analytic Workspace Manager (a ferramenta administrativa do Oracle OLAP 11g) e são efetuados com eficiência no cubo em tempo de execução.

O Oracle OLAP 11g pode reduzir significativamente os tempos de processamento de consultas para usuários de ferramentas de business intelligence (BI) baseadas em SQL como o Oracle Business Intelligence Suite Enterprise Edition e outras ferramentas de terceiros. O Oracle OLAP 11g também facilita a incorporação de cálculos em um cubo OLAP. Todos os recursos do Oracle OLAP 11g são fornecidos no Banco de Dados Oracle, o que permite o controle centralizado dos dados, das regras de negócios e da segurança.

Criando visualizações materializadas organizadas por cubo para o gerenciamento de resumos

As visualizações materializadas organizadas por cubo são um novo recurso do Oracle OLAP 11g que permite que os cubos sejam usados como uma solução de gerenciamento de resumos, em geral substituindo as tabelas de resumos relacionais e as visualizações materializadas baseadas em tabela. Nesse contexto, um aplicativo consulta a tabela de fatos usando funções de agregação (como SUM) e uma cláusula GROUP BY, e o recurso de reescrita de consulta do banco de dados redireciona automaticamente a consulta para a visualização materializada organizada por cubo. O cubo gerencia os dados de resumo de maneira transparente para o aplicativo que faz a consulta, e os usuários se beneficiam do melhor desempenho das consultas.

Para criar uma visualização materializada organizada por cubo, os DBAs criam um cubo com as tabelas de fatos e de dimensões que os aplicativos consultam no momento. Depois, habilitam no cubo a atualização de visualizações materializadas e a reescrita de consultas. A visualização materializada pode ser criada através de uma chamada de API ou, o que é mais comum, através de algumas opções no Analytic Workspace Manager, conforme mostra a Figura 1. O cubo faz auto-indexação e gerencia todo o espaço agregado.

Crie o cubo de amostra GLOBAL. Para criar e usar o cubo OLAP de amostra GLOBAL e testar os recursos do Oracle OLAP 11g que este artigo descreve, instale e configure os produtos, o esquema e o workspace da seguinte maneira :

1. Instale o servidor e cliente do Banco de Dados Oracle 11g. Observe que o servidor e o cliente têm downloads separados, ambos disponíveis na Oracle Technology Network em otn.oracle.com. Nessa página, selecione Downloads -> Database (Banco de dados) e clique no link See All (Ver tudo) próximo ao nome do sistema operacional da seção de download do Banco de Dados Oracle 11g. Ao instalar o servidor do banco de dados, você pode usar as opções padrão. Ao instalar o cliente, verifique se selecionou a opção Administrator (Administrador) para instalar o conjunto estendido de ferramentas, que inclui o Analytic Workspace Manager .

2. Baixe o esquema GLOBAL (global_11g_schema.zip) em otn.oracle.com/products/bi/olap . Nessa página, clique no link 11.1 próximo à seleção Global Schema (Esquema global) da seção Downloads dessa página. Descompacte o conteúdo do arquivo em um diretório .

3. Baixe o arquivo otn.oracle.com/oramag/oracle/08-may/o38olap.zip , e descompacte o conteúdo, o arquivo global_oramag.xml, em um diretório.

4. Siga as instruções especificadas no arquivo de documentação global_11g_readme.html, fornecido no arquivo compactado do esquema GLOBAL baixado na etapa 2. Essas instruções permitem criar o esquema GLOBAL com as tabelas necessárias para criar um workspace analítico.

5. Inicie o Analytic Workspace Manager. No menu Iniciar do Windows, localize-o em {Home do cliente Oracle 11g} -> Integrated Management Tools -> Oracle OLAP Analytic Workspace Manager and Worksheet .

6. Crie uma conexão com sua instância clicando com o botão direito do mouse em Databases (Bancos de dados) e selecionando Add Database to tree (Adicionar bancos de dados à árvore). . . . Insira uma descrição para a conexão e a string de conexão apropriada para sua instância (como localhost:1521:orcl11). Clique em Create (Criar).

7. Expanda a árvore Database (Banco de dados) até que a nova conexão apareça e clique em + (sinal de mais) ao lado da conexão para se conectar à instância. Digite GLOBAL para o nome de usuário, além da senha atribuída na etapa 4.

8. Expanda a árvore Schemas (Esquemas) até ver Analytic Workspaces (Workspaces analíticos). Clique com o botão direito do mouse em Analytic Workspaces e selecione Create Analytic Workspace from Template (Criar workspace analítico a partir do modelo). . .

9. Navegue pelo seletor de arquivos para localizar o arquivo de modelo global_oramag.xml baixado na etapa 3. Selecione-o e clique em Create (Criar). Aguarde alguns minutos enquanto ele cria o workspace analítico .

10. Clique com o botão direito do mouse no novo workspace analítico GLOBAL e selecione Maintain Analytic Workspace GLOBAL (Manter workspace analítico GLOBAL).

11. Clique >> (Add All) (Adicionar tudo) e clique em Finish (Concluir). Esta etapa carrega os dados no cubo e os agrega conforme as configurações especificadas no arquivo de modelo. Ela pode levar alguns minutos. Depois de concluir, clique em Close (Fechar) para fechar o log de compilação.

Execute a consulta OLAP. Com o cubo GLOBAL carregado e os dados agregados, agora digite -

  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;  

- usando o Oracle SQL Developer (ou outra ferramenta SQL de sua preferência). Essa típica consulta OLAP resume os dados na tabela de fatos UNITS_FACT por ano, classe e região somente para as linhas que têm um valor AMER para REGION_ID.

Se não houvesse tabelas de resumos, essa consulta teria de ser filtrada pela tabela (de dimensão) CUSTOMER_DIM para localizar todas as linhas com REGION_ID='AMER' e por fim as linhas na tabela de fatos com os valores SHIP_TO_ID. Por fim, a consulta teria de resumir a coluna SALES na tabela de fatos, agrupando os dados por ano, classe e região.

Com o Oracle OLAP 11g, usando as visualizações materializadas, esses totais ficam acessíveis imediatamente. Ele simplesmente busca as linhas de dados resumidos e as retorna para o processador SQL. O resultado é uma resposta bem mais rápida à consulta, com um sistema de gerenciamento de resumos simples e fácil de manter.

Se você executar um plano de explicação na consulta, verá que o otimizador Oracle usa a operação CUBE_SCAN, em vez da operação bem menos eficiente HASH JOIN, que é necessária quando não há uma visualização materializada. Para ver a diferença, execute os comandos

  alter materialized view cb$units_cube disable query rewrite  

and

  alter materialized view cb$units_cube enable query rewrite  

e veja os planos de explicação resultantes em cada caso. Você também verá uma enorme diferença em performance. Nos meus testes na pequena tabela de fatos GLOBAL (300.000 linhas de nível inferior), constatei que o recurso de reescrita de consultas habilitado (usando o cubo) possibilitou resultados de duas a cinco vezes mais rápidos do que sem o cubo. Você encontrará diferenças mais acentuadas em performance ao usar cubos de tamanho maior e mais realista.

O sistema de gerenciamento de resumos também é fácil de manter. Ele tem uma única visualização materializada, em vez de uma visualização materializada separada para cada combinação de níveis de cada dimensão. Você pode controlar a extensão na qual os dados são pré-calculados, mas o cubo sempre se apresenta como totalmente resolvido para cada combinação dos níveis das dimensões envolvidas. Um log de visualização materializada acompanha as linhas que se alteram na tabela base, além de controlar quanto do cubo é atualizado e agregado de forma incremental e quando isso ocorre.

Cálculos adicionais expostos através de visualizações automáticas

Você pode usar os cubos no Oracle OLAP 11g como uma solução de gerenciamento de resumos, mas o Oracle OLAP 11g realmente demonstra sua máxima capacidade quando é usado para efetuar cálculos. O Oracle OLAP pode efetuar muitos cálculos talvez difíceis de expressar em SQL e demorados de efetuar com o mecanismo relacional padrão.

Comparativos entre anos e de participações são ótimos exemplos de cálculos efetuados com mais eficácia com o Oracle OLAP. Depois de definidas no workspace analítico como medidas calculadas, essas medidas são expostas como colunas adicionais em uma visualização criada automaticamente para um cubo. Você pode consultá-las assim como consultaria qualquer medida base a partir da visualização de cubo .

Ao trabalhar com visualizações de cubo, não se esqueça de que elas contêm dados pré-agregados em vários níveis. Conseqüentemente, não é necessário incluir nenhuma função de agregação como SUM e em geral as cláusulas GROUP BY não são necessárias também. Entretanto, você também precisa especificar o nível dos dados que deseja selecionar através da cláusula WHERE apropriada.

Digite a consulta em Listing 1 (Listagem 1) no Oracle SQL Developer (ou em sua ferramenta SQL de preferência) para analisar o faturamento trimestral e ver o comparativo entre anos dos mesmos períodos. A consulta também mostra como retornar vários níveis da dimensão de tempo em uma consulta.

Listagem 1 do código: Consulta de vendas trimestrais e de comparação

  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;  

A execução da consulta no Oracle SQL Developer retorna os resultados mostrados na Figura 2. Observe como as linhas são retornadas para os anos, os trimestres e os três meses especificados — todos em uma única visualização.

Este exemplo mostra somente os cálculos mais básicos que o Oracle OLAP 11g pode efetuar. Um novo assistente de medidas calculadas no Analytic Workspace Manager permite calcular participações, índices, classificações, agregados móveis, agregados cumulativos e expressões definidas pelo usuário. Você pode até calcular modelos, previsões e regressões.

Atualizando cubos; usando visualizações materializadas

Como as tabelas de fatos adquirem novos dados, o Banco de Dados Oracle precisa manter os cubos e recalcular os totais. Você pode simplificar esse procedimento aproveitando o recurso de atualização de visualização materializada no Banco de Dados Oracle. Marcando a caixa de seleção Materialized View Refresh of the Cube (Atualização de visualização materializada do cubo – mostrada na Figura 1), você pode especificar a atualização de um workspace analítico para que seja a mesma de qualquer outra visualização materializada. Por exemplo, para atualizar o workspace analítico GLOBAL, use o comando a seguir :

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

Esse comando recarrega o workspace analítico da tabela de fatos e recalcula todos os agregados que precisam de atualização. Os mecanismos e a sintaxe são exatamente os mesmos das visualizações materializadas não-cubo, com métodos para controlar aspectos como ineficiência e programações. Os logs de visualização materializada identificam quais partes do cubo precisam ser recarregadas. O Oracle OLAP usa essas informações para recarregar e recalcular as partes apropriadas do cubo.

Agregação

O Oracle OLAP sempre retorna os dados totalmente resolvidos, mas você tem controle sobre a extensão na qual os dados são pré-agregados. No Oracle OLAP 10g, é possível especificar quais níveis do cubo serão pré-agregados. Esse recurso ainda é suportado no Oracle OLAP 11g, mas agora também é possível especificar a porcentagem do cubo que você deseja pré-agregar. O Oracle OLAP determinará quais áreas do cubo serão pré-agregadas e quais serão agregadas na hora. O Oracle OLAP determina como equilibrar a performance do tempo de execução com a performance do carregamento e o tempo necessário para calcular alguns agregados. A Figura 3 mostra uma caixa de diálogo de criação de cubos que especifica uma pré-agregação de 20%.

Apresentando os dados

Como nas versões anteriores do Oracle OLAP, você pode acessar os cubos do Workspace Analítico através da API OLAP Java. Entretanto, a Oracle posicionou o SQL como linguagem de preferência para acessar dados nos cubos Oracle OLAP. Com as visualizações geradas automaticamente que retornam dados de cubo através de SQL padrão, qualquer ambiente de aplicativo com uma interface SQL pode acessar dados dos cubos. Conseqüentemente, os dados do Oracle OLAP podem ser acessados por aplicativos Oracle como Oracle Business Intelligence Suite Enterprise Edition Plus, Oracle Discoverer e Oracle Application Express. Além disso, qualquer outra ferramenta de desenvolvimento de aplicativos que use SQL também pode acessar os dados do Oracle OLAP. A título de exemplo, a Figura 4 mostra um relatório do Oracle Business Intelligence Suite Enterprise Edition executado com base nos mesmos dados de cubo do Oracle OLAP demonstrados neste artigo. O Oracle OLAP fornece todos os totais e efetua os vários cálculos exibidos.

Outros novos recursos Outros novos recursos do Oracle OLAP 11g facilitam trabalhar com workspaces analíticos e integrar o OLAP ainda mais ao mecanismo do banco de dados. Há novos conselheiros de armazenamento e particionamento que simplificam o processo de criar cubos para garantir o armazenamento e manutenção ideais. Além disso, novas políticas de segurança de dados facilitam a concessão de acesso a diferentes partes dos cubos a vários usuários. Uma discussão detalhada desses recursos vai além do escopo deste artigo. Para obter mais informações, consulte Oracle OLAP 11g Release 1 (11.1) User's Guide .

Conclusão

Com os recursos aprimorados do Oracle OLAP 11g, fica muito mais fácil incorporar o OLAP à estrutura de um data warehouse. Aprimoramentos importantes como visualizações materializadas, criação automática de visualizações e assistentes de agregação tornam o Oracle OLAP uma escolha atraente para acelerar o desempenho das consultas.

Dan Vlamis( dvlamis@vlamis.com ) é presidente da Vlamis Software Solutions, Inc. Trabalha com produtos de business intelligence da Oracle desde 1986. Faz apresentações regularmente em importantes conferências da Oracle e é um renomado especialista em Oracle OLAP e business intelligence Oracle.