DW OLAP e Modelagem Multidimensional Valria Times CInUFPE

DW, OLAP e Modelagem Multidimensional Valéria Times © CIn/UFPE

Sistemas de Suporte à Decisão Sistemas de Suporte a Decisão (SSD) Permitem armazenar e analisar grandes BD para extrair informações que auxiliam a compreensão do comportamento dos dados sobre o negócio Fornecem apoio ao suporte estratégico de decisão Consistem em sistemas computacionais de suporte à decisão que integram dados oriundos de diversas fontes de dados 3/9/2021 © CIn/UFPE 2

Suporte à Decisão X Operacional SSD diferem de SSO Operacional orientado por transação Ex: o produto vendido, o artigo publicado, o imposto predial pago Decisão orientado por assunto Ex: Vendas, Publicações de Artigos, Gerenciamento Urbano Dividir os dados em dois ambientes ortogonais Operacional (normalmente já existe) Estratégico (construir um DW) 3/9/2021 © CIn/UFPE 3

Sistemas de DW Provêem diferentes níveis de análise São chamados sistemas analíticos Permitem que usuários naveguem nos diferentes níveis de dados Dados são organizados através de modelos multidimensionais Resultados de consultas são interpretados em uma variedade de visões multidimensionais São providas pelas ferramentas OLAP 3/9/2021 © CIn/UFPE 4

Data Warehouse Definição DW é uma base de dados que facilita a execução de consultas de apoio à decisão (Bill Inmon) Objetivos Fornecer uma imagem global da realidade do negócio Exibir informações em níveis apropriados de detalhes (resumido ou detalhado) Otimizar o processamento de consultas complexas (Modelo Estrela ou Flocos de Neve) 3/9/2021 © CIn/UFPE 5

Data Warehouse Principais características Orientada ao assunto: permite tomar decisões sobre o assunto - tema dos dados Perfeitamente integrada: consolida dados de diferentes fontes heterogêneas Variante no tempo: mantém um histórico dos dados, permitindo comparações ao longo do tempo Não volátil: Informações já presentes no banco são raramente modificadas (sobrescritas) Novos dados são carregados no sistema, integrando-se com informações armazenadas previamente © CIn/UFPE 6 3/9/2021

Data Mart Definição DM é um DW departamental (específico de um dado departamento) Subconjunto do DW que satisfaz os requisitos de um certo tema ou atividade de negócio Benefícios Tempo e dificuldade de implantação minimizados São mais facilmentendidos e possuem consultas mais rápidas Restrição Requerem planejamento prévio, para evitar ilhas de dados 3/9/2021 © CIn/UFPE 7

ETL e ODS Ferramentas de ETL Responsáveis pela conversão dos dados do ambiente operacional para o de suporte à decisão Realizam Acesso, Extração, Transformação, Validação e Carga dos dados Operational Datastore (ODS) Repositório de dados operacionais integrados Benefícios Otimiza a criação do DW Possibilita a realização de consultas relacionais sobre dados históricos 3/9/2021 © CIn/UFPE 8

Data Warehousing Definição Processo de construção do DW = base de dados multidimensional Benefícios Vantagem competitiva e aumento de produtividade na tomada de decisões Data Warehousing BD Op. BD BD Op. DM DM DM ETL Extract Transform Load 3/9/2021 Metadados ODS Operational Data Store DW DM Estrela DM ou DM F. Neve DM © CIn/UFPE produzidos em todas as etapas OLAP Data Mining Relatórios 9

Arquiteturas de DW Data Warehousing (Abordagem Top-Down) Visão do DW corporativo Grande abrangência Mais Complexo, Custoso e Demorado Alta probabilidade de insucesso Só pode ser avaliado quando terminado Data Warehousing Top-Down BD Op. BD BD Op. ETL Extract Transform Load 3/9/2021 DM ODS Operational Data Store DW OLAP DM Data Mining DM © CIn/UFPE Metadados produzidos em todas as etapas Relatórios 10

Arquiteturas de DW Data Warehousing (Abordagem Botton-Up) Visão do DW Departamental Foco específico nos aspectos mais críticos Menos Complexo, Custoso, Demorado Alta probabilidade de ilhas de dados Pode acontecer dos DM não se integrarem Data Warehousing Botton-Up BD Op. BD BD Op. ETL Extract Transform Load 3/9/2021 DM ODS Operational Data Store DM OLAP DW DM © CIn/UFPE Metadados produzidos em todas as etapas Data Mining Relatórios 11

