Projecto Final de Licenciatura Engenharia Informática - Computadores e Sistemas. elaborado por: Filipe Manuel Marques Pinto Pinheiro

O

OL

LA

AP

P

(

(O

O

N

N

L

L

I

I

N

N

E

E

A

A

N

N

A

A

L

L

Y

Y

T

T

I

I

C

C

A

A

L

L

P

P

R

R

O

O

C

C

E

E

S

S

S

S

I

I

N

N

G

G

)

)

Projecto Final de Licenciatura

Engenharia Informática - Computadores e Sistemas

elaborado por:

Filipe Manuel Marques Pinto Pinheiro

orientado por:

Engº Paulo Alexandre Duarte Ferreira

Instituto Politécnico do Porto

Instituto Superior de Engenharia do Porto Departamento de Engenharia Informática

Í

Í

N

N

D

D

I

I

C

C

E

E

3 CONCEITO DE DATA WAREHOUSE ... 13

4 PORQUÊ CONSTRUIR UM DATA WAREHOUSE ? ... 14

5 CRIAÇÃO DE UM DATA WAREHOUSE... 15

6 CONSTRUÇÃO DE UM SISTEMA DE SUPORTE À DECISÃO ... 16

6.1 OBJECTIVOS DE UM SISTEMA DE SUPORTE À DECISÃO... 17

6.2 PLANEAMENTO DE UM SISTEMA DE SUPORTE À DECISÃO... 18

OLAP - ONLINE ANALYTICAL PROCESSING ... 19

1 INTRODUÇÃO ... 20

2 PERSPECTIVA HISTÓRICA ... 21

3 DIFERENÇAS ENTRE SISTEMAS OLAP E OLTP ... 22

3.1 SISTEMAS OLTP ... 22

3.2 SISTEMAS OLAP ... 22

4 TRANSFORMAÇÃO DE DADOS OLTP EM DADOS OLAP ... 23

4.1 JUNÇÃO DE DADOS... 23

4.2 ELIMINAÇÃO DE DADOS... 24

4.3 AGREGAÇÃO DE DADOS... 24

4.4 ORGANIZAÇÃO DE DADOS EM CUBOS... 24

5 ETAPAS PARA TRANSFORMAÇÃO DOS DADOS... 25

6 MANUTENÇÃO DE DADOS OLAP ... 26

6.1 MUDANÇAS NO DATA WAREHOUSE... 27

6.2 ADIÇÃO DE DADOS... 27

6.3 ALTERAÇÃO DE DADOS... 28

6.4 MUDANÇA NA ESTRUTURA... 29

6.5 SINCRONIZAÇÃO DE DADOS ENTRE O DATA WAREHOUSE E O OLAP... 29

7 ESTRUTURA DE DADOS NORMALIZADA VS. NÃO NORMALIZADA ... 30

8 ANÁLISE DE DADOS NUM SISTEMA RELACIONAL NORMALIZADO ... 30

9.2 INFORMATIONAL TABLES... 33

9.3 ESQUEMAS DE ESTRUTURAÇÃO DE DADOS... 35

9.3.1 Esquema Star...35

9.3.2 Esquema Snowflake...37

9.3.3 Principais diferenças...38

9.4 DIMENSÕES ASSOCIADAS ÀS INFORMATIONAL TABLES... 38

9.4.1 Dimensões de estrutura...39

9.4.2 Dimensões de informação...41

9.4.3 Informational tables construídas a partir de dimensões de partição ...41

9.4.4 Dimensões de categoria ...42

10 CUBOS ... 42

10.1 AGREGAÇÕES... 46

10.1.1 Explosão de dados ...49

10.1.2 Relação entre a granularidade e a explosão dos dados...49

10.2 ROLAP,MOLAP E HOLAP ... 50

10.2.1 ROLAP ...50 10.2.2 MOLAP ...51 10.2.3 MOLAP vs. ROLAP...51 10.2.4 HOLAP ...53 11 PARTIÇÕES ... 54 11.1 ESTRUTURA... 55 12 CUBOS VIRTUAIS ... 56 12.1 ROLES... 59

DTS – DATA TRANSFORMATION SERVICES ... 60

1 INTRODUÇÃO ... 61 2 FUNCIONALIDADES DO DTS... 62 2.1 EXTRACÇÃO... 62 2.2 TRANSFORMAÇÃO... 62 2.3 CARREGAMENTO... 63 3 PACKAGES ... 64 3.1 CONEXÃO... 65 3.2 TAREFA... 66 3.3 PASSO... 67 3.3.1 Constantes de precedência...68

3.3.2 Uso de múltiplas constantes de precedência...69

MDX – MULTIDIMENSIONAL EXTENSIONS ... 71

1 INTRODUÇÃO ... 72

2 UTILIZAÇÃO DE MDX NUM DATA WAREHOUSE ... 72

2.1 DESENHO DO DATA WAREHOUSE... 72

3 MDX E OS SERVIÇOS OLAP ... 73

4 INSTRUÇÕES MDX... 73

4.1.1 Números ...73

4.1.2 Strings...74

4.1.3 Membros...74

4.1.4 Tuplos...74

4.1.5 Conjuntos...74

5 ESTRUTURA BÁSICA DE UMA INSTRUÇÃO MDX ... 75

6 EXEMPLOS DE INSTRUCÇÕES ... 75

EXEMPLO PRÁTICO ... 79

CONCLUSÃO... 87

BIBLIOGRAFIA ... 89

Í

Í

N

N

D

D

I

I

C

C

E

E

D

D

E

E

F

F

I

I

G

G

U

U

R

R

A

A

S

S

Figura 1 - Sistema de suporte à decisão ... 16

Figura 2 - Data Warehousing e OLAP ... 20

Figura 3 - Etapas de transformação e componentes de um Data Warehouse ... 25

Figura 4 - Esquema star ... 35

Figura 5 - Representação de uma dimensão ... 36

Figura 6 - Informational table de um esquema star... 36

Figura 7 - Esquema snowflake ... 37

Figura 8 - Modelo ER de uma dimensão geográfica ... 40

Figura 9 - Hierarquia relacional dos dados... 40

Figura 10 - Exemplo de um cubo... 45

Figura 11 - Representação dos valores numa hierarquia ... 46

Figura 12 - Opções de agregação com performance=50% ... 47

Figura 13 - Opções de agregação com performance=100% ... 48

Figura 14 - Opções de agregação com espaço de armazenamento máximo=300 Mb ... 48

Figura 15 - Esquema de armazenamento ROLAP... 50

Figura 16 - Esquema de armazenamento MOLAP ... 51

Figura 17 - Esquema de armazenamento HOLAP... 53

Figura 18 - Criação de um cubo virtual... 57

Figura 19 - Solução construída com base em cubos virtuais... 58

Figura 20 - Transferência de dados para um Data Warehouse ... 61

Figura 21 - Objectos de um package... 65

Figura 22 - Precedências de execução ... 69

Figura 23 - Múltiplas constantes de precedência ... 70

Figura 24 - Modelo de dados em Access ... 80

Figura 25 - Query Vendas ... 81

Figura 26 - Query Zonas... 81

Figura 27 - Query Produtos_Vendidos ... 81

Figura 28 - Novo modelo de dados em SQL ... 82

Figura 29 - Estrutura do Cubo ... 83

Figura 30 - MDX - Total de vendas por cliente... 83

Figura 31 - Vista resultante do Exemplo 1 ... 84

Figura 32 - MDX - Total de vendas por zona e produto ... 84

Figura 33 - Vista resultante do Exemplo 2 ... 84

Figura 34 - MDX - Total de vendas por zona e produto, segundo um vendedor ... 85

Figura 35 - Vista resultante do Exemplo 3 ... 85

Figura 36 - MDX - Criação de um calculated member ... 86

A

