Aula22_DawareHouseOLAP
Propaganda
SCC0141 - Bancos de Dados e Suas Aplicações Data Warehouse e OLAP Prof. Jose Fernando Rodrigues Junior 1 Introdução No início, uma única fonte de dados No início, uma única fonte de dados Exemplo: primeira loja do grupo Casas Bahia. Mais tarde Exemplo: primeira cidade ocupada pelo grupo Casas Bahia. E assim sucessivamente Exemplo: primeiros estados ocupados pelo grupo Casas Bahia. Finalmente Exemplo: Brasil (primeiro país?) ocupado pelo grupo Casas Bahia. Até que… O que está acontecendo nas minhas lojas? - - O que é vendido mais? Quando? Por qual loja? Qual a melhor loja? Quanto estou faturando? Qual seria uma boa oferta? As vendas cresceram ou subiram? Quais regiões vendem mais? ... Até que… O que está acontecendo nas minhas lojas? Soluções: - O que é vendido mais? 1) Ir- até cada uma das lojas e perguntar; Quando? - - Por qual loja? Pouco viável. Qual a melhor loja? Quanto estou faturando? Qual seria uma boa oferta? As vendas cresceram ou subiram? Quais regiões vendem mais? ... Até que… O que está acontecendo nas minhas lojas? Soluções: - O que é vendido mais? 2) Usar os dados das lojas para analisar o que está - Quando? acontecendo no meu negócio. - Por qual loja? - Qual a melhor loja? Boa idéia!!! - Quanto estou faturando? - Qual seria uma boa oferta? - As vendas cresceram ou subiram? - Quais regiões vendem mais? - ... Até que… O que está acontecendo nas minhas lojas? Soluções: - O que é vendido mais? 2) Usar os dados das lojas para analisar o que está - Quando? acontecendo no meu negócio. - Por qual loja? - Qual a melhor loja? Boa idéia!!! - Quanto estou faturando? - Qual seria uma boa oferta? Mas... - As vendas cresceram ou subiram? - Quais regiões vendem mais? - ... Impecilhos ao uso dos dados… - Diversos sistemas de bancos de dados em uso nas lojas; depende do gerente que o implementou: PostgreSQL, Oracle, DB2, SQLServer, ... - Cada loja, um controle transacional diferente, de acordo com suas necessidades regionais; - Esquemas diferentes, tipos de dados diferentes, distribuição geográfica, falta de interconexão. Impecilhos ao uso dos dados… - Diversos sistemas de bancos de dados em uso nas lojas; depende do gerente que o implementou: PostgreSQL, Oracle, DB2, SQLServer, ... Total falta de conformidade!!! - - Cada loja, um controle transacional diferente, E muitos, muitos dados!!! regionais; de acordo com suas necessidades O que fazer? Esquemas diferentes, tipos de dados diferentes, distribuição geográfica, falta de interconexão. O que é necessário? - 1) Recolher (extrair) os dados não importando qual o tipo do sistema de dados; - 2) Padronizar (transformar) os dados, para terem um significado comum mesmo que, originalmente, codificados de maneira diferente; resolução de dados ausentes e espúrios; - 3) Unir (carregar) os resultados das duas operações em um único sistema capaz para responder às minhas perguntas. O que é necessário? - 1) Recolher (extrair) os dados não importando qual o tipo do sistema de dados; Complicado. - 2) Padronizar (transformar) os dados, para Como fazer comum então? terem um significado mesmo que, originalmente, codificados de maneira diferente; resolução de dados ausentes e espúrios; - 3) Unir (carregar) os resultados das duas operações em um único sistema capaz para responder às minhas perguntas. Mas há uma solução Mas há uma solução Loja 1 Oracle Modelagem x Loja 2 DB2 Modelagem y Loja 3 SQLServer Modelagem z ... Loja n-2 Loja n-1 Loja n Oracle Modelagem u DB2 Modelagem v SQLServer Modelagem w Extrair, transformar, carregar dados Data warehouse O ETL – Extract Transform Load processo descrito de se extrair (Extract), transformar (Transform) e carregar (Load) os dados a partir das diversas fontes de dados é denominado ETL ETL – uma das camadas principais da arquitetura de um data warehouse Consolidação de dados O ETL – Extract Transform Load processo descrito de se extrair (Extract), transformar (Transform) e carregar (Load) os dados a partir das diversas fontes de dados é denominado ETL ETL – uma das camadas principais da arquitetura de um data warehouse Extrair, transformar, carregar dados Consolidação de dados ETL – Extract Transform Load Extração/transformação (Extract/Transform) de dados extração de múltiplas fontes consolidação e integração de dados de múltiplas fontes limpeza e validação conversão dos dados para o modelo do DW ETL – Extract Transform Load Carregamento (Load) de dados armazenamento de acordo com o modelo do DW criação e manutenção de estruturas de dados criação e manutenção de caminhos de acesso tratamento de dados que variam no tempo suporte a atualização refresh purging (eliminação) Conceitos O que é exatamente? “Data Warehouse é uma coleção de dados orientados por assunto, integrada, nãovolátil, variante no tempo, que dá apoio às decisões de administração” (W.H. Inmon, 1992). Orientados a transações: vendas, operações bancárias, acessos à informação. Introdução Aplicações empresas de telefonia redes de varejo instituições financeiras instituições governamentais instituições de ensino e pesquisa …. 24 Sistemas OLTP Extrair, transformar, carregar dados Sistemas OLTP Data warehouses são, comumente, alimentados por sistemas OLTP independentes. Extrair, transformar, carregar dados Sistemas OLTP Data warehouses são, comumente, alimentados por sistemas OLTP independentes. Extrair, transformar, carregar dados Sistemas OLTP (Online Transaction Processing): gerenciamento de transações; toda vez que você vai ao mercado, ao banco ou faz uma compra online, você está usando um sistema OLTP Objetivos Consolidação dos dados de uma empresa Desempenho na consulta aos dados Separação entre suporte à decisão e bancos de dados operacionais Suporte à ferramentas: mineração de dados, visualização e On-line Analytical Processing (OLAP) Objetivos Os termos Datawarehouse, OLTP e OLAP não se refererem a software apenas São termos que englobam software e serviços (muitos serviços – consultoria) São termos cunhados para a comunidade empresarial não possuindo uma correspondência simples em Ciência da Computação Relação OLTP e OLAP Arquitetura de um data warehouse Arquitetura de um data warehouse Arquitetura organização De 1. 2. 3. 4. definida pelo contexto da maneira geral, tem as seguintes camadas: Operacional (OLTPs): fornecem dados De acesso aos dados: ETL Acesso à informação: ferramentas de acesso a dados, geração de relatórios, e análise (OLAP) Business Intelligence Metadados: detalhamento do conteúdo do data warehouse dicionário de dados Arquitetura de um data warehouse Arquitetura organização De 1. 2. 3. 4. definida pelo contexto da maneira geral, tem as seguintes camadas: Operacional (OLTPs): fornecem dados De acesso aos dados: ETL Acesso à informação: ferramentas de acesso a dados, geração de relatórios, e análise (OLAP) Business Intelligence Metadados: detalhamento do conteúdo do data warehouse dicionário de dados Dicionário de dados Descrição dos dados do DW: origem regras de transformação nomes e aliases formato dos dados histórico de atualizações acesso e segurança responsabilidades sobre os dados Sem o dicionário, não há sistema Data warehouse vs Banco de dados operacional Data warehouse vs Banco de dados operacional Data warehouse Banco de dados operacional Orientado a objetivos específicos Orientado a transações Grande (centenas de GBs até TBs) Dados históricos Pequeno/Médio (MBs até alguns GBs) – distribuído se necessário Dados correntes De-normalizado (poucas tabelas com muitas colunas) Atualizações em Batch Normalizado (muitas tabelas com poucas colunas) Atualizações contínuas Otimizado para acesso Otimizado para escrita/atualização Juntos data warehouse e bancos de dados provém uma solução completa Bancos de dados Inserção/Atualização Data Warehouse Acesso aos dados Visão Geral Extrair, transformar, carregar dados 1. Camada Operacional (OLTPs) 2. Camada de acesso aos dados (ETL) 3. Camada de acesso à informação: MD, relatórios, OLAP Business Intelligence 4. Dicionário de dados Projeto de Data warehouses Datamarts Datamart: ambientes de análise de dados de menor magnitude, com fins mais específicos, limitados a subcomunidades de uma organização Exemplo, uma loja das casas Bahia Loja i Modelagem x Datamarts Datamart: ambientes de análise de dados de menor magnitude, com fins mais específicos, limitados a subcomunidades de uma organização Exemplo, uma loja das casas Bahia Modelagem x Acréscimo de ferramentas de análise Datamarts Datamart: ambientes de análise de dados de menor magnitude, com fins mais específicos, limitados a subcomunidades de uma organização Exemplo, uma loja das casas Bahia Loja i Modelagem x Datamart i Bottom-up Projeto Bottom-up: exemplo, rede das casas Bahia .... Datamart 1 Datamart 2 Datamart 3 Datamart n Bottom-up Projeto Bottom-up: exemplo, rede das casas Bahia .... Datamart 1 Datamart 2 Datamart 3 Datamart n Bottom-up Projeto Bottom-up: exemplo, rede das casas Bahia .... Datamart 1 Datamart 2 Datamart 3 Datamart n Data warehouse : conjunto de pequenos sistemas de tratamento e análise de dados. Bottom-up Projeto Bottom-up: exemplo, rede das casas Bahia .... Datamart 1 Datamart 2 Datamart 3 Datamart n Data warehouse : conjunto de pequenos sistemas de tratamento e análise de dados. Grande esforço de consolidação modelo de dados não universal. Top-down Projeto Top-down Data warehouse : projeto bem definido. Top-down Projeto Top-down Datamart 1 Data warehouse : projeto bem definido. Top-down Projeto Top-down Datamart 1 Datamart 2 Data warehouse : projeto bem definido. Top-down Projeto Top-down Datamart 1 Datamart 2 Datamart 3 Data warehouse : projeto bem definido. Top-down Projeto Top-down .... Datamart 1 Datamart 2 Datamart 3 Data warehouse : projeto bem definido. Datamart n Top-down Projeto Top-down .... Datamart 1 Datamart 2 Datamart 3 Datamart n Data warehouse : projeto bem definido. Esforço reduzido de consolidação modelo de dados universal e bem definido. Projeto Top-down vs Bottom-up Bottom-up Top-down Consistência via manutenção constante Alta consistência Expansão custosa Expansão com novos DMs facilitada Custo diluído ao longo do tempo Inicialmente custosa Natural em organizações Rara em start-ups Baixa latência Alta latência Conceitos vistos Data warehouse OLTP ETL Dicionário de dados DW vs BD Datamart Projeto Bottom-up vs Top-down OLAP – Online Analytical Processing OLAP Definição: uma categoria de tecnologia de software que visa à compreensão de dados Provê: acesso interativo DW rápido, consistente e e OLAP, em muitos casos, conceitos indissociáveis OLAP - FASMI FASMI: Fast Analysis Multidimensional Information Fast: of agilidade em responder consultas Analysis: versatilidade analítica Shared: dados/analistas múltiplos Multidimensional: orientado a dimensões de dados Information: propósito fim Cubo de dados OLAP Cubo de dados 240 101 110 150 204 190 90 83 90 35 87 19 27 35 45 Cubo de dados Estrutura básica da prática de OLAP Observam-se As dimensões dos dados As medidas sobre os dados O cubo é orientado a planos (faces) Apesar da complexidade dos sistemas OLAP, seus objetivos analíticos são básicos: contagem, média, máximo, mínimo, soma, ... Agregação, rápida e flexível, sobre imensos volumes de dados Cubo de dados - slicing Pode ser interessante ver o cubo a partir de diferentes perspectivas (planos) Operações sobre o cubo: slicing, dicing e rotating (pivoting) Cubo de dados - slicing • A operação de slicing equivale a fatiar o cubo, definindo um novo plano de apreciação dos dados • A operação geométrica é apenas uma analogia, o slicing dispara o processamento OLAP para calcular o novo plano Cubo de dados - slicing • A operação de slicing equivale a fatiar o cubo, definindo um novo plano de apreciação dos dados • A operação geométrica é apenas uma analogia, o slicing dispara o processamento OLAP para calcular o novo plano Dados calculados e acessíveis ao analista. Cubo de dados - slicing • A operação de slicing equivale a fatiar o cubo, definindo um novo plano de apreciação dos dados • A operação geométrica é apenas uma analogia, o slicing dispara o processamento OLAP para calcular o novo plano Exemplo de dado calculado: quantas TVs de plasma foram vendidas em 2002 na região SE? Cubo de dados - slicing • A operação de slicing equivale a fatiar o cubo, definindo um novo plano de apreciação dos dados • A operação geométrica é apenas uma analogia, o slicing dispara o processamento OLAP para calcular o novo plano Exemplo de dado calculado: quantas TVs de plasma foram vendidas em 2002 na região SE? Cubo de dados - slicing • A operação de dicing é semelhante ao slicing, mas usa dois, ou mais, planos de corte Dados calculados e acessíveis ao analista. Dicing conseguido com 5 planos de corte. Cubo de dados - slicing • A operação de dicing é semelhante ao slicing, mas usa dois, ou mais, planos de corte Dados calculados e acessíveis ao analista. Dicing conseguido com 5 planos de corte. Exemplo de dado calculado: quantas Celulares foram vendidas em 2001 na região CO? Cubo de dados - slicing • A operação de dicing é semelhante ao slicing, mas usa dois, ou mais, planos de corte Dados calculados e acessíveis ao analista. Dicing conseguido com 5 planos de corte. Exemplo de dado calculado: quantas Celulares foram vendidas em 2001 na região CO? Cubo de dados - rotating • A operação de rotating muda a perspectica do cubo todo. Novo plano: Produto x Tempo Dados calculados e acessíveis ao analista. Cubo de dados - rotating • A operação de rotating a perspectica do cubomuda apenas cubo todo. Obviamente refere-se a para fins didáticos – aplicações de DW/OLAP geralmente envolvem mais do que 3 dimensões, definindo hipercubos. Novo plano: Produto x Tempo Dados calculados e acessíveis ao analista. Modelo de dados dimensional OLAP e DW OK, mas e o DW, onde entra nisso tudo? O modelo de dados do DW é orientado a servir a análise baseada em Dimensões de dados Modelo de dados DW Data Warehouse Banco de dados operacional FATOS Esquema estrela Esquema complexo Modelo de dados DW Data Warehouse Banco de dados operacional FATOS Esquema estrela Esquema complexo Modelo de dados DW Data Warehouse Banco de dados operacional FATOS Esquema floco de neve (snow flake) Esquema complexo (normalizado) Modelo de dados dimensional Exemplo Estrela: Produto p_chave Tipo Fabricante Modelo Fatos t_chave Tempo t_chave Ano Trimestre Mes p_chave Local l_chave l_chave Nro_unidades Regiao Valor Cidade Assist_tecnica Loja Modelo de dados dimensional Produto Exemplo: Exemplo: p_chave considerando Ano, Tipo de Produto e Quantas vendas e qual valor de vendas ocorreram Tipo Região? Fabricante SELECT Tempo.Ano, Produto.Tipo, Local.Regiao, Sum(Nro_unidades), Sum(valor) Modelo FROM Fatos, Tempo, Produto, Local WHERE Fatos.t_chave = Tempo.t_chave AND Fatos Fatos.p_chave = Produto.p_chave AND t_chave Fatos.l_chave = Local.l_chave Local p_chave GROUP BY Tempo.Ano, Produto.Tipo, Local.Regiao Tempo t_chave Ano Trimestre Mes l_chave l_chave Nro_unidades Regiao Valor Cidade Assist_tecnica Loja Modelagem de Dados para DW Exemplo Snowflake (Elmasri e Navathe, 2005) 79 Modelo de dados dimensional Cubo de dados: análise dimensional das medidas (dados) DW: modelo de dados dimensional Dimensões: dão contexto aos fatos Fatos: números transacionais Modelo de dados dimensional Observe que as dimensões dos dados possuem uma hierarquia categórica Por exemplo: Tempo(Ano, Trimestre, Mês) Modelo de dados dimensional Hierarquia das dimensões: apreciação dos dados em diferentes granularidades. Exemplo: Itens_vendidos(ano) > Itens_vendidos(Trimestre) Itens_vendidos(Trimestre) > Itens_vendidos(Mês) Duas outras operações muito importantes Drill down Roll up Drill down / Roll up Drill-down e roll-up: navegação ao longo dos níveis hierárquicos das dimensões Exemplo Drill-down Itens_vendidos(Região) Itens_vendidos(Cidade) Roll-up Drill down / Roll up Drill-down sobre as três dimensões simultaneamente. Drill down / Roll up Exemplo: Quantas vendas e qual valor de vendas ocorreram considerando Ano, Tipo de Produto e Região? Drill-down sobre as três Drill Down em todas as dimensões dimensões Quantas vendas e qual valor de vendas ocorreram considerando Trimestre, simultaneamente. Fabricante e Cidade? SELECT Tempo.Ano, Tempo.Trimestre, Produto.Tipo, Produto.Fabricante, Local.Regiao Local.Cidade, Sum(Nro_unidades), Sum(valor) FROM Fatos, Tempo, Produto, Local WHERE Fatos.t_chave = Tempo.t_chave AND Fatos.p_chave = Produto.p_chave AND Fatos.l_chave = Local.l_chave GROUP BY Tempo.Ano, Tempo.Trimestre, Produto.Tipo, Produto.Fabricante, Loca.Regiao, Local.Cidade Drill down / Roll up Exemplo: Quantas vendas e qual valor de vendas ocorreram considerando Ano, Tipo de Produto e Região? Drill-down sobre as três Drill Down em todas as dimensões dimensões Quantas vendas e qual valor de vendas ocorreram considerando Trimestre, simultaneamente. Fabricante e Cidade e considerando um slicing de ano entre 2001 e 2002? SELECT Tempo.Trimestre, Produto.Fabricante, Local.Cidade, Sum(Nro_unidades), Sum(valor) FROM Fatos, Tempo, Produto, Local WHERE Fatos.t_chave = Tempo.t_chave AND Fatos.p_chave = Produto.p_chave AND Fatos.l_chave = Local.l_chave AND Tempo.Ano between 2001 AND 2002 GROUP BY Tempo.Trimestre, Produto.Fabricante, Local.Cidade OLAP - Agregação OLAP - Agregação Mais do que cubinhos, o OLAP/DW é uma tecnologia sofisticada que visa responder às diferentes possibilidades e níveis de agregação de maneira rápida e precisa. Síntese http://etl-tools.info Relational OLAP (ROLAP) Recursos OLAP sobre SGBDs disparam todas as agregações necessárias para se obter os resultados requisitados O OLAP não relacional, também é conhecido como Multidimensional OLAP - MOLAP SQL:1999 ROLLUP e CUBE SELECT …. FROM …. WHERE … GROUP BY CUBE (trim, região) SELECT …. FROM …. WHERE … GROUP BY ROLLUP (trim, região) ex: selecionar total de vendas por trim. por região total de vendas por trim. total de vendas por região total geral de vendas ex: selecionar total de vendas por trim. por região total de vendas por trim. total geral de vendas 90 Relational OLAP (ROLAP) Sobre ROLLUP e CUBE ROLLUP e CUBE aplicados sobre k atributos ... GROUP BY CUBE (....) é equivamente a agrupamentos sobre cada um dos 2k subconjuntos de atributos ex: k=2 ⇒ ... GROUP BY CUBE (a,b) agrupamentos sobre: (a,b), (a), (b), (null) agrupamento em (null) é o total geral de vendas 91 Relational OLAP (ROLAP) (cont...) (cont...) ... GROUP BY CUBE (....) é equivamente a agrupamentos sobre cada um dos 2k subconjuntos de atributos ex: k=3 ⇒ ... GROUP BY CUBE (a,b,c) agrupamentos sobre: (a,b,c), (a,b), (a,c), (b,c) (a), (b),(c), (null) 92 Relational OLAP (ROLAP) Dept Funcao COUNT(*) SUM(Salario) ------------------------------------------------ Exemplo GROUP BY CUBE SELECT Dept, Funcao, 10 Secretario 1 100 10 Gerente 1 500 10 Presidente 1 900 3 1500 10 20 Analista 2 350 FROM Empregados 20 Secretario 2 240 GROUP BY CUBE(Dept, Funcao); 20 Gerente 1 800 5 1390 Secretario 3 340 Gerente 2 1300 Presidente 1 500 Analista 2 350 8 2490 COUNT(*), SUM(Salario) 20 Relational OLAP (ROLAP) (cont...) (cont...) ... GROUP BY ROLLUP (....) é equivamente a agrupamentos em k+1 subconjuntos: k=2 ⇒ ... GROUP BY ROLLUP (a,b) agrupamentos sobre: (a,b), (a), (null) agrupamento em (null) é o total geral de vendas k=3 ⇒ ... GROUP BY ROLLUP (a,b,c) agrupamentos sobre: (a,b,c), (a,b), (a), (null) 94 Ferramentas Ferramentas de suporte à criação e manutenção de DW Ferramentas OLAP Ferramentas de BI … 95 Referências ELMASRI, R; NAVATHE, S.B. Sistemas de Banco de Dados, Addison Wesley, 4a edição, 2005. Ramakrishnan R.; Gehrke, J. Database Management Systems, Mc Graw Hill, 2000. 96