Arquiteturas de DW Data Warehousing (Abordagem corrente) Visão do DW Incremental Planejar Top-Down Desenvolver Botton-Up ODS Histórico Data Warehousing Corrente BD Op. BD BD Op. ETL Extract Transform Load 3/9/2021 DM ODS Operational Data Store OLAP Data Mining DM DM DW Relatórios DW Metadados produzidos em todas as etapas © CIn/UFPE 12

Modelagem de Dados Modelo ER Usado para identificar relacionamentos entre tipos de entidades Visa remover a redundância de dados Processamento de Transações On-Line (OLTP) Modelo Dimensional Apresenta dados em uma estrutura intuitiva permitindo alta performance de acesso Independe da representação física dos dados Organiza dados em tabelas de fatos e dimensões Processamento Analítico On-Line (OLAP) 3/9/2021 © CIn/UFPE 13

Modelagem Dimensional Componentes de Modelagem Dimensional Tabelas de Dimensão Geográfica Dimensão Medidas Tabela Fatos Geográfica Produto Tempo Und. $ Fatos Tempo 3/9/2021 © CIn/UFPE 14

Fatos e Dimensões Tabelas de Dimensões Tabelas periféricas com pouco volume de dados Armazenam as descrições do negócio Normalmente desnormalizadas (esquema estrela) Atributos organizados em hierarquias Produto (Categoria Marca Descrição) Loja (Tipo Endereço Nome_Loja) Tempo (Ano Mês Dia_Do_Mês) Podem ser compartilhadas ou privadas 3/9/2021 © CIn/UFPE 15

Fatos e Dimensões Tabela de Fatos Tabela central com grande volume de dados Armazena as medidas numéricas do negócio e chaves das dimensões (ID das dimensões) Cada fato é a interseção entre todas dimensões Idealmente medidas são numéricas e aditivas Ex: Vendas(R$), Vl_unitário(R$), Despesas(R$), Qtd. Vendida Exemplo de medida não numérica: Temperatura 3/9/2021 © CIn/UFPE 16

Fatos e Dimensões Tabela de Fatos (Cont. ) Tabelas de fatos são esparsas e únicas por esquema Requer determinação do nível de detalhe Quando um dado numérico é Medida ou Atributo? Medida varia continuamente a cada amostragem Ex: Quantidade vendida de um produto Atributo praticamente constante Ex: Peso de um produto 3/9/2021 © CIn/UFPE 17

Modelos Dimensionais Esquema Estrela (Star Schema) Dim_Funcionario Chave_Funcionario Codigo_Funcionario . . . Tabela Dimensão Dim_Tempo Chave_Tempo Data . . . Tabela Fato Dim_Produto Fato_Vendas Chave_Produto Codigo_Produto Chave_Tempo Chave_Empregado Chave_Produto Chave_Cliente Chave_Entregador . . . Data_Requerida Dim_Entregador . . . Dim_Cliente Chave_Entreegador Codigo_Entregador . . 3/9/2021. Chave_Cliente Codigo_Cliente © CIn/UFPE . . . 18

Modelos Dimensionais Esquema Flocos de Neve (Snowflake Schema) Tabela-Dimensão Principal Fato_Vendas Dim_Produto Chave_Tempo Chave_Funcionario Chave_Produto Chave_Cliente Chave_Entregador Chave_Produto Required. Date Codigo. Marca . . . Nome Tamanho n. Dimensões são normalizadas n. Ganho de espaço de armazenamento é pouco relevante n Estrutura complexa Marca. Produto Codigo. Marca Codigo. Categoria n Custo de junções 3/9/2021 Tabelas-Dimensão Secundárias Nome © CIn/UFPE 19

Modelos Dimensionais Escolhendo um Esquema Clareza Star Snowflake + fácil + difícil < > + simples + complexo + rápido + lento Número de tabelas Complexidade de consultas Performance de consulta 3/9/2021 © CIn/UFPE 20