Desejo agradecer ao Engº Paulo Alexandre Duarte Ferreira por todo o apoio prestado, quer sob a forma de comentários a este trabalho através da leitura cuidada de todas as suas versões preliminares, ou através da indicação de bibliografia útil para o desenvolvimento deste trabalho.

Quero também agradecer à Vânia por todo o apoio que me deu e paciência demonstrada ao longo destes meses, sem a qual eu não conseguiria certamente levar a cabo este trabalho.

Por fim, e como não podia deixar de ser, desejo agradecer aos meus pais todo o apoio que me deram não só nesta fase final, como também ao longo de todos estes anos que fui estudante do Departamento de Engenharia Informática do Instituto Superior de Engenharia do Porto.

I

A tecnologia OLAP é inovadora, não estando ainda generalizada nas organizações que trabalham com grandes quantidades de informação relacionada com a sua área de negócios. Um sistema OLAP é uma ferramenta que auxilia as organizações no seu processo de gestão da informação disponível, por forma a garantir o seu sucesso e prevenir potenciais falhas.

Esta tecnologia está associada ao conceito de Data Warehouse que será abordado neste relatório, assim como os conceitos mais importantes relacionados.

Associada a esta tecnologia existem outros conceitos que convém explicar, dado que auxiliam o processo de transformação e análise da informação pretendida, de onde se destacam:

• DTS – Data Transformation Services • MDX – Multidimensional Extensions

Para melhor compreender como e onde esta tecnologia pode ser utilizada, serão apresentados alguns exemplos práticos dos vários conceitos abordados. No fim será apresentado um pequeno exemplo prático que engloba todos os exemplo focados anteriormente, e que pretende demonstrar o processo de criação de um cubo, por forma a podermos analisar os dados de um sistema OLAP.

D

1 I

Nos dias que correm, a gestão da informação nas organizações constitui um dos aspectos mais relevantes para a tomada de decisões que influenciam o seu desempenho a médio e longo prazo. Para alcançar uma gestão eficiente dessa informação, é necessário organizá-la e estruturá-la de forma a que seja precisa e consistente para permitir uma análise eficiente ao longo do tempo.

Na maior parte dos casos, a informação existente não está acessível, devido à falta de meios técnicos para o efeito, ou recursos humanos qualificados para o fazer, o que torna o processo de gestão lento e pouco eficiente. Quando a informação tem origem em fontes de dados diferentes (Aplicações externas, Bases de dados, ficheiros de texto, etc.), este processo torna-se ainda mais complicado. As decisões tomadas com base em informação insuficiente, podem muitas vezes ser prejudiciais para o sucesso das organizações face à pressão constante em mercados muito competitivos e em constante evolução.

O sistema ideal deveria permitir o acesso fácil à informação por parte de utilizadores não especializados, em qualquer período de tempo. Para isso, é necessário organizar e estruturar a informação, recorrendo a tecnologias de suporte à decisão.

Por vezes, o problema não reside na pouca informação existente, mas sim no seu excesso, exigindo uma correcta filtragem do que é essencial.

Uma solução para estas situações consiste na criação de um Data Warehouse com a informação existente e a disponibilização de soluções para acesso rápido e simples como forma de análise e suporte à decisão.

2 F

Para garantir o sucesso de uma organização, é necessário ter a informação correcta no momento certo de forma a que seja possível tomar decisões acertadas e implementá-las na altura devida.

As constantes trocas de informação recorrendo a variadas tecnologias, sistemas operativos e diferentes tipos de armazenamento de dados, exigem um formato uniforme e consistente, o que requer um planeamento cuidado e um conjunto de aplicações e infra-estruturas de forma a obter uma performance elevada.

Existem dois tipos de informação essenciais para o suporte à decisão: • Informação Operacional;

• Informação Analítica.

2.1 Informação Operacional

A informação operacional é constantemente actualizada e modificada, ou seja, é dinâmica. Um exemplo disso, consiste na informação de entrada de encomendas numa base de dados. A informação operacional representa a informação corrente numa determinada altura como por exemplo, o estado de uma encomenda pendente, os níveis do stock, etc. Este tipo de informação diz-nos o estado corrente de algo, e pode ser alterada a qualquer momento.

2.2 Informação Analítica

A informação analítica, por outro lado, consiste numa informação histórica e normalmente permanece inalterada a partir de uma determinada altura, ou seja é estática. Esta informação deve apenas ser alterada se existir um erro na sua forma original. Por exemplo, ao fim de um certo tempo, uma venda torna-se final e não é reversível. Nesta altura, esta informação torna-se estática e pode ser transferida da fonte de dados dinâmica para uma fonte de dados estática.

A informação analítica é utilizada para uma verificação periódica, como por exemplo, para verificar o total de vendas em Janeiro, a quantidade de um produto vendido num determinado dia, ou a variação dos salários dos funcionários nos últimos seis meses.

Geralmente, a sua construção é efectuada a partir de informação operacional e é utilizada para efectuar uma análise exaustiva sobre a informação de uma organização num período de tempo. Esta informação deve ser precisa, acessível, e apresentada numa estrutura flexível.

A informação analítica nem sempre vem do interior de uma empresa, podendo também ser oriunda de fontes externas e em grandes quantidades, e pode ser disponibilizada, por exemplo, através da Internet a um preço estabelecido.

Num Data Warehouse, a informação passa por um processo de verificação da integridade dos dados, é transformada num formato único e armazenada num servidor OLAP (Online Analytical Processing) para fácil acesso.

3 C

D

W

As ferramentas OLAP efectuam análises complexas à informação analítica, recorrendo a uma estrutura de dados especial denominada Estrutura Multidimensional. Estas estruturas são armazenadas numa base de dados específica chamada Data Warehouse. Um Data Warehouse é um armazém de dados que contém a informação recolhida durante a actividade de um negócio num determinado período de tempo. Um Data Warehouse construído correctamente disponibiliza informação histórica consistente para toda a organização.

As organizações recolhem informação no decurso normal da sua actividade. A finalidade de um Data Warehouse é de consolidar e organizar os dados de forma a que possam ser analisados e usados para auxiliar a tomada de decisões de negócio. Em muitos casos, o Data Warehouse contém a história viva de uma organização.

Um Data Warehouse, normalmente contém dados históricos, recolhidos de uma forma variada, desde sistemas OLAP, ficheiros de texto, folhas de cálculo, etc. O Data Warehouse combina estes dados, verifica a sua consistência e precisão, e organiza-os de forma a tornar os queries mais eficientes e fáceis de executar.

Algumas definições de Data Warehouse incluem vários elementos tais como: • Área de preparação de dados;

• Processos de verificação;

• Base de dados que contém os dados do Data Warehouse;

• Ferramentas que organizam e apresentam os dados às aplicações cliente.

Outras definições restringem o Data Warehouse a uma base de dados. Em grandes aplicações Data Warehouse, os dados são normalmente segmentados em componentes específicos, denominados Data Marts. Algumas definições consideram que os Data Marts fazem parte do Data Warehouse; outras, consideram-nos como entidades separadas.

É possível verificar que, depois de construir um Data Warehouse, muitas pessoas na organização apenas acedem a determinadas porções da informação. Desta forma, e estando o Data Warehouse segmentado, não é necessário efectuar inquéritos a todo o armazém, quando se pretende apenas uma parte. Neste caso, os inquéritos são apenas dirigidos aos Data Marts, acelerando os tempos de resposta.

4 P

D

W

?

Um Data Warehouse armazena dados estáveis e válidos. Comparando um Data Warehouse com uma base de dados transaccional obtemos as seguintes diferenças:

