Módulo 2. Definindo Soluções OLAP
Objetivos
Ao finalizar este módulo o participante:
Recordará os conceitos básicos de um sistema OLTP com seus exemplos.
Compreenderá as características de um Data Warehouse junto com seus componentes.
Reconhecerá a necessidade dos processos de extração, transformação e carga de dados (ETL) que permitem alimentar as tabelas auxiliares que suportarão a estrutura multidimensional.
Conhecerá as diferenças entre um sistema transacional e um Data Warehouse.
Compreenderá o termo OLAP e a sua relação com a navegabilidade da informação.
Conhecerá as transformações necessárias para montar um DW a partir de um Banco de Dados Operacional.
Introdução
Para desenvolver um Data Warehouse, devemos considerar uma série de pautas que deverão estar alinhadas com os objetivos do negócio e os fatos que precisam ser analisados, incluindo o alcance do sistema, a granularidade dos dados e a navegabilidade desejada.
Devem ser identificadas as origens dos dados para selecioná-los, depurá-los, transformá-los e importá-los.
Conteúdo do módulo
2.1 Sistema Transacional (OLTP)
2.1.1 Características
2.1.2 Usos comuns de sistemas OLTP
2.2 Sistemas OLAP
2.2.1 Bancos de Dados (Estruturas)
2.2.2 Usos Comuns de sistemas OLAP
2.3 Dados de Origem X Informações do Negócio
2.3.1 Convertendo Dados em Informações
2.3.2 Extração, transformação e carga de dados – ETL
2.1 Sistema Transacional (OLTP)
2.1.1 Características
Os sistemas OLTP (On-Line Transaction Processing) são os sistemas que capturam as transações de um negócio e as mantêm em estruturas relacionais chamadas Banco de Dados.
As principais características dos sistemas OLTP são:
Realizar transações em tempo real do processo de um negócio, motivo pelo qual os dados armazenados mudam continuamente. Os sistemas OLTP, nas suas transações, controlam processos essenciais do negócio.
Os sistemas OLTP são os responsáveis pela manutenção dos dados, acrescentando dados, realizando atualizações ou eliminando-os.
As estruturas de dados devem estar otimizadas para validar a entrada dos mesmos e rejeitá-los se não atenderem determinadas regras de negócio.
Para a tomada de decisões, os sistemas OLTP possuem capacidades limitadas, pois não é seu objetivo e, portanto, não é uma prioridade no seu desenvolvimento. Se desejasse obter uma determinada informação histórica relativa ao negócio consultando um sistema OLTP, seria produzido um impacto negativo no funcionamento do sistema.
Normalmente, para o desenho de um sistema OLTP é definido um modelo de Diagramade Relação de Entidades (DRE). Um DRE é uma representação da realidade através de um esquema gráfico que contém os seguintes elementos:
Entidades: Uma Entidade é um tipo de objeto que pode ser identificado de forma única por algum meio. Este objeto é traduzido para a estrutura física de um banco de dados como uma tabela.
Atributos: As características particulares que diferenciam as Entidades são denominadas Atributos.
Relações (ou Relacionamentos): vínculos existentes entre as tabelas que servem para garantir a integridade referencial.
Um exemplo de Entidades e Atributos é:
Pessoa (IdPessoa, Nome, Sobrenome, IdLocalidade)
Grupo (IdPessoa, Telefone)
Para conseguir esquematizar um DRE, deve ser realizado um processo de padronização baseado nas Formas Normais, que também garante uma otimização do espaço utilizado no disco.
2.1.2 Usos Comuns de sistemas OLTP
Toda organização ou empresa efetua seus objetivos diários realizando um conjunto de tarefas que estão cuidadosamente agrupadas dentro de processos relacionados entre si. Os processos podem pertencer à área Industrial, ao departamento de Marketing, ao departamento de Vendas ou ao setor Administrativo, mencionando apenas alguns deles.
Podemos dizer que na definição de OLTP podem ser enquadrados todos os sistemas tradicionais dedicados à captura, validação e armazenamento de dados de forma estruturada e que correspondem aos procedimentos.
Sistema OLTP
Imaginemos estar diante de um Sistema de Caixas Eletrônicos. O sistema, ao ser operado por um cliente, passará pelas seguintes situações:
Receber o cartão do Cliente.
Validar o Cliente. Consultar no Banco de Dados se o Cliente existe e, se existir, confirmar que está em uma linha de caixas habilitada.
Autenticar o cliente no sistema. Se desejar realizar uma transferência:
Verificar se apresenta autorização para realizá-la. Verificar se apresenta saldo.
Inicializar a transferência tratando-a como uma transação.
Emitir comprovante. Despedir-se do Cliente.
A situação em um Sistema de Vendas através de um Site seria a seguinte:
Validar o cliente e autenticá-lo no sistema. Aceitar o pedido.
Controlar os limites de crédito.
Informar os valores parciais da compra e acumulados. Confirmação do cliente antes de enviar o pedido. Enviar o pedido.
Descontar as quantidades vendidas do estoque. Informar o número da venda e a data de entrega. Despedir-se do cliente.
Podemos verificar que o sistema transacional garante um conjunto de regras de negócio, como no exemplo de um sistema de vendas pela Web, antes de realizar a venda verifica-se se o cliente não ultrapassou o limite de crédito. Por sua vez, deve ser mantida uma integridade na informação, isto é, se em uma tabela manipula-se o estoque dos produtos e em outra são tratadas as movimentações realizadas destes produtos, as quantidades movimentadas na tabela de movimentações devem ser descontadas na mesma quantidade que as apresentadas na tabela de produtos.
As organizações precisam então registrar as transações ocorridas durante seus processos operacionais, para controle e consulta posterior.
Um sistema OLTP é utilizado em:
Sistemas bancários Processamento de pedidos Comércio eletrônico Sistemas de faturamento Sistemas de estoque
2.2 Sistemas OLAP
2.2.1 Bancos de Dados (Estruturas)
Os sistemas OLAP (On-Line Analytical Processing, ou Processamento Analítico On-line) oferecem uma alternativa aos sistemas transacionais, proporcionando uma visão dos dados orientada à análise, além de uma navegação rápida e flexível.
A tecnologia OLAP apresenta as seguintes características:
Os bancos de dados OLAP apresentam um esquema otimizado para que as perguntas realizadas pelos usuários sejam respondidas rapidamente.
As perguntas realizadas a um OLAP devem permitir a utilização interativa com os usuários.
Os cubos OLAP armazenam vários níveis de dados formados por estruturas altamente otimizadas que atendem às expectativas de negócio da empresa.
Um sistema OLAP está preparado para realizar relatórios complexos de uma forma simples.
O OLAP proporciona uma visão multidimensional dos dados. Os cubos oferecem uma visão multidimensional dos dados que vai além da análise de duas dimensões, oferecida por uma simples planilha de cálculo utilizada como tal.
Os usuários podem modificar facilmente as filas, as colunas e as páginas nos relatórios do OLAP, sendo possível visualizar a informação da forma que seja mais conveniente para análise.
Um Sistema OLAP
Os sistemas OLAP representam uma solução que retorna respostas rápidas para as consultas realizadas.
A partir de sistemas OLAP podem ser obtidos relatórios de negócios sobre Vendas ou Marketing, entre outros.
2.2.2 Usos Comuns de sistemas OLAP
Os sistemas OLAP são utilizados pelas empresas para conhecer o histórico do negócio e poder realizar a tomada de decisões.
Podemos enunciar as seguintes áreas onde o uso de um sistema OLAP está difundido:
Sistemasdeinformaçãoexecutivos. Os usuários e os administradores geralmente de cargos altos e médios, recebem a informação sobre os indicadores de funcionamento dominantes do negócio e das exceções ou as variações segundo os padrões pré-estabelecidos. Os Sistemas de Informação Executivos (EIS) geralmente apresentam dados multidimensionais em formatos gráficos.
Aplicações financeiras. Os bancos de dados OLAP possuem diversos usos no mercado financeiro, incluindo a comunicação, análise do mês de fechamento, análise do aproveitamento do produto, orçamentos e
OLAP em EIS Alertas.
previsões. Os analistas financeiros utilizam sistemas OLAP extensivamente para análise de dados financeiros e operacionais para responder as perguntas dos superiores.
Aplicações de Vendas e Marketing. Existem diferentes formas de chegar aos clientes para atingir os objetivos de venda e de comercialização propostos. Por isso, é aconselhável a utilização de sistemas OLAP onde é importante contar com informação organizada de forma rápida. Os exemplos incluem análise do faturamento, análise de produto, análise do cliente e análise de vendas regional.
Outros Usos. Os bancos de dados do OLAP adaptam-se a uma ampla gama de análises, incluindo rendimento de processamento e eficácia da produção, eficácia do serviço ao cliente e análise de custo do produto. Definitivamente, um sistema OLAP é útil para todo processo no qual seja necessário tomar decisões.
2.3 Dados de Origem X Informações do Negócio
O esquema a seguir representa as diferentes etapas que devem ser executadas para a construção de um Data Mart, a partir da identificação dos
OLAP na Área Financeira Relatórios analíticos. Planejamento. Análise. OLAP no Marketing Análise de Produtos. Análise de Clientes. Análise de Faturamento.
OLAP em Outros Usos Análise da Produção.
Análise de Serviços ao cliente. Evolução do Custo do Produto.
dados originais nos sistemas transacionais até que os usuários possam utilizar essa informação. Ele indica qual parte destes processos cada módulo cobrirá.
As etapas que devem ser atendidas durante o processo de construção de um Data Warehouse são as seguintes:
1. Identificação das necessidades e requerimentos.
2. Reconhecimento das fontes de dados originais e suas estruturas.
3. Baseado nos requerimentos, definir as tabelas auxiliares e os processos de extração, transformação e importação de dados.
4. Construir o esquema multidimensional. Este esquema deve estar de acordo com os requerimentos e com as tabelas auxiliares, como primeira forma de teste.
5. Acesso ao sistema a partir das estações de trabalho dos analistas, obtendo a informação identificada na etapa de requerimentos.
2.3.1 Convertendo Dados em Informações
Para converter os dados em informação, deve ser entendida de que forma podem ser interpretados os dados armazenados nos sistemas OLTP, determinando:
Como os fatos que desejamos medir se relacionam com os dados que podemos obter.
Como estes dados refletem as metas e objetivos englobados pelo negócio.
Um Data Warehouse classifica a informação com base nos aspectos que são de interesse para a empresa.
O ambiente operacional é orientado a aplicativos e funções (vendas, faturamento, estoque, etc.). O banco de dados combina os processos em uma estrutura que responde às necessidades das regras do negócio.
Entretanto, em um Data Warehouse estes elementos são orientados a sujeitos (vendedores, produtos, filiais, etc.).
Após reconhecer a análise do negócio como um valor significativo para uma organização, as solicitações dos dados e da informação tornam-se numerosas e freqüentes.
Satisfazer estas solicitações pode ser uma tarefa muito complexa em um sistema OLTP, sendo necessário procurar entre grandes quantidades de dados obtidos de diferentes fontes, tentando selecionar, adequar e consolidar a informação. Em um sistema OLAP, estes pontos são resolvidos de uma só vez, na etapa de design.
2.3.2 Extração, Transformação e Carga de Dados – ETL
Os dados que alimentam um Data Warehouse são resultantes de diferentes fontes; estas fontes são diferentes sistemas OLTP que a empresa possui, geralmente não homogêneos e não concordando necessariamente com o que é necessário, sendo necessário realizar todas as adaptações pertinentes.
ETL
Os diferentes processos concentrados no conceito de extração, transformação e carga de dados em um Data Warehouse denomina-se ETL, em inglês Extract – Transform – Load.
É comum que os sistemas OLTP das organizações tenham sido desenvolvidos por diferentes equipes de programadores ou empresas de software e, que no seu desenvolvimento, tenham adotado diferentes convenções na codificação de variáveis, nomes dos atributos das tabelas, diferentes tipos de dados ou formatos de datas.
Ao reunir dados dos diferentes sistemas deve ser definida uma norma única para o Data Warehouse e realizar as transformações necessárias em cada caso. Basicamente devem ser realizadas as seguintes tarefas:
Estabelecer as regras que serão utilizadas para realizar a transformação.
Detectar as inconsistências que podem ocorrer ao extrair dados de diferentes fontes.
Planejar cuidadosamente e com detalhes a transformação dos dados, que ofereçam como resultado final conjuntos de dados consistentes.
Convenções diferentes no desenvolvimento de aplicações
Codificação: Um claro exemplo é a codificação e descrição do sexo do indivíduo. Este dado pode ter sido armazenado de diferentes formas. Por exemplo, pode ser encontrado como “M” e “F”, “1” e ”0”, “Homem” e “Mulher” ou “Masculino” e “Feminino.” Na transformação deverá ser escolhida uma convenção única para o Data Warehouse, que pode ser “M” e “F e transformar os dados originais, padronizando-o na tabela de destino.
Unidades de medida dos atributos: As unidades podem apresentar diferentes unidades de medidas, de acordo com a origem do sistema OLTP. Um exemplo e falar em litros, centímetros cúbicos ou decilitros. Deve ser escolhida uma única unidade de medida que seja útil para o Data Warehouse e transformar os dados.
Operacional Data Warehouse
Aplicação A: M e F Aplicação B: 1 e 0
Aplicação C: Masculino e Feminino
Formatos: Outro exemplo claro são os formatos de data encontrados nos diferentes sistemas operacionais. As datas podem estar armazenadas como aaaa/mm/dd, mm/dd/aaaa ou dd/mm/aaaa. No desenvolvimento do Data Warehouse devemos escolher alguma delas e realizar a transformação correspondente.
Várias colunas para uma: Em um sistema OLTP, os dados de uma pessoa, como Endereço podem ser armazenadas em diferentes campos da mesma tabela (Rua, Número, Andar e Apartamento). Ao transformar estes dados para que possam ser utilizados em um Data Warehouse, é possível armazená-los em um única coluna. O mesmo pode acontecer com Nome e Sobrenome. No sistema OLTP pode estar armazenado em duas colunas e no OLAP estar em apenas uma.
Operacional Data Warehouse
Aplicação A: Litros Aplicação B: cm3 Aplicação C: Decilitros
Litros
Operacional Data Warehouse
Aplicação A: aaaa/mm/dd Aplicação B: mm/dd/aaaa Aplicação C: dd/mm/aaaa
Uma coluna para vários: Os sistemas mais antigos costumavam colocar o tipo e número de documento no mesmo campo da tabela. Em um DW é possível que seja necessário colocar o tipo de documento em um campo e o número de documento em outro.
Granularidade
No momento de importar os dados da fonte de origem devem ser realizadas as sumarizações requeridas. Deve ser definida a granularidade máxima a ser armazenada e somar os dados, agrupando-os de acordo com esse critério. Ao definir a granularidade está sendo decidido ao mesmo tempo:
As análises que são de interesse.
O grau de detalhe necessário.
Isto é, se tomarmos como exemplo a medição do tráfego telefônico, é possível definir a necessidade dos totais de ligações por cliente por dia. Vemos que o máximo detalhe requerido é o dia, não interessando a hora da ligação nem o tempo de cada uma das ligações. Por isso, deve ser agrupado e somado utilizando o critério por Cliente e Dia.
Se desejar ter a quantidade e valor das vendas por mês, cliente e produto, é necessário agrupar por estas três aberturas, deixando no sistema OLTP o detalhe por dia por nota fiscal ou por varejo, obtendo o resultado visto no gráfico.
Por contar com o plano de trabalho desenvolvido segundo as regras de transformação, colhemos os dados do sistema OLTP e os importamos dentro da nossa área de dados. Utilizaremos tabelas auxiliares para armazenar os dados de origem para ajudar durante a transformação.
Interpretação equivocada dos Requerimentos
Durante a etapa de análise prévia ao desenho de um sistema OLAP é importante entender com precisão a problemática do negócio. Isto inclui definir o fato e quais medidas serão necessárias para se desenvolver o sistema.
Muitos sistemas não obtêm sucesso devido a uma etapa de análise onde os requerimentos propostos não apontam para os objetivos do negócio.
Estudo de Caso
Relevando os Requerimentos
No Módulo 1 identificamos as necessidades da Contoso e quais fatores deseja analisar para a tomada de decisões.
Agora devemos identificar de que forma, através das aberturas e das medidas, vamos medir os fatos que a empresa precisa analisar.
Levando em consideração que cada ponto mencionado nos requerimentos está relacionado às vendas da empresa, podemos dizer que o fato do nosso Data Warehouse será, justamente, as Vendas.
Começaremos analisando cada necessidade e qual é a dimensão ou medida que deverá ser criada para satisfazê-la. Depois, deve ser desenvolvida uma tabela onde será resumida a informação obtida. Esta tabela será utilizada na etapa de design.
Analisaremos o primeiro conjunto de necessidades:
A quantidade de unidades vendidas nos países atingidos pelo mercado atual.
Nesta ordem detecta-se como possível medida as unidades vendidas, que precisamos ver detalhadamente por País. Por outro lado, a quantidade de unidades vendidas refere-se aos produtos: detectamos uma nova dimensão, o Produto.
O custo incluído em cada unidade vendida. Deste requerimento resulta a medida custo de vendas.
O valor de venda de cada produto.
Aqui, precisamos contar com a medida valor de vendas, sabendo que será utilizada a dimensão Produto para obter o Valor da Venda de cada Produto.
O lucro obtido na venda de cada produto.
A medida Lucro obtido, será obtida da diferença entre o valor da venda e o custo do produto.
Esta informação requer apresentação por região geográfica e filial. Aqui é apresentada uma nova dimensão, que será chamada de Filial.
Agora, realizaremos a análise do segundo conjunto de requerimentos: Por outro lado a empresa deseja:
Montar cestas de produtos de acordo com o perfil de compra dos clientes de cada cidade na qual tenha um local de varejo. Para isso, é necessário um estudo das vendas realizadas abertas por categoria de
produto (com a possibilidade de obter o detalhe por produto), por cidade, por mês, para os últimos 13 meses (para detectar paradas). Verificamos que é necessário analisar os produtos de acordo com a sua categoria e os clientes que os adquiriram. A partir daqui se faz necessária uma nova dimensão chamada Clientes e que os produtos sejam agrupados por Categoria de Produtos, definindo um nível na dimensão Produto.
Premiar anualmente os vendedores que ultrapassem os objetivos de venda atribuídos. A análise, neste caso, deverá incluir os vendedores, as vendas realizadas, os objetivos de venda e o indicador de cumprimento detalhados por mês para o ano fiscal (O prêmio será diferente se forem atingidos os objetivos globais para o ano ou se, além disso, forem atingidos os objetivos em todos os meses em particular). Sobre estes requerimentos, devemos acrescentar apenas a dimensão Vendedor, pois as medidas utilizadas serão as mesmas destacadas anteriormente.
Levando em consideração que a empresa chega aos clientes tanto através dos supermercados quanto dos hipermercados, poderia ser muito útil realizar a análise de cada uma das medidas por Tipo de Filial.
Todo Data Warehouse contém informação histórica que a empresa analisará para diferentes períodos, então, acrescentaremos mais uma dimensão denominada Tempo.
É comum que seja necessário analisar as vendas obtendo a sua média. Portanto, vendo esta possível necessidade, seria conveniente desenvolver a medida Vendas Unidades Média.
Para ver a informação obtida nas análises de uma forma mais clara e compreensível, é conveniente elaborar uma tabela de entrada dupla onde colocaremos nas linhas as medidas e nas colunas as dimensões. Nas intersecções de linhas e colunas, colocaremos uma cruz se é necessário ver a medida por essa dimensão.
Fato a medir: Venda de Produtos Dimensões
Medidas Tempo Filial Vendedor Cliente Produto
Vendas_Valor X X X X X Vendas_Custo X X X X X Vendas_Unidades X X X X X Vendas_ValorTotal X X X X X Vendas_Lucro X X X X X Vendas_Média X X X X X
Esta tabela resumida é muito útil para ver claramente os requerimentos, agrupar por abertura e começar a definir os cubos que devem ser criados.
É possível compreender mais profundamente a estrutura de um sistema OLTP.
Foi compreendido onde é utilizado um sistema OLTP. Foi demonstrado de que forma é estruturado um
sistema OLAP.
Foi abordado em detalhes em quais áreas um sistema OLAP é utilizado.
Foram abordadas as inconsistências que podem ocorrer quando um sistema OLAP é alimentado a partir de um sistema operacional (OLTP).
É possível compreender como transformar os dados antes de chegar ao sistema OLAP.
Foram analisados os Fatos que são de interesse?
Foram executadas as aberturas pelas quais será analisada a informação?
Foram analisadas as medidas ou indicadores que serão utilizadas para avaliar os Fatos?
Qual é a granularidade necessária para visualizar a informação no sistema OLAP?
Foram definidas as fontes de onde serão retirados os dados?
Foram definidos os formatos dos arquivos de transferência e dos dados que eles incluem?
Foram desenhados os processos de extração, transformação e carga de dados (ETL)?
