Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Jornadas de Engenharia Informática Instituto Politécnico da Guarda Henrique Madeira Departamento de Engenharia Informática Faculdade de Ciências e Tecnologia Universidade de Coimbra Jornadas de Engenharia Informática Instituto Politécnico da Guarda 27 e 28 de Maio de 2003 1 Para que serve uma data warehouse? • Transformar os dados disponíveis numa organização em informação relevante para apoio à decisão. Henrique Madeira, DEI-FCTUC, 2003 • Dar a possibilidade aos utilizadores de analisar os dados segundo uma grande variedade de critérios. Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Data Warehousing e OLAP 2 1 Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Elementos básicos de uma data warehouse OLAP application Multidimensional server (result analysis) Sistemas legados Folhas de cálculo, ficheiros, ... Ad hoc queries ROLAP/ MOLAP Henrique Madeira, DEI-FCTUC, 2003 BDs operacionais Net Relatórios estatísticos Fontes externas Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP 3 Data Marts • É, normalmente, um subconjunto de uma DW; • Numa Data Mart os dados são focalizados numa área específica (processo de negócio); Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Data Warehousing e OLAP Henrique Madeira, DEI-FCTUC, 2003 • Muitas vezes uma Data Mart é feita para responder rápidamente a uma área de actividade. 4 2 Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Arquitectura de BDs de uma organização 1 BDs operacionais Data Warehouse Data Mart Sistemas legados Henrique Madeira, DEI-FCTUC, 2003 Folhas de cálculo, ficheiros, ... Fontes externas Utilizadores Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Utilizadores Data Warehousing e OLAP 5 Arquitectura de BDs de uma organização 2 BDs operacionais Data Mart Sistemas legados Data Warehouse Henrique Madeira, DEI-FCTUC, 2003 Folhas de cálculo, ficheiros, ... Utilizadores Fontes externas Utilizadores Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Data Warehousing e OLAP 6 3 Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Sistemas fonte • sistema de registo de transacções • gestão de clientes, gestão de produtos, gestão de vendas, etc… • principais características assumidas disponibilidade pesquisas típicas limitadas a fichas individuais mantêm pouca informação histórica A obtenção de relatórios de gestão é complicada e pesada Pouca ligação com restantes sistemas da empresa Henrique Madeira, DEI-FCTUC, 2003 • • • • • – registos de facturação não ligados a base de produtos ou clientes Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP 7 Elementos básicos de uma data warehouse BDs operacionais OLAP application Multidimensional server (result analysis) Ad hoc queries Sistemas legados Data Staging Area ROLAP/ Net MOLAP Henrique Madeira, DEI-FCTUC, 2003 Folhas de cálculo, ficheiros, ... Relatórios estatísticos Fontes externas Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Data Warehousing e OLAP 8 4 Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Área de processamento temporário (Staging Area) • Área e processos que actuam sobre os dados fonte limpeza transformação combinação preparação Staging Area Data Warehouse Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Henrique Madeira, DEI-FCTUC, 2003 • • • • 9 Metadados • É necessário uma estrutura (na prática outra base de dados) para descrever os dados da DW. Deve descrever: – Que dados existem na DW; – Onde estão armazenados; – Como se relacionam com os dados de outras bases de dados; – Qual a proveniência dos dados e quem são os seus donos. Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Data Warehousing e OLAP Henrique Madeira, DEI-FCTUC, 2003 – Qual o seu formato; 10 5 Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Processos básicos da DW • Extracção • Limpeza • Transformação Henrique Madeira, DEI-FCTUC, 2003 • Carregamento e indexação • Tratamento de erros • Utilização (pesquisas OLAP) Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP 11 Elementos básicos de uma data warehouse BDs operacionais OLAP application Multidimensional server (result analysis) Ad hoc queries Sistemas legados Data Staging Area ROLAP/ Net MOLAP Relatórios estatísticos Fontes externas Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Data Warehousing e OLAP Henrique Madeira, DEI-FCTUC, 2003 Folhas de cálculo, ficheiros, ... 12 6 Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Dados operacionais Dados da Warehouse Objectivos operacionais Registo histórico Acessos de leitura/escrita Acessos só de leitura Acesso por transacções pré-definidas Acesso por queries ad hoc e relatórios periódicos Acesso a poucos registos de cada vez Muitos registos em cada acesso Dados actualizados em tempo real Carregamentos periódicos de mais dados Estrutura optimizada para actualizações Estrutura optimizada para queries complexas Event-driven: os processos geram dados Data-driven: os dados geram respostas Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Henrique Madeira, DEI-FCTUC, 2003 BD operacionais vs Data Warehouses 13 O que é o modelo multidimensional O que é que está aqui dentro? Net Henrique Madeira, DEI-FCTUC, 2003 ? Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Data Warehousing e OLAP 14 7 Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Modelo multidimensional • Factos armazenados num array multidimensional; • As dimensões são usadas para indexar o array; do • Normalmente construídas sobre bases de dados relacionais. Continente Leiria Continente Coimbra Henrique Madeira, DEI-FCTUC, 2003 Hi pe rm er ca Vendas Leite Produto Farinha Açúcar Café Jan Fev Mar Abr Data Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP 15 Exemplo de esquema em estrela Cadeia de Lojas Tempo Produto ID_produto Nome Tipo Marca Categoria Embalagem Descrição Loja Venda ID_data ID_produto ID_loja Unid_vendidas Custo_compra Valor_venda Nº_Clientes Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP ID_loja Nome Localidade Distrito Área Nº_Caixas Data Warehousing e OLAP Henrique Madeira, DEI-FCTUC, 2003 ID_data Dia Dia_da_semana Semana_do_ano Mês Trimestre Ano 16 8 Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Modelo em estrela O modelo dimensional típico conduz a uma estrutura em estrela, contendo uma tabela central com os factos à qual estão ligadas as tabelas das dimensões Tabela dimensão 1 Descrição 1 Atributo . . . Tabela dimensão 2 ID_dimensão 2 Descrição 2 Atributo . . . Tabela dimensão 3 ID_dimensão 3 Tabela Factos Descrição 3 Atributo . . . ID_dimensão 1 ID_dimensão 2 ID_dimensão 3 ID_dimensão 4 Facto 1 Facto 2 . . . Facto n Henrique Madeira, DEI-FCTUC, 2003 ID_dimensão 1 Tabela dimensão 1 ID_dimensão 4 Descrição 4 Atributo . . . Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP 17 Algumas características do modelo em estrela Tabela dimensão 11 ID_dimensão • Constituída por atributos numéricos (factos) e pelas chaves forasteiras que a ligam à tabelas de dimensões; • A tabela de factos está bastante normalizada; Descrição 1 Atributo . . . Tabela dimensão 22 ID_dimensão • Contém normalmente uma enorme quantidade de registo (ocupa vulgarmente mais de 95% do espaço da DW). Tabela Tabela Factos ID_dimensão 1 ID_dimensão 2 ID_dimensão 3 ID_dimensão 4 Facto 1 Facto 2 . . . Facto n Descrição 2 Atributo . . . dimensão 33 ID_dimensão Descrição 3 Atributo . . . Tabela dimensão 14 ID_dimensão Descrição 4 Atributo . . . Tabelas de Dimensões • Há tantas dimensões quantas vertentes sob as quais se pretende analisar os factos; • As tabelas de dimensões são fortemente desnormalizadas, sendo normalmente tabelas com muitos atributos; • Normalmente, apesar de terem muitos atributos, contêm poucos registos (quando comparados com a tabela de factos). Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Data Warehousing e OLAP Henrique Madeira, DEI-FCTUC, 2003 Tabela de Factos 18 9 Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Passos para definir modelos em estrela 1 - Identificar os processos de negócio/actividade 3 - Identificar dimensões; 4 - Escolher a ganularidade os ad Ds d B s s o eis ( a t ) v vis poní , etc e is os rd de nte d cheir r e dos e a iregistar. pdados m vam ais, f e i S ct on efe eraci op Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Exemplo Data Warehousing e OLAP Henrique Madeira, DEI-FCTUC, 2003 2 - Identificar os factos; 19 - Cadeia de lojas Produto ID_produto ID_data ID_Loja ID_Promoção Unid_vendidas Custo_compra Valor_venda Nº_clientes Tempo Promoção ID_data Dia_do_mês Dia_da_semana Dia_do_ano Semana_do_ano Mês Número_do_mês Trimestre Período_fiscal Flag_feriado Flag_dia_semana Flag_últ_dia_mês Estação_ano Aconteci_espec ………. ID_promoção Número Nome_promo Tipo_red_preço Tipo_anúncio Tipo_cartaz Tipo_coupons Meio_anúncio Meio_cartaz Custo_promoção Início_promoção Fim_promoção ……... Loja ID_loja Nome Número_loja Endereço Localidade Código_postal Distrito Região Telefone Gestor_loja Área_total Área_mercearias Área_congelados Área_bazar Nº_Caixas Data_inauguração Data_ult_remod. ……... Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Data Warehousing e OLAP Henrique Madeira, DEI-FCTUC, 2003 ID_produto Número Nome Marca Categoria Subcategoria Departamento Tipo_embalagem Tipo_dieta Peso Unidade_de_peso Quant_caixa Caixas_p_pallete Larg_prateleira Altura_prateleira Profun_pratelei ……... 20 10 Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Dimensão tempo ID_data Dia_do_mês Dia_da_semana Dia_do_ano Semana_do_ano Mês Número_do_mês Trimestre Período_fiscal Flag_feriado Flag_dia_semana Flag_último_dia_mês Estação_ano Acontecimento_espec ………. • Existe sempre, pois representa a dependência temporal inerente à DW; • Deve descrever o tempo tal como ele é visto para fins de gestão da actividade (negócio) em causa; • Deve conter a caraterização do tempo nos atributos pelos quais se pretende posteriormente fazer pesquisas; • É gerada, normalmente, de uma forma sintética (i.e., sem ser a partir de uma BD operacional) para todo o período de tempo considerado na DW. Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Henrique Madeira, DEI-FCTUC, 2003 Tempo 21 Dimensão produto Produto • Deve conter a caraterização dos produtos tal como eles são vistos pelo gestor da cadeia de lojas; • Contém todos os atributos pelos quais se pretende posteriormente fazer perguntas; • Como acontece normalmente nas tabelas de dimensões, é uma tabela bastante desnormalizada. Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Data Warehousing e OLAP Henrique Madeira, DEI-FCTUC, 2003 ID_produto Número Nome Marca Categoria Subcategoria Departamento Tam_embalagem Tipo_embalagem Tipo_dieta Peso Unidade_de_peso Quantidade_caixa Caixas_por_pallete Largura_prateleira Altura_prateleira Profud_prateleira ……... 22 11 Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Dimensão loja Loja • Contém a caraterização das lojas tal como eles são vistos pelo gestão da cadeia de lojas; • Contém todos os atributos pelos quais se pretende posteriormente fazer perguntas, incluindo atributos de natureza geográfica (localização) e de natureza temporal (datas de inauguração,…). Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Henrique Madeira, DEI-FCTUC, 2003 ID_loja Nome Número_loja Endereço Localidade Código_postal Distrito Região Telefone Fax Gestor_loja Área_total Área_mercearias Área_congelados Área_bazar Nº_Caixas Data_inauguração Data_ultim_remod. ……... 23 Dimensão promoções ID_promoção Número Nome_promo Tipo_red_preço Tipo_anúncio Tipo_cartaz Tipo_coupons Meio_anúncio Meio_cartaz Custo_promoção Início_promoção Fim_promoção ……... • Contém a caraterização das promoções efectuadas; • Neste exemplo há apenas uma dimensão de promoções (para todos os tipos de promoções), mas seria possível ter em alternativa uma dimensão para cada tipo de promoção; • A dimensão promoção representa, neste exemplo, uma dimensão muito sensível e importante, pois as promoções são um dos aspectos em que o gestor mais facilmente pode actuar quando pretende incrementar as vendas numa loja ou num determinado produto. Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Data Warehousing e OLAP Henrique Madeira, DEI-FCTUC, 2003 Promoção 24 12 Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Análise dos dados (ferramentas OLAP) Produto ID_produto ID_data ID_Loja ID_Promoção Unid_vendidas Custo_compra Valor_venda Nº_clientes Tempo ID_data Dia_do_mês Dia_da_semana Dia_do_ano Semana_do_ano Mês Número_do_mês Trimestre Período_fiscal Flag_feriado Flag_dia_semana Flag_últ_dia_mês Estação_ano Aconteci_espec ………. Loja ID_loja Nome Número_loja Endereço Localidade Código_postal Distrito Região Promoção Telefone Gestor_loja ID_promoção Área_total Número Área_mercearias Nome_promo Área_congelados Tipo_red_preço Área_bazar Tipo_anúncio Nº_Caixas Tipo_cartaz Data_inauguração Tipo_coupons Data_ult_remod. Meio_anúncio Valor_venda x Unid_vendidas ……... Meio_cartaz Custo_promoção Início_promoção Fim_promoção ……... Select avg( ) from Venda V, Tempo T, Produto P where JOIN_TABELAS group by P.Marca, T. Mês Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Henrique Madeira, DEI-FCTUC, 2003 ID_produto Número Nome Marca Categoria Subcategoria Departamento Tipo_embalagem Tipo_dieta Peso Unidade_de_peso Quant_caixa Caixas_p_pallete Larg_prateleira Altura_prateleira Profun_pratelei ……... 25 Mais do que uma estrela Tempo Vendas ID_tempo ID_produto ID_armazém Unid_vendidas Custo_compra Valor_venda Nº_Clientes Quant_existente Quant_saída Valor_de_custo Últim_preço_venda Produto Armazém • Uma ou mais estrelas interligam-se por uma ou mais dimensões; • As dimensões que promovem a interligação têm de ser conformes (conter informação consistente entre si); • Drill across: consulta à DW que cruza mais do que uma estrela. Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Data Warehousing e OLAP Henrique Madeira, DEI-FCTUC, 2003 Loja Existências ID_data ID_produto ID_Loja 26 13 Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Exemplo de dimensões conformes Dimensão_1 Dimensão_2 Dimensão_3 Dimensão_1 Tabela de Factos Dimensão10 Tabela de Factos Dimensão_6 Dimensão_7 Dimensão_8 Têm de ser conformes Dimensão_9 Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Henrique Madeira, DEI-FCTUC, 2003 Tabela de Factos Dimensão_4 Dimensão_5 27 Será que é tudo tão simples? • As dimensões nem sempre são pequenas • As dimensões nem sempre são estáticas • É difícil definir o modelo multidimensional para alguns negócios • Há relacionamentos M para N entre dimensões e factos. Henrique Madeira, DEI-FCTUC, 2003 • Etc, etc, etc • E ainda os grandes problemas – Espaço ocupado – Velocidade de execução das queries Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Data Warehousing e OLAP 28 14 Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Exemplo - Cadeia de lojas Produto ID_produto ID_data ID_Loja ID_Promoção Unid_vendidas Custo_compra Valor_venda Nº_clientes Tempo Promoção ID_data Dia_do_mês Dia_da_semana Dia_do_ano Semana_do_ano Mês Número_do_mês Trimestre Período_fiscal Flag_feriado Flag_dia_semana Flag_últ_dia_mês Estação_ano Aconteci_espec ………. ID_promoção Número Nome_promo Tipo_red_preço Tipo_anúncio Tipo_cartaz Tipo_coupons Meio_anúncio Meio_cartaz Custo_promoção Início_promoção Fim_promoção ……... Loja ID_loja Nome Número_loja Endereço Localidade Código_postal Distrito Região Telefone Gestor_loja Área_total Área_mercearias Área_congelados Área_bazar Nº_Caixas Data_inauguração Data_ult_remod. ……... Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Henrique Madeira, DEI-FCTUC, 2003 ID_produto Número Nome Marca Categoria Subcategoria Departamento Tipo_embalagem Tipo_dieta Peso Unidade_de_peso Quant_caixa Caixas_p_pallete Larg_prateleira Altura_prateleira Profun_pratelei ……... 29 Cálculo simplificado do espaço ocupado Granularidade = Produtos vendidos / em cada loja / em cada dia Tempo = 3 anos Nº Produtos = 100.000 (apenas 20% dos produtos são vendidos diariamente) Lojas = 100 Tamanho médio de registo = 8 atributos x 4 Bytes = 32 Bytes Tamanho aproximado da DW = 32 x 2.190.000.000 = 70 GBytes • Despreza-se o espaço ocupado pelas tabelas de dimensões; • Não considera o armazenamento dos índices nem vistas materializadas; Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Data Warehousing e OLAP Henrique Madeira, DEI-FCTUC, 2003 Nº de registos de factos = 3 x 365 x 20.000 x 100 = 2.190.000.000 30 15 Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Como acelerar as respostas a queries? – Infraestrutura HW + SO + SGDB correctamente administrada e afinada. – Cuidados particulares com os sistemas de discos (vários discos, particionamento, RAID). – Parâmetros físicos correctos para as tabelas (e todos os outros objectos). – Correcta indexação (B*Tree e Bit-map) com parametros físicos correctamente definidos. – Manutenção das estatísticas do SGBD para permitir optimização das queries. • • • • Usar agregados (vistas materializadas) Afinar, afinar, afinar Usar processamento paralelo/distribuído Redução de dados Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Henrique Madeira, DEI-FCTUC, 2003 • Bom projecto lógico dos esquemas em estrela • Bom projecto físico (as coisas básicas) 31 Redução de dados e sumarização Henrique Madeira, DEI-FCTUC, 2003 Net Net Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Data Warehousing e OLAP 32 16 Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Processamento distribuído e paralelo Henrique Madeira, DEI-FCTUC, 2003 Net Net Jornadas de Engenharia Informática, Instituto Politécnico da Guarda, 27 e 28 de Maio de 2003 Data Warehousing e OLAP Data Warehousing e OLAP 33 17
Download