• Uma base de dados transaccional auxilia a execução de tarefas e actividades, enquanto que um Data Warehouse ajuda a tomada de decisões. Por exemplo, uma base de dados transaccional pode mostrar que lugares estão disponíveis num voo de modo a que um agente de viagens possa efectuar reservas. Um Data Warehouse, por outro lado, pode mostrar o padrão histórico de lugares vazios por voo de modo a que o gerente da companhia aérea possa decidir se deve ajustar os horários dos voos no futuro;

• Uma base de dados transaccional é volátil, ou seja, a informação é modificada constantemente conforme as ordens que são dadas ou canceladas, novos produtos são construídos ou eliminados, ou são efectuadas novas reservas. Uma Data Warehouse é estável, ou seja, a sua informação é actualizada periodicamente (anualmente, mensalmente, semanalmente, etc.). O ideal seria que as actualizações apenas acrescentassem novos valores desse período, sem alterar valores previamente armazenados;

• Uma base de dados transaccional centraliza-se nos detalhes: um agente de viagens ao efectuar uma reserva num voo não necessita de saber a média de lugares vazios. Um Data Warehouse centraliza-se em agregações de alto nível: o gerente ao actualizar os horários dos voos não necessita de saber especificamente quais os lugares vazios, mas sim, ter uma visualização geral da situação. Isto implica que os valores chave num Data Warehouse devem ser numéricos e devem poder ser sumariados;

• Uma base de dados transaccional providencia toda a informação que é posteriormente armazenada num Data Warehouse.

5 C

D

W

Um Data Warehouse utiliza os sistemas OLTP para recolher os dados das transacções diárias do negócio e existem processos de transformação destes dados, que os preparam para um formato estabelecido no Data Warehouse. Finalmente, existem ferramentas que permitem analisar esta informação, como por exemplo uma ferramenta OLAP.

Um armazém de informação deste tipo é, normalmente, construído para permitir o suporte às decisões e análise online, possuindo as seguintes características:

• Contém dados históricos: um Data Warehouse representa dados históricos, ou seja, informação analítica de longos períodos de tempo que descrevem o sistema;

• Dados read-only: depois dos dados serem transferidos para um data Warehouse, não é possível alterá-los, a não ser que estivessem errados desde o início. Estes dados não são actualizados, alterados ou eliminados, porque representam informação histórica acerca do negócio. As únicas operações possíveis são a adição de novos dados e a realização de inquéritos.

Ralph Kimball, defensor da filosofia de Data Warehousing, define um Data Warehouse como sendo simplesmente “o local onde as pessoas podem aceder aos seus dados”. Ele aponta seis requerimentos fundamentais para um Data Warehouse:

1. O acesso aos dados deve ser rápido;

2. Os dados devem ser sólidos e consistentes: os dados são recolhidos de diferentes fontes, são tratados e tornados consistentes, eliminando redundâncias e utilizando normas estabelecidas para o seu formato;

3. Os utilizadores devem ter a capacidade de extrair e comparar dados; 4. Deve incluir ferramentas de pesquisa fáceis de utilizar;

5. Os dados devem ser fiáveis e precisos;

6. Para que se possa ter dados de qualidade num Data Warehouse, é necessário uma recolha de informação com qualidade.

Os serviços de análise (OLAP) desempenham um papel muito importante na estratégia de Data Warehousing de uma organização, mas não satisfazem todas as suas necessidades. De facto, o OLAP por si só, apenas satisfaz dois destes seis requerimentos: ajuda a aceder aos dados

de uma forma rápida (requerimento 1), e facilita a extracção e comparação dos dados (requerimento 3). O OLAP não providencia directamente ferramentas de pesquisa de informação (requerimento 4) e os três requerimentos restantes – que dizem respeito à transformação dos dados de um Data Warehouse tornando-os consistentes, fiáveis e precisos – devem ser executados antes de se utilizar esta tecnologia. Por outras palavras, o OLAP assume que já temos um Data Warehouse válido e funcional criado usando bases de dados relacionais e um SGBD como por exemplo o SQL Server.

O OLAP cria uma nova camada no cimo de um Data Warehouse relacional já existente, com o objectivo de tornar o acesso aos dados muito rápido e flexível (dois dos requerimentos de Kimball).

6 C

Figura 1 - Sistema de suporte à decisão

Quando falamos de um sistema de suporte à decisão, a base de dados analítica é apenas uma parte de um grande sistema. Este sistema inclui ferramentas para armazenar e gerir cubos multidimensionais (OLAP Server e OLAP Manager), ferramentas para apresentar os dados

Servidor BD Fontes de dados Ficheiro de texto Informação Operacional DTS Informação Analítica Data Warehouse Servidor OLAP Aplicação OLAP Aplicação

(Pivot Table Services, Excel, Visual Basic, etc.) e ferramentas para transformar os dados, se necessário (DTS).

6.1 Objectivos de um sistema de suporte à decisão

• Profundidade da informação: Um sistema de suporte à decisão deve permitir aos utilizadores uma visualização da informação desde os níveis mais altos aos mais baixos. Assim, o utilizador pode comparar informação de níveis mais elevados com níveis inferiores conforme as suas necessidades;

• Comparar informação: As comparações mencionadas no parágrafo anterior, constituem uma funcionalidade essencial num sistema de suporte à decisão: A capacidade de comparar conjuntos de informação. Por exemplo, uma das comparações mais comuns consiste no volume de vendas num determinado período comparada com outro período. Um bom sistema de suporte à decisão deve proporcionar facilidade na execução deste tipo de comparações;

• Informação útil: Ser capaz de extrair grandes quantidades de informação é inútil se esta não for precisa e aplicável aos problemas de negócio que necessitam de resolução. Para além disso, os sistemas devem ser construídos por forma a validar a informação suficiente que permita ao utilizador efectuar as decisões que beneficiem a organização;

• Disponibilidade da informação: Não é recomendável ter um sistema que demore dias ou semanas para mover a informação para a base de dados analítica, quando é necessário disponibilizá-la em algumas horas. Se a informação é correcta e pertinente, mas chega demasiado tarde à organização, torna-se inútil;

• Análise rápida: Para satisfazer as necessidades dos utilizadores, cada query deve ser efectuado rapidamente, de forma a que um utilizador possa efectuar vários queries num curto espaço de tempo;

• Informação acessível: A informação deve ser apresentada num linguagem perceptível no âmbito do negócio, e não numa linguagem específica de sistemas analíticos. O interface deve permitir aos utilizadores uma boa interacção através uma linguagem e termos que lhe sejam familiares.

Uma sistema construído desta forma providencia informação útil e detalhada para a maior parte dos processos de negócio de uma organização.

6.2 Planeamento de um sistema de suporte à decisão

Para construir um sistema de suporte à decisão, é necessário um planeamento cuidado e compreender as diferentes opções disponíveis.

Um dos assuntos mais difíceis consiste em integrar os dados de um número variável de fontes de dados. Alguma desta informação pode estar disponível num formato pouco usual e que requer uma manipulação intensiva para ser utilizável no Data Warehouse.

Através de uma análise cuidada da organização, um planeamento detalhado e uma análise de risco, é possível identificar estes problemas e criar planos para os resolver.

O

1 I

Apesar de algumas vezes serem usados em conjunto, os termos Data Warehousing e OLAP, aplicam-se a componentes diferentes de um sistema muitas vezes denominado de Sistema de Suporte à Decisão. Estes sistemas são constituídos por bases de dados e aplicações que providenciam as ferramentas de análise necessárias às tarefas de tomada de decisão nas organizações.

Um Data Warehouse é uma base de dados que contém informação que normalmente representa a área de negócio e a história de negócio de uma organização. Estes dados históricos são usados para análise e mais tarde irão auxiliar na tomada de decisões a muitos níveis, desde o planeamento estratégico, até à avaliação de performance da organização. A informação contida num Data Warehouse está organizada para suportar análise, em vez de processamento de transacções em tempo real, tal como acontece em sistemas OLTP.