Cubo Multidimensional (Abstração do DW) Metáfora de uma abordagem multidimensional para visualização e organização dos dados Várias dimensões podem ser usadas simultaneamente Dados são manipulados mais rapidamente e facilmente (agregação em níveis de hierarquia) 3/9/2021 © CIn/UFPE 21

Cubo Multidimensional Agregação em Níveis de Hierarquias 3/9/2021 © CIn/UFPE 22

Cubo Multidimensional Como representar as dimensões no cubo? Produto AAAA BBBB CCCC DDDD Loja Vendas F 01 50 F 02 60 F 03 100 F 01 40 F 02 70 F 03 80 F 01 90 F 02 120 F 03 140 F 01 20 F 02 10 F 03 30 3/9/2021 Produto AAAA AAAA BBBB BBBB CCCC CCCC DDDD DDDD Loja F 01 F 02 F 03 F 01 F 02 F 03 Tempo Vendas 1999 50 1999 60 1999 100 1999 40 1999 70 1999 80 1999 90 1999 120 1999 140 1999 20 1999 10 1999 30 2000 50 2000 60 2000 100 2000 40 2000 70 2000 80 2000 90 2000 120 2000 140 © 2000 CIn/UFPE 20 2000 10 2000 30 2000 1999 AAAA BBBB CCCC DDDD F 01 F 02 F 03 23

Exemplo de Consultas Qual o total de vendas do produto AAAA? 2000 1999 AAAA BBBB CCCC DDDD F 01 3/9/2021 F 02 © CIn/UFPE F 03 24

Exemplo de Consultas Qual o total de vendas da loja F 03? 2000 1999 AAAA BBBB CCCC DDDD 3/9/2021 F 02 © CIn/UFPE F 03 25

Exemplo de Consultas Qual o total de vendas do ano 1999? 2000 1999 AAAA BBBB CCCC DDDD 3/9/2021 F 02 © CIn/UFPE F 03 26

Exemplo de Consultas Qual o total de vendas do produto BBBB no ano de 1999 e na loja F 2? 2000 1999 AAAA BBBB CCCC DDDD 3/9/2021 F 02 © CIn/UFPE F 03 27

OLTP x OLAP O que é OLAP (On-Line Analytical Processing)? Conjunto de tecnologias projetadas para analisar e acessar dados típicos de suporte a decisão que estão no DW Fornece dados em alto nível (totais, médias, min. . ) Acessa vários registros Tem alta performance e consultas fáceis e interativas Lida com dados históricos (dimensão temporal) Oferece visões multidimensionais (perspectivas) 3/9/2021 © CIn/UFPE 28

OLTP x OLAP O que é OLAP ? (Cont. ) Exemplos de consultas típicas de OLAP: Quais os produtos mais bem vendidos no mês passado? Quais os 10 piores vendedores departamentos da filial X? dos Qual a média salarial dos funcionários de informática na região sul nos últimos 5 anos? 3/9/2021 © CIn/UFPE 29

OLTP x OLAP O que é OLTP (On-Line Transaction Processing)? Conjunto de tecnologias projetadas para analisar e acessar dados típicos de suporte operacional Exemplos de consultas típicas de OLTP: Qual o produto mais vendido? Qual o pior vendedor? Qual o total de vendas? Qual a média salarial dos funcionários? 3/9/2021 © CIn/UFPE 30

Suporte a Decisão X Operacional Diferenças entre os ambientes Características Operacional Objetivo Op. diárias do negócio Visão dos dados Relacional ou Obj-Rel. Incl. , Alt, Excl e Cons. Op. com os dados Atualização Contínua (tempo real) o N de usuários Milhares Tipo de usuário Operacional Interação c/ usuário Pré-definida(predom. ) Granularidade dados Detalhados Redundância dados Ocorrência minimizada Volume dos dados Megabytes-Gigabytes Histórico dos dados Até a última atualização © CIn/UFPE Acesso a 3/9/2021 registro Dezenas Estratégico Análise do negócio Dimensional Carga e Consulta Temporal (lote) Dezenas Gerencial Pré-definida e ad-hoc Detalhados e Resumos Ocorrência maximizada Gigabytes-Terabytes 5 a 10 anos 31 Milhares

