DB2 Data Warehouse. Alunas: Caroline Beatriz Perlin Elaine Amendola Vanessa Magalhães - PDF
Download "DB2 Data Warehouse. Alunas: Caroline Beatriz Perlin Elaine Amendola Vanessa Magalhães"
Pedro Henrique Fialho Meneses
1 DB2 Data Warehouse Alunas: Caroline Beatriz Perlin Elaine Amendola Vanessa Magalhães
2 Agenda Introdução DB2 Data Warehouse Edition (DB2 DWE) Ferramentas Metadados DB2 cube views Materialized Query Tables (MQTs) Exercícios 2
3 O que é DB2? DB2 é um banco de dados relacional desenvolvido pela IBM. DB2 pode rodar em Linux, Unix e Windows (LUW). Também pode ser instalado em i5/os (computadores midrange) e z/os (mainframe). DB2 usa o mesmo código base para os sistemas operacionais LUW. A partir da versão 9, o DB2 é considerado um banco de dados híbrido, pois ele pode armazenar nativamente tanto dados relacionais quanto dados hierárquicos (XML). DB2 9.7 é a versão mais recente. 3
4 A família DB2 system x (Intel), AMD, system p servers DB2 para Linux UNIX e Windows DB2 para z/os DB2 para VSE e VM DB2 para i5/os Midrange servers (system i) 4
5 História do DB2 Ano IBM inventa o modelo relacional e a linguagem SQL. Nasce o DB2 no mainframe primeiro Sistema Gerenciador de Banco de Dados relacional. Lançamento do DB2 para AIX. Lançamento do DB2 Universal Database (UDB) versão 5 DB2 9, o primeiro banco de dados híbrido, é lançado DB2 9.7 é lançado Realização 5
6 Centro de Controle do DB2 6
7 Versões do DB2 7
8 DB2 Data Warehouse Edition Uma plataforma integrada para análise baseada em data warehouse Toda a infra-estrutura e ferramentas para construção de DW Estruturas analíticas para mineração de dados e OLAP Aplicações web com tabelas e gráficos para mostrar as métricas 8
9 DB2 Data Warehouse Edition - Arquitetura Plataforma integrada para análise baseada em warehouse Arquitetura de componentes Ferramentas e metadados compartilhados Análise embutida em aplicações web Ferramenta integrada para modelagem de dados, OLAP, mineração de dados e DW 9
10 Pré-requisitos de instalação Espaço em disco Aproximadamente 1.2 GB de espaço temporário para a imagem de instalação 550 MB de espaço temporário para executar a instalação 1.5 GB de espaço permanente no HD para Linux e Windows, 2 GB no AIX, e 2.5 GB para Solaris e HP-UX. Memória 1 GB para executar o programa de instalação Mínimo de 1GB para executar o DB2 Data Warehouse Edition, sendo que o recomendado é 2 GB de memória. 10
11 Componentes do DB2 DWE DB2 Warehouse Design Studio DB2 Warehouse SQL Warehousing Tool DB2 Warehouse Administration Console DB2 Enterprise Server Edition V9.5 for Linux, UNIX, and Windows DB2 Warehouse Cubing Services DB2 Query Patroller V9.5 DB2 Warehouse Intelligent Miner 9.5 IBM Alphablox V9.5 Documentação do DB2 Warehouse V9.5 WebSphere Application Server V
12 DB2 Alphablox Ferramenta de análise web citada anteriormente. Para usuário final é uma aplicação web com análise embutida e interface amigável. 12
13 Business Intelligence (BI) Entender o passado Otimizar o futuro 13
14 Metadados As informações sobre o ambiente OLAP são armazenadas como metadados. Os metadados contém informações sobre: A estrutura do banco de dados (agregações) Métricas de negócio (atributos da tabela de fatos) Hierarquias existentes em cada dimensão (níveis) Melhoram a produtividade pois explicam a natureza dos dados. Como a estrutura dos metadados é conhecida, as aplicações podem utilizá-los. 14
15 DB2 Cube views Permitem o armazenamento dos metadados do modelo OLAP Medidas Dimensões Hierarquias das dimensões Permite o compartilhamento de metadados com outras ferramentas DB2 é OLAP-aware, ou seja, é capaz de usar os metadados para otimizar o banco de dados para OLAP. 15
16 Materialized Query Tables - MQTs Agregações ou sumários dos dados base podem ser criados com antecedência e armazenados como Materialized Query Tables (MQTs). O otimizador do DB2 reconhece se uma consulta requer agregação e se ela possui uma MQT relevante disponível para o uso, então reescreve a consulta para ser executada sob a MQT. Como o DB2 tem acesso aos metadados OLAP, ele pode sugerir as MQTs mais eficientes a serem construídas. Benefícios: Como a MQT é um sumário (ou filtro) dos dados base, ela tende a ter tamanho menor que as tabelas base da qual foi derivada ganho de desempenho Economia no custo de junção ganho de desempenho 16
18 Criando um banco de dados Vamos criar um banco de dados chamado DWESAMP, executando o script \scripts\setupsqw.bat no prompt do Windows. Iniciar > Run > digita cmd > executa o arquivo setupsqw.bat. db2 terminate db2 force applications all db2 drop db dwesamp db2 create db dwesamp db2 connect to dwesamp db2 -tvf sqw\dwesamp_cfg.sql db2 -tvf createwarehousetables.sql db2 -tvf loadwarehousetablesminusitmtrns.sql db2 -tvf adddwhri.sql db2 -tvf sqw\dwesamp_marts_func.sql db2 -tvf createmarttables.sql db2 -tvf loadmarttablestimeproduct.sql db2 -tvf addmartri.sql db2 -tvf countrowssqw.sql 18
19 Verificar se o banco foi criado Iniciar > Programas > IBM DB2 > General Administration Folders > DB2 Control Center Selecione o modo Avançado do Control Center. Expandir All databases > clique em DWESAMP 19
20 Criar um projeto no design studio 1. Abra o Design Studio by clicando em Iniciar Programas IBM DB2 Data Warehouse Edition Design Studio. 2. No Design Studio, clique Arquivo Novo Projeto. 3. No wizard de novo projeto, selecione Armazenamento de dados OLAP (Data Design Project) e clique em Avançar. 4. Coloque o nome de Proj1 no projeto e clique em Concluir. O projeto chamado Proj1 deve aparecer no Explorador de projetos de dados. 20
21 Criar um modelo de dados físico com engenharia reversa Clique com o botão direito no diretório Modelos de dados e vá em Novo Modelo de Dados Físico. Na primeira parte do wizard, selecione os seguintes itens e clique em Avançar: Nome do arquivo: DWEProj1. Versão: V8.2. Selecione Criar a partir de engenharia reversa. Selecione Banco de dados e Avançar. Na próxima janela, selecione Criar uma Nova Conexão e clique em Avançar Desmarque a opção Utilizar convenção de nomenclatura padrão. Nome do banco de dados: DWESAMP Id do usuário: db2admin Senha: db2admin Teste a conexão Selecione os esquemas DWH e MARTS. Clique em Concluir. Os esquemas DWH e MARTS estão agora no modelo de dados físico de seu projeto de dados. 21
22 Criando um modelo de cubo Agora iremos criar um modelo de cubo para análise OLAP que descreve os relacionamentos entre os dados relacionais. O modelo de cubo permite a definição dos aspectos dos dados que são importantes para o projeto. Os modelos de cubo são criados geralmente para representar um modelo estrela ou floco de neve: Esquema estrela: tabela de fatos ao centro e uma ou mais ta belas de dimensão que são agregadas a tabela de fatos. Esquema floco de neve: é uma estensão do esquema estrela tal que uma ou mais dimensões são definidas por múltiplas tabelas. O modelo de cubo é construído em torno de um objetos de fatos central. Em cada face do cubo, temos a resposta a uma hierarquia de dados. 22
23 Importar o modelo de cubo e seus metadados Clique em Arquivo Importar. Na próxima janela, selecione Metadados OLAP e clique em Avançar. Selecione o arquivo XML do caminho /OLAP/partialSample.xml. Na lista Importar para, selecione DWESAMP (Exemplo/DWEProj1.dbm) e clique em Avançar. (O arquivo XML pode ter sido gerado pelo Design Studio ou pelo DB2 Cube Views) Expandir MARTS para ver os objetos criados. Marque a opção Substituir objetos existentes e clique em Concluir. Mostrar o arquivo XML. Verifique os metadados OLAP que foram importados: Expandir Proj1 Modelo de Dados DWEProj1.dbm DWESAMP MARTS Objetos OLAP Modelos de cubo para ver o modelo de cubo da Análise de Perfil de Compra (Purchase Profile Analysis) e metadados relacionados que você importou. Expanda o modelo de cubo Purchase Profile Analysis para ver o objeto de fatos e as dimensões Product, Store, e Time. Clique no objeto de fatos Purchase Profile Analysis e veja as medidas na visão Propriedades. As medidas da seguinte tabela foram importadas das necessidades da companhia. 23
24 Medidas Clique Arquivo Salvar todos para salvar as atualizações dos metadados do projeto. 24
25 Adicionar hierarquia à dimensão Time No Explorador de projeto de dados, faça a expansão da dimensão Time. Clique com o botão direito no diretório Hierarquias e inclua uma hierarquia. Coloque Calendar Year Hierarchy como nome da hierarquia. Em Propriedades, vá em Níveis. Especificaremos os níveis a serem incluídos na hierarquia. Clique no botão de + para adicionar um nível à hierarquia. Selecione os seguintes níveis e clique em OK: Calendar Year Level Calendar Year-Quarter Level Calendar Year-Quarter-Month Level Day of Calendar Month Level Selecione todos os níveis e coloque o nome All Time (Calendar). Este é um nível opcional que existe no topo da hierarquia e tem um membro que representa a agregação dos membros de níveis inferiores. Na seção Otimizar, selecione Dependência funcional para especificar que você quer aplicar dependência funcional entre os níveis de atributos para melhorar a otimização. Clique Arquivo Salvar todos. 25
26 Criando um cubo (1/3) Criaremos um cubo chamado Análise de Preços que inclui as métricas e dimensões necessárias aos analistas de negócios da empresa. Cubos são componentes críticos dos metadados OLAP, utilizados pelas aplicações que geram relatórios. Para criar o cubo Análise de Preços: No Explorador de projeto de dados, faça a expansão do diretório Cubos, dentro do modelo de cubo Purchase Profile Analysis. Clique com o botão direito no diretório Cubos e inclua um cubo. O nome do cubo deve ser Analise de Precos Adicione dimensões ao cubo: Na seção Dimensões da área de Propriedades, clique no ícone + para adicionar dimensões Na janela de dimensões, clique em Selecionar todos e clique OK. 26
27 Criando um cubo (2/3) Especificar uma hierarquia para cada dimensão do cubo: No Explorador de projeto de dados, clique com o botão direito na dimensão Time do cubo e selecione Incluir Objeto de Dados Hierarquia do Cubo. Selecione a hierarquia para cada dimensão. Product Store Dimensão Hierarquia Product Category Management Organization Unit Store Time Calendar Year hierarchy Veja na seção Níveis se todos os níveis de cada hierquia do cubo foram incluídos. Adicionar as medidas que queremos incluir nos Fatos do Cubo: Selecione o objeto Fatos do Cubo e abra a seção de Medidas Clique no ícone + para adicionar medidas no Fatos do Cubo.Selecione as seguintes medidas: Number of Items Product Book Price Amount Sales Amount Average item price sold Average product book price Profit Amount Average profit amount per item Profit margin percentage 27
28 Criando um cubo (3/3) Clique OK para adicionar as medidas ao objeto Fatos do Cubo. Especifique Sales Amount como medida padrão, clicando no check box da coluna Padrão. Clique Arquivo Salvar todos. 28
29 Implementar os metadados no banco de dados Clique com o botão direito no modelo de cubo Purchase Profile Analysis no Explorador de projeto de dados e clique em Implementar no Banco de Dados. Selecione o banco DWESAMP e clique em Concluir. 29
30 Criando MQTs Vá em Janela Mostrar visualização Explorador do banco de dados. Faça a expansão do banco de dados DWESAMP clicando em Esquemas MARTS Objetos OLAP Modelos de Cubo. Clique com o botão direito no modelo de cubo Purchase Profile Analysis e vá em Orientador de Otimização. (O modelo de cubo é validado em relação às regras de metadados. Se qualquer parte do modelo for inválida, aparecerá um erro e você precisará modificar o modelo de cubo para que ele seja válido, antes de otimizá-lo). 30
31 Criando MQTs Agora devemos identificar os tipos de consultas que desejamos otimizar o cubo Análise de Preço. O tipo de consulta ao cubo é usado para melhorar os resultados da otimização. Para especificar o tipo da consulta: Clique no botão... para modificar o tipo de consulta ou especificar porções de otimização para o cubo de Análise de Preço. Na janela de Tipo de Consulta, selecione Configurações Avançadas e clique no botão Especificar. Na janela de Porções de Otimização, vamos criar uma porção de otimização para o cubo Análise de preço. Clique no ícone + para adicionar uma nova porção de otimização ao cubo. Para a nova porção, especifique um nível para cada hierarquia. Dimensão Time: nível Calendar Year Dimensão Product: qualquer nível Dimensão Store: qualquer nível Clique em OK. Clique em OK e em Avançar. Esta porção irá otimizar as consultas drill-down que analisam os dados para qualquer nível de produtos e lojas que sejam agregadas por ano. Drill Down Roll Up 31
32 Criando MQTs Na janela de tabelas de resumo, especifique a opção de atualização adiada (a atualização não é imediata). Selecione a tablespace USERSPACE1 para armazenar as MQTs e seus índices. Clique em Avançar. Na janela de limitações, selecione Não especifique um limite de espaço em disco e Não especifique um limite de tempo para ter tempo e espaço em disco ilimitado para o Orientador de Otimização (Optimization Advisor). Marque o checkbox de Amostragem de dados (utiliza os dados para sugerir melhores recomendações). Quanto mais espaço, informação e tempo que você especificar, melhores serão os resultados de desempenho. Clique em Iniciar Orientador para iniciar o Orientador de Otimização. Esta tarefa de criar as recomendações deve levar alguns minutos. Após o término das recomendações do Orientador de Otimização, clique em Avançar. Na janela de scripts de SQL, coloque como nome do arquivo createmqts.sql em Script SQL para criar as tabelas de resumo e refreshmqts.sql como nome do arquivo do Script SQL para atualizar as tabelas de resumo. Clique em Concluir para salvar os scripts SQL recomendados. 32
33 Visualização gráfica OLAP Iniciar Programas IBM DB2 Business Intelligence Tools OLAP Center Informações para conexão: Banco de dados: DWESAMP Usuário: db2admin Senha: db2admin Clique em Purchase Profile Analysis para ver a modelagem OLAP. Em Cubes, clique em Analise de Precos para ver o cubo que criamos. 33
34 Implementando as MQTs recomendadas No OLAP Center, clique em Tools Command Editor. Clique em Add para adicionar um target database e selecione o banco de dados DWESAMP. Clique em OK. Clique no botão open e selecione o arquivo createmqts.sql que criamos anteriormente. Clique em OK. O arquivo createmqts.sql irá aparecer na tela. Clique no botão executar (>) para executar o arquivo. Veja na seção embaixo o resultado da execução do arquivo SQL. No Control Center, veja as tabelas que foram criadas: DB2INFO.MQT T01 DB2INFO.MQT T02 34