A tecnologia OLAP possibilita que a informação de um Data Warehouse possa ser usada de uma forma eficiente para análise online, permitindo rápidos tempos de resposta a queries analíticos iterativos e personalizáveis. O modelo de dados multidimensional do OLAP em conjunto com as técnicas de agregação organizam e sumariam grandes quantidades de dados de forma a que possam ser avaliados rapidamente usando ferramentas de análise online e ferramentas gráficas.

A resposta a um query executado em dados históricos, geralmente leva a que sejam executados queries sucessivos enquanto decorre o processo de procura da resposta ou então enquanto se exploram todas as possibilidades de resposta. Os sistemas OLAP tornam possível a análise a este tipos de dados em tempo real, através da sua velocidade e flexibilidade.

Figura 2 - Data Warehousing e OLAP

Utilizadores Dados Operacionais ETL* Data Warehouse OLAP

Um sistema OLAP consiste numa ferramenta que ajuda as empresas no seu processo de gestão da informação disponível, por forma a garantir o seu sucesso e prevenir potenciais falhas. As seguintes afirmações descrevem um sistema OLAP:

• Os sistemas OLAP são ferramentas utilizadas para analisar a informação de um negócio;

• Um sistema OLAP pode ser utilizado por analistas, gestores ou executivos para aprofundarem o seu conhecimento acerca do funcionamento da empresa num determinado período de tempo, ou utilizadores externos que buscam uma fonte de informação fiável, flexível e de acesso rápido, disponibilizada por uma empresa; • Uma ferramenta OLAP é rápida, consistente e interactiva que permite uma

visualização alargada da informação pretendida.

2 P

H

Em 1980, E. F. Codd revelou o termo Online Transaction Processing (OLTP) e propôs doze critérios para definir uma base de dados OLTP. A sua terminologia e critérios tornaram-se mundialmente aceites como standard para bases de dados utilizadas para gerir operações do dia-a-dia (transacções) de uma organização. Em 1990 Codd apareceu com o termo Online Analytical Processing (OLAP) e propôs de novo doze critérios que definiam uma base de dados OLAP. Desta vez, os seus critérios não usufruíram de uma aceitação global, mas o termo OLAP subsistiu, parecendo perfeito para muitos como forma de descrever bases de dados destinadas a facilitar o suporte à decisão de uma organização.

Algumas pessoas usam o termo OLAP simplesmente como um acrónimo para Data Warehousing. Normalmente, contudo, o termo OLAP descreve ferramentas especializadas que tornam os dados de um Data Warehouse facilmente acessíveis.

3 D

OLAP

OLTP

3.1 Sistemas OLTP

Os dados num sistema OLTP estão organizados de forma a suportar, na sua maioria, transacções que são executadas rapidamente e acedem a pedaços de informação relativamente pequenos. Os sistemas OLTP são destinados e estão “afinados” para processar centenas ou milhares de transacções ao mesmo tempo.

Apesar dos sistemas OLTP serem melhores na tarefa de armazenar os dados necessários para suportar as operações diárias, os dados não estão organizados de forma a disponibilizar a informação necessária aos gestores para planear o trabalho da sua organização de uma forma rápida e simples. Os gestores necessitam de informação sumariada através da qual possam analisar factores que afectem a sua organização ou equipa; têm de encontrar os factores críticos para o sucesso e analisar a melhor forma de ajustar esses factores para aumentar o sucesso da organização.

3.2 Sistemas OLAP

Os sistemas OLAP têm a finalidade de executar queries para pesquisar informação necessária para descobrir factores críticos para o sucesso dentro de uma organização. Estes queries normalmente requerem grandes quantidades de dados.

Acontece que muitas organizações não possuem apenas um sistema OLTP que guarda toda a informação. Muitas das grandes organizações têm múltiplos sistemas OLTP, muitos dos quais foram desenvolvidos em alturas diferentes e usam diferentes tipos d software e/ou hardware. Em muitos casos, as nomenclaturas e códigos mudam de sistema para sistema. Os gestores que executam queries OLAP geralmente necessitam de referenciar a informação de vários destes sistemas OLTP.

Os dados OLAP estão organizados em cubos multidimensionais. Esta estrutura proporciona um aumento de performance aos queries, em relação aos dados organizados em tabelas relacionais. A unidade básica de um cubo multidimensional é denominada measure (unidade dos dados que estão a ser analisados). As measures estão organizadas em dimensões.

Os sistemas OLAP são muito diferentes dos sistemas OLTP (Online Transaction Processing).

As principais características de cada um são resumidas no esquema seguinte:

OLTP OLAP

Objectivo Entrada de dados em tempo real Leitura e análise de dados históricos

Actualização Sim Não

Edição Sim Não

Fonte dos dados Entrada de dados Dados históricos OLTP

Base de dados associada Operacional Analítica, pode ser operacional

Uma das principais diferenças entre os sistemas OLAP e OLTP é que os sistemas OLTP têm um conjunto bem definido de queries que o utilizador pode executar, enquanto que num sistema OLAP tal não acontece.

Num sistema OLTP, todos os queries que o sistema pode executar (listar todos os clientes, todos os fornecedores, etc.) são predefinidos. Com um sistema OLTP podemos optimizar os queries para obter o máximo de performance por parte do sistema.

Quando estamos perante um sistema OLAP, sabemos qual o tipo de informação o utilizador deseja analisar, mas não sabemos exactamente quais os queries que o utilizador irá efectuar. Assim, os queries são executados e optimizados pelo sistema no momento em que são requeridos, conforme as necessidades do utilizador.

4 T

OLTP

OLAP

É necessário transformar o dados OLTP em dados OLAP, de forma a que se proporcione uma performance aceitável em sistemas OLAP. Os processos necessários são apresentados de seguida.

4.1 Junção de dados

É necessário que seja possível juntar todos os dados relacionados de múltiplos sistemas OLTP num único sistema OLAP. O processo de junção deve resolver diferenças de codificação entre diferentes sistemas OLTP. Por exemplo, um sistema pode atribuir um ID a cada empregado, e outro sistema pode nem ter ID para cada empregado. O processo de junção deve ser capaz de identificar dados de empregados dos dois sistemas, talvez através da comparação de nomes e moradas nos dois sistemas. Este processo deve também ser capaz de converter dados armazenados usando diferentes tipos de dados em cada sistema para um único tipo de dados utilizado no sistema OLAP. É necessário também seleccionar quais as colunas de cada sistema

que não são relevantes para o sistema OLAP e então, excluir estas colunas do processo de junção de dados.

4.2 Eliminação de dados

O processo de junção de dados OLTP para um Data Warehouse dá-nos a possibilidade de remover dados irrelevantes ou redundantes. Podemo-nos deparar, por exemplo, com sistemas OLTP que dão nomes diferentes para um mesmo item; tal pode ser descoberto pelo processo de junção de dados. Podemos ainda encontrar outras inconsistências, tais como diferentes moradas para a mesma loja, empregado ou cliente. Estas inconsistências têm e devem ser eliminadas antes dos dados poderem ser transferidos para o Data Warehouse para serem usados pelos sistemas OLAP.

4.3 Agregação de dados

Os dados OLTP guardam todos os detalhes das transacções. Geralmente, os queries OLAP necessitam apenas de dados sumariados, ou dados agregados de alguma forma. Analisemos o query seguinte: cálculo dos totais de vendas mensais para cada produto durante o ano 2000; este query é executado muito mais rapidamente se a base de dados tiver apenas colunas com dados sumariados das vendas diárias de cada produto, do que se o query tiver de procurar em cada detalhe de transacção do ano 2000, para cada produto, e calcular daí o total de vendas.