Arquiteturas OLAP Categorias de Ferramentas OLAP Relacional OLAP (ROLAP) Multidimensional OLAP (MOLAP) Híbrido OLAP (HOLAP) ROLAP Utiliza BD relacional para análise dos fatos Manipula dados de forma multidimensional via SQL Lida com fatos atômicos assim como sumarizados Permite o uso de várias dimensões 3/9/2021 © CIn/UFPE 32

Arquiteturas OLAP ROLAP 3/9/2021 © CIn/UFPE 33

Arquiteturas OLAP MOLAP Utiliza MDDB proprietários (com matrizes ndimensionais) para manipular fatos agregados Principal premissa Armazenar de forma multidimensional para visualizar de forma multidimensional Desvantagens Não manipula fatos atômicos Não trabalha com muitas dimensões 3/9/2021 Não gerencia um grande volume de fatos © CIn/UFPE 34

Arquiteturas OLAP MOLAP 3/9/2021 © CIn/UFPE 35

Arquiteturas OLAP HOLAP Mistura de MOLAP com ROLAP Suporta manipulação de fatos atômicos e agregados Utiliza MDDB para analisar os fatos agregados Utiliza SQL para manipular fatos atômicos É mais complexo implementar 3/9/2021 para © CIn/UFPE administrar e 36

Arquiteturas OLAP HOLAP 3/9/2021 © CIn/UFPE 37

Arquiteturas OLAP Resumo sobre as Arquiteturas Armazenamento MOLAP HOLAP ROLAP Dados de base Cubo Tabela Relacional Agregações Cubo Tabela Relacional MOLAP HOLAP ROLAP Imediato Mais rápido Rápido Alto Médio Baixo Perspectiva do Cliente Performance de consulta Consumo em disco Manutenção do cubo 3/9/2021 © CIn/UFPE 38

Arquiteturas OLAP Conclusão sobre as Arquiteturas ROLAP é mais flexível e mais geral MOLAP é para soluções específicas departamentais ou HOLAP é supostamente a tendência 3/9/2021 © CIn/UFPE 39

Desempenho x Armazenamento Calcular os agregados no momento da recuperação ou armazená-los? Tempo de Resposta BD 4 BD 3 BD 2 BD 1 BD 3 BD 2 BD 4 BD 1 Quanto maior o número de agregados, melhor o desempenho e mais dados para serem armazenados e gerenciados! © CIn/UFPE 40 3/9/2021

Principais Operações OLAP Drill Down e Rolll Up (ou Drill Up) Técnica que possibilita caminhar pela estrutura multidimensional (hierarquias), permitindo ver diferentes níveis de dados Drill/Roll Up 1 Total 5 Regiões 50 Estados 500 Cidades 3/9/2021 © CIn/UFPE 50. 000 Clientes Drill Down 41

Principais Operações OLAP Drill Down e Roll Up (ou Drill Up) Drill Down Loja F 01 F 02 F 03 Vendas 200 260 350 3/9/2021 Drill Up Loja F 01 Tipo Vendas Luxo 50 Popular 40 Padrão 90 Franquia 20 F 02 Luxo 60 Popular 70 Padão 120 Franquia 10 F 03 Luxo 100 Popular 80 Padrão 140 © CIn/UFPE Franquia 30 200 260 350 42

Principais Operações OLAP Rotação Técnica que gira o cubo, permitindo ter diferentes visões dos dados 4 3 2 2 3 4 x 2 x 3 3/9/2021 4 4 3 x 2 x 4 © CIn/UFPE 3 2 4 x 3 x 2 43

Principais Operações OLAP Slice and Dice Técnica que fatia o cubo, permitindo restringir a análise aos dados, sem inversão de eixos “Semelhante” a cláusula WHERE de SQL 4 4 2 2 2 3 4 x 3/9/2021 2 x 3 2 2 4 x 2 ©x CIn/UFPE 2 3 2 x 2 x 3 44

Referências DW e OLAP Grupo de DW da UFRJ (http: //genesis. nce. ufrj. br/dataware/) Data Warehouse Brasil (http: //www. dwbrasil. com. br/) Artigos de Kimball (http: //www. ralphkimball. com/html/articles. html) Data Warehousing Institute (http: //www. dw-institute. com/) OLAP Report (http: //www. olapreport. com/) 3/9/2021 © CIn/UFPE 45