O detalhe com que se agregam os dados no Data Warehouse depende de muitos factores, tais como a necessidade de performance elevada dos queries OLAP e o nível de granularidade necessário para a análise dos dados. Por exemplo, se agregarmos detalhes de vendas em sumários por dia em vez de sumários por hora, os queries serão executados mais rapidamente. No entanto, esta solução teria interesse se nunca precisássemos de analisar as vendas com base horária, caso contrário seria impossível alcançar este nível de granularidade, com base nessas agregações.

4.4 Organização de dados em cubos

Os dados relacionais OLTP estão organizados de uma forma que torna alguns processos de análise difíceis e muito morosos. Implica que quando os dados OLTP são movidos para um Data

Warehouse, devem ser reorganizados de forma a suportar eficientemente a análise e o suporte à decisão.

O processo de construção de um Data Warehouse envolve a reorganização dos dados OLTP armazenados em tabelas relacionais, para dados OLAP armazenados em Cubos Multidimensionais.

5 E

Normalmente, o processo que torna os dados disponíveis através de aplicações OLAP percorre as seguintes etapas:

1. Extracção de dados de fontes OLTP para uma área temporária;

2. Transformação dos dados num formato utilizável num sistema OLAP. Esta etapa envolve processos de eliminação e agregação de dados;

3. Carregar estes dados para um Data Warehouse ou Data Mart.

O processo de extracção dos dados de fontes OLTP e a sua transformação para os servidores do Data Warehouse é denominado por ETL (Extraction; Transformation; Loading), e é executado com períodos fixos.

Uma vez carregados os dados no Data Warehouse, é fundamental que o sistema OLAP possibilite o acesso aos dados para que se possa analisá-los. A figura seguinte representa as categorias dos componentes de um sistema OLAP que tornam estes serviços possíveis.

Figura 3 - Etapas de transformação e componentes de um Data Warehouse

Meta Dados Fontes de dados Armazém de dados operacionais Área temporárea de armazenamento Armazém de dados intermédio Data Warehouse Data Mart Servidores Data Warehouse Data Mart Ferramentas de Inquérito Relatórios Informação de Negócio Aplicações Analíticas Data Mining

• Fontes de dados: A informação contida em bases de dados OLTP ou outras fontes, tem que ser transformada em informação OLAP para poder ser transferida para o Data Warehouse ou para o Data Mart;

• Armazém de dados intermédio: Área de armazenamento de dados que processa, analisa e transforma os dados OLTP em dados utilizáveis pelo sistema OLAP;

• Servidores Data Warehouse: São os computadores que possuem bases de dados relacionais que contêm os dados para os Data Warehouses e Data Marts, e servidores que gerem os dados OLAP;

• Informação de negócio: O conjunto de ferramentas e aplicações que executam inquéritos aos dados OLAP e disponibilizam relatórios e informação às pessoas encarregues de tomar decisões no seio da organização;

• Meta Dados: Modela a organização em termos de dados e aplicações dentro dos diferentes componentes OLAP. Os meta dados descrevem objectos tais como tabelas em bases de dados OLTP, cubos em Data Warehouses e Data Marts, etc. Também regista quais as aplicações que referenciam as diferentes partes da informação.

6 M

OLAP

A finalidade dos sistemas OLAP é proporcionar um rápido acesso analítico aos dados de um Data Warehouse. Para conseguir isto, o OLAP cria cubos multidimensionais a partir de dados presentes nas tabelas de factos e dimensões (fact e dimension tables) de um Data Warehouse. A measures numéricas são também sumariadas em valores pré agregados durante a construção do cubo. Os cubos são armazenados em estruturas multidimensionais com a finalidade de facilitar um tempo de resposta a inquéritos muito rápido, combinando a informação pré agregada com dados “crus” da tabela de factos (fact table) para responder a uma variedade muito grande de inquéritos.

Os cubos podem conter dados sumariados, copiados ou lidos directamente do Data Warehouse. As mudanças na estrutura do Data Warehouse ou dos dados lá contidos podem afectar a integridade e a precisão dos cubos que foram criados a partir do Data Warehouse. Uma vez que o OLAP providencia acesso contínuo aos cubos, quando se efectuam mudanças no Data Warehouse é necessários que se esteja ciente das implicações que isso poderá ter nos cubos. Deverá também existir uma preocupação com a sincronização dos dados do Data Warehouse e dos cubos.

Os dados OLAP têm de ser actualizados assim que se verifique uma mudança nos dados do Data Warehouse. É necessário processar os cubos, dimensões e partições para incorporar os novos dados ou os dados alterados do Data Warehouse. O método para processamento de um objecto OLAP depende do próprio objecto e do tipo de operação efectuada no Data Warehouse, podendo ser de adição de dados, alteração de dados ou mudança na sua estrutura.

O OLAP em tempo real é uma característica que usa cubos em tempo real para sincronizar automaticamente os dados dos cubos com mudanças na base de dados relacional à qual está ligado. Este tipo de cubos pode ser usado em aplicações que necessitem de monitorizar e analisar dados em tempo real; não tendo como finalidade substituir os cubos tradicionais e aplicações, mas sim aumentar as capacidades do OLAP.

6.1 Mudanças no Data Warehouse

Os dados são normalmente adicionados periodicamente ao Data Warehouse de forma a que este possa reflectir a informação mais recente possível acerca das actividades de negócio da organização. As mudanças nos dados que já estão no Data Warehouse são menos frequentes e normalmente ocorrem só para se efectuarem correcções em erros posteriormente descobertos pela fonte através da qual foram transferidos, ou para reestruturar a informação devido a mudanças na estrutura da organização. As mudanças na estrutura do Data Warehouse são as menos frequentes.

6.2 Adição de dados

É comum adicionar novos dados ao Data Warehouse. A informação do cubo, que está disponível online às aplicações dos clientes, pode ser afectada quando são adicionados dados ao Data Warehouse, devido à interacção entre os dados e as partições do cubo. Podem-se gerir os

efeitos da adição de dados, através da criação de filtros de partições ou através da criação de estratégias de sincronização de dados entre o OLAP e o Data Warehouse.

6.3 Alteração de dados

As mudanças num Data Warehouse apenas para correcção de erros podem ser minimizadas se as operações de transformação, validação e eliminação dos dados forem executadas com o máximo de atenção. Outras mudanças nos dados existentes no Data Warehouse podem ser resultantes da alteração da estrutura da organização ou dos seus produtos. Por exemplo, a reorganização de produtos em diferentes categorias pode necessitar de mudanças significativas nos dados do Data Warehouse, assim como em relatório derivados do Data Warehouse. Em alguns casos, tais mudanças, podem obrigar a uma completo reestruturação dos cubos. Noutro casos, pode obrigar a reestruturar dimensões, e neste caso basta apenas processar de novo os cubos que usem tais dimensões para que tudo fique actualizado.

As alterações para correcção de erros em dados básicos, devem ser incorporadas na base de dados origem (normalmente uma base de dados de um sistema OLTP) e só depois podem migrar para o Data Warehouse de uma forma controlada. Muitas vezes é necessário aplicar mudanças à estrutura de algumas bases de dados OLTP, através do uso de uma transacção que exclui os dados incorrectos e introduz novos dados.

É mais fácil gerir o impacto dessas transacções para correcção de dados em dados OLAP. Os cubos podem incorporar novas transacções de dados para corrigir erros em valores. Contudo, as transacções que movem um membro de uma dimensão para outra, podem afectar os resultados das funções de agregação tal como a função Avg. Por exemplo, se o valor de uma venda é colocado a erro mas o registo não é eliminado, este registo será incluído na contagem de vendas e afectará o resultado dos cálculos. Isto também é verdade para bases de dados não OLAP.

Dependendo da estrutura de armazenamento do cubo, as alterações nos dados da tabela de factos (fact table) podem afectar a precisão dos queries a um cubo, até que o cubo seja de novo processado.

As hierarquias de dimensões podem ser afectadas por mudanças nos dados das tabelas de dimensões do Data Warehouse apesar do esquema da tabela permanecer inalterado. A hierarquia de dimensões é baseado em relações entre membros numa tabela de dimensões. Quando estas relações são alteradas (por exemplo, quando cidades são reorganizadas em diferentes regiões de vendas), a estrutura da dimensão tem que ser reconstruída.

A integridade referencial deve ser mantida quando são adicionados, alterados ou eliminados dados do Data Warehouse. A perda da integridade referencial pode resultar em erros durante o processamento do cubo, registos que são esquecidos ou informação OLAP imprecisa.

6.4 Mudança na estrutura

A estrutura dos cubos OLAP e as dimensões podem ser afectadas por alterações na estrutura do Data Warehouse tais como adição, eliminação ou alteração de tabelas ou de relações entre tabelas. Quando a estrutura altera, deve-se modificar a estrutura dos cubos e dimensões afectadas, redefinir as partições e agregações e processar completamente os cubos e dimensões modificadas.

6.5 Sincronização de dados entre o Data Warehouse e o OLAP

Os cubos válidos estão online e disponíveis para as aplicações dos clientes quando o servidor OLAP está a correr. Devido à interacção entre as partições do cubo OLAP e os dados do Data Warehouse, a estrutura do Data Warehouse deve incluir uma estratégia de sincronização para permitir a adição de dados, sem que fazer com que os cubos que estão online devolvam informação errada.

Uma estratégia para gerir as adições ao Data Warehouse e aos dados OLAP consiste em criar um sistema de actualizações baseada em processos batch. Neste tipo de estratégia, todos os registos da tabela de factos (fact table) do Data Warehouse incluem um numero batch.

Os dados adicionados a uma tabela de dimensões não afectam as dimensões públicas ou partilhadas de um cubo já existentes, até que as dimensões sejam processadas. Um número batch em cada registo de uma tabela de dimensões torna-se desnecessário, mas pode ser útil para garantir a integridade referencial.

As dimensões e os cubos ou partições, podem ser processados para incorporar novos dados, após a adição de dados batch à tabela de factos ou dimensões (fact ou dimension table). As dimensões partilhadas devem ser processadas antes dos cubos que as usam.

7 E

.

O modelo relacional de desenho é vocacionado para agrupar informação relacionada. É utilizado pelos sistemas OLTP e está provada a sua capacidade de produzir informação consistente.

A utilização de bases de dados relacionais permite a um sistema OLTP um funcionamento rápido e eficiente, e que mantém as regras de consistência e flexibilidade da informação. Isto acontece, mesmo com actualizações constantes no sistema. Apesar deste modelo funcionar muito bem num sistema OLTP, o mesmo nem sempre se consegue num sistema OLAP.

Como num sistema OLAP a informação deve ser estática e sofrer o mínimo de actualizações possíveis, não se verifica a necessidade de utilizar um modelo relacional, dado que este modelo é utilizado precisamente para tornar o processo de actualização mais eficiente e consistente. É possível utilizar um modelo relacional para estruturar a informação num sistema OLAP de uma forma eficiente, mas não existe essa necessidade.

De facto, uma estrutura não normalizada (por completo) aumenta a rapidez do processo de consulta da informação, uma vez que reduz o número de joins necessários para efectuar os inquéritos. Um sistema OLAP apoiado numa base de dados relacional possui uma vantagem: requer menos espaço para o armazenamento. Quando esta necessidade não existe, uma estrutura não normalizada torna o processamento mais rápido e acessível.

8 A

Se construirmos um sistema analítico de dados, movendo os dados OLTP para um conjunto separado de tabelas relacionais normalizadas, mas dentro da mesma base de dados operacional, para analisarmos os dados, temos de executar um conjunto de queries SQL que irão incidir sobre essas tabelas. Podemos então chegar a uma situação em que mesmo para executar uma tarefa simples, necessitamos de recorrer a queries relativamente complexos, que se tornam complicadas de analisar, ocupando também muito tempo de processador. Tudo isto faz com que a performance do sistema saia prejudicada, e quando tal acontece, todo o negócio baixa de performance, podendo afectar de uma forma grave todo a organização.

A performance pode ser aumentada se criarmos uma cópia dos dados num servidor completamente independente, ou através do uso de um componente separado que corre num segundo servidor, e que é responsável por efectuar todos os cálculos necessários. Através do uso

Mas, mesmo com o uso de um segundo servidor, continuamos a ter de executar stored procedures complicadas por cada tipo de análise de dados que o utilizador desejar. Isto significa que, para cada vista que o utilizador tenha dos dados, é necessário escrever queries longos, complicados e específicos. Infelizmente, esta situação não tem remédio, pois num sistema analítico, nunca se pode saber ao certo que tipo de informação é que o utilizador irá desejar, nem o próprio utilizador, muitas vezes sabe que tipo de informação irá necessitar, até ao momento em que ele próprio inicia a procura. Consequentemente, baseando-se apenas em stored procedures como forma de análise da informação, o utilizador tem apenas formas predefinidas de analisar e visualizar informação.

Toda esta situação pode ser melhorada através do uso de sistemas OLAP, que trabalham com estruturas de dados relacionais normalizadas. É da responsabilidade do sistema OLAP registar o pedido de informação por parte do utilizador, e consequentemente criar um query eficiente para retornar ao utilizador a informação requerida. Apesar de isto ser um pouco lento, irá permitir tipo de queries analíticos. A velocidade depende dos queries, do tipo de optimização que sofreram e de muitos outro factores. Contudo, o tempo de retorno dos resultados é perfeitamente imprevisível. Esta situação pode ser melhorada, se usarmos uma estrutura de base de dados relacional, multidimensional e não normalizada.

9 A

O principal objectivo dos sistemas OLAP é efectuar cálculos e modelar métricas de negócio importantes. Estas métricas são chamadas dimensões. Assim como em matemática e na ciência usamos dimensões de espaço e/ou tempo, dimensões de negócio podem também ser tratadas no tempo e no espaço.

No mundo do Data Warehousing, um valor numérico sumariável utilizado para monitorizar um negócio é chamado measure (medida). Quando necessitamos de informação numérica, a primeira questão é: Que measure queremos ver ? Alguns exemplos de measures são o volume de vendas de uma empresa, lucros resultantes das vendas, etc.

Quando se efectuam análises sobre sistemas OLAP, cada measure é calculada sobre as várias dimensões. Se a measure é “total de vendas” e as dimensões forem de tempo e geográficas, com tempo igual ao mês de Maio e a geografia igual a Porto, o sistema OLAP calcula o número de vezes que um item foi vendido em Maio na região do Porto. Estes sistemas analisam frequentemente measures sobre várias dimensões, e é por isto que são chamados de multidimensionais.

Nota

Por norma, quando analisamos informação colocamos na seguinte forma: Uma measure por dimensão1, dimensão2, ..., dimensão N.

Por exemplo, podemos inquirir o seguinte ao sistema OLAP: “Quero ver o total de vendas por região e departamento ao longo do tempo”. Neste caso, a measure é total de vendas, e as três dimensões são: departamento, região e tempo. Devemos, sempre que possível, induzir os utilizadores do sistema OLAP a colocarem as suas questões nesta forma específica.

Uma dimensão é uma métrica de negócio. A métrica actual pode ser representada de muitas formas diferentes. Por exemplo, podemos representar a dimensão tempo como horas, semanas, dias, trimestres ou anos. Um grupo de várias métricas relacionadas entre si formam uma dimensão. Assim sendo, podíamos criar uma dimensão temporal constituída por ano e mês e construir outra dimensão constituída por ano fiscal e trimestre. Podemos querer visualizar uma métrica de muitas formas diferentes e criar diferentes dimensões para cada representação de uma métrica.

Os membros de dimensões podem, muitas vezes, ser agrupados em diferentes níveis de uma métrica, estando todos relacionados uns com os outros. Estes diferentes níveis são chamados de hierarquias. As dimensões temporais podem ser representadas por mês e ano. Estas diferentes representações de tempo estão relacionadas entre si (um ano tem doze meses, um mês tem entre vinte e nove e trinta e um dias, etc.). A segunda dimensão temporal que consiste em ano fiscal e trimestres também é uma hierarquia. Uma dimensão geográfica pode ter as seguintes hierarquias: região, distrito e cidade. O OLAP pode percorrer estas hierarquias de forma a que o processo de retorno de informação seja o mais rápido possível. Por exemplo, o OLAP pode proporcionar uma vista das vendas de produtos no Porto durante o mês de Março. Podemos usar o OLAP para visualizar as vendas mensais de qualquer cidade do distrito do Porto através da selecção da cidade adequada.

Através do uso de dimensões, hierarquias e measures os sistemas OLAP podem executar uma análise através de períodos sequenciais de tempo, centrar-se em partes específicas de informação e criar, de uma forma rápida, uma nova vista que representa essa porção de informação analisada.

Quando se usa um sistema OLAP para analisar dados, pergunta-se muitas vezes “Qual a measure usar sobre esta dimensão ?”. Por exemplo, “Quantos pregos foram vendidos no mês de Julho, Agosto e Setembro na região Norte ?” Esta questão é multidimensional, pois para lhe dar uma resposta é necessário efectuar inquéritos a muitas dimensões.

Podemos criar uma vista multidimensional através do uso de estruturas de dados construídas a partir de tabelas que contêm measures e dimensões. Existem dois tipos de estruturação das dimensões e measures: os esquemas star e snowflake.

9.1 Fact Table

A tabela central de uma base de dados multidimensional é denominada por fact table. As suas linhas constituem os factos. Estas measures constituem as métricas de negócio da organização, que o utilizador necessita de calcular partindo de várias dimensões. As measures são observações do mercado em que a organização está inserida e são muito úteis quando são numéricas e/ou aditivas. Os factos aditivos, permitem ao OLAP somar centenas ou milhares de registos rapidamente.

Nas fact tables, em oposição às informational tables, não é suposto que a informação mude ao longo do tempo e, consequentemente, muitas fact tables atingem grandes dimensões (atingindo até milhões de registos), ocupando grandes espaços de armazenamento.

As measures não têm que ser aditivas, isto porque em alguns casos não faz sentido adicioná-las umas às outras, como por exemplo o número de artigos em stock. No entanto, já faz sentido calcular a média de artigos em stock. Assim sendo, podemos usar unidades em

stock como uma measure, mas fazemos uma média de valores em vez de uma soma. As

measures podem também ser baseadas em valores calculados, tal como o total de lucro.

Nota

O esquema star é constituído por pequenas dimensional tables e grandes fact tables. Quando são processados queries sobre os dados, o OLAP começa por seleccionar o registo correcto das

informational tables. Uma vez que estas tabelas são relativamente pequenas, estes queries são

rápidos e eficientes. Quando as linhas apropriadas são seleccionadas nas informational tables, é possível utilizar as suas chaves primárias para encontrar o registo correspondente na fact table. Apesar desta tabela ser grande, como as pesquisas são efectuadas sobre atributos chave este processo é eficiente.

9.2 Informational tables

As informational tables são tabelas mais pequenas que contêm dados que podem ser alterados ao longo do tempo. Por exemplo, a informational table que guarda os dados dos clientes de uma organização, é alterada se um determinado cliente alterar a sua morada, número

de telefone, etc. O número de linhas desta tabela é limitado ao número máximo de clientes. Assim sendo, o tamanho desta tabela e de todas as informational tables será muito pequeno em relação à fact table que contém, por exemplo, todas as vendas de 1999. Podemos então considerar que as informational tables são pequenas tabelas no mundo do Data Warehouse.

As informational tables incluem também campos chave. A chave primária das informational tables são normalmente incluídas como chaves estrangeiras da fact table. Se esta chave primária não estiver incluída na fact table, é denominada por dimensão degenerada (degenerate dimension). Neste caso, as chaves primárias são normalmente geradas pela base de dados analítica.

Nota

Podemos considerar as informational tables como sendo um grande “saco” que contém todas as diferentes representações da métrica de negócio da organização, como sendo o tempo, geografia ou o cliente. Para podermos construir as nossas dimensões temos que ir a esse “saco” e retirar as representações que iremos necessitar para as nossas dimensões. Para primeira dimensão poderíamos retirar ano, mês e dia. Para a segunda ano e semestre. Para a terceira ano e mês. Assim sendo, podemos construir um número muito variado de dimensões a partir dos membros das

informational tables.

Geralmente, as fact tables têm uma informational table de tempo relacionada. Esta tabela possui as seguintes características:

• Os atributos da tabela representam divisões por tempo e eventos; • As dimensões de tempo mais comuns restringem-se a dia, mês e ano;

• Períodos de tempo especiais, tais como dia de trabalho, fim de semana, férias, etc. também podem ser representados.

As informational tables podem ser construídas usando dois tipos de esquemas: • Star

9.3 Esquemas de estruturação de dados

9.3.1 Esquema Star

O nome star está relacionado com a forma da estrutura, ou seja, todas as tabelas estão directamente ligadas à fact table, formando uma estrela.

A figura seguinte representa um esquema star:

Figura 4 - Esquema star

As tabelas Cliente, Produto e Tempo apresentadas na figura 4, constituem informational tables cujos atributos representam as métricas de negócio essenciais à organização.

A tabela Vendas constitui a fact table que contém não só as measures, como também as chaves estrangeiras que a relacionam com as informational tables. Convém referir que as informational tables podem conter campos para além daqueles que representam as dimensões.

Quando queremos converter uma base de dados operacional, relacional e normalizada que obedeça ao esquema star, devemos executar as seguintes operações:

1. Determinar as measures e dimensões; 2. Criar as informational tables;

3. Criar a(s) fact table(s).

Cliente

Tempo

Produto Fact Table:

Criação de informational tables para um esquema Star

Num esquema star, toda a informação de uma dimensão é colocada numa única informational table. A informação contida nessa tabela inclui um único identificador e os atributos necessários para todas as dimensões que serão construídas a partir dela.

Vejamos o exemplo seguinte que representa a dimensão Produto:

Figura 5 - Representação de uma dimensão

Num esquema star, estes dados serão agregados numa única informational table não normalizada (tal como na figura 4):

Figura 6 - Informational table de um esquema star

Nesta tabela, o campo IDProduto identifica unicamente cada produto. Os atributos Marca e Categoria representam respectivamente os nomes da marca e categoria correspondentes a um produto.

Como esta tabela não está normalizada, ocupa mais espaço de armazenamento, no entanto, a velocidade de acesso é maior, uma vez que reduz o número de joins.

Para além do esquema star, existe um outro método de organizar as informational tables designado por esquema snowflake.

PRODUTO IDProduto Nome Preço Marca Categoria 1 N N 1 PRODUTO IDProduto Nome Preço IDMarca IDCategoria MARCA IDMarca Nome CATEGORIA IDCategoria Nome

9.3.2 Esquema Snowflake

A figura 7 representa uma estrutura do tipo snowflake, onde múltiplas tabelas definem uma ou mais dimensões da fact table. Isto significa que um esquema deste género constitui uma extensão do esquema star, em que cada tabela extra de uma dimensão está ligada não directamente à fact table, mas sim a outras tabelas da dimensão.

Figura 7 - Esquema snowflake

Criação de informational tables para um esquema Snowflake

Neste tipo de esquema, as informational tables são optimizadas até à terceira forma normal. O exemplo citado anteriormente não sofreria alterações à sua estrutura:

Quando as tabelas são colocadas num diagrama, parecem-se com um floco de neve (daí o nome deste esquema). Para aceder à informação, são necessários vários joins. Estes joins têm uma taxa de ocupação de processamento considerável, mas o espaço de armazenamento é menor.

1 N N 1 PRODUTO IDProduto Nome Preço IDMarca IDCategoria MARCA IDMarca Nome CATEGORIA IDCategoria Nome Fact Table: Vendas Cliente Marca Produto Categoria

9.3.3 Principais diferenças

Podemos dizer que, geralmente, um esquema snowflake é menos eficiente que um esquema star. Um quadro comparativo entre estes dois esquemas é apresentado de seguida:

Esquema Star Esquema Snowflake

Número de linhas Elevado Baixo

Leitura Fácil Difícil

Número de tabelas Menor Maior

Tempo de procura nas dimensões Rápido Lento

O esquema snowflake oferece apenas uma vantagem: ocupa menos espaço de armazenamento. A normalização das informational tables não melhora a tarefa de análise dos dados, portanto, talvez seja melhor deixar estas tabelas no seu formato não normalizado. O espaço de armazenamento extra, necessário pela informational table não normalizada é muito pequeno, quando comparado com o espaço de armazenamento requerido pela fact table; sobretudo se compararmos com os ganhos de rapidez em relação a uma estrutura normalizada.

9.4 Dimensões associadas às informational tables

Existem quatro tipos de dimensões associadas às informational tables: 1. Estrutura;

2. Informação; 3. Partição; 4. Categoria.

A dimensão de estrutura é a mais comum, contendo membros que podem ser colocados numa hierarquia. Dimensões de informação contém atributos necessitam de ser calculados. As dimensões de partição são usadas nas comparações de informação, como por exemplo: vendas previstas e vendas realmente efectuadas. Finalmente, as dimensões de categoria são usadas para criar grupos numa dimensão, baseados em atributos.

9.4.1 Dimensões de estrutura

As dimensões de estrutura apresentam a informação acerca de uma métrica na forma de uma hierarquia. Assim sendo, ano, mês e dia formam uma dimensão de estrutura.

Como exemplo de utilização de uma dimensão de estrutura, vamos imaginar que um departamento usa o número total de vendas de um produto como sendo uma measure. Associada a esta measure está a informational table do produto que contém todos os atributos relevantes para descrever as características do produto. As dimensões que podem ser criadas, partindo da informational table do produto são, por exemplo, nome_produto, marca_produto,

categoria_produto e família_produto. Como se pode constatar, as dimensões deste produto formam uma hierarquia. Podemos também adicionar, se assim o desejarmos, uma informational table que reflicta o tempo. Podíamos construir uma dimensão de tempo, que iria consistir em

ano, mês e dia. Usando esta measure e estas duas dimensões de estrutura, podíamos usar o OLAP para determinar o número total de vendas de um determinado produto durante um período de tempo específico.

Alguns exemplos de dimensões de estrutura mais comuns:

• Dimensão geográfica do cliente: Esta dimensão irá proporcionar uma hierarquia que agrupa clientes, baseando-se para isso no local onde vivem. Um exemplo típico de uma dimensão para cliente é: cliente_cidade, cliente_país, etc. Esta dimensão é muitas vezes usada como forma de se estabelecer uma diferença entre vendas, lucros, etc. entre clientes que vivem em localizações geográficas diferentes.

• Dimensão tempo: A dimensão temporal irá mostrar quando um determinado evento teve lugar. Exemplos típicos: ano, mês e dia.

• Dimensão geográfica do vendedor: Esta dimensão irá proporcionar uma hierarquia que agrupa vendedores baseando-se para isso nas zonas onde vende os seus produtos. É muitas vezes usada para comparar vendas, lucros, etc. para as diferentes zonas de venda de produtos.

• Dimensão do produto: Representa o item que foi comprado. Esta hierarquia pode incluir nome_produto, marca_produto, etc. Esta dimensão é usada para que se possam comparar vendas, lucros, etc. entre diferentes linhas de produtos.

Todas estas dimensões de estrutura contêm atributos que são organizados em hierarquias. As hierarquias numa dimensão de estrutura serão demonstradas de seguida.

Natureza hierárquica das dimensões de estrutura

As dimensões de estrutura contêm um conjunto de membros relacionados. Se olharmos para essas dimensões na base de dados operacional original, verificamos que os atributos que formam a dimensão estrutural, normalmente, têm um relação de um para muitos entre si.

Figura 8 - Modelo ER de uma dimensão geográfica

As relações representadas na figura 8, encaixam na definição original de um hierarquia, já apresentada. Podemos redesenhar esta informação em termos hierárquicos, através de uma vista parcial dos dados (figura 9).

Figura 9 - Hierarquia relacional dos dados

Existe um factor importante que está relacionado com as hierarquias, e que não deve ser esquecido: cada item na categoria pode ter determinadas measures associadas.

As measures aditivas são muito úteis quando trabalhamos com hierarquias. Se sabemos o valor de uma measure aditiva para cada item do fundo da hierarquia, podemos calcular o valor para cada nível mais elevado da hierarquia, desde que a estrutura da hierarquia seja conhecida.

1 N 1 N CONCELHO IDDistrito IDConcelho Designação DISTRITO IDDistrito Designação FREGUESIA IDDistrito IDConcelho IDFreguesia Designação Porto Gondomar Maia . . . S. Cosme Valbom . . . Nível 1 Nível 2 Nível 3 . . . Aveiro

Podem ser unidades vendidas, volume de vendas ou qualquer quantidade aditiva. Se usarmos dimensões construídas sobre hierarquias e factos aditivos, apenas necessitamos de armazenar os valores para o nível mais baixo dentro da hierarquia. Tal como já foi referido, os valores para os níveis mais elevados podem ser facilmente calculados partindo dos valores dos níveis inferiores. Mas à frente este tema será discutido com maior pormenor quando falarmos em agregações.

9.4.2 Dimensões de informação

As dimensões de informação são construídas a partir de membros calculados. Podemos querer visualizar o total de vendas, por produto e por lucro. Podemos pensar que os produtos com o maior número de vendas tenha maior lucro, mas nem sempre isto acontece. Se subvalorizarmos um produto, podemos verificar que o índice de vendas é muito elevado, mas no entanto o lucro das vendas é muito baixo. Por outro lado, se sobrevalorizarmos um produto podemos ter índices de vendas muito baixos em contrapartida com alto o valor dos lucros. Assim sendo, colocando o lucro como dimensão e o total de vendas como measure, pode ter resultados muito úteis acerca da informação sobre produtos.

Podemos fazer dois tipos de cálculos acerca do lucro. O primeiro é lucro por item, que é calculado subtraindo ao preço de venda o preço de compra. Uma vez divulgado o resultado do lucro por produto, podemos multiplicar esse valor pelo número total de vendas desse produto e obter o lucro total por dia.

Se criarmos uma dimensão que inclua o lucro por item e o lucro total, temos uma dimensão de informação. Normalmente, os membros calculados são quase sempre measures. No entanto, com um pouco de criatividade podemos usar os atributos calculados em dimensões (em vez de measures).

9.4.3 Informational tables construídas a partir de dimensões de partição

As dimensões de partição são usadas quando duas ou mais dimensões são criadas a partir da mesma estrutura. Por exemplo, se quisermos criar dimensões que representem as vendas previstas e as vendas reais, a estrutura destas duas dimensões é igual, apenas mudam os valores que cada uma contém. Outro exemplo é a dimensão tempo. Todos os anos têm os mesmos trimestres, meses e dias (excepto nos anos bissextos, mas tal não afecta a dimensão). No OLAP recorre-se frequentemente a dimensões de tempo particionadas para particionar os dados no Data Warehouse.